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
Is it posssible to add Asap Widget on Wordpress?
I have tried to add the ASAP widget so users could iniciate chats and see the KB information but nothings seems to work. I have tried to add the script using a php snippet that adds the to the footers and also tried one for the header in the functioRequire ticket resolution
Hi Zoho team, Is there a way to require resolution even if an agent did not use a blueprint? for example, our blueprint has a "resolve" transition but what if agent revoked blueprint and manually set the status of ticket to closed? Is there a way whereTables from ZohoSheets remove images when updated from source
I have a few tables from a ZohoSheet in a ZohoWriter document that will remove the images in the cells when I refresh from the source. The source still has the images in the table when I go to refresh. After updating from the source, as you can see theUnable to Receive Emails on Zoho Mail After Office 365 Coexistence Setup – Error: 553 Relaying Disallowed
Hello, My domain name is bigniter.com, and I’ve been using Zoho Mail as my email service provider without any issues. Recently, I followed the steps outlined in the Zoho documentation to enable Coexistence with Office 365: 🔗 https://www.zoho.com/mail/help/adminconsole/coexistence-with-office365.html#multi-serverAPI Pagination Error: 'from' Parameter Limit
Hello, I am encountering an error while paging through the Zoho Desk API results: Status code: 422 - {"errorCode":"UNPROCESSABLE_ENTITY","message":"The value passed for field 'from' exceeds the range of '0-4999'."} Is 5000 the maximum number of recordsHow to go to the next open ticket in the queue when agents closes ticket
Zoho Desk When agent closes a ticket - eg when they choose 'Send and Close" - where is the setting that automatically redirects them to the next open ticket in the queue?External download link limit
Can You please help us to understand this For Zoho WorkDrive external users, the download limit is a maximum of 5 GB total download size and a maximum of 50 first-level files and folders What is the meaning of first level? We are using these files inCRM verify details pop-up
Was there a UI change recently that involves the Verify Details pop-up when changing the Stage of a Deal to certain things? I can't for the life of me find a workflow or function, blueprint, validation rule, layout rule ect that would randomly make itAutomatically Add Recurring Zoho Meeting Events to Zoho Calendar / Zoho Meeting Calendar
Hello Zoho Meeting Team, Hope you are doing well. We would like to request an enhancement regarding recurring meetings created inside Zoho Meeting. At the moment, when we schedule a recurring meeting in Zoho Meeting, it does not appear in Zoho CalendarThe difference between Zoho Marketing Automation and Zoho Campaigns
Greetings Marketers! This post aims to differentiate between Zoho Marketing Automation and Zoho Campaigns. By the time you get to the end of the post, you will be able to choose a product that objectively suits you. What is Zoho Marketing Automation?Custom templates for calendar report
What about being able to design custom templates for the calendar report, as well as for other types of reports? I think more users are waiting for this.Print a price list or price book
Hi Community. Am I right in concluding that Zoho has no functionality to print a price list from either Zoho CRM, Zoho Inventory or Zoho Books? I won't get stuck on the fact that Zoho doesn't sync price books between Zoho CRM and Books/Inventory (moreDisable payment thank-you emails
Hello, can someone please tell me how to disable sending of the "Payment Thank-You" emails?Maximum tags possible in Contacts Records
I read in some documentation that Zoho allows a total of 200 tags across all records. Is this correct? So is it not possible to have one tag per record for 500 records?Integrate your Outlook/ Office 365 inbox with Zoho CRM via Graph API
Hello folks, In addition to the existing IMAP and POP options, you can now integrate your Outlook/Office 365 inbox with Zoho CRM via Graph API. Why did we add this option? Microsoft Graph API offers a single endpoint to access data from across Microsoft’sAny way to "Pay with Check" or "Refund with Check" for Credit Notes?
When we have a Bill in Zoho Books, we can select the "Pay with Check" option which then allows us to print/cut the check directly out of Zoho Books. When we created a Credit Note and want to refund the customer, is there any way to Refund with Check,CRM Mobile reports
When our engineers finish a job they like to email the customer a job report. This is best done todate as an email template but we can find no way to include an image field from that module. Is there any other options?Unify All Zoho Video Meeting Experiences into One Standardized Platform
Hi Zoho Team, We would like to share an important user experience concern regarding the current state of video meeting functionality across the Zoho ecosystem. The Problem Within Zoho, there are multiple ways to initiate or schedule a video meeting: ZohoWhen Zoho Tables Beta will be open to EU data center
Hello all, We in EU are looking at you all using and testing and are getting jealous :) When we will be able to get into the beta also? We don't mind testing and playing with beta software. Thank you!Start Form on a different page (i.e., hide form pages)?
I have a Zoho form that uses the `Field Alias - Prefill URL` feature. My goal is to have a pre-filled field that directs the user to a specific starting page in the form. For example: The URL will have a field alias that will auto-populate a field withMicrosoft Teams now available as an online meeting provider
Hello everyone, We're pleased to announce that Zoho CRM now supports Microsoft Teams as an online meeting provider—alongside the other providers already available. Admins can enable Microsoft Teams directly from the Preferences tab under the MeetingsStandard Payment Term is not pulled from account to quotation
Hey Team There seems to be something off. I do have "Net 30" as my default payment term in Zoho Books for my customers. If, from the customer overview or quote section, I create a new Quotation, the payment terms field stays blank and doesn't get thePassing the CRM
Hi, I am hoping someone can help. I have a zoho form that has a CRM lookup field. I was hoping to send this to my publicly to clients via a text message and the form then attaches the signed form back to the custom module. This work absolutely fine whenHow can we disable "My Requests"?
We are not using this functionality in our system at all and our users get confused.PayPal payment received recording problem
Hi, A little while back one of our customers used the PayPal payment option to pay an invoice For some reason though the payment is showing up twice within the Payments section of the invoice! Instead of setting the invoice value to ZERO, it now shows a negative value Anyone else face this problem? I've checked PayPal and there is only 1 payment in reality... A bug? Actonia ps: for anyone from Zoho Customer Service or tech team, its Invoice 785 in our accountstring(87) "{"code":"INVALID_TOKEN","details":{},"message":"invalid oauth token","status":"error"} " grtting this error
Using access token i am trying to add sales orders through api but it is throwing errors like the above i have mentioned. Please help me for thatHow to mute chat notification sound by default in Zoho SalesIQ?
We’ve recently embedded the Zoho SalesIQ chatbot on our website, and we’ve noticed that notification sounds sometimes play even when the visitor hasn’t interacted with the chat widget yet. We’re trying to understand two things: Why do these sounds occurKanban View for Projects.
At our organization, we describe active projects with various statuses like "In Proofing" or "Printing" or "Mailing". In the Projects view, one can set these project statuses by selecting from the appropriate drop-down. While this works, it's difficult to view and comprehend the progress of all of your projects relative to each other in a table. Creating a Kanban view for projects where I can move them from one status to another allows me to see where each project is in the order of our workflow.How to Hide Article Links in SalesIQ Answer Bot Responses
I have published an article in SalesIQ, and the Answer Bot is fetching the data and responding correctly. However, it is also displaying the article link, which I don’t want. How can I remove the link so that only the message is shown?Currency limitation when integrating Zoho CRM & Zoho Books
Brief Video of Problem: https://www.loom.com/share/d61d1d07d0904e149d80c1901a22418e. Background · Our business is based in Australia and we have to have Zoho Books in AUD to comply with tax and reporting regulations in Australia. · We sellAdd RECURRING option when adding email to calendar events
When you add an email to a calendar event, there is no option to make that new calendar event into a recurring event. It is counterproductive to make an event from your email to then have to go to your calendar, find the event, and make it recurring.Using Contains as a filter
We are using Zoho Reports, ServiceDesk Plus analytics. I do not see how to create a report filter using Contains comparison of a string values, is one string contained in another. For example, Task Title contains the word Monitor. Is this possible in Zoho Reports? This reporting feature is available in SDP reporting. Thanks in advance, Craig RiceLINE Auto Message Connect to Zoho
When I integrated LINE into the CRM, I was prompted to disable “Chat,” “Auto Response,” and “Greeting Messages,” and to enable the webhook. However, since I have already set up some auto-reply features in LINE, including Rich Messages and greeting automation,Option to block bookings from specific email address or ip adresss in zoho booking
Sometime few of our client keep booking irrelevant booking service just to resolve their queries and they keep booking it again and again whenever they have queries. Currently its disturbing our current communication process and hierarchy which we haveThreaded conversations for emails sent via automation
Hi Guys, I hope you are doing well. Don't you guys think we should have an option in a workflow to notify users either as a new email or the previous email thread. For example, if you have one deal in the process and there are 10 different stages duringAll new Address Field in Zoho CRM: maintain structured and accurate address inputs
The address field will be available exclusively for IN DC users. We'll keep you updated on the DC-specific rollout soon. It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition. Managing addressesCreate folder is fetch fails
coming from zapier... zapier has a google drive task that searches for a specific folder in google drive, and if it fails to find the folder it will create a folder based on the search criteria, and contine along the singluar path of the flow. TryingMeetups de Usuarios de Zoho - Noviembre 2025
¡Hola, Comunidad! Durante el mes de noviembre celebraremos los Meetups de usuarios de Zoho, encuentros presenciales pensados para quienes queráis mejorar vuestras estrategias de lead nurturing y aprender a sacar el máximo partido a herramientas como ZohoIntroducing 7 New Connectors in Zoho DataPrep!
We’ve just made data management even easier - Zoho DataPrep now supports 7 new external connectors to help you build more robust, scalable ETL pipelines. Why it matters: ✅ Broader data access ✅ More automation, less manual work ✅ Smarter pipelines, betterSystem flaws and lack of response from Zoho
I have had to go on here as no-one is replying to my emails regarding my problem (been 10 days and no email reply) and your chat facility online through your Zoho Books software opens and closes immediately, so not functioning properly. I actually calledNext Page