Create an automated Google Sheets Dashboard - A step-by-by step guide

Krishna Srinivas V

Krishna Srinivas V

Data is the new oil - we’ve heard that over and over again. 

But the fact is, raw data on its own is of little use, much like its metaphorical counterpart. 

For data to be useful, it needs to go through a set of processes such as compiling, manipulating, and consolidating before it can transform into insights. 

How do we do that? 

Thanks to Google Sheets and it's all-powerful, customizable dashboards, data analysis has never been easier.

Just take a look at this sample dashboard for marketers, created using Google Sheets:- 

In a single view, you can see all the relevant numbers in your marketing funnel - your site traffic, which channels bring the most users, how your week-on-week growth is etc.

Rather than sit and pour over hundreds of rows of numbers, dashboards help you cut through the noise and lead you straight to actionable insights in no-time. ( Or even real-time! Learn more.)

Best part? 

You can build such powerful Google Sheets Dashboards tailored specifically to your team’s goals, focusing only on the metrics you want.

Table of Contents:-

  • 4 Questions to Ask Before Creating a Google Sheets Dashboard 
  • Creating an automated Google Sheets Dashboard 
  • 3 Things to do before building the dashboard
  • Building the Google Sheets Dashboard

                     - Adding the Google Analytics tracking code to your website

                     - Installing the Google Analytics add-on for Google Sheets

                     - Outlining the Metrics to track in Google Analytics

  • Building the Google Sheets Dashboard

                    - Metric #1: Website visitors in the last 7 days 

                    - Metric #2: Average duration of a session in the last 7 days 

                    - Metric #3: Website traffic from different sources 

                    - Metric #4: Website visitors week-on (Last week vs. This Week)

  • Fine Tuning the Dashboard

4 Questions to Ask Before Creating your Google Sheets Dashboard 

Before creating your dashboards, you have to ask a few questions which will pave the way for setting up the right kind of dashboard giving you the insights you want.

#1 - Who are you creating the dashboard for? 

When it comes to data analysis, the first step is to get a clear picture on the end user.

This is crucial because even within the same team, the metrics you’ll want to measure differs from role to role - for instance, a content writer might be focused on the numbers around blogs, while a campaign specialist will be keen on the email outreach numbers.

In contrast, the marketing head or the CEO will want a holistic overview of the entire marketing funnel.

So before you even open your Google Sheets to create your dashboard, the first question to ask is, who are you creating it for.

#2 - What is the purpose of your dashboard? 

The next key aspect is understanding is why you are creating this dashboard. 

Or more specifically, what is the single most important objective you want to get out of this dashboard? 

It could be to:

  • Get an overview of your website traffic
  • Understand which is your best performing marketing channel 
  • Where is the most drop-off in your marketing funnel

#3 - What are the metrics you are going to measure? 

Once you’ve set a goal, the next step is to identify the key, relevant metrics which will help you gauge how you stand with respect to your single goal. 

You could even be more specific by quantifying it - “What are the 5 metrics that I am going to measure?” It could even be 3 or even 7 - it doesn’t matter. 

The idea is to narrow down and focus only on the right metrics instead of running after vanity metrics in random fashion, which is a colossal waste of your time and resources.

It could be:

  1. Website visits
  2. Traffic sources by channels
  3. Average session time on your website 
  4. Week-on-Week user growth 

#4 - What are the tools you are going to rely on? 

The last piece of the puzzle is, listing down the tools you would integrate with Google Sheets to pull up the data you  need for your analysis.

For example, to track the above metrics you’d need to export data from Google Analytics. For other advanced or rather very specific metrics, like, say, your newsletter open rates, you might need an email marketing tool like Mailchimp to pull in the information you need.

But for most use-cases, a Google Analytics account and Google Sheets should be more than sufficient.

Now that we’ve discussed all the questions we need to, let’s jump into building our dashboard.

3 Things to do before building the dashboard 

1. Adding the Google Analytics tracking code to your website

To create an automated Google Sheets Dashboard, the first step is to integrate Google Analytics with your website, so you can start tracking the key website metrics.

Set up Google Analytics on your website already? Jump straight to Step #2  .

To do this, you need to install a code snippet from Google Analytics to your website (No developer help required!)

In your Google Analytics account, (You can create one for free, using your existing Gmail address), navigate to Settings -> Admin -> Tracking Info -> Tracking Code


In this page, the tracking snippet can be found under Global Site Tag which you will copy + paste to your website (every page you want to track).

To copy and paste your gtag.js, follow these steps:

  1. Copy the gtag.js shown on the page. 
  2. Replace the GA_TRACKING_ID with your unique tracking ID provided by Google Analytics.

Once you paste this code under the <head> Tag on each page of your site, you will be able to track your website metrics in Google Analytics.

If you have correctly installed the tracking code snippet in your website, your Google Analytics account should look something like this:


2. Installing the Google Analytics add-on for Google Sheets

