Open Refine & AdWords

Open Refine is a really powerful tool for analysing and modifying large sets of messy data; it's the perfect tool for analysing large AdWords accounts.

Download OpenRefine

You’re going to need a copy of OpenRefine before you can use it to analyse your AdWords data.

OpenRefine is a free tool that was previously built by Google (as Google Refine), but later open-sourced and renamed, it’s designed to clean up large volumes of messy data, which is perfect for AdWords.

Grab yourself a copy of OpenRefine.

Download AdWords Data

We’re going to analyse your search terms in this example, since it’s likely that you will have a lot, and it’s annoyingly complex to analyse it in desktop spreadsheet tools like Excel and Numbers.

  • Log into Google AdWords
  • Go into the Account
  • Set the appropriate date-range top-right
  • Click into the Keywords Tab
  • Click the “Details” button below the graph, select “All” beside search terms
  • Click the Download button
  • Change the format to just .csv
  • Click the blue create button, it should download the file.

Heads Up! From this point on, we’re using Google Refine version 2.5 [r2407] on a Mac, if you’re using a different version, or a different platform, then your experience may vary.

Get the data into OpenRefine

We’re going to get the data into OpenRefine, in a format that you can start working with it.

  • Open Google Refine
  • Your browser should open up at a URL similar to http://127.0.0.1:3333/
  • In the left column, click to “Create Project”.
  • Get data from “This Computer”, choose the AdWords file you downloaded (Search term report.csv), and click next
  • Along the bottom in “parse data as”, select the radio box beside (CSV)
  • On the right-hand side tick the option to “Ignore first” and enter “1″ line at beginning of file
  • Tick the option to “Parse next” and enter “1″ line as column header
  • Un-tick the options to store blank rows and cells
  • Give your project a snappy name top-right, and click the “create project” button

Well Done! It’s now putting all the data in a format for Google Refine, this can take a little while (1-2 minutes), so be patient

Tidy the data up so it’s easier to work with

OpenRefine is really powerful, but AdWords exports data with extra formatting to try and make it easier for people to work with the data, and we want to remove them all before doing any analysis. For example the total row, percentage symbols, commas, etc.

Removing symbols from data

Look for any column that could contain a % or a comma, Google Refine interprets those values as text, which obviously we don’t want, since we’re working with numbers.

AdWords will commonly add commas to any number cell over 999, and will add % symbols on the end of percentage

It can be time consuming to do this in OpenRefine, so if possible, sometimes it’s easier to just open the data in excel and do a mass find/replace for the characters, but this generally only work if there aren’t a lot of rows/columns (in which case OpenRefine maybe isn’t all that useful)

  • Click the down arrow beside the column name
  • Select “Edit Cells”, then “Transform…”
  • In the “Expression” window you should see “value” Change the Expression to value.replace("???","") replacing ??? with the character to you want to replace. e.g. , or $ or %
  • You will see a preview of the result in the preview tab below the Expression section
  • Click the OK button

The system will run through every cell and remove your character. A side effect of the transform is changing the cell type to “text”.

Changing cell types

Every cell in a column has a type, of number, date, or text. You want to make sure the column type is correct, because it affects how facets work. Text cells look like normal text, whereas number cells show up as green.

  • Click the down arrow beside the column name
  • Select “Edit Cells”, then “Common transforms”
  • Select “To number”, “To date” or “To text”
  • The system will change cell type, this can take a little while

Facet and Filter

Facets are basically the filtering options for all of your data, it’s what you’ll use heavily to actually analyse the data.

  • Click the down arrow beside the column name
  • Click “Facet”, then the type of facet that’s appropriate (text, numeric, timeline, scatterplot
  • Your facet will appear on the left, and look different depending on the type

Logarithmic facet

Sometimes instead of seeing an actual number, you want to see the logarithmic curve of all of the numbers, so you can work out what that average is, and where the outliers are. This is super useful for numbers that are an average (cost per click), or per conversion figures (cost per conversion).

  • On your facet on the left, click the “change” link
  • In the expression for the facet, instead of “value”, enter value.log()
  • Click the OK button
  • Wait for the system to generate the facet, it might take a little while (depending on how much data there is)
  • Underneath your facet, un-tick the options for non-numeric, blank and error