March 8, 2018
Large PPC accounts can be fun to work on, but a common frustration is how to manipulate and gather insights from mountains of data. In order to take advantage of said data, you need to be able to easily (and quickly) pivot, label, group, sum, etc. In this post we’ll walk through a few options to make your data easier to manage.
The Problem
In a nutshell: tons and tons and tons of data! How do you manipulate it? What’s the best way to pull actionable insights from it? So many questions!
Here’s a breakdown of one of our larger paid search accounts that we’ll be using as an example today.
For this particular account, the numbers above only represent one AdWords account out of many, and they also operate on Bing. As you can see, the numbers very quickly add up when you combine all the accounts.
We need to run analysis broken down by month, with the data grouped by brand vs. nonbrand, and single keyword ad group (SKAG) vs. non-SKAG. We’d like to look at overall keyword and ad performance, as well as quality score.
Solution #1 – Use Filters
There are multiple ways to use filters to pull less data into Excel or sheets. Two common ones are filtering by status or filtering by category (brand vs. non brand, etc.)
The pro here is that this is easy to do within most interfaces. The con is that once you pull the data, you lose the option to use data that has been filtered out later on. For me personally, I like to have all the available data to mess around with, in case I decide I want to tackle something from a different view. Going back to the interface to re-download can be quite a time suck.
Solution #2 – Labels Galore
If you want to take the time to label all of your campaigns / keywords / ads / etc., then you have the option to use the Report builder in AdWords.
This will group all of your data together by label, but you do lose some potential detail here. For example, if you pull campaign labels you won’t be able to see keywords. There are some instances where this is a great option, but again, you’re a little limited in what you can do.
Solution #3 – Third Party Platform
This is where platforms such as Acquisio, Marin, Kenshoo, etc come into play.
One benefit of using these services is the ability to link with multiple ad platforms such as Google, Bing, Facebook, LinkedIn, etc. Reporting abilities typically come built in, and some of the dashboards are pretty slick.
You may be reading this thinking “great! Sign me up!” but hold on to your horses. The downsides of going this route include
- Price – be prepared to fork over some cash for a subscription
- Usability – speed can also be an issue here, and often we run into issues when trying to export large amounts of data
Solution #4 – RStudio
If you are getting bogged down with slow platforms or tools (lookin at you – rainbow spinny wheel when Excel is about to crash) and are comfortable with a little code, RStudio can be a great option to manipulate your data. While this isn’t a super common tool in the PPC world – it’s extremely versatile and PPC managers would be well served to use it for their larger accounts.
There are several ways to get your data into RStudio,
- Have it read a csv file
- Have it read from a Google Sheet
- Pull directly from the AdWords API (note: this requires an AdWords developer token)
Once you reference the data, there are tons of opportunities to slice, dice, and analyze to your hearts content. In addition to manipulating data quickly, there also also some advantages to pulling things into RStudio that you can’t easily do in Excel. For example, this account is mainly comprised of single keyword ad groups, and we want to easily find which ad groups contain one keyword, label them, and then compare brand vs. non brand.
A simple script groups the data in an easy-to-read table:
If you prefer visuals, R has some great options for graphing:
R also has a variety of statistical analysis tools (which can be handy for landing page or ad tests!).
I won’t go into all of the code here, but if this interests you there are tons of free resources available online.
Wrap Up
If you find yourself immersed in data from a huge account, hopefully the solutions outlined above can help you manage your data. I’d love to hear how you currently handle these situations! Do you use any programming language to manage your data? Rely on third party platforms? Let us know @ppchero!