To get the relevant data into your dashboard, you need to integrate your Google Analytics account to Google Sheets - luckily, Google has the work cut out with an amazing Google Analytics plugin that you can find on the GSuite Marketplace.

To install the plugin, in Google Sheets, click on Add-Ons -> Get Add-ons.

This will take you to the G Suite Marketplace where you can install the Google Analytics add-on.


3. Outlining the Metrics to track in your dashboard

Before jumping into building dashboards, outline the specific metrics you want to track. For this guide, we’ll recreate the sample dashboard from the introduction of this post:

As you can see, it contains four important marketing metrics:

  1. Website visitors in the last 7 days 
  2. Average duration of a session in the last 7 days 
  3. Website traffic from different sources 
  4. Website visitors week-on ( Monday to Sunday every week) 

Building the Google Sheets Dashboard

Prefer watching a video tutorial instead instead of reading the blog? Here you go.

Create a New Report

To get the website metrics (available in your Google Analytics), to your Google Sheets Dashboard, go to Add-ons -> Google Sheets -> Create a New Report

This is the function you’ll keep returning to whenever you want to add  any new website metrics to the dashboard.


Metric #1: Website visitors in the last 7 days 

The first step is to give a name to our report.

Next, we’ll choose the account from which we want to pull the metrics. 

Here, we are going with a personal account created just for this tutorial.

Finally, let’s select the metrics we want to run a report for - since we are looking at last 7 days website visitors, let us select users as our metrics and date as our dimension.

Note: In Google Analytics, website visitors are called Users.

This returns the data for the number of users who have visited our website in the last 7 days ( By default, Google Analytics selects the time period as ‘Last 7 days’. We will later explain how you can change this. 



As you can see, once the report is successfully created, the Google Analytics add-on automatically creates a new sheet with the same name as our report, containing relevant data based on the metrics and dimensions we chose.

Click on the new sheet to view the metrics.



The report has returned with values of users who visited the website in the last 7 days - July 27 to August 2. 

Creating charts using the metrics data

Now that we have our first set of data from Google Analytics, the next step is to transform that into a graphical chart for the dashboard.

To do that, let us first create two separate sheets:

The reason we do this is, everytime we run a report, a new sheet gets created automatically - to create our dashboard, we need to consolidate the data from all these sheets into one master sheet - we’ll call that ‘Dashboard Metrics’ (which will contain only the raw data from the other sheets) and another sheet called ‘Google Sheet Dashboard’ where we will have only the charts, making it easy for us to read and analyze the data.

To avoid confusion, let’s change the name of our first report from ‘Google Sheets Dashboard’ to something more specific - we’ll call it the Last 7 days - website visitors since that is the metric available in the sheet.

When changing the name, make sure to change it in the Report Name cell in the Report Configuration sheet as well as the sheet’s name at the bottom.

Now that we’ve clearly defined which sheet is for what purpose, let us head over to Dashboard Metrics to create our first chart - Last 7 days website visitors.

Here’s a step-by-step guide to convert the raw data into a chart. 

Step 1: Create two new columns in the sheet called Date and Users

Step 2: In the cell right below the Date, enter ‘=’ ( this allows us to get the data from other sheets) 

 

Step 3: Head over to the Last 7 days - website visitors sheet and select the first cell below the data column. At this point, we’re basically referencing this cell value to our dashboard metrics sheet.

Select the cell and press enter. This will take you back to the dashboard metrics sheet and the same cell value gets copied there.

If you’ve done this correctly, this is how your sheet will look like:

Step 5: To get all the dates, simply click on the cell and drag it down.

Step 6: Follow the same method to get the users count to the corresponding dates. 

Now that we have the data we need, let’s turn it into a chart:

Select the two columns, click on Insert -> Chart

Our chart for the first metric in our dashboard is ready! 


Metric #2: Average duration of a session in the last 7 days 

It is important to understand how much time the visitors spend on your website so you can improve its content accordingly.

Since you have already created your first report, there are two ways to create the subsequent reports in Google Sheets. 

You can simply follow the same steps, i.e go to Add-ons -> Google Sheets -> Create a New Report

Alternatively, you can simply copy the details from the first report, changing only the metrics and dimensions value, since the other data like View ID, Start Date, End Date will remain the same for every metric.

Since we are looking for the numbers for session duration, let’s replace ga:users to ga:avgSessionDuration and run the report. 

Just like the first report, you will see a new sheet added at the bottom, containing the numbers for the average session duration for your website visitors in the last 7 days. 

You can create a chart by following the same steps as you did previously.

Metric #3: Website traffic from different sources 

Identifying from which channels the visitors land on your site is an important measure of your outreach efforts - this will help you understand which channels work the best, where to focus your efforts and so on. 

To get the data we need, let’s create a new report like usual:

But unlike the previous reports where we compared users (Metric #1) and Avg. Session Duration (Metric #2) where Date was the dimension, here we are comparing Users with the respective Traffic Sources.

That means, our metric will be users and the dimension will be Source.

You can change the dimension from Traffic Sources -> Source

After creating the report, add the new numbers to the dashboard metrics sheet and create a chart. 

Metric #4: Website visitors week-on (Last week vs. This Week) 

We’ve already created a Last 7 days Visitors report - but if you want to go one level deeper and get more granular data, this is a useful metric which measures how many visitors come to your site week after week.

A This Week vs. Last Week comparison chart reflects how your efforts as a team in the last week translated into website visitors.

This way you can look at this data and put a finger on your activities from the previous week that would have contributed to the number - whether it is a new blog, lead magnet, or a new landing page; discarding the ones that didn’t work or doubling down on what worked really well.

However, this metric is slightly tricky to bring into our dashboard and will require the use of creating two different reports and the use of Google Sheet functions.

If you prefer video, here is a tutorial where we walk through how to get this data in Google Sheets dashboard:

Before we begin, for the report to make sense, you need to define a 7-day window with the start and end date - this is the data that gets updated week after week.

For our convenience, let us use the standard 7-day period - Monday to Sunday so you can easily track back to your marketing efforts in the previous week.

To summarize, here are the 2 reports we will be creating: 

This Week Report:-

Start Date - Current week’s Monday

End Date -   Current date (Today’s date)

Last Week Report:-

Start Date -  Previous week’s Monday

End Date -    Previous week’s Sunday

This Week Report

Start Date

The function for the start date is the tricky part - since you want the report to always start with Monday and end on a Sunday irrespective of which day you create the report. 

Here’s the function to enter in the start date cell.

=today()-weekday(today())+2

Looks complex? 

We’ll break it down the different components in the function:

=today()

The today function returns the current day of the week.

Eg. Wednesday, Saturday; whichever day you are creating the report.

But that is not the date we are after - we want to know what date Monday falls on in the current week, since that is the start date for our report. 

To find this, we’ll use Google Sheets’ weekday function that returns the value for what weekday the current date is. 

 = weekday(today())

Example. 5 means Thursday, 4 means Wednesday and so on.

In the same function, adding a +1 at the end returns the date of the Sunday in the week.

 = weekday(today())+1)

The above function is useful if our week was from ‘Sunday’ to ‘Saturday’.

Since our week is Monday to Sunday we’ll add a +2 at the end:

(= weekday(today())+2)

Additional read: Here’s an article that explains how the weekday function works in Google Sheets. 

So the function for our start date is ( = weekday(today())+2)returning the date of the Monday in the week.

End date

This is straightforward because while your report starts on Monday, it should always end on the current day. 

If you are wondering why so, once you have created this report, - you are looking for the numbers from the start of the week (Monday) to whatever is the current day you are reading the report. 

Function: 

Start Date: = weekday(today())+2)

