Find all interactions where goal of ID d2bd86b2-8de8-4af4-8893-2e36cf0ff51c
select *
from [xdb_collection].[Interactions] I
CROSS APPLY OPENJSON(I.Events) WITH (
ODataType nvarchar(100) '$."@odata.type"',
DefinitionId nvarchar(100) '$.DefinitionId'
) j
WHERE j.ODataType = '#Sitecore.XConnect.Goal'
AND DefinitionId = 'd2bd86b2-8de8-4af4-8893-2e36cf0ff51c'
Same as above but different:
Find all interactions where goal of ID d2bd86b2-8de8-4af4-8893-2e36cf0ff51c
SELECT *
FROM [xdb_collection].[Interactions] I
WHERE EXISTS (
SELECT 1
FROM OPENJSON([events]) WITH (
[@odata.type] NVARCHAR(MAX),
[DefinitionId] NVARCHAR(MAX)
)
WHERE [@odata.type] = '#Sitecore.XConnect.Goal'
AND DefinitionId = 'd2bd86b2-8de8-4af4-8893-2e36cf0ff51c'
)
Get all contact Id’s for a certain goal after a certain date
select ContactId
from [xdb_collection].[Interactions] I
CROSS APPLY OPENJSON(I.Events) WITH (
ODataType nvarchar(100) '$."@odata.type"',
DefinitionId nvarchar(100) '$.DefinitionId'
) j
WHERE j.ODataType = '#Sitecore.XConnect.Goal'
AND DefinitionId = 'c0bc91b9-b5fc-4faa-bc12-3dba8bbae44f'
AND StartDateTime > '2019-11-1'
Group By ContactId
Get email address identifiers
SELECT CONVERT(VARCHAR(MAX), [Identifier]) as DecodedIdentifier
FROM [xdb_collection].[ContactIdentifiers]
WHERE [Source] = 'emailaddress'
Find contact by preferred email address (NOT Identifier)
SELECT *
FROM [xdb_collection].[ContactFacets] i
CROSS APPLY OPENJSON(i.FacetData) WITH (
email nvarchar(100) '$.PreferredEmail.SmtpAddress'
-- and additional columns definitions
) j
WHERE j.email = '[email protected]'
Find duplicate emails
SELECT email, count(*) as counts
FROM [xdb_collection].[ContactFacets] i
CROSS APPLY OPENJSON(i.FacetData) WITH (
email nvarchar(100) '$.PreferredEmail.SmtpAddress'
-- and additional columns definitions
) j
WHERE email != ''
Group By email
Order by counts DESC
Find duplicate emails for for one type of identified contact
SELECT email, count(*) as counts
FROM [xdb_collection].[ContactFacets] i
CROSS APPLY OPENJSON(i.FacetData) WITH (
email nvarchar(100) '$.PreferredEmail.SmtpAddress'
-- and additional columns definitions
) j
WHERE email != ''
AND i.ContactId IN
(SELECT [ContactId]
FROM [xdb_collection].[ContactIdentifiers]
WHERE Source = 'emailaddress')
Group By email
Order by counts DESC
Get all facet possible values in the DB
SELECT MembershipStatus
FROM [xdb_collection].[ContactFacets] i
CROSS APPLY OPENJSON(i.FacetData) WITH (
MembershipStatus nvarchar(100) '$.Registration.MembershipStatus'
-- and additional columns definitions
) j
WHERE FacetKey = 'Membership'
AND MembershipStatus != ''
Group By MembershipStatus
Get contacts by facet value
SELECT ContactId, MembershipStatus
FROM [xdb_collection].[ContactFacets] i
CROSS APPLY OPENJSON(i.FacetData) WITH (
MembershipStatus nvarchar(100) '$.Registration.MembershipStatus'
-- and additional columns definitions
) j
WHERE FacetKey = 'Membership'
AND MembershipStatus = 'Expired'
Get contacts with duplicate emails (case insensitive)
SELECT TOP (1000) [ContactId]
, Count(*) as cnt
FROM [xdb_collection].[ContactIdentifiers]
Where Source = 'emailaddress'
Group By ContactId
ORDER By cnt DESC
Find contacts who matched a pattern
select TOP 20 *
from [xdb_collection].[ContactFacets] f
CROSS APPLY OPENJSON(f.FacetData, '$.Scores') WITH (
MatchedPatternId nvarchar(100) '$.Value.MatchedPatternId'
) j
WHERE j.MatchedPatternId = '691f6492-91df-4e1e-b4ca-d3371fe2a939';
Find matched patterns in a specific profile
select TOP 20 *
from [xdb_collection].[ContactFacets] f
CROSS APPLY OPENJSON(f.FacetData, '$.Scores') WITH (
ProfileDefinitionId nvarchar(100) '$.Value.ProfileDefinitionId',
MatchedPatternId nvarchar(100) '$.Value.MatchedPatternId'
) j
WHERE j.ProfileDefinitionId = '2d495b84-0453-42e8-9b46-6a0a0d8bf966'
AND j.MatchedPatternId != ''
All profile/patterns that were not triggered by goals.
select I.ContactId
from [xdb_collection].[Interactions] I
CROSS APPLY OPENJSON(I.Events) WITH (
ODataType nvarchar(100) '$."@odata.type"',
DefinitionId nvarchar(100) '$.DefinitionId'
) j
WHERE Created > '3/10/20'
AND j.ODataType = '#Sitecore.XConnect.Goal'
AND ( DefinitionId = 'f5a607b9-501d-409c-9b49-b3df784fdda2' OR
DefinitionId = 'e502291b-5f75-4906-98af-31588d707007' )
AND I.ContactId NOT IN
(
select f.ContactId
from [xdb_collection].[ContactFacets] f
CROSS APPLY OPENJSON(f.FacetData, '$.Scores') WITH (
ProfileDefinitionId nvarchar(100) '$.Value.ProfileDefinitionId',
MatchedPatternId nvarchar(100) '$.Value.MatchedPatternId'
) j
WHERE FacetKey = 'ContactBehaviorProfile'
AND f.LastModified > '3/10/20'
AND j.ProfileDefinitionId = '2d495b84-0453-42e8-9b46-6a0a0d8bf966'
AND j.MatchedPatternId != ''
)
select I.ContactId
from [xdb_collection].[Interactions] I
CROSS APPLY OPENJSON(I.Events) WITH (
ODataType nvarchar(100) '$."@odata.type"',
DefinitionId nvarchar(100) '$.DefinitionId'
) j
WHERE Created > '3/10/20'
AND j.ODataType = '#Sitecore.XConnect.Goal'
AND ( DefinitionId = 'f5a607b9-501d-409c-9b49-b3df784fdda2' OR
DefinitionId = 'e502291b-5f75-4906-98af-31588d707007' )
Find contacts with an array as input
SELECT * FROM [xdb_collection].[ContactFacets] f
WHERE FacetKey = 'Classification'
AND ContactId IN (
'201F73EA-B3CB-0000-0000-05ADF3B67724',
'0ED50200-FF63-0000-0000-05CBCCA5A599',
'0ED50200-FF63-0000-0000-05CBCCA5A599',
'644FBE44-B92D-0000-0000-05A2A9560976',
'26EF19A4-5F98-3200-0000-05B5BA6E94E5',
'615DEA04-35B9-0000-0000-05CB66CC27E2',
'615DEA04-35B9-0000-0000-05CB69FFF0FD',
'615DEA04-35B9-0000-0000-05CB6AE2C01E',
'615DEA04-35B9-0000-0000-05CB6AED52B3',
'615DEA04-35B9-0000-0000-05CB6DE98E70',
'615DEA04-35B9-0000-0000-05CB71717199',
'26EF19A4-5F98-3600-0000-05B5B619C632',
'615DEA04-35B9-0000-0000-05CB7B64E843',
'26EF19A4-5F98-1200-0000-05B5A73D57E9',
'26EF19A4-5F98-2B00-0000-05B5BA3D92A3',
'26EF19A4-5F98-3200-0000-05B5BA6E94E5',
'EE963529-8343-0000-0000-058AF6FD87D4',
'0A37441D-B16A-0000-0000-05C8834DB8B8'
)
Find contacts with no identifiers
SELECT [ContactId]
FROM [xdb_collection].[Contacts] c
WHERE NOT EXISTS
(
SELECT [ContactId]
FROM [xdb_collection].[ContactIdentifiers] i
WHERE i.ContactId = c.ContactId
)