nicobrx

/** Google Analytics Insights fetcher
* 
* This script automates the collection of Google Analytics “new insight” emails and turns them into a structured log 
* in a shared spreadsheet. From there, you can build a dashboard in Looker Studio or another tool to monitor alerts for 
* one or more GA properties over time.
* To use the script, set the configuration variables below, and add a trigger to run the script hourly or daily. 
* When it runs, it searches the Gmail inbox of the account the script is running under and looks
* for any “new insight” notifications received since the previous day, and logs the the property name, metric, frequency, 
* alert text, and a link to the alert in GA. It filters out insights you’ve already logged, and writes only the new records 
* into an “insights” sheet. 

nicobrx

-- This is intended to be run as a Looker Studio BigQuery custom query. To run it directly in BigQuery, change @DS_START_DATE
-- and @DS_END_DATE to 'YYYYMMDD' string values. To use it in BigQuery, you will also need to enable date range parameters
-- in the custom query. Be wary of using this query for high-volume GA4 properties or long date ranges, as it could incur 
-- BigQuery analysis costs. This github repo has a Google Dataform repository that is designed to update a table with path data
-- on a daily basis, which is a much more efficient and cost-effective approach: https://github.com/nicobrx/ga4-path-analysis
-- This query references an 'extract_path' UDF, the original version of which can be found here: 
-- https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/udfs/community/cw_url_extract_path.sqlx
-- You will need to update the reference in this query to the location of the UDF you create in your BigQuery project
-- You will also need to update the FROM references in the source_mediums

nicobrx

select
    -- day of week, starting with Sunday (dimension)
    format_date('%w',parse_date("%Y%m%d",event_date)) as day_of_week,
    -- hour (dimension)
    format("%02d",extract(hour from timestamp_micros(event_timestamp))) as hour,
    (select value.string_value from unnest(event_params) where key = 'page_location') as page_path,
    count(event_name) as sessions
from
    -- change this to your google analytics 4 export location in bigquery
    `yourproject.analytics_1234567.events_*`

nicobrx

-- set dates here so we don't have to update them in multiple places
with dates as (
  select 
    '20230812' as start_date,
    -- the next line gets yesterday
    format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date
),

-- get all events with event values
p as (

nicobrx

--Looker Studio BigQuery custom query to get sessions per user

with s as (select distinct 
  user_pseudo_id,
  concat(user_pseudo_id,(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS session_id,
  timestamp_micros(event_timestamp) as timestamp
from `yourbqproject.analytics_1234567.events_*`
where _table_suffix between @DS_START_DATE and @DS_END_DATE
and event_name = 'session_start')

nicobrx

SELECT
  page_location,
  SUM(engagement_time_seconds) AS engagement_time_seconds,
  SUM(page_views) AS page_views,
  SAFE_DIVIDE( SUM(engagement_time_seconds), SUM(page_views) ) AS average_engagement_time_per_page
FROM (
  SELECT
    (
    SELECT
      value.string_value

nicobrx

SELECT
  event_date,
  COUNT(DISTINCT(session_id)) AS sessions,
  SUM(ga4_resource_page_views) AS ga4_resource_page_views
FROM (
  SELECT
    CONCAT(user_pseudo_id,(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id')) AS session_id,
    COUNT((SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'page_location'
      AND value.string_value LIKE 'https://twooctobers.com/blog/ga4-resources/%')) AS ga4_resource_page_views,
    PARSE_DATE('%Y%m%d',event_date) AS event_date

nicobrx

<script>
 if(!(window.ga && ga.create)) {
   dataLayer.push({'event': 'gaNotLoaded'});
 } 
</script>

nicobrx

<script>
/*
*  adapted from https://developers.google.com/analytics/devguides/collection/analyticsjs/cross-domain#iframes
*  I wrapped the whole thing in a setTimeout, so I could observe the delayed execution time
*  of the GA function in the JavaScript console, using the GA debugger Chrome plugin.
*  alternatively, you could just wrap the frameWindow.postMessage in a setTimeout
*/

setTimeout(function(){ 
  ga(function(tracker) {

nicobrx

<script>
/*
* adapted from https://developers.google.com/analytics/devguides/collection/analyticsjs/cross-domain#iframes
* I include the GA object reference in my script, so that one version of 
* the script works whether served in an iFrame or on a standalone page. 
* I usually have to rely on the 3rd-party vendor to implement the code, so 
* I like to keep implementation as simple as possible.
*/

  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){