Qualify and Report on Call Leads (without listening to each call)
Published: May 28, 2014
Author: Heather Scott
Ah, call lead qualification…that’s a digital marketer’s quandary. Call-tracking software solutions –even those that integrate with AdWords – may not be granular enough to qualify calls. And once your campaigns get significant call volume, you may not have enough industrious interns kicking around to actually listen to call recordings.
So what to do? Well, we’ve come up with a workaround. We’ll use one fast-growing account (for which we used to listen to the calls) as an example and walk you through how it works.
Based on our initial documentation of calls, we determined points in call time with a lift in call lead CVR. We then determined how many calls within each time frame resulted in call leads. For example, suppose we had 40 calls between one and three minutes, 18 of which were leads; this would mean that 45% of calls within this time frame could be considered leads moving forward.
We then added a formula to our call reporting to automatically determine our call lead percentages per campaign. (I’d like to give a call out to Katie Eschenburg for lending her Excel expertise in developing this process!) In this example, 10% of calls between 50-59 seconds=lead, 45% of calls between 1-3 minutes=lead, 75% of calls between 3-5 minutes=lead, and 90% of calls 5+ minutes=lead. The formula is =IF(AND(D2>=TIME(0,0,50),D2<=TIME(0,0,59)),0.1,IF(AND(D2>=TIME(0,1,0),D2<TIME(0,3,0)),0.45,IF(AND(D2>=TIME(0,3,0),D2<TIME(0,5,0)),0.75,IF(D2>=TIME(0,5,0),0.9,0))))
In order to combine DoD call data with Google AdWords reporting, the date needs to be isolated. To do this, copy and paste the date/time column into a new column, and separate the date from time with find/replace space* with nothing:
Next, concatenate the campaign with the date:
And pivot the data to get the sum of calls per campaign per date:
In Google’s campaign DoD report, concatenate the date and campaign as well in order to vlookup to the pivoted call data for results:
Naturally, it is then necessary to recalculate CPA and CVR to include call leads (in above report, lead form conversions are being tracked in AdWords).
Following this process, we are also able to roll up calls to review geo or day parting as well as keyword, ad testing, and desktop/tablet vs. mobile performance by using utm parameters to pull data into call reporting.
Once formulas are in place and the process established, we can easily review overall performance as well as performance per individual campaign or even keyword. I’m sure someday we’ll be able to upload leads from call tracking systems based on formulas that help to determine lead quality, but in the meantime, this workaround does the trick.