{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"_kg_hide-output":true,"scrolled":false},"cell_type":"code","source":"import bq_helper\n#Here's how we can use the BQHelper library to pull datasets/tables from BigQuery\nga_bq_train = bq_helper.BigQueryHelper(active_project= \"kaggle-public-datasets\", \n                                       dataset_name = \"ga_train_set\")\nga_bq_test = bq_helper.BigQueryHelper(active_project= \"kaggle-public-datasets\", \n                                       dataset_name = \"ga_test_set\")\nga_bq_test.list_tables()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7ab24aaab3edd435bbff7fbf287674db0195e4b6"},"cell_type":"code","source":"#Take a look at the test set schema\nga_bq_test.table_schema('ga_sessions_20170802')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bb7b879a31df5cade5e8f49a48338cce1d92fa6f"},"cell_type":"code","source":"#Here's an example of how we might query the BQ train set tables to summarize total transaction revenue per user, where fullVisitorId is unique per user.\nall_train_summary_query = \"\"\"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\"\"\"\nall_train_summary = ga_bq_train.query_to_pandas_safe(all_train_summary_query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2a27b11ea0c090203cc0ddcf1d624e6ccc226df5"},"cell_type":"code","source":"#Take a look at some basic analytics of the transaction revenue pulled from our query\nall_train_summary.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9499bbc00d239aac9a69f35328b41d1390a21c9e"},"cell_type":"code","source":"#How many of the users spent any money?\nlen(all_train_summary[all_train_summary.total_transactionrevenue_per_user > 0])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3b8fd067596a5ca42c8d2ec79d4d2d98fff8397d"},"cell_type":"code","source":"#Let's take a look at the average transaction revenue for each user who spent money.\n\navg_transrev_query = \"\"\"SELECT\n( SUM(total_transactionrevenue_per_visitorid) / SUM(total_visits_per_visitorid) ) AS\navg_revenue_by_user_per_visit\nFROM (\nSELECT\nfullVisitorId,\nSUM( totals.visits ) AS total_visits_per_visitorid,\nSUM( totals.transactionRevenue ) AS total_transactionrevenue_per_visitorid\nFROM\n`kaggle-public-datasets.ga_train_set.ga_sessions_*`\nWHERE\ntotals.visits > 0\nAND totals.transactionRevenue IS NOT NULL\nGROUP BY\nfullVisitorId );\n        \"\"\"\navg_transrev = ga_bq_train.query_to_pandas_safe(avg_transrev_query, max_gb_scanned=10)\navg_transrev.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"089754802e5384ede632983deebbf123bd6039db"},"cell_type":"code","source":"#Here we'll create another query to pull a few specific datapoints per user.\nall_train_summary_query2 = \"\"\"SELECT  fullVisitorId, \nSUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user, \nSUM(totals.pageviews) AS total_pagesviews_per_user,\nSUM(totals.visits ) AS total_visits_per_user,\nSUM(totals.timeOnSite ) AS total_timeonsite_per_user\n  FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n  GROUP BY fullVisitorId\n\"\"\"\nall_train_summary2 = ga_bq_train.query_to_pandas_safe(all_train_summary_query2)\nall_train_summary2.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a11999887f3f29efb9f5d52337c11683e4ba3e28"},"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(all_train_summary_query2)\nfull_df.describe()","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}