How to Build Your Own Powerful Marketing Performance Tools

By losing our biggest family golf championship, I stumbled upon an insanely powerful, free platform that lets you build your own marketing performance tools. 

The platform I'm referring to is the combination of Google Sheets and Google Apps Script. With it, I’ve managed to:

• Connect sheets to data sources like Google Analytics through APIs.
• Strategically build large data sets from multiple sources.
• Marry marketing activities to performance metrics.
• Build small algorithms to sort through and pull insights out of thousands of data points.
• Allow multiple Google Sheets to communicate with one another.
• And much more.

If you’ve got some coding background or are interested in learning to code, I’ve tried to incorporate everything you need to get started building your own marketing tools, including:

• An introduction to Google’s Apps Script platform.
• Descriptions of its capabilities as they relate to marketing.
• A step-by-step guide on how to get started.

And if you’re not interested in building, I’ve also included links to some marketing-related add-ons that you can integrate into your Google Sheets. 

Quick, the backstory ... (skip ahead)

In our family, golf is a way of life, and winners of our annual championship are revered and honored above all others at the regular potlucks. Posters are made. Shirts printed. Trophies engraved. The standard stuff.

Well, in 2010, I had miraculously put myself into a position to tie and force a sudden death playoff. Unfortunately, my brother managed to pull two shots out of his [LONG SERIES OF EXPLITIVES] to win by one stroke.

What I realized once I got home was that I added my own score wrong. We should have tied. For the next 74 hours, I aggressively wept in this position:

After pulling it together, I started looking for ways to automate the addition process, thereby removing any chance of messing up 3 + 7 again. This eventually led me to Google Sheets, which allows multiple people to simultaneously add their golf scores to the same sheet (scorecard), and it’s free. 

But, I still needed a way to account for each player’s golf handicap, which led me to Google Apps Script. It was the answer to my arithmetic nightmare. With Apps Script, I could give my spreadsheet scorecard the brains I was lacking. 

I’ve been playing around with Google Apps Script now for four years, and it has redefined many aspects of how PR 20/20 operates, including how we evaluate marketing campaign performance and even manage the agency.

What is Google Apps Scripts?

From Google: “Google Apps Script is a scripting language based on JavaScript that lets you do new and cool things with Google Apps like Docs, Sheets, and Forms. There's nothing to install — we give you a code editor right in your browser, and your scripts run on Google's servers.”

Let’s break down Google’s definition a bit and explain why you don’t need to be a developer to use it:

• “…Is a scripting language based on JavaScript”: If you understand the basics of JavaScript, you’ll be able to wield an Apps Script. My understanding of JavaScript includes what I learned from a Treehouse training course, and what I’ve picked up through trial and error. Google’s thorough reference documentation makes the learning curve very manageable.

  For those with no JavaScript experience, in a previous post I detail how to get started learning to code. I promise, it’s a skill worth knowing for a number of reasons, not the least of which are mentioned in the next bullet. 

• “…lets you do new and cool things with Google Apps”: This is a pretty decent understatement. Google Apps Script lets you:

  • Connect your sheets to data sources like Google Analytics through APIs.
  • Strategically build large data sets from multiple sources.
  • Marry marketing activities to performance metrics.
  • Build small algorithms to sort through and pull insights out of thousands of data points.
  • Allow multiple Google Sheets to communicate with one another.
  • And much more.

