Build Custom Analytics Report - Part 2 – Storing and Retrieving Data

In part 1 of this blog posts series I discussed the business needs for building a custom analytics report, In this part I will show you how you can save your analytics data to MongoDB using the analytics tracker, then how you can aggregate this data into reporting SQL server database, in future blogs posts will show you how we can build SPEAK application to display the data and how you can extend the analytics filters based on your report needs

If you missed part 1 you can check it here.

First thing you need to do is to create a sitecore goal item for each button you want to track, this goal will be triggered when a site visitor click on the required element, as you can see from the following screen shot:

The following script will andadd a click event to the required element (button, anchor…etc.), and as you can see from the following snippet an ajax call will fire to call a backend method called “RegisterGoal”.

	$('.Targetbutton).unbind('click').click(function () {
	var ItemId = $(this).data("ItemId ");
	$.ajax({
	url: '/api/analytics/RegisterGoal,
	type: 'GET',
	dataType: 'json',
	data: { ClickedItemID: ItemId },
	success: function (data) {
	},
	error: function (x, y, z) {
	}
	});
	});

view raw Register Goal hosted with ❤ by GitHub

The following code snippet is the back-end controller method that will be called and as you can see I get all the information needed to be saved, as you see I used the analytics tracker that help you to identify and track contacts so you can see all site contacts activities on your website, You can use this information gathered by tracking contacts during current and past web sessions for personalization, reporting and optimizing your website.

More information can found here.

 

	[ActionName("registergoal")]
	[HttpPost]
	private void RegisterGoal(string ClickedItemID)
	{
	Item goalItem = ItemHelper.GoalItem;
	var ItemInformation = Sitecore.Context.Database.GetItem(ClickedItemID).GlassCast<Location>();
	var goal = new PageEventItem(goalItem)
	var eventRow = Tracker.Current.Interaction.CurrentPage.Register(goal);
	var ItemDataToSave = new StringBuilder();
	ItemDataToSave.Append("Name: ");
	ItemDataToSave.Append(ItemInformation.DisplayName);
	ItemDataToSave.Append(",Address: ");
	ItemDataToSave.Append(ItemInformation.Address + ", " + ItemInformation.City + ", " + ItemInformation.Zip);
	eventRow.DataKey = ClickedItemID;
	eventRow.Data = locationItem.DisplayName;
	eventRow.Text = locationData.ToString();
	}

view raw Register Goal Controller Method hosted with ❤ by GitHub

If you execute the above code you will get the following result in the interaction collection in the analytics MongoDB:

	"PageEvents" : [
	{
	"Name" : "Item Name",
	"Timestamp" : NumberLong(0),
	"Data" : "Item Data",
	"DataKey" : "650fce89-87bb-4768-ab40-8bc988d7a729",
	"Text" : "Item Information",
	"PageEventDefinitionId" : LUUID("e7798282-5362-af4d-80ce-07055fcf3b86"),
	"IsGoal" : true,
	"DateTime" : ISODate("2015-08-23T16:12:21.686Z"),
	"Value" : 10
	}
	],

view raw MongoDB Page Event Data hosted with ❤ by GitHub

Now you need to move the data from the interactions in the analytics collection to the reporting SQL database and to do that you need to configure an aggregation processor which will be triggered when rebuilding the reporting database, detailed information about rebuilding the reporting database can be found here

Following is the code needed for this:

	public class EventKey : DictionaryKey
	{
	public Guid EventId { get; set; }
	public Guid PageId { get; set; }
	public Guid ItemId { get; set; }
	public DateTime Date { get; set; }
	}
	public class YourProcessor : AggregationProcessor
	{
	protected override void OnProcess(AggregationPipelineArgs args)
	{
	try
	{
	var fact = args.GetFact<PageEvent>();
	foreach (var page in args.Context.Visit.Pages)
	{
	foreach (var pEvent in page.PageEvents)
	{
	var eventkey = new EventKey
	{
	EventId = pEvent.PageEventDefinitionId,
	Date = args.DateTimeStrategy.Translate(pEvent.DateTime),
	ItemId = pEvent.ItemId,
	PageId = page.Item.Id
	};
	var eventValue = new EventValue
	{
	Count = 1
	};
	fact.Emit(eventkey, eventValue);
	}
	}
	}
	catch (Exception ex)
	{
	}
	}
	}
	public class EventValue : DictionaryValue
	{
	public int Count { get; set; }
	internal static EventValue Reduce(EventValue left, EventValue right)
	{
	var profileValue = new EventValue
	{
	Count = left.Count + right.Count
	};
	return profileValue;
	}
	}
	public class PageEvent : Fact<EventKey, EventValue>
	{
	public PageEvent() : base(EventValue.Reduce)
	{
	}
	}

view raw Aggregation Processor Code hosted with ❤ by GitHub

Following is the configuration to add your custom processor to the analytics aggregation:

	<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
	<sitecore>
	<pipelines>
	<group groupName="analytics.aggregation">
	<pipelines>
	<interactions>
	<processor type="YourDLL.YourProcessor, YourDLL" />
	</interactions>
	</pipelines>
	</group>
	</pipelines>
	</sitecore>
	</configuration>

view raw Aggregation Processor Config hosted with ❤ by GitHub

One last thing, you need to create a fact table in the reporting database, in this table that data will be aggregated from the MongoDB by the aggregation process, following is the script that you can use to create the table:

	CREATE TABLE [dbo].[Fact_PageEvent](
	[Date] [smalldatetime] NOT NULL,
	[EventId] [uniqueidentifier] NOT NULL,
	[PageId] [uniqueidentifier] NOT NULL,
	[ItemId] [uniqueidentifier] NOT NULL,
	[Count] [int] NOT NULL,
	CONSTRAINT [pk_uniqueEvent] PRIMARY KEY CLUSTERED
	(
	[Date] ASC,
	[EventId] ASC,
	[ItemId] ASC,
	[PageId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

view raw Fact Table Script hosted with ❤ by GitHub

Now you have the data ready to be display in your SPEAK report! SPEAK report is the next step!