How to Build An eCommerce Dashboard for KPI Reporting Using Google Sheets & Supermetrics

While Dashboards in Google Analytics are nice features, they really lack some significant functionality, such as displaying the percent change between two numbers (say traffic last month vs. this month). In addition, formatting is limited, and only two time periods can be compared at once.

Using one of Inflow’s favorite and most used Google Sheets Add-ons, SuperMetrics, you can easily create a fully functional dashboard containing your eCommerce KPIs in Google Sheets. The dashboard will automatically refresh in the time increments you specify and you can easily pull in data from:

• Google Analytics
• Google Search Console
• Google AdWords
• Moz
• SEMrush
• Bing
• Facebook Ads
• Adobe SiteCatalyst
• And many other sources

In this post, you’ll learn how to quickly create a dashboard that contains metrics for Overall, Organic, and Paid traffic. We will be looking at traffic and sales metrics in six different time comparisons (month over month, this month over this month last year, last three months vs. three months before that, etc.).

As a bonus, I’ll show you how to combine reports for multiple sites into one centralized dashboard.

You can download the template for the dashboard by filling out the form at the bottom of this post. Please make a copy, rename it, and then follow the below instructions to customize to your site and data points.

Exploring the eCommerce KPI Dashboard

Before we get into the nitty-gritty of building your dashboard, let’s “begin with the end in mind” per Stephen Covey and review exactly what is in this dashboard.

The dashboard above represents an eCommerce website with one other “significant” non-eCommerce goal, like downloading an important buying guide or registering for the email list. The dashboard assumes you care mostly about Overall, Organic, and Paid traffic.

The purpose of the dashboard is to get us a quick “health check” of the site performance baselined against various points in time. Most questions developed by looking at this data will need to be answered by diving into Google Analytics. The example dashboard shown pulls metrics from Google Analytics, Google AdWords, and Moz.

We’ve laid out this dashboard to include three sections:

1. Overall Traffic
2. Organic Traffic
3. Paid Traffic

Each section features a graph of Traffic (sessions) and Revenue month over month for the last two years.

Here is a close-up of the Overall Traffic section. Organic Traffic and Paid traffic sections look similar except Paid contains CPC and ROAS as well.

Here’s an explanation of the columns:

This Month = The Reporting Month

Last Month = The month before Reporting Month

Same Month Last Year = Reporting Month last year

Last 3 Months = The Reporting month plus the two months prior

Previous 3 Months = The three months prior to the last three months

Same 3 Months Last Year = Reporting month plus the two months prior, last year.

Building the eCommerce KPI Dashboard

Now that we know what we’re building, let’s get started!

First, make sure to install the SuperMetrics Google Sheets Add-on.

Next, open up your copy of the dashboard template and launch SuperMetrics by clicking:

Add-Ons -> Supermetrics -> Launch Sidebar

Step 1: Configure the Dashboard to use your Analytics and AdWords accounts by clicking:

Add-Ons - SuperMetrics -> Duplicate This File For Another Account

Step 2: Adjust metrics to be appropriate for your website. Choose the right goals for the dashboard etc.

To do this, Search and Replace within the SuperMetricQueries tab. For example, in the template, the main conversion goal was Goal 8 in Google Analytics. Maybe for your website you need to use Goal 1 as the main goal and also need to see data for Goal 2. I’d search and replace using the following:

SEARCH:

"goal8completions","goal8conversionrate_perc”,

REPLACE:
"goal1completions","goal1conversionrate_perc","goal2completions","goal2conversionrate_perc”, "transactions","EcommerceConversionRate_perc","revenuepertransaction",”transactionrevenue"

WITHIN THIS SHEET ONLY

Step 3: Customize Report tables with the proper labels on the left-most column.

Don’t delete rows; only delete values from the table, as this can affect the “inner workings” of SuperMetrics.

You can shift around what data goes in which row, but just make sure it matches the query output from SuperMetrics.

Step 4: Refresh All Queries.

Check to make sure all queries executed without error by checking the Status message on the SuperMetrics Query tab (hint: the tab might be hidden).

See the last status…

Step 5: Customize Graphs to illustrate revenue as well as traffic. You can also graph conversion goals with some slight modifications.

BONUS: Track Multiple Sites in One Workbook

If you want to track multiple sites (let’s say clients) in one centralized workbook, follow these extra steps:

Step 1: Create a new Master KPI Dashboard workbook.

Step 2: Copy the dashboard Sheet to your Master KPI Dashboard workbook using “Copy To”.

Step 3: Clear all data and text, including column headers, but not including graphs from the copy of your Dashboard Sheet in the Master KPI Dashboard Workbook.

If you are having problems with the graphs, you may need to change the sheet name to “Sheet1” temporarily. If that doesn’t work, check the ranges on the graphs.

Step 4: Use Importrange() in cell A1 to bring in all the data and have it stylized, etc.

=importrange("YOUR DASHBOARD URL","sheet1!A1:AK200")

Ideas For Further Improvement and Other Resources

There are plenty of other ways this most basic eCommerce KPI Dashboard could be expanded and improved. I am happy to freely release this to the community to collaborate and make the necessary improvements.

Some ideas include pulling in additional eCommerce-specific metrics as goals like top products, Add To Cart conversions, Various Steps In Checkout Process, and more. In addition, data from other sources such as Google Search Console, Moz, and SEMrush could really put this dashboard on steroids. Lastly, using Google Data Studio to make some kickass graphs and nice looking reports is on the list!

For more resources on how to build dashboards as well as sample templates, etc., I recommend checking out CodingIsForLosers.com. In addition, here is a link to SuperMetrics eCommerce template.