Why does GA4 load 3 times when creating the event table in Bigquery?

user_10085681 0
Viewed 152

I want to know when the events table creation job is completed by using Cloud Logging.

protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.load.destinationTable.projectId="PROJECT_ID"
protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.load.destinationTable.datasetId="DATASET_ID"

protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.load.destinationTable.tableId="TABLE_ID"
protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.load.writeDisposition="WRITE_TRUNCATE"
protoPayload.methodName="jobservice.jobcompleted"

but I've checked that GA4 loads 3 times when creating the event table at Cloud Logging. enter image description here

And my question is that

  1. why does GA4 load 3 times when creating the event table in Bigquery?
  2. Which cloud logging query will tell me when the final event table is created?

Thanks in advance. and have a nice day everyone seeing this stupid question :)

google-cloud-logging google-analytics-4 google-bigquery
2 Answers

Using User-defined log-based metrics and The AuditData messages in BigQuery audit logs, you can create an Alert policy that lets you know when a BigQuery table is created.

The audit log message system relies on structured logs, and the BigQuery service provides three distinct kinds of messages:

  • AuditData: The old version of logs, which reports API invocations.
  • BigQueryAuditMetadata: The new version of logs, which reports resource interactions such as which tables were read from and written to by a given query job and which tables expired due to having an
    expiration time configured.
  • AuditLog: The logs that BigQuery Reservations and BigQuery Connections use when reporting requests.

Using BigQueryAuditMetadata, specifically the BigQueryAuditMetadata.TableCreation event, you can create the filter for your alerting policy using the JSON format.

{
  "table": {
    object (BigQueryAuditMetadata.Table)
  },
  "reason": enum (BigQueryAuditMetadata.TableCreation.Reason),
  "jobName": string
}

To create a distribution metric by using the Google Cloud CLI, specify the full metric configuration using the --config-from-file flag. The configuration format is JSON or YAML. For a full description of the LogMetric configuration, see projects.metrics.

For more detailed information about:

user_169223420

Logging query for new GA4 export tables created on BigQuery

This query will only match the initial table creation entries so you get one log event per day.

protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"
protoPayload.metadata.tableCreation.reason="JOB"
protoPayload.authorizationInfo.permission:"bigquery.tables.create"
NOT protoPayload.resourceName:"_intraday" -- ignore intraday table creation

Multiple GA4 exports connected to a single BigQuery project

Isolate specific tables by replacing DATASET_NAME with the relevant id from your BigQuery dataset.

protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"
protoPayload.metadata.tableCreation.reason="JOB"
protoPayload.authorizationInfo.permission:"bigquery.tables.create"
NOT protoPayload.resourceName:"_intraday" -- ignore intraday table creation
protoPayload.resourceName:"DATASET_NAME/tables/events_" -- OPTIONAL for BigQuery projects connected to more than one GA4 export
user_139895870
Related