Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Google BigQuery Pull data from tables where each table is a separate date

Hello,   I am trying to pull GA4 events data from Big Query but events are each stored in their own table partitioned by date. Each data source looks like the following: I want to combin...
Need Help
  • MongooseGeneral's avatar
    MongooseGeneral
    3 years ago

    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