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,

Businesses can provide personalized product recommendations to customers during purchase.

Retailers can optimize product placements by placing or displaying related items closely and
     having adequate stock levels.

Marketers can curate focused campaigns and product bundle deals that resonate with customer preferences.

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, 

1. 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).
   
2. 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.

1. 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).
   
2. 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

1. Access the Total Transactions for all Combinations query table, click the Create icon on the side navigation panel.
   
2. Choose New Chart view from the drop-down menu.
   
3. Drag and drop the column as shown below
   
1. X - axis:  Item
   
2. Y - axis - Item Copy
   
3. Color - Number of transactions
   
   
   
   
4. Click Generate Graph.
5. 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.

Sample Workspace: https://analytics.zoho.com/workspace/1160261000009211018

• 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

• Recent Topics

• Marketing Tip #14: Increase cart value with product bundles

  Bundling products is a great way to increase average order value while giving customers more convenience. Think “camera + tripod + memory card” or “soap + lotion + bath salts.” Bundles make shopping easier and feel like a better deal. It’s a win-win for

• Problem with Workdrive folders

  I'm having a problem a problem accessing files in a Zoho work drive folder when using the Zoho writer app. The problem folder appears grayed out in the Zoho work drive window in both the online and writer application. However I can open the folder in

• Sortie de Zoho TABLE ??

  Bonjour, Depuis bientôt 2 ans l'application zoho table est sortie en dehors de l'UE ? Depuis un an elle est annoncée en Europe Mais en vrai, c'est pour quand exactement ??

• Add RTL and Hebrew Support for Candidate Portal (and Other Zoho Recruit Portals)

  Dear Zoho Recruit Team, I hope you're doing well. We would like to request the ability to set the Candidate Portal to be Right-to-Left (RTL) and in Hebrew, similar to the existing functionality for the Career Site. Currently, when we set the Career Site

• Auto tracking URL generation based on Carrier

  Hi, While creating a shipment order for a package in Zoho Books, I have a requirement that for example, if the carrier is Delhivery and tracking number is 1234, then can automatically the tracking link/URL be generated as www.delhivery.com/1234. Similary,

• Zoho Bookings form pre-filled with Zoho Forms in

  Hi, I've got a contact page on my website and I'd like to have the option to book an appointment (redirected to zoho bookings page) after an option is submitted on the contact form. how would I go about doing this? thanks

• Pre-orders at Zoho Commerce

  We plan to have regular producs that are avaliable for purchase now and we plan to have products that will be avaliable in 2-4 weeks. How we can take the pre-orders for these products? We need to take the money for the product now, but the delivery will

• Customer ticket creation via Microsoft Teams

  Hi all, I'm looking to see if someone could point me in the right direction. I'd love to make it so my customers/ end users can make tickets, see responses and respond within microsoft teams. As Admin and an Agent i've installed the zoho assist app within

• Zoho Books' 2025 Wrapped

  Before we turn the page to a new year, it’s time to revisit the updates that made financial management simpler and more intuitive. This annual roundup brings together the most impactful features and enhancements we delivered in 2025, offering a clear

• Can multiple agents be assigned to one ticket on purpose?

  Is it possible to assign one ticket to two or more agents at a time? I would like the option to have multiple people working on one ticket so that the same ticket is viewable for those agents on their list of pending tickets. Is something like this currently

• This user is not allowed to add in Zoho. Please contact support-as@zohocorp.com for further details

  Hi Team, when I,m trying to create a email account (imagixmidia.com.br) it's showing this error >>  This user is not allowed to add in Zoho. Please contact support-as@zohocorp.com for further details plz help me  thanks

• Edit default "We are here to help you" text in chat SalesIQ widget

  Does anyone know how this text can be edited? I can't find it anywhere in settings. Thanks!

• Can't change form's original name in URL

  Hi all, I have been duplicating + editing forms for jobs regarding the same department to maintain formatting + styling. The issue I've not run into is because I've duplicated it from an existing form, the URL doesn't seem to want to update with the new

• Feature Request: Sync Leave Tracker to Zoho Mail / Calendar or provide option to download information

  Zoho Leave Tracker offers the option to sync the leave Calendar to Microsoft 365 and Google Calendar. Adding an option to sync to Zoho-Mail Calendar would avoid duplication and add significant value for users. An alternative would be to allow users to

• Compensation | Salary Packages - Hourly Wage Needed

  The US Bureau of Labor Statistics says 55.7% of all workers in the US are paid by the hour. I don't know how that compares to the rest of the world, but I would think that this alone would justify the need for having an hourly-based salary package option.

• Multiple currencies - doesn’t seem to work for site visitors / customers

  I am trying to understand how the multiple currency feature works from the perspective of the website visitor who is shopping on my Zoho Commerce site. My site’s base currency is US Dollars (USD) but my store is for customers in Costa Rica and I would

• Archiving Contacts

  How do I archive a list of contacts, or individual contacts?

• How do people handle using Outlook and Zoho Project calendar at the same time?

  We have an ongoing problem in our organisation where we use Zoho Projects to plan all of our projects tasks and that also allows us to look forward using the workload report to see which of our consultants are overstretched etc and which are available.

• 10GB Email Storage Limits in Zoho CRM

  We’ve had Zoho One for almost 5 years and have always synced our emails from Gmail via IMAP… As of late, we’ve run into issues with our emails not syncing, due to being over the 10GB storage cap… What’s very odd is that we haven’t changed a thing? I know

