Statistical Correlation between ordered SKUs

A

Adam G

#1
Hi All,
i have 320 SKUs in my warehouse. Customers place orders for about ~10 SKU on each and there is round 50 orders/day.
I'd like to check if there is say...90% probability when Customer orders SKU 100097 he will also order 100087 (for example) on the same order.

I plan to reorganize my WHS and having rotation ABC done, i'd like to know which SKUs i should place near to each other as they go together often.

How to do that?
 
Elsmar Forum Sponsor

Ninja

Looking for Reality
Staff member
Super Moderator
#2
Re: Statistical Correlation between orderded SKUs

Hi Adam,

Great approach!

If your orders are already generated from a database, contact a local dbase programmer or your dbase maintenance company and ask about "mining" your data to look for relationships.

What you are trying to do is one of the main reasons databases were created...to mine the data looking for relationships and conflicts.

Short of that, poll your order pickers. They probably recognize some of these relationships and you may get a free head start for what you're after. People who do repetitive tasks like order picking tend to "get in a rut"...ask the pickers about the ruts they have noticed.
 

Steve Prevette

Deming Disciple
Staff member
Super Moderator
#3
Re: Statistical Correlation between orderded SKUs

I suspect the best way to do this may be by pair-wise data. If a customer orders A, D, and G in one order, that is an A-D pair, a D-G pair, and a A-G pair. Once you have converted the orders to pairs (this should be accomplishable with an excel macro) then count up the most common pairs.

I don't know if you may also need to look at triples, quadruples, etc. For example, if one customer orders A and D, and a second D and G, and a third A and G, that would be treated the same was as if one customer ordered A, D, and G.
 

Bev D

Heretical Statistician
Staff member
Super Moderator
#4
Steve has it exactly.

there may be an elegent program outthere or perhpas someone can write one for you but you can probably do this manually long befroe the elegant solution is available to you.
 

Steve Prevette

Deming Disciple
Staff member
Super Moderator
#5
Interesting thing - just after answering this question I ended up with a similar question at work about how to group some like results in medical data. Didn't take too long to set up pairs for about 16 items (16 x 15 pairs) and use some array formulae count them up.

320 definitely would require resorting to a macro to identify the pairs, but is do-able in Excel.
 

Statistical Steven

Statistician
Staff member
Super Moderator
#6
Hi All,
i have 320 SKUs in my warehouse. Customers place orders for about ~10 SKU on each and there is round 50 orders/day.
I'd like to check if there is say...90% probability when Customer orders SKU 100097 he will also order 100087 (for example) on the same order.

I plan to reorganize my WHS and having rotation ABC done, i'd like to know which SKUs i should place near to each other as they go together often.

How to do that?
Methods like logistic regression and some multivariate methods might assist in this process. You need to expand the analysis to more than just pairwise comparison and determine if there are any higher order relationships (3 SKUs, 4 SKUs, etc) which is abour ~430,000,000 comparisons (~51,000 just for pairwise comparisons). Supermarkets and Home Improvement stores have been doing this analysis for a long time and it usually requires a data warehouse to run this models. This is NOT a trival analysis. It took 5 full time SAS programmers 2 months to mine a 18 million record with 500 variable warehouse for this associations.
 
A

Adam G

#7
Thanks guys,
I was hoping i can pull the data, load to Minitab, run a fancy test and get it...fool me;)
Will go with some manual calculations i have selected ~60 A rotation SKU, will start with them and let you know results, if any
 

Miner

Forum Moderator
Staff member
Admin
#8
You can reduce the effort by sorting based on volume. The old 80/20 rule still works (i.e., 20% of your SKUs will account for 80% of your volume).
 
