How to query Google Analytics 4 data via BigQuery into Power BI? Dynamic Table name?? Help!

[rsderby68]

Hello All, 

I set up our GA4 account to link to BigQuery, which I would like to pull the data from into Power BI.  Google can't ever make anything easy so they are creating different tables by date names and streaming tables for the day-of; on top of that it can take them up to 72 hours to actually process before a full day table it created.  

Does anyone have a query that would pull all the tables and combine them (or at least combine the full-day tables, and separately combine the streaming day tables)?  I'd prefer to be working from one main past-days table in Power BI (with historical information) and one day-of table for the  latest streaming data that day. 

Help??  This is what I am seeing in BigQuery and Power BI, below and the info from Google.  

For each day, streaming export creates one new table:

• events_intraday_YYYYMMDD: An internal staging table that includes records of session activity that took place during the day. Streaming export is a best-effort operation and may not include all data for reasons such as the processing of late events and/or failed uploads. Data is exported continuously throughout the day. This table can include records of a session when that session spans multiple export operations.This table is deleted when events_YYYYMMDD is complete.

If you select the daily option when you set up BigQuery Export, then the following table is also created each day.

• events_YYYYMMDD: The full daily export of events.

You should query events_YYYYMMDD rather than events_intraday_YYYYMMDD so you're querying a stable dataset for the day.

Updates to the tables that are created as part of BigQuery Export are governed by the time zone of the Analytics property from which data is being exported.

Streaming-export tables (events_intraday_YYYYMMDD) are updated continuously throughout the day (e.g., from 12:00:00 am until 11:59:59 pm in the property's time zone). Once a new day starts in the property's time zone, events are written to a new intraday table.

Daily export tables (events_YYYYMMDD) are created after Analytics collects all of the events for the day. Analytics will update daily tables for up to 72 hours beyond the date of the table with events that are timestamped with the date of the table, e.g., event bundles that come in late from Measurement Protocol or the Firebase SDKs. For example, if the table date is 20220101, then Analytics will update the table through 20220104 with events that are timestamped 20220101.

On occasion, Analytics may update the daily tables anytime after the 72-hour window under circumstances that require Analytics to reprocess historical data (e.g., a bug fix that remedies a processing error).

[rsderby68 · Accepted solution]

I figured out how to do this by going to the Source step and just grabbing everything into one table.  Works great! 

[ScrubberKing]

In case anyone else stumbles across this question, I had the same problem and @MongooseGeneral provided a perfect solution which does not necessitate appending data. See here: https://community.fabric.microsoft.com/t5/Desktop/Google-BigQuery-Pull-data-from-tables-where-each-t... 

@MongooseGeneral  wrote:

I had the same issue and found a tutorial suggesting using the following line in the query that you enter in Advanced Options under the BigQuery conector in Power BI. I think I got it from here: Page dimensions & metrics (GA4) (ga4bigquery.com)

from

    `projectid.analytics_311943674.events_*`

That should bring them all together.

E.g. this is what I use to get pageviews:

select

    (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') as page,

    countif(event_name = 'page_view') as page_views,

    event_date

from

    -- change this to your google analytics 4 export location in bigquery

       `projectid.analytics_311943674.events_*`

where

    -- define static and/or dynamic start and end date

    _table_suffix > '20230301'

group by

    page,

    event_date

order by

    page_views desc

---

[vincent_nox]

@rsderby68 I'm not sure I've figured that out. Could you expand on that, please? 😅

[rsderby68]

You would need to append the multiple tables (they have mostly the same fields) into one and that is where you can work from.  If you look up appending tables in Google there are articles on how to do in Power BI.  

[v-yalanwu-msft]

Hi， @rsderby68 ；

May be could refer to those link:

Connecting BigQuery, Google Analytics and Power BI - YouTube

GA4 BigQuery - Connect Google Analytics 4 with BigQuery - Optimize Smart

How to connect and export data from GA4 to BigQuery - Optimize Smart

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.