{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Preface\nI will be looking at some common digital marketing metrics by exploring the Google Analytics data of the Google Mercandise Store, an ecommerce site. I will primarily be using SQL and bq_helper to assess the data from BigQuery, along with some pandas and seaborn functionsfor additional processing and visualizations. "},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":1,"outputs":[{"output_type":"stream","text":"['train_v2.csv', 'test_v2.csv', 'sample_submission_v2.csv']\n","name":"stdout"}]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import bq_helper\n\n# https://www.kaggle.com/sohier/introduction-to-the-bq-helper-package\nbq_assistant = bq_helper.BigQueryHelper(\"bigquery-public-data\", \"google_analytics_sample\")\nprint(bq_assistant.list_tables()[:5])\nprint(bq_assistant.list_tables()[-5:])\ntable_names = bq_assistant.list_tables()\n# a table for each day : 2016-08-01 - 2017-08-01\n# a year's worth of data","execution_count":2,"outputs":[{"output_type":"stream","text":"Using Kaggle's public dataset BigQuery integration.\n['ga_sessions_20160801', 'ga_sessions_20160802', 'ga_sessions_20160803', 'ga_sessions_20160804', 'ga_sessions_20160805']\n['ga_sessions_20170728', 'ga_sessions_20170729', 'ga_sessions_20170730', 'ga_sessions_20170731', 'ga_sessions_20170801']\n","name":"stdout"}]},{"metadata":{},"cell_type":"markdown","source":"# Look at available columns"},{"metadata":{"trusted":true},"cell_type":"code","source":"bq_assistant.head(\"ga_sessions_20160801\", num_rows=5)\n# Many nested columns \n # BiQuery allows access via \"category_name.subcategory_name\"\n# What do rows represent?\n # Each row within a table corresponds to a session in Analytics 360.\n","execution_count":3,"outputs":[{"output_type":"execute_result","execution_count":3,"data":{"text/plain":" visitorId ... socialEngagementType\n0 None ... Not Socially Engaged\n1 None ... Not Socially Engaged\n2 None ... Not Socially Engaged\n3 None ... Not Socially Engaged\n4 None ... Not Socially Engaged\n\n[5 rows x 15 columns]","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
visitorIdvisitNumbervisitIdvisitStartTimedatetotalstrafficSourcedevicegeoNetworkcustomDimensionshitsfullVisitorIduserIdchannelGroupingsocialEngagementType
0None11470117657147011765720160801{'visits': 1, 'hits': 3, 'pageviews': 3, 'time...{'referralPath': '/yt/about/', 'campaign': '(n...{'browser': 'Internet Explorer', 'browserVersi...{'continent': 'Americas', 'subContinent': 'Nor...[{'index': 4, 'value': 'North America'}][{'hitNumber': 1, 'time': 0, 'hour': 23, 'minu...7194065619159478122NoneSocialNot Socially Engaged
1None1511470083489147008348920160801{'visits': 1, 'hits': 3, 'pageviews': 3, 'time...{'referralPath': '/yt/about/', 'campaign': '(n...{'browser': 'Chrome', 'browserVersion': 'not a...{'continent': 'Americas', 'subContinent': 'Nor...[{'index': 4, 'value': 'North America'}][{'hitNumber': 1, 'time': 0, 'hour': 13, 'minu...8159312408158297118NoneSocialNot Socially Engaged
2None11470052694147005269420160801{'visits': 1, 'hits': 4, 'pageviews': 3, 'time...{'referralPath': '/yt/about/', 'campaign': '(n...{'browser': 'Chrome', 'browserVersion': 'not a...{'continent': 'Asia', 'subContinent': 'Southea...[][{'hitNumber': 1, 'time': 0, 'hour': 4, 'minut...9236304747882138291NoneSocialNot Socially Engaged
3None11470061879147006187920160801{'visits': 1, 'hits': 4, 'pageviews': 4, 'time...{'referralPath': '/yt/about/', 'campaign': '(n...{'browser': 'Firefox', 'browserVersion': 'not ...{'continent': 'Americas', 'subContinent': 'Nor...[{'index': 4, 'value': 'North America'}][{'hitNumber': 1, 'time': 0, 'hour': 7, 'minut...1792676004815023069NoneSocialNot Socially Engaged
4None11470090830147009083020160801{'visits': 1, 'hits': 4, 'pageviews': 2, 'time...{'referralPath': '/yt/about/', 'campaign': '(n...{'browser': 'Internet Explorer', 'browserVersi...{'continent': 'Americas', 'subContinent': 'Nor...[{'index': 4, 'value': 'North America'}][{'hitNumber': 1, 'time': 0, 'hour': 15, 'minu...7305625498291809599NoneSocialNot Socially Engaged
\n
"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"Description of columns available at https://support.google.com/analytics/answer/3437719?hl=en \n\nNotable columns:\n1. fullVisitorId: str, unique visitor ID (hashed) \n2. visitID - int, identifier to session - only unique within user -- completely unique id is 1+2\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"last_schema = bq_assistant.table_schema(\"ga_sessions_20170801\")\nfirst_col_names = bq_assistant.table_schema(\"ga_sessions_20160801\")['name']\nlast_col_names = bq_assistant.table_schema(\"ga_sessions_20170801\")['name']\nprint(\"Number of columns in 2016:\", len(first_col_names))\nprint(\"Number of columns in 2017:\", len(last_col_names))","execution_count":4,"outputs":[{"output_type":"stream","text":"Number of columns in 2016: 336\nNumber of columns in 2017: 338\n","name":"stdout"}]},{"metadata":{"trusted":true},"cell_type":"code","source":"# print new columns \n[c for c in last_col_names if c not in first_col_names.tolist()]","execution_count":5,"outputs":[{"output_type":"execute_result","execution_count":5,"data":{"text/plain":"['hits.product.productCouponCode', 'clientId']"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def inspect(query, nrows=15, sample=False):\n \"\"\"Display response from given query but don't save. \n query: str, raw SQL query\n nrows: int, number of rows to display, default 15\n sample: bool, use df.sample instead of df.head, default False \"\"\"\n response = bq_assistant.query_to_pandas_safe(query, max_gb_scanned=10)\n if sample:\n return response.sample(nrows)\n return response.head(nrows) \n\ndef retrieve(query, nrows=10):\n \"\"\"Save response from given query and print a preview. \n query: str, raw SQL query\n nrows: int, number of rows to display\"\"\"\n response = bq_assistant.query_to_pandas_safe(query, max_gb_scanned=10)\n print(response.head(nrows))\n return response","execution_count":6,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Check for duplicates "},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT\n COUNT(*)\nFROM\n `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`\n\"\"\"\ninspect(query)","execution_count":7,"outputs":[{"output_type":"execute_result","execution_count":7,"data":{"text/plain":" f0_\n0 1711","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n
f0_
01711
\n
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT COUNT(*)\nFROM \n (SELECT DISTINCT fullVisitorId, visitID\n FROM\n `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`) s\n\"\"\"\ninspect(query)","execution_count":8,"outputs":[{"output_type":"execute_result","execution_count":8,"data":{"text/plain":" f0_\n0 1711","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n
f0_
01711
\n
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Total Sessions\nquery = \"\"\"\nSELECT\n COUNT(*)\nFROM\n `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n\"\"\"\ninspect(query)\n# The wild card parses to a UNION ALL","execution_count":9,"outputs":[{"output_type":"execute_result","execution_count":9,"data":{"text/plain":" f0_\n0 903653","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n
f0_
0903653
\n
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Unique sessions \nquery = \"\"\"\nSELECT COUNT(*)\nFROM \n (SELECT DISTINCT fullVisitorId, visitID\n FROM\n `bigquery-public-data.google_analytics_sample.ga_sessions_*`) s\n\"\"\"\ninspect(query)\n\n# A few(~900) sessions don't have unique ID's if looking across all tables","execution_count":10,"outputs":[{"output_type":"execute_result","execution_count":10,"data":{"text/plain":" f0_\n0 902755","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n
f0_
0902755
\n
"},"metadata":{}}]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# Quick Check of alternative method Unique sessions \nquery = \"\"\"\nSELECT COUNT(*)\nFROM \n (SELECT DISTINCT concat(fullVisitorId, cast(visitID as string))\n FROM\n `bigquery-public-data.google_analytics_sample.ga_sessions_*`) s\n\"\"\"\ninspect(query)","execution_count":11,"outputs":[{"output_type":"execute_result","execution_count":11,"data":{"text/plain":" f0_\n0 902755","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n
f0_
0902755
\n
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Test alt way to count distinct \ntable = table_names[0]\nprint(table)\ndate = table[-8:]\nprint(date)\nquery = f\"\"\"\nSELECT {date} as table, \nCOUNT(*) as total, \ncount(DISTINCT CONCAT(fullVisitorId, CAST(visitID as string))) as unique\nFROM \n `bigquery-public-data.google_analytics_sample.{table}`\n\"\"\"\ninspect(query)","execution_count":12,"outputs":[{"output_type":"stream","text":"ga_sessions_20160801\n20160801\n","name":"stdout"},{"output_type":"execute_result","execution_count":12,"data":{"text/plain":" table total unique\n0 20160801 1711 1711","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
tabletotalunique
02016080117111711
\n
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"# ' UNION '.join(['A', 'B', 'C'])","execution_count":13,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Create a bunch of queries with a loop\n# queries = []\n# for table in table_names:\n# date = table[-8:]\n# query = f\"\"\"SELECT {date}, COUNT(*) as total, count(DISTINCT CONCAT(fullVisitorId, CAST(visitID as string))) as unique FROM `bigquery-public-data.google_analytics_sample.{table}`\"\"\"\n# queries.append(query)\n# queries[:5]","execution_count":14,"outputs":[]},{"metadata":{"_kg_hide-input":false,"trusted":true},"cell_type":"code","source":"# %%time # wall time 9 mins\n\n# tables = []\n# mismatches = []\n# for query in queries:\n# table_info = bq_assistant.query_to_pandas_safe(query)\n# tables.append(table_info)\n# if table_info.loc[0, 'total'] != table_info.loc[0, 'unique']:\n# print(table_info['date'])\n# mismatches.append(table_info)","execution_count":15,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# # List of tables with record counts\n# table_records = pd.concat(tables, axis=0)\n# table_records","execution_count":16,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Test unnesting- needed since it's a list within the column\nunnested_query = \"\"\"\nSELECT\n fullVisitorId,\n visitId,\n visitNumber,\n hits.hitNumber AS hitNumber,\n hits.page.pagePath AS pagePath\nFROM\n `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`, UNNEST(hits) as hits\nWHERE\n hits.type=\"PAGE\"\nORDER BY\n fullVisitorId,\n visitId,\n visitNumber,\n hitNumber\n\"\"\"\nunnested_query_df = retrieve(unnested_query)\nunnested_query_df\n\n","execution_count":17,"outputs":[{"output_type":"stream","text":" fullVisitorId ... pagePath\n0 000722514342430295 ... /home\n1 000722514342430295 ... /google+redesign/apparel\n2 000722514342430295 ... /asearch.html\n3 000722514342430295 ... /asearch.html\n4 0014659935183303341 ... /home\n5 0015694432801235877 ... /home\n6 0015694432801235877 ... /google+redesign/electronics\n7 0015694432801235877 ... /google+redesign/apparel/men++s/men++s+t+shirts\n8 0015694432801235877 ... /google+redesign/apparel/kid+s/kid+s+infant\n9 0015694432801235877 ... /google+redesign/apparel/kid+s/kid+s+infant/qu...\n\n[10 rows x 5 columns]\n","name":"stdout"},{"output_type":"execute_result","execution_count":17,"data":{"text/plain":" fullVisitorId ... pagePath\n0 000722514342430295 ... /home\n1 000722514342430295 ... /google+redesign/apparel\n2 000722514342430295 ... /asearch.html\n3 000722514342430295 ... /asearch.html\n4 0014659935183303341 ... /home\n5 0015694432801235877 ... /home\n6 0015694432801235877 ... /google+redesign/electronics\n7 0015694432801235877 ... /google+redesign/apparel/men++s/men++s+t+shirts\n8 0015694432801235877 ... /google+redesign/apparel/kid+s/kid+s+infant\n9 0015694432801235877 ... /google+redesign/apparel/kid+s/kid+s+infant/qu...\n10 0015694432801235877 ... /google+redesign/lifestyle\n11 0015694432801235877 ... /google+redesign/apparel/men++s/men++s+outerwear\n12 001747219368939605 ... /google+redesign/apparel/men++s/men++s+t+shirts\n13 001747219368939605 ... /home\n14 001747219368939605 ... /google+redesign/apparel/men++s\n15 001747219368939605 ... /google+redesign/bags\n16 001747219368939605 ... /google+redesign/drinkware\n17 001747219368939605 ... /google+redesign/drinkware/quickview\n18 001747219368939605 ... /google+redesign/electronics\n19 001747219368939605 ... /google+redesign/lifestyle\n20 001747219368939605 ... /google+redesign/lifestyle/quickview\n21 001747219368939605 ... /google+redesign/lifestyle/quickview\n22 001747219368939605 ... /google+redesign/lifestyle/quickview\n23 001747219368939605 ... /google+redesign/office\n24 001747219368939605 ... /google+redesign/apparel/kid+s/kid+s+infant\n25 001747219368939605 ... /home\n26 0018526595751613673 ... /home\n27 0018526595751613673 ... /google+redesign/bags/backpacks\n28 0018526595751613673 ... /google+redesign/gift+cards\n29 0018526595751613673 ... /google+redesign/limited+supply\n... ... ... ...\n9813 9998597322098588317 ... /basket.html\n9814 9998597322098588317 ... /google+redesign/electronics\n9815 9998597322098588317 ... /google+redesign/lifestyle\n9816 9998597322098588317 ... /google+redesign/office\n9817 9998597322098588317 ... /google+redesign/office/quickview\n9818 9998597322098588317 ... /basket.html\n9819 9998597322098588317 ... /google+redesign/office/other/unlimited+badge+...\n9820 9998597322098588317 ... /asearch.html\n9821 9998597322098588317 ... /google+redesign/apparel/women+s/women+s+t+shirts\n9822 9998597322098588317 ... /google+redesign/bags\n9823 9998597322098588317 ... /google+redesign/drinkware\n9824 9998597322098588317 ... /basket.html\n9825 9998597322098588317 ... /signin.html\n9826 9998597322098588317 ... /signin.html\n9827 9998597322098588317 ... /signin.html\n9828 9998597322098588317 ... /register.html\n9829 9998597322098588317 ... /register.html\n9830 9998597322098588317 ... /payment.html\n9831 9998597322098588317 ... /payment.html\n9832 9998597322098588317 ... /payment.html\n9833 9998597322098588317 ... /payment.html\n9834 9998597322098588317 ... /basket.html\n9835 9998597322098588317 ... /yourinfo.html\n9836 9998597322098588317 ... /payment.html\n9837 9998597322098588317 ... /revieworder.html\n9838 9998597322098588317 ... /ordercompleted.html\n9839 9998597322098588317 ... /ordercompleted.html\n9840 9998597322098588317 ... /myaccount.html?mode=vieworder\n9841 9998597322098588317 ... /wishlist.html\n9842 9998597322098588317 ... /google+redesign/office\n\n[9843 rows x 5 columns]","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
fullVisitorIdvisitIdvisitNumberhitNumberpagePath
0000722514342430295147009372711/home
1000722514342430295147009372713/google+redesign/apparel
2000722514342430295147009372714/asearch.html
3000722514342430295147009372715/asearch.html
40014659935183303341147003728211/home
50015694432801235877147004373211/home
60015694432801235877147004373212/google+redesign/electronics
70015694432801235877147004373213/google+redesign/apparel/men++s/men++s+t+shirts
80015694432801235877147004373214/google+redesign/apparel/kid+s/kid+s+infant
90015694432801235877147004373215/google+redesign/apparel/kid+s/kid+s+infant/qu...
100015694432801235877147004373217/google+redesign/lifestyle
110015694432801235877147004373218/google+redesign/apparel/men++s/men++s+outerwear
12001747219368939605147008014911/google+redesign/apparel/men++s/men++s+t+shirts
13001747219368939605147008014912/home
14001747219368939605147008014913/google+redesign/apparel/men++s
15001747219368939605147008014914/google+redesign/bags
16001747219368939605147008014915/google+redesign/drinkware
17001747219368939605147008014917/google+redesign/drinkware/quickview
18001747219368939605147008014918/google+redesign/electronics
19001747219368939605147008014919/google+redesign/lifestyle
200017472193689396051470080149111/google+redesign/lifestyle/quickview
210017472193689396051470080149112/google+redesign/lifestyle/quickview
220017472193689396051470080149115/google+redesign/lifestyle/quickview
230017472193689396051470080149116/google+redesign/office
240017472193689396051470080149117/google+redesign/apparel/kid+s/kid+s+infant
250017472193689396051470080149118/home
260018526595751613673147005107011/home
270018526595751613673147005107013/google+redesign/bags/backpacks
280018526595751613673147005107014/google+redesign/gift+cards
290018526595751613673147005107015/google+redesign/limited+supply
..................
98139998597322098588317147008600616/basket.html
98149998597322098588317147008600617/google+redesign/electronics
98159998597322098588317147008600618/google+redesign/lifestyle
98169998597322098588317147008600619/google+redesign/office
981799985973220985883171470086006111/google+redesign/office/quickview
981899985973220985883171470086006113/basket.html
981999985973220985883171470086006114/google+redesign/office/other/unlimited+badge+...
982099985973220985883171470086006115/asearch.html
982199985973220985883171470086006116/google+redesign/apparel/women+s/women+s+t+shirts
982299985973220985883171470086006117/google+redesign/bags
982399985973220985883171470086006118/google+redesign/drinkware
982499985973220985883171470086006119/basket.html
982599985973220985883171470086006120/signin.html
982699985973220985883171470086006121/signin.html
982799985973220985883171470086006122/signin.html
982899985973220985883171470086006123/register.html
982999985973220985883171470086006124/register.html
983099985973220985883171470086006125/payment.html
983199985973220985883171470086006126/payment.html
983299985973220985883171470086006127/payment.html
983399985973220985883171470086006128/payment.html
983499985973220985883171470086006129/basket.html
983599985973220985883171470086006130/yourinfo.html
983699985973220985883171470086006131/payment.html
983799985973220985883171470086006132/revieworder.html
983899985973220985883171470086006133/ordercompleted.html
983999985973220985883171470086006134/ordercompleted.html
984099985973220985883171470086006135/myaccount.html?mode=vieworder
984199985973220985883171470086006136/wishlist.html
984299985973220985883171470086006137/google+redesign/office
\n

9843 rows × 5 columns

\n
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"# How many unique visitors ?\nquery = \"\"\" SELECT COUNT (DISTINCT fullVisitorId) unique_visitors\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` \n \"\"\"\ninspect(query)","execution_count":18,"outputs":[{"output_type":"execute_result","execution_count":18,"data":{"text/plain":" unique_visitors\n0 714167","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n
unique_visitors
0714167
\n
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"# How many unique customers ?\nquery = \"\"\" SELECT COUNT (DISTINCT fullVisitorId) unique_visitors\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n WHERE totals.transactions > 0 \n \"\"\"\ninspect(query)","execution_count":19,"outputs":[{"output_type":"execute_result","execution_count":19,"data":{"text/plain":" unique_visitors\n0 10022","text/html":"
\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n
unique_visitors
010022
\n
"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Repeat customers and how many sessions with a purchase\nquery = \"\"\" SELECT fullVisitorId, COUNT(DISTINCT visitId) cnt_purchases\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n WHERE totals.transactions > 0 \n GROUP BY fullVisitorId\n HAVING COUNT(DISTINCT visitId) > 1\n \"\"\"\n \nrepeat_customers = retrieve(query)\n","execution_count":20,"outputs":[{"output_type":"stream","text":" fullVisitorId cnt_purchases\n0 4869304818683909994 2\n1 773808986747306437 2\n2 743123551680199202 2\n3 832152661091318994 2\n4 6095240958469403590 2\n5 4988517937139937145 5\n6 8205269849536680674 2\n7 7594157089401877562 2\n8 8039691484832064792 2\n9 5341271361784611942 5\n","name":"stdout"}]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(repeat_customers.shape)\nrepeat_customers['cnt_purchases'].sum()\n","execution_count":21,"outputs":[{"output_type":"stream","text":"(970, 2)\n","name":"stdout"},{"output_type":"execute_result","execution_count":21,"data":{"text/plain":"2499"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Inspiration Questions:\n\n1. [What is the total number of transactions generated per device browser in July 2017?](#insp1)\n2. [The real bounce rate is defined as the percentage of visits with a single pageview. What was the real bounce rate per traffic source?](#insp2)\n3. [What was the average number of product pageviews for users who made a purchase in July 2017?](#insp3)\n4. [What was the average number of product pageviews for users who did not make a purchase in July 2017?](#insp4)\n5. [What was the average total transactions per user that made a purchase in July 2017?](#insp5)\n6. [What is the average amount of money spent per session in July 2017?](#insp6)\n7. [What is the sequence of pages viewed?](#insp7)"},{"metadata":{"trusted":true},"cell_type":"markdown","source":" \nWhat is the total number of transactions generated per device browser in July 2017?"},{"metadata":{"trusted":true},"cell_type":"code","source":"# group by device browser from july tables \nquery = f\"\"\"\nSELECT device.browser, \n COUNT(totals.totalTransactionRevenue) AS cnt_revenue,\n COUNT(totals.transactions) AS cnt_transactions, \n COUNT(*) AS cnt_all_rows, --sessions with browser\n COUNT(totals.transactions) / COUNT(*) AS sess_conversion_rate,\n sum(totals.totalTransactionRevenue) AS sum_revenue, \n Sum(totals.transactions) AS sum_transactions,\n sum(totals.totalTransactionRevenue) / Sum(totals.transactions) AS revenue_per_transaction\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*` \n GROUP BY device.browser\n ORDER BY sum_transactions DESC\n\"\"\"\n\ntransactions_by_browser = retrieve(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"transactions_by_browser.head(10)\n# Most transactions are done through Chrome. It also has the highest session-to-transaction conversion rate and revenue per transaction.","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" \nThe real bounce rate is defined as the percentage of visits with a single pageview. What was the real bounce rate per traffic source?"},{"metadata":{"trusted":true},"cell_type":"code","source":"# \"Real\" bounce rate -- go by definition and filter where total.pageviews = 1 and divide by total visits\nquery = \"\"\"\nSELECT t.source,\n t.total_visits,\n b.bounce_visits,\n 100 * b.bounce_visits / t.total_visits AS bounce_rate\nFROM \n(SELECT trafficSource.source, COUNT(visitId) AS total_visits \nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nGROUP BY trafficSource.source) t\n\nJOIN (SELECT trafficSource.source, COUNT(visitId) bounce_visits \nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE totals.pageviews = 1\nGROUP BY trafficSource.source) b\nON t.source = b.source\nORDER by total_visits DESC\n\n\"\"\"\ninspect(query)\n\n# Slightly different results than using totals.bounces column - more bounces visits but total visits are the same","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# GA bounce rate \nquery = \"\"\"\nSELECT trafficSource.source, \n COUNT(visitId) AS total_visits,\n COUNT(totals.bounces) AS bounce_visits,\n 100 * COUNT(totals.bounces) / COUNT(visitId) AS bounce_rate\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nGROUP BY trafficSource.source\nORDER BY total_visits DESC\n\"\"\"\n\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT DISTINCT totals.pageviews AS pageview_values\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE totals.bounces= 1\n\"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT DISTINCT totals.bounces AS bounce_values\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE totals.pageviews= 1\n\"\"\"\ninspect(query)\n\n# total.bounces column - for convenience but is sometimes null for a session with 1 pageview -- explains the extra bounces ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Cofirm same results as other kernel\nhowto_query = \"\"\"SELECT\nsource,\ntotal_visits,\ntotal_no_of_bounces,\n( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate\nFROM (\nSELECT\ntrafficSource.source AS source,\nCOUNT ( trafficSource.source ) AS total_visits,\nSUM ( totals.bounces ) AS total_no_of_bounces\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\nWHERE\n_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'\nGROUP BY\nsource )\nORDER BY\ntotal_visits DESC;\n \"\"\"\ninspect(howto_query)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" \nWhat was the average number of product pageviews for users who made a purchase in July 2017?\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"# get users who made a purchase in July 2017\nquery = \"\"\"\nSELECT DISTINCT fullVisitorId \nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE totals.transactions > 0\n\"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# more info on users who made a purchase in July 2017\nquery = \"\"\"\nSELECT \n COUNT(fullVisitorId) AS total_visitors, \n COUNT(DISTINCT fullVisitorId) AS unique_visitors, \n SUM(totals.transactions) AS sum_transactions,\n SUM(totals.totalTransactionRevenue) AS sum_revenue,\n AVG(totals.pageviews) as avg_pageviews -- in sessions where purchase was made\n\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE totals.transactions > 0\n\"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# calculate average among those users who made a purchase in July 2017\nquery = \"\"\"\nSELECT AVG(totals.pageviews) as avg_pageviews -- includes other sessions by user in which they did not make a purchase\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE fullVisitorId IN (SELECT DISTINCT fullVisitorId \n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\n WHERE totals.transactions > 0)\n\"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" \n\nWhat was the average number of product pageviews for users who did not make a purchase in July 2017? \nSlight differences in the average for both questions 3 and 4 depending on interpretation of question but overall trend is that users who make a purchase have more than 3 times the page views as users who do not make a purchase."},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT \n COUNT(fullVisitorId) AS total_visitors, \n COUNT(DISTINCT fullVisitorId) AS unique_visitors, \n SUM(totals.transactions) AS sum_transactions,\n SUM(totals.totalTransactionRevenue) AS sum_revenue,\n AVG(totals.pageviews) as avg_pageviews -- in sessions where purchase were not made\n\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE totals.transactions IS NULL\n\"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# calculate average among those users who did not made a purchase in July 2017\nquery = \"\"\"\nSELECT AVG(totals.pageviews) as avg_pageviews -- includes other sessions by user in which they did make a purchase\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE fullVisitorId IN (SELECT DISTINCT fullVisitorId \n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\n WHERE totals.transactions IS NULL)\n\"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" \n\nWhat was the average total transactions per user that made a purchase in July 2017?\n\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT \n COUNT(fullVisitorId) AS total_visitors, \n COUNT(DISTINCT fullVisitorId) AS unique_visitors, \n SUM(totals.transactions) AS sum_transactions,\n AVG(totals.totalTransactionRevenue) AS sum_revenue,\n AVG(totals.transactions) as avg_transactions, -- in sessions where purchase were made\n SUM(totals.transactions) / COUNT(fullVisitorId) as alt_avg_transactions\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE totals.transactions > 0\n\"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# calculate average among those users who made a purchase in July 2017\nquery = \"\"\"\nSELECT AVG(totals.transactions) as avg_transactions, -- includes other sessions by user in which they did not make a purchase\n SUM(totals.transactions) / COUNT(fullVisitorId) as alt_avg_transactions -- AVG function ignores NULLs by default\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE fullVisitorId IN (SELECT DISTINCT fullVisitorId \n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\n WHERE totals.transactions > 0)\n\"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" \nWhat is the average amount of money spent per session in July 2017?\n\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT \n AVG(totals.totalTransactionRevenue) as avg_money_spent_per_purchase,\n SUM(totals.totalTransactionRevenue) / COUNT(*) as avg_money_spent\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\n\"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":" \nhowto_query6 = \"\"\"SELECT\n( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS\navg_revenue_by_user_per_visit\nFROM (\nSELECT\nfullVisitorId,\nSUM( totals.visits ) AS total_visits_per_user,\nSUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user\nFROM\n`bigquery-public-data.google_analytics_sample.ga_sessions_*`\nWHERE\n_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'\nAND\ntotals.visits > 0\nAND totals.transactions >= 1\nAND totals.transactionRevenue IS NOT NULL\nGROUP BY\nfullVisitorId );\n\"\"\"\ninspect(howto_query6)\n# Why group by VisitorId (ie user) when it asks for money spent per session? ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT \n COUNT(totals.visits) as visits,\n COUNT(*) - COUNT(totals.visits) as sessions_not_visits\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\n--WHERE totals.visits 1\n\"\"\"\ninspect(query) \n# so all sessions are visits, as it should be ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" \nWhat is the sequence of pages viewed?"},{"metadata":{"trusted":true},"cell_type":"code","source":"howto_query7 = \"\"\"SELECT\nfullVisitorId,\nvisitId,\nvisitNumber,\nhits.hitNumber AS hitNumber,\nhits.page.pagePath AS pagePath\nFROM\n`bigquery-public-data.google_analytics_sample.ga_sessions_*`,\nUNNEST(hits) as hits\nWHERE\n_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'\nAND\nhits.type=\"PAGE\"\nORDER BY\nfullVisitorId,\nvisitId,\nvisitNumber,\nhitNumber;\n \"\"\"\nview_seqs = retrieve(howto_query7)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"print(view_seqs.shape)\nview_seqs.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# hitNumber has the order within the session\n# view_seqs.groupby('fullVisitorId')['visitNumber'].count() # find a sample customer with more than 1 visit\nview_seqs[view_seqs['fullVisitorId'] == '0000436683523507380']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# apply list to each group - a way to aggregate the different records into the same row\n# nice to see but not recommended to store a list in a dataframe\nview_seqs.groupby(['fullVisitorId', 'visitId']).agg({'hitNumber': list, 'pagePath': list}).head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get first page from each session - could be nice to use for first touch attribution \n# view_seqs.groupby(['fullVisitorId', 'visitId']).head(1)\n# Top 10 landing pages from sessions - homepage 50% of time\nview_seqs.groupby(['fullVisitorId', 'visitId']).head(1)['pagePath'].value_counts(normalize=True).head(10)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Exploring other columns\nSticking with July 2017\n1. trafficSource.source\n2. trafficSource.medium\n3. device.browser\n4. device.deviceCategory\n5. totals.sessionQualityDim\n6. totals.timeOnScreen\n"},{"metadata":{},"cell_type":"markdown","source":"### TrafficSource"},{"metadata":{"trusted":true},"cell_type":"code","source":"# expand traffic source \nquery = \"\"\"SELECT\nfullVisitorId,\nvisitId,\nvisitNumber,\nhits.hitNumber AS hitNumber,\ntrafficSource.*\nFROM\n`bigquery-public-data.google_analytics_sample.ga_sessions_*`,\nUNNEST(hits) as hits\nWHERE\n_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'\n;\n \"\"\"\ninspect(query)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# What are possible sources? in July 2017 value_counts\nquery = \"\"\"SELECT\ntrafficSource.source, COUNT(*) as cnt\nFROM\n`bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nGROUP BY trafficSource.source\nORDER BY cnt DESC\n \"\"\"\ntraffic_sources = retrieve(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(traffic_sources.shape)\nprint(\"Too many to plot, even if establishing a threshold at like 100 for July. Need a good way of combining sources\")\ntraffic_sources.head(15)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# confirm (direct) is the source of the (none)'s\nquery = \"\"\"SELECT\n trafficSource.source, trafficSource.medium, count(*) cnt\nFROM\n`bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nWHERE -- trafficSource.source IN ('google', '(direct)') AND \n trafficSource.medium = '(none)'\nGROUP BY 1,2\nORDER BY cnt DESC\n\n \"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### What are the possible values for various columns of interest?"},{"metadata":{"trusted":true},"cell_type":"code","source":"# What are possible media? in 2017 value_counts\nquery = \"\"\"SELECT\ntrafficSource.medium, COUNT(*) as cnt\nFROM\n`bigquery-public-data.google_analytics_sample.ga_sessions_2017*`\nGROUP BY trafficSource.medium\nORDER BY cnt DESC\n \"\"\"\ninspect(query)\n# cpc: cost per click - use when has good offer with high conversion rate using ads adapted to that specific offer\n# cpm: cost per thousand impression - may save money if good CTR, can buy traffic from premium spots\n# cpv = cost per unique visitor","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# What are possible devices? value_counts\nquery = \"\"\"SELECT\ndevice.browser, COUNT(*) as cnt\nFROM\n`bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nGROUP BY device.browser\nORDER BY cnt DESC\n \"\"\"\ninspect(query)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# What are possible device categories? value_counts\nquery = \"\"\"SELECT\ndevice.deviceCategory, COUNT(*) as cnt\nFROM\n`bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nGROUP BY device.deviceCategory\nORDER BY cnt DESC\n \"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# What are possible browsers? value_counts -- counts unique users - but session is more relevant\nquery = \"\"\"\nSELECT\n deviceCategory, \n COUNT(*) as cnt\nFROM\n (SELECT \n fullVisitorId,\n visitId,\n MAX(device.deviceCategory) as deviceCategory\n FROM\n `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\n GROUP BY fullVisitorId, visitId\n ) s\nGROUP BY deviceCategory\nORDER BY cnt DESC\n \"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**totals.sessionQualityDim**: An estimate of how close a particular session was to transacting, ranging from 1 to 100, calculated for each session. A value closer to 1 indicates a low session quality, or far from transacting, while a value closer to 100 indicates a high session quality, or very close to transacting. A value of 0 indicates that Session Quality is not calculated for the selected time range.\n\n

How well does it predict actual conversion/transacting? Come back to this.

"},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT \n totals.sessionQualityDim,\n COUNT(*) cnt\nFROM \n`bigquery-public-data.google_analytics_sample.ga_sessions_201707*`\nGROUP BY totals.sessionQualityDim\nORDER by cnt DESC\n\n\"\"\"\nsess_quality_vc = retrieve(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import seaborn as sns\nsns.set()\nsns.scatterplot(x='sessionQualityDim', y='cnt', data=sess_quality_vc[sess_quality_vc['sessionQualityDim'] > 2])\n# exclude the two ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sess_quality_vc.sort_values('sessionQualityDim').tail(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT \n totals.timeOnSite,\n COUNT(*) cnt\nFROM \n`bigquery-public-data.google_analytics_sample.ga_sessions_2017*`\nGROUP BY totals.timeOnSite\nORDER by cnt DESC\n\n\"\"\"\ntime_on_site = retrieve(query) \n# totals.timeOnScreen - not a valid column\n# timeOnSite: Total time of the session expressed in seconds.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"time_on_site['timeOnSite'].describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Having found interesting columns to explore, let's see how they might affect conversion rate.\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Base conversion rate in July\nquery = f\"\"\"\nSELECT \n COUNT(totals.totalTransactionRevenue) AS cnt_revenue,\n COUNT(totals.transactions) AS cnt_transactions, \n COUNT(*) AS cnt_all_rows, --sessions with browser\n COUNT(totals.transactions) / COUNT(*) AS sess_conversion_rate,\n sum(totals.totalTransactionRevenue) AS sum_revenue, \n Sum(totals.transactions) AS sum_transactions,\n sum(totals.totalTransactionRevenue) / Sum(totals.transactions) AS revenue_per_transaction\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*` \n\"\"\"\n\nbase_CR = retrieve(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"base_CR","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Base conversion rate by month "},{"metadata":{"trusted":true},"cell_type":"code","source":"# Base conversion rate by month \nquery = f\"\"\"\nSELECT \n substr(date, 1, 6) as ym,\n COUNT(totals.totalTransactionRevenue) AS cnt_revenue,\n COUNT(totals.transactions) AS cnt_transactions, \n COUNT(*) AS cnt_all_rows, --sessions with browser\n COUNT(totals.transactions) / COUNT(*) AS sess_conversion_rate,\n sum(totals.totalTransactionRevenue) AS sum_revenue, \n Sum(totals.transactions) AS sum_transactions,\n sum(totals.totalTransactionRevenue) / Sum(totals.transactions) AS revenue_per_transaction\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions*` \nGROUP BY ym\nORDER BY ym\n\"\"\"\n\nmonthly_base_CR = retrieve(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"monthly_base_CR\n# Not all transactionshave revenue? ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"monthly_base_CR['ym'] = pd.to_datetime(monthly_base_CR['ym'], format='%Y%m')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.lineplot(x='ym', y='sess_conversion_rate', data=monthly_base_CR)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.lineplot(x='ym', y='cnt_all_rows', data=monthly_base_CR)\n# the big CR dip in Nov is due more to higher traffic than a drop in transactions","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Base conversion rate by month and device category"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Base conversion rate by month and device category\nquery = \"\"\"\nSELECT \n substr(date, 1, 6) as ym,\n device.deviceCategory,\n COUNT(totals.totalTransactionRevenue) AS cnt_revenue,\n COUNT(totals.transactions) AS cnt_transactions, \n COUNT(*) AS cnt_all_rows, --sessions with browser\n COUNT(totals.transactions) / COUNT(*) AS sess_conversion_rate,\n sum(totals.totalTransactionRevenue) AS sum_revenue, \n Sum(totals.transactions) AS sum_transactions,\n sum(totals.totalTransactionRevenue) / Sum(totals.transactions) AS revenue_per_transaction\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\nGROUP BY device.deviceCategory, ym\nORDER BY device.deviceCategory, ym\n\"\"\"\nmonthly_device_CR = retrieve(query)\nmonthly_device_CR.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import matplotlib.pyplot as plt\ndef plot_metric_by_month(vc_df, metric, group):\n '''Convert date from string and plot metric by group\n vc_df: df, SQL value counts output grouped by month and group\n metric: str, column name for metric of interest\n group: str, column name of group'''\n df = vc_df.copy()\n df['ym'] = pd.to_datetime( df['ym'], format='%Y%m')\n sns.lineplot(x='ym', y=metric, hue=group, data=df, marker='o')\n plt.title(f'Monthly {metric}')\n plt.legend(loc=\"upper left\", bbox_to_anchor=(1,1))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_device_CR, 'sess_conversion_rate', 'deviceCategory')\n# much higher CR when visited via desktop","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_device_CR, 'cnt_transactions', 'deviceCategory')\n# raw count looks similar in shape for desktop and mobile, so likely consistent total traffic but changing number of transactions","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Base conversion rate by month and traffic source"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Base conversion rate by month and traffic source\nquery = \"\"\"\nSELECT \n substr(date, 1, 6) as ym,\n trafficSource.medium, \n COUNT(totals.totalTransactionRevenue) AS cnt_revenue,\n COUNT(totals.transactions) AS cnt_transactions, \n COUNT(*) AS cnt_all_rows, --sessions with browser\n COUNT(totals.transactions) / COUNT(*) AS sess_conversion_rate,\n sum(totals.totalTransactionRevenue) AS sum_revenue, \n Sum(totals.transactions) AS sum_transactions,\n sum(totals.totalTransactionRevenue) / Sum(totals.transactions) AS revenue_per_transaction\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\nGROUP BY trafficSource.medium, ym\nORDER BY trafficSource.medium, ym\n\"\"\"\nmonthly_medium_CR = retrieve(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_medium_CR, 'sess_conversion_rate', 'medium')\n# direct ads (cpc and cpm) > organic > referral/affiliate\n# medium can have a pretty strong effect on CR - diverging from the overall average of 1.5% ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_medium_CR, 'cnt_transactions', 'medium')\n# organic has a low CR but it makes up for a good chunk of the transactions\n# Need to look further into (none) --> direct traffic - so people who want to buy stuff often visit directly \n# - bookmarks? returning customers?","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Base conversion rate by month and browser "},{"metadata":{"trusted":true},"cell_type":"code","source":"# Base conversion rate by month and browser \nquery = \"\"\"\nSELECT \n substr(date, 1, 6) as ym,\n device.browser,\n COUNT(totals.totalTransactionRevenue) AS cnt_revenue,\n COUNT(totals.transactions) AS cnt_transactions, \n COUNT(*) AS cnt_all_rows, --sessions with browser\n COUNT(totals.transactions) / COUNT(*) AS sess_conversion_rate,\n sum(totals.totalTransactionRevenue) AS sum_revenue, \n Sum(totals.transactions) AS sum_transactions,\n sum(totals.totalTransactionRevenue) / Sum(totals.transactions) AS revenue_per_transaction\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\nGROUP BY device.browser, ym\nORDER BY device.browser, ym\n\"\"\"\nmonthly_browser_CR = retrieve(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mask = (monthly_browser_CR['sess_conversion_rate'] > 0) | (monthly_browser_CR['cnt_all_rows'] > 1000) # doesn't catch every month if a browser doesn't always make it\nprint(len(monthly_browser_CR[mask]))\nmonthly_browser_CR[mask].sample(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_browser_CR[mask], 'sess_conversion_rate', 'browser')\n# Chrome dominates both raw count and CR\n# Silk has that one blip of relatively high CR","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_browser_CR[mask], 'cnt_transactions', 'browser')\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_browser_CR[mask & (monthly_browser_CR['browser'] != 'Chrome')], 'cnt_transactions', 'browser')\n# Safari being a distant second but another sizeable gap from the rest","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Base conversion rate by month and source"},{"metadata":{"trusted":true},"cell_type":"code","source":"\n# Base conversion rate by month and source\nquery = \"\"\"\nSELECT \n substr(date, 1, 6) as ym,\n trafficSource.source,\n COUNT(totals.totalTransactionRevenue) AS cnt_revenue,\n COUNT(totals.transactions) AS cnt_transactions, \n COUNT(*) AS cnt_all_rows, --sessions with browser\n COUNT(totals.transactions) / COUNT(*) AS sess_conversion_rate,\n sum(totals.totalTransactionRevenue) AS sum_revenue, \n Sum(totals.transactions) AS sum_transactions,\n sum(totals.totalTransactionRevenue) / Sum(totals.transactions) AS revenue_per_transaction\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\nGROUP BY trafficSource.source, ym\nORDER BY trafficSource.source, ym\n\"\"\"\nmonthly_source_CR = retrieve(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"monthly_source_CR.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"monthly_source_CR.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mask = (monthly_source_CR['cnt_all_rows'] > 500) \nplot_metric_by_month(monthly_source_CR[mask], 'sess_conversion_rate', 'source')\n# dominated by direct or google\n# not sure what dfa is","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mask = (monthly_source_CR['cnt_all_rows'] > 500) \nplot_metric_by_month(monthly_source_CR[mask], 'sess_conversion_rate', 'source')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We've found that conversion rate and the number of transactions have associations with the traffic source as well the device and browser used for the visit. Direct visits and paid ads appear to be positively correlated with conversion ratios. Chrome users make up an overwhelming majority of conversions. \n\n"},{"metadata":{},"cell_type":"markdown","source":"# To do's\n

This is a rich data set, so a lot remains to be explored. This includes looking at different campaigns, closer looks into specific audiences, other definitions of conversion, other metrics like ROI and ROAS. An interesting feature is the totals.sessionQualityDim, which is GA's estimate on how close a session came to converting. There are other ones like totals.timeOnScreen, which may be more a vanity metric but could be insightful on the user experience.

"},{"metadata":{},"cell_type":"markdown","source":"Misc Queries\n\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"# looking into a particuar visitor\nquery = \"\"\"SELECT date, visitId, channelGrouping, visitNumber, trafficSource.medium, \n totals.*\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n WHERE fullVisitorId = '7813149961404844386'\n ORDER BY date \n \n \"\"\"\nq = retrieve(query)\n\n","execution_count":22,"outputs":[{"output_type":"stream","text":" date ... sessionQualityDim\n0 20161117 ... None\n1 20161118 ... None\n2 20161118 ... None\n3 20161122 ... None\n4 20161122 ... None\n5 20161202 ... None\n6 20161206 ... None\n7 20161206 ... None\n8 20161206 ... None\n9 20161206 ... None\n\n[10 rows x 18 columns]\n","name":"stdout"}]},{"metadata":{"trusted":true},"cell_type":"code","source":"q.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# channelgrouping - ga channels\nquery = \"\"\"SELECT channelGrouping, COUNT(*) cnt\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n GROUP BY channelGrouping\n \"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"SELECT channelGrouping, COUNT(DISTINCT trafficSource.medium) cnt\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n GROUP BY channelGrouping\n \"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# verify conditions match up with definition https://support.google.com/analytics/answer/3297892\nquery = \"\"\"SELECT channelGrouping, array_agg(DISTINCT trafficSource.medium) cnt\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n GROUP BY channelGrouping\n \"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# above by with counts within each group\nquery = \"\"\"SELECT channelGrouping,trafficSource.medium, count(*) cnt\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n GROUP BY channelGrouping,trafficSource.medium\n \"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# group by medium\nquery = \"\"\"SELECT trafficSource.medium, channelGrouping, count(*) cnt\n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n GROUP BY trafficSource.medium, channelGrouping\n ORDER BY trafficSource.medium\n \"\"\"\ninspect(query)\n# so (none) can have labeled in channelGroupig even when ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# what do these look like? \nquery = \"\"\"SELECT * \n FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\n WHERE trafficSource.medium = '(none)'\n AND channelGrouping = 'Referral'\n \n \"\"\"\ninspect(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"query = \"\"\"\nSELECT \n substr(date, 1, 6) as ym,\n channelGrouping,\n COUNT(totals.totalTransactionRevenue) AS cnt_revenue,\n COUNT(totals.transactions) AS cnt_transactions, \n COUNT(*) AS cnt_all_rows, --sessions with browser\n COUNT(totals.transactions) / COUNT(*) AS sess_conversion_rate,\n sum(totals.totalTransactionRevenue) AS sum_revenue, \n Sum(totals.transactions) AS sum_transactions,\n sum(totals.totalTransactionRevenue) / Sum(totals.transactions) AS revenue_per_transaction\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\nGROUP BY channelGrouping, ym\nORDER BY channelGrouping, ym\n\"\"\"\nmonthly_channel_CR = retrieve(query)\nmonthly_channel_CR.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"monthly_channel_CR.sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_channel_CR, 'sess_conversion_rate', 'channelGrouping')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_channel_CR, 'cnt_transactions', 'channelGrouping')\n# Why does this look so different than raw source medium\n # because (none) can be assigned to various channelGroupings and other caveats\n# https://support.google.com/analytics/answer/3297892","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Base conversion rate by month and traffic source\nquery = \"\"\"\nSELECT \n substr(date, 1, 6) as ym,\n trafficSource.medium, \n COUNT(totals.totalTransactionRevenue) AS cnt_revenue,\n COUNT(totals.transactions) AS cnt_transactions, \n COUNT(*) AS cnt_all_rows, --sessions with browser\n COUNT(totals.transactions) / COUNT(*) AS sess_conversion_rate,\n sum(totals.totalTransactionRevenue) AS sum_revenue, \n Sum(totals.transactions) AS sum_transactions,\n sum(totals.totalTransactionRevenue) / Sum(totals.transactions) AS revenue_per_transaction\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`\nGROUP BY trafficSource.medium, ym\nORDER BY trafficSource.medium, ym\n\"\"\"\nmonthly_medium_CR = retrieve(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"monthly_medium_CR.sum()\n# revenue per transaction and sess_conversion_rate changed -- rest stayed the same","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_medium_CR, 'sess_conversion_rate', 'medium')\n# direct ads (cpc and cpm) > organic > referral/affiliate\n# medium can have a pretty strong effect on CR - diverging from the overall average of 1.5% ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_metric_by_month(monthly_medium_CR, 'cnt_transactions', 'medium')\n# organic has a low CR but it makes up for a good chunk of the transactions\n# Need to look further into (none) --> direct traffic - so people who want to buy stuff often visit directly \n# - bookmarks? returning customers?","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Resources and Acknowledgements:\n* Table schema https://support.google.com/analytics/answer/3437719?hl=en\n* BigQueryHelper https://www.kaggle.com/sohier/introduction-to-the-bq-helper-package\n* BigQuery tricks: https://www.kaggle.com/vikramtiwari/bigquery-in-depth-using-google-analytics-data\n* BQ Standard SQL - Unnest: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest"}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.4","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}