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_notestable, which pulls information on changes to the GA4 data collection from a Google Sheet - the
govuk_timelineandplanning_message_calendartables, 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:
SQL
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