Home / admin / analytics-kpi-definition
analytics-kpi-definition
Use this skill to define, document, and validate KPI metrics for CRM Analytics — covering metric formula design, dimension selection, target-dataset modeling, benchmark setting, and the KPI register that must exist before any dashboard or lens is built.
Open the skill folder on GitHub
Analytics KPI Definition
This skill activates when a practitioner needs to define, document, and validate KPIs for CRM Analytics before any lens or dashboard is built. It produces a KPI register — the canonical artifact that specifies metric formulas, dimension groupings, target attainment models, and benchmark values — that the dashboard builder uses as the single source of truth.
Before Starting
Gather this context before working on anything in this domain:
- KPI definition must happen before dashboard design. Building a lens before the KPI formula is agreed upon leads to re-work when stakeholders dispute the calculation method.
- CRM Analytics distinguishes measures from dimensions at the dataset level. A measure is a numeric field used in aggregation; a dimension is a categorical field used in grouping. This distinction is set when the dataset is configured and affects what aggregation functions are valid.
- The most common wrong assumption: practitioners conflate KPI definition (agreeing on the formula and target) with dashboard wiring (configuring the tile type and chart). These are separate activities — KPI definition is a pre-build requirements task.
- Target attainment in CRM Analytics is modeled by loading a separate targets dataset and joining it at query time via SAQL or recipe — it cannot be done by editing the source dataset in place.
Questions to Ask Before Configuring
Each question traces to a gotcha in references/gotchas.md.
| Question | Why it matters | What a good answer adds | What proper configuration adds over just doing it |
|---|---|---|---|
| "What exactly counts, what is excluded, and is it a row count or a distinct count?" | A count on a dimension is a unique count, and dimensions cannot be summed (Gotcha 2) | Inclusion and exclusion rules and an explicit count() or unique() | Every dashboard computes the same number for the same KPI |
| "Which groups must appear even with no activity: territories with no wins, reps with no deals?" | An inner cogroup drops unmatched groups (Gotcha 4) | The population stream and a left outer join with coalesce() | Zero results show as zero instead of disappearing |
| "At what grain are targets set, who maintains them, and in what key format?" | Targets join by exact, case-sensitive keys (Gotchas 1, 3) | A targets dataset schema, an owner, and the key format | Attainment never goes blank because of a spelling difference |
| "Does a blank value mean zero or unknown for each measure?" | Null measure handling decides how blanks aggregate (Gotcha 5) | A null rule per measure and an explicit population filter | Averages are not dragged down by records that should not count |
| "Which currency and which fiscal calendar does each KPI use?" | No currency conversion, and custom fiscal years must be imported (Gotcha 6) | A currency rule, a converted field, and the fiscal setting | Fiscal-quarter KPIs agree with Salesforce reports |
| "How far back must the KPI trend, and is that history in the source data?" | Snapshot trending has row and dataset caps (Gotcha 7) | A history source per KPI and a snapshot volume estimate | Multi-year trends are promised only where the platform can hold them |
What proper configuration adds over "just building the lens": a signed register whose formulas say how to count, which groups to keep, how blanks behave, and which currency and calendar apply, so every dashboard built from it agrees.
Core Concepts
Measures vs Dimensions
CRM Analytics datasets have two field types that affect KPI modeling:
- Measures — numeric fields (Amount, Quantity, Revenue). Aggregation functions (sum, avg, count, max, min) apply. Used in KPI formulas.
- Dimensions — string/categorical fields (Stage, Owner, Region). Used in GROUP BY expressions. Cannot be summed or averaged.
A KPI formula must correctly identify which fields are measures (to aggregate) and which are dimensions (to group by). Using a dimension in a SUM() function produces a SAQL error.
KPI Target Attainment Pattern
CRM Analytics does not support inline target values in a dataset. The canonical pattern:
- Create a separate targets dataset with columns matching the KPI's dimension groupings (e.g., Owner, Region, Quarter) plus a Target value measure
- Join the targets dataset to the actuals dataset at query time using a SAQL left outer cogroup from the targets side (or a recipe join), with
coalesce()for groups that have no actuals - Compute attainment as
actual / target * 100
Target datasets must be updated on the same cadence as the reporting period (quarterly targets loaded quarterly). The join key between actuals and targets must be an exact string match — case differences cause nulls.
Leakage and Proxy Field Risk
Einstein Discovery and CRM Analytics KPI definitions share a common pitfall: using fields that are causally downstream of the outcome (leakage) or fields that are proxies for the outcome (e.g., using Opportunity Close Date in a win-rate formula when Close Date is only populated on Closed Won/Lost records):
- A field filled in only after the outcome is known is a leakage risk for predictive KPIs
- For descriptive/historical KPIs, this is less of a concern but still affects filter logic
Common Patterns
Pattern: KPI Register
When to use: Before any CRM Analytics lens or dashboard is built — for every KPI stakeholders need.
How it works:
- For each KPI: document the metric name, plain-English definition, aggregation formula (SUM/AVG/COUNT/RATIO), dimension(s) to group by, and the dataset + field path
- Document target model: fixed value, external targets dataset join, or formula-derived
- Document benchmark: industry benchmark, historical baseline, or threshold
- Validate: confirm the formula fields are measures in the dataset (not dimensions)
Why not the alternative: Building lenses without a KPI register leads to multiple interpretations of the same metric, inconsistent calculation across dashboards, and rework when stakeholders reject the formula.
Pattern: Target Dataset Schema Design
When to use: When any KPI requires attainment tracking (actual vs target).
How it works:
- Define the granularity of targets: by Owner, by Region, by Quarter, or some combination
- Create a targets dataset schema with columns: all grouping dimensions + Target_Amount (or equivalent measure)
- Define the load schedule: targets are uploaded via CSV or External Data API on the same cadence as reporting periods
- Document the SAQL join key: the exact field name and format used to join actuals to targets
Decision Guidance
| Situation | Recommended Approach | Reason |
|---|---|---|
| KPI is a simple sum or count from one dataset | SAQL measure aggregation in lens | Straightforward — no target dataset needed unless attainment tracking required |
| KPI requires target vs actual comparison | Separate targets dataset + SAQL cogroup | CRM Analytics cannot store targets inline in actuals dataset |
| KPI grouping field is a string (Region, Owner name) | Use as dimension in GROUP BY | String fields are dimensions and cannot be aggregated |
| Stakeholders use different formulas for the same metric | KPI register with signed-off definition before build | Locking the formula before build prevents mid-project disputes |
| KPI involves multiple datasets (e.g., Opp + Account data) | Recipe join before dataset publish | Join in recipe so the output dataset has all needed fields before KPI formula is applied |
Recommended Workflow
Step-by-step instructions for an AI agent or practitioner working on this task:
- Collect all KPIs stakeholders mention — even informally named ones. Document every metric and the person requesting it.
- For each KPI: write a plain-English definition that specifies what counts (included criteria), what excludes (excluded criteria), the time period, and the granularity.
- Map each KPI to CRM Analytics dataset fields: identify which field is the measure to aggregate, which fields are dimensions for grouping, and confirm field types in the dataset configuration.
- Identify KPIs that require target attainment: for these, design the targets dataset schema — dimension columns matching the KPI grouping, Target measure column, join key to actuals.
- Document SAQL formula sketch for each KPI:
sum(Measure) groupby [DimensionField]with target join pattern if applicable. Use the register format and attainment query inreferences/examples.md, Example 3. - Get stakeholder sign-off on the KPI register before any lens or dashboard is built — record who approved each formula and target definition.
- Hand off the signed KPI register to the dashboard builder as the authoritative specification.
Review Checklist
Run through these before marking work in this area complete:
- [ ] Every KPI has a plain-English definition with inclusion/exclusion criteria
- [ ] Measure vs dimension classification confirmed for each field used in formulas
- [ ] Target attainment model specified (fixed value / targets dataset / formula)
- [ ] Targets dataset schema designed for all KPIs requiring attainment tracking
- [ ] SAQL formula sketch validated against dataset field types
- [ ] Stakeholder sign-off recorded on KPI register
- [ ] No KPI left as "to be confirmed" before dashboard build starts
Salesforce-Specific Gotchas
The full list with sources is in references/gotchas.md. The ones that most often corrupt a KPI:
| Gotcha | Consequence |
|---|---|
| Case-sensitive join keys (Gotcha 1) | Attainment is blank for groups whose key differs only in case |
| Inner cogroup drops unmatched groups (Gotcha 4) | Ratio KPIs lose the groups with zero numerator and look better than they are |
| Sparse fields and Einstein Discovery (Gotcha 8) | The earlier "70% fill rate" rule is UNVERIFIED; record fill rates and check the story's field analysis |
Output Artifacts
| Artifact | Description |
|---|---|
| KPI register | Signed-off table of metric name, plain-English definition, formula, dimensions, target model, and benchmark |
| Targets dataset schema | Column definitions for the external targets dataset to support attainment tracking |
| SAQL formula sketches | Per-KPI SAQL query patterns for developer reference during lens/recipe build |
Related Skills
admin/analytics-requirements-gathering— upstream skill: gather analytics requirements before defining KPIsadmin/saql-query-development— use to implement SAQL formulas from the KPI registeradmin/marketing-reporting-requirements— companion skill for marketing-specific KPI definition