Market Basket Analysis (Association Rule Mining) With Excel

Подписаться 75 тыс.

Просмотров 51 тыс.

50% 1

Market basket analysis (aka association rule mining) is a wildly useful skill for ANY professional. In this video I demonstrate how to do a market basket analysis with Microsoft Excel - no VBA code required!
In this video you'll learn how to use Excel's Solver to implement association rule mining to conduct a powerful market basket analysis using the famous Titanic machine learning dataset.
☕ If you found this content useful and would like to support the channel, you can buy me a coffee: www.buymeacoffee.com/DaveOnData
★★★ My online data analysis courses ▶ school.daveondata.com
-----------------------------------------------------------------------------------------------------------------------
Video Resources
-----------------------------------------------------------------------------------------------------------------------
💻 Get the Excel workbook for this video:
github.com/DaveOnData/DataAna...
💥 Learning R programming is easy! 💥
📺 • Learning R Programming...
-----------------------------------------------------------------------------------------------------------------------
Excel Analytics Training
-----------------------------------------------------------------------------------------------------------------------
👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist:
📺 • Introduction to Busine...
Stay healthy and happy data sleuthing!
#MarketBasketAnalysisWithExcel #AssociationRuleMiningWIthExcel #MarketBasketAnalysis #AssociationRuleMining

Наука

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 79

@DaveOnData 3 года назад

💥 Learning R programming is easy for Excel users! 💥 📺 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MNpsyjSuR20.html 👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist: 📺 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xIXymabyFIM.html

@notjakegyllenhaal 2 года назад

I’m a data analyst who just started working for a grocery store, so you can’t imagine how excited I am to watch this. Thank you thank you!!

@timhannafin8054 Год назад

I'm interviewing with one very soon. And i love Excel. This is very interesting stuff and gives me so much insight, confidence, and further learning focus to develop my skills and career!

@nnajiobikachilo6807 9 месяцев назад

Can i talk with you in private...I need your help with sth

@notjakegyllenhaal 8 месяцев назад

@@nnajiobikachilo6807 no

@jtwest8 Год назад

This is incredible and I don't know why we didn't learn this in school (or even in grad school) but all the better to learn from you Dave! Thank you for sharing your knowledge!

@davidbolduc4378 2 года назад

Perfect timing to find this gem of a channel. Intro to Data mining & Intro to Business Analytics and Big Data finals are right around the corner.

@darnelb1912 2 года назад

I love how you solve this type of analysis with Excel cause you can see everything step-by-step. I read and followed an article to do it with R but I didn't know what was happening behind the algorithm and was pretty difficult to understand till now. Thanks Dave!

@kristoferbrown8007 8 месяцев назад

Fantastic content Dave. Very simple, straight forward yet powerful demonstration.

@kisholoymukherjee 2 года назад

David, your content is awesome. Please put up more videos on machine learning.

@pamidimalla Год назад

Great explanation. Subscribed and going to watch all your excel videos Dave. Thanks so much for your efforts in making such great videos.

@hardiktyagi1955 2 года назад

TBH I was a little skeptical to start a 20 min video on this but this was a very efficient way of explaining each and everything. I am really thankful to RU-vid algorithm that I it showed on the top of my list. LIKED, SUBSCRIBED (for all notifications). also hyped to learn R with @david. Thanks Man! Happy new year!

@ltdata5282 3 года назад

Thank you for the explanation! I’m planning on using python for my mba, but seeing you explain it using excel really helped me understand the concept in high level way

@DaveOnData 3 года назад

You are welcome and so glad you found the content useful!

@lotteryplayer 11 месяцев назад

Awesome stuff David. I’ve used countifs in excel to associate two different criterias. One main criteria with a table of others. Then I create another column of total frequencies with a final hit percentage. I used a data link to change the main criteria. Using solver I could dig even deeper. Maybe I’ll look at R to get a full count down from best to second best, etc.

@ThepExcel Год назад

Really Great Example! Thx!!

@AMA_AKAR Год назад

Many Thanks for a great content!

@yemiakinwande7039 3 года назад

Very useful for me as a Business Intelligence Analyst. Awesome teaching, thanks David

@DaveOnData 3 года назад

So glad you found the content useful! Be sure to check out some of my other tutorials (e.g., K-Means clustering). 😁

@ashishyadav7380 2 года назад

great video dave! congrats on the quality work.

@DaveOnData 2 года назад

Thank you for the feedback, much appreciated! 🙏🙇‍♂️

@olasunkanmibabatope9859 2 года назад

Thank you very much for this content

@nealcardoso6528 2 года назад

Great video and explanation of how Market basket analysis works in excel. thank you for posting

@DaveOnData 2 года назад

