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




                                  Zoho Desk Resources

                                  • Desk Community Learning Series


                                  • Digest


                                  • Functions


                                  • Meetups


                                  • Kbase


                                  • Resources


                                  • Glossary


                                  • Desk Marketplace


                                  • MVP Corner


                                  • Word of the Day



                                      Zoho Marketing Automation


                                              Manage your brands on social media



                                                    Zoho TeamInbox Resources

                                                      Zoho DataPrep Resources



                                                        Zoho CRM Plus Resources

                                                          Zoho Books Resources


                                                            Zoho Subscriptions Resources

                                                              Zoho Projects Resources


                                                                Zoho Sprints Resources


                                                                  Qntrl Resources


                                                                    Zoho Creator Resources



                                                                        Zoho Campaigns 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