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

Nico Brooks

September 22, 2016

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

fail_recover_header

Part I – Failure and recovery

This post is aimed at people who work in marketing and advertising, and are interested in automating tasks to improve quality and efficiency. It describes my recent journey towards achieving these goals. For a little context on my philosophy of automation, check out this post from a few years back. In a nutshell, it explains my preference for the flexibility of task-focused tools versus marketing automation platforms that dictate methodologies and workflows.

Failure

A little more than a year ago, I was in a difficult place. I’d hired a contract developer to build us a system to manage campaign bids and budgets. The project had not gone as I had hoped. We were about four months in, and I needed to decide whether it was time to pull the plug. Doing so meant throwing about ten thousand dollars of investment down the drain. Not doing so meant more money, and the goal posts seemed like they kept getting further away. As an organization, we’d already spent a fair bit of time and money evaluating marketing automation platforms, and this project was my baby. I have a long background in technology for agencies, so I had confidently told my business partner and the team that I had this handled.

I pulled the plug. I hated to do it. I was embarrassed, and we were struggling with an Excel-based system that desperately needed replacing. But I knew from previous projects to listen to the warning signs. Every feature took at least two times as long what the developer expected. Every test resulted in a backlog of fixes that reached all the way down to database design. And the developer was competent. Replacing him wouldn’t help. I had set the course, and it was a bad one. To make matters worse, this was just meant to be a first step. I was looking to identify and start building a technology stack from which we could automate and streamline many of the things we do.

I had to keep reminding myself that failure is necessary on the path to success. Being like Mike, as it were.

Starting Over

I couldn’t bear failing again, so I decided to fix the most obvious problem: me. I didn’t know enough about software development to make good choices and ask the right questions. I thought I did, but mostly I’d just spent a lot of time around people who knew more than me, and made it look easy. With a little help from a few of those people, I started over. They advised me on how to approach the problem, and I set about educating myself.

For the web development part, I’d narrowed it down to Ruby on Rails and JavaScript (with an eye towards Google Sheets/Docs), so I set about learning both. My goal was to get to the point where I could build something useful with either, then make a decision on which I preferred. I tried courses on both Codeacademy and Codeschool, and liked Codeschool more, so I went through their tracks in both languages. I stopped taking work home with me, and just focused on this task nearly every night and weekend. The courses are interactive and pretty fun, so that part was good. But it sometimes felt like I was moving the opposite direction from my goal. I desperately needed to get something done, and instead of making progress, I was back in school.

Fairly early into my studies, I decided to focus on JavaScript. I was very familiar with VBA macros in Office, and Google has provided similar capabilities in their office tools in the form of Google Apps Script (GAS), which is JavaScript with added hooks into Google Apps/Docs. I figured it would be easier for me to understand a familiar paradigm, and about 90% of what we do at Two Octobers involves spreadsheets. Below is a list of all of the courses I took and resources I found as part of this purely educational phase of my journey. I did not know how long this phase would take when I started, but after about three months, I was ready to start building something real.

Courses and resources

  • Codeacademy
    • JavaScript – this is a good course, it just doesn’t get into as much depth as the Codeschool courses
  • Codeschool – their courses can be a bit goofy, but I can attest to the results. I really felt like I understood JavaScript by the time I was done.
    • JavaScript Road Trip Parts 1-3
    • JavaScript Best Practices
    • Try jQuery – I did not end up needing this for my first project, but it was super-helpful for setting up advanced Google Analytics event tracking via Google Tag Manager
    • jQuery: The Return Flight – ditto
    • Anatomy of Backbone.js – I did not end up doing anything with Backbone, but learning it was helpful for seeing how JavaScript can be extended
    • Discover DevTools – Google Chrome Dev Tools are incredibly powerful, and free with Chrome. Dev Tools also became my new best friend when troubleshooting Google Analytics and other tracking and conversion tags.
  • Google Sheets Programming with Google Apps Script – this book has proved invaluable for learning the ins and outs of programming with Google Sheets.

Whenever I was stuck on a concept, I would also find blog posts or Stackexchange threads to see it explained from different points of view. In some ways, this became the core of my education. I came to see the lessons I breezed through as just necessary steps to get to the next blocking point, when my inability to understand something would force me out into broader conversations on core concepts, common design patterns and performance optimizations.

marketing-automation

Part II: My First Project
Part III: Automation Everywhere

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.