Shopify Store Analytics: 7 Questions Every Store Owner Should Answer

Your sales are up on last year. So why doesn’t it feel like it? A sales total can rise while discounts, refunds, slow stock and missed reorders quietly eat the gain, and a single number never tells you which.

This guide walks through seven questions that tell you how your Shopify store is really doing. For each one, it explains why it matters, what to look at and the trap to avoid, then shows how to answer it with a spreadsheet in Mixtable Analytics Spreadsheet. By the end you have one spreadsheet that answers all seven, plus a worksheet of decisions that keeps you honest.

The video above builds the whole spreadsheet step by step. The example store in it, and in this guide, is a small farm-and-home shop with made-up data.

Three rules for reading your numbers

Three rules run through every question:

• Look at net sales, not gross. Gross sales are what customers ordered before discounts and refunds. Net sales are what you actually kept
• Look at profit, not just sales. A product that sells a lot at a thin margin can earn you less than a quieter one
• Compare like with like. Compare the same months last year, and full months only. A month that isn’t over yet always looks weak

This guide sticks to what your orders and your stock can tell you. It skips traffic and ads, which your ad platforms and Shopify’s own reports already show you.

The seven questions at a glance

You don’t need all seven on day one. Start with the question that worries you most and add the others over time.

Before you start

• Install Mixtable Analytics Spreadsheet from the Shopify App Store. It’s free for stores with fewer than 1,000 lifetime orders
• For profit numbers, give your variants a Cost per item in Shopify. Without it, Mixtable can’t work out cost of goods sold (see Shopify cost per item)

Everything below happens in one Mixtable spreadsheet. You create it in question 1, and each later question adds a worksheet to it with the + button at the end of the worksheet tabs.

1. How is the business doing?

Think of the top half of an income statement. Start with gross sales, take away discounts and refunds, and you get net sales. That’s the number to watch.

When net sales move, find out why. Did you get more orders, or did each order get bigger? Compare the number of orders with the average order value: more orders points to more customers or more visits, while a bigger average order points to pricing, bundles or what people put in the cart.

The trap: comparing periods that aren’t equal

This month isn’t over yet, so it always looks smaller than last month. Compare full months only, or compare the same days last year. And a dip against last month may just be the season, which question 6 checks against last year.

Answer it in Mixtable

1. Open your Mixtable dashboard and click Create spreadsheet at the top of the page. If you haven't created a spreadsheet yet, click Browse all templates below the dashboard's suggestions instead. In the Create spreadsheet window, find Monthly Store Performance under Sales reports on the Reports tab, or type its name in the search box, and click it to preview it. Then click Create spreadsheet at the bottom of the window. Mixtable creates the spreadsheet and fills it with data from your Shopify store.

2. The spreadsheet opens on a worksheet with a column for each of the last 12 months, newest first, and a row for each store metric, including Order Count, Order Gross Sales, Total Discounts, Total Refunds Excluding Tax, Order Net Sales and Average Order Value. The first month column is the current month, which isn’t over yet

3. To see how each month compares with the month before, type a label such as “Net sales vs. month before” in column A of an empty row. In column B of that row, type =TEXT(B4/C4-1,"+0%;-0%"), where row 4 is Order Net Sales. Then copy the cell into the other month columns. Each cell shows the change as a percentage, such as +12% or -5%

4. To add the refund rate, right-click the number of another empty row and click Choose what to analyze… at the top of the menu. Under What do you want to analyze?, choose Store. Under Which metric?, select Percent of Orders Refunded, then click Save Row

Read the refund rate of the current month with care. Refunds land in the month they happen, so refunds for last month’s orders show up in this month’s row while this month’s sales are still coming in. That’s one more reason to compare full months.

In our example store, sales grew through the summer, but discounts and refunds grew faster. The last two months dipped, which question 6 explains.

For every row this report offers, see the Monthly Store Performance template.

2. What’s selling?

Rank your products two ways: by net sales and by units sold. They aren’t the same list. A pricey product can top the sales ranking while a cheap one moves the most units, and both matter for different decisions.

Usually, a small share of your products brings in most of your sales. The rule of thumb is 80/20: about a fifth of the products bring about four fifths of the sales. An average per product hides that long tail, so look at the ranking instead.

