Paid Search Power Tools: SQL

I don’t think many of you would disagree that the most important tool for getting the best out of a paid search account is the brain of the analyst working on it. But if I focused on that then this would be a pretty short talk; I don’t know much about recruitment or how to tell if someone really knows what they are talking about in a short interview.

So, assuming you already have the best possible people working for you the tools question becomes one about how best to enable these minds to produce high quality outcomes.

One of the things you do not want to do is constrain creativity in any way; remember, you have the best people working on this so you want them to be able to express themselves however they like. I feel a bit stupid touting a spreadsheet as an excellent tool for creative people, but Excel is very good at this.

With Excel, AdWords Editor and a bit of ingenuity/Googling there isn’t much that can’t get done but you are here to here about Paid Search Power tools, not to listen to me validate stuff you are already doing. Excel is a jack of all trades and by picking specific tools for some common tasks we can do much better.

One such task is gathering and marshalling data. I believe the best tool for this job is SQL and one of the many databases that allow you to query them in this language.

Whoa! Hold on there. I don’t know SQL
Writing basic SQL queries is not hard. And besides, you are intelligent and a geek; you can learn this. W3Schools have a good tutorial that is covered in adverts and I like the PostgreSQL Documentation and the SQL tutorial from SQLzoo.

It all sounds pretty complicated
It isn’t. Suck it up and learn something

I don’t really want to go into specifics about how to do certain tasks but here is an idea of some of the queries I have run over the last couple of weeks (written in English to aid comprehension):

  • Find all keywords where the quality score has changed since last week
  • Find all keywords without a keyword level destination URL where the adverts in the ad group only have 1 destination url (i.e fix my mistake of leaving off tracking urls without breaking the ad groups where we are split testing landing pages)
  • Compare metrics from queries that contain “dogs and cats” to queries that contain “cats and dogs”. There was no difference so I felt safe lumping them into the same ad groups

I’ve focused the above examples on tasks which required linking data from more than one report/dataset (e.g keyword report and advert report in example 2). My Excel-fu is too weak to do this easily; I think it is much easier to do this in SQL.

Another useful thing to remember is that you don’t just have to link with other datasets from Adwords. Pull in stuff from Google Analytics, get CRM data, compare with competitor ad rankings and ad texts, correllate with weather reports and maybe even import clicks and costs from Yahoo. A database provides a convienient place to store all this data and SQL provides the means to marshall it and turn it into useful insights.

SQL also has the advantage that it is easier to automate than an Excel sheet. Take my quality score example from above; I wouldn’t know where to start with setting this up to alert me automatically everytime something important changed; this is much easier for a programmer or developer to do if they can interface with an SQL database.

If it is so awesome why isn’t everyone doing this already?
I think there are four reasons for this:

  1. You don’t know SQL but you are already pretty good with Excel.
  2. SQL is only good at one thing
  3. Getting data from AdWords into an SQL database is not as easy as getting data from AdWords into Excel
  4. Thinking vs. Doing

I’ll deal with the first two points together and answer them both at the same time:
You don’t have to only use SQL. You can still use Excel for some things

Excel is good at drawing graphs, doing simple calculations and it is comfortably familiar. My current workflow starts with an SQL query to get the data I want into a format where I can easily mess about with it in Excel. I still spend more time in Excel than anything else (except perhaps tvtropes.com). It is very possible to make a transition into using SQL as part of your everyday life. As I have become more proficient at SQL I find myself spending less time on it because I can write the query that I need faster.

The third point is a tricky one. My current agency, Forward3d have their own system for this which I am not going to talk about here because I wouldn’t do it justice. Luckily for you, in my old job at SEOptimise I spent some evenings working on my own solution which you can use for free.

Download at https://github.com/ppcanalytica/AdWords-to-PostgreSQL

Alternatively I’m sure that almost all of the paid search tool providers can offer you this feature or something similar enough that it makes no difference (don’t confuse the tool with the result; SQL is not the point, flexible data analysis is).

It’s pretty basic and hasn’t had much testing but it will be enough to get you started. You can @RichardFergie on Twitter or use the contact form on this site to get in touch with me if you have any questions or problems with it. I’ll do my best to help you get started.

One of the things I found harder to adjust to when first starting to use SQL what the “Thinking vs. Doing” difference. When working in Excel my hands are pretty much always moving; I’m applying filters, sorting columns, adding colours, creating derived metrics, adjusting scales on graphs and all sorts of other minor tasks. Quite a lot of this is just ‘busyness’ and I am not actually finding anything useful. The sparseness of an SQL prompt doesn’t allow me to do this so it sometimes feels like I’m being unproductive. In fact, I’m being forced to think. That is a good thing even though it feels like I’m just sitting there doing nothing.