Learn business growth with Google Analytics 4 Forums Google Analytics 4 Divergent Session Counts: GA4 vs BigQuery's Events_Intraday Table

  • Divergent Session Counts: GA4 vs BigQuery's Events_Intraday Table

    Posted by Daniel on 21 April 2023 at 7:30 pm

    We’ve run into a bit of a puzzle here. The session number from GA4 for November, at 559,555, is way more than what we’re calculating from the BigQuery table, which is 468,991. A bit odd, right? We’re leaning more towards BigQuery’s number as it aligns better with our actual traffic and Google Analytics 360 data. A thing to note – we started noticing these discrepancies when we introduced the eCommerce event on our site. Not sure if there’s a connection or not.

    Here’s what our questions boil down to:

    1. Wouldn’t you expect the GA4’s screen numbers and the BigQuery data to be matched up or at least along similar lines?
    2. What’s the best way to get these numbers to align more closely? We really need them to make sense.

    To give you the full picture, we’ve been calculating session numbers in BigQuery with this formula:

    SELECT
      HLL_COUNT.EXTRACT(
        HLL_COUNT.INIT(
          CONCAT(
            user_pseudo_id,
            (SELECT value FROM UNNEST(event_params) WHERE key = 'ga_session_id' LIMIT 1).int_value),
          12)) AS session_count,
    FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*
    

    If you’re interested, here’s the link to the HLL guide we’ve been following: https://developers.google.com/analytics/blog/2022/hll.

    You guys are the pros here. We’re all ears for any advice or insights you can provide so we can sort out this issue.

    Daniel replied 11 months, 1 week ago 3 Members · 2 Replies
  • 2 Replies
  • Mia

    Member
    2 June 2023 at 7:38 am

    This issue is indeed quite intriguing! Alright, here’s the scoop. Google Analytics 4 (GA4) and BigQuery might show different data due to their distinct approaches to session calculations. Your trusted BigQuery tends to be more exacting in its computation, which is probably why the numbers align closer with your actual traffic and other analytics. On the other hand, the GA4 interface usually provides estimates.

    Moreover, there are certain sessions, such as those derived via Google Signals, that aren’t included in BigQuery. Whether it’s tied to your recently incorporated eCommerce event is hard to say, but it’s definitely worth keeping in mind these fundamental differences between GA4 and BigQuery data handling.

    So, to your questions:
    1. Yes, we’d often anticipate some similarity between GA4’s and BigQuery’s numbers. However, as I mentioned above, due to differing calculation methods and data inclusions, some discrepancy might occur.
    2. To align these numbers more accurately, it really comes down to gaining a deeper understanding of what each tool counts as a ‘session’ and adjusting expectations and calculations accordingly. Ensure any additional events or tags you’re deploying are correctly incorporated in your calculations. There isn’t a one-size-fits-all solution, but with some tweaking and understanding of the processes involved, you should see more consistent results.

    I hope this helps clarify the situation a bit!

  • Daniel

    Member
    16 June 2023 at 10:41 am

    Yes, ideally the GA4’s screen numbers and the BigQuery data would be closely aligned. However, discrepancies can occur due to several reasons like data processing differences, internal filters applied in GA, or event-based or cookie-based session tracking differences. Introducing new events to your site could also potentially affect session tracking and cause discrepancies. In order to align the numbers more closely, you may need to investigate how each platform is calculating and filtering session data. Sometimes it might be an error in the query or the data collection method itself, so make sure to verify that your BigQuery formula is accurately reflecting the session calculations and try to account for any filtering or processing differences within GA4. It’s also important to note that GA4 and BigQuery use different methods to count sessions, handle cookies and manage user identification, which could explain why the numbers don’t match. You’re on the right track, and with some data investigation, you should be able to understand and resolve these discrepancies.

Log in to reply.