{"id":2847,"date":"2012-09-07T04:00:40","date_gmt":"2012-09-07T04:00:40","guid":{"rendered":"http:\/\/www.adviceinteractivegroup.com\/?p=2847"},"modified":"2012-09-07T04:00:40","modified_gmt":"2012-09-07T04:00:40","slug":"seo-tracking-tactics-using-excel","status":"publish","type":"post","link":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/","title":{"rendered":"Totally Awesome Tracking Tactics Using Excel"},"content":{"rendered":"

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.\u00a0Now, 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.<\/p>\n

We were recently introduced to a PPC campaign that did exactly that. This client\u2019s 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.<\/p>\n

\"screenshot35\"<\/a>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.<\/p>\n

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\u2019s when I ran across this little tool.<\/p>\n

The CDXZipStream<\/a>! (cue the oo\u2019s and ahh\u2019s) 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.<\/p>\n

Accompanied by step by step instructions from their\u00a0YouTube tutorials<\/a>, I was able to get a great look at the spreadsheet data.<\/p>\n

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\u00a0aren’t fully displayed, for privacy purposes).<\/p>\n

\"screenshot\"<\/a><\/p>\n

This chart shows the number called on the left, zip code of caller on top, and duration of call in the table.<\/strong><\/p>\n

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.<\/p>\n

\"screenshot\"<\/a>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.<\/p>\n

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\u2019s distance from each of the four target zip codes.<\/p>\n

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.<\/p>\n

\"screen<\/a><\/p>\n

Unfortunately for the spreadsheet I had, the duration of the ads weren\u2019t 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.<\/p>\n

Next time you\u2019re dissecting some data, whether it\u2019s for campaigns or demographic research, make sure that you check out the CDX add on. It\u2019s very easy to use and has a free 30-day trial offer on their site.<\/p>\n","protected":false},"excerpt":{"rendered":"

