Custom Definitions in Bigquery

Question

I'm pretty new to Bigquery/Firebase/GA even SQL. (btw, if you have some good experience or recommendations where I can start learning, that would be great!)

But I have main issue with Bigquery that needs solving right now. I'm kinda trying all sources I can get some info/tips from. I hope this community will be one of them.

So my issue is with Custom Definitions. we have them defined in Google Analytics . We want to divide users with this definition and analyze them separately:

My question is: where/how can I find these custom definitions in bigquery to filter my Data? I have normal fields, like user ID, Timestamps etc. but can't find these custom definitions.

I have been doing some research but still don't have a clear answer, if someone can give me some tips or mby a solution I would be forever in debt ! xD

I got one solution from the other community which looks like this, but I couldn't make it work, my bigquery doesn't recognize customDimensions as it says in the error.

select cd.* from table, unnest(customDimensions) cd

Answer 1

You can create your own custom function, Stored Procedure on Bigquery as per your requirements. To apply formal Filter over filed like user ID, & Timestamps, you can simply apply standard SQL filter as given below:-

SELECT * FROM DATA WHERE USER_ID = 'User1' OR Timestamps = 'YYY-MM-DDTHH:MM'

Moreover, unnest is used to split data on fields, do you have data which need to be spited ? I could help you more if you share what are you expecting from your SQL.

Answer 2

Your custom dimensions sit in arrays called customDimensions. These arrays are basically a list of structs - where each struct has 2 fields: key and value. So they basically look like this example: [ {key:1, value:'banana'}, {key:4, value:'yellow'}, {key:8, value:'premium'} ] where key is the index of the custom dimension you've set up in Google Analytics.

There are 3 customDimensions arrays! Two of them are nested within other arrays. If you want to work with those you really need to get proficient in working with arrays. E.g. the function unnest() turns arrays into table format on which you can run SQL.

• customDimensions[]
• hits[]
  • customDimensions[]
  • product[]
    • customDimensions[]

Example 1 with subquery on session-scoped custom dimension 3:

select
  fullvisitorid,
  visitStartTime,
  (select value from unnest(customDimensions) where key=3) cd3
from
  ga_sessions_20210202

Example 2 with a lateral cross join - you're enlargening the table here - not ideal:

select
  fullvisitorid,
  visitStartTime,  
  cd.*
from ga_session_20210202 cross join unnest(customDimensions) as cd

All names are case-sensitive - in one of your screenshots you used a wrong name with a "c" being uppercase.

This page can help you up your array game: https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays - just work through all the examples and play around in the query editor