• Performance is degrading

  We have used Mail and Cliq for about three years now. I used to use both on the browser. Both have, over the past 6 months, had a severe degradation in performance. I switched to desktop email, which appeared to improve things somewhat, although initial

• How to set page defaults in zoho writer?

  hi, everytime i open the zoho writer i have to change the default page settings to - A4 from letter, margins to narrow and header and footer to 0. I cannot set this as default as that option is grayed out! so I am unable to click it. I saved the document

• Add Israel & Jewish Holidays to Zoho People Holidays Gallery

  Greetings, We hope you are doing well. We are writing to request an enhancement to the Holidays Gallery in Zoho People. Currently, there are several holidays available, but none for Israel and none for Jewish holidays (which are not necessarily the same

• Unable to Send Different Email Templates for Different Documents in Zoho Sign

  Hello Zoho Community, I am facing a limitation with Zoho Sign regarding email notifications sent to customers when a document is sent for signing. Currently, whenever I send any template/document for signing, the email notification that goes to the customer

• Enable History Tracking for Picklist Values Not Available

  When I create a custom picklist field in Deals, the "Enable History Tracking for Picklist Values" option is not available in the Edit Properties area of the picklist. When I create a picklist in any other Module, that option is available. Is there a specific reason why this isn't available for fields in the Deals Module?

• ZO25: The refreshed, more unified, and intelligent OS for business

  Hello all, Greetings from Zoho One! 2025 has been a remarkable year, packed with new features that will take your Zoho One experience to the next level! From sleek, customizable dashboards to an all-new action panel for instant task management, we’ve

• Vault crashes on Android Devices

  Vault is continuously closing after entering the master password on my Android device. After several attempts I get a system message that says there is a bug in the app. I've uninstalled and reinstalled the app, and cleared the app cache, but nothing

• ¿Cómo puedo configurar las contraseñas creadas bajo una directiva para que nunca caduquen y no aparezcan como caducadas en los informes?

  ¿Cómo puedo configurar las contraseñas creadas bajo una directiva para que nunca caduquen y no aparezcan como caducadas en los informes? La razón por la cual contraseña estas no deben caducar es porque su actualización depende de mi cliente y no de mí.

• Camera access

  My picture doesn't appear in a group discussion. (The audio is fine.) The guide says "Click the lock icon on address bar," but I can't find it. Advise, please

• Are static links available

  I'm still using Zoho Meeting in trial mode. My previous webinar software provided a static link, and I made the mistaken assumption that I could send out my link and start a meeting later. Mass confusion, but my fault. With a paid version do you get a

• Why is Zoho Meeting quality so poor?

  I've just moved from Office 365 to Zoho Workplace and have been generally really positive about the new platform -- nicely integrated, nice GUI, good and easy-to-understand control and customisation, and at a reasonable price. However, what is going on

• How to print a label from zoho creator app?

  Hello, I would like to print a label from zoho creator app record similar to attached one. Size 74mm x 102mm. I tried record template. It leaves plenty of space around the content and also I couldn't set the height of the page. So it is not printing properly. Could someone please direct me to right direction for this requirement?

• Integration between "Zoho Sprints Stories" and "Zoho Projects Tasks/Subtasks"

  We have two separate teams in our organization using Zoho for project management: The Development team uses Zoho Sprints and follows Agile/Scrum methodology. The Infrastructure team uses Zoho Projects for traditional task-based project management. In

• Where can we specify custom CSS in Zoho Forms custom theme ?

  I'm using a form with a dark theme. The OTP popup window is unreadable, because for some reason, the OTP popup background fixes color to white, but still takes the font color specified in the custom theme. This ends up as white on white for me, rendering

• Team Gamification

  Would love to motivate, engage and encourage our team with our social media posts. Would like to include Gamification features of Social Media in Zoho Social or Marketing Automation. And also bring in Social Advocacy tools/tracking/management to these,

• Sync Data from MA to CRM

  Currently, it's a one-way sync of data from the CRM to MA. I believe we should have the ability to select fields to sync from MA to the CRM. The lead score is a perfect example of this. In an ideal world we would be able to impact the lead score of a

• Update CRM record action

  Currently, MA only offers a "Push Data" action to push data to a CRM module. This action is built to cover the need to both create a new record and update an existing record. Because it has been implemented this way all required fields on the CRM module

• Pro Lite Upgrade - Quick Access Tray

  Hello, I was going to upgrade to Pro Lite but the Quick Access Tray feature isn't available for Windows. Of the four features not available for Windows, the QAT is what I'm most interested in. Are there plans to add this feature for Windows anytime soon?

• Boost your CRM communication with new font types, sizes, and default reply-to options while composing emails

  Hello Everyone, We’re excited to introduce a series of impactful enhancements to the email composer settings in Zoho CRM. These updates enable you to personalize and optimize your customer interactions with greater efficiency. So what's new? Add custom

• 3 year sick leave cycle

  How do you set up a sick leave cycle for South Africa? In SA the sick works like this for the first 6 months you get 0.83 paid sick days a month, then after 6 months you sick leave balance is reset to 30 days that can be used over a 36 month cycle.  This

• WorkDrive and CRM not in sync

  1/ There is a CRM file upload field with WorkDrive file set as the source: 2/ Then the file is renamed in WorkDrive (outside CRM): 3/ The File in CRM is not synced after the change in WorkDrive; the file name (reference) in CRM record is not updated (here

• Next Page