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


    Access your files securely from anywhere









                          Zoho Developer Community





                                                • Desk Community Learning Series


                                                • Digest


                                                • Functions


                                                • Meetups


                                                • Kbase


                                                • Resources


                                                • Glossary


                                                • Desk Marketplace


                                                • MVP Corner


                                                • Word of the Day


                                                • Ask the Experts





                                                          Manage your brands on social media



                                                                Zoho TeamInbox Resources



                                                                    Zoho CRM Plus Resources

                                                                      Zoho Books Resources


                                                                        Zoho Subscriptions Resources

                                                                          Zoho Projects Resources


                                                                            Zoho Sprints Resources


                                                                              Qntrl Resources


                                                                                Zoho Creator Resources



                                                                                    Zoho CRM Resources

                                                                                    • CRM Community Learning Series

                                                                                      CRM Community Learning Series


                                                                                    • Kaizen

                                                                                      Kaizen

                                                                                    • Functions

                                                                                      Functions

                                                                                    • Meetups

                                                                                      Meetups

                                                                                    • Kbase

                                                                                      Kbase

                                                                                    • Resources

                                                                                      Resources

                                                                                    • Digest

                                                                                      Digest

                                                                                    • CRM Marketplace

                                                                                      CRM Marketplace

                                                                                    • MVP Corner

                                                                                      MVP Corner







                                                                                        Design. Discuss. Deliver.

                                                                                        Create visually engaging stories with Zoho Show.

                                                                                        Get Started Now


                                                                                          Zoho Show Resources


                                                                                            Zoho Writer Writer

                                                                                            Get Started. Write Away!

                                                                                            Writer is a powerful online word processor, designed for collaborative work.

                                                                                              Zoho CRM コンテンツ




                                                                                                Nederlandse Hulpbronnen


                                                                                                    ご検討中の方




                                                                                                          • Recent Topics

                                                                                                          • Kaizen #129 : Client Script Support for Blueprints

                                                                                                            Hello everyone! Welcome to another week of Kaizen. Today, let us discuss about how you can use Client Script during a Blueprint transtion to meet your requirements. This Kaizen post will provide solution for the post - Need non-mandatory fields in blueprint
                                                                                                          • Business Continuity - Disaster Recovery

                                                                                                            I know about the Zoho CRM backup .zip files, however, this doesn't include any of the infrastructure with like custom fields or custom modules. I am curious on what everyone has in place for a true backup or what your plan is if your Zoho instance were
                                                                                                          • Empowered Custom Views: Cross-Module Criteria Now Supported in Zoho CRM

                                                                                                            Hello everyone, We’re excited to introduce cross-module criteria support in custom views! Custom views provide personalized perspectives on your data and that you can save for future use. You can share these views with all users or specific individuals
                                                                                                          • Free webinar alert on October 16 - Less Complexity, More Security: Workplace + Directory

                                                                                                            Hello Zoho Workplace Community! Security and productivity shouldn't be at odds—and with Zoho, they're not. Discover how Zoho Workplace + Directory delivers seamless collaboration with enterprise-grade security, all in one integrated ecosystem. Join our
                                                                                                          • Narrative 11: Are your customers happy?

                                                                                                            Behind the scenes of a successful ticketing system: BTS Series Narrative 11: Are your customers happy? Happiness isn't just something you experience; it's something you remember. Hear your customers' voices by enabling customer happiness ratings in Zoho
                                                                                                          • Don't understand INVALID_REQUEST_METHOD when I try to post up an attachment

                                                                                                            When I make the POST request (using python requests.post() for files): https://www.zohoapis.com/crm/v8/Calls/***************01/Attachments I get this response: r:{ "code": "INVALID_REQUEST_METHOD", "details": {}, "message": "The http request method type
                                                                                                          • Organization Emails in Email History

                                                                                                            How can I make received Org Emails to show up here?
                                                                                                          • Signature field is showing black

                                                                                                            Hello, When customer signed the service form, it is showing as below picture Phone model: iPhone 16 Pro We tried delete and install application, but it not solved. This has on phone of a few person. There is any advice to solve this?
                                                                                                          • Zoho CRM Android app updates: record sharing, user image upload, Zoho Survey integration, and more

                                                                                                            Hello everyone, We've made some important improvements to Zoho CRM's Android app, and we'd like to walk you through the latest updates. Here's what's new: Record sharing with org users User image upload Zoho Survey integration support Venue preference
                                                                                                          • How to Delete/hide Google adwords section from Layout?

                                                                                                            Hi people, maybe someone can tell me how to remove or hide the Google adwords section from my layout? Im not using it and it takes too much space.
                                                                                                          • WIDGET in related record list ZOHO CRM; how to get and put data to subform custom fields?

                                                                                                            he need: Read and write two custom subform line-item fields on Quotes: Segment_wyceny (picklist/text) and W_pakiecie (number). Write works; read does not return these fields via SDK. Environment Zoho CRM Widget Zoho Embedded App SDK v1.2 Module: Quotes
                                                                                                          • Zoho CRM Formula - Current Time minus Date/Time field

                                                                                                            Hello, I am trying to prevent duplicate emails going to clients when more than 1 deal is being updated. To do this, I would like to create a formula to identify if a date/time field is >= 2 hours ago. Can someone please help me write this formula? Example:
                                                                                                          • ヒートマップ詳細設定について

                                                                                                            はじめまして マーケティング担当の浅田です。 PageSenceのヒートマップ設定について質問です。 単一ページ毎の設定は上手くできるのですが 詳細設定にて、トップページのURLを含ませ全体のヒートマップを計測できないか試したところ お知らせ:データを受信していません。と表示されてしまいます。 トップページURLで始まるページの条件も試しております。 全頁を反映させたいと思い、詳細設定を上手く設定できたらと考えております。 どなたか詳しい方がいらっしゃいましたら、教えて頂けませんでしょうか 宜
                                                                                                          • Zoho CRM still doesn't let you manage timezones (yearly reminder)

                                                                                                            This is something I have asked repeatedly. I'll ask once again. Suppose that you work in France. Next month you have a trip to Guatemala. You call a contact there, close a meeting, record that meeting in CRM. On the phone, your contact said: "meet me
                                                                                                          • Drag 'n' Drop Fields to a Sub-Form and "Move Field To" Option

                                                                                                            Hi, I would like to be able to move fields from the Main Page to a Sub-Form or from a Sub-Form to either the Main Page or another Sub-Form. Today if you change the design you have to delete and recreate every field, not just move them. Would be nice to
                                                                                                          • Introducing Assemblies and Kits in Zoho Inventory

                                                                                                            Hello customers, We’re excited to share a major revamp to Zoho Inventory that brings both clarity and flexibility to your inventory management experience! Presenting Assemblies and Kits We’re thrilled to introduce Assemblies and Kits, which replaces the
                                                                                                          • Way to update CRM records in quik view

                                                                                                            I have custom module in zoho crm and that module have 500 records. I want a quick way or UI so that user can easily update the record information in quick view without going to record detail view or edit view. I tried zoho sheet option but in zoho sheet
                                                                                                          • Writer sing up problom

                                                                                                            Zoho writer sing up prolom face
                                                                                                          • Add additional features to Zoho Tables

                                                                                                            Zoho Tables is a really great tool, why not add features like diagramming capability into the tool from applications like Draw.io which I believe is open source, you should be able to do wireframes, process flow diagrams, network design, etc. Please note
                                                                                                          • Where to integrate Price Book and Product List Price

                                                                                                            Hello, We sync zoho crm all modules with all data to zoho analytics. In zoho crm, we have "Price Books" and "Products" modules, where each product is assigned to a few price books with different list prices. From zoho crm, I am able to export a dataset
                                                                                                          • Form / CRM Integration Not entering into workflow

                                                                                                            I have a simple form setup with company name, first name, last name and lead source. Each of the fields are mapped to CRM Leads module. When the form is submitted, the lead is populated properly. I also have a workflow created that when the lead source
                                                                                                          • Automating CRM backup storage?

                                                                                                            Hi there, We've recently set up automatic backups for our Zoho CRM account. We were hoping that the backup functionality would not require any manual work on our end, but it seems that we are always required to download the backups ourselves, store them,
                                                                                                          • Zoho Slowness - Workarounds

                                                                                                            Hi all, We've been having intermittent slowness and Zoho just asks for same stuff each time but never fix it. It usually just goes away on it's own after a couple weeks. Given that speed is a very important thing for companies to be able to keep up with
                                                                                                          • First day of trying FSM in the field.

                                                                                                            What we found. 1. with out a network connection we were unable to start a service call? 2. if you go to an appointment and then want to add an asset it does not seem possible. 3. disappointed not to be able to actually take a payment from within the app
                                                                                                          • In arattai received message can't be deleted

                                                                                                            The issue has been noticed in following: arattai app (Android) arattai app (Window) arattai web While the message posted by me may be deleted, the ones received from others can't be. The item <Delete> change to <Report> when the message is a received
                                                                                                          • Exclude Segment from Campaign Recipients

                                                                                                            I've created two Segments in order to separate Non-Marketing Contacts from Marketing Contacts. I'd like to send an Eblast to all Marketing contacts in my lists, but when I go to select Recipients, I have two options: 1. To Choose Lists to Send to; 2.
                                                                                                          • Don't understand why Forms Mobile Scan and Fill not working

                                                                                                            I have configure enabled mobile scan and fill, I have enabled QR and Bar code on two fields name and position, I have mapped seq 1 to Name and seq 2 to position, I have created a 2d QR code with the person names and position, seperated by a comma. When
                                                                                                          • Recurring Supervisor Rule Reminders for Open/In-Progress Tickets

                                                                                                            Hello Zoho Support Team, I would like to suggest a potential improvement regarding reminders for tickets and activities in Zoho Desk. Currently, it is possible to set reminders only once. In the Supervisor Rules section, it is possible to configure reminders
                                                                                                          • Syntax for URLs in HTML Snippets

                                                                                                            What are some best practices for inserting a URL in an HTML snippet? I've looked at Zoho Help articles on navigation-based and functional-based URLs, but I'm still unclear on how to incorporate them in an HTML snippet. For example, 1. How do I link to
                                                                                                          • Template usage

                                                                                                            Hi, We are using some templates as a response to customer questions. Is it possible to analyze the usage of these templates? We want to know if the use of our templates has increased over time
                                                                                                          • Round robin

                                                                                                            Hi, I'm trying to set up a round robin to automatically distribute tickets between agents in my team but only those tickets that are not otherwise distributed by other workflows or direct assignments. Is that possible and if so which criteria should I
                                                                                                          • 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
                                                                                                          • How to mute chat notification sound by default in Zoho SalesIQ?

                                                                                                            We’ve recently embedded the Zoho SalesIQ chatbot on our website, and we’ve noticed that notification sounds sometimes play even when the visitor hasn’t interacted with the chat widget yet. We’re trying to understand two things: Why do these sounds occur
                                                                                                          • Ticket Status Colors

                                                                                                            Can i change the colors of Ticket Status in the admin panel? Or even change the background of the entire cell of a Critical ticket? This way its easy for my agents to see a urgent ticket when it comes in. Right now everything is black text. Here Right
                                                                                                          • Sync Lookup Fields from Zoho CRM

                                                                                                            HI Team, I have synced a lookup field from my CRM data to Campaigns. When I view the synced data the field appears to display a Zoho CRM record ID rather than the text value. Is it possible to get the sync to import the text value rather than the CRM
                                                                                                          • New From Address cannot verify

                                                                                                            I have created a new From Address, which is the support@ address for my domain, that forwards to the default support mailbox. Presumably then, the verification email that is sent, should turn up as a ticket, but it does not. How can I verify my from address so that I can use my own domain?
                                                                                                          • Closing Accounting Periods - Invoice/Posting dates

                                                                                                            Hi, I have seen in another thread but I'm unsure on how the 'transaction locking' works with regards to new and old transactions. When producing monthly accounts if I close December 24 accounts on 8th Jan 25 will transaction locking prevent me from posting
                                                                                                          • How to update/remove file in zoho creator widgets using javascript API

                                                                                                            Hi Team, I have developed a widget which allows inserting and updating records I have file upload field with multiple file upload. Now while doing insert form record, I am using uploadFile API to upload files for that record. I am using updateRecord API
                                                                                                          • Transitioning to API Credits in Zoho Desk

                                                                                                            At Zoho Desk, we’re always looking for ways to help keep your business operations running smoothly. This includes empowering teams that rely on APIs for essential integrations, functions and extensions. We’ve reimagined how API usage is measured to give
                                                                                                          • Sheet View in CRM portal

                                                                                                            Hi, When will it be possible for my CRM portal users to edit/add records with Sheet View? George
                                                                                                          • Next Page