An Introduction Into GA4 and BigQuery: Landing Pages, Sessions & Conversions

Recorded Live: on September 1, 2023

Learn the essentials of querying landing pages, interpreting session data, and managing scope with session summary tables.

Join CEO of Vision Labs, JJ Reynolds and Ameet Wadhwani from Analytics Canvas as they demystify key aspects of data analysis using GA4 & BigQuery.

What is covered:

The fundamental principles of BigQuery and its role in data analytics.
The significance of GA4 and BigQuery for your business and how to use them effectively.
Techniques for querying landing pages, sessions, and date queries.
The concept of a landing page and how to effectively manage sessions with session summary tables.
The practice of creating scorecards and summary tables, making your data analytics easier and more efficient.
How to blend conversion events from API with BQ data for a more holistic view of your business’s performance.

Watch The Replay

Handouts & code

During the workshop Ameet shared some code on screen, this is that code.

SELECT sourceDataSet,
       EXTRACT(DATE FROM `sessionLocalDateTime`) AS sessionDate,
       landingPage,
       count(distinct `ac_session_uid`) AS sessions
FROM (
  SELECT E.sourceDataset,
         E.ac_session_uid,
         S.sessionLocalDateTime,
         landingPage_tbl.landingPage
  FROM (
    SELECT 'your_project.analytics_yourPropertyID' AS sourceDataset,
           CONCAT(
             (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'),
             '_',
             user_pseudo_id
           ) AS ac_session_uid
    FROM `your_project.analytics_yourPropertyID.events_*`
    WHERE _table_suffix BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'
  ) E
  INNER JOIN (
    SELECT *
    FROM (
      SELECT CONCAT(
               (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'),
               '_',
               user_pseudo_id
             ) AS ac_session_uid,
             row_number() OVER (
               PARTITION BY CONCAT(user_pseudo_id, (SELECT params.value.int_value FROM UNNEST(event_params) AS params WHERE params.key = 'ga_session_id'))
               ORDER BY event_timestamp
             ) AS sessionRank,
             EXTRACT(DATETIME FROM TIMESTAMP_MICROS(`event_timestamp`) AT TIME ZONE 'America/Toronto') AS sessionLocalDateTime
      FROM `your_project.analytics_yourPropertyID.events_*`
      WHERE _table_suffix BETWEEN 'YYYYMMDD - 1' AND 'YYYYMMDD + 1'
    )
    WHERE sessionRank = 1
      AND sessionLocalDateTime >= 'YYYY-MM-DD'
  ) S
  ON S.ac_session_uid = E.ac_session_uid
  INNER JOIN (
    SELECT ac_session_uid,
           landingPage
    FROM (
      SELECT CONCAT(
               (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'),
               '_',
               user_pseudo_id
             ) AS ac_session_uid,
             (SELECT params.value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_location') AS landingPage,
             event_name,
             row_number() OVER (
               PARTITION BY CONCAT(user_pseudo_id, (SELECT params.value.int_value FROM UNNEST(event_params) AS params WHERE params.key = 'ga_session_id'))
               ORDER BY event_timestamp
             ) AS sessionRank
      FROM `your_project.analytics_yourPropertyID.events_*`
      WHERE _table_suffix BETWEEN 'YYYYMMDD - 1' AND 'YYYYMMDD + 1'
    )
    WHERE sessionRank = 1
  ) landingPage_tbl
  ON landingPage_tbl.ac_session_uid = E.ac_session_uid
  GROUP BY sourceDataSet,
           ac_session_uid,
           sessionLocalDateTime,
           landingPage
)
GROUP BY sourceDataset,
         sessionDate,
         landingPage

Share on