GOV.UK historic data report

The GOV.UK historic data report displays historic Universal Analytics (UA) data in tables that contain similar information to the old Universal Analytics interface. The data in the report runs from 6th April 2021 to 30th June 2024.

The report is split into several pages, with the sections down the left hand side designed to mirror the old UA interface. Within each section you will find individual report pages which again have been designed to mimic the old UA interface.

The last section in the report called ‘References’ contains screenshots of the original report pages in the UA interface for comparison.

Using the GOV.UK Historic Data Dashboard

Get access to the GOV.UK Historic Data Dashboard

The report can be viewed by anyone with a @digital.cabinet-office.gov.uk email address. You can share the report simply by sending the URL to others. If you require higher access for any reason, please contact the Analytics team who can grant you this or otherwise help you get what you need.

Those without an @digital.cabinet-office.gov.uk email address will not be able to view this report at the moment. We are working on a solution to better enable access to historic UA data for public sector workers outside of GDS. For the time being, those outside of GDS will have to query the UA data in BigQuery.

It can be edited by anyone in the GDS performance analysts Google group.

Data quality notes

The filters which were applied to the Main view of the www.gov.uk UA property have not been replicated in this dashboard. This will lead to differing figures in some places. If you have questions about any of the data you see in this report, please get in touch with the Analytics team.

How the GOV.UK historic data report works

Data sources

The GOV.UK historic data report draws Universal Analytics data from BigQuery, specifically from the govuk-bigquery-analytics project, and the FlattenedDailyData dataset. Within this dataset, there are a series of flattened tables containing UA data, which is what this report uses.

Custom Looker Studio queries

Because the dashboard required many calculated fields and dimensions not natively available in the raw UA data, each page has its own custom query written in Looker Studio. Each custom query is specific to the dimensions and metrics required to replicate the report page in question.

Should the dashboard need to be modified or recreated, these custom queries should be fairly straightforward to decipher. They all share the same FROM and WHERE clause values, referencing the flattened UA data, and using the built-in @DS_START_DATE and @DS_END_DATE date variables in Looker Studio:

FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
  WHERE
     _table_suffix BETWEEN @DS_START_DATE
     AND @DS_END_DATE

The custom queries are as follows:

Audience - Overview page

SELECT
date,
language,
CASE 
  WHEN newVisits = 1 THEN 'New Visitor'
  WHEN newVisits IS NULL THEN 'Returning Visitor'
END AS user_type,
count(distinct(fullvisitorid)) as user_count,
count(distinct if(newVisits = 1, fullVisitorId, NULL)) AS new_visitors,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) as sessions,
count(bounces) as bounce_count,
COUNT(CASE WHEN type = 'PAGE' THEN 1 END) AS page_count,
sum(timeonsite/hits) as session_time_seconds,
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
  WHERE
     _table_suffix BETWEEN @DS_START_DATE
     AND @DS_END_DATE
Group by 1,2,3

Audience - Language page

SELECT
date,
language,
count(distinct(fullvisitorid)) as user_count,
count(distinct if(newVisits = 1, fullVisitorId, NULL)) AS new_visitors,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) as sessions,
count(bounces) as bounce_count,
COUNT(CASE WHEN type = 'PAGE' THEN 1 END) AS page_count,
sum(timeonsite/hits) as session_time_seconds,
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
   WHERE
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
Group by 1,2

Audience - New vs Returning Users page

with new_visitors as (
Select date,
'new_visitor' AS user_type,
fullvisitorid, 
visitStartTime,
bounces,
type,
timeonsite,
hits
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
WHERE newVisits = 1 
  AND 
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
),

returning_visitors as(
Select date,
'returning_visitor' AS user_type, 
fullvisitorid,
visitStartTime,
bounces,
type,
timeonsite,
hits
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
WHERE newVisits IS NULL
    AND 
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
)

