Reading Google Analytics 4 Detail From Big Query

If you have already setup Big Query linking from Google Analytics 4, here are the steps you need to get that detailed data into Excel via Analytics Edge…simplified.

Saving Your Data

In the Big Query SQL Workspace, select the event tables for your project, and click Query Table.

The change the default SQL from something like:

SELECT FROM `kinetic-harbor-#######.analytics_247679596.events_20210305` LIMIT 1000

to something like (start with SELECT … FROM, and drop the LIMIT 1000):

SELECT event_date,event_timestamp,event_name,event_params,event_previous_timestamp,event_value_in_usd,event_bundle_sequence_id,event_server_timestamp_offset,user_id,user_pseudo_id,user_first_touch_timestamp,user_ltv,device,geo,app_info,traffic_source,stream_id,platform,event_dimensions,ecommerce FROM `kinetic-harbor-#######.analytics_247679596.events_20210305`

This list of fields will not pull ‘user_properties’ or ecommerce ‘items’ detail, but it does grab all the ‘event_params’ detail.

Note that you can change the date (e.g. 20210305) at the end of the query expression as desired. Click the Run button.

One of the first things you will notice about the Big Query data is that it is hierarchical, with one ‘row’ containing multiple rows for the event parameters…rows within a row. This doesn’t lend itself easily to exporting to Excel for further analysis. Solutions usually involve you learning the Big Query SQL language (UNNEST) or something requiring a lot of effort (Excel connector).

The good news is that you can Save the data in JSON format (up to 1 Gig of data to Google Drive), and then read that file using the Analytics Edge Read Text File wizard.

Reading Your Data Into Excel

In an Analytics Edge macro, select the File > Read Text File function, then browse to your Google Drive JSON file, and pick the JSON option in the wizard (leave the top node entry blank):

Analytics Edge will parse the JSON and when it sees a row-within-a-row, it automatically flattens the data for Excel. Each row-in-a-row is assigned it’s own set of columns, and the field names show the hierarchy (event_params/key, event_params/value/int_value, etc.). Each set of columns (originally rows) is numbered so that the column names are unique (event_params-1/key, event_params-2/key, etc). Some people like this row-oriented approach.

Reformatting Your Data

Other people may prefer to see the event_params/key values all in the same column, and Analytics Edge has a function to do that — Table > Arrange > MultiRow Records. This function repeats all the other column values for each row-in-a-row, recreating the original structure.

With all your Google Analytics 4 data now in Excel, you can sort, filter and analyze as you desire.

With Analytics Edge, the process is greatly simplified…no coding required.