Extending Sitecore’s Experience Profile – The Pipelines

Part 1 – ExperienceProfileContactViews Pipeline

I’ve been writing this series on Sitecore xDB, Contacts and the Experience Profile for a while now. I’ve been covering how to identify Contacts for Sitecore’s Experience Database (xDB) and how to extend xDB so it can hold custom data.

This time I’m going to explain how to surface that useful BIG DATA you extended xDB to hold so that it is accessible to Content Editors. This is a large topic so I’ll be splitting it between two posts, this first part will focus on the ExperienceProfileContactViewsPipeline which retrieves the data in a format that the Experience Profile can use.

It’s worth noting that I figured most of this out by just jumping into it, decompiling code and a reasonable amount trial and error. Just take that into consideration 🙂

Sitecore Experience Profile

So Sitecore’s Experience Profile is a dashboard that displays all information collected for every site visitor (called a Contact) and it comes out of the box with Sitecore 8 onwards.

The dashboard displays all Contacts in a searchable list and each can be drilled down into to see the detail of a Contact. As all Contacts are listed, potentially hundreds of thousands, distinguishing between each of them is a necessity – this is one of the benefits of identifying Contacts. This detailed view below is the Experience Profile.

The Experience Profile is used to show most, if not all, information in xDB about the Contact that Sitecore tracks by default. The data is split into logic areas and accessible through tabs.

The timeline gives chronological visual representation of the occurrence of visits, page views, goals etc. The right-hand summary section gives basic information of the Contact as well as their latest visit and overall visit stats.

The tabs offer more detailed information:

Overview displays the latest Events, Campaigns and Profile Cards matched.
Activity holds detailed breakdowns of visits, goals, engagement plans and search keywords.
Profiling shows the pattern matches to personas etc
Details list basic personal information, contact details addresses etc
Social holds their Facebook, Twitter, LinkedIn and for some reason Google+
The Interactions is my custom tab which this post explains how to create

Display custom goals in Experience Profile

It’s worth me covering this quickly before we get into things. If you have created custom Goals, or any other Page Event, in Sitecore and you want them to display in the Experience Profile (I mean why wouldn’t you?) you will need to configure them to do so.

In the Content Editor navigate to the location Page Events are stored typically sitecore/system/Settings/Analytics/Page Events, open the Item relating the goal / page event, and scroll down to the Field section called ‘Experience Profile Options’.

There you will find checkboxes field Track as Latest Event and Show in Events to include these events in the Latest Events List and Events List in the Experience Profile. Check these and they will appear in their respective location. Also you can add an icon for your custom goal if you want.

Retrieve xDB data for Experience Profile

So getting the actual data to show in the Experience Profile is a little different from what you might expect. Essentially it all functions from groups of pipelines and data providers.

ExperienceProfileContactViews pipeline group is used to construct a DataTable and fill it with the data retrieved by the DataSourceQuery for an Experience Profile view
ExperienceProfileContactDataSourceQueries pipeline group actually does the querying of xDB for the Contact’s data
intelResultTransformers provider transforms the DataTable into a format that works with the SPEAK interface of Experience Profile

I’ll continue using the same example from before to understand the real-world application. We have a website, Users can order samples through a purchase path. The sample order’s information is written xDB against the User’s Contact to show what they ordered.

ExperienceProfileContactViews

Each pipeline in the ExperienceProfileContactViews pipeline group consists of a number of processors. A processor to construct the DataTable , to initiate the ContactDataSourceQuery, to fill the DataTable with the retrieved data and default processors to allow paging and sorting of the data through the Experience Profile. Arranged via the following config file.

	<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
	<sitecore>
	<pipelines>
	<group groupName="ExperienceProfileContactViews">
	<pipelines>
	<sampleorders>
	<processor type="ISlayTitans.CMS.Pipelines.ContactFacets.Reporting.SampleOrders.ConstructSampleOrdersDataTable, ISlayTitans.CMS" />
	<processor type="Sitecore.Cintel.Reporting.Processors.ExecuteReportingServerDatasourceQuery, Sitecore.Cintel">
	<param desc="queryName">sampleorders-query</param>
	</processor>
	<processor type="ISlayTitans.CMS.Pipelines.ContactFacets.Reporting.SampleOrders.PopulateSampleOrdersWithXdbData, ISlayTitans.CMS" />
	<processor type="Sitecore.Cintel.Reporting.Processors.ApplySorting, Sitecore.Cintel"/>
	<processor type="Sitecore.Cintel.Reporting.Processors.ApplyPaging, Sitecore.Cintel"/>
	</sampleorders>
	</pipelines>
	</group>
	</pipelines>
	</sitecore>
	</configuration>

view raw

ExperienceProfileContactViews.xml

hosted with ❤ by GitHub

