How to Use Excel for Search Marketing – To Lower Cost-per-Conversion

Claudio Dominguez

12 years ago



Some days ago I presented you with some tips on how to implement the use of Excel before optimizing your AdWords campaign. In the previous post, I gave you some pointers on how to easily analyze big sets of data before diving into any optimization. This time I’d like to uncover more on the subject of analyzing data, but I want to first focus on the importance of viewing the right data that will allow you to make adjustments to start lowering your cost-per-conversion.

First off and before I even begin to give you advice, you will need to make sure that you have a campaign that is properly measuring conversions and that you have a steady amount of conversions stemming from it.

If you don’t have conversion tracking enabled, or if you do but your campaign only triggers a small number of conversions a month (this will depend on your industry), then there will simply not be enough data for you to really make sound decisions.

Once you have made sure that your campaign has a good amount of conversions, then you can implement some of the ideas I have put to use in the past.

The following are some methods which have helped me to easily reduce the cost-per-conversion of a campaign:

    • Analyzing what hours of the day are performing better
    • Analyzing what landing pages are giving the best cost-per-conversion
    • Analyzing where users are converting most from

This entire analysis can be made from the Dimensions tab within AdWords, so please navigate there now.

Once you are in the Dimensions tab of the campaign that you want to analyze, you will need to make sure that you have the right set of columns selected. You will need to make sure you are clearly visualizing your ad groups.

For this, you need to do the following:

excel to lower cpc 1

excel to lower cpc 2

This will allow you to better be able to visualize the data at an ad group level, which will simply make it easier for us to analyze and optimize the campaign later on.

Now that you selected your campaign, you’ll find yourself in the Dimensions tab with the right set of columns in view so that we can now commence.

Let me go over how to implement my tips:

Analyzing Hours of the Day:

In the Dimensions tab, make sure that you select View, and then Time, and finally Hour of Day, as so:

excel to lower cpc 3

Now that you have these sets of data prepared, you will need to select the date range that you want to analyze and then download everything in a CSV file, like so:

excel to lower cpc 4

Open the file and as I mentioned in my previous article, the first step will be then to delete any rows that have totals, as they may skew your data when you are sorting or filtering it.

Secondly, you should select all the data in the table and create a pivot table with it. This can be accomplished like this:

excel to lower cpc 5

Now that you have your pivot table ready to go, we will need to arrange the elements in it. This is the particular setup that I recommend you use:

excel to lower cpc 6

This will basically allow you to determine at what hours of the day your ad groups are converting better and at what hours they are not proving profitable.

Based on this, you could:

    • Schedule bid adjustments
    • Remove your ads from showing at certain hours in the day
    • Split up your campaign by making new campaigns for ad groups that are performing better at different hours than the average ad groups in the campaign

Analyzing Landing Pages:

This technique will better assist you when you have been testing with different landing pages in your ads to see which one converts best (if you have been using only 1 or 2 landing pages in the campaign, then you won’t really get much from this tip).

The concept behind this (and for the following tips, for that matter), should be the same; you will need to select View and then Destination URL, like this:

excel to lower cpc 7

Now you will need to do the same that we did with the previous tip: download the data in a CSV file, delete any totals, and create a pivot table with your data. The set up that I recommend for this pivot table is as follows:

excel to lower cpc 8

Through this technique, you will be able to easily analyze what landing pages have been working out better for you. You can make a comparison between costs and the number of conversions that your landing pages are generating.

Furthermore, it will also allow you to test landing pages across ad groups, or even discard some and simply avoid using them in your campaign altogether moving forward.

Analyzing User Location:

For this method, you need to select View and then User Location, just like this:

excel to lower cpc 9

Afterward, and in order for your data to be cleaner, I will recommend you to exclude certain columns. You will need to exclude the Most Specific Location & Metro Area columns. You do so like this:

excel to lower cpc 10

Now you can proceed like we did before: Download the data in a CSV file, delete any totals and create a pivot table with your data. The set up that I recommend for this pivot table is as follows:

excel to lower cpc 11

These insights regarding your conversions and cost-per-conversion based on user location can help you determine if you want to exclude certain locations.

These are some good tips that could help you make simple changes that will make a big impact on how your campaign is performing.

Furthermore, it will help you better understand what your campaign is costing you overall. This can be accomplished by trimming the unnecessary fat so you can easily eliminate extra spending as well as raising your conversions by focusing more on what is really working in the campaign.

Free Adwords Book