How to unnest Google Analytics custom dimension in Google Data Prep

Question

Background story: We use Google Analytics to track user behaviour on our website. The data is exported daily into Big Query. Our implementation is quite complex and we use a lot of custom dimensions.

Requirements: 1. The data needs to be imported into our internal databases to enable better and more strategic insights. 2. The process needs to run without requiring human interaction

The problem: Google Analytics data needs to be in a flat format so that we can import it into our database.

Question: How can I unnest custom dimensions data using Google Data Prep?

What it looks like?

----------------
customDimensions
----------------
[{"index":10,"value":"56483799"},{"index":16,"value":"·|·"},{"index":17,"value":"N/A"}]

What I need it to look like?

----------------------------------------------------------
customDimension10 | customDimension16 | customDimension17
----------------------------------------------------------
56483799          | ·|·                | N/A

I know how to achieve this using a standard SQL query in Big Query interface but I really want to have a Google Data Prep flow that does it automatically.

Answer 1

Define the flat format and create it in BigQuery first. You could

• create one big table and repeat several values using CROSS JOINs on all the arrays in the table
• create multiple tables (per array) and use ids to connect them, e.g.
  • for session custom dimensions concatenate fullvisitorid / visitstarttime
  • for hits concatenate fullvisitorid / visitstarttime / hitnumber
  • for products concatenate fullvisitorid / visitstarttime / hitnumber / productSku

The second options is a bit more effort but you save storage because you're not repeating all the information for everything.