Google Analytics 4 BigQuery (Service Account) Extractor

Copy

SELECT
event_date,
event_name,
EP.key AS param_key,
COALESCE(value.string_value,CAST(value.int_value AS STRING), CAST(value.float_value AS STRING),
CAST(value.double_value AS STRING)) AS param_value
FROM
`your_project_id.your_dataset_id.events_*`,  -- Replace table name.
UNNEST(event_params) AS EP
WHERE
event_name = 'user_engagement'  -- Provide required event_name.
AND _TABLE_SUFFIX BETWEEN '20230801' AND '20230831';  -- Replace date range.

Copy

SELECT
  EP.key AS event_param_key,
  COUNT(*) AS occurrences
FROM
   `your_project_id.your_dataset_id.events_*`,   -- Replace table name.
   UNNEST(event_params) AS EP
WHERE
  _TABLE_SUFFIX BETWEEN '20230801' AND '20230802'   -- Replace date range.
GROUP BY
  event_param_key
ORDER BY
  event_param_key ASC;

Copy

SELECT distinct key,
COALESCE(value.string_value,CAST(value.int_value AS STRING), 
CAST(value.float_value AS STRING), CAST(value.double_value AS STRING)) 
AS param_value
FROM  
`your_project_id.your_dataset_id.your_table_id`, -- Replace table name.
UNNEST(event_params) AS event_params
-- Use this to filter for a specific key and provide the required key
-- WHERE key in ('ga_session_id','ga_session_number','session_engaged','campaign','medium') 
ORDER BY key;  

Copy

SELECT event_date, event_timestamp, event_name, user_pseudo_id,items.*
FROM  `your_project_id.your_dataset_id.your_table_id`,  -- Replace table name.
UNNEST(items) AS items 
ORDER BY 3;

Copy

SELECT event_date, event_timestamp, event_name, user_pseudo_id,
key,
COALESCE(value.string_value,CAST(value.int_value AS STRING), CAST(value.float_value AS STRING), CAST(value.double_value AS STRING)) AS param_value
FROM  `your_project_id.your_dataset_id.your_table_id`,  -- Replace table name.
UNNEST(user_properties) AS uprop
ORDER BY 1;

Copy

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count -- Counts unique events by date and by event name for a specifc period of days
FROM  `your_project_id.your_dataset_id.events_*`   -- Replace table name.   
WHERE 
  event_name IN ('page_view', 'session_start', 'purchase') --PASS enent_names here
  AND _TABLE_SUFFIX BETWEEN '20230701' AND '20230708'   -- Replace date range TO get data for specific date range
GROUP BY 1, 2;

Copy

SELECT _TABLE_SUFFIX AS event_date,count(distinct user_pseudo_id) AS date_wise_user_count
FROM    `your_project_id.your_dataset_id.events_*`   -- Replace table name.
WHERE   _TABLE_SUFFIX BETWEEN '20230717' AND '20230722' -- Replace date range TO get data for specific date range
GROUP BY _TABLE_SUFFIX
ORDER BY 1;

Copy

WITH
  new_users AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    -- Replace table name.
    FROM  `your_project_id.your_dataset_id.events_*` 
    WHERE _TABLE_SUFFIX BETWEEN '20230701' AND '20230708'   -- Replace date range TO get data for specific date range
    GROUP BY 1
  )
SELECT
  COUNT(*) AS total_user_count,
  SUM(is_new_user) AS new_user_count
FROM new_users;
--
-- Queries the individual timestamps and values for all 'purchase' events.

SELECT
  event_timestamp,
  (
    SELECT COALESCE(value.int_value, value.float_value, value.double_value)
    FROM UNNEST(event_params)
    WHERE key = 'value'
  ) AS event_value
FROM
  -- Replace table name.
   `your_project_id.your_dataset_id.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20230801' AND '20230802';

Copy

DECLARE N INT64 DEFAULT -3 ; -- Replace 3 BY LAST NUM OF DAYS REQUIRED
DECLARE REPORTING_TIMEZONE STRING DEFAULT 'America/Los_Angeles'; --Replace America/Los_Angeles BY REQUIRED TIMEZONE

CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
  (SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);

CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
  (SELECT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);
WITH
  userpseudoid AS(
  SELECT
    DISTINCT user_pseudo_id
  FROM
     `your_project_id.your_dataset_id.events_*`,
    UNNEST(event_params) AS event_params
  WHERE KEY IN ('ga_session_id','ga_session_number')
  AND _TABLE_SUFFIX BETWEEN '20230517' AND '20230822'
  )

SELECT DISTINCT
  events.user_pseudo_id,
  FIRST_VALUE(GetParamValue(event_params, 'ga_session_id').int_value)
    OVER (UserWindow) AS ga_session_id,
  FIRST_VALUE(GetParamValue(event_params, 'ga_session_number').int_value)
    OVER (UserWindow) AS ga_session_number
FROM
  -- Replace table name.
    `your_project_id.your_dataset_id.events_*` events
  INNER JOIN   userpseudoid AS upid
          ON(upid.user_pseudo_id = events.user_pseudo_id)
WHERE
RIGHT(_TABLE_SUFFIX, 8)
BETWEEN GetDateSuffix(N, REPORTING_TIMEZONE)
AND GetDateSuffix(0, REPORTING_TIMEZONE)
WINDOW UserWindow AS (PARTITION BY events.user_pseudo_id ORDER BY events.event_timestamp DESC)
ORDER BY 1;