Recently I posted an article about how to do some simple use of Excel to help you create and post your campaign faster on AdWords. This time around I will be giving you more tips on how to use Excel for AdWords. This time however I will concentrate on easy implementations for you to analyze information prior to your optimizations.
I will give some quick and easy pointers on how can you manage more data and analyze your campaigns better by simply using Excel.
Similar to Editor, you can do most of the stuff you can do in Excel by using the AdWords interface, but still I have found a couple of ways to make your job easier and faster with Excel whenever you are optimizing.
I will divide my tips into checking three aspects of one campaign:
- Ad Groups
- Search Terms
Analyzing the information in your ad groups
You will need to access your AdWords account and then in your campaign select the Ad Groups tab, now you will need to download a report of this view and then open it using Excel.
For this you need to do the following:
After you do this you will only need to open the file that was downloaded with Excel.
Start with Applying a Filter
The first step I will advise you to take in this file is to delete any rows that have totals as they may skew your data when you are sorting it or filtering it, second will be to give all the headers a filter.
There are many ways to put a filter, but the way I do it is that I select the row where my headers are and then type CTRL+Shift+L.
After doing this the headers should look like this:
What this filter will do to help you is that it will allow you to sort, take away certain items or select only some specific ones.
You can do this by clicking on the arrow corresponding to the particular element that you want to interact with (for example that you don’t want to see data for a particular ad group, you can click on the arrow that is on Ad group and then from the list that drops down simply take out the check mark of any ad group that don’t want to see).
Also if you click on the arrow of any of the metrics you can sort the data from largest to smallest or from smallest to largest depending on what you want to see specifically.
This will be like the basic takeaway that you can get out of Excel to be able to see the metrics for your ad groups better. But the way I use it is combined with the segments option.
By combining the segment’s feature and Excel you can easily see a lot of data at once; the main segments I check are:
Time » Hour of Day:
After you have segmented the Ad Groups, download your report, and then delete the totals as I recommended earlier. Now what you need to do is select all the data starting from the headers and create a pivot table with it.
You do this by doing the following:
This will create the pivot table on a separate sheet by default, you know need to select the proper elements in it.
I use the following set up:
You first drag the Hour of day element to the Row Labels and then the Ad group element, afterwards you can add any metric that you deem important to the Values (for example you can add Clicks, CTR, Conversions, etc.).
The values will be added as a sum of them and in numerical format as a default, you can change this by clicking on the arrow showing on the value name and then clicking on “Value Field Settings”, here you can change your CTR (or any other metric that normally represents a percentage) to reflect averages and to show as percentages.
By doing all of this you will be analyze your ad groups very granularly by how their performance is based on the time of the day.
Same deal as the one mentioned above, segment your ad groups, download the report, delete your totals, and insert a pivot table based on this information.
The setup for this should now be:
To do this you first drag the Device element and then the Ad group, add the metrics that you deem important for your campaign as values, change any value settings depending on how you want to see them and you will be able to have a well distributed amount of information that will show you how your ad groups are performing depending on the device that is being used to search for your keywords.
You can actually do this same analysis with pretty much all the options available in the segments, but those 2 I mention are the main ones I use. Feel free to check the other options to see if perhaps there are elements that you may consider more relevant to your goals with AdWords.
Analyzing the information in your keywords
In order to efficiently use this you need to make sure you have the right set of columns in your AdWords interface in the Keywords tab.
I for example always use the following set of columns:
When you have the set of columns that you consider will work best for you then you can download a report of this view to be able to work it on Excel. As we did when we were analyzing the ad groups you will need to delete the totals and then create a pivot table with the data table that you currently have. Now the set up that I will recommend you for this pivot table will be the following:
In this case you will first add the Ad group element followed by the Keyword. Now you will need to add any important metric that you want to review as a value, these will allow you to quickly view and analyze all the keywords that you have in your campaign while knowing to what specific ad group they pertain to.
Also by implementing filters to this pivot table, just like we did with the headers previously you will be able to take away certain elements or certain metrics being met in order for you to easily see the performance of your keywords.
Analyzing the information in your search terms
In order for you to be able to get this information you will need to be at a campaign level and in the Keywords tab, here you will need to select Keywords details and then select All, this will allow you to view all the search terms that have helped trigger your keywords in the date range that you have specified.
Here is how to do it.
Once you have the search terms displayed on your interface you can download the report for this view to be able to work it on Excel. Once you have the information in Excel you will do as we did with the previous ones, delete all the totals and create a pivot table based on the information you have.
The set up for this should be the following:
This will allow you to easily analyze all of the search terms based on the ad group where they will respectively triggered and on the match type that lead the specific search term to trigger our keywords.
As we did to the previous examples please add any metric that you will consider important in the values, you can for example add clicks and CTR to it to know what search terms gave you the most amount of clicks or has the most relevance to searchers.
Play with the metrics depending on what you want to know from your search terms.
By having this thorough analysis through some of the different aspects of your campaign’s performance you should have an easier job determining what to optimize and how to actually do it. These are just some simple ways on how to use Excel to make your life easier when handling big sets of data; it should save some time out of your optimizations while still allowing you to make good decisions.