Build This Project · Portfolio Guide
Customer Support Performance Dashboard — Complete Project Guide
The most common mistake in support dashboards is ranking agents by the number of tickets they close. The agent at the top is often the one who picks easy chats, while the colleague who takes long email escalations looks slow. A good support dashboard measures speed, quality and effort together, and shows them in a way that is fair. That is what this project builds.
It is a strong portfolio piece if you are coming from a BPO or customer-service role, because you already know what these numbers feel like from the inside.
The data you need
One row per ticket, with at least: ticket ID, channel (chat, email, phone), agent, created time, first response time, resolved time, number of customer contacts, whether it was reopened, and the CSAT survey score if the customer replied. Public options include customer support ticket datasets on Kaggle, or you can simulate one. Help-desk tools such as Zendesk and Freshdesk export similar fields, so the same model works on real data.
Define the metrics carefully
| Metric | Definition used here | Watch out for |
|---|---|---|
| First response time (FRT) | Minutes from ticket creation to the first human reply | Auto-replies should not count |
| SLA breach % | Tickets whose FRT exceeded the target (60 minutes in this example) ÷ all tickets | Different channels usually have different targets |
| First contact resolution (FCR) | Tickets solved with one customer contact ÷ all tickets | Agents can close tickets early to inflate it; check reopen rate alongside |
| Resolution time | Hours from creation to final resolution | Skewed by a few long cases; use the median as well as the mean |
| Reopen rate | Tickets reopened after being marked solved ÷ all tickets | The honest check on FCR and speed |
| CSAT | Average survey score (1–5) among customers who replied | Low reply rates make it unreliable; always show the reply rate |
Compute them in SQL
This runs as-is with Python's built-in sqlite3 module, on nine sample tickets:
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE tickets (
id INTEGER, channel TEXT, agent TEXT,
first_response_min INTEGER, resolution_hours REAL,
contacts INTEGER, reopened INTEGER, csat INTEGER -- csat 1-5, NULL = no survey reply
);
INSERT INTO tickets VALUES
(1,'chat','Meera', 4, 0.5, 1, 0, 5),
(2,'chat','Meera', 6, 1.0, 1, 0, NULL),
(3,'chat','Rohit', 3, 0.4, 2, 1, 2),
(4,'email','Rohit', 95, 20.0, 3, 0, 3),
(5,'email','Meera', 40, 6.0, 1, 0, 4),
(6,'email','Sana', 70, 30.0, 2, 1, NULL),
(7,'phone','Sana', 1, 0.2, 1, 0, 5),
(8,'phone','Sana', 2, 2.0, 2, 0, 4),
(9,'email','Sana', 130, 48.0, 4, 1, 1);
""")
query = """
SELECT channel,
COUNT(*) AS tickets,
ROUND(AVG(first_response_min), 1) AS avg_first_resp_min,
ROUND(100.0 * SUM(first_response_min > 60) / COUNT(*), 1) AS sla_breach_pct,
ROUND(100.0 * SUM(contacts = 1) / COUNT(*), 1) AS fcr_pct,
ROUND(AVG(csat), 2) AS avg_csat,
ROUND(100.0 * COUNT(csat) / COUNT(*), 1) AS survey_reply_pct
FROM tickets
GROUP BY channel
ORDER BY sla_breach_pct DESC, channel;
"""
cur = db.execute(query)
print(" | ".join(d[0] for d in cur.description))
for row in cur:
print(" | ".join(str(v) for v in row))
agents = db.execute("""
SELECT agent, COUNT(*) AS tickets,
ROUND(100.0 * SUM(reopened) / COUNT(*), 1) AS reopen_pct,
ROUND(AVG(csat), 2) AS avg_csat
FROM tickets GROUP BY agent ORDER BY agent;
""")
print()
for row in agents:
print(row)
Output:
channel | tickets | avg_first_resp_min | sla_breach_pct | fcr_pct | avg_csat | survey_reply_pct
email | 4 | 83.8 | 75.0 | 25.0 | 2.67 | 75.0
chat | 3 | 4.3 | 0.0 | 66.7 | 3.5 | 66.7
phone | 2 | 1.5 | 0.0 | 50.0 | 4.5 | 100.0
('Meera', 3, 0.0, 4.5)
('Rohit', 2, 50.0, 2.5)
('Sana', 4, 50.0, 3.33)
Two SQL details are worth knowing. In SQLite a comparison such as first_response_min > 60 returns 1 or 0, so SUM() counts the matching rows. And AVG(csat) and COUNT(csat) skip NULLs, which is exactly right for survey scores, but you must show the reply rate next to the average. For a refresher on grouping and joining, see SQL joins in real projects.
Read the results like a support manager
- Email is the problem channel: 75% of email tickets breached the 60-minute first-response target and only 25% were solved in one contact. That is a staffing or routing question, not an individual agent issue.
- Meera looks best, but her mix is easiest: two of her three tickets were quick chats. Sana took both long email cases that reopened, plus phone calls.
- Rohit's low CSAT comes from two replies only. With so few survey responses, a single unhappy customer moves the average a lot.
The lesson for your dashboard: compare agents within the same channel and ticket type, and always show the volume and survey reply count next to any rate.
Dashboard layout
- Header cards: tickets this week, SLA breach %, FCR %, median resolution time, CSAT with reply rate.
- Channel view: a table or bar chart of the channel query above, with targets marked.
- Trend: daily ticket volume against SLA breach %, to show whether breaches follow volume spikes (for example after a sale or a billing run).
- Backlog: open tickets by age bucket (under 4 hours, 4–24 hours, over 24 hours).
- Agent view: filtered by channel, showing tickets, reopen rate and CSAT side by side, with no single "rank" column.
- Topic view: top contact reasons if your data has categories; this is where product fixes come from.
Power BI, Tableau and Looker Studio all handle this. If you are building in Power BI, Power BI for business case projects shows how to structure the report around questions.
Write it up for your portfolio
- State the stakeholder (a support team lead) and the three questions the dashboard answers.
- List each metric's exact definition, including the SLA target you chose.
- Include the SQL in the repository and screenshots plus a live link.
- Add two or three findings and one recommendation each, saying clearly if the data is simulated.
To round out a portfolio, add a project from another domain such as the supply chain dashboard or customer churn analysis. If you are moving from a support job into analytics, the BPO to SQL and Power BI route explains how to present that experience.