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 richard.fergie@gmail.com 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 ~/secrets.sh. This file contains passwords and other other secret information the will be needed when the application runs. See secrets-example.sh for details.
3. Run deploy-all.sh 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/run.sh 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-cron.sh daily. This updates the database with new data each day. My cronjob looks like this:

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

   Be aware, if you copy and paste this; Github may format the quotation marks in a way that causes it to break

Still to do

Short term

• [X] Fix keyword performance report bug - not downloading/inserting sometimes. *This isn’t actually a problem. Keyword performance is downloaded in the Text Ad Report to see kwd/ad performance
• [-] 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,
      adGroupId: XXXXXXXXXX,
      newBid: XXXX,
      newName: "foo - bar"
     },
     {campaignName: "bar",
      name: "bar - quux",
      bid: XXXXXXXX
     },
     {campaignId: XXXXXXXXX,
      name: "bar - baz",
      bid: XXXXXX
     }
   ]
   // and so on
   }

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