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.
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 email@example.com to discuss further.
I hope that helps,
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.