Understand Customer Purchase Behavior and Boost Sales with Market Basket Analysis
Delivering a personalized purchase experience is the key differentiator for businesses to increase sales and brand loyalty. Market basket analysis, also called affinity or association analysis, is a data mining technique that helps discover significant patterns and relationships exhibited by individuals while making a purchase.
By identifying the items that occur together,
having adequate stock levels.
Data Requirements
To conduct a market basket analysis, you'll need a transactional dataset that includes the item name, bill number (or order ID), quantity, and date and time of the transaction.
We are using a sample e-commerce store data saved as Orders for illustration. Each row in the Orders data table denotes a transaction.
Preparing the data for analysis
To conduct the market basket analysis, we need to extract the following data from the base table (Orders) using SQL queries.
- A query table that contains all possible pairs of combinations that could occur together.
- A query table to get the number of transactions for combinations that have appeared together or combinations that has been purchased.
- As a final step, merge the all possible combinations query table and the number of transactions query table.
Generating all Possible Pairs of Combinations
The first step in the data preparation process is to generate all distinct pairs of items that could appear on the transaction. To get that,
- Perform a self-join operation on the Items Name column of the base table, Orders. The self-join operation compares each row (transaction) with every other row (transaction).
-
Use the condition Greater than (>) to ensure that each pair of items is considered only once and avoid duplicates where the same pair appears in reverse order, like (Lavender Candle, British Rose) or (British Rose, Lavender Candle) or (Lavender Candle, Lavender Candle)SELECT"Orders" . "Item Name" "Item","Orders copy". "Item Name" "Item Copy"FROM ( SELECT `orders` .`Item Name` "Item Name"FROM "Orders"GROUP BY 1) AS "Orders"JOIN(SELECT `orders`.`Item Name` "Item Name"FROM "Orders"GROUP BY 1) AS "Orders copy" ON "Orders" . "Item Name" > "Orders copy" . "Item Name"
Finally, save the query table with a suitable name. We are saving it as All Items Combinations.
Distinct Count for Existing Combination
The second step in the data preparation process is to get the distinct count for the pairs of items that appear together in transactions.
- Perform a self-join operation on the Items Name column of the base table, Bill Items. The self-join operation compares each row (transaction) with every other row (transaction).
- Use the count_distinct function on the Bill Number column to get the total number of times a combination has occurred together.
|
SELECT
"Orders" . "Item Name" "Item",
"Orders Copy" . "Items Name" "Item Combo",
count_distinct ("Orders Copy" . "Bill Number") "Number of Transactions"
FROM "Orders"
JOIN( SELECT
`Orders`.`Item Name` "Items name",
`Orders`.`Bill Number` "BillNo"
FROM "Orders"
) AS "Orders Copy" ON "Orders" . "Bill Number" = "Orders Copy". "Bill Number"
AND "Orders" . "Item Name" > "Orders Copy" ." Items Name"
GROUP BY 1,
2
|
Finally, save the query table with a suitable name. We are saving it as Count for Existing Combinations.
Merge Tables using Left Join
The final step in the data preparation process is to join the All Combinations table and the Count for Existing Combinations table. Use the Left Join operation on the All Items Combination table, and use the if_null function for combinations that have not occurred or been bought together.
|
SELECT
"All Items Combination" . "Item" "Item",
"All Items Combination" . "Item Copy" "Item Copy",
if_null("Count for Existing Transactions" . "Number of Transactions", 0) as "Number of Transactions"
FROM "All Items Combination"
LEFT JOIN "Count for Existing Transactions" ON "All Items Combination" . "Item" = "Count for Existing Transactions". "Item"
AND "All Items Combination" . "Item Copy" = "Count for Existing Transactions" . "Item Combo"
|
By doing this, we will have a query table showing the count of pairs of items that occur together, with 0 assigned to combinations that do not occur together.
Data Visualization - Creating a Heat map
- Access the Total Transactions for all Combinations query table, click the Create icon on the side navigation panel.
- Choose New Chart view from the drop-down menu.
- Drag and drop the column as shown below
- X - axis: Item
- Y - axis - Item Copy
- Color - Number of transactions
- Click Generate Graph.
- The heat map for pairs of combinations will be ready!
Inference
From the above graph, we infer that combinations like {Silver Antique Vase, Chocolate Box} and {Silver Antique Vase, Rainbow Umbrella} {Rainbow Umbrella, Chocolate box} {Silver Antique Vase, Porcelain Rose} have a higher purchase frequency.
Items with a higher purchase frequency can be displayed on the home page of the e-commerce site as Best Sellers. They can also be shown as recommendations to cross-sell products if the cart has any one of the related items that is frequently bought.
Market Basket Analysis for 3 - item Combination
You can create a query table similar to the above query table to find the three items that occur together in transactions and create a pivot table for visualization. Refer to the sample workspace for queries.
Access your files securely from anywhere
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Nisha Angel J
Sticky Posts
Tip of the Week - Spot Risky Sales with Conditional Formatting
In Zoho Analytics, small tweaks can lead to big insights. One such feature is Conditional formatting based on other columns, your key to instantly spotting where sales success is overshadowed by product returns. Our tip this week shows you how to apply
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
The Social Wall: September 2025
Hello everyone, As we step into the fall season, some major updates are on the horizon. Meanwhile, here are the exciting updates we rolled out this September. Approvals in iOS Managing approvals just got more seamless on mobile. With this update, theIntroducing Detailed View for Candidates in Vendor Portal
We’ve added a new Details sub-tab inside the Vendor Portal to help vendors easily view complete candidate information after submission. With this update, vendors can now access all candidate details, from personal information to associated job openings,Zoho One Down
Zoho Team, Checking if when the services up - currently Zoho One is downHow can I track which zoho users are actively using Zoho CRM
I have several licenses of Zoho CRM. We now need to add a new user. I could purchase a new license, but before I do, I would like to see if any of our existing users are not actively using the license assigned to them. How can I determine the activityAccess to Detail View From HTML Snippet
Zoho Creator displays a detail view that slides out from the right onClick of a record in a report. Am I able to access that detail view from an html snippet, e.g. click a record in a list and display the detail view? The zc_LoadIn dialog is a bit clunky,Billing Management: #10 Solving Common Mistakes in Billing
Over the past few weeks, we have explored different facets of billing, from the simplicity of traditional one-time billing to the evolving landscape of subscriptions, retainers, and usage-based models. We've unpacked how billing isn't just about sending【開催間近 - 10/17】東京 ユーザー交流会 Vol.3 参加登録 受付中！(参加無料)
ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 10/17(金)に、東京・新橋で「東京 ユーザー交流会 Vol.3」を開催します！ ZOHOLICSよりも小規模なイベントですので、「リアル開催はちょっと緊張する…」という方も、安心してご参加いただけます✨ 当日は、初公開の事例を2つご紹介予定です！ なお、セッション映像のアーカイブ配信は予定していないため、会場にお越しいただいた方だけが、登壇者へ直接質問したり、リアルな声を聞いたりできる貴重な機会となっています。 ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーLoading CSS Stylesheets into HTML Snippet
Combining html/css into a single snippet can grow quite large for a UI that has a lot of functionality/styling. To keep things tidy, are we able to pull files into an html snippet using a <link> tag? If so, what are some best practices?Notes Attachments
Two things it would be nice to have the attachment size the same as the attachments sections and it would be nice to be able to attach links like you can in the attachments section. Thank youCanvas: empty images
Hello. If I add an image field like contact photo in a Canvas design, and the field is empty, there is an ugly placeholder in its place. This doesn't happen in the standard view. In the standard view, if the contact photo is empty, nothing appears inGoogle enhanced conversions not working
Hi guys, I've connected Zoho CRM through Google Ads interface with the goal to setup the enhanced conversion tracking in Google Ads. I have to Zoho related conversion goals which you can see in the images below: For the conversion goal above I've setupMS Teams Meeting to Zoho CRM
Has anyone figured out a good way to push MS Teams meeting info on a trigger of "meeting end" to Zoho CRM? We're looking for a way to take attendees of a meeting and meeting duration and push it into Zoho CRM after the meeting has ended. If I can justFont Size 11 - Zoho CRM Email Templates
Our company communicates with our vendors exclusively using Calibri Font Size 11, as this is the standard formatting for professional emails. Since the CRM only allows for the selection of font sizes 10 & 12, we have been unable to utilize the CRM emailSubforms in stateless forms
I think the title says it all. We need to be able to add subforms to stateless forms. Currently the only workaround is to create a Form and delete each record upon submission of the form. I need to build an interface to update our inventory. BasicallyCalling Function via REST API with API Key gives 401 using Zoho Developer
Hi, I created a couple of functions using the one month trial of Enterprise edition, which I was able to call using the API Key method from Postman and from an external site. Now that my trial has expired, I have created the same functions in the DeveloperHow do I move a section or element from one page to another in the NEW Zoho Sites UI
I have a section on my home page with numerous elements within it and I'd like to move the entire section to a different page on my site so I don't have to recreate it from scratch. Is there a way for me to do that easily? I could use a quick answer on this please.Zoho Projects app update: Global Web Tabs support
Hello everyone! In the latest version(v3.10.10) of the Zoho Projects app update, we have brought in support for Global Web Tabs. You can now access the web tabs across all the projects from the Home module of the app. Please update the app to the latestExport as MP4 or GIF
Hi, Just wondering if there's a way to export/convert a presentation to an MP4 video file or even a GIF. One use case would be to use the animation functionality to create social media graphics/charts/gifs/videos. Thanks for a great tool... Rgds JonPage Layout- Horizontal Rule
When editing the layout of, for instance, the Potentials page, is there a way to insert a horizontal rule or white space in between fields? I'd like to keep a group of fields in the same Section, but would like to create some seperation in order to further group together certain fields within the Section. If this is not possible, does anybody have any other suggestions on how to create this same effect? Thank you!Zoho Books will discontinue support for older browser versions soon
Hello users, Starting from May 15, 2024, Zoho Books will no longer support the following browser versions: Browsers Version Restrictions Firefox Browser Versions older than 100 Google Chrome Versions older than 100 Microsoft Edge Versions older than 100Zoho Projects - Q3 Updates | 2025
Hello Users, The final quarter of the year 2025 has begun, and we at Zoho Projects are all set with a plan. New targets to achieve and new milestones to reach, influenced by the lasting imprint of the past quarter. 2025's Q3 saw some new features andZoho Sheet - Printing - Page Breaks and Printing Customization
I think the title is descriptive enough in that I cannot find help documentation on a simple task of adding in page brakes for separating pages on print. ThanksIssue with Trident exe file
Hello Team, Exe Setup file It's showing harmful for user pc please check and do needful. this message for developer team. Thanks Bhargav PurohitDifferent languages for users
Hello, Do you plan to enable individual users to select their languages for interface? Currently language can be changed for everyone - it looks like a settings for a whole portal, which is not good when you are working internationally. Best regards,Transaction Locking with the dynamic date
Is it possible to dynamically update dates on transaction locking. We want to lock transaction x days from todayUnable to change sales_order status form "not_invoiced" to "invoiced"
I am automating process of creating of invoice from sales_orders by consolidated sales_orders of each customer and creating a single invoice per customer every month. I am doing this in workflow schedule custom function where i create invoice by gettingApply Vendor Credits Automatically
We are bulk importing Vendor credits in Zoho Books!!! Is there a way to apply vendor credits automatically to the first UNPAID bill of the Vendor?Apply Vendor Credit Automatically
Hello!!! Is there a way where in we can apply vendor credits automatically on the FIRST OUTSTANDING BILL of the vendor?? We have lots of VENDOR CREDITS ISSUES mostly!!! Applying it manually is a pain for us. Would be great if we have a way to apply theNeed Easy Way to Update Item Prices in Bulk
Hello Everyone, In Zoho Books, updating selling prices is taking too much time. Right now we have to either edit items one by one or do Excel export/import. It will be very useful if Zoho gives a simple option to: Select multiple items and update pricesCreator problem: Edit form View not displaying whole form
I'm having problems with the form in my database. The edit view is not showing the whole form: This is how it looks in the normal view: As you can see, there is a whole section in the bottom right of the form which is not displaying in the Edit View. This means that I can't change or delete any of these fields... Anybody had a similar problem or know a solution? Any help would be appreciated. Cheers,Vendor Master Enhancements for Faster Purchase Entry
I’d like to suggest a few features that will improve accuracy and speed during purchase voucher entry: Automated Item Tax Preference in Vendor Master Add an option to define item tax preference in the vendor master. Once set, this preference should automaticallyQuick Item Search & Auto-suggestion for Invoices
Hi Team, I am facing an issue while creating invoices in Zoho Books. Currently, I have to type the full item name in the correct sequence and spelling for it to appear. For example, my item name is: "Distemper Acri Silk Special White 10kg" If I type somethingFunction #53: Transaction Level Profitability for Invoices
Hello everyone, and welcome back to our series! We have previously provided custom functions for calculating the profitability of a quote and a sales order. There may be instances where the invoice may differ from its corresponding quote or sales order.Integrating Chatbot with Zoho Creator Application
Is it possible to integrate a chatbot with a Zoho Creator application?Average Costing / Weighted Average Costing
Hello fellow maadirs. I understand Zoho Books uses FIFO method of dealing with inventory costing, but do you guys have any plans to introduce average costing? We indians need average costing. It's part of our culture. Please. I beg thee. Thanks.'Add Tax To Amount' not reflected in Invoice
Hi Zoho Support, I'm experiencing an issue with tax calculation display in my invoice template. Despite having "Add tax to amount" box checked in the template settings, the Amount column is not showing the tax-inclusive total for line items. Current behaviour:"Subject" or "Narration"in Customer Statement
Dear Sir, While creating invoice, we are giving in "Subject" the purpose of invoice. For Example - "GST for the month of Aug 23", IT return FY 22-23", "Consultancy", Internal Audit for May 23". But this subject is not coming in Customer Statement. OnlyA real WYSIWYG field instead of the limited rich text
Hi to everyone A "real" WYSIWYG or HTML field that outputs good HTML code when accessed through the API would be excellent. I tried to use the rich text field, but the styling options are limited. For example, there are no heading tags (h1 to h6), andDelete my store of Zoho commerce
Hi Team, I want to delete my stores of commerce. Please help me asap. Looking for the positive response soon. Thanks Shubham Chauhan Mob: +91-9761872650Delete Inactive Zoho Accounts - Access Cleanup_User Id: 60001640923
As part of our Zoho access hygiene, we’ve reviewed and deactivated several inactive user accounts. These accounts have not been used in the past year and are no longer tied to active operations. All access rights have been revoked, and records retainedNext Page