Google Analytics Data Analysis (BigQuery)

%load_ext bigquery_magics
# To supress warnings and make our notebook more legible
import warnings
warnings.filterwarnings('ignore')
from google.cloud import bigquery
from google.oauth2 import service_account
# I'll use pandas to explore table structures, field values etc
import pandas as pd
# for plotting
import matplotlib.pyplot as plt
import plotly.graph_objects as go
import plotly.io as pio
!pip install nbstripout -q

key_path = "data/week-3-bigquery-369916-341d3b137e42.json"

# Create credentials object
credentials = service_account.Credentials.from_service_account_file(key_path)

# Set context manually
from google.cloud.bigquery import magics
magics.context.credentials = credentials
magics.context.project = credentials.project_id

# data visualisation settings
pio.templates.default = "plotly_white"
color_palette = ['#8c4053', '#40798C','#a170a9', '#70A9A1']

%%bigquery df
-- Create a dataframe from the table - this will only be used to explore nested content, unique values etc
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;

df.info()

df['totals']

# Since 'totals' is a nested STRUCT, I'll expand it using json_normalize
df_totals = pd.json_normalize(df['totals'])

print(df_totals.columns)

# Explore the hits column
df['hits']

# List the keys of the first hit
df['hits'].iloc[0][0].keys()

%%bigquery

SELECT COUNT(DISTINCT fullVisitorId) AS unique_users
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;

%%bigquery

SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
LIMIT 5;

%%bigquery

SELECT
  column_name,
  data_type
