Creating Key Performance Indicator Dashboards with SQL

by dhaines.dev | Dec 9, 2024 | Business Intelligence, Tech How-To | 0 comments

In today’s data-driven business environment, having a streamlined way to visualize key performance indicators (KPIs) is essential for effective decision-making. Zoho Analytics provides powerful tools to help management quickly access and understand critical metrics. This blog post will guide you through creating an easy-to-use KPI dashboard in Zoho Analytics, focusing on metrics like meetings scheduled, proposals sent, and jobs closed.

We’ll also cover two essential SQL queries that can be used in Zoho Analytics to measure sales performance and close rates by sales representatives.

Why KPI Dashboards Matter

A KPI dashboard centralizes essential business data, enabling management to:

Monitor Sales Performance: Identify how well the sales team is converting proposals into closed deals.
Evaluate Productivity: Track the volume of meetings, proposals, and deals closed.
Make Data-Driven Decisions: Quickly spot trends and respond to changes in performance.

When your dashboard displays metrics clearly, it minimizes the need to dig through complex reports and enhances the ability to act quickly.

Identifying Key Metrics

Before building the dashboard, it’s crucial to identify which metrics matter most to your organization. Here are three key areas to consider:

Meetings Scheduled:
How often is your sales team engaging with prospects? This metric helps gauge outreach efforts and productivity.
Proposals Sent:
Tracking the number of proposals sent out provides insights into how actively your team is pursuing new business opportunities.
Closing Jobs (Deals Won):
The close rate gives a clear picture of sales effectiveness. A higher close rate means your team is converting more leads into successful deals.

Building Your Zoho Analytics Dashboard

Step 1: Create Visualizations for Key Metrics

To create a robust KPI dashboard in Zoho Analytics:

Add Widgets and Charts:
- Use widgets to show total meetings scheduled, proposals sent, and deals closed.
- Create bar charts to visualize sales performance over time.
Customize Filters:
- Add filters to segment data by sales representative, date ranges, or opportunity stages.
Use SQL Queries:
- Use custom SQL queries to generate detailed reports that feed into your visualizations.

Essential SQL Queries for Your Dashboard

Here are two powerful SQL queries you can integrate into your Zoho Analytics dashboard to track performance effectively.

1. Sales Close Rate by Salesman (YTD)

This query calculates the close rate by each sales representative for the current year:

sql

SELECT

O.`Opportunity Owner Name` AS “Salesman”,

COUNT(O.`Id`) AS “Total Opportunities”,

SUM(CASE

WHEN O.`Stage` = ‘Closed Won’ THEN 1

ELSE 0

END) AS “Closed Opportunities”,

ROUND((SUM(CASE

WHEN O.`Stage` = ‘Closed Won’ THEN 1

ELSE 0

END) / COUNT(O.`Id`)) * 100, 2) AS “Close Rate (%)”

FROM `Opportunities (Zoho CRM)` AS O

WHERE ((YEAR(O.`Proposal Send Date`) = YEAR(CURDATE())

AND O.`Proposal Send Date` >= CONCAT(YEAR(CURDATE()), ‘-01-01’))

OR (YEAR(O.`Created Time`) = YEAR(CURDATE())

AND O.`Created Time` >= CONCAT(YEAR(CURDATE()), ‘-01-01’)))

AND O.`Duplicate/Variation` <> TRUE

GROUP BY O.`Opportunity Owner Name`

ORDER BY `Salesman`

What It Does:

Shows the total number of opportunities per salesperson.
Calculates the percentage of deals that were successfully closed (Closed Won).

Visualization Idea:

Display the results in a bar chart with salespeople on the x-axis and close rates on the y-axis.

2. YTD Sales Performance Comparison

This query compares sales performance (total amount, number of deals, and average sale amount) for the current year versus the previous year:

sql

SELECT

O."Opportunity Owner Name" AS "Salesman",

/* Total Amount for Current Year */

SUM(CASE

WHEN YEAR(O.”Sales Agreement Signed Date”) = YEAR(CURDATE())

THEN O.”Final Sale Amount”

ELSE 0

END) AS “Total Amount_2024”,

/* Total Amount for Previous Year */

SUM(CASE

WHEN YEAR(O.”Sales Agreement Signed Date”) = YEAR(CURDATE()) – 1

THEN O.”Final Sale Amount”

ELSE 0

END) AS “Total Amount_2023”,

/* Number of Deals for Current Year */

SUM(CASE

WHEN YEAR(O.”Sales Agreement Signed Date”) = YEAR(CURDATE())

THEN 1

ELSE 0

END) AS “Number of Deals_2024”,

/* Number of Deals for Previous Year */

SUM(CASE

WHEN YEAR(O.”Sales Agreement Signed Date”) = YEAR(CURDATE()) – 1

THEN 1

ELSE 0

END) AS “Number of Deals_2023”,

/* Percentage Change in Total Amount */

ROUND((SUM(CASE

WHEN YEAR(O.”Sales Agreement Signed Date”) = YEAR(CURDATE())

THEN O.”Final Sale Amount”

ELSE 0

END) – SUM(CASE

WHEN YEAR(O.”Sales Agreement Signed Date”) = YEAR(CURDATE()) – 1

THEN O.”Final Sale Amount”

ELSE 0

END)) / NULLIF(SUM(CASE

WHEN YEAR(O.”Sales Agreement Signed Date”) = YEAR(CURDATE()) – 1

THEN O.”Final Sale Amount”

ELSE 0

END), 0) * 100, 2) AS “Total Amount % Change”

FROM `Opportunities (Zoho CRM)` AS O

WHERE O.”Stage” LIKE ‘%Won’

GROUP BY O.”Opportunity Owner Name”

ORDER BY O.”Opportunity Owner Name”

What It Does:

Compares total sales amounts and deal counts for the current and previous year.
Calculates percentage changes to identify trends in sales performance.

Visualization Idea:

Use side-by-side bar charts for a quick comparison of sales amounts.
Add percentage change widgets for a high-level overview.

Final Tips for an Effective Dashboard

Simplicity is Key:
Avoid clutter. Focus on the metrics that directly impact decision-making.
Interactivity:
Use filters and drill-downs to allow management to explore the data further.
Regular Updates:
Ensure your data sources refresh frequently so the dashboard remains relevant.
KPIs at a Glance:
Place the most critical KPIs at the top of the dashboard for immediate insight.

Conclusion

Creating a KPI dashboard with Zoho Analytics empowers your management team with the insights they need to track performance and make informed decisions. By leveraging key metrics and SQL queries, you can build a dashboard that is both functional and actionable.

← The Impact of Technology on Modern Businesses: How Starbucks, Netflix, and Disney Redefined Their Industries

Written By

About Daniel Haines

Driven by a passion for technology and design, I strive to create impactful digital experiences. With a background in software development and a keen eye for aesthetics, I am committed to crafting solutions that are both functional and visually appealing.

No Results Found

The page you requested could not be found. Try refining your search, or use the navigation above to locate the post.