How to Create an Ecommerce Dashboard in Google Sheets
Your e-commerce data is scattered everywhere. Shopify shows your sales, Google Analytics has your traffic sources, and Facebook Ads Manager holds your campaign spending. Answering a simple question like "Which ads are actually driving sales?" requires logging into multiple platforms and wrestling with spreadsheets. This guide will walk you through building a centralized e-commerce dashboard directly in Google Sheets, giving you a single source of truth to track your business performance.
Why Use Google Sheets for an Ecommerce Dashboard?
Before we build, let's look at why Google Sheets is such a popular choice for this job. While it's not a purpose-built business intelligence tool, it offers some compelling advantages for store owners and marketers.
It’s Free and Accessible: You don't need to purchase any expensive software. If you have a Google account, you have access to Google Sheets.
Highly Customizable: You have complete control over what metrics you display and how you visualize them. You aren't locked into the pre-made widgets of a specific platform.
Centralized Data: It's the perfect place to pull together data from different sources (like Shopify sales and Google Ads spending) to see the full picture.
Easy to Share: You can effortlessly share your dashboard with teammates, business partners, or clients with secure, controllable access.
The biggest challenge? It’s a manual process that requires setup and regular maintenance. But the clarity you gain from seeing your key metrics in one place is often well worth the effort.
Phase 1: Planning Your Dashboard and Gathering Data
A great dashboard starts with a solid plan. Jumping straight into building without a clear goal often results in a cluttered spreadsheet that raises more questions than it answers. Following these steps will give you a clear roadmap.
Step 1: Define Your Key Metrics (KPIs)
First, decide what you need to track. Don't try to include every single metric available. A crowded dashboard is an ignored dashboard. Focus on the key performance indicators (KPIs) that tell you if your business is healthy and growing. Ask yourself: "What numbers do I need to see every day or week to make better decisions?"
Here are some essential e-commerce KPIs to consider:
Sales KPIs: Total Revenue, Orders, Average Order Value (AOV)
Marketing KPIs: Website Sessions, Traffic by Source/Channel, Return on Ad Spend (ROAS), Cost Per Acquisition (CPA)
Conversion KPIs: Ecommerce Conversion Rate, Add to Carts, Cart Abandonment Rate
Customer KPIs: New vs. Returning Customers, Customer Lifetime Value (CLV)
Choose 5-7 core KPIs to start with. You can always add more later.
Step 2: Get Your Data into Google Sheets
Now that you know what data you need, you have to get it into your spreadsheet. You have a few options, ranging from fully manual to semi-automated.
Method 1: The Manual Export (Copy & Paste)
This is the most straightforward but also the most time-consuming approach. It involves going into each platform (e.g., Shopify, Google Analytics), exporting your data as a CSV file, and then importing or pasting that data into a Google Sheet.
Pros: Simple, no setup required.
Cons: Repetitive, prone to errors, and has to be done every time you want to update your dashboard. This is the classic "spend Monday morning pulling reports" routine that drains your time.
Method 2: Using a Connector or Add-on
Many platforms offer official or third-party add-ons for Google Sheets. The official Google Analytics add-on, for example, allows you to pull data directly into your sheet and schedule regular updates.
Pros: Can be automated, reduces manual work.
Cons: Might not be available for all the platforms you use (e.g., getting Shopify, Facebook Ads, and Klaviyo data into one sheet can be tricky).
Method 3: Automation Tools like Zapier
Tools like Zapier or Make.com allow you to create automated workflows. For example, you can set up a "Zap" that automatically adds a new row to a Google Sheet every time you get a new order in Shopify.
Pros: Live (or near-live) data connection, highly flexible.
Cons: Can have a learning curve and may require a paid subscription depending on your data volume.
For this tutorial, we’ll assume you’re starting with periodically exported CSV data, as it’s the most common starting point.
Phase 2: Building Your Dashboard
With your data in hand, it's time for the fun part: building the actual dashboard. We'll use a simple, powerful three-tab structure to keep things clean and manageable.
Step 1: Structure Your Spreadsheet
Create three tabs at the bottom of your Google Sheet:
Raw Data: This is where you will paste your data exports. Keep it messy here. Each new export for a time period goes in this tab.
Calculations: This is the engine of your dashboard. We'll use this tab to summarize and organize the information from the 'Raw Data' tab using formulas and Pivot Tables.
Dashboard: This is the polished, presentation-ready final product. It will be made up of charts and key numbers that pull their information directly from the 'Calculations' tab.
This separation is crucial. It keeps your raw data untouched, your complex formulas organized in one place, and your dashboard clean and easy to read.
Step 2: Summarize Your Data in the 'Calculations' Tab
This is where you'll do the heavy lifting. Navigate to your 'Calculations' tab. Here, you'll use formulas and Pivot Tables to aggregate the numbers you need for your dashboard.
Using Formulas for Key Metrics
Let's say your 'Raw Data' from Shopify has columns for Date, Source, and Order Total. In your 'Calculations' tab, you can create a small table for your top KPIs.
To calculate Total Revenue, you can use a simple SUM formula pointing to your data:
(This assumes 'Order Total' is in Column C of the 'Raw Data' tab.)
To calculate the Number of Orders, you can use COUNTA:
(This counts all non-empty cells in the column and subtracts 1 for the header row.)
To calculate your Average Order Value (AOV), simply divide your revenue by your orders:
Using Pivot Tables for Deeper Insights
Pivot Tables are one of the most powerful tools in Google Sheets. They let you quickly summarize large amounts of data without writing complex formulas. They are perfect for analyzing things like "Revenue by Traffic Source."
Go to your 'Raw Data' tab and select all your data (Ctrl+A or Cmd+A).
Go to Insert > Pivot Table.
Choose to place it in your 'Calculations' sheet.
The Pivot table editor will open. To see Revenue by Source, you would:
In the Rows section, add the Source column.
In the Values section, add the Order Total column, summarized by SUM.
Instantly, you'll have a clean table showing your total revenue broken down by each traffic source. This is what you will use to build your charts.
Step 3: Create Your Visualizations in the 'Dashboard' Tab
Now, click on your shiny, new, and currently empty 'Dashboard' tab. This is where you create a scannable, visual overview of your business.
Adding Scorecards for KPIs
Simple, bold numbers are great for displaying your main KPIs. Use Scorecard charts for this.
Select an empty cell in your 'Dashboard'.
Go to Insert > Chart.
In the Chart editor, under Chart type, scroll down and select Scorecard chart.
For the Data range, select the single cell in your 'Calculations' tab that contains the KPI you want to display (e.g., the cell with your Total Revenue formula).
Repeat this for your top 3-5 KPIs like AOV, Total Orders, and Sessions.
Visualizing Trends with Charts
Now, let's turn your summaries in the 'Calculations' tab into visuals.
To create a Bar Chart for Revenue by Source:
Select the data from your Revenue by Source Pivot Table in the 'Calculations' tab.
Go to Insert > Chart. Sheets will likely suggest a bar or column chart.
Customize the titles and colors to make it clear and on-brand.
Click and drag the chart to arrange it on your dashboard.
Use line charts to show trends over time (like weekly sales) and pie charts to show composition (like the percentage of new vs. returning customers).
Making Your Dashboard Interactive with Slicers
Want to filter your whole dashboard by a specific date range or traffic channel? Slicers are the answer.
On your 'Dashboard' tab, go to Data > Add a slicer.
In the settings, choose the column you want to filter by (e.g., Date or Product Name).
Place the slicer on your dashboard. Now, when you use the slicer's dropdown menu, all the charts connected to that data source will update automatically!
Maintaining and Improving Your Dashboard
Building the dashboard is the first step. To keep it useful, you have to keep it fresh.
Schedule Time for Updates: If you're relying on manual data exports, book a recurring 15-minute slot in your calendar every Monday morning to refresh the data in the 'Raw Data' tab. Your formulas and charts will update automatically.
Review Your KPIs: Your business goals will evolve. Revisit your dashboard once a quarter to make sure you're still tracking the metrics that matter most. Don't be afraid to remove charts that are no longer relevant.
Iterate: Did an analysis in the dashboard spark a new question? Great! Go back to your 'Calculations' tab and build a new summary to answer it, then add a new visual to your dashboard. This is how a static report becomes a living tool for business analysis.
Final Thoughts
Creating an e-commerce dashboard in Google Sheets centralizes your key metrics, helping you move from guessing to making data-backed decisions. Although it takes some initial setup, having a single view of your sales, marketing, and customer data is an invaluable asset for growing your online store.
We know firsthand that digging through spreadsheets and refreshing reports is time-consuming. We actually built Graphed to eliminate this exact problem. By connecting your tools like Shopify, Google Analytics, and Facebook Ads directly, you can create real-time, self-updating dashboards simply by describing what you want to see - "Show me a line chart of Shopify sales and Facebook ad spend over the last 90 days." Instead of formulas and pivot tables, it's a simple conversation to get the insights you need in seconds, not hours.