Ever wondered how to find the xDB Contact that was created for a user that visited your site ? No ? Well that’s too bad because I’m going to tell you anyway.

When a user visits a Sitecore site that has tracking enabled, a cookie named SC_ANALYTICS_GLOBAL_COOKIE is created in the response:

The key bit here is the string of hex digits “7828c8717c3d4594be641d919ae2af8c” (but not the |True or |False suffix which is related to robot detection).

This value represents a Device Profile ID in Sitecore which identifies the device from which the user browsed the site. This information gets added to their session and remains there until the session is flushed to xConnect/xDB on expiry/timeout.

Sitecore uses a Device Profile Id rather than a Contact Id because users (contacts) can visit from more than one device and, if we are able to “identify” them in Sitecore (i.e. link a browser session to a specific xDB Contact with some degree of certainty), then the interactions that occurred on different Device Profiles can be merged into the Contact data in xDB. This means that a user’s actions from different sessions and devices can be linked, providing a broader view of a Contact’s interactions and behaviour over time.

So, how to find the Contact record? First, you will need to wait until the session expires and the data is flushed to xDB…

If this is a bit tedious then (outside of production environments anyway) you can change the session timeout in the Web.config to a much shorter period so that the data flows through more quickly.

To check if the data has arrived in xDB, run the following query in xDB:

/* Retrieve all device profiles from shards */

SELECT DeviceProfileId, LastKnownContactId
FROM [Xdb.Collection.Shard1].[xdb_collection].[DeviceProfiles]
WHERE DeviceProfileId = cast('7828c871-7c3d-4594-be64-1d919ae2af8c' as uniqueidentifier)

  UNION ALL

SELECT DeviceProfileId, LastKnownContactId
FROM [Xdb.Collection.Shard0].[xdb_collection].[DeviceProfiles]
WHERE DeviceProfileId = cast('7828c871-7c3d-4594-be64-1d919ae2af8c' as uniqueidentifier)

This query will return the last known xDB Contact Id for that Device Profile. Note that the ID needs to be converted to a string representation of a GUID format (8-4-4-4-12 digits) and cast to a UniqueIdentifier.

If the session has timed out and data is flowing correctly into xDB then this query should return at least one record:

Which contains the last known Contact Id for that Device Profile. In this case “37E8135C-4953-0000-0000-05FE63C6DB57”.

From there it’s a simple matter to retrieve Contact and Facet (or other) data via the Contact Id:

SELECT c.ContactId, FacetKey, FacetData
FROM [Xdb.Collection.Shard1].[xdb_collection].[Contacts] c
     INNER JOIN [ma-demo._Xdb.Collection.Shard1].[xdb_collection].[ContactFacets] cf
     ON c.ContactId = cf.ContactId
WHERE c.ContactId = '37E8135C-4953-0000-0000-05FE63C6DB57'

    UNION ALL
 
SELECT c.ContactId, FacetKey, FacetData
FROM [Xdb.Collection.Shard0].[xdb_collection].[Contacts] c
     INNER JOIN [ma-demo._Xdb.Collection.Shard0].[xdb_collection].[ContactFacets] cf
     ON c.ContactId = cf.ContactId
WHERE c.ContactId = '37E8135C-4953-0000-0000-05FE63C6DB57'

In a follow up post I’ll look at how to get contact Identifiers out of xDB.

To clean out unwanted contact data from your collection databases, there are some referential integrity constraints that mean the deletions have to happen in a specific order. The following query will delete all contacts, facets, interactions, interaction facets, and contact identifiers from xDB.

  delete
  FROM [xdb_collection].[ContactIdentifiers]
  
  delete
  FROM [xdb_collection].[ContactIdentifiersIndex]

  delete
  FROM [xdb_collection].[ContactFacets]
    
  delete
  FROM [xdb_collection].[InteractionFacets]
  
  delete
  FROM [xdb_collection].[Interactions]

  delete
  FROM [xdb_collection].[Contacts]

You will need to run this on both Collection shards.