excel « Datadial Blog
0208 6000 500

On the subject of excel

markenlogo_searchmetrics_0

Matt

July 18th, 2013.

Using Searchmetrics And VLookup For A Competitor Rankings Comparison Report

Searchmetrics is a brilliant SEO tool, the amount of insight that it gives on client and competitor sites is incredibly useful. One of my favourite reports, along with some manipulation in Excel is to run a quick rankings comparison report on your competitors so you can gain insight into what they’re ranking for, more importantly what they’re ranking for and you’re not, and also how your site matches-up a full range of industry keywords.

For this sample report I’m going to take a look at some of the bigger sites in the insurance sector.

http://www.aviva.co.uk
http://www.churchill.com
http://www.lv.com
http://www.morethan.com

Other good insurance companies do exist, along with quite a few terrible ones.

Run each domain through Searchmetrics and run a long-tail keyword report on each of the sites that you wish to compare.

Organic   Rankings   aviva.co.uk  Weekly    Searchmetrics Essentials

Export and download each of these reports.

Organic   Rankings   aviva.co.uk  Weekly    Searchmetrics Essentials2

 

In Excel create different sheets for each of the exports along with the first sheet which should be named ‘comparison’ this is where all of the magic happens and your data will be pulled-in.

sheets

Paste each sites data into onto it’s own sheet, as well as cumulatively into the ‘comparison’ sheet.

Then under Data > Remove Duplicates remove duplicated keywords on the ‘comparison’ sheet.

remove-duplicates

Then delete the ¬†following columns in the ‘comparison’ sheet ¬†- URL, Pos, Title, and Traffic Index. This should leave just Keyword, Search Volume and CPC.

Next add columns for each of the sites that you wish to compare. This should leave you with a sheet that looks something like this.

sheet

Then, using VLOOKUP you’ll need to pull the ranking data from the other sheets into the comparison sheet. So for example into Column C all of the rankings for Aviva will appear.

The formula you’ll need is =VLOOKUP(A:A,Aviva!A:G,3,0) The easiest way to generate this is to use the insert formula function,

function

Lookup value – Is the value that you’re looking up, in this case is column A, the keyword.
Table array – is the table you’re finding the value in, which is the Aviva sheet, so click in the table array entry field, then go to the Aviva sheet and highlight all of the columns.
Col index num – is the column with the data in that you wish to import, so column 3, the ranking position.
Range lookup – Enter FALSE or 0 here to find an exact match. This will cause #N/A to be returned if the site isn’t ranking for the keyword.

Repeat this for each site. And then expand the selection by dragging the corner of the box down to apply to each of the cells in the sheet.

expand

Tidy the sheet up by formatting as a table, and (hopefully) you should have something that looks like this.

final

If the #N/A results are annoying you can easily remove them by modifying the VLOOKUP formular from

=VLOOKUP(A:A,Aviva!A:G,3,0)

to

=IFERROR(VLOOKUP(A:A,Aviva!A:G,3,0),”-”)

You can also colour-code the rankings using conditional formatting.

endsheet

 

If you would like to download this example sheet I have added it here - CompetitorReport

 

 

Adam Adam

August 22nd, 2011.

Visualizing your busiest PPC time periods using pivot tables & Excel

Ad scheduling can be a particularly useful tool to use within Google AdWords if you’re running a campaign on a tight budget. For anyone who hasn’t used Ad Scheduling before, it allows you to set time periods in which your AdWords ads within the selected campaign are allowed to show. This is useful because with a little research you’re able to find out when are the busiest hours of the day and adjust your AdWords campaigns accordingly, allowing your available daily budget for each campaign to be spent only within the time periods specified. I’m going to show you how I go about finding this out for each campaign, and how to set it up in approx. 10 minutes!

Sounds Great! How Do I Know What Times Searchers Are Most Active?

First of all you’ll need a sample period where Ad Scheduling isn’t used and you’ll need a fairly decent daily budget so that the display of ads isn’t limited by your daily budget. I’d suggest running the campaign like this over a month and work with the data available.

Step 1: Download the Report

Log into Google AdWords and select the date range for the sample period. Click on the ‘Campaigns’ tab and click on the reports icon, shown below:

AdWords report button

AdWords report button

 

The box will then expand to show the report name, format, and allow you to add segments. Click the ‘+ segment’ link, adding the three segments shown below:

AdWords report segments

 

Add the ‘Day’, ‘Day of the week’ and ‘Hours of day’ segments to your report and click ‘Create’ to download the report. Once downloaded open the report in Excel.

 

Step 2: Using Pivot Tables to Group Periods

Depending on the number of Campaigns and AdGroups you have running, chances are you’re going to have a spreadsheet with quite a few rows. To make sense of this we are going to break this down using a pivot table.

First delete the top row (containing the report name and the sample data period) so that:

becomes:

 

You will also need to remove the last few rows from the bottom of the spreadsheet containing the totals as well:

Next highlight all columns (my example goes from columns A to Q), and under the ‘Insert’ menu in Excel click ‘Pivot Table’:

 

You will then see a dialog box similar to the one below- click ‘OK’ to create a pivot table in a new sheet. After, click on the new sheet where you will see the empty pivot table:

Pivot Table Field List highlighted in Green

 

You can now start adding the fields required to the areas within the ‘Field List’. To start with, drag the ‘Campaign’ field into the ‘Report Filter’ box, ‘Days of week’ into the ‘Column Labels’ box, ‘Hour of day’ into the ‘Rob Labels’ box and ‘Impressions’ into the ‘Values’ box. The field list should look like this:

Next click the down arrow on ‘Count of Impressions’ value in the ‘Values’ box and click ‘Value Field Settings:

and select ‘Sum’ before clicking ‘OK’.

You should now see that ‘Count of Impressions’ has changed to ‘Sum of Impressions’ and the values within the pivot table have also changed. You can now see the total number of impressions for the selected campaign broken down by hour of the day for each day of the week:

Note you can filter by campaign by selecting the campaign name (highlighted)

This is pretty useful as you can see the number of total number of impressions for each hour of the day for each day of the week. The only problem is I’ve then got to compare the numbers, and since I prefer pretty pictures or graphs, I’d rather see this visually represented.

Step 3: Make It Pretty

To see a visual representation of more popular hours we can add conditional formatting to the table and highlight busier periods. To do this, start off by selecting all of the values for ‘Monday’ and under the ‘Home’ menu, click on ‘Conditional Formatting->Color Scales and select an awesome-looking colour scale:

 

Then do the same for the other columns for other days of the week (you’ll have to do each column individually). Afterwards you’ll end up with something like this:

Here you can see how the number of impressions differs by hour on each typical weekday, and more importantly when the quieter periods are. You can then apply this data to each Campaign (by changing the Campaign drop down in cell B1) and apply ad scheduling to these periods. This will allow you to show your ads only during the periods where searchers are more active, meaning your available daily budget is used more wisely.

Remember to consider different timezones- if your campaign is targeting more than one timezone you will need to account for this, and you may wish to separate different timezones into separate campaigns.

 

Recent Posts »

Our work »

What we do »

Who we work with »

Got Questions? Lets Talk »