Troubleshooting Sitecore content tests

Sitecore Experience Optimization is a powerful tool that allows running A/B and multi-variant tests to help content and marketing teams improve their website content and overall customer experience. However, a fully-scaled Sitecore XP has many parts that are involved in test data processing and aggregation. I met teams who had set up their A/B tests in Sitecore but struggled to see them working or couldn't get any reports.

In this post I will go through the test lifetime from creation to reporting to help you identify which part of the system is not working as expected.

---

Is my A/B test working?

The first step is to check that the test works and visitors can see different experiences. This can be done by reloading the test page a few times in an incognito browser window or by clearing cookies between attempts. Eventually you should see an alternative version of the page or component.

If it does not happen and you always see the default variant, it means that Sitecore XP features are not configured properly or the Content Delivery server does not see the test definition in web database.

1. The first and obvious thing to check is that these three settings are enabled in Sitecore config on the Content Delivery server: xDB.Enabled, xDB.Tracking.Enabled and ContentTesting.Enabled.
   ContentTesting.Enabled setting alone is not enough because Sitecore checks xDB.Enabled and xDB.Tracking.Enabled behind the scenes to determine whether content tests should run or not.

2. Check that you have SC_ANALYTICS_GLOBAL cookie in the browser. Cookie consent modules and custom code can block this cookie if there is no consent given.
   Another possible reason for missing analytics cookie can be lack of @Html.Sitecore().VisitorIdentification() tag in the website layout file.

3. Make sure that your page with the test and all relevant data sources are published to the Web database. If you are not sure, try republishing the page with its related items (child items are not required).

4. Another important element is the test definition item, it should be located in the folder /sitecore/system/Marketing Control Panel/Test Lab, have the workflow state Deployed and published to the Web database too.

5. When in doubt, it is always good to check Content Delivery server logs, never underestimate this step!

Is test data being saved to xDB?

If you can see test experiences, the next step is making sure that your visits are recorded correctly in the xDB on session end. You will need access to the Shard DBs via SQL Management Studio or Azure Query Editor.

Use the script below to check recent entries in the Interaction table of Shard DBs (if you have multiple shards, remember to check each of them!).

USE [database_name] -- replace with your Shard DB name, you may need to check all your Shard DBs
GO

SELECT TOP(100) * --always limit number of results, especially if you Shard DB is large
FROM [xdb_collection].[Interactions]
WHERE ContactId = '0E3CC254-C440-0000-0000-062C9FE15922' -- replace with your contact ID if it is known
AND StartDateTime > '2022-01-01 12:00' -- replace with your date and time, keep the time range as small as possible

-- be careful when adding more fields to the WHERE condition because Interactions table has only 2 indexes and searching for large number of interactions can be slow

Your interaction should contain the special event called MVTestTriggered in the Events column:

You can also try checking that the contact has the facet TestCombinations:

USE [database_name] -- replace with your Shard DB name, you may need to check all your Shard DB
GO

SELECT *
FROM [xdb_collection].[ContactFacets]
WHERE ContactId = '0E3CC254-C440-0000-0000-062C9FE15922' -- replace with your contact ID
AND FacetKey = 'TestCombinations'

There should be a row with data like this in the FacetData column:

If you don’t see this data in any of the Shard databases, the problem is between the CD server, xConnect collection service and xDB.

1. Make sure there are no files in the folder /App_Data/SubmitQueue on CD server. If there are any, it means that CD collected tracking information but had problems pushing sessions to the xConnect collection.

2. Have you already checked your CD server logs? 😊

If xConnect collection service returns an error or unavailable, it will be written to the CD log. I will not go into every single scenario here because most of these errors have already been discovered and solved by the community. Here are some of the articles that helped me to resolve similar issues in the past:

• Troubleshooting xConnect certificate issues
• Troubleshooting xDB data issues
• Sitecore 9 - XConnect - Not working, having error - HTTP Error 403.16 - Forbidden -Your client certificate is either not trusted or is invalid
• Sitecore XConnect Xdb Collection Unavailable Exception, urgh!
• Fix xConnect Certificate Errors
• Sitecore XConnect certificate error – HTTP response was not successful: Forbidden
• Debugging Sitecore 9 Analytics Issues

Is xDB data being aggregated?

Moving on, if you see raw data in the Interactions table correctly, the next step is finding this data in the Reporting database. The following tables will be of interest to us:

• Fact_MvTesting
• Fact_TestConversions
• Fact_TestPageClicks

USE [database_name] -- replace with your Reporting DB nam
GO

-- [Fact_MvTesting] contains aggregated number of visits, engagement value, bounces, etc. for each test variant
-- TestSetId is ID of test definition item and TestValues represent each of the test variants
SELECT *
FROM [dbo].[Fact_MvTesting]
WHERE TestSetId = 'DB071F98-C2CE-4DBB-8873-2E378F88CEB4' -- replace with your test ID

-- [Fact_TestConversions] stores aggregated number of triggered goals, associated visits and engagrement value
-- counted for each test variant and goal ID
SELECT *
FROM [dbo].[Fact_TestConversions]
WHERE TestSetId = 'DB071F98-C2CE-4DBB-8873-2E378F88CEB4' -- replace with your test ID
AND GoalId = '3430F877-4767-4B2E-A121-FDA73122FEFB' -- optionally you can filter by a specific goal ID

-- [Fact_TestPageClicks] represents aggregated number of page views
-- counted for each test variant and page ID
SELECT *
FROM [dbo].[Fact_TestPageClicks]
WHERE TestSetId = 'DB071F98-C2CE-4DBB-8873-2E378F88CEB4' -- replace with your test ID
AND ItemId = 'D8771EF4-47D5-4EDA-9F96-0785207AA052' -- optionally you can filter by a page ID

If there is no data in these tables or data is too old, it’s time to look at the Processing role.

1. Check log files on your Processing server.

2. Ensure that xDB.Enabled, xDB.Tracking.Enabled and ContentTesting.Enabled are set to true on the Processing server. If they are not, the Processing server will not aggregate test data correctly.

3. Review server resource consumption on the Processing server and databases ReferenceData, Shards and Reporting. If the server or databases are maxing out, you will see delays in aggregated data appearing in the Reporting DB.

Is aggregated data being returned correctly?

Starting from Sitecore 10.1 the xDB Reporting role has been combined with the Content Management role to reduce hosting costs. So if you have version 10 or lower, check your Content Management and xDB Reporting roles, especially connectivity between them.

For Sitecore 10.1 and higher, it must be an issue with the Content Management role or its connection to the Reporting DB.

---

Hopefully this article shed some light on how the A/B test data transforms and flows between different parts of Sitecore platform and which things to check when troubleshooting A/B tests.

All scripts from this article are saved in the GitHub repository https://github.com/geann/troubleshooting-sitecore-content-tests so you can save them for future use.

geann / troubleshooting-sitecore-content-tests

Scripts for troubleshooting Sitecore content tests

Scripts for troubleshooting Sitecore content tests

This repository contains SQL scripts used in the article Troubleshooting Sitecore content tests:

1. Interactions.sql - this script checks raw interactions data in Shard databases with ability to filter by a date and/or contact ID.
2. Contact facets.sql - checks if a contact contains a special facet with A/B test combinations, i.e. validates the fact that this contact participated in an A/B test. Results can be filtered by contact ID.
3. Reporting.sql - checks test results in the Reporting database for specific test ID:
   • overall test statistics
   • test conversions for specific goal
   • test page clicks

Screeenshots with example data are saved for reference to the folder /screenshots.

View on GitHub