How to Build a Comprehensive Shopify Sales Dashboard For Your Store
If you run a Shopify store, monitoring your sales metrics is crucial for your business. Shopify constantly accumulates lots of raw data, which you can see in the Analytics and Reports. However, in many cases, you might need to go beyond the native functionality and build a custom dashboard.
In this blog post, you’ll learn how to create your Shopify sales dashboard from scratch. We’ll explore different tools you can use and see how you can update the sales information in your dashboard automatically.
Shopify sales dashboard: What you have out of the box
In Shopify, you can keep track of your store’s numbers using the native overview dashboard. Among other metrics, it provides some basic information on sales. For example, Total sales, Sales attributed to marketing, Sales by POS location, and Sales by social source or traffic source. Here, you can check your sales channels’ performance and monitor sales trends over time. To access the overview dashboard, go to your Shopify admin, and then to Analytics.
If you switch to Reports, you’ll find a list of various sales reports with much more detailed information.
However, this is not a sales dashboard, and you need to dive into each report separately to learn the details. The only sales metric shown here is Total sales for the last 30 days. When you click on a specific report, you can get a more granular view by applying different filters, keywords, and time ranges.
As we can see, Shopify doesn’t provide a native sales dashboard. But you can monitor key metrics in the overview dashboard and access more data in the Reports section. The options for customization and visualization will be very limited in this case. Despite this, for some purposes, this in-built dashboard and reports can be enough. Let’s take a closer look at the main report types to see if this option can meet your needs.
Shopify sales reports. What do they cover?
There are 11 types of detailed sales reports on Shopify:
|Sales over time||Here, you can find information on your total sales and the number of orders over a selected period.|
|Sales by product||In this report, you can view your sales data grouped by the product and see what items are most popular or profitable.|
|Sales by product variant SKU||This report provides more detailed information than the previous one. In particular, it shows statistics for different product variants.|
|Sales by product vendor||Here, you can view data on your suppliers, the products they provide you, and the number of sold items for each position and vendor.|
|Sales by discount||If you use discount codes or automatic discounts, you can see how often people use them and analyze your sales from this perspective.|
|Sales by traffic referrer||From this report, you can learn where your customers come from. It shows whether they searched for your store on Google, clicked a link in an email, or on social media.|
|Sales by billing location||Here, you can explore the geography of your client base and see where the orders come from. The report shows your sales metrics grouped by country and region.|
|Sales by checkout currency||In this report, you can see what currency your customers use at the checkout and the value of each order in your local currency.|
|Sales by channel||This data shows you what applications or channels your customers use to order products from your store.|
|Sales by customer name||Statistics in this report allow you to analyze your audience in more depth, find your most active customers, and see what type of products they are interested in.|
|Average order value over time||This report shows you how much your customers pay for their orders on average. You can also select different time ranges and use other filters to explore the emerging trends.|
How to customize your Shopify sales reports
You can adjust all the default sales reports in several ways:
- Applying filters
Shopify allows you to narrow down the information in sales reports by using keywords and various filters. For instance, you can sort your metrics by billing city, UTM campaign name, or product type. But you won’t be able to save these settings if your plan is lower than Advanced Shopify.
- Adjusting time range
For most sales reports, the default time range is one month to the current date. You can change it to another period, like a week, calendar month, several months, etc. You will also be able to save your preferences.
- Adding or deleting certain columns
You can exclude information you don’t need and include other metrics that are useful to you. For example, you can add new columns that show things like Average order value, Ordered quantity, Tax, and others.
To summarize, the sales reports discussed above cover some of the key sales metrics you may need. And with the available customization options, you can adjust them for your goals and obtain more focused information on your store’s performance. If you feel this does the trick, then building a customized Shopify sales dashboard may be unnecessary in your case. To find out more about Shopify’s native analytics, take a look at our detailed guide to Shopify reports.
However, some goals require more flexibility than Shopify can offer. For instance, if you want to calculate your net sales with the shipping fees deducted, you may face unexpected obstacles. Although the task seems rather simple, even such minor customization can be a problem for Shopify reports. Let’s take this case as an example and see what you can or cannot do when you want to tweak your metrics a bit.
How to remove shipping fees from sales on the Shopify dashboard
By default, the Shopify overview dashboard in the Analytics section shows you the Total sales metric. This number is the sum of your net sales, taxes, and shipping fees. Sadly, native functionality doesn’t allow you to customize metrics on the overview dashboard.
However, you can still exclude shipping fees from your sales reports.
- Click View report in the upper right corner of the Total sales section on the Analytics dashboard.
- Then find the Edit columns button on the right below the graph, and unselect Shipping.
Now, this column won’t be shown in your report anymore (but this won’t affect the numbers on the overview dashboard). With other sales reports, you can remove the Shipping column in the same way.
Some sales reports don’t include shipping fees by default. For example, the Sales by product report or Sales by product variant SKU report. Since one order can contain several products, the proportion of shipping fees for each product separately is not calculated. For the same reason, the shipping tax is also not included in these reports.
The limitations of Shopify sales reports and overview dashboard
- Shopify doesn’t have a separate sales dashboard
- Native sales reports have a limited number of metrics
- You cannot perform calculations or analyze your data on Shopify
- You need to open and adjust each sales report separately to get the picture you need
- You cannot save your report settings if your plan is lower than Advanced. Instead, you’ll have to manually customize each report from scratch every time you use it
- Some plans don’t give you access to all sales reports
- Native sales reports can contain mistakes. For example, if you edit an already existing order or change a product description, sales reports will show the initial version and the edited one as two separate entries
- The general overview dashboard has only four sales metrics, and you cannot add any other sections to the dashboard
- Customization options for the default sales reports are very limited
As you can see, native Shopify reports and overview dashboard may not be a perfect option for everyone. Luckily, you can export your Shopify data and create a tailored sales dashboard with external tools.
What tools to use to build a custom Shopify sales dashboard
There are many solutions that can help you create a Shopify sales dashboard from scratch.
- Spreadsheet apps – Excel, Google Sheets. With these tools, you can perform advanced calculations and analysis, create new metrics, and fine-tune the numbers in your dashboard. Besides this, their native visualization functionality allows you to build informative dashboards without much effort.
- Data analytics tools – BigQuery, Databricks, Cloudera. These tools can give you valuable insights into your store’s sales processes, your customer’s preferences, and more. For example, if you export Shopify to BigQuery, you can leverage in-built machine learning mechanisms to analyze your data in-depth and even predict future trends.
- Data visualization tools – Data Studio, Tableau, Power BI. Once your analysis results are ready, you can use these specialized apps to build pro-level dashboards with advanced visual representation.
Building a sales dashboard for a Shopify store in Google Sheets
This is one of the best and simplest ways to create a custom Shopify sales dashboard. Google Sheets is a free tool, it’s easy to use, and it allows you to analyze, visualize, and share your sales results with others.
There are three main stages to complete.
- First, you’ll need to import your Shopify data to Google Sheets and automate data refresh.
- Then, you may want to manipulate your data (to apply formulas, create new metrics, or perform analysis).
- And finally, you’ll need to present your data in a visual format. Let’s see how we can do this and get an informative sales dashboard like this one.
Export your Shopify sales data
First, let’s create a new spreadsheet, add several blank sheets, and then name them. For example, you can name the first sheet Sales Dashboard, the next ones – Metrics, Orders with line items, Customers, Products, and so on. This will make working with your data easier. The number of sheets and their content depends on what you want to see on your dashboard. You’ll be able to add more sheets later, if necessary.
Now, let’s transfer your data. Shopify has a native feature for exporting information as a CSV table. But we will use a much better option. We’ll show you how to export Shopify to Google Sheets regularly on a set schedule. This way, the sales data in your spreadsheet will always stay up to date, and your sales dashboard will change accordingly. We can easily achieve this by using Shopify integrations by Coupler.io.
Coupler.io is a data automation and anayltics platform that provides an ETL solution to transfer data from around 200 sources to Google Sheets, Excel, and BigQuery. Coupler.io has many useful features, and one of them is Automatic data refresh. It imports your information to the destination point at the specified time intervals. Besides, with Coupler.io, setting an automated integration is fast and simple.
First, sign up to Coupler.io with your Google account. This will only take a couple of clicks. Once this is done, press Add new importer and select Shopify as a source and Google Sheets as a destination. Click Proceed.
Select Microsoft Excel as a destination If you want to connect Shopify to Power BI.
Then, name your importer – for example, My Shopify Sales. Now, we are ready to set the Source parameters.
Click Connect to connect your Shopify shop to Coupler.io. You’ll need to enter your shop name in the format
shop-name.myshopify.com and log in to your Shopify account if haven’t done before.
Then, choose a data entity to export. In this example, we will export Orders with line items. This will allow us to calculate some useful metrics for our Shopify dashboard. Later you’ll be able to import other types of data as well. Select Orders with line items from the dropdown menu and press Continue.
Now, you see the menu that allows you to filter your data before exporting it. For instance, you can export all data starting from a specific order. You can also select the time range, order status, payment status, and more. If you want to export all your data on orders, skip this step.
Connect your Google account so that Coupler.io can transfer your Shopify data there. Click the Connect button, choose your account, then tick all the boxes to grant the necessary permissions, and press Continue.
Now, select the spreadsheet and specify the sheet for data exporting. You can use the spreadsheet we’ve already prepared for this purpose earlier. When it’s done, press Continue.
After this, choose the Import mode you prefer. The Replace mode will delete all previous data (if any) in the specified sheet and insert the newly imported data instead. The Append option will add the new information below the previous entries. In this example, we’ll go with Replace. Select the mode and press Continue.
Please note: you can follow the same process if you want to export Shopify to Excel and create your sales dashboard there. You’ll just need to select Excel as your application in the Destination section, then connect your Microsoft account, and choose a workbook for data importing. All the other steps will be pretty much the same as for the Google Sheets integration.
Now, let’s move to the part that will keep your Shopify sales dashboard always up to date. Toggle on the Automatic data refresh feature, and Coupler.io will start fetching your updated information from Shopify to Google Sheets according to your schedule. This way, metrics in your worksheet will stay up-to-date, and your sales dashboard will automatically reflect the changes.
First, choose the interval for the updates from the dropdown menu. With a Personal or Squad plan, you can choose between an hourly, daily, or monthly refresh. The Business plan allows you to update every 30 or 15 minutes.
Then, specify the days of the week when you want your data to be refreshed. You can also select the preferred time range and your time zone.
After this, press Save and run.
Great! The first portion of your sales data is already in your spreadsheet. It has appeared on the Orders with line items sheet, which we created earlier.
Transform your data
Once your data is in Google Sheets, you can perform analysis or other manipulations you need. You can apply various formulas or filters, calculate your profits, determine the most promising locations or most popular products, and so on. Just decide what metrics you want to see on your Shopify sales dashboard.
Let’s take a look at the metrics for our dashboard and the formulas we use to calculate them. We’ll mainly use the data from the Orders with line items sheet, which we populated earlier. The metrics will be calculated on a separate sheet. This way, your results, formulas, and filters won’t disappear when the sheets with raw data are automatically refreshed.
Please note that it’s better to check the field names in your datasheet when applying the formulas. Also, if you want to add other metrics, just change the formulas accordingly.
|Total sales||The sum of all rows in the current_total_price column on the Orders with line items sheet|
|Taxes (total)||The sum of all rows in the total_tax column|
|Net profit||Here, we deduct additional costs from the Total sales value:[total_tax] – [shipping_lines.price] – [current_total_price]|
|Average order value||The average value of all rows in the current_total_price column|
|Sales by product||Here, we’ll search the line_items.title and line_items.quantity columns to filter orders by specific product. Then, we’ll use the =SUM function to calculate the total sales for each product. Please see the details below|
|Orders by location|
– – –
|In this case, we’ll use the billing_address.country column without additional manipulations. This way, the map will show where most of the orders come from|
Here’s what our sheet with the calculated metrics looks like.
Importing additional data
To get all the information for these metrics, you need to import another data entity, Orders with shipping lines. It will be necessary to calculate the Shipping price for Net profits. Just go to My importers and find your Shopify dashboard importer on the list. Press the three dots on the right, and click Copy.
Now you have a copy of your first importer with most of the fields already pre-filled. Click Edit in front of the Source and choose Orders with shipping lines as the data entity. Then, select another sheet in your Destination settings. This way, new data won’t be written over the previous contents of your spreadsheet. Hit Run. That’s it!
Calculating Sales by product
For most of the metrics on our list, one simple formula will be enough. With the Sales by product, we’ll need to use a couple of functions. Let’s take this case as an example and explore it in more detail.
On the Orders with line items sheet, you can see how many products of different types were sold. But, in the line_items.title column, all products are mixed together.
To separate them, we’ll use this formula:
=FILTER('Orders with line items'!EY:FC,'Orders with line items'!FC:FC="dress")
'Orders with line items'!EY:FC is our range,
"dress" is the keyword, and
'Orders with line items'!FC:FC is the column where we need to search for this keyword. Just change
"dress" to another keyword and filter by any other product you need.
Insert this formula into an empty sheet to return the results there. Here’s what we’ve got.
Now, we can get back to the Metrics sheet and use the simple =SUM function to see how many dresses we sold:
We’ll need to repeat this for other products as well, and then we’ll get our numbers:
Keep in mind that you’ll only need to do this once. After this, it’ll be enough to just add new products. As an alternative, you can filter by line_items.product_id, line_items.sku, or other parameters.
Visualize your metrics
First, we’ll need to decide what visual elements we want to include and map out their layout.
- For net profit and total sales, we’re going to use scorecards.
- For taxes and average order value, we’ll go with gauge charts.
- Orders by product will be presented as a pie chart.
- For orders by location, we’re going to have a map.
To add a new block, press the Insert chart icon on the Google Sheets control panel.
Then, choose the element you need and place it on your dashboard sheet. Let’s start with a map and scorecards for the main metrics.
Here, we’ve added the map and outlined two scorecards.
- To create a scorecard, just merge several cells and fill the background with a color.
- Type in the titles for your scorecards and connect the value fields to the corresponding cells on the Metrics sheet. To do so, just type “=” in the value cell and jump to the number you need.
- If you want the value to be displayed with the currency sign, highlight the cell with the number and press Format. Then choose Number and Currency for US dollars or Custom currency for another option.
Let’s see how to add some data to the map.
- Click the three dots in the upper right corner of the map and select Edit chart.
- Press the icon on the right from Data range, then jump to the Orders with line items sheet and select the billing_address.country column. The map will reflect this information.
- Customize your map using the same Edit chart menu, the Customize tab. You can change the size, colors, and geographical region.
Now, let’s add taxes and average order value to our dashboard.
- Go to the Chart editor and select Gauge chart.
- Then, click the icon in front of the Data range, jump to the Metrics sheet, and select the cell with the corresponding value. The number will appear on the gauge.
- After this, click Add label. Jump to the metrics page and select the Taxes cell. The title “Taxes” will be added to the gauge.
Then, repeat these steps to create the gauge for the average order value. On the Customize page, you can change the gauge according to your preferences. Here’s what it can look like.
Now, let’s build a pie chart showing sales by product.
- Go to the Chart editor and select Setup, then scroll down and choose the pie chart. We’ll go with a 3D pie chart, but it can also be a donut or regular pie chart.
- Press the Select data range icon in front of the Data range field.
- Then, jump to the metrics and select the data you need.
- Press Enter and meet your chart.
- Customize the chart if necessary. Let’s add labels to the pie slices to make the chart more informative. Go to the Customize tab, then select the Pie chart menu, and pick the label type from the Slice label dropdown menu. We’ve selected Value, so now we see how many items were ordered in each category. We’ve also turned our 3D pie chart into a donut by adjusting the Donut hole percentage.
- Also, you can add a title so that it’s clear what the chart represents. Go to the Chart & axis titles on the Customize tab. Type in the title for the chart in the Title text field and adjust the font size and color. That’s it!
Let’s take a look at the final result.
And the best thing about it – with Coupler.io, your Shopify sales dashboard will be regularly updated without any manual effort.
Of course, this is just an example, and you can do much more with Google Sheets native functionality. You can add various bar charts, scatter charts, tables, different maps, complex combo charts, treemaps, and timelines.
You can even import data from other sources to the same worksheet and create a cross-channel sales dashboard. So, basically, your sales dashboard can be as complex and detailed as you need it to be.
In addition, you can easily share this dashboard with others without giving anyone access to your shop.
How to create a Shopify dashboard for specific product sales in Google Sheets
If you need a sales dashboard that is more product-centered, you can do it in Google Sheets as well. The tool gives you lots of customization options so you can easily make specific product sales its primary focus. To build such a dashboard, make sure to export all product-related data you have in Shopify. When transferring information with Coupler.io, you’ll need to choose such data entities as Products, Inventory, and Orders with line items.
If you have other sources with product information besides Shopify, you can use it for your dashboard as well. Coupler.io can help you transfer your data from more than 20 systems. This includes Salesforce, Dropbox, Pipedrive, Airtable, Slack, and others. You can have your data from all your sources delivered automatically to the same worksheet where you build your sales dashboard. This way, you’ll be able to analyze your data across different systems and get a more granular view of your sales. See the list of the available Google Sheets integrations to check if your other data sources are included.
How to build a Shopify dashboard with high sales volume
If you need to analyze and visualize high sales, you will probably want to see a bigger picture on your dashboard. This way, you won’t get lost in too much detail and will be able to spot important trends and emerging changes. In this case, keep in mind that your dashboard quality will heavily depend on the analysis quality. With high sales, you’ll likely have huge amounts of data, so it makes sense to analyze it in-depth.
For this purpose, you can use BigQuery. This is a powerful tool for advanced analysis. It will help you obtain valuable insights and better understand your shop’s performance and prospects. BigQuery’s in-built machine learning algorithms can help you model future business scenarios and determine the best strategy.
You can transfer your data from Shopify to BigQuery using Coupler.io. The process will be rather similar to the one we’ve described above for the Shopify to Google Sheets importer.
Once your analytics is ready, you can connect BigQuery to Google Data Studio and build a pro-level dashboard with advanced visualization. You can read more about this in our article, How to Visualize Shopify Data in Data Studio.
Building an interactive Shopify sales dashboard is simple
To sum up, Shopify doesn’t have a built-in sales dashboard, and its native sales reports lack flexibility. If you need to customize them, your options will be very limited, and many parameters cannot be changed.
On the other hand, we’ve shown that anyone can easily build a comprehensive Shopify sales dashboard using such external tools as Google Sheets or Excel.
This approach allows you to analyze your sales data, calculate new metrics, and create a custom dashboard according to your preferences. You can use the spreadsheet tools’ native functionality to visualize your data with a variety of graphs, charts, maps, and more. And if you use Coupler.io to export your sales data from Shopify, the metrics on your dashboard will be updated automatically. Just turn on Automatic data refresh, and Coupler.io will keep fetching updated information to your worksheet. Your sales dashboard will adjust accordingly, always showing you the latest news. If you want to go even further and create more complex visual representations of your data, you can connect Google Sheets or Excel to Google Data Studio and build your dashboard there.
We hope this guide was useful to you. Good luck with your sales!Back to Blog