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

          • Custom Related List Inside Zoho Books

            Hello, We can create the Related list inside the zoho books by the deluge code, I am sharing the reference code Please have a look may be it will help you. //..........Get Org Details organizationID = organization.get("organization_id"); Recordid = cm_g_a_data.get("module_record_id");

          • Zoho Meeting - Feature Request - Introduce an option to use local date and time formating

            Hi Zoho Meeting Team, My feature request is to add an option for dates to be displayed in the users local format. This is common practice across Zoho applications and particularly relevant to an application like Zoho Meeting which revolves around date

          • Cannot give public access to Html Snippet in Zoho Creator Page

            Hi, I created a form in Zoho Creator and published it. The permalink works but I want to override the css of the form. (style based URL parameters is not good enough) So I created a page and added an Html snippet. I can now override the css, which is

          • How can Outlook 365 link back into Zoho Projects so meetings and events in Outlook calendar show in Zoho?

            We use Outlook 365 for our emails and diaries and have integrated Zoho Projects with Office 365. One challenge we face is getting Zoho Projects to recognise when we have meetings and events in Outlook and not allow project managers to assign tasks over that period. Is there a way to resolve this? Thanks

          • On Edit Validation Blueprint

            Hello, I have a notes field and a signature field. When the Approve button is clicked, the Signature field will appear and must be filled in. When the Reject button is clicked, the Notes field will appear and must be filled in. Question: Blueprint will

          • Zoho Invoice en Navarra (Spain)

            Hola, ¿Alguien usa Zoho Invoice en la Comunidad Foral de Navarra? En Navarra tenemos un sistema tributario diferente y no aplica Verifactu (la Hacienda Foral de Navarra ha anunciado su alternativa, NaTicket, pero no ha informado cuándo entrará en vigor).

          • Emails from Zoho are getting blocked due to Zoho IP address being blacklisted

            This is the info I got from my hosting provider – please address this issue immediately. I don’t expect this from such a big household name. Every single invoice I have sernt it not being received by my clients, all being blocked. All of a sudden. As

          • agentid : Where to find?

            I've been looking around for this agenId to check for the total ticket assigned on a specific agent url :"https://desk.zoho.com/api/v1/ticketsCountByFieldValues?departmentId=351081000000155331&agentId=35108xxxxxx132009&field=statusType,status" type :GET

          • Zoho DataPrep integration with OpenAI (beta)

            We are thrilled to announce Zoho DataPrep's integration with OpenAI. The public beta roll-out opens up three features. Users who configure their OpenAI Organizational ID and ChatGPT API key (Find out how) will be able access the features. The features

          • AI Bot and Advanced Automation for WhatsApp

            Most small businesses "live" on WhatsApp, and while Bigin’s current integration is helpful, users need more automation to keep up with volume. We are requesting features based on our customer Feedbacks AI Bot: For auto-replying to FAQs. Keyword Triggers:

          • Setting total budget hours for a specific project

            Hi there, I work on a lot of projects that have fixed budget hours. Is there a way to enter the total budgeted hours so i can track progress and identify when hours have been exceeded. I see in the projects dashboard there is a greyed out text saying

          • Clone entire dashboards

            If users want to customize a dashboard that is used by other team members, they can't clone it in order to customize the copy. Instead they have to create the same dashboard again manually from scratch. Suggestion: Let users copy the entire dashboard

          • Introducing Formula Fields for performing dynamic calculations

            Greetings, With the Formula Field, you can generate numerical calculations using provided functions and available fields, enabling you to derive dynamic data. You can utilize mathematical formulas to populate results based on the provided inputs. This

          • Getting started with Zoho PDF Editor

            Hello users, If you are new to Zoho PDF Editor or aren't sure of its full potential, then this article is for you. Zoho PDF Editor is a free online PDF editing tool, that allows you to upload and edit PDFs, insert text and images, add fillable and e-signature

          • Zoho Projects - Cloning a task does not trigger task workflow when created

            Hello! I have a Project where my team uses a set of tasks from a tasklist as templates, so we could simply clone it and drag it to another list in kanban view to avoid creating a new one from scratch. The process works well, but after cloning it the new

          • Purchase Orders not in sequence

            I am unable to sort by Purchase Order Numbers. I can only sort by date; however, the PO numbers aren't in the order they were entered. This was not the case prior to today.

          • Internal Fillable Contract with Zoho Writer (Before Sending to Client)

            Hi everyone, I’m trying to automate the following process in Zoho CRM and would appreciate some guidance. Process: When a Deal moves to a specific stage, CRM triggers an automation. CRM sends a contract template to an internal team member so they can

          • Date/time displayed in ZohoCRM does not match date/time of entries in ZohoForm

            Hello there, we use a ZohoForm as a worksheet, i.e. users use it to track start time, break and stop time for every working day. The ZohoCRM org time zone is set on GM -4, so is the Time Zone in the Date&Time section in ZohoForm (see attachment). Despite

          • Update Existing Records greyed out in Free Version

            Trying to update records from an Excel sheet, and not getting the option to update. Only option is to add as new accounts. All documentation I can see says update should be an option! Accounts, Leads, Contacts, all the same.

          • Dynamically Populate Picklist Values from Another Module Using Client Script

            I am working in Zoho CRM and trying to dynamically populate a picklist field in the Partners module using values stored in another custom module. I have two modules: 1. Partners Module Field: Partner_Type_Pick Field Type: Picklist 2. Partners_Type Module

          • Add zoho calendar to google calendar

            Hi I keep seeing instructions on how to sync Zoho CRM calendar with google calendar but no instructions on how to view Zoho calendar in my google calendar.

          • Zoho Community Digest : Jan 2026 | Part 1

            Hello Everyone! Staying in the loop with Zoho's latest product updates and features across the vast Zoho Community Forums can be a real challenge. We get it. With over 50+ applications, each with its dedicated forum, it's easy to miss out on important

          • World date & time format

            Hello, Is there a timeline to get the worldwide used date and time format ? I mean not the american one... I mean day month year, and 24 hours clock. Regards

          • Nimble enhancements to WhatsApp for Business integration in Zoho CRM: Enjoy context and clarity in business messaging

            Dear Customers, We hope you're well! WhatsApp for business is a renowned business messaging platform that takes your business closer to your customers; it gives your business the power of personalized outreach. Using the WhatsApp for Business integration

          • Conditional layouts - support for multi-select picklists

            Hi, The documentation for conditional layouts says the following: "Layout Rules cannot be used on the following field types: Auto Number Lookup Multi Select Lookup User Lookup Formula File Upload Multi Line" I have a custom module with a multi-pick list

          • Dont want to list inactive items.

            If an item is made inactive, there is no point in showing it in the item list. Please provide an option to hide all inactive items in 'Preferences'. 

          • Actual vs Minimum

            Hi all, I am sure I am not the only one having this need. We are implementing billing on a 30-minute increment, with a minimum of 30 minutes per ticket. My question is, is there a way to create a formula or function to track both the minimum bill vs the

          • Client Script Not Working When Field is Set by Workflow

            Problem Context: I have implemented a client script in the Cases module that automatically assigns commands based on the value of the Priority field. The script functions correctly when the Priority field is manually set by a user through the form. Observed

          • Integration of CRM and Recruit

            hi team, Is it possible to sync deals <> job openings from only 1 pipeline? My configuration of CRM has pipeline for each business unit, so I will have all data in the CRM system. body leasing and recruitment is one BU (hence 1 pipeline) - can I sync

          • {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,

          • integrating Zoho CRM vendors with Zoho projects

            In most of our projects we collaborate with our Vendors. Being able to integrate only Accounts and not Vendors from CRM, is a huge limitation for our perspective and needs. We would really love to see this feature in the CRM-Projects integration.

          • Zoho Creator Workshops 2026—Europe & UK | Coming to a city near you!

            Hello everyone! We're excited to announce the Zoho Creator Workshop Series 2026, coming to cities across Europe and the United Kingdom this year! Whether you're looking to explore the intermediate-to-advanced capabilities of Creator or you're a seasoned

          • Validation rule for Date field

            The condition settings for a Date field are are absolutlly usless. Conditions can only be set for a specific date, which is logically ineffective in most cases. When setting a condition for a Date field, users usually need to compare the value relative

          • Number 9 envelopes for invoice printing

            I email and print invoices. Being new to Zoho and coming from QB, we did both as we have a more traditional So in Zoho i want to do the same using Number 9 envelopes. These have both a return window and mail to windoow see attached image. Im just looking for best suggestions on how to get a ZOHO invoice to work, so I can mail my invoices...

          • Zoho Books/Square integration, using 2 Square 'locations' with new Books 'locations'?

            Hello! I saw some old threads about this but wasn't sure if there were any updates. Is there a way to integrate the Square locations feature with the Books locations feature? As in, transactions from separate Books locations go to separate Square locations

          • Open Sans Font in Zoho Books is not Open Sans.

            Font choice in customising PDF Templates is very limited, we cannot upload custom fonts, and to make things worse, the font names are not accurate. I selected Open Sans, and thought the system was bugging, but no, Open Sans is not Open Sans. The real

          • Super Admin Logging in as another User

            How can a Super Admin login as another user. For example, I have a sales rep that is having issues with their Accounts and I want to view their Zoho Account with out having to do a GTM and sharing screens. Moderation Update (8th Aug 2025): We are working

          • Add Reporting feature to display variance/change columns when comparing periods

            When running reports to compare periods (for example, Profit and Loss comparing current year to previous), I would like to be able to display variance columns in both (a) amount or (b) percentage.

          • Payroll and BAS ( Australian tax report format )

            Hello , I am evaluating Zoho Books and I find the interface very intuitive and straight forward. My company is currently using Quickbooks Premier the Australian version. Before we can consider moving the service we would need to have the following addressed : 1.Payroll 2.BAS ( business activity statement ) for tax purposes 3.Some form of local backup and possible export of data to a widely accepted format. Regards Codrin Mitin

          • Invalid scope choice: Workdrive integration in CRM

            Bug: There is an invalid option in the permission choices for Workdrive integration in CRM. If the entry "WorkDrive.teamfolder.CREATE" is selected, it will return a message indicating invalid OAuth scope scope does not exist.

          • Next Page