<jupyter_start><jupyter_text>Spider Diagram   Authorisation<jupyter_code>import json
from datetime import datetime

import numpy as np
import pandas as pd
import plotly.graph_objects as go
from google.cloud.bigquery import Client, QueryJobConfig
from google.colab import auth, files

# Authenticate the user - follow the link and the prompts to get an authentication token
auth.authenticate_user()<jupyter_output><empty_output><jupyter_text>Input Variables<jupyter_code># @title Set Input Variables Form
project_id = "govuk-bigquery-analytics"  # @param {type:"string"}
ga_dataset = "87773428"  # @param {type:"string"}
start_date = "2021-12-14"  # @param {type:"date"}
end_date = "2021-12-14"  # @param {type:"date"}
page_path = "/"  # @param {type:"string"}
path_or_title = "Page Path"  # @param ["Page Path", "Page Title"]

# @markdown Remove query strings from all previous and next page paths:
remove_query_string = False  # @param {type:"boolean"}

# @markdown Set the device categories; select one or more devices:
Desktop = True  # @param {type:"boolean"}
Mobile = True  # @param {type:"boolean"}
Tablet = True  # @param {type:"boolean"}
# Define type constants that define the previous, and next types in the SQL queries
TYPES_PREVIOUS = ["acquisition_source", "previous_page_path"]
TYPES_NEXT = ["exit", "next_page_path"]

# Define type constants for GOV.UK
TYPES_GOVUK = ["previous_page_path", "next_page_path"]

# Define the colour map for the output visualisation, and the colour of the central bar
COLOUR_MAP = {
    t: "#150578" if t in TYPES_GOVUK else "#5AB7E0" for t in TYPES_PREVIOUS + TYPES_NEXT
}
COLOUR_NODE = "#C0C0C0"

# Prepare date variables for the output visualisation
start_date_vis = datetime.strptime(start_date, "%Y-%m-%d").strftime("%d-%m-%Y")
end_date_vis = datetime.strptime(end_date, "%Y-%m-%d").strftime("%d-%m-%Y")

# Construct a BigQuery client object, setting the location to EU (necessary for EXECUTE IMMEDIATE later)
client = Client(project=project_id, location="EU")

# Prepare input variables for SQL insertion
start_date_str = datetime.strptime(start_date, "%Y-%m-%d").strftime("%Y%m%d")
end_date_str = datetime.strptime(end_date, "%Y-%m-%d").strftime("%Y%m%d")
ga_table = f"`{project_id}.{ga_dataset}.ga_sessions_*`"
# Create variables for SQL insertion depending on the input variable
# page_or_title, remove_query_string, and the checkbox selection for Desktop,
# Mobile, Tablet
if path_or_title == "Page Path":
    display_variable_pp = "prev_page_path"
    display_variable_np = "next_page_path"
    query_path = True
    bq_schema_display_variable = "hits.page.pagePath"

elif path_or_title == "Page Title":
    display_variable_pp = "prev_page_title"
    display_variable_np = "next_page_title"
    query_path = False
    bq_schema_display_variable = "hits.page.pageTitle"

else:
    display_variable_pp = ""
    display_variable_np = ""
    bq_schema_display_variable = ""


# Create a list of device types based on checkbox selection
device_choices = [
    "desktop" if Desktop else "",
    "mobile" if Mobile else "",
    "tablet" if Tablet else "",
]

# Drop any blank entries in `device_choices`, and raise an error if no checkboxes were ticked.
# Otherwise compile the device types into a single string for SQL insertion
device_list = [device for device in device_choices if device]
if not device_list:
    raise ValueError("Need to tick at least one device type! None ticked!")
