How to get analytics data from shopify API

[get-cues]

We attempted to find gross sales for a specific date using the Orders API by adding all 'current_subtotal_price' values.

API: admin/api/2023-01/orders.json?created_at_min=2023-02-03T00:00:00-05:00&created_at_max=2023-02-03T23:59:59-05:00&limit=100
However, we get different results from API and website analytics reports.

I also tried shopifyqlQuery for the analytics data, but it returns an empty set.
"FROM orders SHOW sum(net_sales) AS monthly_net_sales GROUP BY month SINCE -3m ORDER BY month"

Please suggest a better way to get analytics data (Gross sales, Discounts, Net sales, Total sales) from Shopify .

[mzwarych]

We are facing a similar issue and I believe the discrepancies come from two main causes:
1) Your Shopify instance is running in a local timezone (check store settings) and API data is always reported in UTC. This requires a simple conversion to your local timezone to match your store as part of your query or transformation. 

2) Return dates differ from order dates. If you sell $100 today, your net sales is $100, but if $50 gets returned tomorrow your net sales is -$50 (all else equal). When you try to aggregate gross sales and discounts you need to do so by order date, but when you try to aggregate returns it needs to be done based on the return (refund) date, which is almost always different than the order date. My approach is to calculate a "report date" field that will choose the right date based on some criteria that I haven't yet fully determined, and use that to aggregate sales info. There may be a better solution. 

Here's an article that explains this fairly well I came across while researching:
https://medium.com/nerd-for-tech/why-shopifys-sales-report-is-not-very-accurate-eaa0cce789ab

Happy coding