BigQuery advanced queries for Google Analytics

https://developers.google.com/analytics/bigquery/advanced-queries

Analyze the steps leading to a purchase, calculating conversion rates by country.

WITH
  PurchaseFunnel AS (
    SELECT
      user_pseudo_id,
      geo.country AS country,
      MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS viewed_item,
      MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS added_to_cart,
      MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchased
    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
    GROUP BY
      user_pseudo_id,
      country
  )
SELECT
  country,
  SUM(viewed_item) AS viewed_item_count,
  SUM(added_to_cart) AS added_to_cart_count,
  SUM(purchased) AS purchased_count,
  SAFE_DIVIDE(SUM(added_to_cart), SUM(viewed_item)) AS add_to_cart_rate,
  SAFE_DIVIDE(SUM(purchased), SUM(added_to_cart)) AS purchase_rate
FROM
  PurchaseFunnel
GROUP BY
  country
ORDER BY
  purchased_count DESC;

Identify the most effective traffic sources.

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS traffic_source,
  COUNT(DISTINCT user_pseudo_id) AS unique_users
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
GROUP BY
  traffic_source
ORDER BY
  unique_users DESC;