No video
Market Basket Analysis (Association Rule Mining) With Excel
Рет қаралды 52,541
Күн бұрын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.buymeacoff...
★★★ My online data analysis courses ▶ school.daveond...
-----------------------------------------------------------------------------------------------------------------------
Video Resources
-----------------------------------------------------------------------------------------------------------------------
💻 Get the Excel workbook for this video:
github.com/Dav...
💥 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
@DaveOnData 3 жыл бұрын
💥 Learning R programming is easy for Excel users! 💥 📺 kzfaq.info/get/bejne/g7SgptybuNu2YmQ.html 👩🔬👨🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist: 📺 kzfaq.info/get/bejne/rq-IrNCSx9-qeYE.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 2 жыл бұрын
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 10 ай бұрын
Can i talk with you in private...I need your help with sth
@notjakegyllenhaal 10 ай бұрын
@@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 9 ай бұрын
Fantastic content Dave. Very simple, straight forward yet powerful demonstration.
@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!
@RichardRebeles 2 күн бұрын
very awesome video great job bro!
@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). 😁
@kisholoymukherjee 2 жыл бұрын
David, your content is awesome. Please put up more videos on machine learning.
@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 KZfaq 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!
@lotteryplayer Жыл бұрын
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.
@ashishyadav7380 3 жыл бұрын
great video dave! congrats on the quality work.
@DaveOnData 3 жыл бұрын
Thank you for the feedback, much appreciated! 🙏🙇♂️
@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.
@pamidimalla Жыл бұрын
Great explanation. Subscribed and going to watch all your excel videos Dave. Thanks so much for your efforts in making such great videos.
@ThepExcel Жыл бұрын
Really Great Example! Thx!!
@eugenioremedi7380 3 жыл бұрын
From Argentina... Awesome, absolutely awesome explanation, congratulations
@DaveOnData 3 жыл бұрын
Argentina!? Awesome! So glad you found the content useful.
@AMA_AKAR Жыл бұрын
Many Thanks for a great content!
@pillslifestylereviews6714 7 күн бұрын
I Finally mangaed to duplicate this in Power BI. Nearly broke me but I feel like I've got superpowers now :) The formula for 3 way lift however is different from the traditional formula seen elsewhere (Number of A,B, C Occurences / (Total transactions * Fraction of A occurrences * Fraction of B occurrences * Fraction of C occurrences)). This Formula used here seems to be (Number of A,B, C Occurences / (Fraction of A Occurances * Fraction of B&C Occurances). Is there a reason for this? I know this is probably TLDR :D
@britishrockguy3229 3 ай бұрын
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: kzfaq.info/get/bejne/j9uoqs6Uzbmlh2g.html
@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 :/
@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?
@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.
@ericfritts5994 Жыл бұрын
This video is great but does anyone know how to organize raw data into a workable table? What do you do to organize the data if you have columns with variables in random order. Using the titanic example, what if your passenger data looks like this for thousands of rows: Passenger 1| single | survived | third class | adult Passenger 2| survived | child | with group | second class
@sethforbes2739 9 ай бұрын
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?
@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.
@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. 👍👍
@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!
@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.
@olasunkanmibabatope9859 2 жыл бұрын
Thank you very much for this content
@Nr0991 10 ай бұрын
viele danke
@shobindas680 3 жыл бұрын
Great video David
@DaveOnData 3 жыл бұрын
Thank you! Glad you liked the video.
@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/
@JasonMinhas 2 жыл бұрын
In articles on the same topic I see that Lift is calculated using Confidence, how come Confidence is not being applied here?
@user-wo4en7tk3h 11 ай бұрын
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?
@dmitrijssulickis3014 3 жыл бұрын
Good content, liked it
@DaveOnData 3 жыл бұрын
Thank you for the feedback! Most appreciated. 🙏🙇♂️
@youdontmatter5085 2 жыл бұрын
WOW your AWESOME!!
@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?
@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.
@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.
@TheMohitosh007 3 жыл бұрын
Hey dave, Amazing Content. Do you have the R code for this analysis as well?
@DaveOnData 3 жыл бұрын
Check out the following blog post on my website: 💻 www.daveondata.com/blog/titanic-market-basket-analysis-with-r/
@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!
@teranginofficial 10 ай бұрын
Cool
@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
@stanley_wealth1 2 жыл бұрын
I must confess that learning how to use the software is what gets procastinating to start making soft. It scares lol
@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 9 ай бұрын
Because multiple features can occur at the same time. Ex: You can be Female, FirstClass and HasChild.
@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 KZfaq 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 KZfaq 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. 🙏🙇♂️
@newagedesigntz 5 ай бұрын
I dont have solver
@DaveOnData 5 ай бұрын
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
@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
BI With Gurpreet
Рет қаралды 12 М.
QAZSPORT TV / ҚАЗСПОРТ TV
Рет қаралды 719 М.
QAZSPORT TV / ҚАЗСПОРТ TV
Рет қаралды 719 М.