If you've connected your Google Analytics 4 export in BigQuery, you've probably noticed inflated or inconsistent user metrics when you compare it with Looker Studio's GA4 connector.

To get accurate, deduplicated user metrics, you'll want to query the GA4 BigQuery export directly.

This article covers:

• How the GA4 BigQuery export is structured.
• Writing SQL for distinct user counts.
• Setting up Looker Studio to dynamically filter by dates.

1. Understanding the GA4 BigQuery Export

When you enable GA4 BigQuery linking, event data is exported daily into tables named like: analytics_XXXXXXX.events_YYYYMMDD.

Where:

• XXXXXXX = your GA4 property ID.
• YYYYMMDD = the event date.

Each row in these tables includes a user_pseudo_id (the client identifier). If you've set up authenticated tracking, you may also see a user_id.

2. Writing SQL for Distinct User Counts

The key to accurate reporting is deduplication. Since the same user can trigger multiple events in a day, you'll want to count them once per period. The best way to do this is with COUNT(DISTINCT ...).

Example: Counting Distinct Users by Day

    
    SELECT
        event_date,
        COUNT(DISTINCT user_pseudo_id) AS daily_users
    FROM
        `my_project.analytics_XXXXXXX.events_*`
    WHERE
        _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
    GROUP BY
        event_date
    ORDER BY
        event_date;
    

This query:

• Scans all daily event tables in January 2025.
• Counts distinct user_pseudo_id for each day.

Example: Counting Monthly Active Users

    
    SELECT
        COUNT(DISTINCT user_pseudo_id) AS monthly_users
    FROM
        `my_project.analytics_XXXXXXX.events_*`
    WHERE
        _TABLE_SUFFIX BETWEEN '20250101' AND '20250131';
    

If you use a logged-in identifier (user_id), you can replace user_pseudo_id with a COALESCE(user_id, user_pseudo_id) expression to unify anonymous and logged-in tracking.

3. Dynamically Sending Dates from Looker Studio to BigQuery

Hardcoding dates in SQL isn't scalable if you need to look at flexible date ranges. Instead, you can use Looker Studio parameters that pass start and end dates into BigQuery.

Step 1: Use Looker Studio's Date Range Controls

When creating a custom SQL data source, Looker Studio gives you two system parameters:

• @DS_START_DATE — the start date selected in your report.
• @DS_END_DATE — the end date selected in your report.

Step 2: Update Your SQL to Accept Dynamic Dates

    
    SELECT
        event_date,
        COUNT(DISTINCT user_pseudo_id) AS users
    FROM
        `my_project.analytics_XXXXXXX.events_*`
    WHERE
        _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', @DS_START_DATE)
        AND FORMAT_DATE('%Y%m%d', @DS_END_DATE)
    GROUP BY
        event_date
    ORDER BY
        event_date;
    

Now, whenever you change the date range in the dashboard, the query dynamically updates to filter the BigQuery export tables.

4. Best Practices

A few tips to keep things running smoothly:

• Partition pruning: Use _TABLE_SUFFIX filtering instead of scanning events_* without constraints. This reduces cost and speeds up queries.
• Unifying identifiers: Use COALESCE(user_id, user_pseudo_id) if you're tracking both anonymous and authenticated users.
• Avoid double counting: Always rely on COUNT(DISTINCT ...) rather than event counts or naive aggregation.
• Pre-aggregation: If you have heavy traffic, you can create scheduled aggregated tables (daily active users, weekly active users, etc.) to avoid expensive queries in Looker Studio.

Keep in mind your user counts won't exactly match what the GA4 admin is showing due to the algorithm that Google uses previously. You can read more about this here: Why Don't Sessions and Users in the GA4 BigQuery Dataset Match What I See in the GA Admin?

Next Steps for Smarter Reporting

By combining GA4's BigQuery export with Looker Studio's dynamic date filters, you can avoid inflated numbers and build dashboards you can trust.

Whether you're just starting with GA4 exports or looking to refine a complex setup, Calibrate can partner with you to make your reporting seamless

Get in Touch