FROM `bigquery-public-data.google_analytics_sample.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'ga_sessions_20170801';

%%bigquery

SELECT
  totals.visits,
  totals.pageviews,
  totals.transactionRevenue,
  device.deviceCategory,
  device.isMobile,
  trafficSource.source,
  trafficSource.medium,
  geoNetwork.country
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
LIMIT 5;

%%bigquery

SELECT
  MIN(totals.pageviews) AS min_pageviews,
  MAX(totals.pageviews) AS max_pageviews,
  AVG(totals.pageviews) AS avg_pageviews,
  STDDEV(totals.pageviews) AS std_pageviews,
  COUNT(*) AS total_rows,
  COUNTIF(totals.pageviews IS NULL) AS null_count
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;

%%bigquery

SELECT
  MIN(totals.transactions) AS min_transactions,
  MAX(totals.transactions) AS max_transactions,
  AVG(totals.transactions) AS avg_transactions,
  STDDEV(totals.transactions) AS std_transactions,
  COUNT(*) AS total_rows,
  COUNTIF(totals.transactions IS NULL) AS null_count
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;

%%bigquery

SELECT
  MIN(totals.transactionRevenue) / 1e6 AS min_transactionRevenue,
  MAX(totals.transactionRevenue) / 1e6 AS max_transactionRevenue,
  AVG(totals.transactionRevenue) / 1e6 AS avg_transactionRevenue,
  STDDEV(totals.transactionRevenue) / 1e6 AS std_transactionRevenue,
  COUNT(*) AS total_rows,
  COUNTIF(totals.transactionRevenue IS NULL) / 1e6 AS null_count
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;

%%bigquery

SELECT
  COUNT(*) AS sessions_missing_userid
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE fullVisitorId IS NULL;

%%bigquery

SELECT
  COUNT(*) AS sessions_with_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE totals.transactions IS NOT NULL;

%%bigquery

SELECT
  COUNT(*) AS sessions_with_pageviews
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE totals.pageviews IS NOT NULL;

%%bigquery

SELECT
  trafficSource.source,
  COUNT(*) AS session_count
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY trafficSource.source
ORDER BY session_count DESC;

%%bigquery

SELECT
    trafficSource.source,
    COUNT(*) AS session_count
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY trafficSource.source
ORDER BY session_count DESC
LIMIT 5;

%%bigquery
# Ranking traffic sources by revenue in million dollars

SELECT
    trafficSource.source,
    SUM(totals.transactionRevenue) / 1e6 AS sources_revenue_million
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY trafficSource.source
ORDER BY sources_revenue_million DESC
LIMIT 5;

%%bigquery
# ranking traffic by medium

SELECT
    trafficSource.medium,
    COUNT(*) AS session_count
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY trafficSource.medium
ORDER BY session_count DESC
LIMIT 10;

%%bigquery df

SELECT
  device.deviceCategory AS device_category,
  COUNT(*) AS device_count
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY device_category
ORDER BY device_count DESC;

plt.figure(figsize=(6, 4))
plt.bar(
    df['device_category'],
    df['device_count'],
    color=color_palette[0]
)

# labels and title
plt.ylabel("Number of Sessions")
plt.title("Device Category Counts")

# remove surrounding bounding box / spines
ax = plt.gca()
for spine in ["top", "right", "left", "bottom"]:
    ax.spines[spine].set_visible(False)

ax.tick_params(left=False, bottom=False)

plt.xticks(rotation=0)
plt.show()

%%bigquery

SELECT
  device.isMobile,
  COUNT(*) AS device_count
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY device.isMobile
ORDER BY device_count DESC;

%%bigquery

SELECT
    CASE WHEN
        totals.newVisits = 1 THEN 'new_visitors'
        ELSE 'returning_visitor'
    -- Create a new column
    END AS user_type,
    COUNT(*) AS returning_user_count,
    SUM(totals.transactions) AS total_transactions,
    SUM(totals.transactions) / COUNT(*) AS conversion_rate,
    SUM(totals.transactionRevenue) / 1e6 AS transaction_revenue_visitor,
    SUM(totals.transactionRevenue) / 1e6 / COUNT(*) AS transaction_revenue_per_visitor
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY user_type
ORDER BY transaction_revenue_visitor DESC;

%%bigquery

SELECT
    geoNetwork.country,
    COUNT(*) AS country_count,
    SUM(totals.transactionRevenue)  / 1e6 AS revenue_per_country,
    SUM(totals.transactions) / COUNT(*) AS transactions_session,
    SUM(totals.transactionRevenue) / 1e6 / COUNT(*) AS transaction_revenue_per_visitor
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY geoNetwork.country
ORDER BY transaction_revenue_per_visitor DESC
LIMIT 5;

%%bigquery bounce_df
--I'll use a Common Table Expression (CTE) to create a temporary result set for bounce data

WITH bounce_table AS (
    SELECT
        trafficSource.medium,
        CASE WHEN totals.pageviews = 1 THEN 1 ELSE 0 END AS is_bounce
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
)
SELECT
    medium,
    COUNT(*) AS session_count,
    SUM(is_bounce) AS total_bounce,
    ROUND(SAFE_DIVIDE(SUM(is_bounce), COUNT(*)) * 100, 1) AS bounce_ratio
FROM bounce_table
GROUP BY medium
ORDER BY bounce_ratio DESC;

bounce_df

plt.figure(figsize=(6, 4))
plt.bar(
    bounce_df['medium'],
    bounce_df['bounce_ratio'],
    color=color_palette[0]
)

plt.xlabel("Traffic Medium")
plt.ylabel("Bounce Ratio (%)")
plt.title("Bounce Ratio by Medium")

# remove bounding box
ax = plt.gca()
for spine in ["top", "right", "left", "bottom"]:
    ax.spines[spine].set_visible(False)

ax.tick_params(left=False, bottom=False)

plt.xticks(rotation=0)
plt.show()

%%bigquery

WITH bounce_stats AS(
SELECT
  h.page.pagePath AS landing_page,
  COUNT(DISTINCT visitId) AS total_sessions,
  SUM(CASE WHEN totals.pageViews = 1 THEN 1 ELSE 0 END) AS total_bounces
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
CROSS JOIN
  UNNEST(hits) AS h
WHERE
  h.hitNumber = 1
GROUP BY
  landing_page
)

SELECT
  *,
  ROUND(SAFE_DIVIDE(total_bounces, total_sessions) * 100,1)  AS bounce_rate
FROM
  bounce_stats
WHERE
  total_sessions >= 10
ORDER BY
  bounce_rate DESC

%%bigquery
WITH session_time AS(
SELECT
  visitId,
  MIN(h.time) / 1000 AS session_start_time_seconds,
  MAX(h.time) / 1000 AS session_end_time_seconds,
  CASE
    WHEN COUNT(h.time) = 1 THEN 0
    ELSE (MAX(h.time) - MIN(h.time)) / 1000
  END AS session_duration_seconds,
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
CROSS JOIN
  UNNEST(hits) AS h
WHERE
  -- Only real interactions
  h.isInteraction IS TRUE
GROUP BY
  visitId
ORDER BY
  session_duration_seconds DESC
)

SELECT
  AVG(session_duration_seconds) AS average_session_duration
FROM
  session_time

%%bigquery

WITH session_time AS(
SELECT
  visitId,
  MIN(h.time) / 1000 AS session_start_time_seconds,
  MAX(h.time) / 1000 AS session_end_time_seconds,
  CASE
    WHEN COUNT(h.time) = 1 THEN 0
    ELSE (MAX(h.time) - MIN(h.time)) / 1000
  END AS session_duration_seconds,
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
  -- Use cross join to explode an array into rows
CROSS JOIN
  UNNEST(hits) AS h
WHERE
  -- Only real interactions
  h.isInteraction IS TRUE
GROUP BY
  visitId
ORDER BY
  session_duration_seconds DESC
)

SELECT
  trafficSource.source,
  AVG(session_duration_seconds) AS avg_session_duration
FROM
  session_time
JOIN
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` AS t
ON
  session_time.visitId = t.visitId
GROUP BY
  trafficSource.source
ORDER BY
  avg_session_duration DESC
LIMIT 20;

%%bigquery
-- Let's calculate average page and event hits per session

WITH hit_table AS(
    SELECT
        visitId,
        SUM(CASE WHEN h.type = 'PAGE' THEN 1 ELSE 0 END) AS page_hits,
        SUM(CASE WHEN h.type = 'EVENT' THEN 1 ELSE 0 END) AS event_hits,
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
    UNNEST(hits) AS h
    GROUP BY visitId
)