device_variable = '("{}")'.format('", "'.join(device_list))<jupyter_output><empty_output><jupyter_text>Query - Previous Page Path<jupyter_code># Compile the previous page path query using the variables, and print it
prev_page_path = f"""
SELECT
    page_path,
    IF({remove_query_string} AND {query_path}, REGEXP_REPLACE({display_variable_pp}, r'[?#].*', ''), {display_variable_pp}) AS name,
    "previous_page_path" AS type,
    COUNT({display_variable_pp}) AS value
FROM (
    SELECT *
    FROM (
        SELECT
            CONCAT(fullVisitorId, "-", CAST(visitStartTime AS STRING)) AS sessionId,
            hits.page.pagePath AS page_path,
            device.deviceCategory AS device_type,
            LAG({bq_schema_display_variable}) OVER (
                PARTITION BY fullVisitorId, visitStartTime ORDER BY hits.hitNumber
            ) AS {display_variable_pp}
        FROM {ga_table}
        CROSS JOIN UNNEST(hits) AS hits
        WHERE _TABLE_SUFFIX BETWEEN "{start_date_str}" AND "{end_date_str}"
        AND hits.type="PAGE"
    )
    WHERE page_path = "{page_path}"
    AND {display_variable_pp} IS NOT NULL
    AND device_type IN {device_variable}
)
GROUP BY
    name,
    page_path,
    type
"""
print(prev_page_path)<jupyter_output><empty_output><jupyter_text>Query - Acquisition Source<jupyter_code># Compile the acquisition source query using the variables, and print it
acq_source = f"""
SELECT
    page_path,
    full_traffic_source AS name,
    "acquisition_source" AS type,
    COUNT(session_id) AS value
FROM (
    SELECT
        *,
        CASE
            WHEN traffic_medium = "referral" THEN CONCAT(traffic_medium, " / ", full_referrer)
            ELSE CONCAT(traffic_source, " / ",traffic_medium)
        END AS full_traffic_source
    FROM (
        SELECT
            CONCAT(CAST(fullVisitorId AS STRING), CAST(visitId AS STRING)) AS session_id,
            hits.page.pagePath AS page_path,
            hits.page.pageTitle AS page_title,
            trafficSource.source AS traffic_source,
            trafficSource.medium AS traffic_medium,
            device.deviceCategory AS device_type,
            CONCAT(trafficSource.source,trafficSource.referralPath) AS full_referrer,
        FROM {ga_table}
        CROSS JOIN UNNEST(hits) AS hits
        WHERE _TABLE_SUFFIX BETWEEN "{start_date_str}" AND "{end_date_str}"
        AND hits.isEntrance
        AND hits.page.pagePath = "{page_path}"
        AND device.deviceCategory IN {device_variable}
    )
)
GROUP BY
  page_path,
  name,
  type
"""
print(acq_source)<jupyter_output><empty_output><jupyter_text>Query - Next Page Path<jupyter_code># Compile the next page path query using the variables, and print it
next_page_path = f"""
SELECT
    page_path,
    IF({remove_query_string} AND {query_path}, (IF({display_variable_np} IS NULL, "(exit)", REGEXP_REPLACE({display_variable_np}, r'[?#].*', ''))), (IF({display_variable_np} IS NULL, "(exit)", {display_variable_np}))) AS name,
    IF({display_variable_np} IS NULL, "exit", "next_page_path") AS type,
    COUNT(session_id) AS value
FROM (
    SELECT
        CONCAT(fullVisitorId, "-", CAST(visitStartTime AS STRING)) AS session_id,
        hits.page.pagePath AS page_path,
        device.deviceCategory AS device_type,
        LEAD({bq_schema_display_variable}) OVER (
            PARTITION BY fullVisitorId, visitStartTime ORDER BY hits.hitNumber
        ) AS {display_variable_np}
    FROM {ga_table}
    CROSS JOIN UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN "{start_date_str}" AND "{end_date_str}"
    AND hits.type = "PAGE"
)
WHERE page_path = "{page_path}"
AND device_type IN {device_variable}
GROUP BY
    page_path,
    name,
    type
"""
print(next_page_path)<jupyter_output><empty_output><jupyter_text>Execute queries<jupyter_code># Compile all the queries into a single query using UNION ALL
compiled_query = "\nUNION ALL\n".join([acq_source, prev_page_path, next_page_path])

