Help: Need to get Most popular items sold on sales report,

[franorio]

Hello everybody,

this is an example of a sales report i get from shopify, of different beers & products that are sold on internet. I would like to have visibility of wich products are sold at the most. (¨Lineitem name¨ column is the product, column before quantity bought).

Thought on creating a tree map, with subtotal as values, but the problem is subtotal is only on the first row affecting just 1 product, and not all of them (the ones that where bought together, with the same # of order, under column Name). The other problem of the tree map, there are like 1000 different products, so it's really a mess. 

Any ideas for creating a formula for the most popular sold items? For example would like to see the 10 most sold products.

To consider, the report has orders with ¨Fullfilled¨ & ¨unfulfilled ¨, should have visibility only of the ones that are fulfilled, same problem, ¨fulfilled¨ status is only on the first row, not for all products with the same # of order. 

Is there a way to add a new conditional column for example if order #6910 is once with status fulfilled, al #6910 orders should also get fulffilled status???

It would be also great to have a way to analyze the marketing mix, which products are most commonly bought together. Any ideas?

Thanks & Regards

[dkay84_PowerBI]

Regarding you marketing mix question, there is a custom R visual you can use to do an association rules analysis:

https://app.powerbi.com/visuals/show/PBI_CV_3EEA6E26_A754_4ECA_BE51_C93859851FB2

https://community.powerbi.com/t5/R-Script-Showcase/Association-Rules/m-p/58491

[franorio]

thanks @dkay84_PowerBI i've already downloaded the visual and i'm currently installing all R packages needed, I'm going to give it a try. Have you ever used it before?, do you have any insghits on how should I used my data in order to get the measures i need?

Thanks & Regards!

[dkay84_PowerBI]

I have used this before, and it is a much easier solution than using your own R code, but perhaps not as good looking (but the result is essentially the same from an analytics perspective).

For some background on association rules mining, take a look at the following:

http://www.kdnuggets.com/2016/04/association-rules-apriori-algorithm-tutorial.html

You need to set up your data so that each transaction has one record (entire basket is in one column with another column for basket ID number).  Then, you need to split the basket into a left (LHS) and right (RHS) hand side (the link above will explain what this means).  Then, the visual will ask for the LHS and RHS fields and the ID field, and then it will map the rules.  You can change the threshold levels for support, confidence etc.

Regarding the specific measures you discussed earlier, this will not do that.  What you are describing is DAX formulas specific to your use case and business logic.  The apriori algorithm looks for what products are associated with what other combination of products, and can help your business with cross-selling.  For example, you identify (from the association rules) that Product A is often bought with Product X and Y, so you can think about placing those near eachother in the store/website so that users will purchase them.  If you combine this with profitability, you can better understand not only what products are sold together, but what combinations make you the most money, and thus try to identify the most profitable cross selling opportunities.  This can be used for customized offers, especially in real time environments (like a customer adds Product A to their cart so they get a coupon for product X and Y).

[franorio]

Hi @dkay84_PowerBI it's been a while, I was working on other project so I couldn't move on with the association rules. 

Have been looking arround, could you please help me understand which data should be placed in each category? Been trying different alternatives, but nothing looks convincent. Trying to understand, which products are bougt usally together in same orders, or in returning customers. Which field should I use for each LHS or RHS

Thanks!

Best Regards!

[franorio]

Hi @dkay84_PowerBI it's been a while, I was working on other project so I couldn't move on with the association rules. 

Have been looking arround, could you please help me understand which data should be placed in each category? Been trying different alternatives, but nothing looks convincent. Trying to understand, which products are bougt usally together in same orders, or in returning customers. 

Thanks!

Best Regards!

[dkay84_PowerBI]

Another option to see combinations of products can be seen here:

https://community.powerbi.com/t5/Desktop/Generating-a-matrix-of-interactions/m-p/115389

[v-qiuyu-msft]

Hi @franorio,

#1. Any ideas for creating a formula for the most popular sold items? For example would like to see the 10 most sold products.

In your scenario, you can create a measure use RankX() to rank subtotal for each product group: https://powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/ .

#2. Is there a way to add a new conditional column for example if order #6910 is once with status fulfilled, al #6910 orders should also get fulffilled status???

I think you already found the answer here: http://community.powerbi.com/t5/Desktop/Help-cracking-the-formula-for-new-column/m-p/137438#M59026 .

#3. It would be also great to have a way to analyze the marketing mix, which products are most commonly bought together. Any ideas?

I'm not clear which column indicates mix marketing, if possible you can clarify the requirement.

Best Regards,
Qiuyun Yu

[franorio]

Hello @v-qiuyu-msft

And thanks for your reply and all of your help on the first two issues, definetely i'm going to take a look at the RankX formula.

About the Marketing Mix i do not have a special column for it.

With the actual columns I have on my report as: # of order, client, region / city, items bought by client, quantity of each item, category of item, price, etc.

I am interested in having visibility of how an order is compounded, over 40k orders, not interested in seeing each order, but how most of them are frequently compounded, to find patterns ... and adding filters as per country, by region, by city, by gender , By age, etc. to take strategic desisions with this information.

What I mean by marketing MIX would be creating some measures to get something like the following info:

• Mix of order in units (how orders are compund, for example by category: 20% category A, 20% category B, 60% ...)

• What % of the orders are packs and which ones are self-made (selected)

• Do returning customers have the same behavior or is it different? Example: the clients who are buying packs, do they always buy packs? Or are they also buying merch, self-made, etc? The clients who purchase for example merchandising, do they always buy merchandising or are they buying something else? What do they buy later?

• Typical basket or star product by State / Region

Also would be awesome having some analysis on the customers behavior:

Purchase behavior according to day & hour (Saturday afternoon there are more beer purchases than other category or other day)
Weekday behavior (Mon-Fri) vs. Weekend

Any ideas & help more than welcome!

Thanks again