Getting around Sitecore Experience Database with a focus on available data to create rich customer experiences

This post is going to form part of a series aimed to walking through some Sitecore technical marketing concepts, how to leverage them from a commerce perspective with the ultimate goal to enable rich and immersive customer experiences.

What is xDB

Sitecore’s Experience Database, xDB for short, is Sitecore’s central data repository for the entire customer experience that can store vast amounts of data. It’s where every interaction with one of your digital properties, managed by Sitecore or otherwise, are stored (including data across channels).

A bit of history, pre version 7.5 xDB was implemented in SQL Server, then moved to MongoDB as it was the best technology at the time for the job. In version 9 MongoDB, Microsoft SQL Server 2016 or SQL Azure), leveraging new features of SQL Server and a new database design making it more suitable for the workload. In my opinion the new design and being back in SQL Server makes it much easier to work with.

Data in xDB

Nearly all visitor data and interaction data, known or anonymous, is stored in xDB. The data being captured can be used to drive on or off site personalisation, drive reporting and analysis, and be exported to data warehouses for further analysis with other data being captured across an organisation.

For this post we are going to drill down on on contacts and their interactions, focusing on understanding what data is stored and where. Its always important to understand available data and how to best leverage it or extend.

Part of the XDB database design in v9 distributes data across a configurable number of shards, in most instances you will at least have two shards like this example. Here is database view of the tables we will be focusing on, each shard having the same structure:

xDB Contacts

A contact represents an individual who interacts interacts with your organization. The contact contains all the information that you collect about an individual from their interactions across channels, devices and websites.

In xDB a crucial pieve of data is the contact id, to find it for a particular visitor\customer it’s best to query the ContactIdentifiers table and here is what a query looks like to make it easy to find the contact by email address. Note, the Identifier field is HEX value when stored.

SELECT *
FROM
(
SELECT *,CONVERT(VARCHAR(MAX), [Identifier]) as DecodedIdentifier, 'Shard0' as Shard
FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[ContactIdentifiers]
UNION ALL
SELECT *,CONVERT(VARCHAR(MAX), [Identifier]) as DecodedIdentifier, 'Shard1' as Shard
FROM [habitathome_Xdb.Collection.Shard1].[xdb_collection].[ContactIdentifiers]
) ContactIdentifiers_Unioned
WHERE Source = 'CommerceUser'

Results:

From these results you can take note of the ContactId and it’s corresponding shard. In this case I’m going to zoom in on magic@sitecore.net

xDB Contact Facets

A facet is a generic concept in xDB, if you review the image above database tables you will see there are a number of facet tables. A facet is a piece of information that enriches a contact or an interaction. For contacts, this might include their name and address. For interactions, this might include the location of the interaction.

Contact facets should describe the qualities of a contact, not their behaviour (thats what interactions are for). For example:

Frequent flyer status
Supermarket loyalty card number

Using this query we can query which contact facets exist for my contact

DECLARE @ContactId varchar(max) = 'E65F09D3-BF06-0000-0000-05ABFFFB0D0F'

SELECT *
FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
WHERE ContactId = @ContactId

Here are the results from the query in my demo environment. You can a number of important facets. Take special note of the facets postfixed with ‘Cache’ as I will hopefully create another post in the coming weeks showing how to create a new facet for a contact with a commerce personalisation focus. But if you need something now, there is some pretty good documentation on the process https://doc.sitecore.com/developers/92/sitecore-experience-platform/en/facets.html and a heap of blog posts that already cover the process.

Another important aspect of facets, again talking xDB facets in general, not specific to contacts, is to determine which facets are loaded into session. Here is some documentation on the topic https://doc.sitecore.com/developers/90/sitecore-experience-platform/en/load-facets-into-session.html. For convenience, here is a quick summary – if you review config file \App_Config\Sitecore\Marketing.Tracking\Sitecore.Analytics.Tracking.config and look for the xconnect dataAdapterManager you will find the configuration which dictates which facets are loaded. Here is what I have in my demo instance:

    <dataAdapterManager defaultProvider="xconnect">
    <providers>
    <clear/>
    <add name="xconnect" type="Sitecore.Analytics.XConnect.DataAccess.XConnectDataAdapterProvider, Sitecore.Analytics.XConnect">
    <facets hint="raw:AddFacet">
    <facet facetKey="Classification"/>
    <facet facetKey="EngagementMeasures"/>
    <facet facetKey="ContactBehaviorProfile"/>
    <facet facetKey="Personal"/>
    <facet facetKey="KeyBehaviorCache"/>
    <facet facetKey="ListSubscriptions"/>
    </facets>
    <GetOperationTimeout>0.00:00:05</GetOperationTimeout>
    </add>
    </providers>
    </dataAdapterManager>

xDB Interactions

