FICILCOM Inc. LogoFICILCOM Inc.
  • Company
  • Company Overview
  • Mission · Vision · Values
  • Guidelines
  • Services
  • NeX-Ray
  • Xtrategy
  • Trial Job Change
  • Tsurugi
  • Recruit
  • Culture
  • Benefits
  • Process
  • FAQ
  • Open Positions
Contact
  1. Home
  2. Blog
  3. Campaign Management
  4. How to Build a KPI Tracking Sheet: Definitions, Formulas, and Forecast Examples

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

KPI管理表の作り方|Excelテンプレートと、運用に乗せるための型

Published: 08/20/2026

Last Updated: 10/10/2026

Category: Campaign Management, Marketing DX

Authors: Shusaku Yosa

Ready to put this into practice?

Start Xtrategy for free→

1-minute signup, free, cancel anytime

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)

  • Google Analytics: Collect campaign data with custom URLs
  • Microsoft: SUMIFS function

Related reading

  • How to Measure Campaign Performance: Shared KPIs, Deduplication, and GA4 Attribution
  • Marketing Campaign Progress Tracking: Linking Tasks, Actual Costs, and Open Commitments
  • Weekly Campaign Progress Template: Filled Examples and Review Rules

 

 

 

 

Table of Contents

  1. Separate KPI Definitions from Performance Snapshots
  2. Define Units, Periods, and Data Sources
  3. Calculate Counts, Rates, and CPA Correctly
  4. State the Assumptions Behind a Forecast
  5. Assign Updates and Handle Missing Data
  6. Turn Variances into Decisions and Actions

 

Company

  • Company
  • Company overview
  • Mission · Vision · Values
  • Guidelines

Services

  • Services

Blog

  • Blog
  • Categories
  • Authors

Estimate

  • Estimate simulator

Careers

  • Careers
  • Culture & Work Style
  • Benefits & Systems
  • Hiring Process
  • FAQ
  • Open Positions

Policies

  • Privacy Policy
  • Anti-Social Policy
  • Information Security Policy

Contact

  • Contact

Social

  • X
  • LinkedIn
  • Facebook
  • Pinterest
© 2026 Ficilcom Inc.

Related Articles

施策、営業への引き継ぎ、受注をつなぐ流れ / A connected path from a campaign through sales handoff to a won deal
Campaign Management11/16/2026

Revenue Marketing: Connect Campaigns, Sales, and Won Deals

Build a revenue marketing workflow with shared sales definitions, deal IDs, and cohort reviews. Compare campaigns withou...

Shusaku YosaRead more
進捗とは?読み方・意味・使い方と短い報告の例
Campaign Management09/18/2026

What Does Shinchoku Mean? Business Progress and Status Update Examples

Learn how to read and use 進捗 (shinchoku), distinguish related Japanese terms, and write concise progress updates with cl...

Shusaku YosaRead more
やることリストの作り方|空き時間・完了条件・相手待ちを入れた記入例
Campaign Management09/18/2026

How to Make a To-Do List: A Worked Plan with Available Time, Completion Criteria, and Waiting Tasks

Build a to-do list with completion criteria, calendar capacity, and waiting states. A worked example shows how to replan...

Shusaku YosaRead more