Offline Conversions to Google Ads

Import Offline Conversions to Google Ads Using BigQuery & Data Manager

Tracking offline conversions is challenging, especially when the customer journey stretches over days or weeks. Imagine you run a SaaS or streaming service: someone clicks your ad, creates a free trial account, and, after a few days, their trial becomes a paying subscription. How do you ensure that final conversion is attributed to your ad?

In this post, we’ll explore the new Google Ads Data Manager and Google BigQuery integration, which allows for uploading offline conversions directly to Google Ads, ensuring correct attribution of offline events.

Disclaimer

This solution does not require personal data like email or phone numbers, which many companies prefer to avoid for privacy reasons. If you’re open to using personal data, consider exploring Enhanced Conversions.

Tools Required

• GTM Web
• GA4 + BigQuery Export
• Measurement Protocol v2

The Challenge

Consider the following customer journey:

• Ad Click: A user clicks on an ad for your product or service.
• Web Session: The user visits your website, exploring your offerings and engaging with your content.
• Micro Conversion: The user creates a free trial account. At this stage, there’s no direct revenue, but the user is closer to becoming a paying customer. This is a key micro-conversion.
• User Inactivity: The user may be inactive now. They could be offline, not triggering any tracking events in your application.
• Macro Conversion (Offline): Up to seven days later, the free trial converts into a paying subscription. This conversion subscription_start, which drives actual revenue, occurs offline — triggered by backend systems.

Here, we encounter a unique scenario: the macro conversion subscription_start is system-triggered, potentially while the user is inactive. The challenge is ensuring the system notifies Google Ads about the conversion, linking it to the Google Click ID gclid associated with the ad click.

Data Manager & BigQuery

In early 2024, Google introduced Data Manager within Google Ads, allowing connections to data sources, including importing conversions via BigQuery. By maintaining an up-to-date BigQuery table with recent conversions and the corresponding gclid, Google Ads will register them accurately. The end goal is in this guide is to create a BigQuery table with conversion_timestamp and google_click_id, then connect it to Google Ads. For this to happen, we will collect the necessary data to Google Analytics 4, and stitch the conversion to the google_click_id using BigQuery.

Prerequisites

To set this up, ensure you have the following:

1. Google Analytics 4 Website Tracking

   • Ensure basic web tags are set up, particularly for page_view and free_trial (triggered upon account creation).
   • Important: Include a User ID in the user_id field on the free_trial event, generated consistently by your system throughout the conversion journey for each user.

2. Offline Conversions with GA4 Measurement Protocol

   • When the offline conversion happens, send the event subscription_start to Google Analytics via GA4 Measurement Protocol, which allows sending GA4 events directly from any web-connected device.
   • Note: Ensure a User ID is included in this hit as well.

3. Google BigQuery (GA4 Export)

   • If not already active, set up GA4 to BigQuery export. This daily export of event-level data from GA4 to BigQuery forms the dataset needed for the Google Ads upload.

Note: In this case, we’ll use the events free_trial and subscription_start. You can, of course, modify the solution to align with alternative events in your GA4 setup.

Creating the BigQuery Table

The goal is to match the offline conversion with a GCLID. Since User ID is linked to both free_trial and subscription_start, this can be achieved using a SQL query in BigQuery.

This is the code we’re using. Make sure you replace the table details with your own project and dataset in the from clause

The code

with events as (
  select 
    event_name, 
    user_id,
    event_timestamp, 
    event_params,
    last_value(
      (select value.string_value from unnest(event_params) where key = 'gclid') ignore nulls
    ) over(partition by user_pseudo_id order by event_timestamp range between 2592000000000 preceding and current row)
    as previous_gclid
  from `your_project.your_dataset.events_*`
  where
    (_table_suffix between format_date('%Y%m%d', date_sub(current_date, interval 5 week)) and format_date('%Y%m%d', date_sub(current_date, interval 1 day))) and
    user_pseudo_id is not null and
    event_name in('page_view', 'free_trial', 'subscription_start')
),

free_trials as (
  select 
    user_id,
    previous_gclid as gclid
  from
    events
  where
    event_name = 'free_trial' and 
    user_id is not null
  qualify row_number() over(partition by user_id, event_name order by event_timestamp) = 1
), 

subscription_starts as (
  select
    format_timestamp('%Y-%m-%d %H:%M:%S', timestamp_micros(event_timestamp)) as subscription_start_timestamp,
    user_id
  from
    events
  where
    event_name = 'subscription_start' and
    user_id is not null
  qualify row_number() over(partition by user_id, event_name order by event_timestamp) = 1
)

select 
  * except(user_id)
from
  subscription_starts
inner join
  free_trials
using(user_id)
where 
  gclid is not null

The Result

Ensure the query returns the expected results by validating the output. If you successfully link a gclid to an offline conversion, you should see two columns: google_click_id and subscription_start_timestamp.

Automate Data Feeding into Google Ads

Create a Scheduled Query

Before uploading data to Google Ads, set up a query to run on a daily schedule to keep data current.

1. Create the Table: Run your query, click Save results and save the results to a BigQuery table. (e.g., google_ads_offline_conversions), and store it in an appropriate dataset (e.g., google_ads).
   
   
   
   
2. Schedule the Query: In the editor, click “Schedule,” set it to run daily (e.g., 4:00 UTC), and configure it to overwrite the existing table.
   
   
   
   
3. Save your settings.

Create a Conversion Action in Google Ads

1. In Google Ads, navigate to Goals > Create Conversion Action.

2. Select Import, choosing CRMs, files, or other data sources.

3. Connect a new data source by selecting BigQuery > Direct connection.
   
   
   
   

4. Choose a conversion type (e.g., “Subscribe”) that suits your business needs.

5. Link to your BigQuery project, dataset, and table.
   
   
   
   

6. Map required fields like conversion_event_time and gclid. For conversion_event_time, use Transform > Parse timestamp, and specify your time zone.
   
   
   
   

   Note: Although Google Ads requires conversion_event_time as an integer, we discovered—after discussions with Google Support—that it must instead be uploaded as a timestamp string (e.g., “2024-10-10 04:37:18”) in the format %Y-%m-%d %H:%M:%S, with a transformation applied. This requirement is far from intuitive, and we hope Google will clarify this step in the future.

Review and Finalize

1. Verify your setup and ensure the Google Ads schedule runs after your BigQuery query runs.

2. Set the conversion action as a Secondary action initially to validate accuracy. Run it for 1–2 days, and once confirmed, switch it to Primary action for campaign optimization. !
   
   
   
   

3. Go to to the Goals summary page, and validate that the conversion receives conversions as expected. Note: It can take up to 24 hours before the conversions appear in the interface. If you run into issues, visit the diagnostics section for more information on the errors.

Final Notes

In this guide, we’ve built a solution to track offline conversions by integrating GA4, BigQuery, and Google Ads. Starting from capturing initial user interactions in GA4, we used BigQuery to connect micro- and macro-conversions, linking data through gclid. By creating a scheduled query and setting up Google Ads to import these conversions, we’ve enabled accurate attribution from ad click to offline conversion.

We hope you found this guide useful. In our next post, we’ll dive into how to set up a similar solution for Meta Ads, making it easy to capture offline conversions there as well. Stay tuned!