# Calculate the total number of bytes that will be processed by `compiled_query`
bytes_processed = client.query(
    compiled_query, job_config=QueryJobConfig(dry_run=True)
).total_bytes_processed

# Compile a message, and flag to the user for a response; if not "yes", terminate execution
user_message = f"This query will process {bytes_processed / (1024 ** 3):.1f} GB when run,\
 which is approximately ${bytes_processed / (1024 ** 4)*5:.3f}. Continue ([yes])? "
if input(user_message).lower() != "yes":
    raise RuntimeError("Stopped execution!")

# Execute the query, wrapped inside EXECUTE IMMEDIATE, and return as a pandas DataFrame; EXECUTE
# IMMEDIATE ensures only a single SQL statement is executed at a time, and helps reduce SQL
# injection risks
df_compiled = client.query(
    'EXECUTE IMMEDIATE """\n{}\n"""'.format(compiled_query)
).to_dataframe()
df_compiled.head()<jupyter_output><empty_output><jupyter_text>Compile summary statistics<jupyter_code># Calculate the total volume of session IDs recorded, and the proportion for each entry
total_sessions = df_compiled.loc[df_compiled["type"].isin(TYPES_NEXT), "value"].sum()
proportion_of_sessions = df_compiled["value"] / total_sessions

# Assign a new boolean column `other` that determines if a value aggregated into an "(Other)" bin.
# Pages are aggregated into this bin if their value as a percentage is less than 2.5%
df_compiled = df_compiled.assign(
    next_type=df_compiled["type"].isin(TYPES_NEXT),
    proportion=proportion_of_sessions,
    other=proportion_of_sessions < 0.025,
)

# Aggregate the "(Other)" bin by `type`. Calculate the sum of the new
# aggregations. Use the default index. Assign name as "(Other)"
df_compiled_other_sum = (
    df_compiled.query("other")
    .groupby(["other", "page_path", "next_type", "type"])
    .sum()
    .reset_index()
    .assign(name="(Other)")
)

# Drop others in `df_compiled`, and combine with `df_compiled_other_sum`. Sort the values, and
# drop no longer required columns
df_result = (
    pd.concat(
        [df_compiled.query("not(other)"), df_compiled_other_sum], ignore_index=True
    )
    .sort_values(
        by=["next_type", "type", "other", "value", "name"],
        ascending=[True, True, True, False, True],
    )
    .reset_index(drop=True)
    .drop(columns=["page_path", "next_type", "other"])
)

# Define a title row to be inserted into the final dataset
df_title = pd.DataFrame(
    {
        "name": [page_path],
        "type": ["title"],
        "value": [total_sessions],
        "proportion": [1],
    }
)

# Compile the results, dropping the `other` column
df_result = pd.concat([df_title, df_result], ignore_index=True)
df_result<jupyter_output><empty_output><jupyter_text>Create plotly figure<jupyter_code># Create the displayed text on the diagram, where the text is "Value [Proportion] - Name"
s_display = df_result[["value", "proportion", "name"]].apply(
    lambda x: "{val:,} [{prop:.0%}] - {name}".format(val=x[0], prop=x[1], name=x[2]),
    axis=1,
)

# Select the first 63 characters from the text which will display on the diagram; any
# further characters are truncated, and replaced with "..."
s_display = s_display.where(s_display.str.len() < 63, s_display.str[:63] + "...")

# Select the first 70 characters from the page path text which will display as the title;
# any further characters are trucated, and replaced with "..."
if len(page_path) > 70:
    page_path_title = page_path[:70] + "..."
else:
    page_path_title = page_path
# Define the position map for the visualisation
position_map = {
    t: "previous" if t in TYPES_PREVIOUS else "after" for t in COLOUR_MAP.keys()
}

