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;

Hi Anonymous ,

Thanks for Sahir_Maharaj's reply!
And Anonymous , you can try to use partitioning and clustering on your BigQuery tables to speed up queries.
For example, you can partition your table by date:

CREATE TABLE `your_project.your_dataset.your_table`
PARTITION BY date
CLUSTER BY user_id AS
SELECT
  date,
  user_id,
  -- other columns
FROM
  `your_project.your_dataset.your_source_table`

And if your dataset is too large to refresh entirely each time, consider using incremental refresh in Power BI. This way, only new data is loaded, reducing the refresh time and resource usage.
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.