Tracking phone numbers has always been a fairly reasonable tactic for tracking where traffic is coming from, reason for the […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[14],"tags":[],"class_list":["post-2847","post","type-post","status-publish","format-standard","hentry","category-search-marketing"],"yoast_head":"\nTotally Awesome Tracking Tactics for SEO | Advice Local<\/title>\n<meta name=\"description\" content=\"Tracking phone numbers has always been a fairly reasonable tactic. If you need assistance with tracking tactics, call Advice Interactive at (214) 310-1356.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Totally Awesome Tracking Tactics for SEO | Advice Local\" \/>\n<meta property=\"og:description\" content=\"Tracking phone numbers has always been a fairly reasonable tactic. If you need assistance with tracking tactics, call Advice Interactive at (214) 310-1356.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Advice Local\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/advicelocallistings\/\" \/>\n<meta property=\"article:published_time\" content=\"2012-09-07T04:00:40+00:00\" \/>\n<meta name=\"author\" content=\"Advice Local\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:title\" content=\"Totally Awesome Tracking Tactics for SEO | Advice Local\" \/>\n<meta name=\"twitter:description\" content=\"Tracking phone numbers has always been a fairly reasonable tactic. If you need assistance with tracking tactics, call Advice Interactive at (214) 310-1356.\" \/>\n<meta name=\"twitter:creator\" content=\"@advice_local\" \/>\n<meta name=\"twitter:site\" content=\"@advice_local\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Advice Local\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/seo-tracking-tactics-using-excel\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/seo-tracking-tactics-using-excel\\\/\"},\"author\":{\"name\":\"Advice Local\",\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/#\\\/schema\\\/person\\\/24c2a8bbb06a68d8e0642a3283d7f1f4\"},\"headline\":\"Totally Awesome Tracking Tactics Using Excel\",\"datePublished\":\"2012-09-07T04:00:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/seo-tracking-tactics-using-excel\\\/\"},\"wordCount\":607,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Search\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/seo-tracking-tactics-using-excel\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/seo-tracking-tactics-using-excel\\\/\",\"url\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/seo-tracking-tactics-using-excel\\\/\",\"name\":\"Totally Awesome Tracking Tactics for SEO | Advice Local\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/#website\"},\"datePublished\":\"2012-09-07T04:00:40+00:00\",\"description\":\"Tracking phone numbers has always been a fairly reasonable tactic. If you need assistance with tracking tactics, call Advice Interactive at (214) 310-1356.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/seo-tracking-tactics-using-excel\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/seo-tracking-tactics-using-excel\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/seo-tracking-tactics-using-excel\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Totally Awesome Tracking Tactics Using Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/\",\"name\":\"Advice Local\",\"description\":\"Local Digital Marketing Software Solutions and Services\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/#organization\",\"name\":\"Advice Local\",\"url\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/04\\\/logo.png\",\"contentUrl\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/04\\\/logo.png\",\"width\":265,\"height\":55,\"caption\":\"Advice Local\"},\"image\":{\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/advicelocallistings\\\/\",\"https:\\\/\\\/x.com\\\/advice_local\",\"https:\\\/\\\/www.instagram.com\\\/advice_local\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/#\\\/schema\\\/person\\\/24c2a8bbb06a68d8e0642a3283d7f1f4\",\"name\":\"Advice Local\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/?s=96&d=mm&r=g\",\"caption\":\"Advice Local\"},\"url\":\"https:\\\/\\\/www.advicelocal.com\\\/blog\\\/author\\\/advice-local\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Totally Awesome Tracking Tactics for SEO | Advice Local","description":"Tracking phone numbers has always been a fairly reasonable tactic. If you need assistance with tracking tactics, call Advice Interactive at (214) 310-1356.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/","og_locale":"en_US","og_type":"article","og_title":"Totally Awesome Tracking Tactics for SEO | Advice Local","og_description":"Tracking phone numbers has always been a fairly reasonable tactic. If you need assistance with tracking tactics, call Advice Interactive at (214) 310-1356.","og_url":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/","og_site_name":"Advice Local","article_publisher":"https:\/\/www.facebook.com\/advicelocallistings\/","article_published_time":"2012-09-07T04:00:40+00:00","author":"Advice Local","twitter_card":"summary_large_image","twitter_title":"Totally Awesome Tracking Tactics for SEO | Advice Local","twitter_description":"Tracking phone numbers has always been a fairly reasonable tactic. If you need assistance with tracking tactics, call Advice Interactive at (214) 310-1356.","twitter_creator":"@advice_local","twitter_site":"@advice_local","twitter_misc":{"Written by":"Advice Local","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/#article","isPartOf":{"@id":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/"},"author":{"name":"Advice Local","@id":"https:\/\/www.advicelocal.com\/blog\/#\/schema\/person\/24c2a8bbb06a68d8e0642a3283d7f1f4"},"headline":"Totally Awesome Tracking Tactics Using Excel","datePublished":"2012-09-07T04:00:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/"},"wordCount":607,"commentCount":0,"publisher":{"@id":"https:\/\/www.advicelocal.com\/blog\/#organization"},"articleSection":["Search"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/","url":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/","name":"Totally Awesome Tracking Tactics for SEO | Advice Local","isPartOf":{"@id":"https:\/\/www.advicelocal.com\/blog\/#website"},"datePublished":"2012-09-07T04:00:40+00:00","description":"Tracking phone numbers has always been a fairly reasonable tactic. If you need assistance with tracking tactics, call Advice Interactive at (214) 310-1356.","breadcrumb":{"@id":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.advicelocal.com\/blog\/seo-tracking-tactics-using-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.advicelocal.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Totally Awesome Tracking Tactics Using Excel"}]},{"@type":"WebSite","@id":"https:\/\/www.advicelocal.com\/blog\/#website","url":"https:\/\/www.advicelocal.com\/blog\/","name":"Advice Local","description":"Local Digital Marketing Software Solutions and Services","publisher":{"@id":"https:\/\/www.advicelocal.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.advicelocal.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.advicelocal.com\/blog\/#organization","name":"Advice Local","url":"https:\/\/www.advicelocal.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.advicelocal.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.advicelocal.com\/blog\/wp-content\/uploads\/2026\/04\/logo.png","contentUrl":"https:\/\/www.advicelocal.com\/blog\/wp-content\/uploads\/2026\/04\/logo.png","width":265,"height":55,"caption":"Advice Local"},"image":{"@id":"https:\/\/www.advicelocal.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/advicelocallistings\/","https:\/\/x.com\/advice_local","https:\/\/www.instagram.com\/advice_local"]},{"@type":"Person","@id":"https:\/\/www.advicelocal.com\/blog\/#\/schema\/person\/24c2a8bbb06a68d8e0642a3283d7f1f4","name":"Advice Local","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/?s=96&d=mm&r=g","caption":"Advice Local"},"url":"https:\/\/www.advicelocal.com\/blog\/author\/advice-local\/"}]}},"_links":{"self":[{"href":"https:\/\/www.advicelocal.com\/blog\/wp-json\/wp\/v2\/posts\/2847","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.advicelocal.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.advicelocal.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.advicelocal.com\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.advicelocal.com\/blog\/wp-json\/wp\/v2\/comments?post=2847"}],"version-history":[{"count":0,"href":"https:\/\/www.advicelocal.com\/blog\/wp-json\/wp\/v2\/posts\/2847\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.advicelocal.com\/blog\/wp-json\/wp\/v2\/media?parent=2847"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.advicelocal.com\/blog\/wp-json\/wp\/v2\/categories?post=2847"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.advicelocal.com\/blog\/wp-json\/wp\/v2\/tags?post=2847"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}