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

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