Tracking Google Analytics 4 AI Traffic in BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

Use the following SQL (automatically generated via a text prompt in ChatGPT) to track Google Analytics 4 AI Traffic in BigQuery:

-- Calculate AI Traffic.

SELECT
  COALESCE(NULLIF(traffic_source.source, ''), 'null') AS traffic_source,
  COALESCE(NULLIF(traffic_source.medium, ''), 'null') AS traffic_medium,
  COUNT(
    DISTINCT
      CONCAT(
        user_pseudo_id,
        CAST(
          (
            SELECT value.int_value
            FROM UNNEST(event_params)
            WHERE key = 'ga_session_id'
          )
          AS STRING))) AS sessions,
  COUNT(
    DISTINCT
      CASE
        WHEN
          (
            SELECT value.string_value
            FROM UNNEST(event_params)
            WHERE key = 'session_engaged'
          )
          = '1'
          THEN
            CONCAT(
              user_pseudo_id,
              CAST(
                (
                  SELECT value.int_value
                  FROM UNNEST(event_params)
                  WHERE key = 'ga_session_id'
                )
                AS STRING))
        END) AS engaged_sessions
FROM
  `dbrt-ga4.analytics_207472454.events_*`
WHERE
  REGEXP_CONTAINS(
    traffic_source.source,
    r'^.*ai$|.*openai.*|.*copilot.*|.*chatgpt.*|.*gemini.*$')
  AND _TABLE_SUFFIX BETWEEN '20241001' AND '20241031'
GROUP BY
  traffic_source,
  traffic_medium
ORDER BY
  sessions DESC;

I am not a fan of cookie-cutter reports. But if you want one, this one could be for you.

All you have to do is supply your table ID to generate SQL that works for you.

If you want to customize this report according to your unique data analysis requirements then you need to understand the “logic” used behind calculating the various metrics in this report.

Once you understand the logic, customizing this report is easy peasy.

I teach you such logic in my GA4 BigQuery course, where you can learn to automate SQL generation via text prompts in ChatGPT.

There could be ‘N’ use cases, and it won’t be possible to provide ready-made SQL code for every possible case.

So, it is better that you understand the logic to scale SQL generation to the moon.

The future belongs to those who can query raw GA4 data in BigQuery, apply SQL logic, automate SQL generation via AI and drive insights beyond what GA4 UI can offer.

Related Articles:

Himanshu Sharma