Our ConstructSampleOrdersDataTable processor, implementing ReportProcessorBase, creates a simple DataTable with a column added for each property of the sample order and a few things about the Contact and the Visit when the order was placed. These properties won’t need to be shown in the Experience Profile but are required for sorting etc

	public class ConstructSampleOrdersDataTable : ReportProcessorBase
	{
	public override void Process(ReportProcessorArgs args)
	{
	args.ResultTableForView = new DataTable();
	args.ResultTableForView.Columns.Add(new ViewField<Guid>("ContactId").ToColumn());
	args.ResultTableForView.Columns.Add(new ViewField<Guid>("VisitId").ToColumn());
	args.ResultTableForView.Columns.Add(new ViewField<IEnumerable<KeyValuePair<string, string>>>("SampleOrder").ToColumn());
	args.ResultTableForView.Columns.Add(new ViewField<string>("RangeId").ToColumn());
	args.ResultTableForView.Columns.Add(new ViewField<string>("DecorId").ToColumn());
	args.ResultTableForView.Columns.Add(new ViewField<int>("VisitIndex").ToColumn());
	args.ResultTableForView.Columns.Add(new ViewField<DateTime>("VisitStartDateTime").ToColumn());
	args.ResultTableForView.Columns.Add(new ViewField<DateTime>("VisitEndDateTime").ToColumn());
	}
	}

view raw

ConstructSampleOrdersDataTable.cs

hosted with ❤ by GitHub

The next processor is a out-of-the-box processor, ExecuteReportingServerDatasourceQuery, which we tell to execute our sample orders query pipeline via a config param.This executes our custom ExperienceProfileContactDataSourceQueries pipeline processor. It probably goes without saying, ensure the correct spelling and casing of your processor when defining it as a param (sore subject).

ExperienceProfileContactDataSourceQueries

	<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
	<sitecore>
	<pipelines>
	<group groupName="ExperienceProfileContactDataSourceQueries">
	<pipelines>
	<sampleorders-query>
	<processor type="ISlayTitans.CMS.Pipelines.ContactFacets.Reporting.SampleOrders.GetSampleOrders, ISlayTitans.CMS" />
	</sampleorders-query>
	</pipelines>
	</group>
	</pipelines>
	</sitecore>
	</configuration>

view raw

ExperienceProfileContactDataSourceQueries.xml

hosted with ❤ by GitHub

This processor queries xDB for all the sample orders for the Contact we are viewing in the Experience Profile. I based the implementation on the existing queries that the Experience Profile and looks something like this.

	public class GetSampleOrders : ReportProcessorBase
	{
	private readonly QueryBuilder _contactsQueryBuilder = new QueryBuilder()
	{
	collectionName = "Contacts", // Name of the collection containing contacts in MongoDb
	QueryParms =
	{
	{ "_id", "@contactid" },
	},
	Fields =
	{
	"_id",
	"KeyInteractions_SampleOrder", // path so the Sample Order Element in our custom xDB Facet
	"System_VisitCount"
	}
	};

	public override void Process(ReportProcessorArgs args)
	{
	Guid contactId = args.ReportParameters.ContactId;
	DataTable contactQueryExpression =
	base.GetTableFromContactQueryExpression(_contactsQueryBuilder.Build(), contactId, new Guid?());
	Assert.IsTrue(contactQueryExpression.Rows.Count >= 1,
	string.Format("Contact with id {0} was not found.", (object) contactId));
	int? nullable = DataRowExtensions.Field<int?>(contactQueryExpression.Rows[0], "System_VisitCount");
	if (nullable == null)
	Log.Debug(string.Format("No VisitCount exists for contact: {0}", (object) contactId));
	args.QueryResult = contactQueryExpression;
	}
	}

view raw

GetSampleOrders.cs

hosted with ❤ by GitHub

However…

Issue alert

The default xDB Facets Elements exist as Arrays in MongoDB. However. The Elements we create with the Sitecore API, facet.ElementName.Create();, creates the Elements as Fields in MongoDB.

The upshot means that basing our query on the Sitecore existing code won’t work. After talking with Sitecore Support the most effective method I found to retrieve the data from xDB is by getting the Facet and the Element via the API:

	private void PassContactElementIntoQueryResult(ReportProcessorArgs args)
	{
	DataTable queryResultTable = new DataTable();
	queryResultTable.Columns.Add(new ViewField<string>("DecorId").ToColumn());
	queryResultTable.Columns.Add(new ViewField<string>("RangeId").ToColumn());

	var contactRepository = Sitecore.Configuration.Factory.CreateObject("tracking/contactRepository", true) as ContactRepository;
	var contact = contactRepository.LoadContactReadOnly(args.ReportParameters.ContactId);

	IKeyInteractionsFacet facet = contact.GetFacet<IKeyInteractionsFacet>(KeyInteractionsFacet.FacetName);

	foreach (var sampleOrder in facet.SampleOrders)
	{
	DataRow dataRow = queryResultTable.NewRow();
	dataRow["DecorId"] = sampleOrder.DecorId;
	dataRow["RangeId"] = sampleOrder.RangeId;
	queryResultTable.Rows.Add(dataRow);
	}

	args.QueryResult = queryResultTable;
	}