SELECT 
  date, user_type, 
  COUNT(DISTINCT fullvisitorid) AS users,
  COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) AS sessions,
  COUNT(bounces) AS bounce_count,
  COUNT(CASE WHEN type = 'PAGE' THEN 1 END) AS page_count,
  SUM(timeonsite / hits) AS session_time_seconds
FROM 
  returning_visitors
GROUP BY 
  1, 2

UNION ALL

SELECT 
  date, user_type, 
  COUNT(DISTINCT fullvisitorid) AS users,
  COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) AS sessions,
  COUNT(bounces) AS bounce_count,
  COUNT(CASE WHEN type = 'PAGE' THEN 1 END) AS page_count,
  SUM(timeonsite / hits) AS session_time_seconds
FROM 
  new_visitors
GROUP BY 
  1, 2

Audience - Location page

SELECT
date,
country,
count(distinct(fullvisitorid)) as user_count,
count(distinct if(newVisits = 1, fullVisitorId, NULL)) AS new_visitors,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) as sessions,
count(bounces) as bounce_count,
COUNT(CASE WHEN type = 'PAGE' THEN 1 END) AS page_count,
sum(timeonsite/hits) as session_time_seconds,
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
   WHERE
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
Group by 1,2

Acquisition - Channels page

SELECT date, channelGrouping as channel,
count(distinct(fullvisitorid)) as user_count,
count(distinct if(newVisits = 1, fullVisitorId, NULL)) AS new_visitors,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) as sessions,
count(bounces) as bounce_count,
COUNT(CASE WHEN type = 'PAGE' THEN 1 END) AS page_count,
sum(timeonsite/hits) as session_time_seconds,
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
   WHERE
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
Group by 1, 2

Acquisition - Source/Medium page

SELECT date, source, medium,
count(distinct(fullvisitorid)) as user_count,
count(distinct if(newVisits = 1, fullVisitorId, NULL)) AS new_visitors,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) as sessions,
count(bounces) as bounce_count,
COUNT(CASE WHEN type = 'PAGE' THEN 1 END) AS page_count,
sum(timeonsite/hits) as session_time_seconds,
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
   WHERE
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
Group by 1, 2, 3

Acquisition - Referrals page

SELECT date, source, medium,
count(distinct(fullvisitorid)) as user_count,
count(distinct if(newVisits = 1, fullVisitorId, NULL)) AS new_visitors,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) as sessions,
count(bounces) as bounce_count,
COUNT(CASE WHEN type = 'PAGE' THEN 1 END) AS page_count,
sum(timeonsite/hits) as session_time_seconds,
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
   WHERE
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
Group by 1, 2, 3

Behaviour - All Pages page (and pages by date page)

  #Query to replicate the UA All Pages report
  #Page views and unique page views
