214-310-1356
Tracking phone numbers has always been a fairly reasonable tactic for tracking where traffic is coming from, reason for the call, duration, and conversion rate. Now, with the continual growth of Google Ad Campaigns, marketers are teaming up both Google Analytics and call traffic to zero in their results even more.
We were recently introduced to a PPC campaign that did exactly that. This client’s campaign came with a month’s worth of spreadsheet documentation. The sheets were segmented and very well documented, as far as ad content, avg. CPC, and cost per geo target; but what struck our fancy was the phone log. Thousands of calls were made into the numbers associated with specific geos and keywords.
If only there were a way to determine the radius of the calls into one specific phone number we could narrow down our geo reach and possibly manage some more conversions.
First I started with the research, how could this be done the easiest way so we could utilize this same tactic for other clients. That’s when I ran across this little tool.
The CDXZipStream! (cue the oo’s and ahh’s) Created by the CDX company, this Excel add on is used to help, primarily, companies with long lists of demographics, addresses, and other data, to make long calculations easier and built into the spreadsheet they use daily.
Accompanied by step by step instructions from their YouTube tutorials, I was able to get a great look at the spreadsheet data.
I first narrowed down the data to the call log only, and specifically, the phone number called, zip code of the caller, and duration of the call. The following numbers corresponded with campaigns that were specific to the area code used in the phone number (which aren’t fully displayed, for privacy purposes).
This chart shows the number called on the left, zip code of caller on top, and duration of call in the table.
While sorting through the data, I was able to determine which phone numbers were used as test numbers due to the call volume and a little deeper investigation, and separated them from pertinent data.
Next, I found my target zip code, which I determined from the actual storefront of each of the 4 locations. I created a separate page and plugged that target zip into the CDXRadius tool along with the mile radius I wanted to track, which was 10 miles. The results returned with all zip codes within that 10 miles.
From here I was able to use the =VLOOKUP function in Excel to find the corresponding zip code from sheet 2 back to sheet one, and check every zip code’s distance from each of the four target zip codes.
After transferring my data from sheet 2 to back to sheet 1, I was able to determine where the majority of my traffic was coming from for each test number that was optimized. I found that the majority of calls for 2 of the 4 locations were on the outskirts of the 10 mile radius, and possibly even 15 miles, which is interesting due to the size of the metros that were targeted.
Unfortunately for the spreadsheet I had, the duration of the ads weren’t listed. That would have been a great factor to consider for exactly how well the campaign performed and what led up to the actual conversion rate.
Next time you’re dissecting some data, whether it’s for campaigns or demographic research, make sure that you check out the CDX add on. It’s very easy to use and has a free 30-day trial offer on their site.