"Sales by variant SKU" report all messed up

New Member
1 0 0

My store sells entirely on consignment, so every month I need to report the sales back to the brand we franchise from. I used to be able to basically just dump the Sales by Variant SKU report out, reformat it a little bit and then send it.

But now it's doing stuff that makes no sense. There are columns now for discounts, returns, taxes and total sales, which should be fine, except that what Shopify considers a "return" isn't a return or even a refund. It's taking orders that were cancelled prior to them shipping or even being charged and calling those "returns". That means every month now, I have to go through and look at every individual item, match it up with its order and try to figure out what happened and if we've reported that item as sold previously. (Since Shopify now considers a cancelled order a "return", I'm not exactly sure what it considers a "sale" either.)

This is especially problematic with preorder items, which we have no stock of to begin with. When we have no physical stock, it becomes really abstract. Do we have -7 or -8 of an item? I have to try to match them up with a giant preorder spreadsheet that we keep separately. And the problem is a lot of preorder items end up being sold out at the manufacturer, so we have a lot of cancellations of those orders before they're charged. So we end up with a lot of "0's" and "-1's" on the sales reports now, and I have to go through and verify what happened with each one of those before removing them from the report. (Some things really *are* returned or cancelled after the order's been charged and accepted.)

I really preferred it when this report was much simpler and just showed me exactly what we sold during the month. I feel like orders that aren't even charged shouldn't even be there. If an item was never actually sold or pulled from stock, it shouldn't be on any sales report.

Is there some new way I might not know about to get a report that just shows all the individual items we actually captured funds for during the month? If not, I think there should be some way to do that.



Shopify Partner
77 1 11

What if you filter the report by setting 'Canceled' to 'No'?


SimplyCost - Add costs and track profit (https://apps.shopify.com/simplycost)
Shopify Partner
660 0 116

Hi Jeff,

It's confusing, isn't it?

There are a few things...

As far as Shopify is concerned, an order is confirmed as soon as it is placed by the customer, so even if you cancel it before the funds were captured or the items fulfilled, it is still considered a confirmed order.

That's why it must generate a return when you cancel the order.

Each sale and refund/return is recorded as separate entry so that the full history of events can be traced. It's a little bit like an accounting system. You can never delete things. You always adjust by posting new entries to compensate.

Because the full fidelity history is captured, it can be used to generate different types of reports that would otherwise be impossible to generate.

Regarding the negative inventory, yes it can be confusing. Shopify subtracts the quantity from the inventory at the time that the order is placed, because many shops want to prevent customers from ordering items that are sold out. Of course, in your case, it's not required, and it can be confusing...

Now back to your scenario, the important thing is that if you cancel an order, a reverse entry of the original sale will be posted as a return, and the overall aggregated 'total sales' and 'net quantity' number will even out to 0 (so long as the cancel date and order date are both within the selected date range).

In your case, I don't think you want to filter out 'Cancelled' orders because you mentioned that you can have genuine cancellations.

What you can do is filter by 'Payment status' to show only paid and refunded orders (but no 'pending' ones).

You are not the only one struggling with reporting.

That's why I built an app, Better Reports, that provides many simple reports out of the box, including one called 'Monthly sales by product variant'.

Feel free to give it a try (there is a 14 days trial). I can build a custom report for you that will match exactly your scenario. Also feel free to reach out at clement@betterreports.co to discuss further.

I hope that helps,


Shopify Partner
44 0 3
Hey Jeff,
Did you find out a solution for this? I run Frontflip, we are building a consignment platform that helps consignment businesses like you make it easier to manage and grow their stores easier. 
It integrates with Shopify, and we hope to make a product that addresses all the issues you mentioned you had trouble with.
You can view our site here: www.frontflip.co
With the app you will have everything you need to run a consignment model on Shopify.
I would love to chat and help you out, if you do have this problem. You can reach my anytime at: hello@frontflip.co
74 0 5

Hello @Jeff6

If you are still looking for a solution, then Report Pundit can fetch report of the orders that are open, closed or canceled as well the financial status of the order. 

We also have merchants who sell on a consignment basis use our pre-created and custom reports. 

Report Pundit has a powerful filter feature. You can filter the orders that are paid, canceled & payments pending, etc.


Report Pundit - Instant reports and custom dashboards