Solve Adwords Attribution Without Using Adwords API

Measuring the effectiveness of marketing is a necessary evil and with the rise in number of channels marketers can leverage today, it is becoming even harder to solve this problem. Marketers are essentially marketing blindly, without answering the most basic question: Which channel produces the highest ROI?

Current Attribution Challenge in Marketing

A consumer’s purchase journey is complicated in the digital era. Today the end user can have multi touch interaction with a product on different channels [social, ads, email, website] and different devices [mobile, desktop] before making a decision to purchase the product. This is a growing trend and is not going to get any easier. In short, a consumer’s journey is a never ending maze with multiple exits. While there are multiple attribution models that exist: first-click, last-click, cross channel attribution, this post addresses how a marketing team can solve Adwords attribution without using the Adwords API. Most marketing teams spend the highest amount of variable budget on search Ads, hence it is extremely important to know which ad campaign, keywords, ad group, ad copy worked the best. Since I use Hubspot for marketing automation and Salesforce as the CRM, I will address how attribution can be solved using these two. This is an easily replicable workflow in any marketing automation and CRM software using Zapier. For the sake of simplicity I will use Hubspot in place of marketing automation and Salesforce in place of CRM. This by no way means you cannot use any other marketing and CRM software where your leads sit.

Step 1: Send URL and GCLID to Hubspot:

If you are capturing leads using a form on your Ad’s landing page and passing the form fields [Name, Email, Number etc] to Hubspot. Pass the URL and GCLID as well [as hidden fields]. Pre sync the GCLID and URL with Salesforce using the Hubspot and Salesforcenative integration.

Step 2: Fetch Raw URL to a Google Spreadsheet:

Create a dynamic list in Hubspot with all contacts with GCLID as a know variable and setup a Zap to copy the Adwords URL of these contacts into a spreadsheet. Trigger the Zap for every new contact that is added to this list [Refer screenshot below]

Step 3: Clean the Raw URL to obtain Ad ID, Campaign ID, GCLID, Keyword ID, AdGroup ID:

Once the URL’s start to flow into a spreadsheet, using simple excel formulas [a combination of =split, =concatenate, =vlookup], split the URL to fetch the Ad ID, Campaign ID, GCLID from the url [Refer example url below: the parts in bold represent ad id, campaign id, gclid etc] Fetch the actual text value for Campaign Name, Keywords, Adgroup from pre-filled tabs that contain this information against the id's, using vlookup. e.g. if your Campaign name for the campaign ID 56723489 is ‘Brand’, fetch the text using a simple VLookup from the tab that contains the master list of all the campaigns.

https://abc.com/campaign/testing-adwords/?ads_cmpid=968903002&ads_adid=54992465184&ads_matchtype=b&ads_network=g&ads_creative=236619305404...

Step 4: Send Campaign Name, Keyword, Adgroup, Cleaned URL to Salesforce:

Once all the attributes are available in a clean format, setup another Zap to trigger each time a new row is added to the spreadsheet and map particular attributes in Salesforce, with the trigger column being GCLID, since the GCLID is unique and Zapier works only with unique triggers. You will need to setup the trigger as a new row and two consecutive actions in Zapier [read what is a trigger and action in Zapier]: One to find the lead which matches this particular GCLID and the second to map attributes [campaign id, ad id] with that lead based on the GCLID. Note, you will need to have these fields created in Salesforce before sending values from a spreadsheet to Salesforce. [Refer screenshot below]

For those who don’t know Zapier, read more about it here

Four simple steps to save engineering bandwidth and not having to figure out data structures for Adwords and Salesforce.

I personally love Zapier, it has helped me solve many automation problems. I’d recommend every marketer to check out Zapier and use it to automate your marketing operations without the help of your engineering team. It definitely is the easiest way to automate simple tasks

Write to me on [email protected] if you need help setting up Adwords Attribution.  Setup time with me here for a quick 30 minutes chat.