1

Sitecore 9.1

When I manually add an email address to a list, I am wondering where the list and xDB contact are associated. Looking at the code, it looks like it would be a contact facet. But when I pull the facets for my contact, nothing sticks out. Any idea where the list is assigned to the contact or the the contact is assigned to the list. I was the understanding it all happens on the xDB contact.

Update

It looks like the connection is in the contact facets as a facet named ListSubscriptions. But I have not been able to tie one back to a known contact yet.

SELECT TOP (1000) [ContactId]
      ,[FacetKey]
      ,[LastModified]
      ,[ConcurrencyToken]
      ,[FacetData]
  FROM [sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
  WHERE FacetKey = 'ListSubscriptions'
  • Which version of Sitecore? – Peter Procházka Aug 13 at 15:54
  • Updated the OP with an update. Its 9.1 – Chris Auer Aug 13 at 16:37
3

Thanks to Pete Navarra, I was actually only searching one shard for the contact ID, for the user. In the end it was right where he thought it was. As a ListSubscriptions facet on the contact.

To get it you can use this SQL

// shard 0
SELECT TOP (1000) [ContactId]
      ,[FacetKey]
      ,[LastModified]
      ,[ConcurrencyToken]
      ,[FacetData]
  FROM [sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
  WHERE FacetKey = 'ListSubscriptions'
    AND ContactId = '5C722EA3-A86C-0000-0000-0596BE30D17D'

// shard 1
SELECT TOP (1000) [ContactId]
      ,[FacetKey]
      ,[LastModified]
      ,[ConcurrencyToken]
      ,[FacetData]
  FROM [sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets]
  WHERE FacetKey = 'ListSubscriptions'
    AND ContactId = '5C722EA3-A86C-0000-0000-0596BE30D17D'

This will output a facet that has the list ID in it.

{
   "@odata.type":"#Sitecore.XConnect.Collection.Model.ListSubscriptions",
   "Subscriptions":[
      {
         "Added":"2019-07-25T17:31:32.0116076Z",
         "IsActive":true,
         "ListDefinitionId":"a6ee7af9-7e3b-48af-beea-cfd3ce4526f9",
         "SourceDefinitionId":"eef0f298-b272-4e1b-b9e5-c0a7fc529b55"
      }
   ]
}

And that translates to this url in the sitecore list manager that fills the list of emails in the current list.

https://mysite/sitecore/api/lists/a6ee7af9-7e3b-48af-beea-cfd3ce4526f9/contacts?pageSize=20&pageIndex=0&payLoad=full&language=en&filter=&sc_lang=en

1

To map email to known contact you should assign existing identifier source that is used for email identifier in your solution. enter image description here

You can find more information here: https://doc.sitecore.com/users/92/sitecore-experience-platform/en/add-a-contact-to-a-list.html.

For querying all contacts lists from xDb you can also use query:

SELECT ContactId, ListId, SourceId, IsActive, Added
  FROM [sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]

  CROSS  APPLY OPENJSON (FacetData, N'$.Subscriptions')
   with (
       ListId nvarchar(max) N'$.ListDefinitionId',
        SourceId nvarchar(max) N'$.SourceDefinitionId',
        IsActive bit N'$.IsActive',
        Added  nvarchar(max) N'$.Added'
    )
    where FacetKey = 'ListSubscriptions'

UNION  

SELECT ContactId, ListId, SourceId, IsActive, Added
  FROM [sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets]

  CROSS  APPLY OPENJSON (FacetData, N'$.Subscriptions')
   with (
       ListId nvarchar(max) N'$.ListDefinitionId',
        SourceId nvarchar(max) N'$.SourceDefinitionId',
        IsActive bit N'$.IsActive',
        Added  nvarchar(max) N'$.Added'
    )
    where FacetKey = 'ListSubscriptions'

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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