Report of Week - Customer Preferred Brands over Finance Data

Report of Week - Customer Preferred Brands over Finance Data

People tend to choose a few brands over the other. The popularity of the brand or the quality attached to it makes consumers gravitate toward the brand names. Understanding the customers' purchase behavior and measuring their brand preference is vital for your retail strategy. 


This week, let's learn how to track the most and the least preferred brands among your customers and invest in what brings profit. This solution is based on Zoho Finance data. You can customize the solution to suit your finance data structure. 





Steps to create brand preference reports:

  • Join Invoice and Credit Notes data (Query Table)
  • Rank the Brands using Formulas
    • Brand Rank by sales 
    • Brand Rank by number of customers who picked it
  • Create Reports
    • Top Brands by Revenue
    • Top Brands by Popularity
    • Customer by Brands

Join Invoices and Credit Notes data (Query Table)

Join the Invoices and Credit Notes data to get the complete list of customer purchase items along with the brand. The following query allows you to do this.   

 

SELECT

  

                     "Invoices". "Invoice Date" 'Date',  

                     "Sales Persons" . "Name" "Name",

                      "Customers" . "Customer Name" "Customer Name" ,  

                     "Brand" . "Name"   'Brand Name',  

                     "Items" . "Item Name" "Item Name",  

                     "Invoice Items" . "Total (BCY) " 'Amount', 

                     "Invoice Items" ."Quantity" 'Quantity'


FROM    "Invoice Items"

LEFT JOIN "Invoices" ON   "Invoice Items" . "Invoice ID"   = "Invoices" . "Invoice ID"

LEFT JOIN   "Sales Persons"   ON   "Invoices" . "Sales Person ID"   = "Sales Persons" . "Sales Person ID"  


LEFT JOIN "Customers" ON "Customers" . "Customer ID"   = "Invoices" . "Customer ID"


LEFT JOIN   "Items" ON   "Items" . "Item ID"   = "Invoice Items" . "Product ID"


LEFT JOIN   "Brand"   ON   "Brand" . "Brand ID" = "Items". "Brand ID"  


WHERE 

                     "Invoices" . "Invoice Status"    not in ( 'draft'  , 'void'  )


UNION ALL


  SELECT

                   "Credit Notes". "Credit Note Date", 

                   "Sales Persons" . "Name" "Name",  

                   "Customers" . "Customer Name" "Customer Name", 

                   "Brand" . "Name"   'Brand Name',  

                   "Items". "Item Name" "Item Name", 

                   "Credit Note Items" . "Total",  

                   "Credit Note Items" ."Quantity"


FROM    "Credit Note Items"


LEFT JOIN "Credit Notes"   ON   "Credit Note Items" . "CreditNotes ID"   = " Credit Notes". "CreditNotes ID"  


LEFT JOIN "Sales Persons"   ON   "Credit Notes" . "Sales Person ID"   = "Sales Persons" . "Sales Person ID"


LEFT JOIN   "Customers" ON   "Customers" . "Customer ID"   = "Credit Notes" . "Customer ID"


LEFT JOIN   "Items" ON   "Items" . "Item ID"   = "Credit Note Items" . "Product ID"


LEFT JOIN   "Brand"   ON   "Brand" . "Brand ID" = "Items" . "Brand ID"  


WHERE 

                   "Credit Notes" ."Credit Note Status"   not in ( 'draft'  , 'Void'  )



Rank the Brands using Formulas

Create aggregate formulas over the above said query table to rank the brands. Here, we have ranked it based on brand popularity among customers and by sales amount 

Rank the Brands using Formulas

Create aggregate formulas over the above said query table to rank the brands. Here, we have ranked it based on brand popularity among customers and by sales amount. 

Rank Brand by Revenue 

The following formula will rank the brand by the amount of your sales. 


rank(sum  (  "Invoices-Credit Notes"."Amount"  ))



Rank Brand by Popularity

The following formula will rank the brand by the number of customers who picked it. 


rank(count ( "Invoices-Credit Notes"."Customer Name" ))



Creating Reports

Now you can create the customer preference reports over the query table we have created.

Top Brands by Revenue

This report gives the list of top brands by the revenue each brand has generated. 


Follow the below steps to create the report. 

  1. Open the query table we have created. 
  2. Click Create > Pivot View . The Pivot Designer will open.
  3. Add columns from the query table as follows. 
    • Row - Brand Name with Actual
    • Data - Top Brand based on Sales with Actual and Amount with Sum .
  4. Click the Click here to Generate Pivot. 



  5. Click the Show/Hide button and select Totals .
  6. Hide the Totals. The report will be generated. 

Top Brands by Popularity

This report gives the list of the top brands by the number of customers who have purchased it. 

Follow the below steps to create the report. 
  1. Open the query table we have created. 
  2. Click Create > Pivot View . The Pivot Designer will open.
  3. Add columns from the query table as follows. 
    • Row - Brand Name with Actual
    • Data - Top Brand based on Customers with Actual and 
  4. Click the Click here to Generate Pivot. 



  5. Click the Show/Hide button and select Totals.
  6. Hide the Totals. The report will be generated. 

Customer by Brands

