It’s hard to imagine the work of an internet marketer without spreadsheets. Previously, the main tool was Excel from MS Office, but now more and more specialists are switching to Google Sheets. And there’s nothing strange about it, considering the following advantages:
- Basic Google features are free.
- Convenient cooperation – no need to send each other various files all the time.
- All the changes are saved automatically.
- History access – you can roll back to the moment where everything went wrong.
- Automatic third-party data import – analytics and advertising services, tool for tracking calls, etc.
Google sheets integration by Coupler.io is a versatile and functional tool with a bunch of unique features and scripts to use. Using only formulas, you can write a book – without turning your content into a monstrous long read. Let alone advanced features like working with scripts, getting into the details, and doing extensive instructions for each function.
For an online marketer, making reports is a routine task. However, without them, it will be difficult to organize the work, learn about its results, and show them to clients and management. Google Sheets can help in creating and storing reports in the most convenient way. In this post, we will understand the service parameters and learn how to create an ultimate all-in-one dashboard.
Being engaged in e-commerce means to be always aware of what is happening. If you keep an eye on your sales and conversion rates, you can always identify weak spots of your business and understand where to invest. This is why you need structured data to help with you the business aspects above as well as many more issues. After all, the quality and speed of decision making depend directly on how the information is provided and how quickly it’s processed by our brain. That’s why digital dashboards are so popular in today’s online business – they visualize critical data in a perfect way, allowing you to control your business with minimal risks.
Today we will show you how to build an all-in-one digital dashboard for your business in Google Sheets.
eCommerce Metrics
First, you need to decide what metrics you are going to send to your future digital dashboard.
Keep in mind that every metric is important, however, you should not overload your dashboard. Determine the most important metrics to track the performance of your e-commerce business model. Maybe you have a service shop or a standard online store – it doesn’t really matter. What matters is the following 7 eCommerce metrics that we offer you to choose to track your business. Of course, you can add your options as well.
For this article, we have chosen data from a small e-commerce retailer that sells sandwiches online in the San Francisco Bay Area. This is what our dashboard will be based on.
These are the marketing metrics we chose:
- Sales distribution by region (SF Bay Area County)
- Total order value and conversion rate
- Expected and total revenue
- Average order value
- Average order lifetime
- Best Sales and Revenue performance
Digital Dashboard Initial Data
Each metric on your dashboard requires specific data as follows:
1. Sales distribution by region. To see how many sales were made and the revenue in various counties in the SF Bay Area, the following data is required:
- Each sale info;
- Each customer info.
2. Total order value and conversion rate. This is the sum of all placed orders, including those that were lost. Sales conversion rate is the sales/number qualified leads ratio. To calculate this metric you will need information about each order and each sale.
3. Expected and total revenue. The total revenue = the sum of all sales. And the expected revenue is all sales + open orders. To calculate this metric you will need information on each separate order.
4. Average order value. Total revenue to the number or orders ratio.
5. Average order lifetime. You will need information about each sale to figure out how long it takes to make a sale.
6. Best Sales and revenue performance. This metric requires sorting out the top 5 sold products (in our case, sandwiches) by sales and revenue. And again, here you will need information about each sale and product.
Initial Data Sources
If your online store or service center is running on an e-commerce platform like 3dcart, most of your data will be on it. Otherwise, if, for example, you sell on Instagram or have a website with a portfolio, you can choose to store your business data wherever you want. You can use Airtable as a database for information about products, customers, sales, bank accounts, etc. Your basic set of marketing tools may also include other features and services as follows:
- CRM apps (e.g. HubSpot or Pipedrive) for convenient client/sales management;
- Google Analytics for website traffic and customer behavior analysis, etc;
- Email marketing services (e.g. Mailchimp or Sender) to stay in touch with your clients.
We chose Pipedrive CRM for sales pipeline management and Airtable for product/customer information storage. Consequently, these are our data sources.
Importing Data Into Google Sheets
You can do it manually (export a data set from your data source in a supported file format and then import it into Google Sheets) or automatically (use a special tool (e.g. Coupler.ia or Automate.io to connect your data source to Google Sheets to synchronize your data automatically). We chose the automatic version and Coupler.io for our case because we were building a live digital dashboard. Other reasons for our choice were as follows:
Coupler.io pulls data from various sources like Airtable, Pipedrive, HubSpot, etc., and automates data imports by schedule (every hour, 3 hours, daily, and so on).
You will need to choose the data category (Deals in our case) and connect the spreadsheet to Pipedrive. This is to import Pipedrive data into Google Sheets.
Now, to import data from Airtable a shared view link of your Airtable data source is required. You will need this for the Best Performing Products metric and better sales analytics. Once the spreadsheet has the raw data, we can start to build our dashboard.
How to Build an Ultimate eCommerce Dashboard in Google Sheets
We will show you the formulas we used to calculate each metric below. We also had each section represented on a separate sheet for convenience.
Sales by Region
SF County Column
We applied the following formula to the A2 cell:
=unique(‘Airtable Data’!$B$2:$B)
This means the range with the names of regions per each sale. The “unique” function will return all unique values from this range.
Sales Column
The following formula has to be applied to the B2 cell. Drag it down to the range end:
=countif(‘Airtable Data’!$B$2:$B,A2)
The “countif” function will count sales by each county.
Revenue Column
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)
This is the range with the amount per each sale. The “sumif” function will sum the revenue by each county.
Insert a Bubble Chart
Choose the range A1:C10 and go Insert=> Chart. Pick a Bubble Chart type.
Sales Conversion Rate
Apply the following formula:
=COUNTIF(‘Pipedrive Deals’!$AP$2:$AP,”won”)/
COUNTA(‘Pipedrive Deals’!$AP$2:$AP)
The first formula 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. The sales conversion rate is the result of dividing the first formula by the second formula. Choose the cell with the value and insert a Gauge chart type.
Total Orders And Revenue
Total Orders
You will need to apply this next formula:
=COUNTA(‘Pipedrive Deals’!$AP$2:$AP)
The formula is the range with the order status (open, won, lost). The “counta” function will count all orders.
Total Revenue
Apply the following formula:
=SUM(
Filter(‘Pipedrive Deals’!$AI$2:$AI,’Pipedrive Deals’!$AP$2:$AP=”won”))
This is the range with the value of each order. The “filter” function will filter orders by the “won” status. The “sum” function will summarize the won orders to count the total revenue.
Expected Revenue
You will need to 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”))
Here, the “filter” function will filter out the orders by two statuses: “won” and “open”. The “sum” function will sum the won and open orders to calculate the expected revenue.
Scorecard Chart
Insert a Scorecard chart for each metric individually.
Average Order Lifetime & Average Order Value
Average Order Value
You will need to apply the following formula:
=SUM(
Filter(‘Pipedrive Deals’!$AI$2:$AI,’Pipedrive Deals’!$AP$2:$AP=”won”))/
COUNTA(‘Pipedrive Deals’!$AP$2:$AP
Both total revenue and orders are explained above.
Average Order Lifetime
This is where you need to find out how many days were spent on each sale. To do this, go to the Pipedrive deals sheet, create 1 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)))}
The “minus” function will show the difference between the order creation date (AK2:AK) and the order closure date (AY2:AY).
Then you will need to get back to the dashboard and apply the next formula to calculate the average order lifetime:
=IFERROR(AVERAGE(‘Pipedrive Deals’!$A$2:$A))
This is the freshly created range with days per order. The “average” function will get back the average value of the specified range.
Scorecard Chart
Insert a Scorecard chart for every metric individually.
Breakdown or Orders
You will need the following formulas to break down orders by statuses:
Open Orders:
=COUNTIF(‘Pipedrive Deals’!$AP$2:$AP,”open”)
Lost Orders:
=COUNTIF(‘Pipedrive Deals’!$AP$2:$AP,”lost”)
Won Orders (Sales):
=COUNTIF(‘Pipedrive Deals’!$AP$2:$AP,”won”)
Pipedrive Deals’!$AP$2:$AP is the range with the order status (open, won, lost). The “countif” function will return the number of orders sorted by the chosen status (“open”, “lost”, or “won”).
Choose the values of all orders by status and create a 3D pie chart.
Best Products
You will need to filter out all products, calculate sales + 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, we should count sales per each product using the next “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)
Now you are supposed to have a table with 3 columns: Products (A1:A11), Sales (B1:B11), and Revenues (C1:C11). To get the best performing products out, use the “SORTN” function. Check out this formula for the top 5 products by sales.
=SORTN(A2:B11,5,1,B2:B11,false)
And here’s the formula for the top 5 products by revenue:
=SORTN({A2:A11,C2:C11},5,1,C2:C11,false)
Choose the resulting tables and insert a Table chart, individually for each table.
Conclusion
The main purpose of this article is to represent the power of Google Sheets and show its functions and features in the best way. Our dashboard was built on the data from Pipedrive and Airtable, but you are free to apply this knowledge to your eCommerce project or case study. A lot of various tools and plugins allow you to sync spreadsheets with almost any data source. This allows you to add universal metrics to your dashboard and keep more required data in a single place. So don’t waste your time and use Google Sheets with confidence!
