How to create GA4 Content Group Report in BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

Here is how you can create a GA4 ‘Content Group‘ Report in BigQuery.

A content group is a collection of web pages that share a common theme.

For example, in a blog, a content group could consist of web pages covering the same topic, such as Google Tag Manager.

For an e-commerce website, a content group might include pages that sell similar products, such as shirts.

Content groups help measure the performance of a set of web pages at a content category or product category level.

They are particularly useful for large websites with hundreds or thousands of pages, where analyzing individual page-level performance is impractical.

Follow the steps below to create a content group report in GA4 BigQuery:

Step-1: Make sure you have set up content groups in your GA4 property via GTM and collected at least a couple of days of content group data in both your GA4 property and BigQuery data tables.

Step-2: Once you have successfully set up ‘content_group’ for a website, you should see the value of the ‘content_group’ event parameter with the ‘page_view’ event in your GA4 BigQuery data table.

If you have successfully set up ‘content_group’ for a mobile app, then you should see the value of the ‘content_group’ event parameter with the ‘screen_view’ event in your GA4 BigQuery data table.

Step-3: Calculate the values of the ‘Content Group’ column by extracting the values from the ‘content_group’ event parameter for each ‘event_name’ top level field where ‘event_name’ is ‘page_view’ and the value of ‘content_group’ event parameter is not ‘null’.

Step-4: Calculate the values of the ‘Views’ column by counting the total number of ‘page_view’ events for each ‘content_group’ event parameter.

Step-5: Calculate the values of the ‘Sessions’ column for each ‘Content Group’ by counting each unique combination of ‘user_pseudo_id’ and ‘ga_session_id’.

Step-6: The top row of the data table should show:6.1 ‘Total’ under the ‘Content Group’ column.6.2 The total number of ‘Views’ across all ‘Content Group’ under the ‘Views’ column.6.3 The total number of ”Sessions”across all ‘’Content Group’’under the ”Sessions”column.

If you manually write SQL to implement the above logic, it could be 50 or more lines of SQL you need to type. Even the most optimized form is 36 lines of code.

If you don’t understand SQL at all, you need to wait at least a couple of years to create this report from scratch. You spend this time learning the SQL first.

The SQL below for creating a content group report in GA4 BigQuery is automatically generated via a text prompt in ChatGPT.

-- Content Group Report

WITH
  base_data AS (
    SELECT
      (
        SELECT value.string_value
        FROM UNNEST(event_params)
        WHERE key = 'content_group'
      ) AS content_group,
      user_pseudo_id,
      (
        SELECT value.int_value
        FROM UNNEST(event_params)
        WHERE key = 'ga_session_id'
      ) AS ga_session_id
    FROM `dbrt-ga4.analytics_207472454.events_*`
    WHERE
      event_name = 'page_view'
      AND _TABLE_SUFFIX BETWEEN '20241001' AND '20241031'
  ),
  aggregated_data AS (
    SELECT
      content_group,
      COUNT(*) AS views,
      COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(ga_session_id AS STRING)))
        AS sessions
    FROM base_data
    WHERE content_group IS NOT NULL
    GROUP BY content_group
  ),
  total_row AS (
    SELECT
      'Total' AS content_group,
      SUM(views) AS views,
      SUM(sessions) AS sessions
    FROM aggregated_data
  )
SELECT * FROM total_row
UNION ALL
SELECT *
FROM aggregated_data
ORDER BY views DESC;

If you want to learn to automate SQL generation via text prompts in ChatGPT, enrol in my GA4 BigQuery course.

Instead of teaching you SQL, I teach you to create effective text prompts in chatGPT, which will automatically create your desired SQL.

The focus is entirely on teaching the underlying logic (instead of the actual SQL). So that you can scale across ‘N’ SQL use cases and generate consistent outputs repeatedly with 100% accuracy.

[No prior knowledge of BigQuery or SQL required.]

The SQL below for creating a content group report in GA4 BigQuery is automatically generated via a text prompt in ChatGPT.

Related Articles:

Himanshu Sharma