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  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.
      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,
        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.




        • 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

          • Need advice for product/item search functionality when adding invoices.

            My client uses "Catalog or Vendor" name and Product code to search for his items. But Zoho only allow to search by product name and SKU when adding items to Invoices/Estimates. Clients product codes are not unique as they may overlap from different catalogs/vendors.

          • How to dynamically pass IDs from one API response to a second API call in Zoho DataPrep?

            Hi Team, I am setting up a global consolidated reporting pipeline in Zoho Analytics (India) using Zoho DataPrep as the ETL engine. I am pulling data from multiple Zoho Books Data Centers (US, Singapore, India) thats why i used the URL Source (OAuth 2.0)

          • Feature Request: "Send Invitation" Toggle for Events

            I am writing to request a critical "Quality of Life" update for the Activities module. Currently, adding people to the Participants field in an Event triggers an automatic email invitation/acceptance tracking with no way to opt-out. For general events

          • Account Review Pending - Daily Limit 100

            Hi ZeptoMail Team, I'm writing to request a manual review of my account. I am currently stuck on the 100-email daily limit and cannot purchase credits. Status: My domain is already verified (DKIM/CNAME). Issue: The "Customer Validation" option is no longer

          • Automation Series: Move Tasks to a different Task List on Status Change

            In Zoho Projects, you can automatically move a task from one task list to another when its status is updated. This keeps your tasks organised and ensures better visibility while reducing manual work. For instance, during a product launch, the marketing

          • Introducing Bigin's all new integration with Microsoft Teams

            Picture this: You are on call with your team to discuss some important deals that need to be closed soon. While discussing each deal, your team has to switch back and forth between your Teams account and Bigin account to refer to details about the deal.

          • Scaling the customer journey: What's new in CommandCenter

            Dear Customers, The last time we connected about CommandCenter, it was about the tool's new controls and revamped UI. Personally, the overhaul of the UI from the last update, still excites me. Today, we're here with news about its scalability and added

          • Adjusting Physical Inventory

            Not getting very far with support on this one, they say they are going to fix it but nothings happened since November. Please give this a thumbs up if you would like to see this feature or comment if you have some insight. Use Case: Inventory set to be

          • Dynamic Remaining Quantity in Lookup During Allocation

            Hi everyone, From what I understand in Zoho Creator, lookup fields only display the stored value from the source record and do not dynamically update while a form is being filled. Because of this, showing a real-time updated remaining quantity inside

          • Is it possible to use HTML formatting in SMS messages sent from Zoho?

            Hi everyone, I have a question regarding sending SMS from Zoho When we send emails from Zoho, we can include HTML code to format the message (like adding links, styling, etc.). Is it possible to do something similar when sending SMS messages through Zoho

          • Important update: Migrate to the new SalesIQ live chat widget before April 14, 2026

            The old SalesIQ live chat widget will be deprecated on April 14, 2026. This is a final reminder to migrate to the new SalesIQ live chat widget before this date. After April 14, 2026, the old widget will no longer be maintained, which can lead to slower

          • If your IM chats aren’t auto-assigning, here’s what to check

            Hey everyone! We’ve been seeing quite a few questions around Instant Messaging (IM) Chat Routing in Zoho Desk, especially around how it actually behaves in real time. So I thought I’d share a practical breakdown of what’s happening behind the scenes.

          • connect zoho creator with google drive

            Hello everyone, I need to connect to a folder drive. The idea, is that google drive loads a text document with some data, I must read that text document to be able to autofill a form that I have in zoho creator with that data. I also attach PDFs and place

          • Uploaded files are not included when using "Include user submitted data" in Email Notification

            In Send Email notification workflow in Zoho Creator, there is an option called "Include user submitted data" which allows the email to contain all the form submission details. However, when this option is enabled, files or images uploaded through File

          • Extend Zoho Canvas Customization to Zoho Creator Forms and Reports

            Currently, Zoho Canvas allows users to design and customize the UI of Zoho CRM modules with a much better visual experience. This helps organizations create cleaner layouts, improve usability, and design interfaces that match their workflows. However,

          • Kanban 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.

          • Button ''I'm Interested'' won't translate

            Since our main platform is in French, I would like to get this ''I'm Interested'' button translated to French ''Je suis intéressé''. We managed to translate the whole carreer site in French, except the button. How come ? We have a standard subscription,

          • Feature request - image resizing on sales orders

            I need to be able to show the items on the sales orders, currently the item image shows really small and no way to resize it, need the ability to make the image larger to showcase the product on the pdfs

          • ZOHO.CRM.UI.Record.open not working properly

            I have a Zoho CRM Widget and in it I have a block where it will open the blocks Meeting like below block.addEventListener("click", () => { ZOHO.CRM.UI.Record.open({ Entity: "Events", RecordID: meeting.id }).catch(err => { console.error("Open record failed:",

          • Email Parser Not Extracting Fields Correctly with Certain Label Formats

            I’ve been testing the Email Parser functionality in Zoho CRM to automatically extract data from incoming emails and map it to CRM fields. During testing, I noticed that parsing sometimes fails when the email contains field labels formatted like this:

          • Zoho CRM Community Digest - February 2026 | Part 1

            Hello Everyone! February has been off to a productive start in the Zoho CRM Community, with several thoughtful product enhancements and helpful technical discussions making their way into the forums. To help you stay up to speed, we’ve rounded up the

          • Nouvelle fonctionnalité : donnez du style à vos pages avec le Branding

            Que vous prépariez un webinaire important. Vous avez investi du temps dans votre contenu, invité des intervenants de qualité, et les inscriptions commencent à affluer. Mais votre page d’inscription ? Elle ressemble à toutes les autres : générique, sans

          • How to sync Zoho CRM Quotes with Zoho Books/Finance Estimates or Quotes

            Hi everyone, We’re building quotes in the Zoho CRM Quotes module because of its strong CPQ features and better communication options (multiple contacts, email customization, etc.). However, these don’t sync directly with Zoho Books/Finance for invoicing.

          • Marketing Tip #24: Encourage customers to leave reviews

            Reviews are one of the strongest trust signals in ecommerce. When shoppers see real feedback from other customers, they feel more confident about buying, especially when they’re choosing a brand for the first time. Not all reviews are equal. Detailed

          • Ability to Attach Record-Specific Files Automatically in Workflow Email Templates

            Currently in Zoho CRM, email templates allow attachments to be added, but these attachments are static and remain the same for every recipient. There is no straightforward option to automatically attach a file that is stored within the specific CRM record

          • This version of app doesn't support this notecard type Error

            So this problem is happening for any notes created within the last week, as well as any note recently edited on Android. I can open them on my phone fine, but they don't open on the website version. They DO work on the desktop app version. It's just web

          • Issue with Picklist Dropdown Not Opening on Mobile

            Hello I am experiencing an issue with picklist values on mobile. While the arrow is visible, the dropdown to scroll through the available values often does not open. This issue occurs sporadically, it has worked occasionally, but it is very rare and quite

          • {Action Required} Re-authenticate your Google Accounts to Continue Data Sync

            Hello Users! To align with Google’s latest updates on how apps access files in Google Drive, we’ve enhanced our integration to comply with the updated security and privacy standards, ensuring safer and more reliable access to your data. With this update,

          • Allocating inventory to specific SO's

            Is there a way that allocate inventory to a specific sales order? For example, let's say we have 90 items in stock. Customer 1 orders 100 items. This allocates all 90 items to their order, and they have a back order for the remaining 10 items which could

          • Is this a SCAM email or is it really Zoho?

            L.S. I received the following message. Is this from Zoho? I have had a Zoho One account for many years and my website has been online for years. If it is a scam, I think you should know about it.

          • How to close an estimate ?

            Hello, I have created estimates, and converted them to invoices to get 50% payment. Now I have 2 cases where the estimate stills shows status partially invoiced, however: 1. for one of them, project stopped half way, so the remaining part will never be

          • Updating Analytical Fields Data

            Dear Zoho team, I'm having an issue with the recently added fields in both Analytical Desk and Analytical. How can I generate the data in Analytical when new fields are added? https://analytics.zoho.com/workspace/2436819000000007005/edit/24368190000

          • Looking for Guidance on Building a Zoho Website

            I'm exploring the possibility of building a custom website with specific features using Zoho as an alternative platform. My goal is to create something similar to https://gtasandresapk.com , with the same kind of functionality and user experience. I'd

          • My Zoho mail stopped receiving or sending emails about 3 hours ago

            Its a pop 3 account. The emails get into the actual mailbox on the server and I can send emails directly from the server, but they are no longer in Zoho, in neither of my Zoho accounts. All green ticks under Mail Accounts under Settings

          • Zoho Cliq not working on airplanes

            Hi, My team and I have been having this constant issue of cliq not working when connected to an airplane's wifi. Is there a reason for this? We have tried on different Airlines and it doesn't work on any of them. We need assistance here since we are constantly

          • Request to Recover Deleted Task List – Project ID: RIV-MOD-10722

            Hi Zoho Team, I hope this message finds you well. My Zoho task list associated with Project ID: RIV-MOD-10722 appears to have been deleted. When I clicked on the task link from the email notification, I received the following message: "Task has been deleted

          • Email Insights included in Bigin emals are marked as SPAM everywhere

            Today I noticed that email recipients who use Office 365 never receive emails sent from Bigin. Further examination showed that all Email Insights links in email headers are marked as spam/phishing by Office 365. Example screen included. The problem is

          • How do I import Connected Records for a Deal?

            Can you point me to an example of the CSV file that would add related records to an existing CRM Deal? I imported a Deal, then tried importing a connected record using a unique ID that references the Deal ID, but it doesn't attach it to the Deal rec

          • File Upload Field in Zoho Forms Not Updating Existing File in Zoho CRM

            Hi everyone, I’m trying to understand the behavior of a file upload field mapped from Zoho Forms to Zoho CRM. Scenario There is a File Upload field in a Zoho CRM module. A Zoho Form also has a File Upload field, which is mapped to that CRM field. When

          • Zoho Training

            Greetings! I am trainer. My focus area is Project Management and MS Project. I have used Zoho CRM to a good extent. Though, I was interested in using ZOHO projects, as there were no live projects, I could not take it up for studies. Recently a client

          • Next Page