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

                                    • Shifts in Zoho People vs Zoho Shifts?

                                      Hello Zoho People Team, We hope you are doing well. We are evaluating the Shifts functionality within Zoho People and comparing it to the standalone Zoho Shifts product. We’ve encountered comments and discussions suggesting that the Shifts feature inside
                                    • Disable fields in During action in Blueprint?

                                      Hi there. I've tried field disable (setReadOnly(true)) using client script and the event is onMandatoryFormLoad on detail page, assuming it'll work on blueprint fields, but it bears no result. Is this the expected behaviour? That we can't do this yet?
                                    • 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.
                                    • Best Email Backup Wizard in 2026

                                      While searching for an email backup solution, my main hesitation was reliability. As a user, I had already seen many tools that looked promising but failed when handling large mailboxes, skipped folders, or caused authentication issues during the backup
                                    • Subforms and automation

                                      If a user updates a field how do we create an automation etc. We have a field for returned parts and i want to get an email when that field is ticked. How please as Zoho tells me no automation on subforms. The Reason- Why having waited for ever for FSM
                                    • Allow Managers to Create Shifts for Their Departments in Zoho People

                                      Hello Zoho People Product Team, Greetings and hope you are doing well. This feature request is related to Zoho People - please don't move it to zoho one! We would like to submit a feature request regarding shift management permissions in Zoho People.
                                    • Zoho Learn and Zoho CRM integration

                                      I would like to see an integration between Zoho Learn and Zoho CRM. 1. To be able to add articles in a related list in all modules 2. Zia to suggest related articles in a Deal or Case or Lead 3. Ability to read / search articles during a call / follow
                                    • Maintain steady traffic to your domain: How Domain Aliasing helps

                                      Consider this scenario: An organization has its primary domain as administrator.com. Now it wants to shorten its domain to admin.com because it's simpler and easier to remember. However, changing the domain completely can cause the following problems:
                                    • Why Sharing Rules do Not support relative date comparison???

                                      I am creating a Sharing Rule and simply want to share where "Last Day of Coverage" (Date field) is Greater than TODAY (Starting Tomorrow). However, sharing rules don't have the option to compare a date field to a relative date (like today), only to Static
                                    • How do I migrate OLM file to Gmail?

                                      Migrating emails from Outlook for Mac to Gmail can be challenging because Gmail does not support OLM files directly. This limitation often causes confusion and delays, especially when users need quick access to important emails and mailbox data on a web-based
                                    • 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
                                    • Workflow rule only allows 10 workflow per module

                                      Apparently a Zoho professional edition only allows 10 workflow rules per module. This makes workflow allocation literally impossible while allocating potential to different members of the team. I have 15 licenses. Is there a way in which related alerts can be varied? In other words, is it possible to have different related alerts be triggered with different rule criteria. so if I say, if potential is 'x' then trigger related alert 'x' and if potential is 'y' then trigger related alert 'y' Thanks,
                                    • IF Statement in Zoho CRM Formula Field

                                      Hi, I am attempting to write a formula field that will give me one result if one statement AND another statement are true, then a different value if the first statement AND a different statement are true, else 0. Stated differently: if account = destination
                                    • CRM Percent custom fields: When will it show the % symbol and behave like %?

                                      1. Actually Percent custom fields fail to show the % symbol. 2. When in formulas Percent fields work like number: 100 x 5% = 5 ideal world 100 x 5% = 500 what happens actually 3. When importing Percent fields the % symbol has to be removed and the data
                                    • Free Webinar: Zoho Sign for Zoho Projects: Automate tasks and approvals with e-signatures

                                      Hi there! Handling multiple projects at once? Zoho Projects is your solution for automated and streamlined project management, and with the Zoho Sign extension, you can sign, send, and manage digital paperwork directly from your project workspace. Join
                                    • Automatically CC an address using Zoho CRM Email Templates

                                      Hi all - have searched but can't see a definitive answer. We have built multiple email templates in CRM. Every time we send this we want it to CC a particular address (the same address for every email sent) so that it populates the reply back into our
                                    • Editing the Ticket Properties column

                                      This is going to sound like a dumb question, but I cannot figure out how to configure/edit the sections (and their fields) in this column: For example, we have a custom "Resolution" field, which parked itself in the "Ticket Information" section of this
                                    • "Total Hours" on Employee Attendance Report

                                      I'm learning that in Zoho jargon, "total hours" does not include paid breaks. Or at least not the way that my setup is working. That seems a little weird to me, since most jurisdictions in the US don't differentiate between time spent on paid break and
                                    • Fixed assets in Zoho One?

                                      Hi, We use Zoho Books and have the fixed asset option in it. I started a trial for Zoho One and I do not see that as an option. Is the books that is part of zoho one equivalent to Zoho Books Elite subscription or is it a lesser version? Thanks, Matt
                                    • Integration with...

                                      Dear Zoho Commerce team, Please could you consider the integration within Zoho Commerce / Inventory and Qapla'? (https://www.qapla.it/en/) This app is better than Aftership in many ways: - Aftership integration require PRO plan and price start from more
                                    • Repeat Column merge in ZOHO writer columns doesn't allow to set max columns per row

                                      I'm using ZOHO writer to merge data from a ZOHO CRM subform and I want it to make a table. We're using Insert Table for Column Repeat, because this is what we need. (Name of column (Teamname) and underneath that a list of names of teammembers). It works
                                    • Generate leads from instagram

                                      hello i have question. If connect instagram using zoho social, it is possible to get lead from instagram? example if someone send me direct message or comment on my post and then they generate to lead
                                    • Adding Markdown text using Zoho Desk API into the Knowledge Base

                                      Hi Zoho Community members, We currently maintain the documentation of out company in its website. This documentation is written in markdown text format and we would like to add it in Zoho Knowledge Base. Do you know if there is REST API functionality
                                    • Create case via email

                                      Good Afternoon, I have just registered and am taking a look around the system. Is it possible to create a case via email.  I.e. an employee/client/supplier emails a certain address and that auto generates the case which then prompts a member of staff
                                    • Need a Universal Search Option in Zohobooks

                                      Hello Zoho, Need a Universal Search Option in Zohobooks to search across all transactions in our books of accounts. Please do the needful Thanks
                                    • Locked Notebook

                                      Hi, I hadn't used my Notebook in some time and was refamiliarizing myself with it. I clicked a lock icon and now I can't unlock. When I hit the information or unlock icons I'm taken to a page with the notebook icon and a keyboard. When I type, nothing
                                    • Unable to produce monthly P&L reports for previous years

                                      My company just migrated to Books this year. We have 5+ years financial data and need to generate a monthly P&L for 2019 and a monthly P&L YTD for 2020. The latter is easy, but I'm VERY surprised to learn that default reports in Zoho Books cannot create
                                    • Hide fields only for creation

                                      Hello, I'd like to hide some fields only during the creation of a contact in Zoho CRM. In fact I have some fields that are automatically calculated thanks to an automation, so when my users create a contact I don't want them to fill those fields. I know
                                    • Issues with Zoho Sheet in Mac

                                      I have downloaded the Zoho App from App Store but It is failing to Save As, Open & Download Operations. App Store
                                    • Weekly Sales Summary

                                      Is it possible to generate a weekly report in Zoho Books to show -$$ amount of estimates generated -# of estimates generated by Salesperson -$$ amount of Sales Orders created -$$ amount of Invoices generated
                                    • Subject character limit increase

                                      We have an occasional user case where subject lines exceed 500 characters which is the ZeptoMail imposed limit. While I understand long subject lines are not "best practice", these are particular notification emails with specific information for client
                                    • Can I write a check in Zoho Books with no associated bill?

                                      This currently does not seem possible, and I have a client that desperately needs this function if I am able to convert them with Quickbooks. Thank you in advance for your reply. 
                                    • OpenAPI Specs are just plain wrong

                                      The provided yml files for generating the OpenAPI specs are absolutely riddled with errors and inconsistencies. From missing fields on the objects, to just incorrectly named resource objects. I'm having to go through and manually changing the spec to
                                    • About Meetings (Events module)

                                      I was working on an automation to cancel appointments in zoho flow , and in our case, we're using the Meetings module (which is called Events in API terms). But while working with it, I'm wondering what information I can display in the image where the
                                    • Custom Footer – Zoho Writer Document

                                      Hello everyone, I’m having an issue adding a custom footer in a Zoho Writer document. I would like to insert my company information (including a logo + address) in the footer. The problem is that when I add these elements, the main content of my pages
                                    • Report grouping

                                      I have added a grouping in a report but it is not working how i had expected. I wanted to group a summary on a field named Size but when i add the grouping the report is still showing me each record and making a summary at the bottom of the report. What
                                    • Social Media Simplified with Zoho Social: Preview your Instagram grid before posting

                                      For a platform like Instagram that relies on visual appeal, it's important that you plan your image and video content in a way that holds your audience's attention. Planning your grid ahead of time gives you the benefit of understanding how your posts
                                    • Spreadsheet View click & focus issue in Arabic (RTL) localization

                                      Hello Zoho Support Team, I am facing an issue in Zoho Creator Spreadsheet View when using Arabic localization (RTL). Scenario: My app supports English (LTR) and Arabic (RTL). I created a Spreadsheet View for a form. In English, everything works correctly.
                                    • VAT rates - exempt and out of scope

                                      Good Evening, UK based company here. I am a bit confused in respect of setting up VAT rates for exempt goods and services; at present I am simply leaving the VAT rate blank in the transactions in order to prevent any VAT appearing in the VAT return. When
                                    • How to loop through Multiple Upload and Display Actual File Name

                                      I have been reading the help on the File Upload Control and reviewed the Deluge help on files and I can not figure out how to loop through the uploaded files and do anything but get the automatically created file names. The code below will run but each
                                    • Next Page