Matching Big Query Data with Google Analytics - Acquisition Channel report
Continuing to recreate Google Analytics reports in Google BigQuery, this time looking at the Acquisition Channel report.
See my previous article: Matching Big Query Data with Google Analytics - Audience Overview. This covers the basics with:
Users
New Users
Sessions
Bounce Rate
Pages/session
Avg. Session Duration
So I won’t cover those again. In this article I’ll look at breaking these down by channel grouping. I will also cover the remaining metrics:
Transactions
Transactions work a bit differently to goals, goals are counted once per session where as transactions are multiple times in a session. There is also seperate metrics in the BigQuery table for transactions and lucky for us, transactionID, so no matter what you unnest the tables by you can get the number of transactions.
eCommerce Conversion Rate
Converstion rate is just transactions divided by sessions so then we have:
Revenue
There are as many ways to get revenue as to cook an egg. You could use totals.transactionRevenue if you aren’t unnesting anything. You could sum up hits.product.productRevenue and add shipping. For this example I am going to use hits.transaction.transactionRevenue. All revenue needs to be divided by 1,000,000 to get it to be in dollars, this is to allow for conversion to different currencies and what not.
Goal completions
Your goals should be based on a page or event. Do not use smart goals, they suck. Duration and number of page view goals are also not well thought out. Goals are triggered once per session so to get the number of goals we just need to count all the distinct ids that saw a certain page or triggered a certain event. For this example we are counting people who saw the registration page.
Goal Conversion Rate
Just like ecommerce conversion rate, goal conversion is total goals divided by sessions.
Put it all together
So all the metrics together are below, channel groupings could be replaced with trafficSource.campaign or trafficSource.source or trafficSource.medium
Next week I will move onto behaviour reports. I won’t do the pages report as this article outlines it just great: