Public | Automated Build

Last pushed: 2 years ago
Short Description
Database scripts for Cirrus
Full Description
  • Cirrus - An AdWords Data Thing
    This repository contains a selection of tools for downloading AdWords
    data and inserting it into an SQL database.

It is very much work in progress. You should email me at if you actually want to use this so that
we can discuss.

If you want to proceed anyway then here is what you need:

  1. A host computer running Docker. I use the [[][Docker image from Digital Ocean]]

** Instructions

  1. Clone this repository onto your local machine
  2. On the server create a file in ~/ This file contains passwords
    and other other secret information the will be needed when the application runs.
    See for details.
  3. Run which ships stuff to the host and starts some bits up
  4. This next bit is a bit messy. You need to add information on the account into
    the adwords_account table in the admin database. Then you should run
    database/ again which will create the necessary databases. NB: This
    step is out of date. This info is stored in the 'web' database
  5. You need to set a cronjob to run daily. This updates the database with new data each day.
    My cronjob looks like this:

    0 4 * ~/ | sed -e "s/^/$(date +'\%F \%T') /" >> /var/log/update.log 2>&1

Still to do
Short term

  • [ ] Fix keyword performance report bug - not downloading/inserting sometimes
  • [-] Add in web frontend [4/5]
    • [X] Merge in jupyter-notebook proxy stuff to work with the web frontend
    • [X] Deployment script for website
    • [X] Admin interface to add new AdWords accounts
    • [X] Either setup email verification OR make login that doesn't require it
    • [ ] Currently deployment relies on the stack buildtool being
      installed - the web binary is compiled on the local machine. I guess this is
      not desirable.
  • [ ] Better deployment instructions/walkthrough
  • [ ] Example notebooks (this is possibly not short term)

*** Longer term
The essence of any AdWords system is in three parts:

  1. Something that can get the data out of AdWords and store it in a way that is easily queryable
  2. Some kind of business specific magic that figures out what changes to make
  3. Something that makes it easy to push changes back to AdWords.

A generalised way of doing part 2 is way out of scope here.

Cirrus is currently only a solution for part 1. And an incomplete one at that - there is
quite a bit of data in AdWords that isn't downloaded (yet).

** Business magic
I'm working on a "something" (want to say 'platform', but that is mega wankey) for part 2
based on jupyter notebook.

This will also fill in a gap in AdWords Scripts. Scripts are great for doing stuff but shit
for analysis. Jupyter notebooks can cover both analysis and ops/doing very well.

I have something that does the jupyter notebook thing quite well (currently not stored in this
repository) but I need to integrate it with the database stuff in here

** Pushing to AdWords
Plan A is to implement a job queue which the middle layer (magic business logic) can
push to. Then workers will push the changes to the API.

This raises the question of why the magic middle layer can't just push to AdWords itself?

I don't have a great answer for this - it is certainly possible to push directly to the API.

Using a job queue has the following advantages:

  1. The AdWords API is quite complicated (SOAP etc.) and there aren't client libraries
    for a lot of things. By opting only to use a restricted set of common API operations
    we can present a simpler interface in a language agnostic way by using a job queue
    as an intermediary
  2. All changes can be logged "for free". i.e. the workers log the changes so the
    magic business logic doesn't have to worry about this.
  3. Batch jobs can be executed in the correct order (e.g. to create a new keyword in
    a new campaign you must first create the campaign and then the ad group)

The key weasel words here are "restricted set of common API operations". These need to
be defined.

| Keywords | Ads | Ad Groups | Campaigns | Shopping |
| Create | Create | Create | Create | Maybe one day |
| Pause | Pause | Pause | Pause | |
| Change bid | | Change bid | Change budget | |
| Change dest url | | Rename | Rename | |

The dependencies between these operations imples the following structure for an
update (expressed here in JSON):

{ accountId: "XXX-XXX-XXXX",
campaignChanges: [
{campaignId: XXXXXXXXXXXX,
newBudget: XXXXXXX,
newName: "foo"
{campaignName: "bar",
budget: XXXXXX
adgroupChanges: [
{campaignId: XXXXXXXXXX,
newBid: XXXX,
newName: "foo - bar"
{campaignName: "bar",
name: "bar - quux",
{campaignId: XXXXXXXXX,
name: "bar - baz",
// and so on


Existing entities are referred to by id. New entities are referred to by name.

Docker Pull Command
Source Repository