ShopifyQL Query Stopped Working, sales is no longer a valid FROM source

[eivanovue]

Hi there,

I am taking advantage of the Admin API and, more specifically, the GraphQL client to perform ShopifyQL queries. This way, I can extract sales data and visualize it, much like the Shopify Analytics dashboards.

The problem is that I am using the sales dataset/table. However, two days ago, I noticed that I was getting errors from the API saying that the sales dataset is no longer a valid source we can query.

"code": "TABLE_NOT_FOUND"
"message": "sales is invalid FROM source. Use autocomplete to see all available FROM tables."

Here is my query:

from sales show orders, total_sales, total_cost, taxes, returns, shipping, gift_card_gross_sales, total_tips SINCE TODAY

From the documentation, I can see that valid sources are the orders and product tables, which, unfortunately, do not provide me with all the information needed to achieve my purpose.

It seems like the sales table has been deprecated, but I would like to know if this is something permanent. Also, are there any alternatives through which I could fetch the required data from the query above?

Thank you.

[Alaxandros]

I've got the exact same problem. I'm running a graphql query based on the sales tables, then sending out a text message based on the response.

I changed

"from sales show net_sales group by day SINCE """ + start_date + """ UNTIL """ + end_date + """\"

to

"FROM orders SHOW sum(net_sales) group by day SINCE """ + start_date + """ UNTIL """ + end_date + """\"

I think you may be able to change your query to fix that? Something like:

from orders SHOW total_sales, total_cost, taxes, returns, shipping, gift_card_gross_sales, total_tips SINCE TODAY

Maybe you'll need to include the sum with all of them: sum(total_sales), sum(total_cost), sum(taxes), etc.

[eivanovue]

Hi Alexandros, thank you very much for your response. 

I had a quick look in response to your suggestion. I tried to perform on those fields, but I still get back an error that the total_cost gift_card_gross_sales fields do not exist. I am looking at the orders dataset documentation, and they are not specified there, whereas net_sales is. 

https://shopify.dev/api/shopifyql/datasets/orders-dataset

I hope they bring back the sales dataset or at least provide an alternative. I've been looking at changelogs and release notes, but there has been no news of this change!

Thank you.

[shadowlessnotes]

yeah, i've been trying to find a good way to filter product sales by customer_type = first time. It was in the sales table, but not in the products and orders table.

If there's any staff on the threads, kindly let the team know, I would prefer to have the sales table back. Orders/Products dataset seems pretty limited.

[Alaxandros]

Dang... I'm realizing my sum(net_sales) isn't working, the number is vastly different than what Shopify shows on the sales report for a given day, or time period.

Plz bring back sales table my friends at Shopify! WOOOO!

[shadowlessnotes]

I didn't check net_sales, but order_product_quantity works fine using the products dataset (same numbers as the old sales dataset). I think the way it sums/aggregates data is a bit different, so check your WHERE clauses, because my product_price != 0 worked well previously, but now some items don't have a product price, so the numbers get skewed.

[Alaxandros]

Yeah, I'm getting the sense there could be a datetime issue in my "SINCE...UNTIL", because I should be able to compare an identical range with net_sales or gross_sales in Shopify analytics and come up with the same number. I'm using date strings without time to indicate full days, maybe I need to add in the time element. If I can get this working again within an hour I'll be so happy.

[Alaxandros]

Looks like it's not gonna happen. Even the below code returns [['2023-01-23T00:00:00-08:00', 267]]

Whereas Shopify analytics returns

Jan 23, 12:00 AM - Jan 23, 11:00 PM

478

There's clearly a different definition of days/yesterdays/whatever going on, but I'm not sure what it is. Any ideas?

"""{
  shopifyqlQuery(query: "FROM orders SHOW sum(orders) group by day SINCE yesterday UNTIL yesterday\") {
    ... on TableResponse {
      tableData {
        unformattedData
        rowData
        columns {
          # Elements in the columns section describe which column properties you want to return.
          name
          dataType
          displayName
        }
      }
    }
    # parseErrors specifies that you want errors returned, if there were any, and which error properties you want to return.
    parseErrors {
      code
      message
      range {
        start {
          line
          character
        }
        end {
          line
          character
        }
      }
    }
  }
}"""

[Alaxandros]

Update:

After doing some analysis (running the report of orders grouped by hour, along with the order_id), I've realized my orders from "yesterday" end at about 4pm Pacific (which is store time). WHY!?

Why does the query interpret either yesterdays date "2023-01-23" OR the "yesterday" keyword to be up until 4pm store time? It's been a long past couple of weeks. I'm going to scream. And I'm sure it's my fault I just need to find where I'm wrong.

EDIT- After testing orders for "yesterday last year", I've confirmed that the orders table successfully counts the exact same number of orders for last year that Shopify analytics reports. It's only this year yesterday that there's a problem.

Second EDIT- Took a break, walked around. Then ran a report for Sundays orders through the Shopify interface and counted Sundays orders through the graphql orders table. Got the same result. Looks like the issue has only to do with yesterday.

Third edit -- The total net sales for 2 days ago (from the orders table, the products table net_sales is way off) is slightly off from the total net sales  2 days ago according to the Shopify analytics interface.

Everything worked so smoothly when we could access the sales table 😞

[shadowlessnotes]

Welp, I just need the filter conditions that were present in the sales table. 😕

Hope you resolve your date issue, maybe it's how shopify ends a day that is very close to present day. like if you query 'today or yesterday' or since 2 days ago, because it reaches present day...not sure.

[eivanovue]

I think that if you would like to get the total sales, you need some extra calculations. Previously with the sales dataset, the total sales field calculated your revenue - (returns, discounts, tax, and cost of goods sold, I.e., cost per product). I think the net sales field is not taking into consideration some of these values, and therefore you get a different result. It could also be, as you say, a timing issue. 

[ShopifyDevSup]

Hi everyone,

Thanks for all of the comments in this thread. We are gathering this feedback to pass on to our product team.

The Sales table has been removed from ShopifyQL, I would recommend using the admin's sales over time report to access a store's sales data for now.

[shadowlessnotes]

just to clarify, the sales report is not through the api, it's through the admin panel on shopify?

[eivanovue]

I am strictly speaking about the shopifyQL sales dataset in the GraphQL Admin API. 

[brandonyano]

I would like to add that, as far as I can tell, there's no longer a way to get the "total_cost" field that the sales dataset had.

[KevinSchade]

Just to be clear about that, Shopify removed an entire table on ShopifyQL table without even notifying anyone? Should not be an surprise that this is not an ideal workflow

[eivanovue]

Seems like Shopify did, yes. I don't really think it's removed since Shopify Analytics reports use it internally to fetch data, but we no longer have access to it. 

[eivanovue]

Hi,

Thank you very much for your attention. Can you give us a little bit more info on the sales over time report? Can we query this via the API? I have been looking through the documentation and have not seen anything related. Thank you.

[cchitsiang]

if we can perform HTTP call for this endpoint with access token, it will solve the issue as well.

https://analytics.shopify.com/queries?beta=true&dataOnly=true

But the endpoint is called via a analytics token as query param (not sure how to get that as workaround, since analyticsToken is deprecreted)

[eivanovue]

I assume it's an internal API endpoint, but you are right. I think if we are able to call this, it will open the door to even more opportunities for data, i.e., fetching transactions for various payment gateways, which is currently not possible. 

[vbhoj1]

The total returns and shipping still do not match for sales it were matching but not with https://shopify.dev/api/shopifyql/datasets/orders-dataset. Gross sales , discount orders match but not returns shipping

[shadowlessnotes]

Hi,

Is there any other way to get first time customers via the API? It was easy to get sales data by product and sales daterange grouped by customers = first-time in the sales data, but now there doesn't seem to be a straightforward way to do it. Any updates on getting sales table back into API like how it used to work, or a feasible alternative solution would be great, thank you.

[FlorentMonin]

Hello, is there any news on this topic? It has now been more than a year this has been reported, and there are no satisfying ways to automatically get the sales data. I currently have to manually import this data monthly, and I would really appreciate having an alternative to downloading the data from the Custom report in the Website interface.

I can see that there is an internal API used with the following query: 

SHOW orders, gross_sales, discounts, returns, net_sales, shipping, taxes, total_sales, net_quantity, returned_item_quantity OVER day BY order_id, billing_country, customer_id, product_price, product_title, shipping_country FROM sales SINCE -1m UNTIL -1m ORDER BY "day" ASC

Is there any way to get access to this API?

If not, is there anywhere I can track the progress on this issue?

[eivanovue]

Hi,

I completely agree. The Orders and product datasets are very limited. As you say, the best thing would be to get the sales dataset somehow back. Could we get in touch with a staff engineer and request this?

[vbhoj1]

The total returns and shipping still do not match for sales it were matching but not with https://shopify.dev/api/shopifyql/datasets/orders-dataset. Gross sales , discount orders match but not returns shipping

[ComertiseTeam]

Hey! I'm really struggling to receive a successful response. I'd be super interested in talking to you. I'm on +44 7496 898 721. Happy to pay for any time

[vbhoj]

No need to pay.!! 🙂 What is the exact issue you are facing please provide as many details here, and I will try to help.

[ThéoJeanney]

Hello,

I would also like to have access to the "total_sales" that used to exist in the "sales" dataset. Have you found a solution?

Have a nice Tuesday 🙂