GOV.UK GA4 annotations

The GOV.UK GA4 annotations report provides information on users of GOV.UK GA4 data to SPOCs.

The data provided includes:

the dates of changes to the GOV.UK GA4 data collection
the dates of external events which could impact use of GOV.UK

This report was first created in mid 2024 by the GOV.UK Insights and Analytics team to support users of the GOV.UK GA4 data.

Using the GOV.UK GA4 annotations report

Get access to the GOV.UK GA4 annotations report

The report can be viewed by anyone with a GDS email address.

Those in the GOV.UK Insights and Analytics team Google group and in the GDS Performance Analysts Google group are automatically granted ‘Edit’ permissions.

GOV.UK GA4 annotations data definitions

Field name	Definition	Notes
start_date	The date the issue happened or started on
end_date	If present, the date the issue was resolved or stopped occuring	Only present for annotations pulled from the `data_quality_notes` table
category	The type of annotation - for example, if the annotation provides ‘Information’ or tells you when something was ‘Changed’, ‘Created’ or ‘Broken’	Always ‘Information’ for the `govuk_timeline` and `planning_message_calendar` tables
issue_name	The ‘title’ of the issue	Calculated in the custom query for the `govuk_timeline` and `planning_message_calendar` tables
details	A more detailed explanation of the issue	Calculated in the custom query for the `govuk_timeline` and `planning_message_calendar` tables
duration	The difference between the start and end dates	A calculated field added to the data source
impacted_event_names	The event_name value for the GOV.UK GA4 events that might be impacted by this issue - ‘null’ if all events or unknown	Always ‘null’ for the `govuk_timeline` and `planning_message_calendar` tables
impacted_event_types	The type value for the GOV.UK GA4 events that might be impacted by this issue - ‘null’ if all events or unknown	Always ‘null’ for the `govuk_timeline` and `planning_message_calendar` tables
Record Count	A count of the records (rows) in the table	Default Looker Studio field

How the GOV.UK GA4 annotations report works

Data sources

The GOV.UK GA4 annotations reporting Looker Studio report uses the ‘GOV.UK GA4 annotations’ data source. This is a custom query which pulls together data from a few different data sources in the ga4_annotations dataset:

the data_quality_notes table, which pulls information on changes to the GA4 data collection from a Google Sheet
the govuk_timeline and planning_message_calendar tables, which pull information on external events from two Google Sheets

The custom query unions each of these tables together after adding in the ‘Category’ and ‘Issue name’ fields and ensuring that only appropriate rows (where events have specific dates) are pulled in.

Date range parameters are enabled and the whole data source (the result of the union of the above tables) is filtered by the Looker Studio date parameters.

The custom query is as follows:

WITH
  dq_notes AS (
  SELECT
    What_date_did_the_thing_start_ AS start_date,
    What_date_did_the_thing_end___if_relevant_ AS end_date,
    Category AS category,
    Issue_name AS issue_name,
    Details AS details,
    Impacted_event_name_s____comma_separated AS impacted_event_names,
    Impacted_event_type_s____comma_separated AS impacted_event_types
  FROM
    `ga4-analytics-352613.ga4_annotations.data_quality_notes` ),
  first_event_date AS (
  SELECT
    event_name,
    type,
    first_date
  FROM
    `ga4-analytics-352613.ga4_annotations.event_first_recorded_date` ),
  govuk_timeline AS(
  SELECT
    PARSE_DATETIME("%A %e %B %Y", date) AS date,
    CASE
      WHEN other_events IS NOT NULL THEN "External event"
      WHEN covid_events IS NOT NULL THEN "Covid event"
      WHEN govuk_changes IS NOT NULL THEN "GOV.UK change"
      ELSE NULL
  END
    AS issue_name,
    other_events,
    covid_events,
    govuk_changes
  FROM
    `ga4-analytics-352613.ga4_annotations.govuk_timeline` ),
  message_calendar AS(
  SELECT
    PARSE_DATETIME("%e %B %Y", CONCAT(date, year)) AS date,
    CASE
      WHEN national_events IS NOT NULL THEN "National event"
      WHEN fixed_service_deadlines IS NOT NULL THEN "Fixed service deadline"
      WHEN government_announcements IS NOT NULL THEN "Government annonucement"
      WHEN political_calendar IS NOT NULL THEN "Political event"
      WHEN government_events IS NOT NULL THEN "Government event"
      WHEN external_events IS NOT NULL THEN "External event"
      WHEN emotional_calendar IS NOT NULL THEN "Emotional event"
      ELSE NULL
  END
    AS issue_name,
    national_events,
    fixed_service_deadlines,
    government_announcements,
    political_calendar,
    government_events,
    external_events,
    emotional_calendar
  FROM
    `ga4-analytics-352613.ga4_annotations.planning_message_calendar` 
    WHERE
    REGEXP_CONTAINS(date, '^[0-9]{1,2}.[a-z]*') 
    ),
  union_table AS (
  SELECT
    start_date,
    end_date,
    category,
    issue_name,
    details,
    impacted_event_names,
    impacted_event_types
  FROM
    dq_notes
  UNION ALL
  SELECT
    first_date,
    NULL,
    'Created',
    CONCAT(type, " ", event_name, " event added"),
    CONCAT("New event type added - name: ", CONCAT(event_name, CONCAT(", type: ", type))),
    event_name,
    type
  FROM
    first_event_date
  UNION ALL
  SELECT
    DATE(date),
    NULL,
    'Information',
    govuk_timeline.issue_name,
    CONCAT(IFNULL(govuk_timeline.other_events,""), CONCAT(IFNULL(govuk_timeline.covid_events,""), IFNULL(govuk_timeline.govuk_changes, ""))) AS details,
    NULL,
    NULL
  FROM
    govuk_timeline
  WHERE
    govuk_timeline.issue_name IS NOT NULL 
    UNION ALL
  SELECT
    DATE(date),
    NULL,
    'Information',
    message_calendar.issue_name,
    CONCAT(IFNULL(message_calendar.national_events,""), CONCAT(IFNULL(message_calendar.fixed_service_deadlines,""), CONCAT(IFNULL(message_calendar.government_announcements, ""), CONCAT(IFNULL(message_calendar.political_calendar, ""), CONCAT(IFNULL(message_calendar.government_events, ""), CONCAT(IFNULL(message_calendar.external_events, ""), IFNULL(message_calendar.emotional_calendar, ""))))))) AS details,
    NULL,
    NULL
  FROM
    message_calendar
  WHERE
    message_calendar.issue_name IS NOT NULL)
SELECT
  *
FROM
  union_table
WHERE
    (start_date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE)
      AND PARSE_DATE('%Y%m%d', @DS_END_DATE))
    OR (end_date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE)
      AND PARSE_DATE('%Y%m%d', @DS_END_DATE))
    OR ( --date in middle of issue range
      start_date <= PARSE_DATE('%Y%m%d', @DS_START_DATE)
      AND end_date >= PARSE_DATE('%Y%m%d', @DS_END_DATE) AND end_date IS NOT NULL)
ORDER BY
  start_date

This page was last reviewed on 14 November 2024. It needs to be reviewed again on 14 May 2025 .

This page was set to be reviewed before 14 May 2025. This might mean the content is out of date.