Step 3: Create the outgoing link report

This article is part 3 of Sitecore How To: Track Exteral links with DMS series. Before starting with this step you need to be finished with Step 2. Otherwise you don’t have any report data.

Show the outgoing links in a report

The easiest way to generate a report is find a report that matches your needs, copy the report and change the datasource and layout. For this POC the Slow Pages report matches 80% of my requirements, so let’s use the Slow Pages report as a base for our new report.

Download the External Links per Page.mrt Report

You can download the External Links per Page report. Unpack the zip file and save .mrt the file in the folder /sitecore/shell/Applications/AnalyticsReports.

This report is a customized version of the slow pages report. For more information about report designing you can read the Report Designer Cookbook

Copy the Slow Pages Report SQL Query Item

Duplicate the slow pages Report SQL Query item (/sitecore/system/Settings/Analytics/Reports SQL Queries/Slow Pages) and name the Item External Links per Page.

Delete the SQL query in the SQL server field and add the following SQL query.

[code language=”sql”]

select    top 100
Pages.Url,
PageEvents.Data as Link,
COUNT(*) as Clicks
from
Pages,
PageEvents,
Visits,
PageEventDefinitions
where
Pages.VisitId = Visits.VisitId
AND Pages.PageId = PageEvents.PageId
AND PageEventDefinitions.PageEventDefinitionId = PageEvents.PageEventDefinitionId
AND PageEventDefinitions.Name = ‘External Link’
AND Visits.StartDateTime BETWEEN @StartDate AND @EndDate
group by
Pages.Url, PageEvents.Data
order by
Clicks desc

[/code]

This query will return all outgoing clicks from every page.

Copy the Slow Pages Report Item

Open the content editor and duplicate the Slow Pages Report item located at: /sitecore/system/Settings/Analytics/Reports/Reports/Site Health/Slow Pages

Name the duplicated report External Links per Page.

In the External Links per Page item change the Filename to External Links per Page.mrt and change the Report Title field.

In the Queries section. For Failure select the External Links per Page Report SQL Query item.

 

Sitecore DMS Queries
Note: You might want to rename the datasource name Failure to something like Datasource. If you do this you need to do a search and replace within the .mrt file.

Test you report

Login to the Sitecore Desktop and open the Engagement Analytics. Underneath the Site Health node you will find the External Links per Page report.

 


In this report we can see that we generated three clicks from our products page to Google.nl and two clicks from our homepage to test.nl.

This report is only a example. You can create all kind of reports all based on the event table in the Analytics Database.

Step 2-1: Track all external links with a custom processor

This article is part of Sitecore How To: Track Exteral links with DMS series. Before starting with this step you need to be finished with Step 1: Save outgoing click to the DMS analytics database.

For this solution I want to measure all the outgoing link using the Analytics Database of Sitecore Digital Marketing System (DMS). Based on this data I want to create a report in the Sitecore Engagement Analytics that will show all outgoing clicks by page. I want to create a solution that rewrites all external links within rich-text fields and General Link fields. With this solution all external links on an existing website will be automatically rewritten and measured.

In this article I will create a solution for measuring outgoing links in the following steps:

  • Create a processor to rewrite all external links on the website.

Let get started!

Create the processor for rewriting external links

For the rewriting of the external links we are creating a Render Field Processor. This processor will fire for every field that is rendered. During this process we will rewrite the URL.

The easiest way to create a new processor is with the Visual Studio 2010  plugin Sitecore Rocks. One of the many things Sitecore Rocks does is creating Visual Studio Templates. These templates will help you extending Sitecore. Open your solution in VS2010 and add a new item. In the dialog window go to Sitecore –> Pipelines en selecteer Render Field Processor. Name the processor ExternalUrlReWriterProcessor.cs and click Add.

Sitecore custom processor

The Render Field Processor Template will generate two files for you; the ExternalUrlReWriterProcessor.cs and a  ExternalUrlReWriterProcessor.config.

 

Custom sitecore config processor

Modify the ExternalUrlReWriterProcessor class that all external URLs are rewritten to out /link.aspx page that we are going to create next. Below is my POC code. Please take notice of the comments and the TODO comments.

[code language=”csharp”]
public class ExternalUrlReWriterProcessor
{
public void Process([NotNull] RenderFieldArgs args)
{
// Do not modify output if the field is not a rich text field or general link
// or if the page is in page editor mode
if ((args.FieldTypeKey != “rich text” && args.FieldTypeKey != “general link”) ||
String.IsNullOrEmpty(args.FieldValue) ||
Sitecore.Context.PageMode.IsPageEditorEditing)
{
return;
}

//Check if URL is external link
if (args.FieldTypeKey == “general link”)
{
Sitecore.Data.Fields.LinkField linkField = args.GetField();
//Don’t change the link if it’s not a external link
if (linkField.LinkType != “external”)
return;

}
string fieldValue = args.Result.FirstPart;

string changedUrl = Regex.Replace(fieldValue, @”href=””(.*?)”””, delegate(Match match)
{
//Regex definition to check if URL contains HTTP or HTTPS (so it’s an external link)
Regex rgxExternal = new Regex(@”Regex rgxExternal = new Regex(@”(((http(s?))\://){1}\S+)”);”);

string orriginalUrl = match.Groups[1].Value.ToString();
if (rgxExternal.IsMatch(orriginalUrl))
{
//Rewrite the url to the redirect item
//TODO: Make the path configurable
return @”href=””/link.aspx?url=” + orriginalUrl + @””””;

}

return match.Value;
}, RegexOptions.Compiled);

args.Result.FirstPart = changedUrl;

}
}

[/code]

If you check your website all external links will be rewritten to /link.aspx?url=[EXTERNAL LINK]. Now let’s create the links.aspx and save the outgoing link information to the DMS Analytics database.

Test if it works

You can test if the solution is working on your website by testing the following functionalities

Action Expected result
Create a internal link in a rich-text field The link is not rewritten.
Create a external link in a rich-text field The link is rewritten to /link.aspx?url=[THE LINK]
Create a internal link in a general link field The link is not rewritten.
Create a external link in a general link field The link is rewritten to /link.aspx?url=[THE LINK]
Click a external URL The visitor is redirected to the external website.

Converting .mrt reports from OMS to DMS

In this article I’m going to explain how to convert .mrt reports from the Sitecore Online Marketing Suite (Sitecore 6.4) to the Sitecore Digital Marketing System (Sitecore 6.5).

Report related changes between OMS and DMS

Both the OMS and the DMS use .mrt reports for Analitics reporting. There are two mayor changes between the OMS and DMS reporting; a full redesign of the datamodel and the location of the SQL query for the report data.

Mapping the datamodel of the OMS with the DMS

If you compare the datamodel of the OMS to the DMS datamodel you will see that it’s completely different. But the main concept of both datamodels is almost the same.

OMS table DMS Table Description
GlobalSession Vistors Contains the Visitor information.
Session Visits Contains the information of all the visits. A visits is underneed a Visitors.
Page Pages Contains all pages that are visited and are connected to a Session.

The following drawing will show the relation of Visitor->Sessions->Pages.

image

In this example we have a Continue reading “Converting .mrt reports from OMS to DMS”