Discussing APIs and development related to customers, discounts, and order management.
Hi what I am trying to do, is to match report I am generating based on Orders API vs the one available on shopify, the sales report. My attempts end with a small skew and I need to understand why.
Metric the most important for me is total sales, and returns.
So my formula is that I take all orders for particular created_at field, those with financial statuses not in ('refunded', 'pending') and i sum over total_price. This roughly matches total sales from except for days when there are refunds.
Formula for returns is that I take orders with financial statuses == partially_refunded. for date I use refunds.created.at, and as the amount refunds.transactions.amount.
Next question: how should I understand presentment_currency. Is this only information in which customer "paid", but it does not impact the total sales (from sales report)?
Hey @Dariusz ,
What version of the Shopify Admin API are you using? With the API version 2019-07 and below, it will only accept a single value for financial status to be entered as a query string parameter, i.e. `orders.json?financial_status=paid`. If multiple are specified (i.e. `orders.json?financial_status=paid,pending`) then it will resort to the default behaviour (financial_status is the default "any" value). This may be causing the inconsistencies your are seeing from your reports generated by the Orders API, especially for the days where there are refunds.
However with API version 2019-10 and above, this functionality was updated so that you can specify multiple values for the financial_status query string parameter. So entering `orders.json?financial_status=paid,pending` will indeed only return orders with financial status in ('paid','pending') and will no longer just return all orders.
To answer your next question, "presentment_currency" is the currency that the customer sees when they are on your website ordering your product. For most stores this should be the exact same as the shop's currency, unless you have multi-currency enabled in which case this is the currency in which the customer "paid". In the Orders resource, the total_price attribute should be in the shop currency and not in the customer's presentment currency.
To learn more visit the Shopify Help Center or the Community Blog.
I am able to get all orders with all statuses, so I am afraid this is not the answer I am looking for. So what is your definition of sales actually? Can you tell me what is the underlying query (SQL) of the sales report? When I dig deeper I found out, that line_items that represent gift_cards are not taken into account into this report (or other line_item.vendor). I would prefer to get some official statement, because for now it is more of reverse engineering 🙂
Other thing that I found by trial and error is that if there is an order with status refund, the order.total_price should be calculated with order.created_at. However the refund date must be taken form order.refund.created_at and amount refunded must be taken from order.refund.transfers.amount. Is this correct? Is it the same for partial_refunds, voided?
As for currencies, we have multi-currencies enables. And I have 2 orders for the same day, with values:
Please note that I used currency, not presentment_currency field.
It looks to me that on sales report they are just summed up and not take into account currency (meaning I see 30). Does that mean that it was actually 30 our main shop currency (GBP)? Or there is an error, and the sales report should not sum them up?
Hi @Dariusz
To know what the definition of total sales is, you can refer to this documentation: https://help.shopify.com/en/manual/reports-and-analytics/shopify-reports/report-types/sales-report#c...
In short, total sales = gross sales - discounts - returns + taxes + shipping charges, where "gross sales" is defined as product price x quantity (before taxes, shipping, discounts, and returns)
This documentation also explains that if you sell a Gift Card that it is not included in any sales report, nor the sales number on the home page of the Admin, but it is included in the Gift Cards finance report. So no need to reverse engineer anything, the official Shopify information is all out there already.
Regarding your question on refunded orders, the information you are looking for can be found in the Shopify API documentation for Refund. In short, yes order.refund.created_at is the creation date time of the refund and the record of money transferred back to the customer will be store in order.refund.transactions (not transfers). This will be the same for partially_refunded and voided orders.
Finally, as per the Shopify API documentation for Order, the "total_price" field of an order will always be in your shop currency. This means that regardless of is order 1 was paid by the customer in GBP and order 2 was paid by the customer in EUR, if you are only looking at the "total_price" field of the two orders they will always be in GBP since that is the shop currency. So yes, in your example, it actually was 30 in your main shop currency.
Hope that helps!
To learn more visit the Shopify Help Center or the Community Blog.
I am trying to do the same thing, and I constantly see a 0.1% to 5% discrepancy in Total Sales on each day.
Using the GraphQL output for Orders with the elements below, I cannot figure the formula to get the Total Sales, or the sales excluding returns.
The closest I have is gross sales + discounts + shipping + taxes = total sales - returns ~= sum of (subtotalPriceSet) +/- ~3%. The API documentation says that subtotalPriceSet is "the Subtotal of the line items and their discounts (does not contain shipping costs, shipping discounts, and order-level discounts) in shop and presentment currencies", so I should probably need to add cart discounts and shipping amounts, but in that case I get much worse results.
I do get the same number of orders every day, so I know I do not have a timezone issue. Shop currencies are all USD so that's also something I can hopefully ignore for now.
Any idea on how to get the actual total sales - returns from the GraphQL Order or Line Item objects?
Thanks!!
subtotalPriceSet { shopMoney { amount currencyCode } } totalPriceSet { shopMoney { amount currencyCode } } totalDiscountsSet { shopMoney { amount currencyCode } } totalRefundedSet { shopMoney { amount currencyCode } } totalRefundedShippingSet { shopMoney { amount currencyCode } } totalTaxSet { shopMoney { amount currencyCode } } totalShippingPriceSet { shopMoney { amount currencyCode } } cartDiscountAmountSet { shopMoney { amount currencyCode } }
Any updates to the above, I have the same question.
Hi everyone,
I am also having a similar issue. I'm pulling the Orders through API and comparing it with the Total Sales in Analytics. I'm getting discrepancies with the total as well as per day.
Has anyone figured this out? Thanks
is here anyone who was able to solve this? perhaps some one can share the graph ql for this