Making Ends Meet: Combining Lead Data for Closed Loop PPC Reporting

When working in PPC data, we can feel so superior to other marketing channels. We have instant data. We don’t have to wait to see the impact of our optimizations no matter how big or small. When we focus on generating leads, we often say (hopefully), “Look! I have increased your leads 20% YoY!”. But what is happening to those leads?

Last fall I tested Lead Form Ads for Facebook for a B2B client. In one month alone, we saw a 134% increase in lead volume YoY. My work was done (cue fireworks and confetti). As the client’s sales team started digging into the influx of leads, they were realizing a good portion of the leads coming from Facebook were junk.

Side note: this is not me saying that Lead Form Ads for Facebook is a horrible idea. Check out more PPCHero posts on the subject to determine if it is right for you.

So on the front end, our data was positive. On the backend was a different story. Now, some might say, “I just drive the leads, what the sales team does is out of my hands.” And that is true. We can pour leads into the funnel all day long but without the feedback from the sales team on what is going on after we hand off the leads, we could be wasting our time and our money.

There are a few simple solutions to the problem of tying PPC leads to sales and revenue:

1. Ask the sales team
2. Merge backend data with your PPC reports

Ask Sales

This is a great solution if you do not have a CRM system or a way to track sales back to campaigns. It takes patience, but it ultimately can steer your strategy away from things that don’t work and toward new initiatives.

With my client, it seems their CRM isn’t always accurately pulling in campaign information. It is a toss up if it is going to be recorded or if it is, if it will be accurate. Every month, I report on aggregated lead conversions from all channels. Every month, my client gives me a “clean lead” count. A clean lead means the lead has valid, accurate contact information. After months of looking at these numbers side by side, we found a sweet spot. We said that in any given month, we want to aim for 65% clean leads. Meaning 35% of all conversions will be thrown out as bad leads for various reasons.

How does aiming for 65% help set our growth strategy? When we test new audiences, new channels, or new platforms, we can look at the end of the month to see if the expansion was viable. If the number of conversions increased and we held steady to our 65% goal or increased our clean lead percentage, we can infer the test was a success and scale. If our conversions increased and we dropped to 40% clean leads, this is a good indication the test isn’t driving the right results.

Checking in with the sales team opens up the communication to better understand what makes a good lead and how to ensure we aren’t filling the funnel with garbage.

Merge The Data

If you/your client have a sales tracking system that works, then make it work for you. Here are the things you need:

1. Solid, consistent campaign naming conventions
2. Agreement of date ranges
3. Solid, consistent utm parameters

Campaign Names

What’s in a name? Everything. And while there is no one way to name your campaigns, after all, you have to do what works for you, I do have some suggestions that can make your reporting life easier.

• Start with the what: product, line of service. Identify what you are promoting in the campaign
• Targeting: who and/or where are you targeting? If your campaigns are segmented by region, indicate this in the campaign name. If you are only targeting a segment of the population, it helps to know this at a glance to the campaign name.
• Funnel: is this an awareness campaign? A decision campaign? What stage of the buying process is your audience? This is a big one if you have leads that have to be nurtured in order to produce revenue. Being able to group your campaigns by stage of funnel will help you understand why a campaign is or isn’t leading to revenue.

Date Ranges

Get an agreement of dates. Is pulling the backend data possible on a weekly basis? Monthly? As long as you are pulling your data from the same date range, you should be good to go.

UTM parameters

Here’s where you need to be solid on what you are including. Make sure your parameters include the campaign name exactly as it appears. Make sure you the source/medium are consistent across relevant channels. It will be next to impossible to match up the data if either of those are off. And of course you can go down as many levels as you need to. But if your campaigns are structured tightly, you may only need to go as far as the campaign name.

Fold in the cheese (er, I mean data)

1. Download your PPC data how you normally do. I like using supermetrics and Google sheets to combine my data from across platforms.
2. Download or request the sales report broken out by the UTM parameters.
3. Find the function that’s right for you!
   1. VLOOKUP: works well if you have just 1 platform of PPC data
   2. SUMIFS: great option if you need to aggregate the data or segment out by source/medium/campaign
4. PIVOT: grab what you need to see and run with it

Think On These Things

Remember to think about the length of the sales cycle. How long is the lag time between a conversion and a sales rep’s first point of contact? How long does someone stay in the funnel? How is your back end system attributing leads? Understanding the process of the sales team will help you put context around your data. Not everything will align 1:1 100% of the time but it will allow you to get a more robust picture of how PPC is contributing to and driving revenue.