Hello everyone,
I am currently trying to get the total revenue of a store in a specified time. But I cannot find any API that can do that. There is only one API that reads the value of orders. I have tried reading the orders and then adding them together. But this is very time consuming. Because we have customers who can have 10,000 orders a day. If anyone has any good methods, please let me know.
1 Like
How did you end up resolving it?
For Club of Merchants we ended up doing exactly that.
Request access to sensitive information, customer orders.
Importing all of them into our database.
Calculating the revenue based on that data.
We shopped around for other approaches, but as of now there doesn’t seem to be one.
To handle loads of orders, you just need reliable infrastructure. We used Gadget.dev for it.
You could also test and optimize how to run the calculations. Either rhythmically, like once per day/month, etc. Or immediately during order import.
Hi @Ingu_Jeon! Good timing on this question - the answer’s different now than it would’ve been when originally posted.
As of October 1, 2025, you can use ShopifyQL through the GraphQL Admin API to get aggregated revenue without iterating through orders. The sales dataset is pre-aggregated so it’s way faster than summing individual orders:
{
shopifyqlQuery(query: "FROM sales SHOW total_sales SINCE -30d") {
tableData {
columns {
name
dataType
displayName
}
rows
}
parseErrors
}
}
You can adjust the time range with SINCE and UNTIL operators, or use specific dates. Full ShopifyQL syntax reference is in the Help Center.
When this was originally posted (December 2024), ShopifyQL wasn’t available via API yet, so iterating through orders was the only option at the time!
3 Likes
Amazing! That was missing before, and we suggested it multiple times, because it just makes sense to have that in a sales query.
As we were validating revenue, we wanted to make sure that this is actual revenue and not just a lot of unpaid draft orders that were not associated with real customers.
How does this query handle those?
And is this query possible without the sensitive data exception? That was one of our other gripes previously.
Agreed - it can definitely save a lot of leg work for the more analytical API use cases!
For draft orders and payment status - the sales dataset is designed as a ledger of “sale agreements” between the shop and customers, not strictly completed revenue. According to our internal docs, it reports when there’s a “reasonable belief that the value will be exchanged” but doesn’t wait for actual payment or fulfillment. This means it includes pending and unpaid orders. What’s less clear from the docs is whether draft orders (before they’re completed/converted) appear in the dataset at all. Let me check with the analytics team internally to confirm the exact behavior around draft orders and get back to you on that .
For the protected customer data requirement - unfortunately no, you can’t avoid it. The shopifyqlQuery docs explicitly state it requires both read_reports scope AND Level 2 protected customer data access (name, email, address, phone). This requirement exists because ShopifyQL datasets include customer-segmented data (billing regions, addresses, etc.) even in aggregate queries, so Shopify requires the permissions regardless of whether you’re querying individual customer details or just totals.
The only way to avoid it is to stick with the original approach of querying orders with just read_orders scope and summing manually - which, I suppose, is what we were trying to avoid in the first place.
2 Likes
Thank you for the update and for checking with the team.
I’m interested to see what they say.
I had hoped, that with the aggregate sales data the Level 2 protected customer data access is not necessary, but I understand the reasoning.
Counting orders one by one is not pretty, but it was an acceptable workaround. But it was annoying, that we had to request the protected customer data access, even though we only wanted to know order amount, not any names or addresses.
Got confirmation from our analytics team on the draft orders question - they don’t appear in the sales dataset until the sale is actually made. So if you have open draft orders sitting there waiting for payment, they won’t show up in your FROM sales queries. Once the draft order is completed (converted to a regular order), it’ll be included.
That means the sales dataset is really capturing “completed sale agreements” rather than just any order-like object in the system.
Let me know if that clears things up or if you have other questions about the dataset behavior!
1 Like
Thank you for that information. That is already much better.
I would love to query data that only accumulates full paid orders or the ones only paid through official payment gateways, excluding manual payments.
I hear you! The sales dataset doesn’t expose payment gateway or financial status as filterable dimensions - you can only filter by things like sales_channel, billing_country, etc. The dataset is designed to capture completed sales, not intermediate payment states.
Your best option is using the GraphQL orders query with search syntax to filter by payment gateway and aggregate yourself:
query {
orders(first: 250, query: "financial_status:paid AND NOT gateway:manual") {
edges {
node {
id
totalPriceSet {
shopMoney {
amount
}
}
paymentGatewayNames
}
}
pageInfo {
hasNextPage
endCursor
}
}
}
You’d paginate through all results and sum the totalPriceSet.shopMoney.amount values yourself. The NOT gateway:manual filter excludes manual payments.
The analytics datasets are intentionally simplified - they don’t expose every field from the underlying order data. For custom reporting with specific payment filtering, the orders GraphQL API is probably your better option. Having said that, the analytics team are always looking for feedback so I’ll be sure to pass on your use case!
1 Like