SELECT
    ROUND(AVG(page_hits), 2) AS avg_page_hits_per_session,
    ROUND(AVG(event_hits), 2) AS avg_event_hits_per_session,
    COUNT(*) AS total_sessions
FROM
    hit_table;

%%bigquery

WITH session_summary AS (
    SELECT
        fullVisitorId,
        visitId,
        SUM(CASE WHEN h.type = 'EVENT' THEN 1 ELSE 0 END) AS event_count,
        IFNULL(totals.transactions, 0) AS transactions,
        IFNULL(totals.transactionRevenue, 0) / 1e6 AS transaction_revenue -- GA stores revenue in micros (divide by 1M)
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
    UNNEST(hits) AS h
    GROUP BY fullVisitorId, visitId, totals.transactions, totals.transactionRevenue
)

SELECT
    ROUND(AVG(event_count), 2) AS avg_events_per_session,
    ROUND(AVG(CASE WHEN transactions > 0 THEN event_count ELSE NULL END), 2) AS avg_events_in_converting_sessions,
    ROUND(SUM(transactions) / COUNT(*), 4) AS conversion_rate,
    ROUND(SUM(transaction_revenue) / COUNT(*), 2) AS avg_revenue_per_session
FROM
    session_summary;

%%bigquery

WITH event_data AS (
    SELECT
        h.eventInfo.eventCategory AS event_category,
        h.eventInfo.eventAction AS event_action,
        h.eventInfo.eventLabel AS event_label,
        IFNULL(totals.transactions, 0) AS transactions
    FROM
        `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
        UNNEST(hits) AS h
    WHERE
        h.type = 'EVENT'
)

SELECT
    event_category,
    event_action,
    event_label,
    COUNT(*) AS total_event_occurrences,
    -- Mark the sessions that had transactions
    SUM(CASE WHEN transactions > 0 THEN 1 ELSE 0 END) AS event_in_converting_sessions,
    -- Give the ratio of the events that had transactions to the total event occurences
    ROUND(SAFE_DIVIDE(SUM(CASE WHEN transactions > 0 THEN 1 ELSE 0 END), COUNT(*)) * 100, 1) AS conversion_event_ratio
FROM
    event_data
GROUP BY
    event_category, event_action, event_label
ORDER BY
    event_in_converting_sessions DESC
LIMIT 10;

%%bigquery

WITH funnel_data AS (
    SELECT
        visitId,
        -- If at least 1 of the following events has taken place, assign the value 1
        MAX(CASE WHEN h.eventInfo.eventAction = 'Product Click' THEN 1 ELSE 0 END) AS product_click_count,
        MAX(CASE WHEN h.eventInfo.eventAction = 'Quickview Click' THEN 1 ELSE 0 END) AS quickview_click_count,
        MAX(CASE WHEN h.eventInfo.eventAction = 'Add to Cart' THEN 1 ELSE 0 END) AS add_to_cart_count,
        IFNULL(MAX(totals.transactions), 0) AS transaction_total
    FROM
        `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
        UNNEST(hits) AS h
    WHERE
        h.type = 'EVENT'
    GROUP BY
        visitId
)

SELECT
    COUNT(DISTINCT visitId) AS total_visits,
    SUM(CASE WHEN product_click_count=1 OR quickview_click_count=1 THEN 1 ELSE 0 END) AS product_or_quickview_session,
    SUM(CASE WHEN (product_click_count=1 OR quickview_click_count=1) AND add_to_cart_count=1 THEN 1 ELSE 0 END) AS product_or_quickview_and_cart_session,
    SUM(CASE WHEN (product_click_count=1 OR quickview_click_count=1) AND add_to_cart_count=1 AND transaction_total>0 THEN 1 ELSE 0 END) AS product_or_quickview_cart_transaction_session
FROM
    funnel_data;

%%bigquery

WITH average_pageviews AS (
  SELECT AVG(totals.pageviews) AS avg_pageviews
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
    WHERE totals.pageviews IS NOT NULL
)

SELECT
  fullVisitorId,
  visitId,
  totals.pageviews,
  avg_pageviews,
  totals.transactions,
  totals.transactionRevenue / 1e6 AS revenue
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
CROSS JOIN
  average_pageviews
WHERE
  totals.transactions IS NOT NULL
  AND totals.pageviews > (
    SELECT AVG(totals.pageviews) AS avg_pageviews
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
    WHERE totals.pageviews IS NOT NULL
  )
ORDER BY
  revenue DESC
LIMIT 10;

labels = [
    "Total Visits",
    "Product or Quickview",
    "Added to Cart",
    "Transactions"
]

values = [480, 430, 152, 30]

fig = go.Figure(go.Funnel(
    y=labels,
    x=values,
    textinfo="value+percent initial",
    orientation="h",
    marker=dict(color=color_palette[1])
))

fig.update_layout(
    title="User Journey Funnel",
    width=900,
    height=500
)

fig.show()