view raw

PassContactElementIntoQueryResult.cs

hosted with ❤ by GitHub

I’m convinced there is a more suitable way, based on the reports in Sitecore.Analytics.Reports.StimulsoftIntegration.BuiltInFunctionsReportDataSource namespace. I’ll post an update if I do get somewhere with it.

back to ExperienceProfileContactViews

So now we have a populated DataTable in the args of the pipeline we need to transfer the data to the DataTable which can be used by the Experience Profile, i.e. the one we created in the previous processor, ConstructSampleOrdersDataTable.

	public class PopulateSampleOrdersWithXdbData : ReportProcessorBase
	{
	public override void Process(ReportProcessorArgs args)
	{
	DataTable queryResult = args.QueryResult;
	DataTable resultTableForView = args.ResultTableForView;
	ProjectRawTableIntoResultTable(args, queryResult, resultTableForView);
	}

	private void ProjectRawTableIntoResultTable(ReportProcessorArgs args, DataTable rawTable, DataTable resultTable)
	{
	foreach (DataRow sourceRow in DataTableExtensions.AsEnumerable(rawTable))
	{
	DataRow dataRow = resultTable.NewRow();
	TryFillData<Guid>(dataRow, new ViewField<Guid>("ContactId"), sourceRow, "ContactId");
	TryFillData<Guid>(dataRow, new ViewField<Guid>("VisitId"), sourceRow, "_id");
	TryFillData<int>(dataRow, new ViewField<int>("VisitIndex"), sourceRow, "LatestVisitIndex");
	TryFillData<DateTime>(dataRow, new ViewField<DateTime>("VisitStartDateTime"), sourceRow, "StartDateTime");

	TryFillData<string>(dataRow, new ViewField<string>("RangeId"), sourceRow, "RangeId");
	TryFillData<string>(dataRow, new ViewField<string>("DecorId"), sourceRow, "DecorId");
	resultTable.Rows.Add(dataRow);
	}
	}
	}

view raw

PopulateSampleOrdersWithXdbData.cs

hosted with ❤ by GitHub

Almost there for the ExperienceProfileContactViews pipeline. We need to allow the Content Editor to sort and page through the sample orders in the Experience Profile. Adding the Sitecore.Cintel.Reporting.Processors.ApplySorting and Sitecore.Cintel.Reporting.Processors.ApplyPaging processors at the end of our pipeline will add additional properties to allow this. That finishes off the a finish off the pipeline.

IntelResultTransformers – a ResultTransformProvider

On the home stretch for retrieving the data for the Experience Profile. The last piece of code relates to transforming the DataTable into a more useful format. Namely add additional properties to the result set.

Our custom IntelResultTransformer to work on our Sample Orders DataTable is patched in as below, defining which View the Transformer relates by the ViewName property.

	<?xml version="1.0"?>
	<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
	<sitecore>
	<experienceProfile>
	<resultTransformManager>
	<resultTransformProvider>
	<intelResultTransformers>
	<add viewName="sampleOrders" type="ISlayTitans.CMS.Pipelines.ContactFacets.Reporting.SampleOrders.SampleOrderResultTransformer, ISlayTitans.CMS"/>
	</intelResultTransformers>
	</resultTransformProvider>
	</resultTransformManager>
	</experienceProfile>
	</sitecore>
	</configuration>

view raw

intelResultTransformers.xml

hosted with ❤ by GitHub

And the relating code is as follows, adding recency via the Visit’s start DateTime that the interaction occurred on. This is used for sorting on the Experience Profile interface.

	public class SampleOrderResultTransformer : IIntelResultTransformer, IResultTransformer<DataTable>
	{
	private ResultSetExtender resultSetExtender;

	public SampleOrderResultTransformer()
	{
	this.resultSetExtender = ClientFactory.Instance.GetResultSetExtender();
	}

	public SampleOrderResultTransformer(ResultSetExtender resultSetExtender)
	{
	this.resultSetExtender = resultSetExtender;
	}

	public object Transform(ResultSet<DataTable> resultSet)
	{
	Assert.ArgumentNotNull((object) resultSet, "resultSet");
	this.resultSetExtender.AddRecency(resultSet, "VisitStartDateTime");
	return (object) resultSet;
	}
	}

view raw

SampleOrderResultTransformer.cs

hosted with ❤ by GitHub

And that’s not it!

This blog is large enough as it is without all the SPEAK stuff making it unwieldy. So I’ll make a logical break here. In Part 2 I’ll cover how to add to the SPEAK interface so the Experience Profile surfaces all the nice data we just handed it.