You spend time extracting data about AdWords campaigns to send to your clients, your boss, and other stakeholders. But more important for your performance is the data you extract for yourself, to really analyze issues. Let’s look at what you should take into consideration when preparing data for analysis.
This Isn’t Reporting
The most important thing to keep in mind when analyzing your own data is that this isn’t a report. You don’t need to keep things as simple as possible, and you don’t need to dumb things down.
If the problem in your campaign level stats is being caused by a single keyword, you need to know which keyword. So you need keyword level data. If things change on a daily basis, you can’t do your analysis using weekly data.
What Should I Download?
Get more data than you think you need, and don’t try to analyze it in the AdWords interface, or even your reporting system. Get this into a spreadsheet so that you can package it whichever way you need.
AdWords has an intuitive way to download data. Set up the report you want to see in the main interface, and hit the download button. Your date ranges, filters, sorting, and segments will all be respected and included correctly in the download.
- Date ranges: If you intend to do time analysis, get a longer date range than you expect to need. You will get more context for the changes in the period you mean to analyze. If you want to see your winter traffic against your summer traffic, you need last winter’s traffic to make an accurate judgment. Is winter always a lower volume season, or was last winter really great?
- Filters: You’re going to end up downloading a lot of data. Seriously loads. With that in mind, use filters judiciously to get rid of anything you’re not going to need (e.g., filtering out keywords with no impressions in your date range). They haven’t contributed to the traffic or spend, so are likely to be expendable for your analysis.
- Segments: This is where it gets awesome. In the interface you can apply one segment at a time, but when downloading a report you can apply three. Segments provide you with a way to split your data out (e.g., if you are looking at keyword data over a one week period and apply a segment of “day” then each keyword would now have 7 entries: “keyword one, day one” “keyword one, day two” etc.).
If you were to apply a second segment (e.g., device) then you’d now get even more entries per keyword: “keyword one, day one, desktops” “keyword one, day one, mobiles” and so on.
You can see that over a long time period with several segments you’re talking about a lot of rows of data. You can’t open more than about a million rows in a single sheet in Excel, so consider that when putting your data together.
For any time series analysis you’re going to want to keep a time-based segment involved. The other two most common segments you’ll want are likely to be Device and Top vs Other. These two are crucial for many kinds of campaign analysis since the behavior of users in each situation is likely to be so disparate.
You’re Going to Love Pivot Tables…
Pivot tables appear much more intimidating than they really are, because the interface to use them is so different from the rest of Excel. Different, but better.
The purpose of a pivot table is to allow you to grab any set of metrics by any dimension in your dataset. You may have downloaded keyword data, but each row will have a campaign and an ad group associated with it. By setting campaigns as your row labels and clicks as your data, Excel will add up all the clicks which match that row label (e.g., total campaign clicks).
So you can do campaign or ad group analysis, even from your keyword-level dataset. In fact, you can split by your segments too, so you can see total clicks, impressions, etc by device, or by top vs other.
For most pieces of analysis it should be a default to start off by putting your data into a pivot table, and work from there.
Pivot Tables 1, 2, 3
Once you create your pivot table you will have four boxes. Drag any field (e.g. column from your dataset) into a box to set it to have these features:
- Report Filter. This will give you a drop-down to filter in (or out) particular items (e.g., only include items where the “Device” field is “mobile”).
- Row Labels. Drag any field here to make it be the set of rows for your new table. Every unique entry will have one row, and any values will be summed (or counted, or averaged) for every item in your dataset where this field matches.
- Values. The actual values you want to see, e.g. clicks, cost, etc.
Column Labels. This gives you an ability to add a second dimension and see the results as a flat table. In this scenario you’d usually only add a single value, and you’d be able to find the intersection of your rows and columns to find the right value. This might be something like “Top vs Other” if your value is impressions. Then if your row labels are campaigns you’d be able to see the total impressions in the banner vs the right hand side for that campaign.
It’s important to note that if you’re using column labels you’re likely to also want to change the way the values are displayed. Click on the field you’ve dragged to the Values box, and choose “Value field settings”. In the “Show Values As” tab you can choose “% of row total” to show each column as its percentage contribution to the total. Voila, you now have the proportion of impressions in top vs other.
If you aren’t using column labels you might instead choose to show the values as a percentage of the column total. So you could quickly and easily show something like the proportional contribution of each ad group to total spend. Throw in a device filter and you’ve got some interesting data.
Pivot tables have a limitation. If you want a calculated metric in there you’re going to struggle. The default calculation is to sum all values matching that row’s field. To see why this is a problem, imagine you want to see click-through rate (CTR) per campaign from keyword data. Your table will show you a row for each campaign, with the total sum of CTR measures.
That’s bad. You can’t just add up CTR for each keyword to get overall CTR. You need an average. That’s lucky, because pivot tables have an “average” option, instead of a summation.
But that’s bad too. If keyword A has 10,000 impressions and a 3 percent CTR and keyword B has 1,000 impressions and a 10 percent CTR, using the “average” option in a pivot table will give you a value of 6.5 percent CTR. Which is clearly incorrect. Instead you need a weighted average.
Select a cell inside the pivot table and you’ll see the “PivotTable Tools” tabs appear in the ribbon. In there you have a drop-down labeled “Fields, Items & Sets”. From this drop down choose “Calculated Field”.
Set up the calculation you need. In this case, clicks/impressions. What Excel will then do is create a whole new field, which is calculating correctly the sum of clicks, then dividing by the sum of impressions. Much better than adding up or averaging CTR values.
Make sure you’ve downloaded a time-based segment in your report if you want to do time series analysis. If you use “day” you’ll get a date. Use Excel functions to turn those into weeks or months if you need to.
If you choose to insert a pivot chart (or add a pivot chart to an existing pivot table) then you’ll be able to do the same drag and drop setup as before.
Drag your date to the horizontal axis, and drag your value(s) into the value box. Use calculated metrics if necessary. Now use the Legend field (equivalent of the Column Labels field on the pivot table) to set different series (e.g., drag device into the legend field to see your metric as separate lines for desktops, mobiles and tablets).
What This All Means
With pivot tables and pivot charts you can package data by any field that you have in your dataset. But you need to have those fields available to begin with.
Within the constraint of trying to have fewer than 1 million separate lines of data, you can go with the maxim that more is better. You don’t want to set up your comparison and then say “I wish I could have filtered this by device!”
You can apply three segments to your report downloads. Use them all and use them wisely. The ability to slice and dice later on will make your job much easier.