WITH
  PVS AS (
  SELECT
    date,
    pagePath,
    COUNT(*) AS pageviews,
    COUNT(DISTINCT session_id) AS unique_pageviews
  FROM (
    SELECT
      date,
      pagePath,
      CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)) AS session_id
    FROM
      `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*`
    WHERE
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
      AND type = 'PAGE')
  GROUP BY
    pagePath,
    date
  ORDER BY
    pageviews DESC ),
  #Average time on page
  ATOP AS (
  SELECT
    date,
    pagePath,
    pageviews,
    exits,
    total_time_on_page,
    CASE
      WHEN pageviews = exits THEN 0
      ELSE total_time_on_page / (pageviews - exits)
  END
    AS avg_time_on_page
  FROM (
    SELECT
      date,
      pagePath,
      COUNT(*) AS pageviews,
      SUM(
      IF
        (isExit IS NOT NULL, 1, 0)) AS exits,
      SUM(time_on_page) AS total_time_on_page
    FROM (
      SELECT
        date,
        fullVisitorId,
        visitStartTime,
        pagePath,
        hit_time,
        type,
        isExit,
        CASE
          WHEN isExit IS NOT NULL THEN last_interaction - hit_time
          ELSE next_pageview - hit_time
      END
        AS time_on_page
      FROM (
        SELECT
          date,
          fullVisitorId,
          visitStartTime,
          pagePath,
          hit_time,
          type,
          isExit,
          last_interaction,
          LEAD(hit_time) OVER (PARTITION BY fullVisitorId, visitStartTime ORDER BY hit_time) AS next_pageview
        FROM (
          SELECT
            date,
            fullVisitorId,
            visitStartTime,
            pagePath,
            hit_time,
            type,
            isExit,
            last_interaction
          FROM (
            SELECT
              date,
              fullVisitorId,
              visitStartTime,
              pagePath,
              type,
              isExit,
              time / 1000 AS hit_time,
              MAX(
              IF
                (isInteraction IS NOT NULL, time / 1000, 0)) OVER (PARTITION BY fullVisitorId, visitStartTime) AS last_interaction
            FROM
              `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*`
            WHERE
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
              AND type = 'PAGE'))
        GROUP BY
          date,
          pagePath,
          fullVisitorId,
          visitStartTime,
          hit_time,
          type,
          isExit,
          last_interaction )
      GROUP BY
        date,
        pagePath,
        fullVisitorId,
        visitStartTime,
        hit_time,
        type,
        isExit,
        last_interaction,
        next_pageview)
    GROUP BY
      pagePath,
      date
    ORDER BY
      pageviews DESC ) ),
  #Entrances
  ENTRANCES AS (
  SELECT
    date,
    pagePath,
    SUM(entrances) AS entrances
  FROM (
    SELECT
      date,
      pagePath,
      CASE
        WHEN isEntrance IS NOT NULL THEN 1
        ELSE 0
    END
      AS entrances
    FROM
      `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*`
    WHERE
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE)
  GROUP BY
    pagePath,
    date
  ORDER BY
    entrances DESC ),
  #Bounces
  BOUNCES AS (
  SELECT
    date,
    pagePath,
    bounces,
    sessions,
    CASE
      WHEN sessions = 0 THEN 0
      ELSE bounces / sessions
  END
    AS bounce_rate
  FROM (
    SELECT
      date,
      pagePath,
      SUM(bounces) AS bounces,
      SUM(sessions) AS sessions
    FROM (
      SELECT
        date,
        fullVisitorId,
        visitStartTime,
        pagePath,
        CASE
          WHEN hitNumber = first_interaction THEN bounces
          ELSE 0
      END
        AS bounces,
        CASE
          WHEN hitNumber = first_hit THEN visits
          ELSE 0
      END
        AS sessions
      FROM (
        SELECT
          date,
          fullVisitorId,
          visitStartTime,
          pagePath,
          bounces,
          visits,
          hitNumber,
          MIN(
          IF
            (isInteraction IS NOT NULL, hitNumber, 0)) OVER (PARTITION BY fullVisitorId, visitStartTime) AS first_interaction,
          MIN(hitNumber) OVER (PARTITION BY fullVisitorId, visitStartTime) AS first_hit
        FROM
          `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*`
        WHERE
        _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
        GROUP BY
          fullVisitorId,
          visitStartTime,
          date,
          pagePath,
          bounces,
          visits,
          hitNumber,
          isInteraction)
      ORDER BY
        sessions DESC )
    GROUP BY
      pagePath,
      date) ),
  #Exits
  EXITS AS (
  SELECT
    date,
    pagePath,
    pageviews,
    exits,
    CASE
      WHEN pageviews = 0 THEN 0
      ELSE exits / pageviews
  END
    AS exit_rate
  FROM (
    SELECT
      date,
      pagePath,
      COUNT(*) AS pageviews,
      SUM(exits) AS exits
    FROM (
      SELECT
        date,
        pagePath,
        CASE
          WHEN isExit IS NOT NULL THEN 1
          ELSE 0
      END
        AS exits
      FROM
        `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*`
      WHERE
        type = 'PAGE'
      AND
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE)
    GROUP BY
      pagePath,
      date))
  #Joining everything
