Get the last time a product was purchased

[pthieu]

I would like to get all products in a merchant's catalog and get the inventory and last time it was purchased.

I know with the Products API, I am able to get all the products and their inventory and with the Order API, I am able to retrieve all the orders by the user.

With these two pieces of information, I'm able to parse out the information and see when the product was ordered last, but this would quick hit API limits as the store continues to operate or increases catalog size.

Is it possible to get the last time a product was purchased?

[KarlOffenberger · Accepted solution]

Not really using the lowest common denominator of available API calls like orders and products.

If your apps merchant stores are running Advanced plan or higher, you could make use of the Analytics API, specifically ShopifyQL. If not, you'd need to make do with what you mention in your OP. One thing to bare in mind with many such designs is that they often do not need to be real-time or guarantee that the latest data is available - a conscious trade-off to make in order to scale. If you decide you can live with that, you can store/cache most of the data closer to your app.

Also, consider this - say you wanted all orders for product A between a 7 day period prior to the current day. You would most likely already have the data for past 6 days and only need it for today. More over, you wouldn't even need to re-calculate your aggregates because sales wouldn't change in the past - i.e. on Thursday 14 Feb Prod XYZ sold 217x will be the same if I asked today, tomorrow or in 1 year.

So my best attempt at trying to provide a helpful response to your question would be try to be mindful of when you query the API. It's much the same like from the world of RDBMS - just because you can chain 10 left joins doesn't mean they'll perform well and maybe it would be time to de-normalise 😉

[pthieu]

@KarlOffenberger: thanks for the reply. I ended up getting all the orders (I've been approved for the `read_all_orders` permission) and then getting products and comparing the variant IDs to the ones from the Order API calls.

What is weird is that I wrote a loop to get all orders from when a store was created on Shopify, I seem to get everything, but when comparing the `variant_id`'s in the orders received from the Orders API, there are a staggering amount of variants that was returned that didn't show up in an order.

Do you know if:

1. `list_items` from an Orders GET request is not actually the full list?
2. Using `variant_id` in `list_items[n].variant_id` is not accurate?

The only thing I noticed was that there were a lot of `Gift Cards` returned, not sure if that helps.