dbt-labs / dbt-labs/metricflow

[Feature] Conversion metric flag to support same-measure retention

Open
#2,049 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
1.8k
Forks
202
Avg merge
1d 8h
Merged PRs (30d)
14

Description

### Is this your first time submitting a feature request?

- [x] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [x] I have searched the existing issues, and I could not find an existing issue for this feature
- [x] I am requesting a straightforward extension of existing metricflow functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

Add an optional boolean flag (proposed name: `exclude_self_match`, default `false`) to `conversion_type_params`. When set, the join condition that produces successful conversion events is tightened so that a single base event cannot match itself as its own conversion.

Currently `JoinConversionEventsNode` builds the time-window predicate as:

```sql
ON base.entity = conversion.entity
AND base.metric_time <= conversion.metric_time
AND base.metric_time > conversion.metric_time - INTERVAL
```

(see `metricflow/plan_conversion/to_sql_plan/sql_join_builder.py::make_join_conversion_join_description`, and the `<=` in the docs example).

When `exclude_self_match: true`, the rendered join would instead be:

```sql
ON base.entity = conversion.entity
AND base.metric_time < conversion.metric_time -- strict <
AND base.metric_time > conversion.metric_time - INTERVAL
AND base.mf_internal_uuid <> conversion.mf_internal_uuid -- new
```

For the UUID inequality to work when both sides resolve to the same source, the base scan would also need a `GEN_RANDOM_UUID() AS mf_internal_uuid` column. Today only the conversion side is wrapped in `AddGeneratedUuidColumnNode`; the base side would need the equivalent when the flag is set.

#### Example YAML

```yaml
metric:
name: monthly_retention_rate
description: % of users active in a period that are still active within the following month
type: conversion
type_params:
conversion_type_params:
base_measure: active_users
conversion_measure: active_users
entity: user
window: 1 month
calculation: conversion_rate
exclude_self_match: true # new
```

### Describe alternatives you've considered

- **Pre-computing the result in a dbt model and exposing a simple `ratio` metric.** This works today but pushes retention semantics out of the semantic layer and duplicates the conversion-metric pipeline (entity self-join, dedupe, denominator computation) in user-owned SQL.
- **A new `retention` metric type.** Cleaner in isolation, but a much larger surface area for a small step forward — most of the existing conversion-metric machinery (join, UUID dedupe, `FULL OUTER JOIN` combining numerator/denominator, dimensional slicing) already produces the right shape once same-source self-matching is suppressed.
- **A workaround using a derived metric with `offset_window`.** Doesn't model the set-intersection ("active in T *and* active in T+window") that retention requires.

### Who will this benefit?

Anyone building **rolling activity-to-activity retention or stickiness metrics** on top of a single activity stream (typically one row per `user_id`, `activity_date`).

Concrete example: "% of users active in period T who had another activity within the next 1 month." Defined today with `base_measure: active_users` and `conversion_measure: active_users`, the metric is pinned near 100% because every base event satisfies the join against itself (`metric_time <= metric_time`). With the proposed flag, the same metric definition yields the correct forward-looking retention rate, broken out by any time grain and dimensional slice.

Related shapes this unlocks declaratively, reusing the same pattern:

- D1 / D7 / D30 stickiness curves on a single activity measure
- "Returning user rate" per acquisition channel, plan tier, region, product line, etc.
- Combined with `constant_properties`, "retained on the same plan / same product" variants

### Are you interested in contributing this feature?

Possibly — happy to discuss scope with maintainers first.

### Anything else?

Rough implementation sketch (touchpoints in the current codebase):

- `metricflow_semantic_interfaces/protocols/metric.py` — add `exclude_self_match` to the `ConversionTypeParams` protocol
- `metricflow_semantic_interfaces/implementations/metric.py` — add field to `PydanticConversionTypeParams` (default `False`)
- `metricflow_semantic_interfaces/parsing/schemas.py` — surface in the JSON schema
- `metricflow_semantic_interfaces/validations/metrics.py` — validation rule (e.g. warn or noop the flag when `base_measure` and `conversion_measure` clearly resolve to different semantic models, since there is no self to exclude)
- `metricflow/dataflow/nodes/join_conversion_events.py` — thread the flag through the DAG node (and `with_new_parents` / `functionally_identical` / `displayed_properties`)
- `metricflow/dataflow/builder/dataflow_plan_builder.py::_build_aggregated_conversion_node` — forward into `JoinConversionEventsNode.create(...)`; ensure a UUID column is emitted on the base source when the flag is set
- `metricflow/plan_conversion/to_sql_plan/sql_join_builder.py::make_join_conversion_join_description` — emit strict `<` and the UUID inequality conditionally
- Snapshot tests under `tests_metricflow/snapshots/test_conversion_metrics_to_sql.py/` (a new fixture: a conversion metric whose `base_measure` and `conversion_measure` are the same measure, with the flag set, plus a paired snapshot without it for regression coverage)

Public API impact: one new optional field on `conversion_type_params`. Default is `false`, so existing conversion metrics are unaffected.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.