Skip to main content

GOV.UK GA4 SPOCs reporting

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

The data provided includes:

  • the number of users the viewer is the SPOC for
  • the GOV.UK GA4 usage of users the viewer is the SPOC for

This report was first created in early 2024 to support SPOCs and is owned by the GOV.UK Insights and Analytics team.

Using the GOV.UK GA4 SPOCs reporting

Get access to the GOV.UK GA4 SPOCs reporting

The report can be viewed by anyone, but will only work for those who are documented as being SPOCs in the ‘SPOC and Numbers by org’ Sheet.

Viewers will have to give Google consent to read their account’s email address in order to view the report.

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

The ‘GOV.UK GA4 SPOC user report’ data source uses the viewer’s credentials, so users will also require permissions to view the base tables in BigQuery.

GOV.UK GA4 SPOCs reporting data definitions

Field name Definition Notes
access_count The number of times GA reporting data was accessed. Note that every report viewed can result in one or more data access events From the GA4 access logs data
access_mechanism The mechanism through which a user accessed GA reporting data, for example ‘Google Analytics User Interface’ or ‘Google Analytics API’ From the GA4 access logs data
accessor_app_name The name of the application that accessed Google Analytics reporting data, for example ‘Looker Studio’ or ‘Power BI’ From the GA4 access logs data
api_quota_category The quota category for the Data API request, for example ‘Core’ or ‘Realtime’ From the GA4 access logs data
api_tokens_consumed The number of property quota tokens consumed for Data API requests From the GA4 access logs data
domain The email domain, taken from the user’s email address Extracted from the GA4 user admin tool data
epoch_time_micros The unix microseconds since the epoch that the GA user accessed GA reporting data From the GA4 access logs data
last_accessed The date value of the maximum (most recent) epoch_time_micros A calculated field added to the data source
report_type The type of reporting data that the GA user accessed, for example ‘Realtime’ or ‘Free form exploration’ From the GA4 access logs data
spoc The SPOC From the GA4 user admin tool data
status The last status update in the GA4 user admin tool data, for example ‘created’ or ‘deleted’ From the GA4 user admin tool data
status_date The date the user’s status was last updated by the GA4 user admin tool From the GA4 user admin tool data
user_email The user’s email address From the GA4 user admin tool data
Record Count A count of the records (rows) in the table Default Looker Studio field

How the GOV.UK GA4 SPOCs reporting works

Data sources

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

The all_users table is filtered so that only users with GOV.UK GA4 data access are included, and the GA4 access logs data is also filtered to the Looker Studio date parameters. The whole data source is filtered by the viewer’s email address, so that SPOCs can only see their organisation’s users’ data.

This data source is very similar to the GOV.UK GA4 users monitoring Looker Studio data source, but that data source does not enable the viewer email address parameter or filter by user email.

The custom query is as follows:

WITH 
ga4_users as (
  SELECT 
    email AS user_email,
    REGEXP_EXTRACT(email, r'@.+$') as domain
  FROM `ga4-analytics-352613.user_admin.all_users`
  WHERE binding LIKE 'properties/330577055%'
),

ga4_admin_log as(
  SELECT 
    x.user_email, 
    x.status, 
    x.date 
  FROM (
    SELECT 
      user_email, 
      status, 
      date,
      row_number() over (partition by user_email order by date desc) as _rn
    FROM `ga4-analytics-352613.user_admin.user_admin_log` 
  ) x
WHERE x._rn = 1
),

ga4_logs as (
  SELECT 
    epoch_time_micros,
    user_email,
    domain,
    access_mechanism,
    accessor_app_name,
    api_quota_category,
    report_type,
    access_count,
    api_tokens_consumed
  FROM `ga4-analytics-352613.ga4_logs.ga4_logs`
  WHERE TIMESTAMP_TRUNC(epoch_time_micros, DAY) BETWEEN PARSE_TIMESTAMP("%Y%m%d", @DS_START_DATE) AND PARSE_TIMESTAMP("%Y%m%d", @DS_END_DATE)
),

__spocs as (
  SELECT 
    array(select trim(val) from unnest(split(trim(email_domain, '[]'))) val) AS email_domain,
    array(select trim(val) from unnest(split(trim(spoc, '[]'))) val) AS spoc
  FROM `ga4-analytics-352613.user_admin.spocs`
),

_spocs as (
  SELECT
    email_domain,
    spoc
  FROM __spocs, unnest(email_domain) email_domain
),

spocs as (
  SELECT
    email_domain,
    spoc
  FROM _spocs, unnest(spoc) spoc
  GROUP BY 1, 2
)

SELECT 
  A.user_email,
  A.domain,
  B.status,
  B.date as status_date,
  C.* EXCEPT (user_email, domain),
  S.spoc
FROM ga4_users A 
LEFT JOIN ga4_admin_log B on A.user_email = B.user_email
LEFT JOIN ga4_logs C on A.user_email = C.user_email
LEFT JOIN spocs S on A.domain = S.email_domain
This page was last reviewed on 12 November 2024. It needs to be reviewed again on 12 May 2025 .
This page was set to be reviewed before 12 May 2025. This might mean the content is out of date.