Reliable Target-Tracking System & Klipfolio Dashboard

This project began with a deceptively simple question from the Managing Director: “Can you build something—anything—that helps us track daily targets more accurately?” What followed was one of the most technically demanding and strategically impactful solutions I’ve delivered to my client.

The Business Development division operated with daily KPIs for each manager, but lacked a single, trustworthy source of truth. Bank holidays, individual time off, and monthly changes in targets were throwing off every dashboard. Performance couldn’t be evaluated in context because the “target” side of the equation was fragmented and unreliable.

I was given full freedom to solve this problem, and I approached it from both a data engineering and a strategic dashboarding angle. The result was a robust 6-sheet Google Sheets setup that now feeds directly into a 3-part Klipfolio dashboard. This system not only provides accurate, dynamic targets per BDM, per day—but also gives clear visibility into general performance trends, individual contributions, and monthly consistency.

When I was tasked with building this system, the challenge wasn’t just visualizing KPIs—it was about building trust in the numbers. The Business Development team had long struggled to answer a simple question: Are we on track?

There was no central place where daily BDM targets were accurately calculated. Bank holidays, sick leave, and vacation days made the math unreliable, and dashboards built on shaky inputs only led to confusion. So I built a custom 6-sheet Google Sheets system that accounted for:

US bank holidays

Individual time off (tracked and mapped to each BDM)

Monthly working days calculations

Daily targets, per person, per day

A master sheet that brings all of it together with 18 columns of interconnected logic

I also created a companion "Info" sheet to guide internal stakeholders on how to maintain and update the system with minimal effort.

Step 2 – Creating the Dashboard

With a clean, dynamic data source, I moved to Klipfolio and designed a dashboard with three clear sections:

General Overview: Are we on target? Gauge-style charts for Calls, Sales, and Appointments show actual vs. target instantly, including % over/under target.

Monthly Overview: How consistently are we on target? Trend analysis through monthly breakdowns highlights dips (e.g., summer slumps) and helps identify patterns.

Individual View: What is my contribution? Filter by BDM to get a full picture of monthly and YTD progress, including how much ground is left to cover to stay on target.

🧩 Biggest Technical Challenge

Matching targets dynamically with the x-axis in the dashboards was the trickiest part. I had to build complex formulas using:

LOOKUP, GROUP, GROUPBY

Nested SELECT statements

Variable inputs based on filters (like month or sales rep)

This was highly technical, but necessary to ensure the dashboards could scale across timeframes and team members without breaking.

This system solved a long-standing issue in the business: it brought clarity to daily target tracking. For the first time, both leadership and individual BDMs could rely on a shared, consistent source of truth for performance metrics.

What changed:

✅ Accurate target tracking across variable working days, holidays, and individual leave

✅ Full transparency for each BDM—where they stand, where they’re headed, and how to adjust

✅ Data alignment between Google Sheets and Klipfolio—one system powering both planning and real-time dashboards

The Managing Director called the solution “excellent.” It didn't just support reporting—it enabled better decision-making, accountability, and forecasting at every level.