Automating Offline Conversion Tracking in Google Ads

Ryan MootHart, PPC Architect

In the world of lead-based paid search advertising, tracking raw leads as conversions in Google Ads is the norm. The concept is simple and straightforward: when a user clicks on one of your ads and proceeds to contact you via a form submission, an email click, or a phone call, they’re indicating interest in becoming a customer. Therefore, these actions should be tracked as conversions which can then be optimized in Google Ads.

Easy enough and quick to implement, right? You’re all set – go get those conversions!

Except, wait – I lied. You’re actually not all set. Even if you’ve taken the extra steps of inputting an estimated value per conversion for these raw lead actions and choose data-driven attribution in your Google Ads account, you still have more work to do.

Yeah, I know. I’m about to give you more work. Sorry about that.

With the advancement Google has made with automated bidding, user targeting, similar audience creation, and so forth, using raw leads as your sole conversion points just aren’t cutting it anymore. Competition in paid search is as fierce as ever in pretty much any vertical imaginable. You need to make sure the investment and optimizations you’re making in your Google Ads account are going toward not just raw leads but raw leads that turn into qualified leads and booked sales.

To do this, you need to translate those advanced lead stages and actual sales (including, ideally, hard revenue numbers) in your CRM and import those conversions directly into your Google Ads account.

This process is called offline conversion tracking, and it is going to be vital for any lead-based advertiser on Google Ads to begin executing correctly within the next year or two. Of course, the sooner you can get offline conversion tracking working, the better. But, how exactly do you do this, and, more importantly, how do you automate conversion tracking so this process doesn’t become a long-term drain on time and resources?

In this post, we’ll walk you through how to set up offline conversion tracking in Google Ads and link you to the pertinent support documentation for more granular technical details when you need them.

Getting the Right Data Into and From Your CRM

The first thing you need to do is look at the different lead stages in your sales funnel and make sure they’re reflected in your CRM. Depending on your business, you could have a simple conversion funnel with just two or three stages:

Raw Lead → Sales Qualified Lead → Booked Sale

Or a more complex conversion funnel with multiple steps your sales team has built out:

Raw Lead → Assigned Lead → Sales Qualified Lead → Active Consideration → Final Consideration → Booked Sale

Whatever your conversion funnel looks like, identify those key lead cycle stages beyond the initial raw lead to determine which ones you want to import and track within Google Ads. For the sake of simplicity, let’s say you have a simplified conversion funnel, and there are just two advanced lead stages you want to import and track:

  • Sales Qualified Lead
  • Booked Sale

You’ll need to create a new conversion within Google Ads directly for each of these. To do this, go to the Tools & Settings icon at the top and select Conversions in the Measurement column.


Next, you’ll hit the plus sign (+) button at the top left on the (Conversion Actions screen and proceed to select Import as your conversion type. You’ll then select either Other data sources or CRMs or Salesforce (if you use Salesforce for your CRM). Then, select Track conversions from clicks.


For this walkthrough, we’re going to proceed with a generic CRM (not specific to Salesforce). We will focus on importing leads from initial form fills on one’s site (instead of inbound calls unassociated with a Google Ads click).

On the next screen, you’ll complete the following steps: Name your conversion (to match the lead stage)

  1. Determine the value of your conversion (static value input here in the conversion setting or a dynamic value imported from the CRM)
  2. Determine the number of conversions per click (one for unique users, every for unique transactions including multiple per user)
  3. Click-through conversion lookback window (default is 90 days)
  4. Determine if you want to include this conversion data in your Conversions column
  5. Pick your attribution model (match it with the rest of your conversions for consistency)

Regarding the value determination (#2 above), you’ll either choose to import a dollar amount directly from your CRM or calculate the average value per lead and input a static number right there in the conversion settings. You are also allowed to designate no value, but doing so will eliminate your ability to use certain automated bidding strategies (e.g., Target ROAS) if you choose to include these conversions in your Conversions column.

To calculate a static value for a particular lead stage that’s not a final sale, take the total revenue generated from your final sales and divide it by the number of leads you typically acquire in that stage according to your funnel conversion rates.

Here is an example:
150 Sales worth $1,500,000 total at a booking rate of 15%
A booking rate of 15% = 1,000 sales qualified leads (SQLs)
$1,500,000 ÷ 1,000 SQLs = $1,500 average value per SQL

After you’ve created both of your new conversions in Google Ads, you’ll need to make sure Google Click ID (GCLID) values are correctly passing through your online form submissions and into your CRM.

Capturing & Passing GCLID Values

The GCLID value is created automatically by Google upon each ad click a user makes. It is the key to linking offline conversions to their corresponding campaigns within a Google Ads account. Without the GCLID, you’re left to make educated guesses with no real data to tie back to the dollars you’ve spent. This means you need to capture the GCLID value generated by Google when a user clicks through your ad to your site, pass it through the form submission protocols as a user submits a lead, and store it as part of that user’s information in your CRM.

Here are the steps described by Google verbatim:

  1. Update every lead submission form page so the GCLID will be passed to your lead management system.
  2. Add a script to all of your web pages to capture and store the GCLID.
  3. Store the GCLID, along with the prospect’s information, in your lead management system.

Fair warning: I am not a coder, and the specifics of what and how to implement the necessary changes on your site go above my head. Please refer to Part 2 of Google’s support documentation to see the technical details of what changes you’ll need to make. The result of this process should allow you to see, for each applicable user, the GCLID value in the field you have designated for it within your CRM.

Formatting Your Offline Conversion Data

You’ve created your offline conversion actions in Google Ads, and you’ve successfully passed the GCLID value through your form fills into your CRM. Now it’s time to format your offline conversion data so Google Ads can read it correctly. This data will live in an excel sheet, CSV file, or Google Sheet and contain the following attributes:

  • A mandatory parameter for time zone ID or a GMT offset (e.g., “TimeZone=-0500” for GMT -0500/Eastern Standard Time).
  • A mandatory column “Google Click ID” contains the GCLID value exactly as it is.
  • A mandatory column “Conversion Name” matches the corresponding conversion name you created in Google Ads exactly.
  • A mandatory column “Conversion Time” contains the timestamp of the original raw lead in one of Google’s specified timestamp formats.
  • An optional column “Conversion Value” contains any actual revenue attributed to this lead in your CRM.
  • An optional column “Conversion Currency” specifies the currency of such value (e.g., “USD” for US Dollars).

In the end, the data from your CRM export should look something like this:


To view more specifics on any of these fields and find the template you can base your file off; please refer to Part 1 of Google’s support documentation here.

Your final file should have lead data that goes as far back as needed according to your lead maturation timelines. If your leads typically go from raw leads to booked sales in 3 or 4 weeks, then 30 days’ worth of lead data might be sufficient. If your raw leads typically take 90 days or more to close, then you’ll need to ensure you include data over a longer period of time. Whatever the case, make sure your conversion action lookback window is set appropriately in its settings within Google Ads.

Schedule Automatic Uploads

Now that you have your offline conversion data formatted to Google’s specifications, it’s time to create an automated upload schedule, so you don’t have to pull this data manually every day. First, you’ll need to decide what file type you want to use:

  • Google Sheets: your offline conversion data is uploaded to a static Google Sheets file and overwrites on the cadence of your choosing (e.g., every 24 hours).
  • HTTPS or SFTP: your offline conversion data is uploaded in CSV format, which keeps the same file name every time your data is exported from your CRM and housed in an HTTPS or SFTP location.

Once you’ve chosen your method, your update cadence, and have ensured your process is working properly, you’ll create a scheduled upload in Google Ads. Once again, go to the conversions section in your Google Ads account and select Uploads on the left-hand navigation menu. Then, hit the Plus Sign button, navigate to Schedules at the top, select your source, and set your import frequency/time of day.


Click Save & Preview to test your import, and you’re all set!

Be sure to give your offline conversion data upload from your CRM some time, so your scheduled import always grabs the most up-to-date file. For example, if your offline conversion data is scheduled to refresh every 24 hours at 3 am, don’t schedule the import in Google Ads for the very next hour at 4 am. Unexpected delays can happen.

Do not worry if the same conversion appears in your data over and over again every time your data refreshes for the range of time you set your conversion window. Google will recognize the timestamp and GCLID of a unique conversion and won’t duplicate conversions from one day to the next.

Use Offline Conversion Data to Optimize Your Campaigns

Alright, you’ve got offline conversion data for your different lead stage importing into your Google Ads account automatically. Now comes the important part – using this data to your advantage and optimizing off of it.

Suppose you decided to opt these conversion actions into your Conversions column. In that case, you can use the segment tool at the top of your data table in Google Ads to split up your conversion column data by conversion action. Splitting this data will give you a quick view of your offline conversions by the campaign, ad group, and more in one view. However, calculated metrics off of those segments are not provided in this same view, and it may be challenging to make optimization decisions just from this.

What you can do in addition to this is download your Google Ads data by campaign, ad group, etc. (in this example, I’m going to stick with campaigns) while limiting this download to click-based metrics (e.g., cost, clicks, impressions). Then, in the reports section of Google Ads, you can create a custom report which includes your campaigns and conversion actions only.


Make sure the date range matches what you have already, and download that report into a 2nd spreadsheet. Then, filter by conversion action, copy the campaign sets and lead volume, which correspond to each conversion action (one action at a time) and paste them into your original campaign download. As you paste this data over in your original sheet, be sure to create a new column at the top for each conversion action in which the corresponding volume numbers will go in.


Once you successfully blend your campaign and conversion data into one sheet, create a pivot table to see all data by campaign in one table. You’ll have to create calculated field sets for any calculated metric (e.g., avg. CPC, avg. CPA) to see accurate metrics in the pivot table. You leave such metrics out of a raw data table used to create a pivot table because Excel will try to assert a function (e.g., sum, count, average) on these calculations and reflect inaccurate results. It’s a tad time consuming but, once it’s done, you’ll be able to see how crucial conversion metrics by lead stage and by campaign are in one view.


That’s a Wrap

Now you’ve got the right data to analyze and help you make optimization decisions. See how focusing only on raw lead data can be misleading? Viewing raw leads, sales qualified leads, and booked sales all in one view by campaign can help you see things you may have missed otherwise. Not to mention you would have missed seeing them without any offline conversion tracking at all!

Setting up offline conversion tracking and automating the import process to Google Ads is a time-consuming endeavor. You’ll run into a few obstacles on the way to succeeding. But, once it’s done, you’ll be equipped with the data you need to make the wisest investment and optimization decisions in your Google Ads account.

Start call to action

See how Portent can help you own your piece of the web.

End call to action

Leave a Reply

Your email address will not be published. Required fields are marked *

Close search overlay