An interaction describes any point at which a contact interfaces with a brand, either online or offline. Examples of interactions include:

Purchasing a something from a physical store
Using an app
Browsing a website
A phone conversation

Here is a useful query that can be used to return all interactions for a contact

DECLARE @ContactId varchar(max) = 'E65F09D3-BF06-0000-0000-05ABFFFB0D0F'

SELECT *
FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[Interactions]
WHERE ContactId = @ContactId

Results in my environment.

Take note of a few fields

ChannelId – e.g. webinars, e-mail campaigns, or a face-to-face conversation at a trade show
Initiator – if the interaction was initiated by the contact or brand e.g. making a delivery or outbound call
VenueId – Id of the offline venue where the interaction occurred
CampaignId – Id of the campaign that caused the interaction e.g. via email or social or other campaign.
EngagementValue – the total engagement value for the interaction
Events – is a JSON representation of all the events/goals/outcomes triggered during the interaction. Here is a snippet from the JSON showing events and goals

xDB Interaction Facets

An interaction facet enriches the interaction by describing the entire interaction, not an event that occurred within and interaction. Events are kept on the interaction record as described above. For example, if a contact visits a particular shoe store, interaction facets might include:

Means of transportation to store
Active store offers at time of visit
Weather during visit
ID of manager on duty during visit

Here is a query you can use:

DECLARE @ContactId varchar(max) = 'E65F09D3-BF06-0000-0000-05ABFFFB0D0F'

SELECT *
FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[InteractionFacets]
WHERE ContactId = @ContactId

Results from the query in my environment:

Summary

As you can see, there is a substantial amount of data be stored in a very logical manner which makes it easy to understand and potentially extend. We also briefly covered understanding which facets are loaded into the session during a visit which can be used for personalisation or email campaigns, etc. In the next couple of posts I hope to cover some examples on how to extend and/or use some what we have covered here.

Appendix 1 of 2 – SQL query to pull the above together

    DECLARE @EmailAddress varchar(max) = 'magic@sitecore.net';

    /* List xDB contact email addresses */
    SELECT *
    FROM (
    SELECT *,CONVERT(VARCHAR(MAX), [Identifier]) as DecodedIdentifier, 'Shard0' as Shard
    FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[ContactIdentifiers]
    UNION ALL
    SELECT *,CONVERT(VARCHAR(MAX), [Identifier]) as DecodedIdentifier, 'Shard1' as Shard
    FROM [habitathome_Xdb.Collection.Shard1].[xdb_collection].[ContactIdentifiers]
    ) ContactIdentifiers_Unioned
    WHERE Source = 'CommerceUser'

    /* Get a contact id for an email address */
    DECLARE @ContactId varchar(max);
    SELECT @ContactId = ContactId
    FROM (
    SELECT *,CONVERT(VARCHAR(MAX), [Identifier]) as DecodedIdentifier, 'Shard0' as Shard
    FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[ContactIdentifiers]
    UNION ALL
    SELECT *,CONVERT(VARCHAR(MAX), [Identifier]) as DecodedIdentifier, 'Shard1' as Shard
    FROM [habitathome_Xdb.Collection.Shard1].[xdb_collection].[ContactIdentifiers]
    ) ContactIdentifiers_Unioned
    WHERE DecodedIdentifier like '%' +@EmailAddress + '%'
    PRINT 'Contact Id ' + @ContactId + ' for email identifier ' + @EmailAddress

    /* List contacts - contact facets */
    SELECT *
    FROM (	SELECT 'Shard0' as Shard, * FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
    UNION ALL
    SELECT 'Shard1' as Shard, * FROM [habitathome_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets]
    ) tbl
    WHERE ContactId = @ContactId

    /* List contacts interactions */
    SELECT *
    FROM (	SELECT 'Shard0' as Shard, * FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[Interactions]
    UNION ALL
    SELECT 'Shard1' as Shard, * FROM [habitathome_Xdb.Collection.Shard1].[xdb_collection].[Interactions]
    ) tbl
    WHERE ContactId = @ContactId

    /* List contacts - interaction facets */
    SELECT *
    FROM (	SELECT 'Shard0' as Shard, * FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[InteractionFacets]
    UNION ALL
    SELECT 'Shard1' as Shard, * FROM [habitathome_Xdb.Collection.Shard1].[xdb_collection].[InteractionFacets]
    ) tbl
    WHERE ContactId = @ContactId

Appendix 2 of 2 – Using SQL Server Profiler

A good first step in understanding any SQL Server database is turning on SQL Server Profiler. For me this is the configuration I like to use for xDB:

I tend to enable all columns, more so out of habit, and move DatabaseName and LoginName next to TextData

Filter to the two xDB shards – explained a bit later in the post.

Filter out some clutter from the profiling which we are not interested in.

Kaz on Commerce!