Grow Your Online Business with Google Spreadsheets and Google Analytics Integration

9 min readApr 7, 2019

A nifty Google Analytics growth hack, that saves you time and helps you make better data-driven decisions…

My favorite Google Drive product is Google Spreadsheets with all the integration features and query richness. Most of the people I know are using Google Spreadsheets for tracking simple tasks, well, it’s capable of doing a lot more.

I think it’s too late to say “all data will move to the cloud” since it already has. Now, for SMEs and startups around the world, the primary challenge is understanding data, failing fast and cheap, making data-driven decisions.

This content piece is brought to you by Crossover, we’re hiring for Digital Marketing Managers ($100K/Year) and VPs of Digital Marketing ($200K/Year). If you enjoy rigorous testing on Digital Marketing and you are looking for a remote job opportunity, feel free to apply!

Part 1: Connecting Google Analytics API Using Google Spreadsheets

Creating a Sample Sheet

It all starts with creating a sample sheet, once you’re done, you need to go to Add-ons → Get add-ons as shown below, find Google Analytics official add-on and install for your Google Drive account.

Installing Google Analytics Add-on for Google Spreadsheets

Now we’re ready to communicate with a Google Analytics account, assuming you already have one, if you don’t, follow instructions here to set up a Google Analytics account real quick.

Creating Your First Report

All you need is to create your first report using the menu item as shown below, it’s just for creating a simple framework, once we have our framework generated by the add-on, we’ll go deep and play with the data a little more.

Creating the first Google Analytics report

As you can see here, there’s a simple report generator, if you don’t know which exact data-set you want to use in your report, this can be a little bit confusing, so we’ll just ahead and click “Create Report” button to create an empty report framework, we’ll populate data requests later.

First, you need to make sure report engine is using correct Google Analytics Account, Property and View. This is where your report data will come from. If you have multiple Google Analytics accounts, Properties or Views, you’ll have a chance to get data later by using Account, Property, and View IDs.

There are three critical data sets you should learn more about on Google Analytics for each report.

Metrics: What exactly do you want to measure? (Unique users, Pageviews, Minutes spent, Goal completions, Goal value, etc)

Dimensions: How do you want to group your data? (Pages, Hours, weeks, months, days, source, medium, campaign codes, etc)

Unlike segments, you’re free to use multiple metrics or dimensions by separating them with a comma. If you’re using a segment, you can only use one so you need to configure your segment properly.

Segments: This has the potential for reducing a lot of pain, instead of configuring whole report filtering here, you can go ahead and create unique Segments on Google Analytics. For example, you want to learn more about users who read your blog posts. You can create a simple Segment by using Conditions and/or Sequences on Google Analytics and limit your report to that specific segment. Here’s some more information on how to create your segments on Google Analytics.

Locating segments and creating the first segment for Google Analytics

Now we know more about Metrics, Dimensions, and Segments. Your empty report framework should look like this:

Blank Google Analytics report config sheet

Another critical aspect of your report is its timeframe, which master timeframe are you looking for? The sample report is getting 29 days of data starting from 30 days ago until yesterday (including yesterday’s data). You can go ahead and use custom date format here if you want.

Most frequently used tool while creating your reports will be Query Explorer for Google Analytics API. Make sure you bookmark this.

I’d like to share most frequently used query variables in my reports, you can discover a lot more by using Query Explorer as I mentioned. Every business has its own needs, your report should be shaped by your business needs, Publishing, E-Commerce, Lead Generation, every single function should focus on tracking what matters more.

Metrics

ga:users → Number of unique users
ga:newUsers → Number of new unique users. Google is tracking users using browser cookies. New user represents the number of unique users just get installed a browser cookie on their computer. A single user visiting your website from 2 different devices will be identified as 2 new users.
ga:bounceRate → Ratio of users who landed on your website but didn’t take any other action. It’s safe to assume this group is not interested in your content. Lower bounce rate means more intent.
ga:avgSessionDuration → For each page visit, what is the average time users are spending on your site, you can combine this metric with ga:pagePath dimension and understand the quality of your content for your target audience.
ga:pageviews → I believe pageviews are not as meaningful as it was before since there’s a lot of single-page applications after the rise of front-end javascript frameworks. You can still track it though.

Goal metrics are GoalID based, you need to create custom Goals on Google Analytics first in order to track these metrics. Here’s how to do it.

ga:goal[ID]Starts → Goals are basically a single step or a set of steps defined towards an expected action. goalStarts is the number of sessions started your expected journey.
ga:goal[ID]Completions → Pretty self-explanatory, goalCompletions is the number of sessions completed your expected journey.
ga:goal[ID]Value → You can assign a dollar value for your goals, ideal for selling products and services online.
ga:goal[ID]ConversionRate → Your goalCompletions divided into your goal starts. Conversion rate.

