How to Build a KPI Tracking Sheet: Definitions, Formulas, and Forecast Examples

Published:
Last Updated:
Category: Campaign Management, Marketing DX
Authors: Shusaku Yosa
A KPI tracking sheet becomes useful when people agree on what each number means and what decision it supports. Targets and actuals alone are insufficient: different lead definitions, attribution rules, or cost scopes can make matching column names misleading. Start with a metric dictionary, then record dated performance snapshots against it. All figures and people in the examples below are fictional.
Separate KPI Definitions from Performance Snapshots
Keep stable definitions in one table and dated observations in another. The dictionary explains the metric; the performance table records what was known at a particular cutoff, what was expected next, and what the team decided. Link the two with a metric ID. When a definition changes, preserve its effective date rather than silently rewriting the meaning of historical results.
A document request and a qualified new enquiry are different measures. A form submission may still need an eligibility check before it counts as a qualified lead. Use a provisional label until that check is complete. A specific name is more useful than a shared “conversion” column that different people interpret differently.
There is no universal five-metric maximum. Ask who uses each measure and which decision it informs. Move measures with no current decision into supporting analysis, while retaining useful checks on volume, quality, and cost. Reducing the number of columns is not a substitute for defining them.
Define Units, Periods, and Data Sources
Define the numerator, denominator, unit, direction of improvement, period, scope, and source before entering results. Sessions, page views, and landing sessions are not interchangeable. Nor are paid-channel leads and enquiries from all sources. Both sides of a comparison need matching conditions.
ID and metric | Definition | Unit and direction | Period and source | Owner and cutoff |
|---|---|---|---|---|
K01 Qualified leads | Count eligible, deduplicated enquiry IDs | Count; higher | Weekly enquiry register, JST | Sato; Friday 15:00 |
K02 CPA | In-scope ad cost ÷ attributed K01 leads | JPY/lead; lower | Same attribution and period as the denominator | Yamada; Friday 15:00 |
K03 CVR | Attributed qualified leads ÷ in-scope sessions | Percent; higher | Matching entry scope and period | Sato; Friday 15:00 |
K01’s overall total is not automatically a valid denominator for a paid-channel CPA or a particular landing-page CVR. Confirm attribution and eligibility first. If the conditions cannot be aligned, display separate measures instead of calculating a misleading common rate. Record how long later exclusions or duplicate checks can change a provisional figure.
Campaign IDs and names also serve different purposes. Adding UTM parameters does not by itself join advertising costs to lead records. Verify the mapping between URL identifiers, analytics fields, and the operational register.
Calculate Counts, Rates, and CPA Correctly
Counts may be additive, but rates should normally be recalculated from their underlying totals. For a lower-is-better measure such as CPA, actual divided by target exceeding 100% is not success. Write the direction into the target: “at least 25 leads” or “no more than JPY 5,000 per lead.”
Metric | Target | Fictional input | Calculation | Interpretation |
|---|---|---|---|---|
Qualified leads | At least 25 | 20 leads | 20 ÷ 25 = 80% | Five short; also inspect time and remaining activity |
CPA | No more than JPY 5,000 | JPY 120,000; 20 attributed leads | 120,000 ÷ 20 = JPY 6,000 | JPY 1,000 above the cap |
CVR | At least 2% | 20 attributed leads; 1,000 sessions | 20 ÷ 1,000 = 2% | Comparable only under matching scope |
Suppose week A produces 20 leads from 1,000 sessions, or 2%, while week B produces 30 from 3,000, or 1%. The combined rate is 50 ÷ 4,000 = 1.25%. Averaging 2% and 1% gives 1.5%, which is not the combined result. Similarly, combine CPA as total in-scope cost divided by total in-scope leads, rather than averaging row-level CPAs.
With the denominator in Excel cell B2 and numerator in C2, an example formula is =IF(B2=0,"Not calculated",C2/B2). Format numerical results as percentages. This does not validate missing data: distinguish an unavailable input from a measured zero before calculating.
State the Assumptions Behind a Forecast
Separate observed results from forecast assumptions. If a 30-day campaign has produced 60 leads after 10 days, a constant daily-rate illustration is 60 ÷ 10 × 30 = 180 leads. Against a target of 200, that leaves a projected gap of 20. It does not mean the campaign is already 20 leads late on day 10.
A concentrated media burst, an event date, or delayed qualification can invalidate linear extrapolation. Record the remaining activity, unresolved inputs, calculation date, and method. Where the evidence is insufficient, write “not estimated” and identify the missing input instead of inventing a precise landing figure.
Field | Fictional entry |
|---|---|
Scope and cutoff | Qualified leads over 30 days; end of day 10 |
Observed result | 60 leads |
Reference forecast | 180, assuming constant daily generation |
Excluded factor | Additional distribution planned for day 20; audience volume unknown |
Next check | Owner confirms audience volume by day 12, then updates forecast |
Historical definitions and tracking conditions matter to the forecast too. If you show a range, explain which assumptions generate its upper and lower cases. A range without a basis is not a more reliable prediction.
Assign Updates and Handle Missing Data
Name the data collector, reviewer, backup, and update deadline. Centralizing entry with one person can simplify ownership, but it creates a bottleneck if no one can cover an absence. The backup needs access to the source, the update procedure, and the correction history.
Measured zero, unavailable, provisional, and not applicable are different states. An unavailable figure may reflect access or reporting delay; a provisional one may await qualification. Show the last update and avoid presenting stale values as current results.
- Collector updates values and the as-of time by Friday 15:00.
- Reviewer checks scope, dates, deduplication, attribution, and cost coverage.
- For missing data, record the reason, recovery owner, and expected resolution.
- For revisions, retain the correction date and explanation.
A shared source of truth helps version control, but banning duplicate files does not ensure data quality. Confirm permissions, version history, and recovery as well.
Turn Variances into Decisions and Actions
Use variance reasons to support action. A category such as “market conditions” or “another department” does not explain what needs checking. If the cause is unknown, keep it unknown and assign a specific investigation and deadline.
For a CPA of JPY 6,000 against a JPY 5,000 cap, first verify that costs and leads share the intended scope and period. Then examine distinct hypotheses such as eligibility changes, traffic cost, and landing-page behaviour. Whether to pause activity depends on approved spending limits and the campaign’s purpose, not on the ratio alone.
Replace “improve performance” with an executable entry: “Yamada checks ineligible-lead patterns by Wednesday; the owner then decides whether to change targeting.” A named action and decision date turn a reporting sheet into an operating tool.
Xtrategy’s Project Management brings campaign costs, task progress, workflows, and monthly plans into a project context. Start with your required fields and update responsibilities, then assess whether that arrangement fits your team.
References (checked October 10, 2026)
Related reading