Put a recent window beside the full year too. A product near the top over the last 30 days but far down over the year is rising. The opposite is fading.

Answer it in Mixtable

1. Click the + at the end of the worksheet tabs. In the Add Worksheet window, click the Reports tab and, under Store data with sales, click Variant margin & inventory
2. Click Next: Columns, search for and tick Vendor, Product type and Tags, then click Next: Rows and Add worksheet. Mixtable adds a Products (with variants) worksheet with a row for each variant, its price, cost, stock, net sales, units sold and profit. You’ll use the same worksheet for questions 3 and 4
3. The report’s numbers start with every order since your store opened. To look at the last year instead, click Change all time periods at the top right of the spreadsheet. Under Over what time period?, choose Fixed dates and the Past year shortcut, then click the button that names how many columns change, such as Change 5 columns
4. Click Sort in the toolbar, choose the Net Sales column, choose Descending order, and click Begin sort. Your best sellers are now at the top
5. Add a running share of sales in the first empty column: each row’s net sales plus everything above it, divided by the total. If Net Sales is column J, type =TEXT(SUM(J$2:J2)/SUM(J:J),"0%") in row 2 and copy it down the column. Where the share reaches 80%, you’ve found the products that carry your store. Sort first, because the share depends on the order of the rows
6. Add net sales for the last 30 days beside it. Click ＋ Shopify data in the header row of an empty column, click Sales & analytics and then Net Sales. Under Over what time period?, choose Rolling period and Last 30 days, then click Save Column. A rolling period moves with the calendar, so this column always shows the latest 30 days

To rank by units instead, sort by Net Quantity Sold.

In our example store, a handful of products carry the store. And one product is quietly taking off: it’s near the top over the last 30 days, but far down the list over the year.

More on this report: Shopify sales report by product and Add analytics columns.

3. What actually makes money?

Gross profit is net sales minus the cost of the goods you sold. A best seller with a thin margin can earn you less than a product that sells a little less, so rank your products by profit, not just by sales.

Discounts cut margin harder than they look. Take a product that sells for $50.00 and costs you $30.00: a 40% margin. Give 30% off, and it sells for $35.00 and earns $5.00, a margin of about 14%. The discount took 30% off the price but almost two thirds off the profit.

Refunds work the same way. A few products usually cause most of them, and each refund takes back the sale but not all of your costs.

Answer it in Mixtable

1. Go back to the Products (with variants) worksheet. It already has Amount of Cost of Goods Sold, Gross Profit and % Gross Margin for each variant
2. Sort by Gross Profit, descending, and watch the order change compared with the net sales ranking
3. Add Total Discount Amount and Percent of Orders Refunded as two more columns the same way as in question 2, each with Fixed dates and Past year. Now you can see which products the discounts and refunds land on

A variant that has no cost per item in Shopify shows No cost instead of a profit. One that has a cost but didn’t sell in the period shows a profit of 0.00 and a margin of No sales.

Know the limits

• Profit needs a cost per item in Shopify. Mixtable uses each variant’s current cost, because Shopify doesn’t keep the cost you paid in the past. If your costs changed, older profit is an estimate
• Some costs aren’t in your orders. Ad spend, payment fees and shipping labels you buy don’t appear in order data. Type them in as your own rows in a worksheet when you want a fuller picture

In our example store, the top seller by sales gets the biggest share of the store’s discounts, and it earns less profit than a quieter product.

More on profit: Shopify profit report: gross margin and COGS by product.

4. What isn’t selling, and what’s about to run out?

Inventory is cash sitting on a shelf, and there are two ways to lose money with it:

• Dead stock ties up cash in products that don’t sell. Look for stock with no sales in the last three months, but judge a new product from its launch date, not before it existed
• Stockouts cost you the sales you could have made. Compare the days of stock you have left with your supplier’s lead time. If the stock runs out sooner than a reorder can arrive, the reorder is already late

The trap: a sell-out looks like weak demand

A product with no stock can’t sell, so its sales drop to zero exactly when demand is strongest. Check a product’s stock before you call it a weak product.

Answer it in Mixtable

