XConnect: Get contacts with at least 1 interaction between dates

Question

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!

Answer 1

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