This report gives an overview of customer purchases along with preferred brands.

Follow the below steps to create the report. 
  1. Open the query table we have created. 
  2. Click Create > Pivot View . The Pivot Designer will open.
  3. Add columns from the query table as follows. 
    • Row - Customer Name, Brand Name , and Item Name with Actual .
    • Data - Quantity and Amount with Sum .
  4. Click the Click here to Generate Pivot . Report will be generated. 

Copy the sample workspace from the below url to learn more on this. 


https://analytics.zoho.com/workspace/19601000018579001


    • 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

    • Zoho Browser??

      hai guys, this sounds awkward but can v get a ZOHO BROWSER same as zoho writer, etc. where i can browse websites @ home and continue browsing the same websites @ my office, as v have the option in Firefox, once i save and close the browser and again when i open it i will be getting the same sites. If u people r not clear with my explanation, plz let me know. Thanks, Sandeep  

    • SMS to customers from within Bigin

      Hi All, Is there anyone else crying out for Bigin SMS capability to send an SMS to customers directly from the Bigin interface? We have inbuilt telephony already with call recordings which works well. What's lacking is the ability to send and receive

    • No OR Filter for Views with a Related Modules Criteria

      We would like to create a Deal View where the User can see all their deals. For that, we would need an OR to connect the criteria. One of the Fields is a "multiselect User", these (Related Modules Criteria) can only be Filter with an AND. Even between

    • Admins cannot see each others' Scheduled Reports?!

      Very frustrating that as an admin I cannot see what my reports my fellow admins have created and scheduled.  After asking about this on the help chat, I was told the issue is trust and security.  By giving someone Admin status, it means we trust them with those responsibilities. Please change this, it is not a good process to have to bother other users to change a report or change users within a report.

    • 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

    • Writer update results in BitDefender blocking it as malware

      After updating Writer to latest update, Bitdefender blocked the app and writer no longer runs.

    • Is there a way to invoke deluge function from within a widget?

      Hi! I have custom functions in deluge and I was wondering whether there is any way to call this function through a widget? Something like on click of a button inside a widget, run the deluge custom function. Would this be possible?

    • Missing Import Options

      Hello, do I miss something or is there no space import option inside of this application? In ClickUp, you can import from every common application. We don't want to go through every page and export them one by one. That wastes time. We want to centralize

    • Zoho CRM Portal Field Level Permission Issue

      Hi Support Team, I am using the Zoho CRM Portal and configuring field-level editing permissions. However, we are unable to restrict portal users from editing certain fields. We have created a portal and provided View and Edit (Shared Only) access for

    • Why am I seeing deleted records in Zoho Analytics syncing with Zoho CRM?

      I have done a data sync between Zoho CRM and Zoho Analytics, and the recycle bin is empty. Why do I see deleted leads/deals/contacts in Zoho Analytics if it doesn't exist in Zoho CRM? How can I solve this problem? Thanks

    • Zoho Tables is now live in Australia & New Zealand!

      Hey everyone! We’ve got some great news to share — Zoho Tables is now officially available in the Australian Data Center serving users across Australia and New Zealand regions! Yes, it took us a bit longer to get here, but this version of Zoho Tables

    • Custom Fonts in Zoho CRM Template Builder

      Hi, I am currently creating a new template for our quotes using the Zoho CRM template builder. However, I noticed that there is no option to add custom fonts to the template builder. It would greatly enhance the flexibility and branding capabilities if

    • Introducing the Zoho Projects Learning Space

      Every product has its learning curve, and sometimes having a guided path makes the learning experience smoother. With that goal, we introduce a dedicated learning space for Zoho Projects, a platform where you can explore lessons, learn at your own pace,

    • All new Address Field in Zoho CRM: maintain structured and accurate address inputs

      The address field will be available exclusively for IN DC users. We'll keep you updated on the DC-specific rollout soon. It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition. Latest update

    • Import Function: ONLY update empty fields

      When setting up an import from a spreadsheet to CRM, there is a checkbox "Don't update empty values for existing contacts" (see screenshot below). While I see some limited benefit from this functionality, I think there should also be an "ONLY update empty

    • Collaboration with customers made easy with Zoom Meeting and Zoho Desk integration

      Hello everyone! We are happy to announce that you can now integrate your Zoho Desk account with Zoom Meeting. The integration bridges the gap between digital communication and human connection, empowering teams to deliver timely support when it matters

    • CRM Canvas - Upload Attachments

      I am in the process of changing my screens to Canvas.  On one screen, I have tabs with related lists, one of which is attachments.  There doesn't appear to be a way to upload documents though.  Am I missing something really obvious?  Does anyone have

    • TrueSync regularly filling up my local disk

      Seems that WorkDrive's TrueSync randomly starts filling up my local hard drive space. None of the folders have been set as "Make Offline" but still it seems to randomly start making file offline. The settings of the app is so minimal and is of no real

    • Kaizen #194 : Trigger Client Script via Custom buttons

      Hello everyone! Welcome back to another interesting and useful Kaizen post. We know that Client Scripts can be triggered with Canvas buttons and we discussed this with a use case in Kaizen#180. Today, let us discuss how to trigger Client Script when a

    • [Webinar] A recap of Zoho Writer in 2025

      Hi Zoho Writer users, We're excited to announce Zoho Writer's webinar for January 2026: A recap of Zoho Writer in 2025. This webinar will provide a recap of the features and enhancements we added in 2025 to enhance your productivity. Choose your preferred

    • Picklist field shows "none" as default

      Hello, Is there an option to avoid showing "none" as the default value in a picklist field? I also don't want to see any option displayed. My expectation is to have a blank bar, and then when I display the drop-down list, I can choose whichever I wa

    • Stage-probability mapping feature in custom module

      Hi, I'm building a custom module for manage projects. I would like to implement the stage-probability feature that Potentials has. Is this possible?

    • Create static subforms in Zoho CRM: streamline data entry with pre-defined values

      Last modified on (9 July, 2025): This feature was available in early access and is currently being rolled out to customers in phases. Currently available for users in the the AU, CA, and SA DCs. It will be enabled for the remaining DCs in the next couple

    • Field Description is very small

      Hello, The field Description in the activity is very small. Why don't try open a new window, or a bigger popup, or increase the width of the "popup". Example:

    • StatusIQ

      Please add StatusIQ to data sources. We using site24x7 and StatusIQ together and site24x7 integration is already there. Thanks and regards, Torsten

    • In Zoho People, the Operations buttons are frequently not visible or do not appear consistently.

      In Zoho People, the Operations buttons are frequently not visible or do not appear consistently. We request you to please investigate and address this issue, as it is affecting daily HR operations and user access.

    • Marketing Tip #14: Increase cart value with product bundles

      Bundling products is a great way to increase average order value while giving customers more convenience. Think “camera + tripod + memory card” or “soap + lotion + bath salts.” Bundles make shopping easier and feel like a better deal. It’s a win-win for

    • Problem with Workdrive folders

      I'm having a problem a problem accessing files in a Zoho work drive folder when using the Zoho writer app. The problem folder appears grayed out in the Zoho work drive window in both the online and writer application. However I can open the folder in

    • Sortie de Zoho TABLE ??

      Bonjour, Depuis bientôt 2 ans l'application zoho table est sortie en dehors de l'UE ? Depuis un an elle est annoncée en Europe Mais en vrai, c'est pour quand exactement ??

    • Add RTL and Hebrew Support for Candidate Portal (and Other Zoho Recruit Portals)

      Dear Zoho Recruit Team, I hope you're doing well. We would like to request the ability to set the Candidate Portal to be Right-to-Left (RTL) and in Hebrew, similar to the existing functionality for the Career Site. Currently, when we set the Career Site

    • Auto tracking URL generation based on Carrier

      Hi, While creating a shipment order for a package in Zoho Books, I have a requirement that for example, if the carrier is Delhivery and tracking number is 1234, then can automatically the tracking link/URL be generated as www.delhivery.com/1234. Similary,

    • Zoho Bookings form pre-filled with Zoho Forms in

      Hi, I've got a contact page on my website and I'd like to have the option to book an appointment (redirected to zoho bookings page) after an option is submitted on the contact form. how would I go about doing this? thanks

    • Pre-orders at Zoho Commerce

      We plan to have regular producs that are avaliable for purchase now and we plan to have products that will be avaliable in 2-4 weeks. How we can take the pre-orders for these products? We need to take the money for the product now, but the delivery will

    • Customer ticket creation via Microsoft Teams

      Hi all, I'm looking to see if someone could point me in the right direction. I'd love to make it so my customers/ end users can make tickets, see responses and respond within microsoft teams. As Admin and an Agent i've installed the zoho assist app within

    • Zoho Books' 2025 Wrapped

      Before we turn the page to a new year, it’s time to revisit the updates that made financial management simpler and more intuitive. This annual roundup brings together the most impactful features and enhancements we delivered in 2025, offering a clear

    • Can multiple agents be assigned to one ticket on purpose?

      Is it possible to assign one ticket to two or more agents at a time? I would like the option to have multiple people working on one ticket so that the same ticket is viewable for those agents on their list of pending tickets. Is something like this currently

    • This user is not allowed to add in Zoho. Please contact support-as@zohocorp.com for further details

      Hi Team, when I,m trying to create a email account (imagixmidia.com.br) it's showing this error >>  This user is not allowed to add in Zoho. Please contact support-as@zohocorp.com for further details plz help me  thanks

    • Edit default "We are here to help you" text in chat SalesIQ widget

      Does anyone know how this text can be edited? I can't find it anywhere in settings. Thanks!

    • Can't change form's original name in URL

      Hi all, I have been duplicating + editing forms for jobs regarding the same department to maintain formatting + styling. The issue I've not run into is because I've duplicated it from an existing form, the URL doesn't seem to want to update with the new

    • Feature Request: Sync Leave Tracker to Zoho Mail / Calendar or provide option to download information

      Zoho Leave Tracker offers the option to sync the leave Calendar to Microsoft 365 and Google Calendar. Adding an option to sync to Zoho-Mail Calendar would avoid duplication and add significant value for users. An alternative would be to allow users to

    • Next Page