# Map colours and positions to each type; fill NAs with the central value, which should
# be the title value. Add the displayed name
df_figure = df_result.copy().assign(
    colour=df_result["type"].map(COLOUR_MAP).fillna(COLOUR_NODE),
    position=df_result["type"].map(position_map).fillna("node"),
    display=s_display,
)

# Define a title for the figure
figure_title = (
    f"<b>Spider Diagram Tool: `{page_path_title}`</b>"
    + f'<br>[{start_date_vis} to {end_date_vis}]  [{"/".join(device_list)}]'
)
# Define the figure layout for the chart
figure_layout = go.Layout(
    barmode="stack",
    showlegend=False,
    xaxis={
        "visible": False,
        "categoryorder": "array",
        "categoryarray": ["previous", "node", "after"],
    },
    yaxis={"visible": False},
    plot_bgcolor="#FFFFFF",
    autosize=False,
    height=900,
    width=1500,
    title={"text": figure_title, "x": 0.03},
    title_font_size=30,
    font={"family": "Arial", "size": 15},
    margin={"l": 30, "r": 30, "b": 30},
)

# Create a Figure object with our pre-defined layout
figure = go.Figure(layout=figure_layout)

# Define the format of the hover labels
figure_hover_template = """
<b>{page}</b><br>
Counts: {counts:,}<br>
Percentage: {percentages:.0%}
"""

# Flip the index of `df_figure`, i.e. put it in reverse order, and then iterate over
# the flipped rows. Plotly will plot the first row it sees at the bottom of the figure,
# hence why we need to flip `df_figure` to ensure entries are plotted in the correct
# order
for ix, s in df_figure.iloc[::-1].iterrows():

    # Create a bar trace with the correct data, and append to `figure`
    figure_bar = go.Bar(
        name="",
        text=[s["display"]],
        x=[s["position"]],
        y=[s["value"]],
        width=[0.95],
        textposition="inside",
        marker_color=s["colour"],
        hovertemplate=figure_hover_template.format(
            page=s["name"], counts=s["value"], percentages=s["proportion"]
        ),
    )
    figure.add_trace(figure_bar)
figure.show()<jupyter_output><empty_output><jupyter_text>Export data, and figure<jupyter_code># Get the current datetime as the execution timestamp
execution_timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

# Define the output filenames
output_filename_base = f"{execution_timestamp}_spider_diagram_tool"
output_filename_metadata = f"{output_filename_base}_metadata.txt"
output_filename_results = f"{output_filename_base}_results.csv"
output_filename_figure = f"{output_filename_base}_figure.html"

# Define metadata for this query
output_metadata = f"""EXECUTION
Timestamp:   {execution_timestamp}
Metadata:    {output_filename_metadata}
Files:       {output_filename_results}
             {output_filename_figure}

QUERY
Project ID:  {project_id}
Dataset:     {ga_dataset}
Start date:  {start_date_vis}
End date:    {end_date_vis}
Path/title:  {path_or_title}
Page:        {page_path}
Device(s):   {', '.join(device_list)}
"""

# Write the metadata to a text file, `df_result` to a CSV, and `figure` to a HTML
with open(output_filename_metadata, "w") as f:
    f.write(output_metadata)
df_result.to_csv(output_filename_results, index=False)
figure.write_html(output_filename_figure)

# Download all files
_ = [
    files.download(f)
    for f in [output_filename_metadata, output_filename_results, output_filename_figure]
]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1169326856349757, 'fraction_numerical': 0.006800832755031228, 'mean_word_length': 4.345326409495549, 'pattern_counts': {'":': 18, '<': 46, '<?xml version=': 0, '>': 45, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '563957', 'license': 'MIT', 'n_tokens_mistral': 4795, 'n_tokens_neox': 4627, 'n_words': 1291, 'path': 'notebooks/spider-diagram-tool.ipynb', 'repo_name': 'alphagov/govuk-user-journey-analysis-tools', 'size': '14410'}