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

          • Clone Entire Zoho Boooks Organization, including all transactions for testing & training

            Can Zoho Books support help with direct cloning of entire Zoho Books & Inventory Organization? including all transactions, just like a copy & paste or disk cloning. Is this possible?

          • Restrict portal signup until registration form and payment are completed

            Hi everyone, I am working on a Zoho Creator portal for a project. In our business flow, users must first fill out a registration form and pay a registration fee before they are allowed to access the portal. However, when I share the portal link, users

          • Zoho Creator In-App Notification

            Hi Team, I have implemented an in-app notification using code, as it required some customization. I followed the example in the link below: https://www.zoho.com/deluge/help/pushnotify-using-deluge.html#Example I have a couple of questions regarding this

          • Tip #64- Exploring Technician Console: Screenshot- 'Insider Insights'

            Hello Zoho Assist Community! Have you ever needed to capture exactly what's happening on a remote machine, whether to document an issue, guide a customer, or keep a record of your session? That's where the Screenshot feature in Zoho Assist comes in! With

          • Relating Invoices to Projects

            Hi Zoho team, If I have already created previously an invoice in Books, so I want to know how can I associate it with a relevant project? Thank you

          • Create a quote/estimate that includes a range of prices

            I am interested in using Zoho Books' Quote templates to create estimates for my customers. I do a mix of fixed-bid quotes and quotes based on an hourly rate. For the hourly rate quotes/estimates, I like to include a price range, for example: 2-4 labor

          • Budget

            I have just upgraded to the standard plan in order to be able to utilize the budgeting function and record budget amount

          • Capirec bank Automatic feed update

            Can anyone tell me if Zoho supports Automatic bank feed update from a Capitec bank account in south africa?

          • Free webinar! Accelerate deals with Zoho Sign for Zoho CRM and Bigin by Zoho CRM

            Hello, Paperwork shouldn’t slow you down. Whether you’re growing a small business or running a large enterprise, manual approvals and slow document turnaround can cost you time and revenue. With Zoho Sign for Zoho CRM and Bigin by Zoho CRM, you can take

          • Add Lookup Field in Tasks Module

            Hello, I have a need to add a Lookup field in addition to the ones that are already there in the Tasks module. I've seen this thread and so understand that the reason lookup fields may not be part of it is that there are already links to the tables (

          • CRM x WorkDrive: File storage for new CRM signups is now powered by WorkDrive

            Availability Editions: All DCs: All Release plan: Released for new signups in all DCs. It will be enabled for existing users in a phased manner in the upcoming months. Help documentation: Documents in Zoho CRM Manage folders in Documents tab Manage files

          • Upload own Background Image and set Camera to 16:9

            Hi, in all known online meeting tools, I can set up a background image reflecting our corporate design. This doesn't work in Cliq. Additionally, Cliq detects our cameras as 4:3, showing black bars on the right and left sides during the meeting. Where

          • 【まだ間に合う！】Zoho ユーザー交流会 | AI活用・CRM・Analytics の事例を聞いて、ユーザー同士で交流しよう！

            ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 3月27日(金)に東京、新橋で開催する「東京 Zoho ユーザー交流会 NEXUS」へのお申し込みがまだの方は、この機会にぜひお申し込みください！（参加無料） ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー イベントの詳細はこちらから▷▷ https://www.zohomeetups.com/tokyo2026vol1#/?affl=communityforumpost3 ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

          • ZeptoMail API

            Hello Since today, we experience issues with the ZeptoMail API. When trying to send e-mails using: https://api.zeptomail.eu/v1.1/email we receive the error: (503) Site unavailable due to a traffic surge. Please try again shortly. I kindly ask you to identify

          • Sender Email Configuration Error.

            Hello Team, Hope you are all doing well. We are in the process of creating the Zoho FSM environment in the UAE. When we try to add the sender email address “techsupportuae@stryker.com”, we receive the error message: “Error occurred while sending mail

          • Managing user mailbox actions

            An organization often has users with different roles and responsibilities, such as leadership, operations, or support teams. While some users may require full access to email features, others may only need limited functionality. For example, enabling

          • Custom function return type

            Hi, How do I create a custom deluge function in Zoho CRM that returns a string? e.g. Setup->Workflow->Custom Functions->Configure->Write own During create or edit of the function I don't see a way to change the default 'void' to anything else. Adding

          • Update P_Leave: code: 7052 "Employee_ID": "Enter Employee ID"

            Hi, Zoho People - Update Leaves Can someone assist? ------------------------------------------------------------------------------------------ col = Collection(); col.insert("recordid":id); col.insert("Date_Check_Approval":zoho.currentdate); info zoho.people.update("P_Leave",col.toMap());

          • Prevent tracking users from specific countries

            Currently, I’m receiving many bot visits from the United States and Malaysia. I would like these visits not to be recorded in SalesIQ. I already enabled the option to exclude traffic from cloud service providers, but I’m still receiving bot visits. Ideally,

          • Es posible cambiar el lenguaje de los modulos del ASAP?

            Es posible cambiar el lenguaje de estos textos? Tengo Zoho configurado en español pero aun así me muestra estos textos en ingles:

          • Using workflows to automatically set classification of new tickets

            Hello, I am trying to use a workflow to set a classification for a new ticket that is created via an email coming into my desk department. The workflow is working fine if I create a ticket from within desk, however if a ticket is emailed in then this

          • Text/SMS With Zoho Desk

            Hi Guys- Considering using SMS to get faster responses from customers that we are helping.  Have a bunch of questions; 1) Which provider is better ClickaTell or Screen Magic.  Screen Magic seems easier to setup, but appears to be 2x as expensive for United States.  I cannot find the sender id for Clickatell to even complete the configuration. 2) Can customer's reply to text messages?  If so are responses linked back to the zoho ticket?  If not, how are you handling this, a simple "DO NOT REPLY" as

          • Default/Private Departments in Zoho Desk

            1) How does one configure a department to be private? 2) Also, how does one change the default department? 1) On the list of my company's Zoho Departments, I see that we have a default department, but I am unable to choose which department should be default. 2) From the Zoho documentation I see that in order to create a private department, one should uncheck "Display in customer portal" on the Add Department screen. However, is there a way to change this setting after the department has been created?

          • Show unsubscribed contacts ?

            Hello, I would like to display the unsubscribed contacts. Unfortunately, I do not have this subscription type as described in the documentation (https://help.zoho.com/portal/en/kb/marketing-automation-2-0/user-guide/contacts/contact-management/articles/subscription-type-24-1-2024#Subscription_Type_field.)

          • What's New in Zoho Inventory | Q2 2025

            Hello Customers, The second quarter have been exciting months for Zoho Inventory! We’ve introduced impactful new features and enhancements to help you manage inventory operations with even greater precision and control. While we have many more exciting

          • "Spreadsheet Mode" for Fast Bulk Edits

            One of the challenges with using Zoho Inventory is when bulk edits need to be done via the UI, and each value that needs to be changed is different. A very common use case here is price changes. Often, a price increase will need to be implemented, and

          • 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

          • ZOHO BOOKS - RECEIVING MORE ITEMS THAN ORDERED

            Hello, When trying to enter a vendor's bill that contains items with bigger quantity than ordered in the PO (it happens quite often) - The system would not let us save the bill and show this error: "Quantity recorded cannot be more than quantity ordered." 

          • Stock count by bin location

            Is there a configuration to make a stock count by bin or area and not by product. these is useful to manage count by area Regards

          • Server-based Appication API access for Social, Sites, Flow, Pages.

            Hello, I am trying to hook up API access for a number of apps and I have hit a wall trying to add these scopes to the API feed. We cannot find the correct way to list the scope for these Zoho apps; Social, Sites, Flow, Writer. Error on web-page comes

          • Zoho Survey – Page Skip Logic Not Working

            Hi everyone, I'm experiencing an issue with the page skip logic in Zoho Survey. Last week, it was working fine, and I haven’t changed anything in the settings. However, today the skip logic is not working at all. I also tried testing it with different

          • Zoho Survey: Bulk Exporting Raw Data (CSV/Excel) from 100+ Individual Survey Projects

            Hi Zoho Community, I am currently managing a 360-degree evaluation process that involves 100+ individual survey projects (one separate survey for each employee being evaluated). I need to download the raw response data (CSV or Excel) for all 100 surveys.

          • Brand Studio Projects in Analytics

            Hi All, Currently pulling my hair out over trying to link together some social media posts for a reporting dashboard in Analytics, so I thought I'd see if anyone on here had a solution. Our Marketing Team created a LinkedIn campaign in Zoho Brand Studio,

          • ERROR: Product type cannot be changed for Items having transactions.

            I have mistakenly added a product type as goods for an item that was a digital service. Now when HSN/SAC became mandatory, this brought my attention to this error I did. So I tried changing the product type but it displayed this error message Product

          • Combine and hide invoice lines

            In quickbooks we are able to create a invoice line that combines and hides invoices lines below. eg. Brochure design         $1000 (total of lines below, the client can see this line) Graphic Design           $600 (hidden but entered to reporting and

          • Introducing Built-in Telephony in Zoho Recruit

            We’re excited to introduce Built-in Telephony in Zoho Recruit, designed to make recruiter–candidate communication faster, simpler, and fully traceable. These capabilities help you reduce app switching, handle inbound calls efficiently, and keep every

          • Include Notes in email templates for task

            Hi there,  I am setting up some automated email reminders via "setup-automation-workflow" to be send out when a task is being edited. I would like to include the "task notes" in the email. Is that possible? I do not find that field in the dropdown table when setting up the email template. Is it also possible to trigger the workflow rule when a new note is added to the task? In my opinion that should be quite essential, since a task update is often done by adding a new note to the task.... Also i

          • Auto-publish job openings on my Zoho Recruit Careers Website

            I have developed a script using the Zoho Recruit API that successfully inserts new jobOpening records to my Zoho Recruit website, but my goal is to auto-publish to the Careers Website. The jobOpening field data shows two possible candidates to make this

          • [Free webinar] Custom domains for portals in Zoho Creator - Creator Tech Connect

            Hello everyone, We’re excited to invite you to another edition of the Creator Tech Connect webinar. About Creator Tech Connect The Creator Tech Connect series is a free monthly webinar featuring in-depth technical sessions designed for developers, administrators,

          • Remove my video

            Hi, How can I remove my video so that I don't have to see myself. It's weird so I always remove my own video from what I see but cannot find this feature here. Thanks!

          • Next Page