Skip to content

Instantly share code, notes, and snippets.

@lfy79001
Last active April 10, 2024 06:38
Show Gist options
  • Save lfy79001/29d61d40f54965a5330efbfb89d5e1f5 to your computer and use it in GitHub Desktop.
Save lfy79001/29d61d40f54965a5330efbfb89d5e1f5 to your computer and use it in GitHub Desktop.
WITH
events AS (
SELECT
session.value.int_value AS session_id,
COALESCE(spend.value.int_value, spend.value.float_value, spend.value.double_value, 0.0)
AS spend_value,
event.*
-- Replace table name
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS event
LEFT JOIN UNNEST(event.event_params) AS session
ON session.key = 'ga_session_id'
LEFT JOIN UNNEST(event.event_params) AS spend
ON spend.key = 'value'
-- Replace date range
WHERE _TABLE_SUFFIX = '20201101'
)
SELECT
user_pseudo_id,
COUNT(DISTINCT session_id) AS session_count,
SUM(spend_value) / COUNT(DISTINCT session_id) AS avg_spend_per_session_by_user
FROM events
WHERE event_name = 'purchase' and session_id IS NOT NULL
GROUP BY user_pseudo_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment