Importing AdWords conversions from Salesforce.com

If you’d like to import offline sales and conversion data that’s stored in Salesforce.com’s Sales Cloud, we’ll show you how.

Before you start, you’ll need to know the basics of the AdWords conversion import feature, it’s requirements, and how the Google click ID (“GCLID”) is passed to the advertiser and then imported back into AdWords. So, be sure to read our Tracking Offline Conversions FAQ before following the steps below.

How it works

No two businesses are alike, and there are endless ways you can define your lead and opportunity stages. But you’ll only want to import some of those as AdWords conversions.

We’ll use one sample scenario below to show you how the integration works, and then give you some ways to customize that to your situation later in this FAQ.

Here’s the sample sales process:

  1. A prospective customer clicks on your AdWords ad, which leads to your website.
  2. The prospective customer explores your web pages — she reads descriptions, watches videos, and downloads whitepapers. During that session, or a later one, she enters her information into your lead submission form that uses Salesforce’s Web-to-Lead solution.
  3. A lead is created in Salesforce.
  4. When the lead becomes "qualified," it’s converted to an opportunity. Then, an opportunity record is created with the initial stage "prospecting."
  5. When the opportunity is won and closed, the opportunity stage is set to "Closed Won.”
  6. Finally, you’d want to measure when a click leads to both a qualified lead and a final sale.
 

SalesForce Conversion Tracking

See the technical details for this process
Channel Key event Technical details
Online A prospective customer clicks on your AdWords ad, which leads to your website. Google passes the GCLID to the website.
Online The prospective customer explores your web pages — she reads descriptions, watches videos, and downloads whitepapers. During that session, or a later one, she enters her information into your lead submission form that uses Salesforce’s Web-to-Lead solution. Using the JavaScript code samples below, the website stores the GCLID in a cookie and then passes the GCLID value (along with the lead information entered on the form) to Salesforce.com. Note: If you’d like to track this lead submission as a conversion, you could use AdWords Conversion Tracking to track conversions on a website.
Offline A lead is created in Salesforce. Salesforce.com creates a new lead that stores the lead’s information + GCLID
Offline When the lead becomes "qualified," it’s converted to an opportunity. Then, an opportunity record is created with the initial stage "prospecting." Salesforce stores this conversion event in the opportunity history so AdWords can later retrieve exactly when the click resulted in a new opportunity (aka qualified lead)
Offline When the opportunity is won and closed, the opportunity stage is set to "Closed Won.” Salesforce stores this conversion event in the opportunity history so AdWords can later retrieve exactly when the click resulted in a final sale, or “win.”

Set up

Below, we’ll show you how a Sales Cloud administrator can do the following:

  • Set up custom fields to store the GCLID and related information.
  • Capture the AdWords click ID (“GCLID”).
  • Modify your web-to-lead HTML form to pass the GCLID to each new lead record.
  • Download the relevant data so you can upload your conversions into AdWords.
Step 1: Set up Salesforce
  1. Create a custom field called “GCLID” (API name is GCLID__c) on the lead object as well as on the opportunity object. The field length is 255 characters. Make this field read-only, as this will be automatically populated.
  2. Create a custom text formula field called "AdWords Action" on both the lead and opportunity object (the API name is AdWords_Action__c). Enter the simple formula 'add'. This field will always contain the static value "add" and is used later to simplify the creation of the data file the admin feeds back into AdWords.
  3. In the Lead Custom Field Mapping screen, map the lead field GCLID to the opportunity field GCLID. That way, the GCLID is transferred from the lead to the opportunity automatically when a lead is converted into an opportunity.
Step 2: Save the Click ID in a cookie

If a visitor clicks on your AdWords ad, AdWords will append the URL parameter "gclid" to the URL that leads to your landing page.

You’ll need to store this parameter's value in a cookie to retrieve it later when a prospective customer enters her data into the Web-to-Lead form.

If you embed the following JavaScript code immediately before the closing </body> tag of your landing page(s), the GCLID value will be saved in a cookie.

    <script type="text/javascript">
function setCookie(a,d,b){var c=new Date;c.setTime(c.getTime()+864E5*b);b="; expires="+c.toGMTString();document.cookie=a+"="+d+b}function getParam(a){return(a=RegExp("[?&]"+a+"=([^&]*)").exec(window.location.search))&&decodeURIComponent(a[1].replace(/\+/g," "))}var gclid=getParam("gclid");if(gclid){var gclsrc=getParam("gclsrc");(!gclsrc||-1!==gclsrc.indexOf("aw"))&&setCookie("gclid",gclid,90)};
</script>

It’s a good idea to insert this code on every web page. So, consider adding this code to your web page template — or a shared page element like the footer — so that all your new pages will automatically include the code.

This way, you won’t need to add it every time you create new ads with new landing pages. You also won’t have to worry about losing GCLIDs if you change the landing pages of existing ads.

