inflow-logo

Inflow: eCommerce Marketing Agency

  • Services
    • SEO
    • PPC
    • CRO
    • Paid Social
  • Clients
    • Case Studies
  • About Us
    • Contact Us
  • Insights Blog
    • Resources
  • Request Proposal
  • Services
    • SEO
    • PPC
    • CRO
    • Paid Social
  • Clients
    • Case Studies
  • About Us
    • Contact Us
  • Insights Blog
    • Resources
  • Request Proposal

Home > eCommerce Digital Marketing Blog > eCommerce Strategy > How to Build An eCommerce Dashboard for KPI Reporting Using Google Sheets & Supermetrics

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

Posted By Mike Belasco on March 21, 2017

  • 33shares

Building eCommerce KPI Dashboards in Google Sheets

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.

eCommerce Dashboard Screenshot
The All-In-One eCommerce Dashboard solution for Google Analytics.

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.

eCommerce KPI 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.

KPIs - Overall Traffic Screenshot

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

KPI Dashboard Screenshot

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.

Key Metrics Dashboard

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

Compare YOY Analytics

 

See the last status…

Query ID Supermetrics

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

Duplicate Spreadsheet

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.

Website KPI Dashboard Template

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.

 

eCommerce KPI Dashboard Download

Get your free copy by email and spend less time reporting, and more time doing!

 



  • 33shares

2 Comments on "How to Build An eCommerce Dashboard for KPI Reporting Using Google Sheets & Supermetrics"
  1. Ben says:
    June 1, 2017 at

    Is this where i get the free template?

    Reply
    • Everett Sizemore says:
      June 2, 2017 at

      Yes Ben. Please scroll to the bottom of the post and you should see the form.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

  • How to Determine Which eCommerce Platform is Best for Your SEO Strategy We discuss all the factors you should consider in choosing an eCommerce platform from the perspective of SEO. This advice is based on experience optimizing SEO for hundreds of eCommerce companies.
  • 5 CRO Best Practices That Are No Longer Best Practices Working with new clients to optimize their websites, one of our early conversations inevitably centers on the topic of best practices. You hear this across pretty much any industry or […]
  • The Clipper (3 of 9 Winning Plays) – Discount Code Fields for Checkout Pages Having a coupon form field extremely visible in the cart and checkout causes potential customers to go off and look for coupons — potentially decreasing conversion rates. About the […]
Mike Belasco on hiking trail.

Mike Belasco

Mike Belasco has been an entrepreneur and digital marketer since 2003. He’s been the CEO of Inflow (founded as seOverflow) since 2007 and has led Inflow to five Denver’s Fastest-Growing Private Company awards and three Inc. 5000 awards. In 2009, he also founded ConversionIQ, which was acquired by Inflow in 2014.

View Author’s Profile

Related Categories

  • KPIs and Reporting (11)
  • Most Popular Posts:

    eCommerce Marketing Automations Systems Compared
    Technical Mobile Best Practices for SEO and Usability
    Expanding the Horizons of eCommerce Content Strategy
    Thin & Duplicate Content: eCommerce SEO
    5 Ways eCommerce Content Audits Can Increase Revenue
    Want to get content like this straight to your inbox? Subscribe to our weekly content alerts and monthly Inflow Insights newsletter now.

    Categories

    • SEO
      • Content Marketing
      • Link Building
      • On-Page SEO
      • SEO Strategy
      • Technical SEO
    • Paid Advertising
      • Goal Metrics and Analytics
      • Paid Search
      • Paid Search Shopping
      • Paid Social
    • Conversion Rate Optimization
      • A/B Testing
      • eCommerce Page CRO
      • Mobile Conversion Optimization
      • Tools and Plugins
      • Usability
    • Case Studies
    • eCommerce Strategy
      • KPIs and Reporting
    • Digital Marketing Trends in eCommerce
    • Inflow News

    Request a Proposal

    We'll build a custom proposal to meet your goals. Get the process started now.

    Google Premier PartnerInflow is a facebook-certified-creative-strategy-professional Moz Recommended Company Inc 5000 Inflow Clutch Profile
    • Services
      • SEO
      • PPC
      • Conversion
    • Insights
      • Insights Blog
      • Case Studies
      • Resources
    • More
      • Contact
      • Careers
    •  
      • Press Info
      • Privacy Policy
    REQUEST A PROPOSAL
     
    CALL US AT 303-905-1504
    Monday - Friday, 8 a.m. - 6 p.m. (MST)
     
    facebook twitter linked-in linked-in rss

    Send this to a friend