There is not much structure to be found in this post, I will mainly use it as a location to keep track of useful xDB troubleshooting tips and tricks.
How does all my xDB data flow and how to pinpoint/analyze issues:
-
To check what user you are within xDB you will need to have the value of your SC_ANALYTICS_GLOBAL_COOKIE. Copy the value without ‘| False’.
-
To force a Session_End you can upload the following .aspx file to a location on your website: “here - Now browse through your website(s), and end the session by hitting the sessionkill.aspx. This should trigger the the xDB data to be send towards your SQL shards.
-
Connect to your SQL server and execute the query below on every shard:
SELECT *
FROM [xdb_collection].[DeviceProfiles]
where DeviceProfileId like ‘%VALUEFROMCOOKIE%’
order by LastModified desc
-
The ID that is found in column “LastKnownContactId” – will map a new “ContactId” - “XXX” - This ID will be used for identification, for example in the Interaction table.
-
To check whether page events are written to the “interaction” tables
SELECT * from
SELECT * from [xdb_collection] .[Interactions]
where ContactId = ‘XXX’
- In case you are missing interactions, you might want to check Application Insights whether there are Session_End submits that are failing [KQL] :
traces
| where timestamp > ago(30d)
| where operation_Name contains “GET /SESSION END”
Extracting Email addresses from xDB shards:
select c.contactid, c.created, c.LastModified, cf.FacetData, JSON_VALUE(cf.FacetData, ‘$.PreferredEmail.SmtpAddress’) AS Email
from [db-shard0db].[xdb_collection].[ContactFacets] cf
inner join [db-shard0db].[xdb_collection].[Contacts] c on c.ContactId = cf.ContactId
where cf.FacetKey = ‘Emails’
How many visits by language and month, run against your reporting database:
select SiteNameId,
DimensionKey,
DATEPART(year,[Date]) as ‘Year’,
DATEPART(month,[Date]) as ‘Month’,
sum(visits) as ‘NumberOfInteractions’
from [dbo].[Fact_LanguageMetrics] lm
inner join [dbo].[DimensionKeys] dk on lm.DimensionKeyId = dk.DimensionKeyId
group by SiteNameId,
DimensionKey,
DATEPART(year,[Date]),
DATEPART(month,[Date])
order by SiteNameId,
DimensionKey,
DATEPART(year,[Date]),
DATEPART(month,[Date])
How many interactions for each facet by month, run against your xDB shards:
select facetKey,
DATEPART(year,lastmodified) as ‘Year’,
DATEPART(month,lastmodified) as ‘Month’,
count(distinct interactionId) as ‘NumberOfInteractions’
from [xdb_collection].[InteractionFacets]
group by facetKey, DATEPART(year,lastmodified), DATEPART(month,lastmodified)
order by facetKey, DATEPART(year,lastmodified), DATEPART(month,lastmodified)