GA4 Big Query

GA4 Big Query

February 23, 2024

Google Analytics 4 #

Introduction to Google Analytics 4 #

source: https://develop… Google Analytics 4 is an analytics service that lets you to measure traffic and engagement across your websites and apps. This documentation provides implementation instructions and reference materials geared towards a developer audience.

Add the GA4 configuration tag to your website

Prerequisites #

Default conversion value options #

source: https://support.goo…

  1. In Admin, under Product Links, click BigQuery Links.
  2. Click Link.
  3. Click Choose a BigQuery project to display a list of projects for which you have access.

If you have linked Analytics and Firebase (or plan to), consider exporting to the same Cloud project, which will facilitate easier joins with other Firebase data. Select a project from the list, then click Confirm. Select a location for the data. (If your project already has a dataset for the Analytics property, you can’t configure this option.)

  1. Click Next. Select Configure data streams and events to select which data streams to include with the export and specific events to exclude from the export. You can exclude events by either clicking Add to select from a list of existing events or by clicking Specify event by name to choose existing events by name or to specify event names that have yet to be collected on the property.

  2. Click Done. Select Include advertising identifiers for mobile app streams if you want to include advertising identifiers. Select either or both a Daily (once a day) or Streaming (continuous) export of data.

  3. Click Next. Review your settings, then click Submit.

…

Where to click

Now what ?? - Create a BigQuery Dataset, Table and Query #

SQL Query Analytics Data from BigQuery #

  • Getting information about views - source
  • List views in a dataset
SELECT table_name
FROM DATASET_ID.INFORMATION_SCHEMA.VIEWS;

  • new vs returning users

WITH UserTypes AS (
  SELECT 
    user_pseudo_id,
    CASE
      WHEN (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number' AND event_name = 'session_start') = 1 THEN 'New User'
      WHEN (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number' AND event_name = 'session_start') > 1 THEN 'Returning User'
      ELSE NULL
    END AS user_type_calc
  FROM 
    `[Project-ID].[Dataset].[TableName]`
)

SELECT 
  user_type_calc,
  COUNT(DISTINCT user_pseudo_id) AS users_calc
FROM 
  UserTypes
WHERE 
  user_type_calc IS NOT NULL
GROUP BY 
  user_type_calc
ORDER BY 
  users_calc DESC;

SELECT count(
      DISTINCT concat(user_pseudo_id, 
                         (SELECT value.int_value FROM UNNEST(event_params) 
                          WHERE key = "ga_session_id")
                      )
            ) as num_of_sessions 
FROM `[Project-ID].[Dataset].[TableName]*`

SELECT count(DISTINCT user_pseudo_id) as total_users
                        
FROM `[Project-ID].[Dataset].[TableName]*`

SELECT count(DISTINCT user_pseudo_id) as active_users
                        
FROM `[Project-ID].[Dataset].[TableName]*`

WHERE is_active_user is true;

with data_prep as (


SELECT
  user_pseudo_id,
  (select value.int_value from unnest(event_params) where event_name='page_view' and key='ga_session_id') as session_id,
  (select value.string_value from unnest(event_params) where event_name='page_view' and key='page_title') as page_title,
  (select value.string_value from unnest(event_params) where event_name='page_view' and key='page_location') as page_location
 FROM `[Project-ID].[Dataset].[TableName]*` 
 where event_name = 'page_view'


)


select
  page_title,
  page_location,
  count(*) as total_pageviews,
  count(distinct concat(user_pseudo_id,session_id)) as unique_pageviews
from
  data_prep


group by 1,2
order by 4 desc

Tutorial - Video Resources #