ShopifyQL

Rubes
Visitor
3 0 1

Hello,

 

I've created a report via my app and I have its ShopifyQL query. So I'd like to know how I can pull the report ( quantity_sold, ending_quantity e.t.c. ) using that query via REST API ( PHP )?

 

Thanks.

Replies 15 (15)

AndyPicamaze
Explorer
41 1 15

I'm just going over the post from the last few days. This is the second one with ShopifyQL related topic and both has no answers. My guess is that's because the answer is NO or it is hidden deeply in the docs. 

https://apps.shopify.com/picamaze
Animated watermarks for product images and ads
rharrigan
Tourist
8 0 3

I too would like like to query the sales data using ShopifyQL via REST for accounting purposes. Why is this not exposed from the REST API?  It has to be less resource intensive than fetching all the orders and manually aggregating the data, as i'm currently forced to do.

Gregarican
Shopify Partner
1033 86 285

You can create a new report that will directly appear in the Shopify web admin. Using ShopifyQL for the query itself, and the Report API accepts REST requests for manipulating the reports. As long as the shop is on Shopify Advanced or Shopify Plus this functionality is available.

Check this out for details --> https://shopify.dev/docs/admin-api/rest/reference/analytics/report#create-2020-07

rharrigan
Tourist
8 0 3

Thanks, but that not exactly the use case I'm describing. We do not want to view the report in the Shopify dashboard. We need to fetch this data for insertion into a separate data warehouse for our accounting team. Hence the need for REST access to the report data.

Bottom line is, we are looking for programmatic access to the results of  the created reports.

Gregarican
Shopify Partner
1033 86 285

Unless I'm mistaken I think your two primary options are the REST API or the GraphQL API. You have more control of the specific fields you need with the latter option. If it's a scheduled task of pulling the data from Shopify and pushing it over to your accounting package then it should consist of a basic API request, where the response then gets pushed over into accounting. If it's a specific requirement for your own Shopify shop then you create a private app, install it within your Shopify shop, and use the API credentials to authenticate within your private app.

rharrigan
Tourist
8 0 3

Yes, but neither of REST API or GraphQL api provides access to running queries with ShopifyQL. Pulling down every order to get a summary of what was sold and and refunded for the day seems absurd, when there's a perfectly good solution already implemented, but limited to dashboard access only. 

Gregarican
Shopify Partner
1033 86 285

Perhaps someone from the Shopify team can chime in here. But as far as I'm aware you can't access the ShopifyQL except via an embedded app SDK. This same train of thought goes back a-ways --> https://community.shopify.com/c/Shopify-APIs-SDKs/How-do-you-query-Analytics-API-without-the-embedde...

Honestly, if it's just a quick pull of daily sales counts and totals then pull via REST of GQL, iterating through the records totaling them up, and pushing over into another receiver isn't that big of a deal. I've implemented that for production apps working with at least 3-4 different provider endpoints. While it's not as efficient as a SQL-type query, it's not terribly ungainly.

rharrigan
Tourist
8 0 3

Yes, I've implemented a process as you've described, getting orders for a given date and summing them. However, I need to also get refunds which apparently are NOT directly query-able by date, one must go though orders. So I can can keep banging my head against the wall trying to account for all of Shopify's business logic by querying raw data.... or they could simply expose programmatic reporting to API users.

HunkyBill
Shopify Expert
4845 60 547

Keep on banging your head. Also, be aware that some Shopify reports are actually not useful for accounting as they have bizarre features. So depending on them might not be in your best interest. 

You say you can't get refunds? They are simply Transactions and they are indeed available, simply by providing the ID of the order they belong to. So it is pretty straight forward to download an order, and all its transactions and have as accurate a source of data as you could hope for. In some cases perhaps, even more accurate than Shopify itself!

For all the work you'd do in accessing reports to feed your system, just working with the actual data is not much more, and it puts you in the driver's seat, where you probably want to be. Will save you the headache of mistakes down the line when you assuming you were feeding the good stuff into maw but weren't.

