Skip to content

Instantly share code, notes, and snippets.

@lfy79001
Last active April 10, 2024 03:22
Show Gist options
  • Save lfy79001/d0ec12c8e30d59f9f9cfdf76c69f9801 to your computer and use it in GitHub Desktop.
Save lfy79001/d0ec12c8e30d59f9f9cfdf76c69f9801 to your computer and use it in GitHub Desktop.
WITH
UserInfo AS (
SELECT
user_pseudo_id,
COUNTIF(event_name = 'page_view') AS page_view_count,
COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201231'
GROUP BY 1
)
SELECT
(purchase_event_count > 0) AS purchaser,
COUNT(*) AS user_count,
SUM(page_view_count) AS total_page_views,
SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment