GA4 query costs dashboard
The GA4 query costs dashboard uses BigQuery log data to show queries and processing costs from jobs which have been run in GDS-owned GCP projects.
The report is split into several pages, breaking down the data by various dimensions including jobs by user, jobs by project, and by specific query.
By monitoring this report, we can spot any jobs which result in unusually large query costs, and other changes or activity that is unexpected.
Using the GA4 query costs dashboard
Get access to the GA4 query costs dashboard
The report can be viewed by anyone with a @digital.cabinet-office.gov.uk email address.
It can be edited by anyone in the GDS performance analysts Google group.
How the GA4 query costs dashboard works
Data sources
The GA4 Query Costs Dashboard uses the ‘BigQuery use across projects’ data source. This is a custom query which pulls together data from a couple of different sources:
- the
_AllLogstable in the ‘all_gcp_logs’ dataset which contains log data from all GDS-owned GCP projects - the
exchange_ratetable in the ‘exchange_rate’ dataset which contains a USD to GBP conversion rate
The custom query which powers the charts in the dashboard works by first, in the first CTE, extracting various fields relating to GCP jobs from the _AllLogs table, and filtering the logs to select only those related to BigQuery resources.
This gets the timestamp, the user who executed the job, the total billed data (in TiB and MiB), the contents of the SQL query, the project ID and referenced tables.
And IF statement also classifies queries as originating from either Looker or BigQuery based on identifiers in the query text.
The second CTE pulls in exchange rate values from the exchange rate table, and, in the final step, the query combines the outputs of these two CTEs and calculates an estimated cost in USD based on a fixed cost rate of approximately $7.16 per TiB of data processed. This is roughly the rate we are paying for our data processing costs, in accordance with the rates determined by Google. The results include details like the user, project, and query classification, along with the aggregated data usage and cost, providing an overview of BigQuery resource consumption and associated expenses.
The full custom query is as follows:
```SQL
WITH cte AS (
SELECT
FORMAT_TIMESTAMP(“%Y-%m-%d”, TIMESTAMP) AS formatted_timestamp,
proto_payload.audit_log.authentication_info.principal_email as user,
CAST(replace(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.totalBilledBytes), ‘”’,’’) AS INT64)/1024/1024/1024/1024 as TiB,
CAST(replace(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.totalBilledBytes), ‘”’,’’) AS INT64)/1024/1024 as MiB,
replace(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobConfiguration.query.query), ‘\n’,’’) as query,
IF (replace(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobConfiguration.query.query), ‘\n’,’’) LIKE ‘%t0%’,’Looker’,’BQ’) as type,
JSON_VALUE(resource.labels,’$.project_id’) as project,
proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.referencedTables,
JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatus.state) as job_status
FROM
gds-bq-reporting.all_gcp_logs._AllLogs
WHERE
resource.type = “bigquery_resource”
AND
severity = “INFO”
AND
JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.totalBilledBytes) IS NOT null
AND
proto_payload.audit_log.method_name =”jobservice.jobcompleted”
AND
JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.billingTier)=”1”
),
exchange_rate AS (
SELECT SUM(exchange_rate) as exchange_rate
FROM gds-bq-reporting.exchange_rate.exchange_rate
)
SELECT GENERATE_UUID() AS uuid, formatted_timestamp, user, type, project, query, job_status, e.exchange_rate, sum(MiB) AS MiB, sum(TiB) AS TiB, sum(TiB * 7.16) AS Estimated_USD_Cost FROM cte, exchange_rate e GROUP BY uuid, formatted_timestamp, user, type, project, query, job_status, e.exchange_rate ```