1. On the Products (with variants) worksheet, the stock count from Shopify is already there
2. Add Net Quantity Sold twice: once with Fixed dates and Past 3 months, and once with Rolling period and Last 30 days
3. Add two formula columns. Change the letters to match your worksheet: in these examples, E is the cost per item, F the stock, S the units sold over the past 3 months and T the units sold over the last 30 days
   • Unsold stock value: =IF(S2=0,F2*E2,""), the stock times the cost per item, for variants with no sales in the past 3 months
   • Days of stock left: =IF(T2>0,ROUND(F2/(T2/30),0),"No sales"), the stock divided by the units sold per day over the last 30 days
4. Copy both formulas down the column. Sort by Unsold stock value, descending, to find where your cash is stuck. Then sort by Days of stock left, ascending, to find what runs out first

Mixtable also calculates stock metrics for you, from your store’s day-by-day stock history: Days of Stock Left, Dead Stock Units, Dead Stock Value at Cost, Sell-Through Rate, In-Stock Rate and Estimated Lost Sales, among others. Estimated lost sales puts a number on what a stockout cost you. See Shopify inventory analytics and Sell-through rate, inventory turnover and dead stock.

In our example store, the cash is stuck in one vendor’s line of candles. And the high-profit product from question 3 isn’t weak at all: it’s sold out right now.

5. Which vendors and tags drive the business?

Vendors are about supplier conversations and about which brands earn their shelf space. Compare each vendor’s sales with its profit and with the unsold stock it leaves you holding. A vendor can lead on sales and still be the one you should talk to about price.

Tags are your own groups, such as a season, a collection drop or a price band. A product can have several tags, so the groups overlap. Compare tags with each other, but don’t add them up: the total would count some sales twice.

Answer it in Mixtable

1. Click the + at the end of the worksheet tabs, click the Reports tab and, under Breakdowns, click Vendor sales leaderboard
2. Click Next: Report settings, choose Fixed dates and Past year, then click Add worksheet. You get a Pivot Table worksheet with a row for each vendor
3. Profit is calculated per variant, so add it up by vendor with a SUMIF formula that reads the Products (with variants) worksheet. In the first empty column, row 2, type =SUMIF('Products (with variants)'!L:L,A2,'Products (with variants)'!H:H), where L is the Vendor column and H the Gross Profit column on that worksheet. Copy it down for every vendor
4. Do the same for unsold stock value, pointing the formula at your Unsold stock value column from question 4. Divide each vendor’s profit by its net sales if you want its margin
5. Add a Product tag scorecard from the same Breakdowns list, the same way

In our example store, the biggest vendor by sales has the thinnest margin. And another vendor’s slow line holds all the unsold stock.

More on these reports: Shopify sales by vendor, Sales by product tag and Excel formulas for your Shopify store, which covers SUMIF.

6. What’s growing, and what’s fading?

A single period hides direction. Look at the same groups month by month, and compare each month with the same month last year. That’s what tells a seasonal dip, like the one from question 1, apart from a real decline.

Fast-moving products can be read by week, but most stores read best by month. A small store should use longer windows, because short ones are noisy: a few orders more or less swing the numbers.

The trap: small bases

A product type that goes from a handful of sales to three times that shows a huge percentage, such as +200%. It’s still a handful of sales. Put the actual change beside the percentage, so a big percentage on a tiny base can’t fool you.

Answer it in Mixtable

1. Click the + at the end of the worksheet tabs, click the Reports tab and, under Over time, click Product type net sales by month
2. Click Next: Report settings, change the number of months from 12 to 16, and click Add worksheet. With 16 months, the last three full months each have the same month last year to compare with. You get a Time Series worksheet with a row for each product type and a column for each month, newest first
3. Add a row for the whole store: right-click an empty row’s number, click Choose what to analyze…, choose Store, select Net Sales, type “Whole store” as the Row label, and click Save Row
4. Add formulas for the last three full months. If column B is the current, unfinished month, column C is the last full month and column O the same month a year earlier. The change from last year is =C2-O2, and that change as a percentage is =IFERROR(S2/O2,""), where S is the column holding the change. Copy them across the three months and down every row

In our example store, the recent dip happened last year too, so it’s seasonal. One product type is genuinely fading. And one fast grower is a blip from a tiny base.

More on these reports: Time Series worksheets and Sales by product type.

7. Who buys, and do they come back?