• “There's nothing to install — we give you a code editor right in your browser, and your scripts run on Google's servers.”  This was the most exciting bit for me. I found the most trying part of starting to learn to code was not knowing what programs to write my code in, or even what these programs are called (FYI, it's a compiler). I also struggled to put the JavaScript I was learning into use without relying on two to three other languages. For example, when using JavaScript to make a website interactive, you also need to know HTML, CSS and JQuery, and how each interacts with the other.

  Google Apps Scripts simplifies all of that. Nothing to install, no compiler to keep updated, and you only need to learn one coding language.

  

Don’t let Google’s description of Apps Scripts intimidate you. Once you get into it, it is a pretty straightforward way to start practicing and growing your coding skills, and create useful marketing tools in the process.

Speaking from experience, here are some of the marketing tools that are possible to build within Google sheets:

• Marketing scorecard.
• Completely automated blog report card.
• Lead database evaluator.
• Google Analytics traffic data analyzer.

If you’re interested, consider subscribing to our blog as the plan is to draft some how-tos around building the above tools.

Not Interested In Building Tools?

If you don’t have the time or interest in building your own marketing performance tools, you’re in luck. Google Sheets has an Add-Ons gallery with dozens of prebuilt programs you can add to any Sheet. 

Tools relevant to the marketing world include:

• Google Analytics: Access all Google Analytics data in a spreadsheet.
• SurveyMonkey: Import SurveyMonkey data directly into a spreadsheet.
• TimeSheets: TimeSheet utilizes your Google Calendar as a time recording tool. You simply create entries with #tags in your calendar and TimeSheet will create full reports about project usage, resource consumption and project status for you.
• Twitter Curator: Helps you find and collect tweets right from inside a Google Spreadsheet.
• LeadIQ Lead Research: LeadIQ automagically researches sales leads, so you can prioritize and focus on better-qualified leads.

Quick note: I’ve only personally used the Google Analytics add-on. It used to be a script you copied and pasted into your own Google Sheets, but Google adopted it, polished it up, updated it, and launched it as an Add-On. It's a very useful tool for pulling a series of metrics into a Google Sheet for later analysis. 

How to Get Started Building Tools with Sheets and Apps Script

Below you’ll find detailed instructions on how to get started with Google Sheets and Google Apps Scripts. I’ll walk you through how to access the Script Editor, and then write a small program that pulls in data from a spreadsheet, processes it, and publishes it back to the spreadsheet.

How to Access the Script Editor

1. Open a Google Sheet.
2. Click on “Tools” from the main menu.
3. Click on “Script Editor.”

Congratulations, here’s your code editor. Better yet, the start of your first program function is already waiting for you.

4. Click on “Untitled” and give your script a name.

Script Editor Menu Items

Instead of explaining each menu item, which could take a while, right now I’m only going to call attention to the three you’ll use most often.

Every program you’ll create will need to be saved and tested:

Saves the most current version of your script.

Select the function you want to run or test (you may eventually end up with dozens of functions).

Run the selected function.

As soon as you click the play button, you’ll see a yellow bar similar to the one below. This will indicate that the script is running. When done, the bar will read, “Finished Script.”

Building Your First Google Apps Script Program

OK, now that that editor stuff is out of the way, create a new spreadsheet and add any number to cell A1. We’ll create a simple program that multiplies your number by 5, and then prints the answer in cell B2.

While this program may sound rather rudimentary, I’m using it more as a demonstration of how to get values from a spreadsheet into your Apps Script, and then send something back to the spreadsheet.

Every app script I’ve ever created includes all of the following elements.

Here’s the full program for reference. I’ll break down each line below. I’ve linked the Google methods (functions that can be performed on an object) used to Google’s own definitions for easy reference. 

• Line 1: “function myFunction( )” declares the function. Every part of your Apps Script program must be contained within a function.
• Line 2: “getActiveSpreadsheet( )” creates an object that contains everything about that specific spreadsheet, including all sheets, rows, columns, values, formatting, etc., and assigns it all to the variable “spreadsheet”.
• Line 3: “getSheetByName(“Sheet1”)” takes a look inside the “spreadsheet” variable for a tab named “Sheet1,” and stores all its details in the “sheet” variable. You can also find a specific sheet with the method “.getActiveSheet( ),” which will pull in the tab that is visible on the user’s screen. For most programs, I prefer the first option because you know exactly which tab will be saved to the variable.
• Line 4: “sheet.getRange(1,1,1,1)” looks into the “sheet” variable and grabs everything associated with a specific range of cells, and saves it to the variable “cell.” Each of the numbers in the parenthesis are range coordinates:
  • Position 1: First row in the range. If I wanted my range to start in row 8, my sheet.getRange parenthesis would include (8,1,1,1).
  • Position 2: First column in the range. Same as above, you need to assign a start column for your range. If I wanted to start it in column 3, my parenthesis would read (1,3,1,1).
  • Position 3: The number of rows to include in the range. In other words, from the start row, how many additional rows do you want included in the range. This number does not represent a specific row number. If I wanted my range to include 4 total rows, my parenthesis would read (1,1,4,1).
  • Position 4: The number of columns my range should include. Similar to Position 3, if my range needs to include 5 total columns, my parenthesis would read (1,1,1,5).

Only include the specific range of cells needed, so as not to chew up too much memory, which when compounded over your entire program can waste valuable milliseconds. 

• Line 5: “cell.getValue( )” actually pulls in the number (or whatever value appears in the cell) and saves it to a variable. It can also pull in a string of letters (aka a string), or a combination of the two.
• Line 6: You can then use the “value” variable in any sort of equation. In the eyes of the program, the variable equals whatever you put in A1. In this case, I multiplied the cell value of 4 by 5, and saved the result to the variable “newValue.”
• Line 7: “getRange(1,2,1,1)” similar to getting a value from a sheet, to print a value back to the sheet, you first need to pull in a cell’s coordinates.
• Line 8: “resultCell.setValue(newValue)” takes the value from the equation on line 6, and then sets the cell associated with “resultCell” equal to it, in this case B2. 

One quick wrinkle to throw in here: If your getRange includes more than 1 row and/or more than 1 column, the “.getValue( )” and “.setValue( )” methods become “.getValues( )” and “.setValues( )” respectively. Reason being, you’re pulling in or printing out more than one value, and the Script Editor needs to account for that.

The most important thing to keep in mind when developing your programs is: you need to tell the program exactly which part of the spreadsheet you want to interact with, and that starts with defining things at the broadest level (spreadsheet) first and working down through each level to a specific cell or group of cells. Miss a step, and your computer will catch fire and melt. 

One other quick note: the program above is written with each specific code instruction on its own line for clarity. This isn’t the best way to write code. Instead, you should try to consolidate instructions where possible.

In this case, you can attach Google methods to one another in one line to consolidate space. Below is an example of how to shorten this program. 

The rule of thumb is: If you think you’ll need some part of your spreadsheet (i.e. sheet, row, cell) elsewhere in your program, save it to its own variable so you can easily reference it later. For example, I knew I’d need the “sheet” again at the end to print the value, so I saved it to its own variable.

For additional background, below is Google’s full reference documentation on each level of a spreadsheet. On each page, you’ll find all the different actions you can take with Apps Script. I haven’t used about 75% of this stuff, but the more you know about what’s possible, the easier your programs become to write.

• Spreadsheets
• Sheets
• Ranges

Stay Tuned

If this was valuable and you’re interested in learning more, be sure to subscribe to our blog below. Over the next several months, I’ll be walking you through how to build a number of cool marketing performance tools.