BigQuery Google Analytics App + Web: Query User Count Where Medium Matches Parameter
When Google Analytics App plus Web hits are sent to BigQuery, the hits contain parameter that address the specific event. For example, have a look at the below event for a sample page_view event. For this event, there are parameters for campaign, medium among other aspects.
In order to address the params, UNNEST function is used to “flatten” the table and then query each individual row where the parameter is met. This would be the equivalent of putting the repeated values in rows [similar to how you can do in Excel pivot tables]
After getting stuck with the query, I posted it on SO to get help with the answer:
What does the below query do?
Well, it runs a distinct count of user id’s where after unnesting the event_params field, the event_name equals page_view and the event parameter key = medium and the corresponding value for the key is “organic”. Ran a quick check in exploration tab in GA > Analysis to be sure value is correct.
