How to Conduct Market Basket Analysis in Zoho Analytics

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.

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.

Sample Workspace: https://analytics.zoho.com/workspace/1160261000009211018

• Topic Participants

• Nisha Angel J

  

• Recent Topics

• How to link to/displaya png stored in workdrive in a Creator HTML snippet?

  How to link to/display png stored in workdrive in a Creator HTML snippet? I've tried it as a plain external link and as an iframe. The iframe works but is kinda slow...and I'd prefer to be able to do finer-grained positioning but referencing the png as

• Has anyone built a ticket export that allows Help Center users to export the tickets shown in the My Area list they are looking at?

  Hi, We are moving to Zoho Desk soon. Our current support system displays an option in our help center allowing customers to export their Open, Closed, or all tickets based on which list they are looking at. We need to offer the same in Zoho Desk help

• Contemplating moving my site from WordPress to Zoho Sites

  Hi Everyone, We currently find ourselves in a situation where we ant to review and update our current sites content. We are small business owners, not developers. We currently use a wide range of Zoho products. We sometimes think about the possibility of either moving or just starting from scratch on Zoho Sites. I would like to know if anyone has done this and of course the things that need to be considered. We have spent quite a bit of time getting our current site positioned organically and I guess

• Using AVG in SQL Query

  My current SQL Query is: SELECT "Client Info", avg("Revenue") FROM "Funded Deals" GROUP BY "Client Info" The current table that this is creating: Client Info avg(Revenue) Previous 4175 1st - New 3411 1st - Old 3013 Renew 3069 While this works as I had

• Unified customer portal login

  As I'm a Zoho One subscriber I can provide my customers with portal access to many of the Zoho apps. However, the customer must have a separate login for each app, which may be difficult for them to manage and frustrating as all they understand is that

• Tracking Implementations

  We create Opportunities (Renamed Deals) with associated Quotes (renamed Products and Services). When we close the deal, we want to be able to track the implementation of each product separately. For instance, the client may have ordered Item A, Item B

• Is this link the transparent pixel used to track opens?

  Could someone please help me identify this highlighted link? It is automatically created by Zoho and a lot of contacts receiving campaigns will click on this link. I don't believe that it is the "update your preferences link" because on the Link Clicks

• Zoho desk desktop application

  does zoho desk has a destop applicaion?

• How can I view Help Center articles in 'Sandbox' mode?

  I have published some help center articles but when I try to preview the knowledge base page, it says 'We have no content to display'.

• Is this possible with Campaigns?

  My company is currently moving CRM's from Monday to Zoho One. Currently, our marketing lead process is send out 7 sms messages over 14 days if the lead is in a certain status. If we don't get a response the lead is put into a "Closed" status. Do we buy

• Tracking leads through the sales process

  Our leads are being generated via paid google ads and are arriving to us with UTM data showing lead sources, campaign mediums and campaign names. I want to track the progress of those leads as they migrate through our sales funnel while maintaining a

• Error 403: Forbidden When Updating Email Signature via API

  Hi Zoho Desk team, First, congratulations again on the excellent Zoho API. But, I’m encountering an issue while attempting to update an email signature via the API. Whenever I make a request to update the signature, the response returns an HTTP 403 Forbidden

• Opening & working multiple tickets

  We work in multiple tickets at the same time. Currently to do this, we have to open multiple instances of Desk.  Is there a way to do this by just opening multiple ticket tabs within 1 instance of Desk?  

• Different content per social media account..

  Is there a way to add different content per social media account on one post?

• Merge tickets

  Hello, I am attempting to merge two tickets; however, the option to merge does not appear to be available. Could you kindly confirm if this feature has been relocated or removed? Additionally, could you please provide guidance on the correct process to

• Add "Merge by Ticket ID" Option on Ticket Screen

  Dear Zoho Team, We would like to request a new feature for the ticket interface in Zoho Desk. Specifically, we propose adding a button to the ticket screen that allows users to merge the current ticket with another ticket by directly providing the second

• Using tickets to train Zia

  Hi Team, I would like to know if there is any way that Zia can also learn from previous tickets in addition to the articles from the knowledge base. Since we have most of our knowledge curerently in the tickets and that this is hard to combine into a

• Unable to delete Junk Lead and Not Qualified from the Lead Status field

  Are Junk Lead and Not Qualified default fields or something that cannot be deleted? I have gone into the Leads module and made sure there are no records using these values. Other values have a minus sign that allows me to remove them, but these two fields do not have that option.

• Welcome Link Expired

  Hi The links sent to the users didn't get clicked on in time and now all the links have expired. Is there a way to send a new link without deleting them and re-adding them>

• Alert for Back Navigation in Zoho Creator Widgets on Mobile Apps

  In Zoho Creator widgets, when a user navigates back on mobile devices, the data within the widget is reset. This leads to a loss of any unsaved changes or inputs, causing frustration for users. To enhance user experience, we need to implement a confirmation

• Zoho Developer Community Monthly Digest - December 2024

  Hello everyone! As we step into the new year, let’s reflect on the strides we’ve made together and gear up for the exciting opportunities 2025 holds. This month’s digest highlights key updates, engaging discussions, and upcoming events designed to keep

• Allow "add new" option to picklists and multiselection fields from add or edit pages

  Hello zoho, please insert an add option (ie +)sign) to pick and multiselect fields so we can add new options while entering or editing records. For example. in my lead module, while adding a new record, I realized I had a new lead source. I went to my

