Many business metrics are really about state changes: how many people became leads, how many contracts enrolled, how many customers churned. These are not event counts — they come from the entity state timeline.

Nexus has two state tables:

Table	Shape	One row per…
nexus_states	EAV	State transition (entity entered a specific state at a specific time)
nexus_entity_states	SCD2 (wide)	Timestamp where any state changed, with all state values pivoted into columns

Use nexus_states for state transition metrics. Each row already represents a single transition — no window functions needed.

---

Why nexus_states over nexus_entity_states

The SCD table (nexus_entity_states) is pivoted: one row per timestamp where any state changed, with columns like customer_lifecycle, business_unit, etc. To detect whether a specific state changed on a given row, you need a LAG window:

-- SCD approach: requires subquery + LAG
SELECT COUNT(DISTINCT s.entity_id) AS new_leads
FROM (
  SELECT
    entity_id,
    customer_lifecycle,
    valid_from,
    LAG(customer_lifecycle) OVER (
      PARTITION BY entity_id ORDER BY valid_from
    ) AS prev_lifecycle
  FROM nexus_entity_states
  WHERE entity_type = 'person'
) s
WHERE s.customer_lifecycle = 'lead'
  AND (s.prev_lifecycle IS NULL OR s.prev_lifecycle != 'lead')
  AND s.valid_from >= '2025-01-01'
  AND s.valid_from < '2026-01-01'

The EAV table (nexus_states) makes this trivial — if a row exists with state_value = 'lead', that entity entered the lead state at state_entered_at:

-- EAV approach: simple filtered count
SELECT COUNT(DISTINCT entity_id) AS new_leads
FROM nexus_states
WHERE state_name = 'customer_lifecycle'
  AND state_value = 'lead'
  AND entity_type = 'person'
  AND state_entered_at >= '2025-01-01'
  AND state_entered_at < '2026-01-01'

Both return the same result. The EAV version is simpler, faster, and fits cleanly into the metric definition schema.

---

The Pattern

Every state transition metric follows the same shape:

nexus_states:
  - name: <metric_name>
    label: <Human Label>
    type: count
    tables: [nexus_states]
    metric_sql: DISTINCT nexus_states.entity_id
    filter:
      - nexus_states.state_name = '<state_dimension>'
      - nexus_states.state_value = '<target_value>'
      - nexus_states.entity_type = '<entity_type>'
    format: integer
    polarity: positive
    tags: [...]
    description: >
      ...

The only things that change between metrics are:

• state_name — which state dimension (customer_lifecycle, subscription_status, etc.)
• state_value — which value the entity transitioned into (lead, enrolled, churned, etc.)
• entity_type — usually person, but could be subscription, account, etc.

Date column

The time axis for state transition metrics is state_entered_at — when the entity entered the state. This is distinct from occurred_at on event tables. Consumers filter or bucket by state_entered_at for time-series analysis.

Re-entries

A person can enter the same state multiple times (e.g. become a lead, churn, become a lead again). Each entry is a separate row in nexus_states. Using COUNT(DISTINCT entity_id) counts unique people who entered the state during the period, regardless of how many times. Use COUNT(*) if you want total transitions including re-entries.

Scoping by business unit or other entity attributes

State transition metrics should not bake in business unit or other entity attributes as filters. The nexus_states table may carry entity-level attributes (depending on the state model), but consumers should apply those filters at query time — the same principle as event metrics where business_unit is a dimension, not part of the metric definition.

---

Example: New Leads

nexus_states:
  - name: new_leads
    label: New Leads
    aliases: [lead entries, new lead count]
    type: count
    tables: [nexus_states]
    metric_sql: DISTINCT nexus_states.entity_id
    filter:
      - nexus_states.state_name = 'customer_lifecycle'
      - nexus_states.state_value = 'lead'
      - nexus_states.entity_type = 'person'
    format: integer
    polarity: positive
    tags: [acquisition, lifecycle]
    description: >
      How many people became leads during a period. A person counts here
      when they first enter the "lead" stage of the customer lifecycle—
      meaning they did something that qualified them beyond anonymous, like
      submitting a form or having a meaningful phone call. The same person
      can re-enter lead if they leave and come back.
    example_questions:
      - How many new leads did we generate last month?
      - Are we generating more leads than last quarter?
      - How do new leads break down by business unit?

An LLM or analyst reads this definition and writes:

SELECT
  DATE_TRUNC('month', state_entered_at) AS month,
  COUNT(DISTINCT entity_id) AS new_leads
FROM nexus_states
WHERE state_name = 'customer_lifecycle'
  AND state_value = 'lead'
  AND entity_type = 'person'
  AND state_entered_at >= '2025-01-01'
  AND state_entered_at < '2026-01-01'
GROUP BY 1
ORDER BY 1

Other lifecycle metrics follow the same pattern

Enrollments, churned customers, paying customers — same structure, different state_value:

- name: new_enrollments
  # ... state_value = 'enrolled'

- name: new_churned
  # ... state_value = 'churned'

- name: new_paying
  # ... state_value = 'paying'

---

Relationship to Event-Based Counts

Event-based counts (e.g. lead_submitted events, enrollment events) and state transition counts measure related but different things:

• Event count: how many times something happened (a form was submitted, an enrollment was processed)
• State transition count: how many entities changed state (became a lead, became enrolled)

These can diverge. Multiple events might lead to a single state change, or a state change might happen without a directly corresponding event (e.g. a time-based transition). The state model is the canonical source for "how many entities are in or entered a given state."