End date:   = today

This is how the report will look like:

Since we’ve created this report on a Tuesday, it returns the numbers for only Monday (08/03) and Tuesday (08/04). 

If we come back to this report later this week, the remaining dates will have been updated here.

Last Week Report

Since we’ve already defined the function for the current week, we can simply use that as a base value to get the numbers for the week before - so the start date is simply the previous Monday, which is 7 days before This Week report’s Start Date. 

Start Date: =E4-7 

End Date: = E4 - 1 (Last week’s End Date is simply one day before the current week’s Start Date) 

Now that we have our reports, let’s turn it into a This Week vs. Last Week chart.

In our Dashboard metrics sheet, add 3 new columns:

Day | This Week | Last Week

Fill up the Days column - from Monday to Sunday. 

For the other two metrics, fill in the data from the two latest metrics and create a chart.

Our chart is ready!

Fine-tuning the Dashboard

With our Google Sheets Dashboard created, there are a couple of improvements we can make to make it easy for us to read and analyze the data.

Move all the charts to one place

When you first create a new chart, Google Sheets by default creates them in the same sheet where you have your metrics. 

Since we want a neat dashboard, simply move the charts to a separate sheet         (Google Dashboard Sheet) which we’ve already created.

Hide the created sheets 

When creating a new report, the Google Analytics add-on will create a series of new sheets at the bottom, making your sheet congested and a little difficult to read through.

To avoid this, let’s hide all sheets except our primary Report Configuration, Dashboard metric, and Google Sheet Dashboard sheets.

You can do this by right clicking the sheets at the bottom and clicking on Hide Sheets.

 To view these sheets, go to View -> Hidden Sheets at the top.

Scheduling Reports

Finally, to create an automated dashboard, we need to schedule reports at a regular interval. 

To do this, head over to Add-ons -> Google Analytics -> Schedule Reports



After saving, the reports will get updated every day in the same window - you can even customize it to every week or even every hour! 

Conclusion

An automated Google Sheets Dashboard is one of the best ways to get a complete overview of your marketing funnel and understand how your team’s efforts are translating into results. 

To make this dashboard even more useful, you can add other metrics such as Traffic by country, Number of signups, etc. that answers important questions like product market fit, buyer persona, best performing channel etc.

Want an easier option? You can download a free template of our Google Sheets Dashboard that you can easily edit and use for your website metrics.