SELECT
  PVS.date,
  PVS.pagePath AS page_path,
  PVS.pageviews,
  PVS.unique_pageviews,
  ATOP.avg_time_on_page,
  ATOP.total_time_on_page,
  ATOP.exits,
  ENTRANCES.entrances,
  BOUNCES.bounces,
  BOUNCES.sessions,
  BOUNCES.bounce_rate,
  EXITS.exit_rate
FROM
  PVS
JOIN
  ATOP
ON
  PVS.pagePath = ATOP.pagePath
  AND PVS.date = ATOP.date
JOIN
  EXITS
ON
  PVS.pagePath = EXITS.pagePath
  AND PVS.date = EXITS.date
JOIN
  ENTRANCES
ON
  PVS.pagePath = ENTRANCES.pagePath
  AND PVS.date = ENTRANCES.date
JOIN
  BOUNCES
ON
  PVS.pagePath = BOUNCES.pagePath
  AND PVS.date = BOUNCES.date
ORDER BY
  date DESC,
  pageviews DESC,
  unique_pageviews DESC

Behaviour - Top Events page

SELECT
  date,
  pagePath AS page_path,
  eventCategory AS event_category,
  eventAction AS event_action,
  eventLabel AS event_label,
  COUNT(*) AS total_events,
  COUNT(DISTINCT CONCAT(CAST(fullvisitorid AS string), CAST(visitstarttime AS string))) AS unique_events,
    FROM
      `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*`
    WHERE
      _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE
AND
  visits = 1
  AND type = 'EVENT'
  AND eventCategory IS NOT null
GROUP BY
  date,
page_path,
event_category,
event_action,
event_label
ORDER BY total_events DESC

Behaviour - Search Terms page

WITH DATA AS(
  SELECT
    date,
  fullVisitorId,
    visitStartTime,
    LOWER(searchkeyword) AS search_term,
    type,
  FROM
    `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*`
  WHERE
    type = "PAGE" 
     AND _table_suffix BETWEEN @DS_START_DATE
      AND @DS_END_DATE)
SELECT
  date,
  search_term,
  COUNT (*) AS total_searches,
  COUNT(DISTINCT CONCAT(CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)), search_term)) AS total_unique_searches,
  COUNT(CONCAT(CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)))) AS results_pageview,
  (COUNT(CONCAT(CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)))))/(COUNT(DISTINCT CONCAT(CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)), search_term))) AS results_pageview_search
FROM
  DATA
WHERE 
search_term IS NOT NULL
GROUP BY
  date, search_term
ORDER BY
  total_unique_searches DESC

Behaviour - Landing Pages page

SELECT
 date,
landingScreenName,
count(distinct(fullvisitorid)) as user_count,
count(distinct if(newVisits = 1, fullVisitorId, NULL)) AS new_visitors,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) as sessions,
count(bounces) as bounce_count,
COUNT(CASE WHEN type = 'PAGE' THEN 1 END) AS page_count,
sum(timeonsite/hits) as session_time_seconds,
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
  WHERE
     _table_suffix BETWEEN @DS_START_DATE
     AND @DS_END_DATE
Group by 1,2

Behaviour - Exit Pages page

SELECT
date, screenName,
SUM(CASE WHEN isexit = TRUE THEN 1 ELSE 0 END) AS exit_count,
SUM(CASE WHEN type = 'PAGE' THEN 1 ELSE 0 END) AS page_view_count
FROM `govuk-bigquery-analytics.FlattenedDailyData.flattened_daily_ga_data_*` 
  WHERE
     _table_suffix BETWEEN @DS_START_DATE
     AND @DS_END_DATE
GROUP BY 1,2

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

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