<jupyter_start><jupyter_text>**Table and graphics from Google Analytics public dataset**<jupyter_code>import os
import pandas as pd
from google.cloud import bigquery
import matplotlib.pyplot as plt


json_path = "/content/big-query-284408-e895d0eb5b23.json"
os.environ["GOOGLE_APPLICATION_CREDENTIALS"]=json_path

client = bigquery.Client()         # Start the BigQuery Client
# Input your Query Syntax here; You may try it first at https://console.cloud.google.com/bigquery
sql = """SELECT *
              FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
              LIMIT 1"""

# I am using low limits, because I have some limits in Google Big Query
df = client.query(sql).to_dataframe()
df.head()

# Explore data
%%bigquery 
# this gives option to run BigQuery statements directly in Jupyter

SELECT distinct(channelGrouping)
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
LIMIT 10<jupyter_output><empty_output><jupyter_text>Date,	channel,	visits,	transactions,	transaction revenue<jupyter_code># query by BigQuery Google Analytics tutorial
sql = """SELECT
                    date,
                    channelGrouping as channel,
                    totals.visits,
                    totals.transactions,
                    totals.transactionRevenue
                    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
                    LIMIT 5"""

def get_data(sql):
  df = client.query(sql).to_dataframe()
  df['date'] = pd.to_datetime(df.date).dt.strftime('%Y-%m--%d')
  return df

df = get_data(sql)
df.head()<jupyter_output><empty_output><jupyter_text>Select date, channel, visits, transactions and transaction revenue - first 100 results Direct visitors and organic search data: ordered by transaction revenue<jupyter_code>sql ="""SELECT
                      date,
                      channelGrouping as channel,
                      totals.visits,
                      totals.transactions,
                      totals.transactionRevenue
                      FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
                      WHERE channelGrouping in ('Direct', 'Organic Search')
                      ORDER BY totals.transactionRevenue desc LIMIT 5"""

df = get_data(sql)
df.head()<jupyter_output><empty_output><jupyter_text>Aggregate transaction totals<jupyter_code># query by BigQuery Google Analytics tutorial
query_organic_direct = """SELECT date, channelGrouping as channel,
                                          sum(totals.visits) as visits, 
                                          CASE WHEN sum(totals.visits) > 0 THEN sum(totals.transactions) / sum(totals.visits) 
                                          ELSE 0 END as conv_rate, 
                                          sum(totals.transactions) as transactions, 
                                          CASE WHEN sum(totals.transactions) > 0 THEN
                                          sum(totals.transactionRevenue) / sum(totals.transactions)
                                          ELSE 0 END as aov,
                                          sum(totals.transactionRevenue) as revenue 
                                          FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
                                          WHERE channelGrouping in ('Organic Search', 'Direct') 
                                          GROUP BY date, channel
                                          ORDER BY transactions 
                                          desc LIMIT 1000"""

df = get_data(sql)
df.head()<jupyter_output><empty_output><jupyter_text>**all channels** Number of visitors per date, by channel - Organic Search and Paid Search<jupyter_code>sql = """SELECT distinct(date), channelGrouping as channel, count(totals.visits) as visitors_num, 
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE channelGrouping like '%Search'
GROUP BY date, channel
ORDER BY date
LIMIT 20 """

df = get_data(sql)
df.head()<jupyter_output><empty_output><jupyter_text>Number of visitors by date (paid/ organic search) in different color schemas<jupyter_code>df_line = df
df_plot = df_line.pivot('date', 'channel', 'visitors_num')
df_plot.plot(figsize=(12, 6));
fig = plt.Figure()
ax = fig.add_subplot()
ax.set_title('Visitors per day from Search channels')<jupyter_output><empty_output><jupyter_text>Paid search and Organic search<jupyter_code>pivot_table = df.pivot(index="date", columns="visitors_num", values="visitors_num")
bar_list = [ 'Blues', 'Blues_r', 'BuPu_r'] # list of styles for graphics
for n, i in enumerate(bar_list):
  pivot_table.plot(kind="bar", stacked=True, colormap=bar_list[n], figsize=(12,6))
ax.set_title("Visitors per day from Search channels")
ax.set_xlabel("Date")
ax.set_ylabel("Visitors")<jupyter_output><empty_output><jupyter_text>Visitors per day from All channels<jupyter_code>sql = """SELECT distinct(date), count(totals.visits) as visitors_num, 
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
GROUP BY date
ORDER BY date
LIMIT 20 """

df_all_visitors = get_data(sql)
df_all_visitors.head()<jupyter_output><empty_output><jupyter_text>Bar chart - Number of visitors per day in different colors<jupyter_code>pivot_table = df_all_visitors.pivot(index="date", columns="visitors_num", values="visitors_num")
ax = pivot_table.plot(kind="bar", stacked=True, colormap =  'Blues', figsize=(15, 7))
ax.set_title("Visitors per day from all channels")
ax.set_xlabel("Date")
ax.set_ylabel("Visitors")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09415758442022512, 'fraction_numerical': 0.014650705735215293, 'mean_word_length': 2.855371900826446, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 39, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 26, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '17867', 'license': 'MIT', 'n_tokens_mistral': 1698, 'n_tokens_neox': 1596, 'n_words': 382, 'path': 'google_analytics.ipynb', 'repo_name': 'Georgitanev/google_big_query', 'size': '5597'}