Dimensions

ga:campaign → Are you familiar with UTM tags? If not, you must learn more here, it’s a global URL variable. Assuming you’re driving traffic to your website, you can define specific campaigns here. Example: ValentinesDay2019
ga:source → Another UTM tag, where are these users coming from?
ga:medium → What type of content/ad-type are your users coming through?
ga:pagePath → Which web page path are they visiting? Remember, this will not produce full URLs, if users are visiting http://sinanata.com/bookshelf/ your report will group this data as /bookshelf/
ga:pageTitle → Whatever is inside <title> tag in your web page.
ga:week → Grouped by weeks, US format. Sunday to Saturday
ga:isoWeek → Grouped by weeks, ISO format. Monday to Sunday
ga:nthWeek → This is strictly limited with your report timeframe, starting from latest date, going backward, the format is 0,1,2,3,4…

Let’s create our first report based on what we’ve learned so far. I populated my report with some sample variables.

Google Analytics report configuration filled with a sample report data

I used a built-in segment of Google Analytics limiting my report to Android users. gaid::-16 segment represents that.

Once you’re done with configuring your report using variables, all you need to do is run your report as shown below:

Running the first Google Analytics report on Google Spreadsheets

Remember, you can always schedule a report for any frequency you need but first, let’s make sure our report is producing the expected outcome. So this is how your report should look like:

Sorting Your Report Data

Since we just created our first report, you’ll realize our report is randomly generated with no order, assuming we’re interested in most popular pages, we can go ahead and use sorting in our reports. You can use any metric with or without a minus in the beginning. You can use multiple metrics for sorting. Example:

-ga:users (descending)
ga:users (ascending)

So let’s add a -ga:users sorting variable and re-run our report.

Sorting/Ordering Google Analytics report data

Here’s how our data looks like after sorting, / pages represents the index page of your domain. Example: http://sinanata.com

Sorted/Ordered Google Analytics Sample Report

All the rate metrics comes in raw format, turning these columns into percentage format can give you a better understanding of data. Select your column and turn cell formatting into percentages:

Formatting percentages on Google Spreadsheets

Part 2: Performance Analysis - Last Week vs Trailing 4 Weeks

I’m a big fan of analyzing last weeks metric performance vs trailing 4 weeks for keeping an eye on improvements. Now we’re going to use our report data for creating a simple analysis.

Important note: In order to avoid losing your analysis data, you should always create new sheets while doing additional analysis based on your auto-generated Google Analytics data.

Since both the “Report Configuration” and “Sample Report” sheets created by our add-on, I’m using Sheet1 for my analysis.

Running an additional analysis on new sheets

I built my simple analysis structure like this on Sheet1. Now, let’s use data from the Sample Reports sheet populated with our Google Analytics data.

Sample Last Week vs Trailing 4 Weeks Comparison

I’m interested in doing this analysis for most important pages for my business goals, so I’m going to limit it down to my for-candidates page and about-us page as a sample. You can go ahead and do the same for 100 pages if you need.

Here’s how our data looks like:

The data is telling me we are getting less user volume visiting our for-candidates page and about-us page. On both users and new users metrics. Also telling me traffic for our about-us page is more likely to engage with our content instead of just leaving. Now I can start applying improvements on my pages and track metric performance.

Last Week

For each pages row, LAST WEEK’s data, I built this simple query for reading my Google Analytics data:

Last Week’s Data Query

=query(‘Sample Report’!A16:E,”select sum(C),sum(D),avg(E) where A contains ‘“&G4&”’ and B=0 label sum(C) ‘’, sum(D) ‘’, avg(E) ‘’ “)

Trailing 4 Weeks

For each pages row, TRAILING 4 WEEKS’ data, I built this simple query for reading my Google Analytics data:

Trailing 4 Weeks’ Data Query

=query(‘Sample Report’!A16:E,”select sum(C)/4,sum(D)/4,avg(E) where A contains ‘“&G4&”’ and (B=1 or B=2 or B=3 or B=4) label sum(C)/4 ‘’, sum(D)/4 ‘’, avg(E) ‘’ “)

Scheduling Reports

Now, you can go ahead and include more metrics and dimensions into the report, perform deep-dives for understanding customer/user behavior on your website and optimize.

Scheduling Google Analytics reports on Google Spreadsheets

If you think this was a good read… feel free to

Ask your questions in the comments below - I’ll answer as soon as I can
Connect with me on Linkedin
Share this post with your friends who you think may need it!

This content piece is brought to you by Crossover, we’re hiring Digital Marketing Managers ($100K/Year) and VPs of Digital Marketing ($200K/Year). If you enjoy rigorous testing on Digital Marketing and you are looking for a remote job opportunity, feel free to apply!