Backfill Google Ads traffic source data as soon as possible (GA 360)

AI-generated Key Takeaways

outlined_flag

• This guide helps you automatically backfill missing traffic source data in BigQuery Fresh Daily exports, typically by 5 AM each day.

• Automating the backfill involves listening for a daily completeness signal, identifying missing data, querying Google Ads for complete data, and joining it with your BigQuery export.

• The process includes creating a Pub/Sub topic to receive the completeness signal and a Log Router sink to route relevant logs to the Pub/Sub topic.

• A Cloud Run function is used to automate the execution of code that joins the missing data when triggered by the Pub/Sub signal.

If you use BigQuery Fresh Daily exports, you might see Data Not Available for some traffic_source fields. This guide helps you automatically backfill most of the missing traffic source data in existing exports as soon as it's available, typically by 5 AM each day.

Here are the steps to automate the backfill:

1. Listen for the daily completeness signal from BigQuery.
2. Identify the events with missing traffic source data in your BigQuery export.
3. Query the complete data for those events from Google Ads.
4. Join the complete event data with your BigQuery export.

Create a Pub/Sub topic

1. Open Pub/Subin the left navigation menu of the Google Cloud console . If you don't see Pub/Sub, search for it in the Google Cloud console search bar:
2. Click + CREATE TOPICin the Topicstab:
3. Enter a name in the Topic IDfield.
4. Select Add a default subscription, leave the other options blank:
5. Click Create.

Create a Log Router sink

1. Open Log routerin the Google Cloud console:
2. Click Create sink:
3. Enter a name and description for your sink, then click Next.
4. Choose Cloud Pub/Sub topicas the sink service.
5. Choose the topic you created, then click Next.

6. Enter the following code in Build inclusion filter:

    logName="projects/ YOUR-PROJECT-ID 
   /logs/analyticsdata.googleapis.com%2Ffresh_bigquery_export_status" 
   

   Replace YOUR-PROJECT-ID with the ID for your Google Cloud console project.

7. Click Next, then click Create sink. You don't need to filter out any logs.

8. Verify the sink is now listed under Log Router Sinks.

Join the missing data

Use a Cloud Run function to automatically execute the code to backfill traffic source data when Pub/Sub detects the completeness signal:

1. Open Cloud Run functions:
2. Click CREATE FUNCTION:
3. Choose Cloud Run functionfor the Environment.
4. Enter a name for your function.
5. Choose Cloud Pub/Subas the Trigger type, and the topic you created as the Cloud Pub/Sub topic.
6. Click Next, then enter your code to join the Google Ads attribution data with your BigQuery export in the box.