Repeat customers are where the margin is: you’ve already paid to win them, and every order after the first costs you less to get. One-time buyers are your win-back list.

Lifetime value should mean profit, not spend. A customer who spends a lot on your thinnest-margin products is worth less than the total suggests. In Mixtable, Customer Lifetime Value is a profit figure: the customer’s net sales excluding tax, minus the estimated cost of the goods they bought.

Answer it in Mixtable

1. Click the + at the end of the worksheet tabs, click the Reports tab and, under Store data with sales, click Customer lifetime value & orders
2. Click Next: Columns and remove any column you don’t need, such as Email if you’ll share the spreadsheet. Then click Next: Rows and Add worksheet. You get a Customers worksheet with a row for each customer
3. Sort by Customer Lifetime Value, descending, to see who’s worth the most
4. Sort by Order Count, ascending, to find the customers who bought only once

Customer Lifetime Value counts only the products that have a cost per item in Shopify, and shows No cost for a customer whose purchases had none.

In our example store, most customers buy once, and the repeat customers are worth far more.

More on customers: Shopify customer analytics and the One-Time Customers template.

Turn the answers into decisions

Answers only help when they change what you do. Give them a worksheet of their own:

1. Click the + at the end of the worksheet tabs, click the From scratch tab and click Blank worksheet. Name it “Actions” with Worksheet > Rename worksheet…
2. Give it four columns: What we saw, What we’ll do, Check on (the date to look again) and Number to watch
3. Write one row per decision
4. Make the number to watch a formula that points at the live number on another worksheet, so it updates by itself. For example, =COUNTIF(Customers!F:F,1) counts the customers with one order, if F is the Order Count column. =SUMIF('Products (with variants)'!A:A,"Pear",'Products (with variants)'!F:F) shows the stock of a product called Pear, if A holds the product title and F the stock. Looking the row up by name keeps the formula right after you sort

For our example store, that’s four decisions:

When the check date comes, the number tells you whether the decision worked.

Make it a routine

A spreadsheet you build once and never open again won’t help. Here’s a rhythm that takes little time:

• Every week, about ten minutes: last week against the same week last year, the products about to run out, and the check dates on your Actions worksheet
• Every month: monthly sales, the profit ranking, vendors and dead stock
• Every quarter or season: redo the best-seller ranking, and review vendors, trends and repeat customers

A few Mixtable tools keep the spreadsheet current with little effort:

• Rolling periods, such as the last 30 days, move with the calendar, so those columns are always up to date
• Fixed dates stay put, which is what you want for a past season or a year you’re comparing against. Change all time periods points every analytics column on a worksheet at a new period in one go. See Analytics timeframes
• The month columns of the Monthly Store Performance and Over time reports are set when you create the worksheet, and only the current month keeps filling in. When a new month starts, add its column with ＋ Analytics data and Calendar month, or add the report again
• Share at the top right invites a partner or your accountant by email, with the access you choose (see Share Shopify data with others). Import & Export > Export to Excel downloads the whole spreadsheet as an .xlsx file

Frequently asked questions

What are the most important Shopify analytics to track?

Start with net sales by month, the number of orders and the average order value, and your refund rate. Then add gross profit and margin per product, unsold stock and days of stock left, and how many of your customers come back. Together they answer the seven questions in this guide.

How do I see profit by product in Shopify?

Give each variant a cost per item in Shopify, then use a report that calculates profit per variant. In Mixtable, the Variant margin & inventory report shows cost of goods sold, gross profit and gross margin for every variant, and you can sort by any of them.

How do I find dead stock in my Shopify store?

Look for variants that have stock but no sales in the last three months, and value that stock at cost. In Mixtable, add Net Quantity Sold for the past 3 months beside your stock, or add the built-in Dead Stock Units and Dead Stock Value at Cost metrics.

Why do my Shopify sales look lower this month?

Usually because the month isn’t over yet, so compare full months or the same days last year. Refunds for last month’s orders also land in this month, and the month may simply be a seasonal low. Comparing with the same month last year tells you which.

How often should I review my store’s numbers?

A short weekly check catches stockouts and slipping sales early, a monthly review covers sales, profit and stock, and a quarterly one revisits your rankings, vendors and customers. The routine above lays it out.