2

In a Sitecore 9.3 XP implementation, using XConnect, while trying to retrieve the contacts with at least 1 interaction between a period of time, the number of contacts is not matching what I see when compared to a SQL query.

XConnect query:

var contactsWithinDateXConnect = new List<Guid>();
var startDate = DateTime.Parse("2022-03-01");
var endDate = DateTime.Parse("2022-05-31");

using (XConnectClient client = Sitecore.XConnect.Client.Configuration.SitecoreXConnectClientConfiguration.GetClient())
{
    var myQuery = client.Contacts.Where(c =>
        c.Interactions.Any(i =>
            i.StartDateTime >= startDate.ToUniversalTime() 
            && i.EndDateTime <= endDate.ToUniversalTime()
        )
    );

    var myResults = myQuery.GetBatchEnumeratorSync(1000);
    while (myResults.MoveNext())
    {
        foreach (var contact in myResults.Current)
        {
            var contactId = contact.Id.Value;
            if (!contactsWithinDateXConnect.Any(p => p == contactId))
                contactsWithinDateXConnect.Add(contactId);
        }
    }
}

This will result in contactsWithinDateXConnect having only 2 contacts.

However, if I use a SQL query to retrieve the contacts from the Shard databases directly, the result will have much more contacts (45).

SELECT DISTINCT [ContactId]
FROM [Sitecore_Xdb.Collection.Shard0].[xdb_collection].[Interactions]
WHERE [StartDateTime] >= '2022-03-01' AND [StartDateTime] <= '2023-05-31'

UNION

SELECT DISTINCT [ContactId]
FROM [Sitecore_Xdb.Collection.Shard1].[xdb_collection].[Interactions]
WHERE [StartDateTime] >= '2022-03-01' AND [StartDateTime] <= '2023-05-31'

Indeed the 2 contacts found by XConnect are present among the 45 contacts found with SQL, however, it looks like XConnect is missing lots of them.

Any clues on why that happens, steps to debug & potential fixes are welcome!

3

1 Answer 1

2

When retrieving contacts from XConnect, the sitecore API uses the xDB search index. By default the index is set to not include anonymous contacts.

What I suspect is happening in your example is that 43 of your contacts are anonymous, and only 2 contacts have been identified and have Isknown flag set to true.

If you join the result of your sql statement to the contats, then contactsidentifiers table you should be able to see the number of known contacts.

Alternatively, if in test environment you could update settings in xconnect to index anonymous. To do this:

Go to sc.Xdb.Collection.IndexerSettings.xml file on search indexer and update:

<IndexAnonymousContactData>true</IndexAnonymousContactData>

Then rebuild the index.

More info here https://doc.sitecore.com/xp/en/developers/91/sitecore-experience-platform/enable-indexing-of-anonymous-contacts-in-the-xdb-index.html

3
  • This is exactly what's happening. Thanks! Would it be a way to "bypass" the index and get anonymous contacts without indexing them to the search index? I assume it's not, but worth asking, Jun 20, 2023 at 17:04
  • Not as far as im aware. I thnk it would definitely be a useful feature if it was possible. Jun 20, 2023 at 18:31
  • Dean, I discovered if you use the SQL queries to retrieve the IDs, then you can still use the contactIds taken from SQL to get the contact using xConnect - the contact will be found normally. Jun 28, 2023 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.