Step 3: Adjust your Web-to-Lead form

To associate and store the GCLID with the lead information, you will need to modify your web-to-lead submission forms.

This means that after the GCLID has been saved in a cookie, you’ll add a hidden field to your lead forms, and write the value of the cookie into that field so it can be transmitted to Salesforce.

Keep in mind: Your Web-to-Lead form needs to be located in the same domain as the rest of your web pages to make the cookie available.

  1. Generate a standard web-to-lead form and include all fields you want your prospects to submit. Also include the lead field "GCLID". The HTML generated by Salesforce will include this code (where xxxx stands for the ID of the field GCLID you’ve created):

    GCLID:<input id="xxxx" maxlength="255" name="xxxx" size="20" type="text" />

  2. Change this code to: <input id="xxxx" name="xxxx" type="hidden" />

    These changes mean that you’re removing the attributes “maxlength” and “size,” and changing the value of the type attribute to "hidden." Leave the attributes “id” and “name” as is (they’ll be different for every Salesforce organization).

  3. Once you have created this new hidden input field, add it to your HTML form in between the <form> tags. On the same HTML lead submission page, add the following code between the head tags:
            <script> 
            document.onload = function getGclid() {        
                 document.getElementById("xxxx").value = (name = new    
            RegExp('(?:^|;\\s*)gclid=([^;]*)').exec(document.cookie)) ? 
            name.split(",")[1] : ""; } 
            </script>
         

    In this code replace "xxxx" with the ID of your new field.

Salesforce and your web pages are ready to gather all the data. This script will populate the hidden field with the GCLID value, and populate the field Lead.GCLID__c when the web-to-lead form is submitted. Again, keep in mind that the fields “id” and “name” will be different in your organization.

Step 4: Export the key event data

This next step assume that you’re exporting and uploading your key event data every three days.

  1. Use Apex Data Loader or Lexi Loader to export OpportunityHistory records using this query:
            SELECT Opportunity.AdWords_Action__c, Opportunity.GCLID__c, 
            StageName, ExpectedRevenue, CreatedDate FROM OpportunityHistory 
            WHERE Opportunity.GCLID__c != null AND (StageName = 
            'Prospecting' OR StageName = 'Closed Won') AND CreatedDate = 
            LAST_N_DAYS:3
         

    This will output a row for every GCLID associated with an opportunity that has been set to the “Prospecting” or “Closed Won” stages. And it will contain all the fields necessary (and in the right order) to quickly create an AdWords conversion import CSV.Alternatively, if you wish to automate the conversion import process by using the AdWords API, then you can skip the next two steps and instead refer to our conversion import API guide.

  2. Once you have the export file, replace the first row of the file with these two rows:
           Parameters:EntityType=OFFLINECONVERSION;TimeZone=HHMM; 
           Action,Google Click Id,Conversion Name,Conversion 
           Value,Conversion Time
         
  3. Finally, update the “TimeZone” value so it reflects your AdWords account’s time zone (e.g -0800 for US Pacific Time). You can see what time zone your account is set to by signing into your AdWords account and checking the footer.

This file is now ready to be uploaded into AdWords. If you need guidance on uploading your files, please read our Tracking Offline Conversions FAQ.

Tip

Because the conversion names we export are opportunity stage values, you might want to set up your AdWords conversion types to have the same name. For example, if “IS Lead Qualified” is used in Salesforce to show that the lead has become qualified, then you’d want to create a conversion type in AdWords with the name “IS Lead Qualified.”

If you don’t do this, you’ll need to open your Salesforce CSV export file and change all the Conversion Name Values to whatever conversion type name you defined in AdWords.

Customize to your business

Your business model and sales process might differ from the example we’ve outlined here. If that’s the case, consider these modifications:

  • If you want to consider only a subset of you opportunities (ex. just a specific record type), add additional clauses to the WHERE part of the SOQL query (ex. ...AND RecordType.Name = 'Electronics' )
  • If you want to update the conversion tracking data more or less frequently than every three days, modify the SOQL query respectively (ex. CreatedDate = LAST_N_DAYS:7) and generate and upload the CSV file within your preferred time interval.
  • If you find other events in your sales process (ex. opportunity stages) useful for optimizing your campaigns (ex. "Paperwork Out"), you’d want to modify these points:
    • Add conversion types in AdWords that have exactly the same name as the additional opportunity stages you find relevant.
    • Modify the SOQL query and add additional clauses within the parenthesis (ex. …OR StageName = 'Paperwork Out’)
  • If the relevant events are not only on the opportunity but also on the lead object, you’ll want to modify these points:
    • Add conversion types in AdWords that have the same name as the lead statuses you want find relevant.
    • Query the LeadHistory object to get the names and time stamps (CreatedDate) of when the field "Status" changed its value to the value you find relevant. You’ll want to join the results set of both queries into one CSV file. Make sure the columns match the specified format.