Hi Community,
This is a question about the Shopify data structure in Power BI which is throwing up a few hurdles for us in our attempt to report on both website listings (i.e. products) and sales by date and vendor. I say it’s a problem within Power BI, because none of this is a problem in the Shopify exports; however, there might be something in the Shopify data structure that one of you could advise on, that could help:
Many of our Shopify orders consist of products stored in different locations; one complete and payed for order could consist of a dress listed by one location, and a pair of shoes listed by another. When we try to report on sales amount by location in Power BI, the orders that contain products from more than one location get attributed to all these relevant locations. So, whereas the money for the aforementioned dress should be attributed to location 1, and the money for the shoes to location 2, both sales get attributed to both locations. The overall total sum is still right, because Power BI cleverly automatically dedupes this number by order ID, but we are unable to report accurate sales numbers by location. As far as I can tell, this would be possible if sale amount was somewhere attributed to products, but the only way products are linked to amount is via orders, so the same problem arises.
In the same vein, I also have a problem with connecting products (i.e. items listed on website) to a date filter, and connect the orders and sales amount to a date filter, again because products and orders seemingly are so intrinsically linked within the Shopify data structure.
I hope this makes some sense. If anyone thinks they may be able to help, feel free to ask for more details or clarification if needed.
Are you reporting on the location at the order level where the actual sale happened (like a POS transaction) or the location where the item originated for inventory purposes. They are different types of locations and used for different purposes.
Hi CloudlabSam,
Thanks for replying, and so quickly.
Essentially, I want to be able to do both. A column or table for products listings by location, so where the product was initially listed, and one for sales by location, so where the sale happened. In this case, then, it would be sales by product rather by order. As far as I can tell, there is no way to link the product data to any sales data without also linking it to orders?
Cheers,
Ann
For Sales by Location, you should be able to get all orders for the location along with the sales numbers that exist at the order level. You just can’t include the product level detail, or you will duplicate.
In regards to the sales by product, you need to track all of the sales data at the line item level. This is possible, just not easy. And of course you have to exclude shipping and any order level discounts because those don’t apply at the item level.
I’m very familiar with this as we have a reporting app in the store. We have spent the better part of a year getting this all right.
Hi again,
Thanks again. What you are saying doesn’t correspond to my experience, though. For me, the duplication lies within orders, I assumed because one order contains products from different locations. Even when I deactivate the relationship between products and orders_lineitems_products, which eventually links to orders_transactions, the order sale amount from orders_transaction by vendor contains duplicates.
Also, we do need to be able to report on both product listings and sales, by vendor, ideally in the same Power BI report.
Ann
You may have duplicates by transaction because you are doing an authorization and a capture. You only want the final successful transaction.
Cheers Sam, I’ll investigate that part.
Thanks for your help.