You think your PPC campaigns are super successful. You’re generating strong lead volume at a cost per acquisition below target. Management is happy but your sales team is not convinced.
What is going wrong?
You’re not sure which moving parts of your campaign are generating quality leads. You doubt whether your campaign is actually profitable.
What if the 70% of your spend that is driving the most leads – at a low cost per acquisition – is actually generating very little sales? Let’s go beyond lead volume and find out where your advertising $$$ are going.
Tip #1: Pass Custom UTM Parameters From Campaigns to Your CRM
If you’re not using custom UTM parameters, this is your Step #1.
UTMs? Stands for “Urching Tracking Modules”. They are what Google uses to track unique URLs.
By adding manual custom UTM parameters to all your destination URLs, you can know the source, campaign and keyword for each lead that your campaign drove to your website or landing page.
Have a look at Konzai’s post for more detail on how to do this the right way. And… Google’s URL builder can help you generate the actual UTM variables for each campaign.
All you need to do is fill in the blanks, and the correct UTM tracking URL will be created for you.
Tip #2: Calculate Your Tracked ROAS with Excel
ROAS should be one of your key metrics to calculate the effectiveness of your PPC ad spend.
Want to know your ROAS for the month of April? To calculate it, you need to divide the revenue generated by those Adwords leads that converted in April, by your advertising spend for that month.
If you can’t do that in your CRM, use Excel. Here’s how:
- Extract all your PPC leads to date in an Excel CSV file. Not just April’s leads but all-time until the end of April. Why all of them? Depending on your average sales cycle you might have customers who researched your product last fall and only decided to buy in April.
- Remove all columns except the leads column (email addresses). Create two blank columns to the left, so that our leads columns becomes column C.
- Paste your list of April customers in column B (email addresses)
- Paste the purchase dates in column A corresponding to the April customers in column B.
- Add a column D which you can call: Bought? This is where you want to enter the VLOOKUP formula, to see which users shopped. (The idea here is to compare the two columns of email addresses and find out which email addresses appear in both columns)
- Run the following formula in D3: =VLOOKUP(C3,B:B,1,0), and drag it down the list.
- Now filter out your column D results leaving out #N/A entries. You should see a list of email addresses.
Here’s your list of April customers that came from your PPC campaign!
Now, let’s tie your customers to the specific Adwords campaigns and keywords they came from:
- Paste this list of emails (column D) and the purchase dates (column A) into a new Excel spreadsheet. Column A becomes your email addresses and column B the corresponding dates.
- Add in the State and Service Value (columns C & D).
- And the UTM parameters matching those emails (columns E, F & G).
Your spreadsheet might look like something like this once you’ve added some extra columns:
All set! You can now filter out the state, keyword, source and campaign columns to start seeing where your sales came from.
Want to isolate areas that did not produce sales? Check out tip #5!
Tip #3: Match Call Tracking Data to Your List of Customers.
If you already use a call tracking solution, this is must. Phone calls to your business can have just as much, if not more, value than website conversions (such as a contact form submission).
You can tie all of this data together, so that you track unique phone calls leads and website leads from your PPC campaigns.
This enables you to:
- Quantify leads: calculate a “blended” cost per acquisition when it comes to lead volume (unique forms + unique calls – make sure you remove duplicates!) and make more informed decisions based on this additional data.
- Qualify leads: as part of your key performance metrics and in order to tie on your campaign to lead quality, you want to first look at source type, medium, city, call length and listen to recorded calls. You can even track keyword-level data with some more advanced packages.
- Tying calls to your ROAS: in some cases your call tracking solution tracks the name of the caller. Try to match this data to your customers list and you’ll get a better sense of your ROAS.
If you don’t use a call tracking solution yet, here is a great article that explains what they do.
Tip #4: Keep in mind some of the tracking holes
If you calculated your ROAS in a way similar to Tip#2, then this is not your actual ROAS! Why? Because there are a great deal of tracking holes in this exercise.
Here are some of them:
- Users who deleted their cookies and you couldn’t track.
- Users who visited your site or landing pages at work, and then converted at home on a different device.
- Users who used a different email address when they signed up and converted (work address and personal).
- Latent conversions: Adwords tracks leads within 90 days following the original click. If a prospect clicks on an text ad or banner ad, comes back on the site and fills out a form after 90 days from the original click the lead source will not be attributed to Adwords but to the last touch point (direct, organic,…).
It’s difficult to estimate what this figure these tracking holes represents. But it is important to be aware of areas where your data might be slightly skewed.
Tip #5: Isolate Areas That Don’t Produce Sales
Identify campaigns and regions producing leads that are not converting into sales, so that you can cut them if they are performing below your target thresholds. There might be some obvious campaign-level budget shifts you could take care of fairly quickly depending on the volume.
If you have a spreadsheet similar to what we built in Tip #2, we can do some Excel magic to get a bird’s-eye-view over what works, and what can be improved. All we need to do is add some columns and formulas, like this:
- Column H – Campaigns: enter the names of all the campaigns as labeled in Adwords.
- Calculate I – Lead Count: calculate the number of sales per campaign. Enter the following formula: =countif(G:G,”campaign name”)
- Column K – Cost: Fill out this column with Adwords data corresponding to each campaign.
- Column J – Revenue: populate this section by entering the following formula: =sumif(G:G,“DUI-USA-D-SCM”,D:D)
- Column L – Ratio: divide revenue (column J) by cost (column K): = J3/K3
- Repeat for rest of the spreadsheet.
Your spreadsheet (from column H onwards) might look this something like this:
Got all the data in place? Good! You can now start gathering some insights by looking at the all time and last month tabs.
You might want to look at the following sections outlined from top level to more detailed optimizations. You especially want to pay attention to the ratio columns and benchmark those with your specific ROAS target.
- Source: Google or/and Bing (column N)
- Campaign types (column N): RLSA, Search, Display,…
- Campaigns (column H)
- State (column N)
The higher up you end up redistributing budget the more impact you will end up having on your campaign performance. Take a crack at it and let me know how it goes!
Tip #6: Label Converting Keywords
Get familiar with which keywords drive sales. Perhaps you’re already making keyword-level decisions based on lead volume and cost per lead, but you can go a step further.
By using Google Adwords’ custom labels, you can label keywords that have generated sales, based on those you have identified via the UTM parameters you’ve set up.
So, your keyword tab might look like this:
Having those labels in place means you can take smarter real-time decisions on the keywords.
Tip #7: Be Patient
You need a lot of data to make smart sales-driven decisions. You don’t want to have too narrow of a view on your campaign progress.
So, don’t make too many campaign changes too often. Make a change, and then let enough data accumulate to be able to take statistically meaningful insight from it.
Calculate your tracked ROAS on a monthly basis but never lose sight of the big picture: your all-time ROAS!
Got enough data now? Good. Now it’s time to optimize!