How to generate an inventory report from SQL database using Panoply?

Technical Q&A
1

We are pulling all the Shopify data into a SQL database using a platform called Panoply. From there we are putting data into Tableau for analytics.

I have been able to recreate just about all the metrics we need for reporting, but have not been able to produce any sort of inventory report. See image below as an example of what we are pulling from Shopify.

Inventory Report Example.png
Inventory Report Example.png1318×139 9.62 KB

This is summarized, but we are trying to get raw data for Tableau. I have been able to get a table that shows quantity sold by product, but Have not been able to figure out what tables are used to get starting quantity or ending quantity. EG, I can see things going out, but never coming in. Obviously Shopify has the data somewhere, but I haven’t been able to identify where.

The tables I am using to get items sold are shopify_orders_order_line_items joined with shopify_orders_order.

Thank you!

2

Hi @jon_slalom

In our Tableau Connector we use the fields below. You can try them on your side:

  • Table: ProductVariants → inventoryQuantity;

Screenshot_20220705-150240.png
Screenshot_20220705-150240.png1644×3840 537 KB

  • Table: inventoryItems - inventoryLevels → incoming (for coming in)

Hope this helps,

Anna