Analytics Kpi Definition

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.

---

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:

1. Create a separate targets dataset with columns matching the KPI's dimension groupings (e.g., Owner, Region, Quarter) plus a Target value measure
2. Join the targets dataset to the actuals dataset at query time using SAQL cogroup or recipe join
3. 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:

1. 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
2. Document target model: fixed value, external targets dataset join, or formula-derived
3. Document benchmark: industry benchmark, historical baseline, or threshold
4. 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:

1. Define the granularity of targets: by Owner, by Region, by Quarter, or some combination
2. Create a targets dataset schema with columns: all grouping dimensions + Target_Amount (or equivalent measure)
3. Define the load schedule: targets are uploaded via CSV or External Data API on the same cadence as reporting periods
4. 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:

1. Collect all KPIs stakeholders mention — even informally named ones. Document every metric and the person requesting it.
2. For each KPI: write a plain-English definition that specifies what counts (included criteria), what excludes (excluded criteria), the time period, and the granularity.
3. 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.
4. 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.
5. Document SAQL formula sketch for each KPI: sum(Measure) groupby [DimensionField] with target join pattern if applicable.
6. Get stakeholder sign-off on the KPI register before any lens or dashboard is built — record who approved each formula and target definition.
7. 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

Non-obvious platform behaviors that cause real production problems:

1. Targets dataset join key must be exact string match — When joining a targets dataset to an actuals dataset in SAQL, the join key field values must be an exact string match including case. A targets dataset with Owner = "John Smith" will not join to an actuals dataset with Owner = "john smith". Null results appear silently — KPIs show actual values with no target column. Document the exact string format for all join keys in the KPI register.
2. Dimensions cannot be aggregated — SAQL error at runtime — Using a dimension field in a SUM(), AVG(), or COUNT(Distinct) aggregation produces a SAQL error at lens runtime. Practitioners who haven't checked the dataset schema assume all numeric-looking fields are measures — but fields configured as dimensions in the dataset editor cannot be aggregated regardless of their data type.
3. Fields below ~70% fill rate are silently dropped from Einstein Discovery — If CRM Analytics KPIs are later used in Einstein Discovery models, fields below approximately 70% fill rate are silently excluded from feature selection. KPI definitions that depend on sparse fields should document the fill rate risk.

---

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 KPIs
• data/saql-query-development — use to implement SAQL formulas from the KPI register
• admin/marketing-reporting-requirements — companion skill for marketing-specific KPI definition

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: PranavNagrecha
• Source: PranavNagrecha/AwesomeSalesforceSkills
• License: Apache-2.0
• Homepage: https://pypi.org/project/sfskills-mcp/

Install and usage instructions live in the source repository linked above.