Learn business growth with Google Analytics 4 Forums Google Analytics 4 Discrepancy between GA4 Data and Big Query Results for Users and Sessions

  • 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.

    Screenshot of data
    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, 3 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.

Log in to reply.