Comparing Order data to Sales Summary Report

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

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.