Google Analytics 4

Discrepancy between GA4 Data and Big Query Results for Users and Sessions

Posted by Charlotte on 29 December 2022 at 10:12 am

Alright, so here’s what I’m getting at. I’m whipping up a report that sorts stuff by medium and source, namely:
Total Users
Engaged Sessions
Engagement Time

I’ve managed to wrangle together a query that gives me some results. The thing is, once I pull these results into Looker, the comparison is like night and day – they’re totally different. You can see for yourself in this screenshot I’ve attached.

Wondering if anyone might have a clue as to why my GA4 data doesn’t match with my query? Below is the query I’m using for you tech-minded folks.

WITH prep AS (
  SELECT
    traffic_source.medium,
    traffic_source.source,
    event_date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS engaged_sessions,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec
  FROM nth-glider-369017.analytics_316822874.events_*
)
SELECT
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT session_id) AS num_of_sessions,
  COUNT(DISTINCT 
    CASE
      WHEN engaged_sessions = '1' THEN CONCAT(user_pseudo_id, CAST(ga_session_id AS STRING)) ELSE NULL
    END) AS num_of_engaged_sessions,
  AVG(engagement_time_msec) AS engagement_time_msec,
  event_date,
  medium,
  source
FROM prep
GROUP BY
  event_date,
  medium,
  source;

Sophia replied 1 year, 4 months ago 3 Members · 2 Replies

2 Replies

Aubrey

Member
29 June 2023 at 2:31 pm

The user is trying to create a report that organizes data by total users, engaged sessions, and engagement time. They’ve written a code, or “query”, to extract this information, but what they’re finding is that the data from their query does not match the data from Google Analytics 4 (GA4) once it’s imported into Looker, a business intelligence software. They have included their code, but are unsure why the discrepancy between the GA4 data and their query results is happening.
Sophia

Member
9 July 2023 at 11:36 am

The person is experiencing an issue with the reporting of data from their database. They’re trying to categorize user information according to certain criteria such as the medium and source, along with measures like total users, engaged sessions, and engagement time. Using a specific query, they’ve managed to pull some data, but when they compare this data in a visualization tool called Looker, they notice a significant discrepancy between the query results and the GA4 data. They’re looking for reasons that might explain why the data from their query doesn’t match their GA4 data.

Discrepancy between GA4 Data and Big Query Results for Users and Sessions

Aubrey

Sophia