MGWM

How To Use Google Colab And Google Sheets For Marketing Reporting

Table of Contents

We've all been there. It's the end of the quarter. A steady stream of client meetings and project reviews is trickling into your calendar. You slowly realize that the coming weeks are going to leave an Excel-style grid burnt on to your vision. 

It's reporting season. And while you’re pulling, cleaning, and visualizing the same, old data, you’re likely thinking, “I do this all the time. How does it still take this long?”

It's tough but true. Most digital marketers don't spend a lot of time doing analysis. We spend the bulk of our time preparing data, which means we have less time to analyze it and help our customers.  

Moreover, when we do get around to the analyses, let’s be honest, we hammer out a collection of averages & time series. “Performance Year Over Year,” “Revenue By Month,” Top Campaigns by Revenue,” and, of course, “Traffic Year Over Year.” It's not intensive data science.

Those metrics are useful, don't get me wrong. They paint a great picture of performance. They just don't always go that deep. 

Because, to our chagrin, most marketers aren’t data scientists. We aren't doing complex statistical analysis. And most of our data comes prepackaged and relatively clean. Still, every quarter we spend hours on reporting that we could do with a single spreadsheet and a few lines of code.

In the following sections, we’ll show you how you can streamline reporting and skip to the fun part: analysis. We'll walk you through how to connect Google Ads, Google Sheets, and Google Colab. And you'll come away with the code you need to get started exploring your own data.

Before we start, we should mention that Google Colab is a programming environment. You will need some programming experience to use and access the data from google sheets. That said, the process itself is clear and simple. Most people should be able to understand the impacts and the benefits of this new approach. But at the very least, we hope you read this and learn about a few new tools to help level-up your reporting. 

Join Google Analytics and Google Sheets

That campaign data you’re pulling from Google Ads? That analytics acquisition report you’re downloading...again? Stop exporting it manually. Get all that information right in Google Sheets without any extra work.

Luckily we can do this. There are two Google Sheets add-ons we’re going to use. One is for Google Ads. The other is for Google Analytics. They'll help us get everything we need populated into spreadsheets. '

A snapshot of google sheets' add-ons tab.

To access these, follow these steps:

  1. Click into the “Add-ons” tab in a spreadsheet
  2. Click “Get add-ons”
  3. Search for Google Ads and Google Analytics. They should be the top listing. 
  4. Download them both.

 

Once they're installed, follow the instructions to connect your accounts. When you’re all set up, you’ll be able to export data from both platforms with almost zero work.

To create your reports, you can add variables from Google Ads and Analytics into saved templates. To do that:

  1. Click on the “Create new report” tab
  2. Search for the variable or metric names.

A snapshot of the google analytics add-on and its features

This will bring up a sidebar, which lets you select the target property and all the data you want to in your chart.

The google analytics extension new report interface.

Once you’ve created the report, you’ll be able to run it from the drop-down. Running the report will bring up two new spreadsheets. The first contains all the information about the pull, and the second is your data.

With these extensions, you can even schedule data pulls from Google Analytics. That can help generate those reports you find yourself creating week after week. As of writing, Google Ads doesn't have this feature. 

So there it is in a nutshell. Now you can join all your analytics data into a spreadsheet.

But what if there were more you could do? Even with your data in one workbook, you still need to churn-out to do the actual mental grunt work of making a report. 

Don’t worry, we’ve got something for that.

Fair warning: this next bit does use a little Python. We assume you have at least a basic understanding of programming. If not, check out some of these free courses & modules to learn Python.

Pulling Google Sheets Data Into Google Colab

We’re going to conduct this next part in a development environment Google has created.  It's called Google Colab

Google Colab lets you share and edit scripts with other people in-and-out of your company. It also contains useful Google-specific libraries, which will come in handy for what we’re about to do. 

Our goal is to make your reporting easier. To do that, we want to limit the actual amount of work you need to do. That’s why we’re going to join our new spreadsheet to Google Colab, using Python. That connection opens the door to streamlined analyses & visualizations.

To learn more about how to connect Google Colab and Google Sheets, check out this awesome walk-through.

For our purposes, this is how we're going to make the connection. 

Start a new Colab project by going to file > new notebook. This will open a new project in a separate tab. Feel free to import whatever libraries or APIs you intend to use for your report. The most important API we will be using is gspread

Among other things, gspread allows us to access spreadsheets from a URL. This, along with GoogleCredentials, will give Google Colab access to a spreadsheet. It lets the program pull data into our environment without any new uploads or downloads.

Here is the code for that:

