Orders API vs Sales Report

Dariusz
Visitor
3 0 0

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)? 

Replies 7 (7)

hassain
Shopify Staff (Retired)
624 104 187

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.

Dariusz
Visitor
3 0 0

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:

  1. total_price: 10, currency :GBP
  2. total_price: 20, currency: EUR 

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?

 

 

hassain
Shopify Staff (Retired)
624 104 187

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.

Dariusz
Visitor
3 0 0
Hello Hassain, thank you for your answers. I still find however one very
wired. I have following data:
1st order: total_price 30.00, currency = GBP, presentment_currency = GBP,
total_price_usd 38.96
"total_price_set": {
"shop_money": {
"amount": "30.00",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "30.00",
"currency_code": "GBP"
}
}
2nd order: total price 24.87, currency = GBP, presentment_currency = USD,
total_usd_price 32.30
"total_price_set": {
"shop_money": {
"amount": "24.87",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "32.30",
"currency_code": "USD"
}
}
3rd order: total price 48.00, currency = EUR, presentment_currency = EUR,
total_usd_price 52.86
"total_price_set": {
"shop_money": {
"amount": "48.00",
"currency_code": "EUR"
},
"presentment_money": {
"amount": "48.00",
"currency_code": "EUR"
}

Given your response, I should accept that my sales are 30 GBP + 24.87 GBP + *48
GBP*. For me it really sounds wired that you provide order "currency" field
and we should discard it. When I look at the data, I would assume 30 GBP +
24.87 GBP + *48 EUR*
nicolas-grasset
Tourist
9 0 1

Hi @Dariusz @hassain 

 

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
                    }
                }

 

 

vbhoj1
Tourist
7 0 2

Any updates to the above, I have the same question.

francis_ilustre
Tourist
6 0 0

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