Onboarding Milestone Completion Rate

Computable definition

The canonical formula in the GASP formula grammar. The MCP server compiles it to SQL over the datasets listed below.

all_time(count(field.onboarding_id where in_period(field.milestone_due_date) and field.milestone_date is not null and field.milestone_date <= period_end)) / all_time(count(field.onboarding_id where in_period(field.milestone_due_date)))

Unit: percent (the formula yields an unscaled ratio)

Notes: Milestones reached over milestones due: onboarding milestones due in the period that were reached by period end, over all milestones due in the period. Each row of onboarding_milestone is one planned milestone, so planned rows carry milestone_due_date before milestone_date is set; all_time keeps unreached rows that the milestone_date period filter would drop. Add field.onboarding_milestone = '<milestone>' to both sides for a single milestone.

Data requirements

Grain: Per event · Frequency: Per milestone · Aggregation: event · Staged: no · Main source: CS Platform.

SELECT (CAST((SELECT COUNT(onboarding_id) FROM onboarding_milestone WHERE (((milestone_due_date >= :period_start AND milestone_due_date <= :period_end) AND milestone_date IS NOT NULL) AND milestone_date <= :period_end)) AS DOUBLE PRECISION) / NULLIF((SELECT COUNT(onboarding_id) FROM onboarding_milestone WHERE (milestone_due_date >= :period_start AND milestone_due_date <= :period_end)), 0)) AS onboarding_milestone_completion_rate