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. '

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.

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

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.