Sales table schema API doc page not working

Adverity_prod
Shopify Partner
8 0 0

https://help.shopify.com/en/manual/reports-and-analytics/shopifyql/notebooks-sales-schema#aggregates

 

This page stopped working and hence we would like to know the available schema for the sales table in ShopifyQL. Thanks!

Replies 9 (9)

ShopifyDevSup
Shopify Staff
1379 229 481

Hi Adverity_prod,

 

Thanks for your post. The sales table has been replaced with the [orders] and [products] datasets so that documentation page has been removed as it's no longer valid.

Developer Support @ Shopify
- Was this reply helpful? Click Like to let us know!
- Was your question answered? Mark it as an Accepted Solution
- To learn more visit Shopify.dev or the Shopify Web Design and Development Blog

Product_ADVRTY
Shopify Partner
9 0 3

Hi ShopifyDevSup,

 

Thanks for your reply.

A sales report containing the following columns can be exported via the Shopify UI:

 

day,utm_campaign_content,utm_campaign_medium,utm_campaign_term,utm_campaign_source,utm_campaign_name,orders,gross_sales,discounts,returns,net_sales,shipping,taxes,total_sales,ordered_item_quantity 2

 

How would you export this via ShopifyQL? I see that neither  the [orders] nor the [products] datasets holds the UTM fields?

Should the GraphQL API be used instead? Would you be able to give an example of this?

 

Thank you !

ShopifyDevSup
Shopify Staff
1379 229 481

Hi @Product_ADVRTY👋

 

The UTM parameters can be found on the order object  as per below. At this time, ShopifyQL only supports the fields listed in the order/product datasets. I'd recommend reviewing the GraphQL `order` object reference docs to locate the connections and parameters as needed.

 

query ($id: ID!){
    order (id :$id){
        customerJourneySummary {
            lastVisit {
                utmParameters {
                    medium
                    source
                    campaign
                }
            }
        }
    }
}

 

 

Hope that helps!

Developer Support @ Shopify
- Was this reply helpful? Click Like to let us know!
- Was your question answered? Mark it as an Accepted Solution
- To learn more visit Shopify.dev or the Shopify Web Design and Development Blog

Product_ADVRTY
Shopify Partner
9 0 3

hey @ShopifyDevSup !

 

So in order to reproduce the following query, that was available via ShopifyQL and the sales dataset about 2 months ago:

 

from sales show ordered_item_quantity, total_sales, net_sales, taxes, shipping, duties, additional_fees, discounts, returns, gross_sales, orders by day, order_name, utm_campaign_content, utm_campaign_medium, utm_campaign_name, utm_campaign_source, utm_campaign_term since -2y until today

 

We would have to query some data from the orders dataset (https://shopify.dev/api/shopifyql/datasets/orders-dataset) via ShopifyQL, then query the UTM parameters for those orders via Graph QL and then join the data?

 

Are you planning to reintroduce the sales dataset in ShopifyQL at some point that would hold the UTM parameters natively?

 

Product_ADVRTY
Shopify Partner
9 0 3

hey @ShopifyDevSup ! 🙂

Were you able to check my previous questions yet? Would be great to understand whether the UTM fields will be made available via ShopifyQL again any time soon ! Thank you!

ShopifyDevSup
Shopify Staff
1379 229 481

If GraphQL has data that isn't available via ShopifyQL at this time, joining the data would be one workaround. Currently we don't have a timeline to share, but I'll be sure to let the product teams know which datasets have the most interest!

Developer Support @ Shopify
- Was this reply helpful? Click Like to let us know!
- Was your question answered? Mark it as an Accepted Solution
- To learn more visit Shopify.dev or the Shopify Web Design and Development Blog

Product_ADVRTY
Shopify Partner
9 0 3

HI @ShopifyDevSup 

We are not running into a problem that when we make the following query, we are running into an API error:

query {{
order(id: "gid://shopify/Order/{order_id}") {{
customerJourneySummary {{
lastVisit {{
utmParameters {{
medium
source
campaign
}}}}
}}

Error

{"data":{"order":null},"errors":[{"message":"This app is not approved to access the Order object. See https:\/\/partners.shopify.com\/846290\/apps\/2581733\/customer_data for more details.","locations":[{"line":3,"column":9}],"path":["order"],"extensions":{"code":"ACCESS_DENIED","documentation":"https:\/\/partners.shopify.com\/846290\/apps\/2581733\/customer_data"}}],"extensions":{"cost":{"requestedQueryCost":4,"actualQueryCost":1,"throttleStatus":{"maximumAvailable":10000.0,"currentlyAvailable":9999,"restoreRate":500.0}}}}

 Would you know what could be the reason why we cannot accses this object?

Thanks

ShopifyDevSup
Shopify Staff
1379 229 481

It looks like you may have missed the message in the error that links to more details on how you can get access to the object: https://partners.shopify.com/846290/apps/2581733/customer_data

Developer Support @ Shopify
- Was this reply helpful? Click Like to let us know!
- Was your question answered? Mark it as an Accepted Solution
- To learn more visit Shopify.dev or the Shopify Web Design and Development Blog

Product_ADVRTY
Shopify Partner
9 0 3

hi @ShopifyDevSup !

Thanks for getting back to us.

We have since been approved by the orders project, but now, we face a different problem as a described here (INTERNAL ERROR): https://community.shopify.com/c/shopify-apis-and-sdks/200-internal-error-with-shopifyql/m-p/1874847#...

Please advise! thank you