# QLQD Dashboard

## Business Overview
QLQD is a hosistic program, covering all parameters/areas of customer buying experiance at TVSM dealerships.

QLQD is basically a **Quality Leadership through Quality Dealership**. Based on different parameters we are giving scores to different Dealerships.

A Quartarly Audit is conducted by TMs at every Dealership and cross audit will be conducted at every dealership once a year.

Based on this dashboard the gaps are noted by TVS Area, Zone & Territory wise and many other filers as given below.


QLQD Dashboard is actually majorly a Dataset so we do not need to be completely dependent on any other dataset like golden dataset. We have data coming from 3 sources -

1. Azure DataBricks - (Hawkeye,BumbleBee) Views.
2. Workspace - Excel Files
3. DataFlows - (fse_golden)

-- The Detailed breakdown of which table in this dashboard is coming from which source is mentioned in this [sheet](https://docs.google.com/spreadsheets/d/1dY9V7-7Th-tpdZGlRb0q0L2XHWerr7VK0n80DZ-hL_Y/edit?usp=sharing).

So According to how dealership performs we have divided the dealerships into 4 categories.

* Objective is to have min. 50% of the dealers in platinum category and 80% of the deaslers in platinum + Gold Category.

We have detailed data for scores for different dealerships. Total score which can be given to Dealer is 500 and score % is score/500. On this % score the category is decided.

### Breakdown of QLQD Dashboard
The scoring is based on three main segments of QLQD -
1. Sales
2. Service
3. Parts

These 3 segments have their independent Pages in the dashboard where the gaps of the respective segment is analysed.

* In the dashboard we have -
1. **Landing Page is - Auto Populated Score**
2. **Second Page is - Sales Auto Populated Score**
3. **Third Page is - Service Auto - Populated Check Points**
4. **Fourth Page is - Parts Auto - Populated Checkpoints.**

Each segment page like sales, service and parts have one more page where user can see all checkpoints at one place. We can navigate to that page from the segment page only.

### Parameters, Sub-Parameters & Checkpoints

* **What is Parameter?**
Parameters are the areas of customer buying experiances under QLQD.
* **What is Sub-Parameter?**
Parameters consists of many small areas of customer buying experiances called as sub-parameters.
* **What is Checkpoint?**
Checkpoints are the more granular points/scoring areas which comes under sub-parameters. We have a scoring logic for each checkpoint for which we have to build a Measure which is applied to each dealer.

So there are parameters and sub-parameters on the basis of which the gaps in the sales, service and parts is calculated on their resp pages. The segment wise paramters & Sub-Parameters are listed below -

So the **checkpoints** are the ones which come under sub parameter(multiple things that come under subparameter) on the basis of which the delaership can be judged.

**1. Sales** (Parameters & Sub-Parameters & Checkpoints)
:::success
:pencil: Please read the below pointers as writtwn below.
* Parameters
* Sub-Parameters
* Checkpoints
:::
* Sales Process
* Enquiry Management
* Walk-In Enquiries: Same day Follow up
* Digital Enquiries: Same day Follow up
* DAR - Digital Aided Retails
* Customer Feedback Process
* Follow up calls after sales and their satisfaction levels and vehicle experience obtained- PSF calling %
* Planning and DWM
* Digital tools usage AMD - DMS Usage %
* Working Capital
* Adequate stock - 30 days as per month plan (DRS plan)
* Billing as per eMOS
* Monthly Retail Ach AMD
* %MS(Market Share) Gain
* Customer Experiance
* Product Explanation
* Did the sales staff highlight features and benefits using product brochures matching customer needs while presenting the vehicle
* Buyer NPS
* Non Buyer NPS
* Sales Staff
* Availablity%
* Check for manpower (DSE) availability as per TVS norms and if updated DMS.
* Sales Staff quality%
* SL3 Availability
* Beginner Certified
* 2 Product Category Certified
* LMS Usage >60 min / DSE / Month
* DSE Prod%
* Sales Person%
* Network & Branch Management
* AD Management%
* Availability of Stock at AD's
* Exclusive AD Business development manager available for managing the AD business.
* AD Sales Return - Less than 2% for the month.
* Digital tools usage/EMS usage at ADs.
* AD Enquiries- Same day follow up.
* Monthly Retail Ach AD
* Employee Management
* Mediclaim%
* Attrition score%
* HR Practices
* Mediclaim Benefits to Employees
* Showroom Infrastructure
* Showroom Infrastructure Score %
* New Retail Identity Implementation
* Accessories
* Accessories score%

**2. Service** (Parameters & Sub-Parameters & Checkpoints)
* Manpower
* Service Staff Availiblity
* Works Manager
* Technical Service Advisor (SA)
* Shop floor supervisor (SFS) & Warranty incharge (WI)
* CCE for PSF & Reminder
* HR Person
* Technicians & Expert Technician Availability
* In - house trainer availble (Bonus)
* EV Manpower (Bonus)
* Service Process
* PDI App Usage
* PDI App Usage
* DIFS
* Employee Engagement
* SA & Technician attrition
* Warranty
* Warranty Claim Submission to TVS-M
* SA&T
* Delivery Process
* Digital instant feedback system should be displayed at dealership (Bonus)
* efficiency(Process)
* Efectiveness System Process
* Efficency
* Pending vehicle status updated in DMS & Same day delivery >85%
* Use of TVS Forum
* 7 Days working workshop
* AMD - Service Branch Implementation and NDI achievement (Bonus Marks)
* Pick up & Drop Facility
* WIP App. and Digi app Usage
* Contribution of Paid Service Appointment to total Paid Service Inflow
* CSI/NPS
* Lead time(S)
* CC/10000
* vehicle perf
* D2S follow up
* Profitability
* NDI
* Last Free
* paid service
* AMC Conversion
* AMC Redemption
* Average Labour
* RSA
* Retention
* LPV

**3. Parts** (Parameters & Sub-Parameters & Checkpoints)
* Manpower(parts)
* PSA
* Stock Management Process
* total parts
* DMS
* Stock management Result
* Billing Consuption
* PMP
* Tyre Workshop

:::info
**IMP:- Creating Checkpoints**
So dealing with checkpoints first thing you need to do is to import the source data shown in the requirement document --> then connect them with Dim Dealer and Dim Date --> then Create a measure
:::

## Dashboard Visuals

### Page 1 - Auto Populated Checkpoints

#### Visual 1 - Parameters Score Quarterly
This visual(stacked bar chart) tells us about the paramter score% by Sales/Service/Parts.
The value on x-axis is coming from DimDate and Y-axis from measures as named in the image below.



#### Visual 2 - Dealer Category
This visual(Bullet Chart) tells us about the delaers are in which category. The caegories are calculated using following measures -



#### Visual 3 - Auto Populated scores
In this visual(Matrix) we have detailed data for scores for different dealerships. Total score which can be given to Dealer is 500 and score % is score/500. On this % score the category is decided.

The values are coming in this visual is as follows -




### Page 2 - Sales Auto Populated Scores
#### Visual 1 - Parameters

The visual (Paypal KPI Dougnut Chart) has induisual 7 doughnut charts grouped together that denotes the Score% of each parameter.

Below that we have Bookmarks that drill down the each Paramater and the visuals that come under that.

Before starting the drilldown one thing we must know is there are two visuals which are constant throughout the 7 Parameters.
* Parameter score - Sales
This is the same thing as above KPI charts but the values are rounded off and shown using visual(stacked column chart)

* Parameter Wise Score - Zone Wise
This visual(table) shows the Zone wise breakdown/distrubution of scores% by each Zone.

Lets drill down each Parameter and see what all visuals come under them.

**1. Sales Process**

* **Dealer Count - Sales Process**
This Visual(matrix) contains count of dealers with % less than 50, Between 50 & 75 and greater than 75 where the values are coming from following metrics -

    *  Greater than 75% - 'Metrics'[No of DLRS with sales process% > 75]
    *  Between 50% and 75% - 'Metrics'[No of DLRS with sales process% btwn 50 & 75]
    *  Less than 50 - 'Metrics'[No of DLRS with sales process% < 50]


* **Sub Parameters**
This visual(Bullet Chart) have Sub-Parameters for sales process where the values are coming from following metrics-

    * Enquiry Management - 'Metrics'[EE %]
    * Customer Feedback Process - 'Metrics'[PSF %]
    * Planning & DWM - 'Metrics'[P&D]
    * Working capital - 'Metrics'[WC %]
    * Monthly Retail Ach AMD - 'Metrics'[AMD ret Score]
    * % MS Gain - 'Metrics' - [MS %]

* **Check Points**
This visual(Matrix) contains Dealer names and the check points/ Scores in front of each dealer. The scoring logic of checkpoint is calculated in the metrics given below

    * Dealer - DimDealer[Dealer]
    * DAR Score (4)
    * Digital enq (Sameday follow Up) (3)
    * PSF Calling score (3)
    * AMD Usage (3)
    * Billing - Emos (3)
    * 30 day stock AMD (10)
    * Monthly Retail Plan Ach (Result Measure) (20) -- 'Metrics'[AMD Retail Score - fse]
    * % MS Gain (Result Measure) (10) - 'Metrics'[MS -Score]


**2. Customer Experiance**
* Dealer Count -