My pleasure! Glad you found the video useful.

@youdontmatter5085 2 года назад

WOW your AWESOME!!

@eugenioremedi7380 3 года назад

From Argentina... Awesome, absolutely awesome explanation, congratulations

@DaveOnData 3 года назад

Argentina!? Awesome! So glad you found the content useful.

@rassten 3 года назад

This is so awesome, best video on this tupic

@DaveOnData 3 года назад

Thank you for the kind words, very much appreciated! Glad you liked the video.

@shobindas680 3 года назад

Great video David

@DaveOnData 3 года назад

Thank you! Glad you liked the video.

@Nr0991 9 месяцев назад

viele danke

@brentcos9370 2 года назад

Outstanding tutorial! You present very well and your method of teaching is excellent. I have used Python for amateur data analysis but I have not used R. Do you see a big difference between the two? Keep in mind I'm not a mathematician or computer science guy. 👍👍

@dmitrijssulickis3014 3 года назад

Good content, liked it

@DaveOnData 3 года назад

Thank you for the feedback! Most appreciated. 🙏🙇‍♂️

@midwest042003 3 года назад

Gosh Dave! You are just the best!! I wish you could be my mentor. Thanks so much for this. It would be really nice to learn how to do this with R. #TeamR

@DaveOnData 3 года назад

Thank you for the compliment and glad you liked the video. If you're interested in market basket analysis with R, check out this blog post on my website: 💻 www.daveondata.com/blog/titanic-market-basket-analysis-with-r/

@nguyenhoangthinh3881 Год назад

Thank you very much for your Video. It is Awesome. I wonder if you may do a simple video about random forest. Thank you!

@britishrockguy3229 2 месяца назад

David, excellent video, so logical and clear, as with your other videos. I see that you base it on binary values of 0 or 1 but what if the characterstics have multiple values such as a likert based scale or for example such as a person's age?

@britesjd 3 года назад

Hey, very nice video. I would love to see more videos focused on specific analysis techniques, rather than tools

@DaveOnData 3 года назад

Thank you for the feedback, much appreciated! Could you provide some more context on this? Are you referring to video context focused on the techniques/algorithms in the abstract? For example, in this video I don't cover the apriori algorithm that is a common market basket analysis implementation.

@britesjd 3 года назад

