Running an e-commerce business means keeping your eye on the ball. If you know how many sales have been made or how big your conversion rate is, you can reveal weak points and identify where to invest more. Data is what provides the answers to all of these business-related questions and more.
The quality and speed of data-driven decisions often depend on how the data is structured and fed into the decision-maker’s brain. That’s why digital dashboards are so in-demand; they provide visual snapshots of crucial metrics and let you track the business’ health. Today we’ll show you how you can build a digital dashboard for your e-commerce business in Google Sheets, and how to make the dashboard live using a data importing tool, Coupler.io.
Every metric matters, but you should not overload your dashboard. Pick the most crucial values to track the efficiency of your e-commerce business model, be it an online store or a service hub. For example, here is a good selection of 7 e-commerce metrics that you can use to track your business. You may add some more if you are using e-commerce email marketing.
For this blog, our dashboard will be based on the data of a small e-commerce retailer, which is selling sandwiches across the San Francisco Bay Area online.
The marketing metrics we chose are:
To calculate each metric on the dashboard, you’ll need specific data:
This metric shows how many sales have been made and the revenue in different counties in the SF Bay Area.
Required Data:
Your total revenue is the sum of all sales. The expected revenue is the sum of all sales plus open orders.
Required Data:
The total orders metric is the sum of all orders placed, including the lost ones. Sales conversion rate is the ratio of sales to the number of qualified leads.
Required Data:
Average order value is the ratio of the total revenue to the number of orders.
Required Data:
The average order lifetime shows how long it takes to make a sale.
Required Data:
For this metric, we’ll sort out the top five of sold products (sandwiches) by sales and revenue.
Required data:
If your online store operates on an e-commerce platform like Shift4Shop, most of your data will be there. If your e-commerce business uses other channels (if you sell on Instagram or have a portfolio website, for example) you can store your business data anywhere. For example, many entrepreneurs use Airtable as a database for the information about products, customers, sales, bank accounts and so on. Your marketer’s tool stack may include other actionable tools and services, for example:
Our assumed e-commerce retailer uses Pipedrive CRM to manage the sales pipeline and Airtable to store the information about products and customers. So, these are our data sources.
Since we’re building a live digital dashboard, the automatic option will do. We’ve picked Coupler.io for our use case because:
To import Pipedrive data into Google Sheets, we need to select the data category (this is Deals in our case) and connect the spreadsheet to Pipedrive.
To import data from Airtable, we need a shared view link of our Airtable data source. This data is required for the Best Performing Products metric.
Once the raw data is in the spreadsheet, we can get to building our dashboard.
Let’s go section by section and check out the formulas we used to calculate each metric. In this spreadsheet, you’ll see each section introduced in a separate sheet. This will let you master the flow efficiently.
Apply the following formula to the A2 cell:
=unique('Airtable Data'!$B$2:$B)
Interpretation:
'Airtable Data'!$B$2:$B is the range with the names of regions per each sale. The UNIQUE function will return all unique values from this range.
Apply the following formula to the B2 cell and drag it down to the range end:
=countif('Airtable Data'!$B$2:$B,A2)
Interpretation:
The COUNTIF function will count sales by each county.
Apply the following formula to the C2 cell and drag it down to the range end:
=sumif('Airtable Data'!$B$2:$B,A2,'Airtable Data'!$I$2:$I)
Interpretation:
'Airtable Data'!$I$2:$I is the range with the amount per each sale. The SUMIF function will sum the revenue by each county.
Select the range A1:C10 and go Insert=> Chart. Pick a Bubble Chart type.
Apply the following formula:
=COUNTIF('Pipedrive Deals'!$AP$2:$AP,"won")/
COUNTA('Pipedrive Deals'!$AP$2:$AP)
Interpretation:
'Pipedrive Deals'!$AP$2:$AP is the range with the order status: open, won, and lost. The COUNTIF function will count all orders with the status "won". The COUNTA function will count all orders. Divide one to another and you’ll get the sales conversion rate.
Select the cell with the value and insert a Gauge chart type.
Apply the following formula:
=COUNTA('Pipedrive Deals'!$AP$2:$AP)
Interpretation:
'Pipedrive Deals'!$AP$2:$AP is the range with the order status: open, won, and lost. The COUNTA function will count all orders.
Apply the following formula:
=SUM(
Filter('Pipedrive Deals'!$AI$2:$AI,'Pipedrive Deals'!$AP$2:$AP="won"))
Interpretation:
'Pipedrive Deals'!$AI$2:$AI is the range with the value of each order. The FILTER function will filter orders by status "won". The SUM function will sum the won orders only to calculate the total revenue.
Apply the following formula:
=SUM(
Filter('Pipedrive Deals'!$AI$2:$AI,'Pipedrive Deals'!$AP$2:$AP="won"),
Filter('Pipedrive Deals'!$AI$2:$AI,'Pipedrive Deals'!$AP$2:$AP="open"))
Interpretation:
In this formula, the FILTER function will filter orders by two statuses "won" and "open". The SUM function will sum the won and open orders to calculate the expected revenue.
You’ll need to insert a Scorecard chart for each metric separately.
Average Order Value = Total Revenue / Total Orders
Apply the following formula:
=SUM(
Filter('Pipedrive Deals'!$AI$2:$AI,'Pipedrive Deals'!$AP$2:$AP="won"))/
COUNTA('Pipedrive Deals'!$AP$2:$AP)
No interpretation is needed since both total revenue and total orders have been explained above.
To calculate average order lifetime, we need to learn how many days have been spent for each sale. For this, go to the sheet with Pipedrive deals, insert one column at the beginning of the sheet and apply the following formula to the A1 cell:
={"Days per order";ARRAYFORMULA(IF(ISBLANK(AY2:AY),"",
MINUS(AY2:AY,AK2:AK)))}
This MINUS function will calculate the difference between the order creation date (AK2:AK) and the order closure date (AY2:AY).
Now, get back to the dashboard and apply the following formula to calculate the average order lifetime:
=IFERROR(AVERAGE('Pipedrive Deals'!$A$2:$A))
Interpretation:
'Pipedrive Deals'!$A$2:$A is the newly created range with days per order. The AVERAGE function will return the average value of the specified range.
You’ll need to insert a Scorecard chart for each metric separately.
Apply the following formulas to break down orders by status:
=COUNTIF('Pipedrive Deals'!$AP$2:$AP,"open")
=COUNTIF('Pipedrive Deals'!$AP$2:$AP,"lost")
=COUNTIF('Pipedrive Deals'!$AP$2:$AP,"won")
Interpretation:
'Pipedrive Deals'!$AP$2:$AP is the range with the order status: open, won, and lost. The COUNTIF function will return the number of orders sorted by the chosen status ("open", "lost", or "won").
Select the values of all orders by status and insert a 3D pie chart.
First, let’s filter out all products and calculate sales and revenues per each product. The following UNIQUE formula will extract all products from the product column ('Airtable Data'!$E$2:$E) exported from Airtable:
=unique('Airtable Data'!$E$2:$E)
Now, calculate sales per each product with the following SUMIF formula:
=sumif('Airtable Data'!$E$2:$E,A2,'Airtable Data'!$H$2:$H)
Drag it down to the end of the range. Then, do the same with the SUMIF formula to calculate revenues:
=sumif('Airtable Data'!$E$2:$E,A2,'Airtable Data'!$I$2:$I)
You should get a table with three columns: Products (A1:A11), Sales (B1:B11), and Revenues (C1:C11). To extract the best performing products, we’ll use the SORTN function. Here is the formula for top 5 products by sales:
=SORTN(A2:B11,5,1,B2:B11,false)
And here is the formula for top 5 products by revenue:
=SORTN({A2:A11,C2:C11},5,1,C2:C11,false)
Select the resulting tables and insert a Table chart. This should be done separately for each table.
The main idea of this blog post is to show the power of Google Sheets’ functions and features. We’ve built the dashboard based on the data exported from Pipedrive and Airtable, but you can apply this experience to your own e-commerce case study. Many add-ons and tools allow you to sync spreadsheets with almost any data source. This will let you add versatile metrics to the dashboard and keep more actionable data in one place. So, go ahead and good luck with your data!