It was the best of times,
it was the worst of times,
it was the age of wisdom,
it was the age of foolishness,
it was the epoch of belief,
it was the epoch of incredulity,
it was the season of Light,
it was the season of Darkness,
it was the spring of hope,
it was the winter of despair,

Title: A Tale of Two Cities
A Story of the French Revolution
Author: Charles Dickens


We had everything before us, we had nothing before us, we were all going direct to the top of SERPs, we were all going direct the other way. We can all remember the days of keyword insights. And since those days have since gone, and passed, we must use all of the tools at our disposal to regain insight. Over the course of this segment, I have worked to provide insight into how you can use paid data to make smart changes to your site. In this post, I’ll show you how you can approach geo-targeting your site based on your paid performance.

The Preparation

We’re going to need to download some data in order to get started, but don’t worry: It’s nothing crazy. Let’s begin with AdWords data. Jump over to the Dimensions tab and change the view to Geographic. Your screen should look something like this:

undefined

Now that you’ve got your view looking like mine, go ahead and download into an excel sheet. Once you’ve got the manipulable data, it’s time to get organized. Sort by the primary KPIs you’re looking to address. Breakdown the data into two smaller levels. You’ll want to see states, and then cities:

undefined

undefined

For the purpose of this blog, I’ve picked the top cities from the top state. Now it’s time for us to grab some insights from Google Analytics as well. We want the following views of data: Keywords by City, Keywords by Landing Page, Landing Pages by City. You should be able to get everything you need from a single custom report with different tabs.

An Opinion

So, now that you’ve got all the data that you’ll need in a neat excel sheet it’s time to do the obnoxious aggregation of data that all of us analysts love the most. Here’s an example of a “Sum ifs” statement that will help aggregate things that are all messy and all over the place:

=SUMIFS(‘User locations report’!L$3:L$521,‘User locations report’!$B$3:$B$521,$A$3,‘User locations report’!$D$3:$D$521,‘Cities of State’!$B3)

  • This is the sum range. In this part of the statement, include the column of data you want to be adding.
  • These sections in blue define the criteria range (where you’re looking for the stuff in green)
  • These segments define the criteria you are looking for in other cells.

Now you should be able to create something like this:

undefined

With the final aggregation of data, you can start advising your SEO team on three dimensions above. They can enhance the pages by creating more localized content (either new or refreshed) in order to increase visibility to persons in those areas.

The impact of this data will be different for every website. However, it’s important that your website optimization teams know what they should be building. More often than not, long-tail keyword insight can give you a deeper granularity into the content you should be building.

Triumph

Congratulations, you’ve completed some advanced data analysis to help guide your team to victory. If you noticed that I stole all of my sub-titles from chapters of a certain book, you should grab a blue ribbon too.

Using the information you’ve compiled, you can help increase KPIs to help your business or clients’ business to grow. Heck, you might even hear someone say:

It is a far, far better thing that you do, than you have ever done; it is a far, far better user experience that I go to than I have ever known.