@@DaveOnData sure! I was trying to say that the videos that I enjoy the most are the ones that you provide a bit of context on a particular technique/algorithm e do a quick showcase in it`s application. Another example was the "Classification Tree Intuition" video. I really liked this Market Basket Analysis video in particular, because your formalized for me a type of analysis that I've done before, but wasn't even aware that it had a name. So it was particularly useful for me, to have a more formal grasp of the technique and be able to google further into it! Of course, it is just my opinion. I'll keep a look out for your future content either way! Keep it up! 🖤

@DaveOnData 3 года назад

Awesome, thanks for the clarification! I'm thinking you might like my latest video on K-means cluster analysis with Excel: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-YuxwkchSAW4.html

@teranginofficial 9 месяцев назад

Cool

@NARFkarriere3 2 года назад

Thanks for a great video! I actually tried your model to analyze some data from my own company, and set it up with 4-ways lift and 5-ways lift. When trying the 5-ways lift I encountered a problem with the Solver, it actually picked the same decision variable twice. In the Titanic Dataset this could for example be "Female", "Firstclass", "Istriplet", "Female" and "Survived" - that is "female" is choocen twice. Do you happen to know how we can make a constraint that ensure unique decision variables? Thanks in advance for any help.

@joncowan2732 2 года назад

Hey David, Great video! I wanted to point out that you don't mention that the formula for the occurrences needs to be entered as an array and it doesn't show that in the video either. Took me a long time to figure this out and get the formulas to work correctly :/

@simonvanleeuwen7877 2 года назад

Hi David, I am wondering if I can use this technique to segment the market for my thesis. In my survey, I have a lot of categorical variables such as the living area, income (in classes), education, etc. The purpose of my thesis is to research if there is a link between these categorical variables and the interest in the product that my thesis company is selling. Do you recommend using the market basket analysis for this or are there any other data analysis methods that will be suitable for this topic? Ps: Thanks for your very informative videos, I genuinely think your work will help me loads with my thesis.

@ajaykulkarni576 3 года назад

David, Great Content!, best explanation. By the way, what are support and confidence?

@DaveOnData 3 года назад

Thanks, Ajay! Glad you liked the video. In a nutshell: Support - The percentage of transactions in the data with a particular itemset. For example, using the Titanic data, the percentage of passengers that were both female and traveling in 1st class. Confidence - The probability that a transaction has the items on the left-hand side of the rule and also contains the item on the right-hand side of the rule. Take the following rule using the Titanic data: {female, 1st Class} => Survived The confidence is the probability of seeing females traveling in first class that survived.

@user-wo4en7tk3h 10 месяцев назад

Hi David, great tutorial. Q: how can you use the same concept for associations that are in decimals like 0.8, 0.4, 0, 1? and how can you highlight that significance is above 0.7 for instance? is that doable in excel?

@brentcos9370 2 года назад

Hi David, I'm looking at my transactional data where each row represents an order and the columns are the products (107 total columns). For each order (row), I have either a 1 (purchased) or 0 (did not purchase) in every cell. I went through your tutorial and it was great. But what I do not understand is that I do not have a RH column. If your example, you had RH as survived 1 or 0. But I do not have such a column as every row is a purchase so yes, every one purchased something. What am I to do?

@americoalves7408 2 года назад

Awesome, David! Do you have something like this excel for conjoint analysis simulation of scenarios?

@DaveOnData 2 года назад

Glad you like the content! At this time I do not have any content on conjoint analysis.

@shikha1311 2 года назад

Does the table data needs to be in binary form only to conduct MBA? like in thr form of 0 and 1 only?

@wanderingbutnotlost4934 2 года назад

I ran the solver and it came back with Female and HasElderly as the best combo, with a lift of 2.53. Did I do something wrong?

@sethforbes2739 8 месяцев назад

If we only are looking for associations with those who've survived, could we clean up this data set to include only records where survived = 1?

@TheMohitosh007 2 года назад

Hey dave, Amazing Content. Do you have the R code for this analysis as well?

@DaveOnData 2 года назад

Check out the following blog post on my website: 💻 www.daveondata.com/blog/titanic-market-basket-analysis-with-r/

@JasonMinhas 2 года назад

In articles on the same topic I see that Lift is calculated using Confidence, how come Confidence is not being applied here?

@ericfritts5994 Год назад

@salahderdar5148 3 года назад

Great job, but isn't it easy if we use the correlation feature of data analysis?

@DaveOnData 3 года назад

Great question! Did you have a specific correlation technique in mind? I'm thinking that cosine similarity might be better between the vectors (e.g., moving from binary to "product" counts), but then there's the problem of handling all the vector combinations (e.g., a rule using 4 "products" on the LHS and 1 "product" on the RHS.

@salahderdar5148 3 года назад

@@DaveOnData Yes, you're right Thank you David for this clarification

@DaveOnData 3 года назад

My pleasure!

@stanley_wealth1 Год назад

I must confess that learning how to use the software is what gets procastinating to start making soft. It scares lol

@mohamed.montaser 3 года назад

why you didn't explain the formulas in this sheet and the concept behind them, for example the 2 way method formulas and table is not like the 3 way method

@DaveOnData 3 года назад

Great question! Unfortunately, that was an editorial call because I wanted to keep the video from getting too long. That and most folks on RU-vid aren't interested in the math - they just want to get things done. I might follow up with a more detailed video on market basket analysis math later. If you're interested in the math right now, check out the following blog post: towardsdatascience.com/a-gentle-introduction-on-market-basket-analysis-association-rules-fa4b986a40ce

@mohamed.montaser 3 года назад

@@DaveOnData thanks and please make a video about market basket analysis math. you are one of the few content creators in data on RU-vid that can explain quite well with easy example, I started following your posts on LinkedIn then I jumped on your channel here.

@DaveOnData 3 года назад

Copy that, I'll add it to my video backlog! And thank you for the compliment, very much appreciated. 🙏🙇‍♂️

@kurodesu404 2 года назад

how to transform like titanic dataset but i have dataset like this id_transaction products 1 egg,bread 2 bread,milk,peanuts 3 egg,milk, peanuts

@newagedesigntz 3 месяца назад

I dont have solver

@DaveOnData 3 месяца назад

The Solver is an Add-in that is not enabled by default. Check out the following article from Microsoft: support.microsoft.com/en-us/office/load-the-solver-add-in-in-excel-612926fc-d53b-46b4-872c-e24772f078ca

@Johnny-lw1yy Год назад

Hello Smart people of the internet. Can anyone please explain to me why his frequency data set doesn't add up to 100% ??

@kristoferbrown8007 8 месяцев назад

Because multiple features can occur at the same time. Ex: You can be Female, FirstClass and HasChild.

@BlueSkyGoldSun 2 года назад

You encoded your data between 0-1 why ?

@avishekkumardas7738 Год назад

Not enough in detail

@bijayadevelop 2 года назад

To those wondering where Solver Parameters is: support.microsoft.com/en-us/office/load-the-solver-add-in-in-excel-612926fc-d53b-46b4-872c-e24772f078ca