Troubleshooting xDB data issues


Description

This article describes the processes for troubleshooting the most frequent issues with Analytics data in the Sitecore Experience Database installation.

To use the troubleshooting instructions in this article, perform the test described in the Verify a connection to the collection database document and then choose one of the following scenarios according to the results:

Scenario 1. Data Is Not Collected In The Collection Database

Description

Contact or interaction data does not appear in the collection database after a contact visits the website.
Note that data does not appear in the collection database until after the session has timed out.
Refer to the How to check interactions stored in the collection database section to check if the new data is saved to the database.

To troubleshoot when data is not collected in the collection database:

  1. For Sitecore XP 8.0.0 or higher, check the Internet connection and network-related restrictions on the MongoDB server. Ensure that the firewall allows you to connect to the MongoDB server.
    For Sitecore XP 9.0.0 or higher, make sure that xConnect services are accessible from CD servers.
  2. Ensure that both private and shared session states are configured according to the session state configuration scenarios.
  3. Ensure that no CDN/cache services, such as Akamai, are running. All requests must be processed by the Sitecore instance to ensure that Sitecore Analytics tracks them.
  4. Using a non-Sitecore session state provider for any private or shared session states requires you to ensure that the service supports the Session_End ASP.NET event.
  5. Check whether the Global.asax file exists in the Website folder. If not, copy the default Global.asax file to the Website folder from the appropriate Sitecore installation archive.
  6. If the custom application class is defined in the Global.asax file, ensure that this class is derived from the standard Sitecore.Web.Application class and does not override any standard logic.
  7. Ensure that all the layout pages contain the <sc:VisitorIdentification runat="server" /> tag (@Html.Sitecore().VisitorIdentification() helper for MVC websites) in the <head> section.
  8. Ensure that the ...\layouts\system path or files at this path are not added to the <setting name="IgnoreUrlPrefixes"...> setting in the Web.config file.
  9. When using the Sitecore MVC, ensure that the Sitecore.MvcAnalytics.config file is not disabled.
  10. If you have a multiserver xDB installation, ensure that all the servers are configured according to the configuration documentation.

Scenario 2. Experience Analytics Data Is Out Of Sync Or Missing

Description

Experience Analytics does not show any data. The fact and dimension tables (Reporting database, SQL Server) do not populate with new Analytics data.

Known issues

To troubleshoot when Experience Analytics data is out of sync or missing:

  1. Ensure that the Interactions collection of the collection database is populated with new interactions. Refer to the How to check interactions stored in the collection database section to check if new interactions are collected. If new interactions are not added to the collection database, have a look at Scenario 1 of this article.
  2. The Reference Data Service is accessible from CM.
  3. Check the Internet connection and network-related restrictions on the reporting SQL database. Ensure that the firewall allows you to connect to the SQL server instance.
  4. For Sitecore XP 8.0.0-8.2.7, ensure that the Segment table of the reporting SQL database is not empty. If this table is empty, populate it with data from a clean Sitecore reporting database or redeploy reporting definitions through the /sitecore/admin/RedeployMarketingData.aspx administrative page.
  5. Check and analyze how many records are in the processing pool by running the sequence of the same queries within a certain time limit. The increasing number of records signals the need for adjusted aggregation speed.

    • For Sitecore XP 9.0.0 or higher, run the following query against the Processing.Pools database :
      SELECT COUNT(*) FROM [xdb_processing_pools].[InteractionLiveProcessingPool]
      Click here to read more about processing performance optimizations.

    • For Sitecore XP 8.0.0 or higher, run the following query against the tracking_live mongo database:
      db.getCollection('ProcessingPool').find({}).count()
      Click here to read more about configuration adjustments.

Notes:

Scenario 3. Experience Profile Data Is Out Of Sync Or Missing

Description

To troubleshoot when Experience Profile data is out of sync or missing:

Notes:

Scenario 4. Path Analyzer Data Is Out Of Sync Or Missing

Scenario

To troubleshoot when Path Analyzer data is out of sync or missing:

How To Check Interactions Stored In The Collection Database

To check that interactions appear correctly in the collection database, for all mentioned releases:

  1. Start InPrivate browsing in Firefox or Chrome and visit a few web pages on the site.
  2. Find the SC_ANALYTICS_GLOBAL_COOKIE cookie and save the value preceding the pipe. For example, retrieve the value "169e920af41d4960ad9eff4d3260f8d9" from the following cookie "169e920af41d4960ad9eff4d3260f8d9|True".
  3. Request the Abandon.aspx page to flush the data immediately or close the window and wait for the end of the session.
  4. Consider one of the following options:
    • For Sitecore XP 8.0.0 or higher, connect to the analytics database and run the following query:
      db.getCollection('Interactions').find({ContactId: NUUID("169e920a-f41d-4960-ad9e-ff4d3260f8d9")})
      //id retrieved from Cookie should go here. Add dashes to make the value correct GUID.
    • For Sitecore XP 9.0.0 or higher, when SQL provider is used, connect to shard databases and run the following query against each of them:
      SELECT LastKnownContactId  FROM [xdb_collection].[DeviceProfiles]
      WHERE DeviceProfileId = '169e920a-f41d-4960-ad9e-ff4d3260f8d9'
      --id retrieved from Cookie should go here. Add dashes to make the value correct GUID.
      One of the databases must contain the LastKnownContactId. Use it to retrieve the interaction (it will be in one of the shard databases):
      SELECT * FROM [xdb_collection].[Interactions] 
      WHERE ContactId = '138E219A-0A5A-0000-0000-05D0EB074C6D'
      -- where 138E219A-0A5A-0000-0000-05D0EB074C6D is the copied LastKnownContactId value.
    • For Sitecore XP 9.0.2 or higher, when MongoDB provider is used, connect to the collection MongoDB database and execute the following query:
      db.getCollection('DeviceProfiles').find({"_id.id":UUID("d2dae1fa-25b7-4948-a426-863115d8af2c")})
      //id retrieved from Cookie should go here. Add dashes to make the value correct GUID.
      Copy lk_contact_id field value and run the following query:
      db.getCollection('Interactions').find({"_id.parent_id":UUID("5e84dc4c-48fa-4b38-bf7c-9e8e1deb0f78")})
      //where UUID("5e84dc4c-48fa-4b38-bf7c-9e8e1deb0f78") is the copied lk_contact_id value.