Thread starter Similar threads Forum Replies Date
S MSA clause 7.1.5.1.1 - Statistical studies on counting weight scale IATF 16949 - Automotive Quality Systems Standard 4
S How to perform verification of the Statistical Analysis Software? Qualification and Validation (including 21 CFR Part 11) 3
M V&V phase: Justification of acceptance criteria (statistical method ) - (Medical Device) Design and Development of Products and Processes 2
Bev D Statistical Alchemy Misc. Quality Assurance and Business Systems Related Topics 1
K Looking for guidance to write an SOP on Statistical Methodologies? Statistical Analysis Tools, Techniques and SPC 7
M Minimum sample size - Guidance and statistical rationale Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
N Design Verification & Process Validation - Statistical sample sizes Design and Development of Products and Processes 2
John Predmore Interactive visualization through graphical simulation of statistical concepts Statistical Analysis Tools, Techniques and SPC 3
Bev D Profound Statistical Concepts Misc. Quality Assurance and Business Systems Related Topics 63
A Statistical Analysis - Check if these organisms at different concentrations affect the growth of wheat seedlings Using Minitab Software 4
H Statistical Techniques Procedure - What should be included Document Control Systems, Procedures, Forms and Templates 4
O Statistical justification of sampling size in V&V tests ISO 13485:2016 - Medical Device Quality Management Systems 5
optomist1 It’s time to talk about ditching statistical significance Statistical Analysis Tools, Techniques and SPC 6
Marc Steve Prevette's Statistical Process Control (SPC) "Library" Statistical Analysis Tools, Techniques and SPC 0
John Predmore A Balanced view of statistical tests Statistical Analysis Tools, Techniques and SPC 3
V Statistical basis and justification while comparing / changing sampling plans Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 11
S SPC (Statistical Process Control) for Unilateral Tolerance - Questions Statistical Analysis Tools, Techniques and SPC 6
S IATF 16949 9.1.1.3 Application of statistical concepts - Our technicians are quizzed for statistical knowledge IATF 16949 - Automotive Quality Systems Standard 3
K Please help identify appropriate statistical treatment Statistical Analysis Tools, Techniques and SPC 13
ScottK Statistical basis for 30 pieces for FAI 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 7
B IATF 16949 clause 7.1.5.1.1 - Statistical studies shall be conducted IATF 16949 - Automotive Quality Systems Standard 3
A Statistical Process Control and Inspection in Footwear Production Statistical Analysis Tools, Techniques and SPC 0
M IATF 16949 Cl. 7.1.5.1.1 - Statistical studies shall be conducted IATF 16949 - Automotive Quality Systems Standard 3
Steve Prevette Statistical Process Control Library Statistical Analysis Tools, Techniques and SPC 17
Marc Happy Birthday Statistical Steven - 2015 Covegratulations 10
L When are Statistical techniques not applicable? Service Industry Specific Topics 16
M FDA 21 CFR 820.250 - Does "valid statistical" always mean math? 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 6
R Common Statistical Errors Using Minitab Software 1
Y Statistical Analysis of Road Traffic Data Statistical Analysis Tools, Techniques and SPC 11
B Class II Medical Device Manufacturer - SOP for 820.250 Statistical 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 3
E Correct Statistical Test comparing 2 Groups Statistical Analysis Tools, Techniques and SPC 14
A Statistical Software Calibration using Ford's "Sample Calibration File" Statistical Analysis Tools, Techniques and SPC 8
J Defining Martial Arts and Gymnastics Statistical Techniques Statistical Analysis Tools, Techniques and SPC 4
J Capability Analysis - Unusual Statistical Distribution of my Proccess Capability, Accuracy and Stability - Processes, Machines, etc. 5
M PhD Thesis Data Statistical Analysis Methods Statistical Analysis Tools, Techniques and SPC 2
J Statistical Significance and SPC Control Chart Reports Statistical Analysis Tools, Techniques and SPC 9
N Statistical Quality Improvement Action for Small Batch Production Statistical Analysis Tools, Techniques and SPC 17
V Validation of macro - scripts - programs used in statistical software (Minitab-SAS... Qualification and Validation (including 21 CFR Part 11) 5
Moncia Statistical Process Control Crash Course - Question Quality Manager and Management Related Issues 10
H Statistical Models for Predictive Management of Software Processes Software Quality Assurance 2
S Minitab and Crystal Ball Statistical Analysis Software Using Minitab Software 13
M Determining if two different X's have any Statistical Significance on the Y's Statistical Analysis Tools, Techniques and SPC 4
I Statistical Stability for the PQ of Analytical Equipment Qualification and Validation (including 21 CFR Part 11) 1
F Statistical Comparison of Product: High Average vs. Low Range Capability, Accuracy and Stability - Processes, Machines, etc. 13
E Using ANOVA during the PQ Validation Run to evaluate Statistical Differences Statistical Analysis Tools, Techniques and SPC 4
W Gage R&R for gage pins used to inspect a hole ID called a Statistical Tolerance Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
B AS 9100C sec 8.2.4 - " Recognized Statistical Principles" meaning AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 7
R What is PSW - Statistical Process Package + Level 5 APQP and PPAP 7
O Is SPC (Statistical Process Control) always required? Statistical Analysis Tools, Techniques and SPC 4
V Any experience/feedback on statistical software tools...minitab -design expert -JMP Using Minitab Software 4

Similar threads

Top Bottom