GOV.UK GA4 flattened table

The GA4 flattened table contains production GOV.UK GA4 data processed into a flat format.

This table is created from the raw data exported into BigQuery and contains most, but not all, of the same fields. This flattened dataset is easier and more efficient to query, so should be used for most analysis and reporting.

Content

The first date for which there is data in the flattened table is the 27th Septemeber 2022. Note that the quality of the early data may be lower than desired as our GA4 implementation was still in active development until early 2024.

This table is a partitioned table, and is partitioned on event_date. A WHERE clause to specify which partitions are needed is required when querying this table - for example, WHERE event_date = "2024-09-12".

Access

Everyone with a @digital.cabinet-office.gov.uk email address (all GDS staff) has access to the data by default.

In addition, everyone who requests and is granted access to the GOV.UK GA4 data will be given access to query this flattened dataset. More information can be found in our GA access policy.

Location

This data can be found in BigQuery in the ga4-analytics-352613.flattened_dataset.partitioned_flattened_events table.

As with the rest of our GOV.UK GA4 BigQuery data, this is located in the GA4 Analytics project.

Set-up

Data collection and processing

This flattened table is the result of several steps of processing:

The GOV.UK GA4 production raw data is received from Google. This arrives sharded into daily tables, and the data within these tables is nested. We store this raw data in BigQuery
The raw data is processed into a partitioned table that is partitioned on event_date and clustered on event_name (though the data is still nested)
The partitioned nested data is flattened into the partitioned flattened table detailed on this page

graph TD 
A[Raw sharded data from Google] --> B
B[Partitioned raw event data] 
B --> C[Partitioned flattened data]

This processing occurs within DataForm in the gds-bq-reporting project, but the data is written back into the ga4-analytics-352613 project. The DataForm script is compiled at 7am UTC and run at 8am UTC. The full code used can be viewed in Github.

More information on the processing can be found in the Policies and processes section.

Schema