from google.colab import auth
auth.authenticate_user()
import gspread
from oauth2client.client import GoogleCredentials
gc = gspread.authorize(GoogleCredentials.get_application_default())
wb = gc.open_by_url('YOUR SPREADSHEET URL') sheet = wb.worksheet('YOUR DATA SHEET') data = sheet.get_all_values() df_csv = pd.DataFrame(data) df_csv.to_csv('df.csv') dfTest = pd.read_csv('df.csv',                      skiprows=14) df = pd.DataFrame(dfTest) df.columns = df.iloc[0] df = df.iloc[1:] 

To break this down, we first give gspread access to the spreadsheet. Then we store your spreadsheet as a DataFrame within the Colab environment. It’s important to say that the data from sheets is not in the best format right away. That’s why we have to skip rows, cast to CSV, and re-cast our data to a dataframe. Luckily, the number of extra top rows is consistent, like we see with Google Ads and Google Analytics CSVs. In this case, it’s usually right around 14 or 15. 

As an added note, gspread pulls all the values in as objects, so be mindful of that as you begin to work with the data. Depending on the variables you imported, you will need to change the data types. Still, it’s no harder to clean and format than an excel sheet. 

Once the code has run, and you’ve re-formatted the data you need, you can manipulate the data frame any way you like. With the time you saved, you can look for new trends, create new visuals, or do some more exploratory analysis. And with scheduled data pulls, you can re-run your code and get even more up-to-date charts & tables.

Conclusion

Data analysis takes time. And we spend a lot of that time downloading, wrangling, and cleaning data. That's important, but it can get repetitive. 

With this process, we can clean, wrangle, and visualize the important data in seconds. This makes us a little more efficient and makes for less leg work. But above all, it leaves more time for the fun part: deeper, meaningful analysis.

It takes some code, but we can use this process to cut out the repetition. And if you’re comfortable around Python, the benefits are clear. But even if you’re not, thinking about how to save time and increase efficiency this reporting season is always a good idea. 

To stay up-to-date on the latest search marketing news and read more reporting tips, be sure to sign up for our SearchLite newsletter.

John Smith

John Smith

John is a Paid Media Manager at Uproer, where he works to build paid search strategies for clients in the e-commerce and SaaS spaces. He's drawn to the ideas, channels, tactics, and emerging trends that tackle big issues in marketing. And he approaches SEM with a focus on data privacy, incrementality, and social impact. When he's not knee-deep in a spreadsheet, John volunteers with local climate organizations and helps spread their message through search.

See More Insights

Don't Sleep on Category Page Content: 6 Tips to Drive Results

Category pages are some of the most important pages on an E-commerce website. They organize products into comprehensive grids, act as shopping hubs for site visitors, and can play a key role in your E-commerce SEO strategy. Don't sleep on category pages, folks. If you've covered your technical bases and

Read More

How a PPC Strategy Refresh Increased Closed Won Revenue By Over 1,000%

Parallax provides industry-leading resource management software to professional service companies. By late 2022, Parallax had been advertising on Google for over a year with little tangible results. Leads were inconsistent, and measurement was uncertain. That’s why they approached Uproer. We conducted a strategy development project to help Parallax turn their

Read More
MGWM

Sr. Manager, SEO & Operations

Dave Sewich

Dave made an accidental foray into digital marketing after graduating from the University of Minnesota Duluth and hasn’t looked back. Having spent the first part of his marketing journey brand-side, he now works with the Uproer team to help clients realize their goals through the lens of search.

When not at work, you’ll find Dave staying active and living a healthy lifestyle, listening to podcasts, and enjoying live music. A Minnesotan born and raised, his favorite sport is hockey and he still finds time to skate once in a while.

Dave’s DiSC style is C. He enjoys getting things done deliberately and systematically without sacrificing speed and efficiency. When it comes to evaluating new ideas and plans, he prefers to take a logical approach, always sprinkling on a bit of healthy skepticism for good measure. At work, Dave’s happiest when he has a chance to dive deep into a single project for hours at a time. He loves contributing to Uproer and being a part of a supportive team but is most productive when working solo.

Founder & CEO

Griffin Roer

Griffin discovered SEO in 2012 during a self-taught web development course and hasn’t looked back. After years of working as an SEO consultant to some of the country’s largest retail and tech brands, Griffin pursued his entrepreneurial calling of starting an agency in May of 2017.

Outside of work, Griffin enjoys going to concerts and spending time with his wife, two kids, and four pets.

Griffin’s DiSC style is D. He’s driven to set and achieve goals quickly, which helps explain why he’s built his career in the fast-paced agency business. Griffin’s most valuable contributions to the workplace include his motivation to make progress, his tendency towards bold action, and his willingness to challenge assumptions.