How to build your own Shopify sales report in Google Sheets
Reporting & Analysis
Feb 28, 2022
To create a Shopify sales report, focus on key metrics like Gross Sales, Net Sales, Total Orders, Total Discounts, Total Shipping Costs, and Total Refunds/Returns. Also, track Average Order Value (AOV), Sales by Product, and Sales by Customers, including Repeat Customers, High-Value Customers, and Customers by Sales Quantity. To simplify the process, you can manually export data from Shopify, but this can be time-consuming. A more efficient approach is to use Airboxr's Hops, which provide quick access to these insights in Google Sheets without complex setups.
The must-have metrics for your Shopify sales report
Shopify generates plenty of analytics data to help you measure the performance of your store. It collates the data and presents them to you in the form of Shopify reports. But it is typically easy to get lost in reports if you don’t prioritize the right metrics.
This guide will go over all the metrics necessary for your Shopify sales reporting. You will also learn how to present your sales data in Google Sheets—one of the most widely used tools for analysis.
We’ll dive into the following topics:
Key metrics for your sales report
Gross Sales: The amount of money earned from the sales of your products over a period of time excluding any deductions like taxes, discounts, returns, and shipping.
Reviewing this metric can help you understand the total amount of revenue made by your store. It is also a basis for calculating other important revenue metrics such as net sales and gross profit margins.
While gross sales represents the total revenue of your business, it does not tell you your store’s profit. To get a better overview of your store’s selling performance, you should also track net sales.
Net Sales: The sum of gross sales minus the total deductions.
This metric gives you insights into the effectiveness of your sales strategies as well as your product quality. Net sales along with gross sales can help you understand how well your business is performing and devise plans to sell better.
Total Orders: The total number of orders generated through your Shopify store during the selected period.
Total Discount Amount: Total reduced prices on your products.
Total Shipping Amount: Total shipping fees paid by customers.
Total Refunds and Returns: Refund is the amount of money being returned to the customer who is not satisfied with the products they purchased. On the other hand, return is the process of a customer bringing back a purchased product to your store, but not necessarily to get their money back. Such returned products could be exchanged for other products, store credits, or could also be refunded.
It is important to take note of the total refunds and returns of products in your store. If there are many customers returning the same product then there could be something wrong with the quality and functionality of the product. This understanding could help you take necessary actions to prevent further returns.
Average Order Value (AOV): The average amount your customer spends per order on your store.
This is an important metric to track as it helps you evaluate the effectiveness of your marketing and pricing strategies. A higher AOV indicates better profit margins and growth opportunities for your business.
Sales by Product: Your best-selling products over a given period.
This helps you identify the most popular products in your store. Keeping track of your best-selling products is helpful for inventory planning, making decisions about promotions and discounts, and so on. This can also help you create strategies for the kind of products you want to sell in your store.
Sales by Customers:
Customers are vital to running your business. They help you drive more sales and increase your revenue. So it’s important to identify who your customers are. There’s a lot of customer data on Shopify to help you understand and analyze your customers. Let’s take a look at some of the important customer metrics that you should include in your sales report.
Repeat Customers: Customers who return to your store to make more purchases.
The number of repeated customers in your store directly impacts your overall revenue. A repeat customer is more likely to visit your store and make purchases again and again. So it’s important to make strategies to turn your new customers into returning customers.
High-Value Customers: Customers who buy higher-value products from your store.
High-value customers are very profitable to a business. They can also influence how other people perceive the brand. You should nurture your high-value customers and come up with strategies to retain them.
Customers by Sales Quantity: Customers who buy the maximum number of products from your store.
These customers can be both new as well as repeat customers. Tracking this metric helps you understand the kind of customers you should target to make more sales.
These are some of the key sales metrics that you should pay attention to while preparing your sales report. Besides these, if you want to learn more about other important metrics to track for your store including industry benchmarks, take a look at our Shopify Whitepaper.
Automate your Shopify sales reports.
Sales Reporting in Google Sheets
Now that we have learned about the key sales metrics, let’s get into how to present this data in Google Sheets.
The manual way
One of the ways you can present these data on Google sheets is by exporting them manually from Shopify and adding them to your spreadsheet. To do so, first, you need to know where to access them on Shopify. You can find these metrics under the sales report. To view the sales report, you need to be subscribed to a regular Shopify plan or premium Shopify plan.
Step 1: Go to Analytics → Reports in the left corner of your Shopify Admin Dashboard.
Step 2: Under the Sales section, click on the report that you want to view.
Step 3: Now export the required report.
You can now manually add these reports to your spreadsheet by importing them.
The biggest problem with the manual approach is that it is very time-consuming. You will have to spend hours just to get your data in place. The second biggest problem is the abundance of data. Each of the Shopify sales reports contains more information than you’d probably require. Even if you want to access certain metrics, you’d have to export the entire report. This slows down the process of analysis.
Improve your DTC game. Sign up for weekly tips.
The Airboxr way
Now let’s take a look at a more efficient and effective way - using Airboxr.
With Airboxr, there is no setup needed. You can view sales insights with one click, using Hops. Hops are saved, reusable queries that automatically give you answers to specific questions, to help you make daily business decisions.
Here are some examples of Airboxr's Shopify Hops:
As an example, you can run a Gross Sales Hop in Airboxr. Here are the steps:
Once you are logged in to Airboxr, go to the Hops Marketplace.
Select the Gross Sales Hop and click Add to My Hops.
Go to My Sheets and click Create New Sheet.
On the left sidebar, you will see the Gross Sales Hop. Select it, and click Run.
Select the date range and location on the sheet where you want the data to be shown. Click Run Hop.
Watch how it works in the gif below:
And that's it! ⚡️ Your Google Sheet (within Airboxr) will populate with your Gross Sales data from Shopify.
With Airboxr, you can generate your own Shopify sales report in seconds.