facebookIcon tracking


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:
The result:
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.

1 S Wacker Drive
Suite 2250

Chicago, IL 60606(650) 539-4124


Want to become a client?

Contact Us decorative arrow

Want to join the team?

View Our Openings decorative arrow

Find us on social media.

Press inquiries.

Email Us decorative arrow

Expert insights for your inbox. Subscribe to our content.

Accept No Limits.

Learn more about 3Q/DEPT READ MORE