How to Find Popular Two- or Three-Product Combinations in Excel
Question details
The user needs to perform market basket analysis to identify which pairs or triplets of products are most frequently purchased together by customers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing customer sales datasets to discover common purchasing patterns and item co-occurrences.
- Observed behavior
- Finding the highest frequency product combinations across the dataset while ensuring duplicate product purchases by the exact same customer are ignored.
Before attempting combination analysis, ensure your dataset is cleaned so that each customer has a distinct ID and any duplicate purchases of the same product by the same customer have been removed.
Use Power Query for Two-Product Combinations
Power Query is highly efficient for transforming sales lists into product pairs and counting their frequencies without needing complex formulas.
Power Query allows you to merge a table with itself (a self-join) to generate all possible combinations of products bought by the same customer, which can then be grouped and counted.
Select your data range, navigate to the Data tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.
Select both the Customer ID and Product columns, right-click the headers, and choose 'Remove Duplicates' to ensure unique customer-product pairings.
Go to the Home tab and select 'Merge Queries'. Merge the table with itself by matching the Customer ID column to create a new column of product pairs.
Expand the merged table, then filter the rows to remove instances where Product A is the same as Product B, and sort them to avoid counting A-B and B-A as separate combinations.
Select your two product columns, click 'Group By', and choose to Count Rows. Finally, sort the Count column in descending order to see the most popular pairs.
Use Helper Columns and COUNTIFS Formulas
For smaller datasets with a limited number of products (e.g., under 100), you can use helper grids and COUNTIFS formulas.
Use Python in Excel for Three-Product Combinations
Because three-product combinations grow exponentially, Python is much better equipped to handle the computational load than standard Excel features.
Analyze Product Combinations Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful data processing tools, advanced functions, and pivot capabilities, empowering you to perform market basket analysis and find product pairs efficiently without complex coding.
- 1. Open your sales dataset: Launch WPS Spreadsheet and open your .xlsx file containing the customer and product data.
- 2. Clean the data: Navigate to the Data tab and use the 'Remove Duplicates' tool to ensure there are no duplicate product purchases for the same customer.
- 3. Summarize with PivotTables: Go to the Insert tab and click 'PivotTable' to reshape your transactional data into a customer-product matrix.
- 4. Calculate frequencies: Use WPS Spreadsheet's built-in formulas like COUNTIFS to identify and rank the most frequently purchased product pairs.
Frequently Asked Questions
Why is finding three-product combinations difficult in standard Excel?
Three-product combinations cause exponential data growth. For example, an inventory of just 100 products yields over 160,000 possible triplets. Standard Excel formulas struggle to process and calculate this volume efficiently, which is why external scripting tools or Power Query are recommended.
How do I ignore duplicate purchases by the same customer before analyzing combinations?
You can clean your data by highlighting your entire dataset, navigating to the Data tab, and selecting 'Remove Duplicates'. Check both the 'Customer ID' and 'Product Name' columns. This ensures only unique occurrences of a product per customer are kept.
What is market basket analysis?
Market basket analysis is a data mining technique used to uncover relationships between items. It helps retailers understand customer purchasing behaviors by identifying pairs or groups of items that are frequently bought together.
Can I use PivotTables to find popular product combinations?
A standard PivotTable cannot generate and list combinations on its own. However, you can use a PivotTable to restructure your transactional data into a matrix format (Customer IDs as rows, Products as columns), making it much easier to apply combination-counting formulas afterward.
