Understand Customer Purchase Behavior and Boost Sales with Market Basket Analysis

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 
      ) AS  "Orders"
      JOIN(SELECT `orders`.`Item Name` "Item Name"
      FROM  "Orders" 
      GROUP BY 
      ) 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.
    3. 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,
       
    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.



        Zoho Campaigns Resources


          • Desk Community Learning Series


          • Digest


          • Functions


          • Meetups


          • Kbase


          • Resources


          • Glossary


          • Desk Marketplace


          • MVP Corner


          • Word of the Day


          • Ask the Experts


            • 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

            Zoho CRM Plus Resources

              Zoho Books Resources


                Zoho Subscriptions Resources

                  Zoho Projects Resources


                    Zoho Sprints Resources


                      Zoho Orchestly Resources


                        Zoho Creator Resources


                          Zoho WorkDrive Resources



                            Zoho CRM Resources

                            • CRM Community Learning Series

                              CRM Community Learning Series


                            • Tips

                              Tips

                            • Functions

                              Functions

                            • Meetups

                              Meetups

                            • Kbase

                              Kbase

                            • Resources

                              Resources

                            • Digest

                              Digest

                            • CRM Marketplace

                              CRM Marketplace

                            • MVP Corner

                              MVP Corner




                              Zoho Writer Writer

                              Get Started. Write Away!

                              Writer is a powerful online word processor, designed for collaborative work.

                                Zoho CRM コンテンツ



                                  ご検討中の方

                                    • Recent Topics

                                    • How to install Widget in inventory module

                                      Hi, I am trying to install a app into Sales Order Module related list, however there is no button allow me to do that. May I ask how to install widget to inventory module related list?
                                    • error while listing mails

                                      I can't access email in any of my folders: Oops, an error occurred - retry produces the second error response: error while listing mails (cannot parse null string). I've signed in and out of Zoho, restarted my iMac.
                                    • Unlocking New Levels: Zoho Payroll's Journey in 2025

                                      Every year brings its own set of challenges and opportunities to rethink how payroll works across regulations and teams. In 2025, Zoho Payroll continued to evolve with one clear focus: giving businesses more flexibility, clarity, and control as they grow.
                                    • MCP no longer works with Claude

                                      Anyone else notice Zoho MCP no longer works with Claude? I'm unable to turn this on in the claude chat. When I try to toggle it on, it just does nothing at all. I've tried in incognito, new browsers, etc. - nothing seems to work.
                                    • Ability to Edit YouTube Video Title, Description & Thumbnail After Publishing

                                      Hi Zoho Social Team, How are you? We would like to request an enhancement to Zoho Social that enables users to edit YouTube video details after the video has already been published. Your team confirmed that while Zoho Social currently allows editing the
                                    • Free Webinar : Unlock AI driven business insights with Zoho Inventory + Zoho Analytics

                                      Are you tired of switching between apps and exporting data to build customized reports? Say hello to smarter & streamlined insights! Join us for this exclusive webinar where we explore the power of the Zoho Inventory–Zoho Analytics integration. Learn
                                    • Critical Issue: Tickets Opened for Zoho Support via the Zoho Help Portal Were Not Processed

                                      Hi everyone, We want to bring to your attention a serious issue we’ve experienced with the Zoho support Help Portal. For more than a week, tickets submitted directly via the Help Portal were not being handled at all. At the same time no alert was posted
                                    • Hide/Show Subform Fields On User Input

                                      Hello, Are there any future updates in Hide/Show Subform Fields "On User Input"?
                                    • Cloning Item With Images Or The Option With Images

                                      Hello, when I clone an item, I expect the images to carry over to the cloned item, however this is not the case in Inventory. Please make it possible for the images to get cloned or at least can we get a pop up asking if we want to clone the images as
                                    • Custom validation in CRM schema

                                      Validation rules in CRM layouts work nicely, good docs by @Kiran Karthik P https://help.zoho.com/portal/en/kb/crm/customize-crm-account/validation-rules/articles/create-validation-rules I'd prefer validating data input 'closer to the schema'
                                    • Adding Default Module Image into mail merge field

                                      As with most people finding their way to these forums i have a specific requirement that doesn't seem to be supported by Zoho I have created 2 custom modules to suit my purpose 1 is an inventory type module that lists aluminium extrusions, and all relevant
                                    • Is it possible to roll up all Contact emails to the Account view?

                                      Is there a way to track all emails associated with an Account in one single view? Currently, email history is visible when opening an individual Contact record. However, since multiple Contacts are often associated with a single Account, it would be beneficial
                                    • Free Training - Explore What’s New in Zoho One 2025

                                      Greetings! We hope you have all had a chance by now to get hands-on with the new features and updates released as part of ZO25. Yes, we understand that you may have questions and feedback. To ensure you gain a comprehensive understanding of these updates,
                                    • Good news! Calendar in Zoho CRM gets a face lift

                                      Dear Customers, We are delighted to unveil the revamped calendar UI in Zoho CRM. With a complete visual overhaul aligned with CRM for Everyone, the calendar now offers a more intuitive and flexible scheduling experience. What’s new? Distinguish activities
                                    • Quick way to add a field in Chat Window

                                      I want to add Company Field in chat window to lessen the irrelevant users in sending chat and set them in mind that we are dealing with companies. I request that it will be as easy as possible like just ticking it then typing the label then connecting
                                    • How to create a two way Sync with CRM Contacts Module?

                                      Newbie creator here (but not to Zoho CRM). I want to create an app that operates on a sub-set of CRM Contacts - only those with a specific tag. I want the app records to mirror the tagged contacts in CRM. I would like it to update when the Creator app
                                    • Zoho Sheet for Desktop

                                      Does Zoho plans to develop a Desktop version of Sheet that installs on the computer like was done with Writer?
                                    • Develop and publish a Zoho Recruit extension on the marketplace

                                      Hi, I'd like to develop a new extension for Zoho Recruit. I've started to use Zoho Developers creating a Zoho CRM extension. But when I try to create a new extension here https://sigma.zoho.com/workspace/testtesttestest/apps/new I d'ont see the option of Zoho Recruit (only CRM, Desk, Projects...). I do see extensions for Zoho Recruit in the marketplace. How would I go about to create one if the option is not available in sigma ? Cheers, Rémi.
                                    • How to import data from PDF into Zoho Sheet

                                      I am looking to import Consolidated Account Statement (https://www.camsonline.com/Investors/Statements/Consolidated-Account-Statement) into zoho sheet. Any help is appreciated. The pdf is received as attachment in the email, this document is password
                                    • Zoho Projects Android app: Integration with Microsoft Intune

                                      Hello everyone! We’re excited to announce that Zoho Projects now integrates with Microsoft Intune, enabling enhanced security and enterprise app management. We have now added support for Microsoft Intune Mobile Application Management (MAM) policies through
                                    • Cant't update custom field when custom field is external lookup in Zoho Books

                                      Hello I use that : po = zoho.books.updateRecord("purchaseorders",XXXX,purchaseorder_id,updateCustomFieldseMap,"el_books_connection"); c_f_Map2 = Map(); c_f_Map2.put("label","EL ORDER ID"); c_f_Map2.put("value",el_order_id); c_f_List.add(c_f_Map2); updateCustomFieldseMap
                                    • About Zoneminder (CCTV) and Zoho People

                                      Hi team I would like to implement a CCTV service for our branches, with the aim of passively detecting both the entry and exit of personnel enrolled in Zoho Peeple, but my question is: It is possible to integrate Zoho People with Zoneminder, I understand
                                    • Admin asked me for Backend Details when I wanted to verify my ZeptoMail Account

                                      Please provide the backend details where you will be adding the SMTP/API information of ZeptoMail Who knows what this means?
                                    • Kaizen #223 - File Manager in CRM Widget Using ZRC Methods

                                      Hello, CRM Wizards! Here is what we are improving this week with Kaizen. we will explore the new ZRC (Zoho Request Client) introduced in Widget SDK v1.5, and learn how to use it to build a Related List Widget that integrates with Zoho WorkDrive. It helps
                                    • Set connection link name from variable in invokeurl

                                      Hi, guys. How to set in parameter "connection" a variable, instead of a string. connectionLinkName = manager.get('connectionLinkName').toString(); response = invokeurl [ url :"https://www.googleapis.com/calendar/v3/freeBusy" type :POST parameters:requestParams.toString()
                                    • Possible to connect Zoho CRM's Sandbox with Zoho Creator's Sandbox?

                                      We are making some big changes on our CRM so we are testing it out in CRM's Sandbox. We also have a Zoho Creator app that we need to test. Is it possible to connect Zoho CRM's Sandbox to Zoho Creator's Sandbox so that I can perform those tests?
                                    • I Need Help Verifying Ownership of My Zoho Help Desk on Google Search Console

                                      I added my Zoho desk portal to Google Search Console, but since i do not have access to the html code of my theme, i could not verify ownership of my portal on Google search console. I want you to help me place the html code given to me from Google search
                                    • Timeline Tracker

                                      Hi Team, I am currently using Zoho Creator – Blueprint Workflows, and I would like to know if there is a way to track a timeline of the approval process within a Blueprint. Specifically, I am looking for details such as: Who submitted the record Who clicked
                                    • Primary / Other Billing Contacts

                                      If you add an additional contact to a Zoho Billing Customer record, and then mark this new contact as the primary contact, will both the new primary and old primary still receive notifications? Can you stop notifications from going to the additional contacts
                                    • Is it possible to enforce a single default task for all users in a Zoho Projects ?

                                      In Zoho Projects, the Tasks module provides multiple views, including List, Gantt, and Kanban. Additionally, users can create and switch to their own custom views. During project review meetings, this flexibility creates confusion because different users
                                    • [Free Webinar] Zoho Creator webinars - Learning Table and Creator Tech Connect Series in 2026

                                      Hello everyone, Wishing you all a wonderful new year! May 2026 and the years ahead bring more opportunities, growth, and learning your way 🙂 We’re excited to kick off the 2026 edition of the Learning Table Series and Creator Tech Connect Series ! Learning
                                    • Reply and react to comments

                                      Hi everyone! We're excited to bring to you a couple of new features that'll make your sprint process simpler. A cloud application brings with it an array of social media features that can be efficiently used in your organizational setup. As an agile scrum
                                    • Module Customisation - Lookup function not available

                                      Good evening, Within my business, I can have multiple customers, who have multiple mobile assets. When I set these assets up, I enter information such as vehicle registration, Vehicle identification number (VIN), Unit number, YOM, in addition to others.
                                    • zoho click, and nord VPN

                                      Unfortunately, we've been having problems with Zoho Click, where essentially the line cuts off after about a minute's worth of conversation every time we are on VPN. Is there a way we can change this within the settings so it does not cut the line off
                                    • Zoho Calender

                                      a) does the clanender in zoho project allow you to see the name of the event in the celnder view, it currently says either "Task (1) or "Milestoen (1)" b) Alternatively does the calender in Zoho project integrate with zoho calender?
                                    • Matching ZOHO Payments in Banking

                                      Our company has recently integrated ZOHO Payments into our system. This seemed really convenient at first because our customers could pay their account balance by clicking on a link imbedded in the emailed invoice. Unfortunately, we can't figure out how
                                    • New Enhancements to Zoho CRM and Zoho Creator Integration

                                      Hello Everyone, We’ve rolled out enhancements to the Zoho Creator and Zoho CRM integration to align with recent updates made to the Zoho Creator platform. With enhancements to both the UI and functionality, This update also tightens access control by
                                    • Work Type - Limitation

                                      Hello, I'm setting up work types and have noticed, a limitation on the parts area to 10 lines. Can this be increased to 20 or greater? In addition to this, when I attempt to add the work type to a work order, the correct labour hours doesn't flow through.
                                    • Emails sent through Bigin are not posting in IMAP Sent folder

                                      I have set up my email to work from within Bigin using IMAP.  I am using IMAP so I can sync my email across multiple devices - phone / laptop / desktop / iPad / etc.  I want all my emails to populate my email client (outlook & iphone email) whether or
                                    • Turning the page for Zoho SalesIQ: 2025 to 2026

                                      As we wrap up 2025, we would like to take a moment to reflect on what we set out to achieve this year, what we’ve delivered, and where we’re headed next. What we focused on in 2025 This year was all about strengthening the core of engagement and AI, making
                                    • Next Page