Identifying Sitecore Reporting discrepancies with Google Analytics

From time to time I get asked why metrics within Sitecore’s reporting differ to what is being seen in Google Analytics. Most often its due to Sitecore’s tracking happening server side vs GA’s tracking happening client side. But on occasion it can be due to 3rd party monitoring. This post is going to outline some quick ways you can identify the latter.

The reason why 3rd party monitoring can cause discrepancies is largely due to the way the monitoring tools work and because they don’t trigger the execution of scripts like GA, while the Sitecore’s server side tracking is being triggered.

But first, here is a great recent article shining a lens on the benefits of server side tracking, given the state of modern browsers: https://www.analyticshour.io/2019/10/08/125-modern-browsers-and-the-destruction-of-the-analysts-dreams-with-cory-underwood/.

All these queries are to be performed in SQL Server, prior to v9 an equivalent Mongo DB query would be required.

First up we will review the user agents to identify any significant outliers.


WITH [Interactions] AS (
    SELECT * FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[Interactions]
    UNION ALL
	SELECT * FROM [habitathome_Xdb.Collection.Shard1].[xdb_collection].[Interactions]
)
SELECT UserAgent, COUNT(*) AS Occurrences
FROM [Interactions]
GROUP BY UserAgent
ORDER BY 2 DESC

Results:

Next up we will review by IP Address


WITH [InteractionFacets] AS (
    SELECT * FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[InteractionFacets]
    UNION ALL
	SELECT * FROM [habitathome_Xdb.Collection.Shard1].[xdb_collection].[InteractionFacets]
)
SELECT JSON_VALUE([FacetData], '$.IpAddress') AS IpAddress, COUNT(*) AS Occurrences
FROM [InteractionFacets]
WHERE FacetKey = 'IpInfo'
GROUP BY JSON_VALUE([FacetData], '$.IpAddress')
ORDER BY 2 DESC

Results:

This query can allow you to review bounce rates


WITH [Interactions] AS (
SELECT  *
FROM
(
SELECT * FROM [habitathome_Xdb.Collection.Shard0].[xdb_collection].[Interactions]
UNION ALL
SELECT * FROM [habitathome_Xdb.Collection.Shard1].[xdb_collection].[Interactions]
) AS [Interactions_ShardUnion]
CROSS APPLY OPENJSON ([Interactions_ShardUnion].[Events]) WITH ([EventType] varchar(200) N'$."@odata.type"', [Url] varchar(max) N'$.Url')  AS [InteractionEventData]
WHERE [InteractionEventData].[EventType] = '#Sitecore.XConnect.Collection.Model.PageViewEvent'
--AND CAST(StartDateTime AS Date) = '2019-04-16'
)
SELECT [Interactions_Use2].[Url], COUNT(*)
FROM
(   SELECT [InteractionId], Count(*) AS PageEventCountPerSession
FROM [Interactions] AS [Interactions_Use1]
GROUP BY [Interactions_Use1].[InteractionId]
HAVING COUNT(*) &amp;amp;amp;amp;lt;= 1
) AS [InteractionsWith1PageView]
INNER JOIN [Interactions] AS [Interactions_Use2]
ON [InteractionsWith1PageView].[InteractionId] = [Interactions_Use2].[InteractionId]
GROUP BY [Interactions_Use2].[Url]
ORDER BY 2 DESC

Results:

Kaz on Commerce!