get more from analytics 360 with bigquery and the google cloud platform
TRANSCRIPT
javier ramirez@supercoco9
Get more from Analytics with Google BigQuery and the Google Cloud Platform
founder of https://datawaki.comhttps://teowaki.com
We help companies on big data,google analytics 360, and cloud.
We can advise, train, optimise,code, or deploy solutions.
Script template for House MD:
* Patient gets in (dying)* Dr launches hypothesis* They take metrics (using
awesome technology)* New hypothesis aftertheir insights
* Conversion! She'll live
A doctor with a basic hypothesiswithout the technology to seebeyond the obvious
We needbetter insights
We already use Google
Analytics
Isn't Google Analytics good enough?
Google Analytics is great but...
Even premium accounts get sampled reports when there are too many data (and not all the reports can be unsampled).
Google Analytics is great but...
If you need to manage advanced segments, and if you want to combine segments, it can get tricky.
And even if now you can get down to user level, it is really designed to show you aggregated data.
Google Analytics is great but...
It's not easy to cross data in Analytics with data from other sources (CRM, invoicing system...)
You can use Data Import, but there are many constraints to what you can do with it
Google Analytics is great but...
Good for knowing what's happening in your application, but difficult for:
* business intelligence/big data (data mining, finding patterns...)
* machine learning (classify information, predict future trends...)
image from http://www.asphaltandrubber.com/
What if Googlegave me accessto my whole
raw data
bigdata is cool but...
expensive cluster
hard to set up and monitor
not interactive enough
What if I could...
..query billions of rows in seconds..
..using a SQL-like web interface..
..on a fully managed cloud..
..paying only when I use it?
Designed to run analytics over huge volumes of raw data, and to integrate with other data sources
https://cloud.google.com/products/bigquery/
Google BigQuery
Google Analytics 360 users get free daily exports from GA to BigQuery.
Google BigQuery + GA 360
All your raw data.Unsampled.Use it however you want.
BOOM!
Google BigQuery + GA 360
integrating with external data sources
* Export from any source* Import into BigQuery
happier than a baby panda
on a rocking horse
data schema
it's just SQL
SELECT trafficSource.source, SUM( totals.transactions ) AS total_transactionsFROM [123456789.ga_sessions_20161001]GROUP BY trafficSource.sourceORDER BY total_transactions;
basic queries (metric/dimension)
SELECT device.deviceCategory, SUM ( totals.pageviews ) AS total_pageviewsFROM [123456789.ga_sessions_20161001]GROUP BY device.deviceCategoryORDER BY total_pageviews;
SELECT IF(DOMAIN(trafficSource.source) is null,
'n/a',DOMAIN(trafficSource.source))
AS normalized_source, SUM ( totals.transactions ) AS total_transactions
FROM [123456789.ga_sessions_20161001]GROUP BY normalized_sourceORDER BY total_transactions;
basic queries with a twist
SELECT ( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS avg_revenue_by_user_per_visitFROM ( SELECT SUM(totals.visits) AS total_visits_per_user, SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user, visitorId FROM [123456789.ga_sessions_20161001] WHERE totals.visits>0 AND totals.transactions>=1 AND totals.transactionRevenue IS NOT NULL GROUP BY visitorId ) ;
Average amount spent per visit
2 segments, combined
SELECT fullvisitorID, visitID, visitNumber, hits.page.pagePathFROM [123456789.ga_sessions_20161001]where hits.type='PAGE'order by fullvisitorID, visitID, hits.hitnumber asc
Identify user path/user actions
individual users' data is awesome
Cross CRM data with individual users actions to seehow your response to incidents affect your users.
Use the “frequently bought together” query and findusers who didn't buy the related products. Send ane-mail campaign with an offer for those products.
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantityFROM [123456789.ga_sessions_20161001]WHERE fullVisitorId IN ( SELECT fullVisitorId FROM [123456789.ga_sessions_20161001] WHERE hits.item.productName CONTAINS 'Light Helmet' AND totals.transactions>=1 GROUP BY fullVisitorId ) AND hits.item.productName IS NOT NULL AND hits.item.productName !='Light Helmet'GROUP BY other_purchased_productsORDER BY quantity DESC;
Users who bought product A,also bought product B
* example query from the BigQuery Cookbook for Google Analytics 360 https://support.google.com/analytics/answer/4419694?hl=en&ref_topic=3416089
SELECT prod_name, count(*) as transactions FROM (SELECT fullVisitorId, min(date) AS date, visitId, hits.item.productName as prod_nameFROM (SELECT fullVisitorId, date, visitId, totals.transactions, hits.item.productName FROM (TABLE_DATE_RANGE([dataset.ga_sessions_], TIMESTAMP('2014-06-01'), TIMESTAMP('2014-06-14'))) )WHERE fullVisitorId IN (SELECT fullVisitorIdFROM (TABLE_DATE_RANGE([dataset.ga_sessions_], TIMESTAMP('2014-06-01'), TIMESTAMP('2014-06-14'))) GROUP BY fullVisitorId HAVING SUM(totals.transactions) > 1 )AND hits.item.productName IS NOT NULLGROUP BY fullVisitorId, visitId, prod_name ORDER BY fullVisitorId DESC)GROUP BY prod_name ORDER BY transactions DESC;
* example query from the lunametrics blog. Check them out for more awesomeness
Products that are purchasedand lead to other products being purchased
Machinelearni...
Hipster!
ML with Google Dataproc
ML with Google ML (Tensorflow)
* prediction example from the Google cloud, big data, and machine learning blog https://cloud.google.com/blog/big-data/2016/05/
ML with Google ML (Tensorflow)
* prediction example from the Google cloud, big data, and machine learning blog https://cloud.google.com/blog/big-data/2016/05/
* prediction example from the Google cloud, big data, and machine learning blog https://cloud.google.com/blog/big-data/2016/05/
* prediction example from the Google cloud, big data, and machine learning blog https://cloud.google.com/blog/big-data/2016/05/
try it out at http://bit.ly/datastudio(login with a bigquery-enabled account required)
GA 360 users get a monthly $500 credit to use BigQueryStorage costs $0.02 per GB/month ($0.01 for data older than 90 days)
Querying costs $5 per TB. With the 1st TB free every month
For power-users BigQuery just released a query flat-rate
What if I don't havea GA 360 account?
just send your own data
define a data structure that fits your needs (or replicate the one GA provides)
use a JS snippet to send data to your server/BigQuery at the same time you send it to GA
Ready to Ready to dive into dive into your GA your GA data?data?
Need help with your data?https://teowaki.com
Thanks! Javier Ramirez@supercoco9