GA4 User Aggregation in BigQuery Without Over-Counting in Power BI

Hello Anonymous,

Can you please try this approach using pre-aggregation in BigQuery:

WITH DailyUsers AS (
  SELECT 
    date,
    APPROX_COUNT_DISTINCT(user_pseudo_id) AS daily_unique_users
  FROM `your_project.analytics_XXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
  GROUP BY date
),
WeeklyUsers AS (
  SELECT 
    FORMAT_DATE('%Y-%W', PARSE_DATE('%Y%m%d', date)) AS week,
    APPROX_COUNT_DISTINCT(user_pseudo_id) AS weekly_unique_users
  FROM `your_project.analytics_XXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
  GROUP BY week
),
MonthlyUsers AS (
  SELECT 
    FORMAT_DATE('%Y-%m', PARSE_DATE('%Y%m%d', date)) AS month,
    APPROX_COUNT_DISTINCT(user_pseudo_id) AS monthly_unique_users
  FROM `your_project.analytics_XXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
  GROUP BY month
)
SELECT * FROM DailyUsers
UNION ALL
SELECT * FROM WeeklyUsers
UNION ALL
SELECT * FROM MonthlyUsers;