{"cells":[{"metadata":{"_uuid":"b86cf94d125e57a3ccc44d41d54d7fcf5a246039"},"cell_type":"markdown","source":"**Google Analytics Customer Revenue Prediction**\n\n* [http://www.kaggle.com/c/ga-customer-revenue-prediction](http://www.kaggle.com/c/ga-customer-revenue-prediction)\n\nCreated by Steve Black\nStarted on Sept.25.2018\n\nThis is a first basic attempt:\n* Connecting to the data using BigQueryHelper\n* Using Python 3 Notebook within Kaggle Kernal\n* Exploring how to access and query the data\n* Making notes on the data\n* Creating a silly model \n\nThe model:\n* For each user with revenue in the train set,\n* Calculate their total spend for each individual user\n* Divide by the total number of days (first transaction for each user to global end of train data time)\n* For any of the users that exist in the final Test set that are also in the Train set ( a small overlap)\n* Multiply the global duration (271 days) of the Test data set by the indivdual dollars per day calculated from the Train data set.\n\nYes, This is a bad model:\n* There are many users within the Test set that are not in the Train set. \n* These unique to Test users all get $0.\n* I used the whole Train set, and thus did not predict any sort of error or confidence with the Train set to compare any next models to.\n* However, it was a quick test of formatting the submisssion without getting deep on a real model.\n\nThis notebook spends most of it's time exploring the data using querys via BigQueryHelper. I've created two google sheets to make notes\n\nA list of the tables within Train and Test, along with their schemas and some notes I made:\n* https://docs.google.com/spreadsheets/d/1-kn-O6H4p_jNbfF0cN2i5o-FG1AN2leBTdXAzFDDdeQ/edit?usp=sharing\n\nA single specific table from the Train set:\n* https://docs.google.com/spreadsheets/d/1-kn-O6H4p_jNbfF0cN2i5o-FG1AN2leBTdXAzFDDdeQ/edit?usp=sharing\n\nI found this google support link that lists out the details of the schema as well:\n* https://support.google.com/analytics/answer/3437719?hl=en\n\n----\nI submitted my results and my RMS score was 1.8181\nNot bad, but it put near the bottom of the list, so it is a bad model"},{"metadata":{"trusted":true,"_uuid":"eb105341ad8f58de632e160659f84052d5b3b457"},"cell_type":"code","source":"# Print me to document when run:\nimport datetime\nprint(\"Start Run: \" + str(datetime.datetime.now()) )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"562b40fc5aa8860b257f5c1e407656459ebf1847"},"cell_type":"code","source":"# Imports:\nimport matplotlib.pyplot as plt\nfrom matplotlib.pyplot import figure\n# import numpy as np\n# import pandas as pd\n# import bq_helper\n# import csv\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# Code created by Kaggle when initializing notebook.\n# KEPT AS IS:\n# ---------------------------------------------------\n# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\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\"))\nprint(os.listdir(\"../\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"# Load data using BigQueryHelper:\n# I'm using some existing code provided by: \n# https://www.kaggle.com/juliaelliott/ga-bigquery-starter-kernel/notebook\n# Also found a great article/ help from Megan Risdal:\n# https://medium.com/google-cloud/learning-to-analyze-huge-bigquery-datasets-using-python-on-kaggle-2c6c6153f542\n\nimport bq_helper\n# use the BQHelper library to pull datasets/tables from BigQuery\n\nprint(\"Loading Training Data Set...\")\nga_bq_train = bq_helper.BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_train_set\")\nprint(\"Loading Test Data Set... \")\nga_bq_test = bq_helper.BigQueryHelper(active_project= \"kaggle-public-datasets\", dataset_name = \"ga_test_set\")\nprint(\"Data Loaded: done\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f5e04b7391e01208ae31897d4b98050ee785edd7"},"cell_type":"code","source":"# What is ga_bq_test? \n# For my reference: some of the BigQueryHelper methods\n# -------------------------------------------------------\n# type(ga_bq_test) # bq_helper.BigQueryHelper\n# ga_bq_test.BYTES_PER_GB # 1073741824\n# ga_bq_test.tables # {}\n# ga_bq_test.client # <google.cloud.bigquery.client.Client at 0x7f1bf9a3ea90>\n# ga_bq_test.dataset # Dataset(DatasetReference('kaggle-public-datasets', 'ga_test_set'))\n# ga_bq_test.dataset_name # 'ga_test_set'\n# ga_bq_test.head # expects the table name head('table_name')\n# ga_bq_test.project_name # 'kaggle-public-datasets'\n# ga_bq_test.total_gb_used_net_cache # 0\n# ga_bq_test.list_tables() # a list of all the tables\n# ga_bq_test.table_schema(table_name) # schema of a specific table","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"85549397d4e2ce53306263e63fab22640d3acf76"},"cell_type":"code","source":"# First look at what we have to work with:\nprint( \"Number of Tables in Train Data Set: \" + str(len(ga_bq_train.list_tables())) )\nprint( \"Number of Tables in Test Data Set: \" + str(len(ga_bq_test.list_tables())) )\nga_bq_test_list_tables = ga_bq_test.list_tables()\nga_bq_test_list_tables[0:3]\n#\n# Number of Tables in Train Data Set: 366\n# Number of Tables in Test Data Set: 272\n# ['ga_sessions_20170802',\n#  'ga_sessions_20170803',\n# ...","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0fbd39cf03d8834ce61c3e54fc80c9359af03090"},"cell_type":"code","source":"# OUTPUT a csv file of the list of tables\nimport csv\n\n# TRAIN : ga_bq_train\nprint('saving... ga_bq_train_list_tables ...')\nlist_of_tables = ga_bq_train.list_tables()\ncsvfile = \"ga_bq_train_list_tables.csv\"\n#Assuming res is a flat list\nwith open(csvfile, \"w\") as output:\n    writer = csv.writer(output, lineterminator='\\n')\n    for table in list_of_tables:\n        writer.writerow([table])    \nprint('done')\n\n# TEST : ga_bq_test\nprint('saving... ga_bq_test_list_tables ...')\nlist_of_tables = ga_bq_test.list_tables()\ncsvfile = \"ga_bq_test_list_tables.csv\"\n#Assuming res is a flat list\nwith open(csvfile, \"w\") as output:\n    writer = csv.writer(output, lineterminator='\\n')\n    for table in list_of_tables:\n        writer.writerow([table])    \nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6ce563db8ecf11b9d307f6c01d938f34a73e17a8"},"cell_type":"code","source":"# TABLE AND SCHEMA INSPECTION VIA GOOGLE SHEETS\n# I used the above csv file to inpsect data in Google Sheets :\n# https://docs.google.com/spreadsheets/d/1XBRL5cmJwqGe2KENLITTL_4DY-DOeAr4-JzlUYoMdYw/edit?usp=sharing\n#\n# NOTES:\n# -- The main field to test against will be: totals.transactionRevenue\n# -- A number of fields will NOT be needed\n# -- The 'time' seems to NOT include timezone","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"32d5497dbdb99fd3056110342c8bcb7af0fda1e4"},"cell_type":"code","source":"# TEST table schema:\n# Take a look at the TEST set schema for one of the tables\nprint(ga_bq_test.table_schema('ga_sessions_20170802').shape)\nga_bq_test_table_schema_ga_sessions_20170802 = ga_bq_test.table_schema('ga_sessions_20170802')\nga_bq_test_table_schema_ga_sessions_20170802.head(5)\n# There are 185 'columns'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0b639bfe15e4ee3638b98a21d540c513d76c9bc9"},"cell_type":"code","source":"# TRAIN table schema:\n# Take a look at the TRAIN set schema for one of the tables\nprint(ga_bq_train.table_schema('ga_sessions_20160801').shape)\nga_bq_train_table_schema_ga_sessions_20160801 = ga_bq_train.table_schema('ga_sessions_20160801')\nga_bq_train_table_schema_ga_sessions_20160801.head(5)\n# There are 185 'columns'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ee04e63a0bc2d0f17e75b7c29c9daa0288fbc6a0"},"cell_type":"code","source":"# Save the schema outputs to csv files:\n# a great quick reference: https://www.kaggle.com/szamil/where-is-my-output-file\nprint('saving csv file...')\nga_bq_test_table_schema_ga_sessions_20170802.to_csv('ga_bq_test_table_schema_ga_sessions_20170802.csv', index = False)\nga_bq_train_table_schema_ga_sessions_20160801.to_csv('ga_bq_train_table_schema_ga_sessions_20160801.csv', index = False)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a06e5cbd61077edac0eea503b4f8f983f5d73da8","scrolled":true},"cell_type":"code","source":"# Just checking the way to reference specific column name:\nreference_schema = ga_bq_test.table_schema('ga_sessions_20170802')\nprint( reference_schema.name[184] )\nreference_schema.name[184]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"fd7f8683b25f97341f74a247234358c25b7faea0"},"cell_type":"code","source":"# I want to make sure all of the tables have the same schema:\n# for an item in list I want to look up the schema and compare it to a reference schema\n# I'm just picking a field at random, and comparing the dimension size\n#\n# ----------- commentted out no need to re-run each commit -----------\n# ----------- all tables do appear to have same schema -----------\n'''\n# CHECK TEST SET:\n# let's use the first table within the test set of data as our reference:\nreference_schema = ga_bq_test.table_schema('ga_sessions_20170802')\nreference_shape = reference_schema.shape\nprint(reference_shape)\n#\nprint('checking test data schema')\nga_bq_test_list_tables = ga_bq_test.list_tables()\nprint(len(ga_bq_test_list_tables))\n# keep a count of the table we are on for easy tracking (old school coding)\nnnn = 0\nfor table in ga_bq_test_list_tables:\n    nnn = nnn + 1\n    # grab the schema\n    table_to_check_schema = ga_bq_test.table_schema(table)\n    table_to_check_shape = table_to_check_schema.shape\n    # ok, I'm going to cheat:\n    # I'm going to check if the shapes are the same\n    # and I'll spot check one random column near the end.\n    if (reference_shape == table_to_check_shape) and (table_to_check_schema.name[150] == reference_schema.name[150]):\n        print(\"table: \" + str(nnn) + \" : \" + table + \" check ok\" )\n    else:\n        print(\"table: \" + str(nnn) + \" : \" + table + \" check NOT OK\")\n        break\nprint('TEST set done')\nprint(' ')\n\n# CHECK TRAIN SET:\n# let's use the first table within the train set of data as our reference:\nreference_schema = ga_bq_train.table_schema('ga_sessions_20160801')\nreference_shape = reference_schema.shape\nprint(reference_shape)\n#\nprint('checking train data schema')\nga_bq_train_list_tables = ga_bq_train.list_tables()\nprint(len(ga_bq_train_list_tables))\nnnn = 0\nfor table in ga_bq_train_list_tables:\n    nnn = nnn + 1\n    # grab the schema\n    table_to_check_schema = ga_bq_train.table_schema(table)\n    table_to_check_shape = table_to_check_schema.shape\n    # ok, I'm going to cheat:\n    # I'm going to check if the shapes are the same\n    # and I'll spot check one random column near the end.\n    if (reference_shape == table_to_check_shape) and (table_to_check_schema.name[150] == reference_schema.name[150]):\n        print(\"table: \" + str(nnn) + \" : \" + table + \" check ok\" )\n    else:\n        print(\"table: \" + str(nnn) + \" : \" + table + \" check NOT OK\")\n        #print(table_to_check_shape)\n        #print(table_to_check_schema.name[150])\n        #print(reference_schema.name[150])\n        break\nprint('TRAIN set done')\n'''\n# ----------- end of commented section -----------\n#\n# Results:\n# OK, \n# I commented this out because it takes some time, and I only needed to do it once to convince myself\n# \n# The Test data and Train data sets are the same except:\n# The Train set has one additional totals.transactionRevenue column\n#\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"eb8da76228da15aea8492cecb9efe910250a98eb"},"cell_type":"code","source":"# DATA FIELDS AS FROM THE CONTEST DESCRIPTION\n# https://www.kaggle.com/c/ga-customer-revenue-prediction/data\n#\n# Data Fields:\n# fullVisitorId- A unique identifier for each user of the Google Merchandise Store.\n# channelGrouping - The channel via which the user came to the Store.\n# date - The date on which the user visited the Store.\n# device - The specifications for the device used to access the Store.\n# geoNetwork - This section contains information about the geography of the user.\n# sessionId - A unique identifier for this visit to the store.\n# socialEngagementType - Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\".\n# totals - This section contains aggregate values across the session.\n# trafficSource - This section contains information about the Traffic Source from which the session originated.\n# visitId - An identifier for this session. This is part of the value usually stored as the _utmb cookie. This is only unique to the user. For a completely unique ID, you should use a combination of fullVisitorId and visitId.\n# visitNumber - The session number for this user. If this is the first session, then this is set to 1.\n# visitStartTime - The timestamp (expressed as POSIX time).","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"36d4a144b62130f625464f82f4d5513008a85f60"},"cell_type":"code","source":"# A query estimate for a single table within the training dataset:\n# ga_sessions_20160801\nqueryy = \\\n    \"\"\"\n    SELECT  *\n    FROM `kaggle-public-datasets.ga_train_set.ga_sessions_20160801` \n    \"\"\"\n\nprint(\"size = \" + str( ga_bq_train.estimate_query_size(queryy) * 1000 ) + \" MB\" )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"65668fc973ce852e827328a6a095376ce6801fbd","scrolled":true},"cell_type":"code","source":"# A query for a single table within the training dataset\n# ga_sessions_20160801\nqueryy = \\\n    \"\"\"\n    SELECT  *\n    FROM `kaggle-public-datasets.ga_train_set.ga_sessions_20160801`\n    \"\"\"\n\nprint('a_small_query: start...')\nga_train_set_ga_sessions_20170803 = ga_bq_train.query_to_pandas_safe(queryy)\nprint('a_small_query: done.')\n\nga_train_set_ga_sessions_20170803.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c2380d71d4e640ef272bbe10cf7ec3b444fb9cc0"},"cell_type":"code","source":"ga_train_set_ga_sessions_20170803.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"da10a4b24f3f433801f712296347453a37de5033"},"cell_type":"code","source":"ga_train_set_ga_sessions_20170803.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"49dec3ce2783263a88a82ebd2db4e96248f34154"},"cell_type":"code","source":"print('saving csv file...')\nga_train_set_ga_sessions_20170803.to_csv('ga_train_set_ga_sessions_20170803.csv', index = False)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e0c546b83b3bc357bffbbd5e8c1aa5a09be19670"},"cell_type":"code","source":"# SPECIFIC TABLE INSPECTION VIA GOOGLE SHEET\n# I used the above csv file to inpsect a single table within Google Sheets :\n# https://docs.google.com/spreadsheets/d/1-kn-O6H4p_jNbfF0cN2i5o-FG1AN2leBTdXAzFDDdeQ/edit?usp=sharing","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5b0a8feb3d9be979efd460bcd9752d366ea30742"},"cell_type":"code","source":"# A query estimate for a ALL tables within the training dataset:\nqueryy = \\\n    \"\"\"\n    SELECT  *\n    FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n    \"\"\"\n\nprint(\"size = \" + str( ga_bq_train.estimate_query_size(queryy) * 1000 ) + \" MB\" )\n# size = 737.8220958635211 MB","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"59f19bfb52584323262ff6c30f760bcd88e92416"},"cell_type":"code","source":"# query the BQ train set tables to summarize total transaction revenue per user, where fullVisitorId is unique per user.\nqueryy = \\\n    \"\"\"\n    SELECT  fullVisitorId, coalesce(SUM( totals.transactionRevenue ),0) AS total_transactionrevenue_per_user\n    FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n    GROUP BY fullVisitorId\n    ORDER BY total_transactionrevenue_per_user DESC\n    \"\"\"\n\nprint(\"size = \" + str( ga_bq_train.estimate_query_size(queryy) * 1000 ) + \" MB\" )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"16403629f0e37d39ad84b367a5cd900baef67094"},"cell_type":"code","source":"# ok, let's do the query and save it:\nprint('starting queryy...')\ntotal_revenue_per_unique_fullVisitorID = ga_bq_train.query_to_pandas_safe(queryy)\nprint('done')\n# This took some time to run","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d0778e6f0576509ffffe8182013681b0f1405e7f"},"cell_type":"code","source":"type(total_revenue_per_unique_fullVisitorID)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a83f5abde34310d2d0b989977b57228ec206bb18"},"cell_type":"code","source":"#Take a look at some basic analytics of the transaction revenue pulled from our query\ntotal_revenue_per_unique_fullVisitorID.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dd2b2e63eea515bb2a4a720fad969ff2152fc0a0"},"cell_type":"code","source":"total_revenue_per_unique_fullVisitorID.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"da8e52991eba9fa4cf7e76b9a9a2a663290413c1"},"cell_type":"code","source":"# Save output.\nprint('saving csv file...')\ntotal_revenue_per_unique_fullVisitorID.to_csv('total_revenue_per_unique_fullVisitorID.csv', index = False)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bc4516f32d3594da87261eaea0876a2ffa5a35d2"},"cell_type":"code","source":"#How many of the users spent any money?\nlen(total_revenue_per_unique_fullVisitorID[total_revenue_per_unique_fullVisitorID.total_transactionrevenue_per_user > 0])\n# 9996","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"398dff29ed9e25bebf30bed268e938f0436dd83a"},"cell_type":"code","source":"total_revenue_per_unique_fullVisitorID.shape\n# (714167, 2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e45244aad604623ff8a5625161598d463c1102a7"},"cell_type":"code","source":"#Let's take a look at the average transaction revenue for each user who spent money.\n\nqueryy= \\\n\"\"\"\nSELECT\n( (SUM(total_transactionrevenue_per_visitorid) / SUM(total_visits_per_visitorid))/1000000 ) AS avg_revenue_by_user_per_visit_as_dollars\nFROM (\n    SELECT\n    fullVisitorId,\n    SUM( totals.visits ) AS total_visits_per_visitorid,\n    SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_visitorid\n    FROM\n    `kaggle-public-datasets.ga_train_set.ga_sessions_*`\n    WHERE\n    totals.visits > 0\n    AND totals.transactionRevenue IS NOT NULL\n    GROUP BY\n    fullVisitorId );\n\"\"\"\n\navg_transrev = ga_bq_train.query_to_pandas_safe(queryy, max_gb_scanned=10)\nprint('done')\navg_transrev.head(5)\n# $133.75","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d208c055f42bf52818013fca9b394654ce87e95d"},"cell_type":"code","source":"# another query to pull a few specific datapoints per user.\nprint('starting query...')\nqueryy = \\\n\"\"\"\nSELECT  fullVisitorId, \n    SUM(totals.transactionRevenue) AS total_transactionrevenue_per_user, \n    SUM(totals.pageviews) AS total_pagesviews_per_user,\n    SUM(totals.visits ) AS total_visits_per_user,\n    SUM(totals.timeOnSite ) AS total_timeonsite_per_user\nFROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \nGROUP BY fullVisitorId\nORDER BY total_transactionrevenue_per_user DESC\n\"\"\"\nall_train_summary2 = ga_bq_train.query_to_pandas_safe(queryy)\nprint('done')\nall_train_summary2.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7f41b09948666c61e51fadbbe75c5d4f8f477b17"},"cell_type":"code","source":"#Now let's drop the data from that query into a dataframe.\n#This is the start of how you might pull features that you've queried out of BQ to begin modelling.\nfull_df = ga_bq_train.query_to_pandas(queryy)\nfull_df.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"65b695430ca5ba56bdf632597347e777da284240"},"cell_type":"code","source":"# END OF REFERENCE FROM \n# https://www.kaggle.com/juliaelliott/ga-bigquery-starter-kernel/notebook\n# JULIA ELLIOTT\n# Thanks Julia!","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ce94852de081e342e88410b6f6230fa120bc5cc3"},"cell_type":"code","source":"# ok... what's next...","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5b457920c460ee1fd08d2e01cf3d74c425e30017"},"cell_type":"code","source":"# This, is all of the users\n# fullVisitorID = 4984366501121503466\nqueryy = \\\n    \"\"\"\n    SELECT  \n    fullVisitorId, \n    sessionId,\n    date,\n    visitStartTime,\n    coalesce(totals.transactionRevenue,0) AS totals_transactionRevenue\n    FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n    ORDER BY visitStartTime\n    \"\"\"\nprint(\"size = \" + str( ga_bq_train.estimate_query_size(queryy) * 1000 ) + \" MB\" )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bda3fdf5a994f0fb2ce4cbae88123f2401d31cd1"},"cell_type":"code","source":"print('starting query...')\nallUserQuery_event_date_revenue = ga_bq_train.query_to_pandas_safe(queryy)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"89181ebf8f36c60e0d6a872df44134a58871ef0b"},"cell_type":"code","source":"allUserQuery_event_date_revenue.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f25cedabd1a9c1e956871e00039a854a45ed103a"},"cell_type":"code","source":"allUserQuery_event_date_revenue.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1790cffb7497f824dd06de331a62a4ecd4d7a438"},"cell_type":"code","source":"# Save output.\nprint('saving csv file...')\nallUserQuery_event_date_revenue.to_csv('allUserQuery_event_date_revenue.csv', index = False)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c2a6077b33b3634c21ed54007e13fadb45a74594"},"cell_type":"code","source":"import matplotlib.pyplot as plt\nfrom matplotlib.pyplot import figure\nfigure(num=None, figsize=(15, 4), dpi=80, facecolor='w', edgecolor='k')\nx = allUserQuery_event_date_revenue.visitStartTime\ny = allUserQuery_event_date_revenue.totals_transactionRevenue\nplt.plot(x, y, 'o', color='black');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"13fb41e95eb870fe41b9613eb6e3863e488e2c51"},"cell_type":"code","source":"# This, is all of the users\n# fullVisitorID = 4984366501121503466\nqueryy = \\\n    \"\"\"\n    SELECT  \n    fullVisitorId, \n    sessionId,\n    date,\n    visitStartTime,\n    coalesce(totals.transactionRevenue,0) AS totals_transactionRevenue\n    FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n    WHERE fullVisitorId = '4984366501121503466'\n    ORDER BY visitStartTime\n    \"\"\"\nprint(\"size = \" + str( ga_bq_train.estimate_query_size(queryy) * 1000 ) + \" MB\" )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"00cd1b06ef16dc4ecb8e7f34057f2737185bac66"},"cell_type":"code","source":"print('start query')\nuserData_4984366501121503466 = ga_bq_train.query_to_pandas_safe(queryy)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ef3178201dc1d754e24325ff2b1d37decb45e91c"},"cell_type":"code","source":"userData_4984366501121503466.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"29e6c85f1688510d22914595e0f59f1b650983e6"},"cell_type":"code","source":"# Let's save it:\nprint('saving csv file...')\nuserData_4984366501121503466.to_csv('userData_4984366501121503466.csv', index = False)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3b0be7eb9cf8137d8a2981d28470f5e61790a4a9"},"cell_type":"code","source":"import matplotlib.pyplot as plt\nfrom matplotlib.pyplot import figure\nfigure(num=None, figsize=(15, 4), dpi=80, facecolor='w', edgecolor='k')\nx = userData_4984366501121503466.visitStartTime\ny = userData_4984366501121503466.totals_transactionRevenue\nmarkerline, stemlines, baseline = plt.stem(x, y, '-.')\n# setting property of baseline with color red and linewidth 2\nplt.setp(baseline, color='r', linewidth=2)\nplt.show()\n#\n# Interesting points about this specific user:\n# Most visits result in revenue\n# Is there a trend, or re-occuring pattern?\n# will need to lay seasons or events along time path.\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1cc685c8aaa04783af5c94f946318f1c5683d766"},"cell_type":"code","source":"# just checking how to sort by time using the pandas sort_values() method:\nallUserQuery_event_date_revenue.sort_values(by=['visitStartTime'])\nallUserQuery_event_date_revenue.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"139a86efb275211e1978e74ec5cfe1b35d6130b8"},"cell_type":"code","source":"# I want the SUM for each individual user\nqueryy = \\\n    \"\"\"\n    SELECT  \n    fullVisitorId, \n    MIN(date) AS minimum_date,\n    MIN(visitStartTime) AS minimum_vistStartTime,\n    coalesce(SUM( totals.transactionRevenue ),0) AS total_transactionrevenue_per_user\n    FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n    GROUP BY fullVisitorId\n    ORDER BY total_transactionrevenue_per_user DESC\n    \"\"\"\nprint(\"size = \" + str( ga_bq_train.estimate_query_size(queryy) * 1000 ) + \" MB\" )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8391dc7acb8d8739956e128020dbae16295a1d19"},"cell_type":"code","source":"print('starting query...')\ntrain_sum_for_each_user = ga_bq_train.query_to_pandas_safe(queryy)\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"997cfe68e4e7fc95138d8cfd632de06ba41fac9b"},"cell_type":"code","source":"train_sum_for_each_user.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"671f94e88dda0641d60cd2b77161a23c5271a11b"},"cell_type":"code","source":"# Double checking the data type, (Want to make sure we are working with numbers, not text)\nprint('minimum_date:')\nprint(train_sum_for_each_user['minimum_date'].describe())\nprint('-----')\nprint('minimum_vistStartTime:')\nprint(train_sum_for_each_user['minimum_vistStartTime'].describe())\nprint('-----')\nprint('total_transactionrevenue_per_user:')\nprint(train_sum_for_each_user['total_transactionrevenue_per_user'].describe())\n# minimum_date is an object (probably text)\n# minimum_visitStartTime is float64\n# total_transactionrevenue_per_user is float64","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3e9d4d7826670b4be5a2bdda265e3facc66bc2ef"},"cell_type":"code","source":"# In this silly model,\n# I want to calculate the average spend per day,\n# for the date range, I want to use the date of the first purchase in the data, divided by the last day of the data.\n#\n# We are subtracting midnight on 8/1/2017 plus 1.5 days (to make sure we don't get any negitive time, found by trial and error)\n# 8/1/2017 is 1501545600 in POSIX time (the time in seconds from 1/1/1970)\n#\n# This pandas dataframe will be a time delta in seconds:\ntrain_user_time_delta = (1501545600+(1.5*24*60*60)) - train_sum_for_each_user['minimum_vistStartTime'] \ntrain_user_time_delta.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4bcafff6b1da7eb00845817189df26a4abc08250"},"cell_type":"code","source":"train_user_time_delta.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f610c6985edc1f168d861158872cac335a0a4883"},"cell_type":"code","source":"# Now calculating the revenue per day in given units of 1/10^6 dollars per day.\ntrain_user_revenue_per_day = train_sum_for_each_user['total_transactionrevenue_per_user'] / (train_user_time_delta / (60 * 60 * 24) )\ntrain_user_revenue_per_day.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2fb52b47d950d66f46b119628a06a6d12ee00560"},"cell_type":"code","source":"train_user_revenue_per_day.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"353e0ed03bf1c95d2e9ff91aa6a6f96dc5b35ebe"},"cell_type":"code","source":"# add results to dataframe as new column:\ntrain_sum_for_each_user['revenuePerDay'] = train_user_revenue_per_day\ntrain_sum_for_each_user.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"848005eab663ae877d17a6d3ae74e436db0978f8"},"cell_type":"code","source":"# checking some stats:\nprint(\"count of NaN: \" + str( train_sum_for_each_user['revenuePerDay'].isna().sum()) )\nprint(\"count of Null: \" + str( train_sum_for_each_user['revenuePerDay'].isnull().sum()) )\nprint(\"count of not NaN: \" + str( train_sum_for_each_user['revenuePerDay'].count()) )\nprint(\"rows and columns of data: \" + str(train_sum_for_each_user.shape) )\n#\n# train_sum_for_each_user[train_sum_for_each_user['revenuePerDay'] > 0].count()\nprint(sum(train_sum_for_each_user['revenuePerDay'] > 0)) # 9996 with revenue\nprint(sum(train_sum_for_each_user['revenuePerDay'] == 0)) # 704,171 no revenue\nprint(sum(train_sum_for_each_user['revenuePerDay'] < 0)) # just being complete","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2abf01b69747b793f244c397eb101d4c01385273"},"cell_type":"code","source":"# Great!\n# now I just need to grab the 'test' data, and apply the silly formula\n# time detla of test set * revenue per day for each user = answer!","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"37ad049f578fdb02831323660fa7a201dfa74768"},"cell_type":"code","source":"# let's query for a list of all the unique test users:\n# I want the SUM for each individual user\nqueryy = \\\n    \"\"\"\n    SELECT  \n    fullVisitorId\n    FROM `kaggle-public-datasets.ga_test_set.ga_sessions_*` \n    GROUP BY fullVisitorId\n    ORDER BY fullVisitorId\n    \"\"\"\nprint(\"size = \" + str( ga_bq_test.estimate_query_size(queryy) * 1000 ) + \" MB\" )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"beb845c48d68ac053ebd94662fc62e57c26f0929"},"cell_type":"code","source":"print('starting query...')\ntest_only_unique_user_list = ga_bq_test.query_to_pandas_safe(queryy)\nprint('done.')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9ceb8b6a853bbb53522836ca5a346edc5ba00619"},"cell_type":"code","source":"test_only_unique_user_list.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"00a4d12bd8b162fff45f7f934e8f101f93015e81"},"cell_type":"code","source":"# Ok... let's create a dataframe where we have the unique list of test users\n# I'll be doing a left-join so I can keep all Test users, but match up any Train users:\ndf_a = test_only_unique_user_list\ndf_b = train_sum_for_each_user\ndf_new = pd.merge(df_a, df_b, on='fullVisitorId', how='left')\ndf_new.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6610d57f3d06d48649a4764330cd534eae1f4ac7"},"cell_type":"code","source":"df_new.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f7e736fd3b0e1d6ec6acd2ed64b5f109ccc8d94f"},"cell_type":"code","source":"# Im going to do an outer and inner join just to see how things add up:\ndf_outer = pd.merge(df_a, df_b, on='fullVisitorId', how='outer')\ndf_inner = pd.merge(df_a, df_b, on='fullVisitorId', how='inner')\nprint('done')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"40291af431fca2777cbf9e3f9495109822026aa1"},"cell_type":"code","source":"print('train set unique count: ' + str(df_b.shape)) # (714167, 5)\nprint('test set unique count: ' + str(df_a.shape)) # (617242, 1)\nprint('train and test total combined: ' + str(df_outer.shape)) # (1323730, 5)\nprint('train within test: ' + str(df_inner.shape)) # (7679, 5)\nprint('count of how many users in test set have postive revenue: ' + str(sum(df_new['revenuePerDay'] > 0)) ) #536\n# NOTES: \n# Yes it all adds up: 714,167 + 617,242 - 7,679 = 1,323,730 (Joins work as they should)\n# We only have 7,679 matching user IDs between test and train.\n# we had 9,996 within Train that had greater than zero revenue (calculated previously)\n# in the new Test set caculated with this silly model: we have 536 users with positive revenue\n# (assuming that our population would be a similar percentage, we are wayyy under)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a5326993d8ae167e57151b8238d60ae6a1458a85"},"cell_type":"code","source":"print(\"count of NaN: \" + str( df_new['revenuePerDay'].isna().sum()) )\nprint(\"count of not NaN: \" + str( df_new['revenuePerDay'].count()) )\nprint(\"rows and columns of data: \" + str(df_new.shape) )\nprint(\"percent of data with NaN: \" + str(609563/617242*100) + \"%\")\nprint(\"check that sums add up: \" + str(609563+7679) + \" ... yes, they add up\")\n# ok, this model sucks\n# 98.8% of users in the test set do NOT have data in the train set","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"37b32443f808b1e8883ccdfbec9d0a6951f9b9f3"},"cell_type":"code","source":"df_new.revenuePerDay.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a9f7a1f730abd367321ef701bc71e2a79326d259"},"cell_type":"code","source":"# getting rid of NaN's to make math work\ndf_new.revenuePerDay.fillna(0,inplace=True)\ndf_new.revenuePerDay.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8bb220f057eae8f21f5b889f73a893c56dabc25a"},"cell_type":"code","source":"# I need to simply multiply the revenuePerday by the number of days in the test set for each user\noutput = np.log(df_new.revenuePerDay * 271+1)\noutput.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"392e67b534f3d584bf93dd6a3b490ba54a3f37ff"},"cell_type":"code","source":"# finally, let's put it all together:\nvisit_id = df_new.fullVisitorId\nout_PredictedLogRevenue = pd.concat([visit_id, output], axis=1)\nout_PredictedLogRevenue.rename(columns={'revenuePerDay':'PredictedLogRevenue'}, inplace=True)\nout_PredictedLogRevenue.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"072d0631b277c6322b24e2ab535d5e935c69c50c"},"cell_type":"code","source":"# Let's save it:\nprint('saving csv file...')\nout_PredictedLogRevenue.to_csv('submission_SteveBlack_v001_date_2018_1003_1137.csv', index = False)\nprint('done')","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}