<jupyter_start><jupyter_text>BigQuery SQL queries used in this project Data collected from this script is available here: https://drive.google.com/drive/folders/1E-pxqkJDrS-jd_MNq6IbS-v7gGNNmrYd?usp=sharing. No need to run these queries yourself.<jupyter_code>from google.oauth2 import service_account
import pandas_gbq
import os

credentials = service_account.Credentials.from_service_account_file(os.getenv("GOOGLE_APPLICATION_CREDENTIALS"))
# import packages
import pandas as pd
import pandas_gbq

# extract data from BigQuery
project_id = "govuk-bigquery-analytics"

# document types to ignore i.e. pages not relevant to WUJs
ignore = (
    "authored_article",
    "news_article",
    "news_story",
    "press_release",
    "world_news_story",
    "utaac_decision",
    "speech",
    "case_study",
    "raib_report",
    "asylum_support_decision",
    "policy_paper",
    "corporate_report",
    "written_statement",
    "consultation_outcome",
    "closed_consultation",
    "maib_report",
    "person",
    "correspondence",
    "employment_tribunal_decision",
    "employment_appeal_tribunal_decision",
    "tax_tribunal_decision",
    "ministerial_role",
    "residential_property_tribunal_decision",
    "cma_case",
)<jupyter_output><empty_output><jupyter_text>Page hits Here we're collecting lists of page paths and content ids, for which there have been more than 5 page/content hits in the last 2 weeks. Subsequently, given one of these lists, nodes within the knowledge graph whose page path is within the list are extracted. These nodes form a subgraph.  By page hits per page path<jupyter_code># get page paths with more than 5 page hits in the last 2 weeks,
# ignoring irrelevant pages
sql = """
DECLARE start_date STRING DEFAULT "20211018";
DECLARE end_date STRING DEFAULT "20211031";

WITH sessions AS (
SELECT DISTINCT
    CONCAT(fullVisitorId, "-", CAST(visitId AS STRING)) AS sessionId,
    REGEXP_REPLACE(hits.page.pagePath, r'[?#].*', '') AS pagePath
FROM `govuk-bigquery-analytics.87773428.ga_sessions_*`
CROSS JOIN UNNEST(hits) AS hits
WHERE
    _TABLE_SUFFIX BETWEEN start_date AND end_date
    AND hits.page.pagePath NOT LIKE "/print%"
    AND hits.type = 'PAGE'
    AND (SELECT value FROM hits.customDimensions WHERE index = 2) NOT IN {} 
)

SELECT DISTINCT
    pagePath,
    COUNT(sessionId) AS noOfSessions 
FROM sessions
GROUP BY pagePath
HAVING noOfSessions >= 5
ORDER BY noOfSessions
""".format(
    ignore
)

# create Data Frame
page_paths = pandas_gbq.read_gbq(sql, project_id=project_id, credentials=credentials)
page_paths.to_csv("../data/processed/page_paths.csv", index=False)<jupyter_output>Downloading: 100%|██████████| 22313/22313 [00:01<00:00, 21408.56rows/s]<jupyter_text>By page hits per content ID<jupyter_code># get content ids with more than 5 page hits in the last 2 weeks,
# ignoring irrelevant pages
sql = """
DECLARE start_date STRING DEFAULT "20211018";
DECLARE end_date STRING DEFAULT "20211031";

WITH sessions AS (
SELECT DISTINCT
    CONCAT(fullVisitorId, "-", CAST(visitId AS STRING)) AS sessionId,
    (SELECT value FROM hits.customDimensions WHERE index = 4) AS contentId
FROM `govuk-bigquery-analytics.87773428.ga_sessions_*`
CROSS JOIN UNNEST(hits) AS hits
WHERE
    _TABLE_SUFFIX BETWEEN start_date AND end_date
    AND hits.page.pagePath NOT LIKE "/print%"
    AND hits.type = 'PAGE'
    AND (SELECT value FROM hits.customDimensions WHERE index = 2) NOT IN {}
)

SELECT DISTINCT
    contentID,
    COUNT(sessionId) AS noOfSessions 
FROM sessions
GROUP BY contentID
HAVING noOfSessions >= 5
""".format(
    ignore
)

# create Data Frame
content_ids = pandas_gbq.read_gbq(sql, project_id=project_id, credentials=credentials)
content_ids.to_csv("../data/processed/content_ids.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09326287978863937, 'fraction_numerical': 0.02404227212681638, 'mean_word_length': 4.924882629107981, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 14, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '99429', 'license': 'MIT', 'n_tokens_mistral': 1352, 'n_tokens_neox': 1236, 'n_words': 354, 'path': 'notebooks/bigQuery.ipynb', 'repo_name': 'alphagov/govuk-wuj-network-analysis', 'size': '3785'}