CODEWITHSUNDEEP
google-analyticsgoogle-cloud-platformgoogle-bigquery
Akshit Zaveri
Akshit Zaveri

Reputation: 4244

BigQuery and Google analytics compatibility issues

I’d like to create conversion funnels with events. Most of the events I created are listed under “Select_content”. However, Google Analytics does not allow me to choose any of the specific events under “Select_content”, only the whole category of “Select_content”. Is there any way that I can create a conversion funnel using the specific events under “Select_content”?

Is there any way to break out the CONTACT_DETAIL event into its specific events (e.g. CALL, MESSAGE, TAG, DATE, etc.)?

This is how I send Google Analytics event from iOS app

Analytics.logEvent(AnalyticsEventSelectContent, parameters: [
   AnalyticsParameterItemID: "id-\(id)" as NSObject,
   AnalyticsParameterItemName: itemName as NSObject,
   AnalyticsParameterContentType: contentType as NSObject
])

What's wrong with it? Why is BigQuery not able to go in to deeper level from already uploaded Google Analytics data?

Upvotes: 7

Views: 217

Answers (1)

Ben P
Ben P

Reputation: 3379

Depending on how you have setup your events you access them via the EventCategory, EventAction or EventLabel and all of these get passed into BigQuery assuming you have connected your Google Analytics data import.

enter image description here

If, for example, the events you are looking for have the Event Category of Select_content then you can start by filtering just these events with a WHERE clause such as: WHERE hits.eventInfo.eventCategory = "Select_content"

Note that in order to get to the .hits level in the nested data you will need to UNNEST hits, for example like this:

SELECT COUNT(hits.eventinfo.eventlabel) AS my_events, 
FROM `PROJECT.DATASET.ga_sessions_20*` AS t
  CROSS JOIN UNNEST(hits) AS hits
WHERE parse_date('%y%m%d', _table_suffix) between 
DATE_sub(current_date(), interval 7 day) and
DATE_sub(current_date(), interval 1 day)
AND hits.eventInfo.eventCategory = "Ecommerce"

You could also add to the WHERE clause to filter into a specific Event Action, if you don't want to see everything that's under the Event Category, for example:

WHERE hits.eventInfo.eventCategory = "Ecommerce"
AND hits.eventInfo.eventAction = "Purchase"

Once you have managed to isolate the correct events for your funnel it should be pretty straightforward to assemble a funnel from the counts.

Hope that helps!

Upvotes: 3

Related Questions