Marketing Automation – a Journey with Google Sheets and Supermetrics – Part III

Nico Brooks

December 7, 2016

Marketing Automation – a Journey with Google Sheets and Supermetrics – Part III


Part III – Automation Everywhere

Two Octobers has become a hotbed of automation. Around the office, people are creating tools to monitor shopping campaigns, forward emails based on client names, and manage ad copy programmatically. The complete list is far too long to include here, and I’m sure there are some I don’t know about. This wasn’t true a year ago.

How did we get here?

In my previous posts, I described how I had failed in my attempt to automate one of the most important things we do: manage advertising bids and budgets. Out of that failure, I sought to improve my knowledge and create a framework for marketing automation at Two Octobers. There’s a lot of “I” in those posts. Some good things started to happen when it became more about “we”.

The framework I ended up with consists of:

  • Google Sheets – and the built-in support for automation and integration using Google Apps Script.
  • Supermetrics – an add-in for Google Sheets that automates fetching of data from Google, Bing, Facebook and other sources.
  • The following conventions for Sheet design:
    • Data from each source lives in it’s own sheet, e.g. there is a sheet for Google Analytics, a sheet for Facebook, etc.
    • Tabs in each data sheet are named YYYY-MM. In almost all cases, data is captured by month.
    • Each sheet represents a single set of data, with a header in row one and data in a grid below
  • A library of functions I developed for working with data laid out according to those conventions. I’m in the process of working on V3 of this library, so more details on that in a future post.

Getting people on the team up to speed with Supermetrics was easy. The UI is pretty intuitive and the payoff was immediate. Most campaign managers had at least a few accounts that required manual custom reporting. Supermetrics offered a way to automate what they were doing already.

To get people familiar with Sheets automation using Apps Script, I decided to offer a workshop, open to anyone in the organization. 8 people signed up, which was about a third of the team. We met once per week for 14 weeks. In the first part of the workshop we went through the Codeacademy: JavaScript course. In the second part we went through the ebook Google Sheets programming with Google Apps Script. In the classes, I explained concepts using examples relevant to our work, and answered questions. Time permitting, I also had people walk through their homework answers. At the end of the 14 weeks, everyone came up with their own work-related project, which was to continue after the workshop. The workshop produced:

  • Three people with reasonable proficiency in Apps Script – e.g. they can write their own scripts from scratch to manipulate sheet data.
  • Five people with some familiarity with Apps Script – e.g. they are comfortable looking at code and tweaking someone else’s script to get it to do what they want.
  • I, of course, improved my skills a lot by teaching the class.
The Google Sheets programming workshop
The Google Sheets programming workshop

Since the workshop, pretty much everyone in the org sits next to someone who can automate tasks in Sheets. And most people on the team are using Supermetrics to automate some aspect of reporting or monitoring. These are very useful skills for what we do, but the greater benefit has come from an organizational increase in computational thinking. The term “computational thinking” refers to the principle that understanding how computing processes work opens your mind to systematic and algorithmic solutions.

Computational methods and models give us the courage to solve problems and design systems that no one of us would be capable of tackling alone. Computational thinking confronts the riddle of machine intelligence: What can humans do better than computers? and What can computers do better than humans?
Jeannette M. Wing, Computational Thinking

Anyone who works at a digital agency is likely to have at least several spreadsheets open at any given time. We also spend our days categorizing, sorting, optimizing, analyzing, and generally looking for opportunities to apply what we learned over there to a problem over here. Most of us don’t come from engineering backgrounds, so we tend to approach these tasks one by one, using a limited set of tools. I’d be willing to bet that most digital marketers cut-and-paste more than they do any other one thing.

This is starting to change at Two Octobers. Several times recently, I have been surprised by a coworker who built a tool to solve a particular business problem. The fact that they did it is not what’s surprising. What is surprising (and exciting) is that it happened without my involvement or knowledge. Computational thinking is going on everywhere around me, all the time.  The result of that is that we are getting better work done, more efficiently. One of Two October’s core principles is, “Get ideas from humans; get efficiency from tools.” We are freeing up more and more time to focus on ideas.


Part I: Failure and Recovery
Part II: My First Project

Nico Brooks oversaw the development of the first commercial SEM management toolset, Go Toast, way back in the year 2000. Since then, he has worked with a variety of agencies and publishers on tools and processes to improve marketing efficiency and effectiveness. At Two Octobers, he gets to collaborate with a bunch of really smart people and play with code.