• Enhancements to Zoho Corp Help Center "Team Requests" View

  Dear Zoho Team, I hope this message finds you well. The ability to view both my tickets and my team’s tickets in the Zoho Corp Help Center is a fantastic feature, especially as the focal point for Zoho in our organization. However, we’ve encountered a

• We've revised the pricing model of CRM portal user licenses

  Hello everyone, We’re making important updates to our pricing structure for portal user licenses, effective from the next payment cycle. The new slab-based pricing is as follows: Previously, these portal user licenses were priced at $5/ user/ month. As

• Zoho and Hostaway webhok integration.

  I want to receive data coming from hostaway webhook and receive it in zoho crm to create or update record in a module based on conditions. The hostaway webhook sends data every time a reservation is created or modified or cancelled. The hostaway sends

• Unable to create custom fields for shipment order

  I'm unable to create custom fields for shipment orders, even though the custom fields are set up correctly. A request to the following endpoint: https://www.zohoapis.com/inventory/v1/settings/preferences/customfields?organization_id=${ZOHO_ORGANIZATION_ID}&entity=shipment_order

• Records per page in New UI

  It seems the new UI lack of "Records per page" function, it is very handy if you are looking for a data that you don't know the exact search term, but you know it may "between" few entries. without a "page" function, we kind of have to keep page down and page, the autoload is not that fast, and you are dealing with thousands of entries. Could we please have the "records per page" function back to New UI (also it shows total counts of the record) Looking forward to hear from you.

• zet pack not working

  We are using the zet pack command to package our Zoho extension. However, after running the command, the extension gets packed, but the resulting package is empty. We've attached a screenshot for reference. Could you please assist us with resolving this

• CSV File Added to Form - Parse and Map to Fields

  Hi,  I apologize, I can't seem to find a clear explanation or help article on how to parse a CSV file. On validate, I process this deluge script. fileContent = Collection(input.File_upload.content); result = fileContent.values(); info result; What I want to do is create a record (in another form), and map certain fields to fields in the form.  I can get values, which is the whole file but no keys. If I use result = fileContent.keys(); I get "0" As you can see from CSV, my first line is my map and

• Encountered an error while creating a bill in Zoho Inventory: {"code":6,"message":"Invalid data provided"}

  I attempted to create a bill using the Zoho Inventory API, but I received an error: {"code":6,"message":"Invalid data provided"}. However, when I made the same request again, it was successful. Does anyone have insights on why this happened?

• Why do I need to send the Customer ID in the Create Purchase Order Request?

  I'm trying to create a purchase order using this endpoint https://www.zoho.com/inventory/api/v1/purchaseorders/#create-a-purchase-order Unfortunately, I'm getting this error { "code": 4, "message": "Invalid value passed for Customer ID" } The doc doesn't

• How to Retrieve Serial Numbers of Items in Zoho Inventory via API?

  Hello, I am currently working with the Zoho Inventory API and need to retrieve the serial numbers associated with specific items in our inventory. After reviewing the documentation, I couldn’t find an endpoint dedicated to fetching serial numbers for

• merging email accounts

  previously I was using 5 mail pop mailboxes within VO , 2 of them are becoming obsolete so I was thinking about deleting the obsolete ones and merge the remainders into my main account mailbox within VO , is this possible ? thnx in advance.

• ZohoPeople API - Retrieve leave type IDs

  Hi All, I have created a leave type in Zoho People UI. Now I need to fetch the  Leave Type ID of it. As per the documentation[1] I used the  curl request [2]. But I ended up with the error response from the API.  {"response":{"message":"Error occurred","errors":{"message":"Server Error Occured","code":7031},"status":1,"uri":"/api/leave/getLeaveTypes"}} The new API[3] does not tell anything on how to retrieve the Leave Type ID.  Have you done any changes to the API recently. If so please let me know

• Inadequate Customer Support

  Hello & Greetings! I have been a pro Zoho user since the last 2 years and I would admit that the apps that are being offered are good, however the support we receive has a lot more to achieve. This being a design issue rather than a staff issue. Being

• Incorrect Closing Stock Amount value

  Act as Zoho Inventory Expert. We are a construction company, OVAL Projects Engineering Limited. We started using Zoho Inventory for Stock Management.I have multiple warehouses. I have encountered a problem while generating custom warehouse wise inventory

• The Next Chapter for CRM for Everyone: Moving from Early Access to Phased Rollout for Customers

  #CRM25Q1 Hello Everyone, Until now, CRM for Everyone has been available in early access mode exclusively for users who opted to try the new version. We are now transitioning to a phased release, starting with the basic edition. We are thrilled to announce

• Zoho vault uses only password to unlock not a TouchID

  TouchID works when normally openning app but when called from keyboard while browsing or trying to log to another app it shows only password option to unlock. This behaviour is only on iPad Mini witch latest 18.2OS

• Unable to add Guest Members

  We are having issues adding Guest Members to our Cliq account. We have sent out a number of request but it seems that only some people are able to access the platform. Others have received a message stating that they need to be granted access from an

• Webhook when estimate is refused is not firing

  Hello, I use a workflow through make that sends estimate with zoho books (I paid books and sign). -Those estimates when accepted are firing the webhook that I create in zoho sign (photo 1) -However when refused they are not firing the webhook that I created

• Next Page