Custom Shopify Apps built just for you! hunkybill@gmail.com http://www.resistorsoftware.com
Shayne
Shopify Staff
253 19 64

As both Greg and HunkyBill have mentioned, working directly with the data is the preferred solution here.

GraphQL is exceptionally versatile, and it's highly likely that you'll be able to get most of the data that you need with a single query. If you need help building the query, let us know what data specifically you need, and I'll lend a hand.

 

Shayne | Developer Advocate @ Shopify 
 - Was my reply helpful? Click Like to let me 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

Gregarican
Shopify Partner
1033 86 285

The GQL query could be something as simple as this:

query ($numTrxs: Int!, $cursor: String) {
  tenderTransactions(first: $numTrxs, reverse: true, query: "processed_at:>=2020-08-31T00:00:00-0500 AND processed_at:<2020-09-01T00:00:00-0500", after: $cursor) {
    pageInfo {
      hasNextPage
      hasPreviousPage
    }
    edges {
      cursor
      node {
        processedAt
        amount {
          amount
          currencyCode
        }
      }
    }
  }
}

Query variable:

{
  "numTrxs": 10
}

 

So I am pulling 10 tendering transactions at time over the specified create span, with cursor-based pagination if the results exceed 10 records. Counting up the nodes will tell me the total number of transactions. Summing up the positive amounts will tell me the sales, and summing up the negative amounts will tell me the refunds.

 

rharrigan
Tourist
8 0 3

Thanks for the example, that may get me some of the data I need for refunds for a day at least. Will still have to query orders to get cost of goods sold, and break out shipping and tax. In order to calculate profit.  I really don't understand the push-back on getting access to a reporting API or REST/GraphQL access to the Sales objects/table.

Gregarican
Shopify Partner
1033 86 285

It's not push-back at least from me. It's stopping someone from banging their head into a brick wall if that currently isn't an option and isn't on the Shopify road map. Here's a bit longer of a GQL query example that will iterate through the orders, pulling total sales amount, total refund amount, and shipping costs. And then each order iterates through the first 5 line items, pulling the items with their unit cost. Summing all of this up should fit the bill?

query ($numTrxs: Int!, $cursor: String) {
  orders(first: $numTrxs, reverse: true, query: "created_at:>=2020-08-31T00:00:00-0500 AND created_at:<2020-09-01T00:00:00-0500", after: $cursor) {
    edges {
      cursor
      node {
        totalShippingPriceSet {
          presentmentMoney {
            amount
          }
        }
        totalRefundedSet {
          presentmentMoney {
            amount
          }
        }
        totalReceivedSet {
          presentmentMoney {
            amount
          }
        }
        lineItems(first: 5) {
          edges {
            node {
              sku
              title
              quantity
              refundableQuantity
              fulfillableQuantity
              unfulfilledQuantity
              nonFulfillableQuantity              
              originalUnitPriceSet {
                presentmentMoney {
                  amount
                }
              }
              discountedUnitPriceSet {
                presentmentMoney {
                  amount
                }
              }
              variant {
                inventoryItem {
                  unitCost {
                    amount
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}
{
  "numTrxs": 10
}
rharrigan
Tourist
8 0 3

Understood and appreciate the help. Comment on your sample. I had originally used similar approach but had to abandon it because of the query cost, due to the nested items. For example, limiting to 5 lineitems. It's quite reasonable for an order to have more than the 5 lineitems, in which case you'll miss data. But increasing it to a sufficiently large number so as to not lose data, you get penalized by the anticipated query cost. It's a lose-lose scenario.

Gregarican
Shopify Partner
1033 86 285

You can utilize GQL API bulk operations, which are much more efficient that iterating through pages than the REST API. Check out this --> https://shopify.dev/tutorials/perform-bulk-operations-with-admin-api. This is intended for pulling bigger chunks of data, you monitor the progress of the query, and then you are provided an URL to pull the JSONL results.