field name	type	mode	description
event_date	DATE	NULLABLE	The date when the event was logged, formatted as YYYYMMDD
user_pseudo_id	STRING	NULLABLE	The pseudonymous ID for a user on a particular client
event_name	STRING	NULLABLE	The name of the event
event_timestamp	INTEGER	NULLABLE	The time (in microseconds, UTC) when the event was logged
unique_session_id	STRING	NULLABLE	The user_pseudo_id concatenated with the ga_session_id to create a unique session ID
page_title	STRING	NULLABLE	The page title
page_location	STRING	NULLABLE	The full URL with the protocol, hostname, page path and query string
cleaned_page_location	STRING	NULLABLE	The page path (the page_location without the protocol, hostname, and any query string
ga_sessionid	INTEGER	NULLABLE	A session ID generated by GA4. This corresponds to the time the session started and is not necessarily unqiue
ga_session_number	INTEGER	NULLABLE	The number of sessions that a user has started up to the current session e.g. ‘5’ for the user’s fifth session
primary_publishing_organisation	STRING	NULLABLE	The organisation that published the content e.g. ‘Home Office’
page_referrer	STRING	NULLABLE	The address of the webpage where a user clicked a link that sent them to the current page
cleaned_page_referrer	STRING	NULLABLE	The path of the page referrer (the page_referrer without the protocol, hostname, and any query string
content_id	STRING	NULLABLE	The content attribute of the `<meta name="govuk:content-id">` tag
browse_topic	STRING	NULLABLE	The content attribute of the `<meta name="govuk:section">` tag
publishing_app	STRING	NULLABLE	The content attribute of the `<meta name="govuk:publishing-application">` tag e.g. ‘collections-publisher’
public_updated_at	STRING	NULLABLE	The date of the last public update (the ‘public_updated_at’ field within the Content API and content attribute of the `<meta name="govuk:public-updated-at">` tag)
updated_at	STRING	NULLABLE	The date of the last internal update (the ‘updated_at’ field within the Content API and content attribute of the `<meta name="govuk:updated-at">` tag)
first_published_at	STRING	NULLABLE	The original publish date (the ‘first_published_at’ field within the Content API and content attribute of the `<meta name="govuk:first-published-at">` tag)
taxonomy_all_ids_DEPRECATED	STRING	NULLABLE	The content attribute of the `<meta name="govuk:taxon-ids">` tag e.g. ‘a7f3005b-a3cd-4060-a127-725accb54f2e,65a25d2c-a0e5-4283-921d-c928babfb6e4’
status_code	INTEGER	NULLABLE	The HTTP response status code
withdrawn	STRING	NULLABLE	Default value of ‘false’, ‘true’ if `<meta name="govuk:withdrawn" content="withdrawn">` tag is present
document_type	STRING	NULLABLE	The content attribute of the `<meta name="govuk:format">` tag
history	STRING	NULLABLE	Default value of ‘false’, ‘true’ if `<meta name="govuk:content-has-history" content="true">` tag is present
taxonomy_main_id	STRING	NULLABLE	The content attribute of the `<meta name="govuk:taxon-id">` tag
taxonomy_all_DEPRECATED	STRING	NULLABLE	The content attribute of the `<meta name="govuk:taxon-slugs">` tag
taxonomy_main	STRING	NULLABLE	The content attribute of the `<meta name="govuk:taxon-slug">` tag
taxonomy_level_1	STRING	NULLABLE	The content attribute of the `<meta name="govuk:themes">` tag
full_taxonomy	STRING	NULLABLE	Deprecated - the taxonomy path parts, concatenated
full_taxonomy_ids	STRING	NULLABLE	Deprecated - the taxonomy ID path parts, concatenated
rendering_app	STRING	NULLABLE	The content attribute of the `<meta name="govuk:rendering-app">` tag
organisations	STRING	NULLABLE	Publishing organisation IDs - the content attribute of the `<meta name="govuk:analytics:organisations">` tag e.g. ‘<D25>’
session_engaged	STRING	NULLABLE
schema_name	STRING	NULLABLE	The content attribute of the `<meta name="govuk:schema-name">` tag
method	STRING	NULLABLE	The method with which the user clicked on the link e.g. ‘primary click’. Populated for navigation and information_click events only
engagement_time_msec	INTEGER	NULLABLE	The time the user has been engaged in milliseconds
event_campaign	STRING	NULLABLE	The campaign information associated with this event
content_language	STRING	NULLABLE	The language the content is written in
type	STRING	NULLABLE	The type of feature the user interacted with e.g. accordion, footer etc
event_source	STRING	NULLABLE	The source information associated with this event
session_source	STRING	NULLABLE	The source information associated with this session (the first source of the session)
link_text	STRING	NULLABLE	The text of the link clicked (sent with navigation and information_click events)
term	STRING	NULLABLE
link_url	STRING	NULLABLE	The href attribute of a link (sent with navigation and information_click events)
event_medium	STRING	NULLABLE	The medium information associated with this event
session_medium	STRING	NULLABLE	The medium information associated with this session (the first medium of the session)
link_domain	STRING	NULLABLE	The destination domain of a link (sent with navigation and information_click events)
event_previous_timestamp	INTEGER	NULLABLE	The time (in microseconds, UTC) the event was previously logged on the client
event_bundle_sequence_id	INTEGER	NULLABLE	The sequential ID of the bundle in which these events were sent to GA4
event_server_timestamp_offset	INTEGER	NULLABLE	Timestamp offset between collection time and upload time in microseconds
user_id	STRING	NULLABLE
user_first_touch_timestamp	INTEGER	NULLABLE	The time (in microseconds) when the user first visited the site
category	STRING	NULLABLE	The device category (mobile, tablet, desktop)
mobile_brand_name	STRING	NULLABLE	The device brand name
mobile_model_name	STRING	NULLABLE	The device model name
mobile_marketing_name	STRING	NULLABLE	The device marketing name
mobile_os_hardware_model	STRING	NULLABLE	The device model information retrieved directly from the operating system
operating_system	STRING	NULLABLE	The operating system of the device
operating_system_version	STRING	NULLABLE	The OS version
language	STRING	NULLABLE	The OS language
is_limited_ad_tracking	STRING	NULLABLE	The device’s Limit Ad Tracking setting
time_zone_offset_seconds	INTEGER	NULLABLE	The offset from GMT in seconds
device_browser	STRING	NULLABLE
device_browser_version	STRING	NULLABLE
browser	STRING	NULLABLE	The browser in which the user viewed content
browser_version	STRING	NULLABLE	The version of the browser in which the user viewed content
hostname	STRING	NULLABLE	The hostname associated with the logged event
continent	STRING	NULLABLE	The continent from which events were reported, based on IP address e.g. ‘Europe’
country	STRING	NULLABLE	The country from which events were reported, based on IP address e.g. ‘United Kingdom’
region	STRING	NULLABLE	The region from which events were reported, based on IP address e.g. ‘England’
city	STRING	NULLABLE	The city from which events were reported, based on IP address e.g. ‘Birmingham’
sub_continent	STRING	NULLABLE	The subcontinent from which events were reported, based on IP address e.g. ‘Northern Europe’
metro	STRING	NULLABLE	The metro from which events were reported, based on IP address e.g. ‘Midlands’
first_user_campaign	STRING	NULLABLE	The campaign through which this user first landed on GOV.UK
first_user_medium	STRING	NULLABLE	The medium through which this user first landed on GOV.UK
first_user_source	STRING	NULLABLE	The source through which this user first landed on GOV.UK
stream_id	STRING	NULLABLE	The numeric ID of the data stream from which the event originated
platform	STRING	NULLABLE	The data stream platform (Web, IOS or Android) from which the event originated
event_dimensions_hostname	STRING	NULLABLE
full_link_URL	STRING	NULLABLE
search_sort	STRING	NULLABLE	Where available this is the sort method e.g. ‘Relevance’
search_term	STRING	NULLABLE	The search term used
search_results	STRING	NULLABLE	The number of results returned by the search
ui_text	STRING	NULLABLE	Identifying text for the specific item the user interacted with
section	STRING	NULLABLE	Used to distinguish between multiple sections within an interaction element where the UI/Link text property is not granular enough
action	STRING	NULLABLE	The action the user has taken e.g. ‘opened’
index	INTEGER	NULLABLE	The index of the link that was interacted with e.g. if the third link was clicked the index would be ‘3’
index_total	INTEGER	NULLABLE	The total number of individual links within a feature e.g. if there are five links in total this would be ‘5’
political_status	STRING	NULLABLE	The content attribute of the `<meta name="govuk:political-status">` tag
publishing_government	STRING	NULLABLE	The content attribute of the `<meta name="govuk:political-status">` tag e.g. ‘historic’. This will only be available on ‘whitehall’ pages
world_locations	STRING	NULLABLE	The content attribute of the `<meta name="govuk:analytics:world-locations">` tag
dclid	STRING	NULLABLE	The Google Marketing Platform (GMP) identifier that was collected with the event
debug_mode	INTEGER	NULLABLE
engaged_session_event	INTEGER	NULLABLE
entrances	INTEGER	NULLABLE	A flag to indicate an entrance, where ‘1’ indicates an entrance
firebase_conversion	INTEGER	NULLABLE
gclid	STRING	NULLABLE	The Google click identifier that was collected with the event
gclsrc	STRING	NULLABLE
content	STRING	NULLABLE
campaign_id	STRING	NULLABLE
index_section	INTEGER	NULLABLE	The index of the section that was interacted with e.g. if the third tab was clicked the index would be ‘3’
index_section_total	INTEGER	NULLABLE	The total number of sections within a feature e.g. if there are five tabs in total this would be ‘5’
tool_name	STRING	NULLABLE	The name of the tool (the smart answer or form)
response	STRING	NULLABLE	The user’s reponse to a form question or error message
ab_test	STRING	NULLABLE	The content attribute of the `<meta name="govuk:ab-test">` tag
navigation_list_type	STRING	NULLABLE	The content attribute of the `<meta name="govuk:navigation-list-type">` tag
navigation_page_type	STRING	NULLABLE	The content attribute of the `<meta name="govuk:navigation-page-type">` tag
percent_scrolled	INTEGER	NULLABLE	The percentage scroll depth the user has reached
sfmc_activity_id	STRING	NULLABLE
sfmc_activity_name	STRING	NULLABLE
sfmc_asset_id	STRING	NULLABLE
sfmc_channel	STRING	NULLABLE
sfmc_journey_id	STRING	NULLABLE
sfmc_journey_name	STRING	NULLABLE
step_navs	STRING	NULLABLE	The content attribute of the `<meta name="govuk:stepnavs">` tag
manual_campaign_id	STRING	NULLABLE	The manual campaign id (utm_id) that was collected with the event
manual_campaign_name	STRING	NULLABLE	The manual campaign name (utm_campaign) that was collected with the event
manual_source	STRING	NULLABLE	The manual campaign source (utm_source) that was collected with the event. Also includes parsed parameters from referral params, not just UTM values
manual_medium	STRING	NULLABLE	The manual campaign medium (utm_medium) that was collected with the event. Also includes parsed parameters from referral params, not just UTM values
manual_term	STRING	NULLABLE	The manual campaign keyword/term (utm_term) that was collected with the event
is_active_user	BOOLEAN	NULLABLE	Whether the user was active (True) or inactive (False) at any point in the calendar day
video_title	STRING	NULLABLE	The title of the video (only sent with video type events)
video_url	STRING	NULLABLE	The URL of the video (only sent with video type events)
video_duration	INTEGER	NULLABLE	The length of the video in seconds (only sent with video type events)
video_current_time	INTEGER	NULLABLE	The time through the video at the moment the event fired in seconds (only sent with video type events)
video_percent	INTEGER	NULLABLE	The percent through the video the user was at the moment the event fired (only sent with video type events)
cookie_banner	STRING	NULLABLE	‘True’ when the cookie banner is displayed to the user
devolved_nations_banner	STRING	NULLABLE	Populated when a devolved nations banner is on the page, e.g. ‘England’ when the banner says ‘Applies to England’
intervention	STRING	NULLABLE	‘True’ when an intervention is displayed to the user
phase_banner	STRING	NULLABLE	Populated when a phase banner is on the page, e.g. ‘beta’ when the beta banner is shown
outbound	STRING	NULLABLE	‘True’ if a link clicked leads away from www.gov.uk
query_string	STRING	NULLABLE	The query string extracted from the page location
spelling_suggestion	STRING	NULLABLE	The spelling suggestion, if one is shown, on a search results page
outcome	STRING	NULLABLE	The outcome reached at the end of form (only sent with form_complete events)
viewport_size	STRING	NULLABLE	The size of the browser window, minus the scroll bars and toolbars
batch_page_id	INTEGER	NULLABLE
batch_ordering_id	INTEGER	NULLABLE
timestamp	INTEGER	NULLABLE	The Unix timestamp in milliseconds. This is a custom dimension, pushed with every dataLayer event, unlike the default event_timestamp above
copy_length	INTEGER	NULLABLE	The number of characters in text copied (copy events only)
item_list_name	STRING	NULLABLE	The name of the item list - on GOV.UK, this will be the name of a finder (search/search results) page
item_id	STRING	NULLABLE	The URL of the search result item. A maximum of 200 search result items (the first 200) will be sent on longer search results pages
item_name	STRING	NULLABLE
item_list_index	STRING	NULLABLE	The index of the search result item. A maximum of 200 search result items (the first 200) will be sent on longer search results pages
item_content_id	STRING	NULLABLE	The content ID of the search result item
emergency_banner	STRING	NULLABLE	Value is true if the user is served the emergency banner
global_bar	STRING	NULLABLE	Value is set to true when a user is shown the global bar - e.g. when it reminded users to register to vote
page_tool_name	STRING	NULLABLE	A tool_name value sent with the page_view event
autocomplete_input	STRING	NULLABLE	The user’s input used to generate search autocomplete suggestions
autocomplete_suggestions	STRING	NULLABLE	The last list of suggestions offered by the autocomplete feature
autocomplete_count	INTEGER	NULLABLE	The number of autocomplete suggestions shown

Retention

At present, there is no default table expiry set up for this table, and no agreed data retention period.

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

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