Comparing Order data to Sales Summary Report

Questions
1

Hello,

I’m trying to reconstruct the numbers in the Sales Summary Report of the Square dashboard using data retrieved from the V2 Search Orders api, but I’m having trouble getting the numbers to line up.

I found this topic saying that it’s possible to reconstruct the gross and net sales figures from the sales summary report (at least, I assume they were talking about the sales summary report), but it doesn’t go into specifics. Retrieving Gross and Net Sales

I’m using orders that have reached a terminal state (I’m querying square orders with a closed_at date between a given range), but I haven’t yet distinguished COMPLETED and CANCELED orders in my calculations. I think that might help some, but my numbers are frequently less than what I see reported in Square so I don’t think that will solve all of my problems.

For reference, I’m querying all locations’ orders with a closed_at date between 2021-01-31 06:00:00 Etc/UTC and 2021-01-31 23:59:59 Etc/UTC (All locations are in the America/Chicago timezone, and I’m accounting for that when I pull data from square. I believe that the Sales Summary report accounts for timezones, but someone please correct me if I’m wrong. The specific date queried is arbitrary.)

An example of my issue is that I’ve calculated $17.60 in service charges, but the corresponding number in the Square Sales Summary report is $30.10. Other figures like gross sales and net sales can also be over or under depending on location, date, etc. At this point, I’ve taken the time to aggregate all of the numeric fields that I can find on an Order object (based on the docs for the api version I’m using), but I still haven’t been able to find any patterns or clear relationships in the aggregated order data.

Another complicating factor is that I need to break these sales numbers down by the categories of the items sold. When I add up things like gross_sales_money for all line items of all orders in the above query, I end up with a number that’s still different from the “Items” subheader of the “Gross Sales” figure in the Sales Summary report.

Orders for my application are mostly coming in from Square’s Point of Sale, but there are some coming from 3rd party apps too. This thread mentions that orders can differ based on their source: OrderLineItem ommited gross_sales_money filed in response I don’t think that the specific problem in there applies to me, but if anyone can speak to other differences in order objects (or their nested objects) that I should watch out for, I’d appreciate it.

Any advice or information on other things to account for, how different pieces of data within an Order and its nested objects relate to each other, or how Square calculates its Sales reports would be much appreciated.

Also, I don’t think this should matter, but I’m using the 2019-08-14 api version.

I’m happy to offer any clarifications on what I’m trying to do and how I’m doing it at the moment. Thank you for your time and help!

Blockquote

2

Just for net amounts, you can go through all Orders and:

  1. Check if the order has any tenders or refunds:
  2. If it’s not a refund: Add the order->total_money->amount to the total.
  3. If it’s a refund: Subtract the order->refunds->refund->amount_money->amount from the total

That’s it, that should get you your “net sales”. If you want gross sales, then you need to it slightly different:

  1. If the order has tenders, refunds, or is in a COMPLETED state (100% discounted order) then…
  2. Add the following to the total: total money, tip money, and discount money

Let me know if that helps at all.

3

Thank you @sjosey for the reply as I’ve been struggling with this same issue.

I tried to follow your steps very closely and I was not able to get my calculation of gross sales or net sales to match the Sales Summary page.

I actually got much closer to the reported numbers with this method.

  1. Gross Sales: order > each line item > add gross_sales_money to total gross sales
  2. Total Returns: if return_amounts is not undefined then I keep a running total of return amount members, so totalMoney, tipMoney, taxMoney.
  3. Total Discounts: if there are discounts I add up all discount entries > appliedMoney then add that to Total Discounts.
  4. Net Sales: Gross Sales - Total Returns - Total Discounts

I’m trying to get Net Sales, they are usually very close and for some days they are correct so I know I’m just missing a couple attributes. If it is ever off it is usually of by like 25.00 even dollars and the cents match.

Any direction on this would be appreciated.

As an additional note I really don’t understand why there’s not an endpoint for these data points already, clearly square is calculating this on the backend somewhere so it’s a bit ridiculous to need to go to this effort in order to get these numbers from the api.

1 Like
4

I have the same issue as this currently and its frustrating i wish the reports would just be available in the api.

did anyone get a resolve to the Missing attributes that you missing in your calculation ?

5

:wave: We’re constantly working to improve our features based on feedback like this, so I’ll be sure to share your request to the API product team.

Do you have an example of where you’re seeing calculations not total correctly? :slightly_smiling_face:

6

Was this ever resolved. Based on your dashboard it does seem like Net Sales = Gross Sales - Returns/Discounts/Comp.