OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
// The cohort matrix is shared by the dashboard retention chart and the MCP /
// agent / REST retention endpoints.
import { DateTime, round } from '@openpanel/shared';
import { range } from 'ramda';
import type { ServiceDeps, Services } from '../../services';
import { TimeRangeError } from '../../shared/time-buckets';
import type { IChartEventFilter } from '../report/report.constants';
import {
type IRetentionCriteria,
type IRetentionInterval,
type RetentionSeriesQueryInput,
retentionCohortQuery,
retentionLastSeenSeriesQuery,
retentionSeriesQuery,
rollingActiveUsersQuery,
} from './src/retention.sql';
import { runQuery } from './src/run-query';
export type {
IRetentionCriteria,
IRetentionInterval,
} from './src/retention.sql';
/** Days-since-last-seen buckets the engagement summary reports on. */
const ENGAGEMENT_ACTIVE_DAYS = 7;
const ENGAGEMENT_RECENT_DAYS = 14;
const ENGAGEMENT_LAPSING_DAYS = 30;
const ENGAGEMENT_INACTIVE_DAYS = 60;
/** getRetentionCohortCore's fixed window. */
const RETENTION_COHORT_WEEKS = 12;
const ROLLING_ACTIVE_USER_LABELS: Record<number, string> = {
1: 'DAU',
7: 'WAU',
30: 'MAU',
};
const RETENTION_PERCENTAGE_DECIMALS = 2;
const RETENTION_COUNT_DECIMALS = 0;
type IGetWeekRetentionInput = RetentionSeriesQueryInput;
export interface IServiceRetentionRollingActiveUsers {
date: string;
users: number;
}
export interface IRetentionCohortRow {
cohort_interval: string;
sum: number;
values: number[];
percentages: number[];
}
export interface IGetRetentionCohortInput {
projectId: string;
/** Event name(s) defining cohort entry. Empty/undefined => any event. */
firstEvent?: string[];
/** Event name(s) that count as "returned". Empty/undefined => any event. */
secondEvent?: string[];
criteria?: IRetentionCriteria;
interval?: IRetentionInterval;
/** ISO or `yyyy-MM-dd HH:mm:ss`. */
startDate: string;
/** ISO or `yyyy-MM-dd HH:mm:ss`. */
endDate: string;
/**
* Property and/or cohort filters scoping the analysed events. Cohort
* membership (inCohort/notInCohort) keeps the fast cohort_events_mv path;
* any property/column filter falls back to the raw events table.
*/
filters?: IChartEventFilter[];
}
// Week-over-week retention graph: for each week, how many active users were
// also active the following week.
//
// Instead of self-joining the raw events table (O(events²) per profile), the
// statement first collapses events to one row per (profile, week), then
// self-joins that much smaller set to its next week.
export function getRetentionSeries(
deps: ServiceDeps,
input: IGetWeekRetentionInput
) {
return runQuery<{
date: string;
active_users: number;
retained_users: number;
retention: number;
}>(deps, retentionSeriesQuery(input));
}
// https://medium.com/@andre_bodro/how-to-fast-calculating-mau-in-clickhouse-fd793559b229
export function getRollingActiveUsers(
deps: ServiceDeps,
{ projectId, days }: { projectId: string; days: number }
) {
return runQuery<IServiceRetentionRollingActiveUsers>(
deps,
rollingActiveUsersQuery(projectId, days)
);
}
export function getRetentionLastSeenSeries(
deps: ServiceDeps,
input: IGetWeekRetentionInput
) {
return runQuery<{
days: number;
users: number;
}>(deps, retentionLastSeenSeriesQuery(input));
}
export async function getRollingActiveUsersCore(
deps: ServiceDeps,
input: {
projectId: string;
days: number;
}
) {
const data = await getRollingActiveUsers(deps, input);
return {
window_days: input.days,
label: ROLLING_ACTIVE_USER_LABELS[input.days] ?? `${input.days}d active`,
series: data,
};
}
export async function getWeeklyRetentionSeriesCore(
deps: ServiceDeps,
input: IGetWeekRetentionInput
) {
return getRetentionSeries(deps, input);
}
// Weekly active-user retention cohort over the last 12 weeks, computed by the
// unified getRetentionCohort engine. firstEvent/secondEvent are omitted so any
// identified activity counts toward the cohort.
export async function getRetentionCohortCore(
deps: ServiceDeps,
projectId: string
) {
const end = DateTime.now();
const start = end.minus({ weeks: RETENTION_COHORT_WEEKS });
return getRetentionCohort(deps, {
projectId,
interval: 'week',
startDate: start.toFormat('yyyy-MM-dd HH:mm:ss'),
endDate: end.toFormat('yyyy-MM-dd HH:mm:ss'),
});
}
export async function getEngagementCore(
deps: ServiceDeps,
input: IGetWeekRetentionInput
) {
const raw = await getRetentionLastSeenSeries(deps, input);
let active_0_7 = 0;
let active_8_14 = 0;
let active_15_30 = 0;
let active_31_60 = 0;
let churned_60_plus = 0;
for (const row of raw) {
if (row.days <= ENGAGEMENT_ACTIVE_DAYS) {
active_0_7 += row.users;
} else if (row.days <= ENGAGEMENT_RECENT_DAYS) {
active_8_14 += row.users;
} else if (row.days <= ENGAGEMENT_LAPSING_DAYS) {
active_15_30 += row.users;
} else if (row.days <= ENGAGEMENT_INACTIVE_DAYS) {
active_31_60 += row.users;
} else {
churned_60_plus += row.users;
}
}
const total =
active_0_7 + active_8_14 + active_15_30 + active_31_60 + churned_60_plus;
return {
summary: {
total_identified_users: total,
active_last_7_days: active_0_7,
active_8_to_14_days: active_8_14,
active_15_to_30_days: active_15_30,
inactive_31_to_60_days: active_31_60,
churned_60_plus_days: churned_60_plus,
},
distribution: raw,
};
}
// Cohort retention matrix: every user is assigned to exactly ONE cohort, the
// interval of their FIRST `firstEvent` within the window. For each cohort we
// count how many of those users performed `secondEvent` 0..N intervals later.
// - criteria 'on' -> active exactly k intervals after (=)
// - criteria 'on_or_after' -> active at least k intervals after (>=, cumulative)
// An empty `firstEvent` / `secondEvent` drops the name filter, measuring
// active-user retention.
const LUXON_UNIT: Record<IRetentionInterval, 'days' | 'weeks' | 'months'> = {
minute: 'days',
hour: 'days',
day: 'days',
week: 'weeks',
month: 'months',
};
// Each period is two columns of `groupUniqArrayIf` over every cohort member,
// so the matrix width is the statement's cost. A year by the day is 366.
const MAX_RETENTION_PERIODS = 1000;
const CLICKHOUSE_DATE_TIME_LENGTH = 'yyyy-MM-dd HH:mm:ss'.length;
const ISO_DATE_LENGTH = 'yyyy-MM-dd'.length;
// Normalize an ISO or `yyyy-MM-dd HH:mm:ss` string into ClickHouse date-time form.
function utc(date: string) {
return date.replace('T', ' ').slice(0, CLICKHOUSE_DATE_TIME_LENGTH);
}
// Number of `interval` buckets spanned by [startDate, endDate]; drives the
// number of retention columns (0..diffInterval).
function diffIntervalCount(
startDate: string,
endDate: string,
interval: IRetentionInterval
) {
const unit = LUXON_UNIT[interval];
const start = DateTime.fromFormat(utc(startDate), 'yyyy-MM-dd HH:mm:ss', {
zone: 'utc',
});
const end = DateTime.fromFormat(utc(endDate), 'yyyy-MM-dd HH:mm:ss', {
zone: 'utc',
});
return Math.max(0, Math.floor(end.diff(start, unit).as(unit)));
}
export async function getRetentionCohort(
deps: ServiceDeps,
input: IGetRetentionCohortInput
) {
const {
projectId,
firstEvent,
secondEvent,
criteria = 'on_or_after',
interval = 'day',
startDate,
endDate,
filters = [],
} = input;
const diffInterval = diffIntervalCount(startDate, endDate, interval);
if (diffInterval > MAX_RETENTION_PERIODS) {
throw new TimeRangeError(
`Retention covers at most ${MAX_RETENTION_PERIODS} ${interval} periods and the selected range spans ${diffInterval}. Pick a coarser interval or a shorter range.`
);
}
const cohortData = await runQuery<{
cohort_interval: string;
total_first_event_count: number;
[key: string]: number | string;
}>(
deps,
retentionCohortQuery({
projectId,
firstEvent,
secondEvent,
criteria,
interval,
start: utc(startDate),
end: utc(endDate),
diffInterval,
filters,
})
);
// Reference point for cohort maturity: we only have return data up to "now",
// so periods that haven't elapsed yet are excluded from the weighted average.
const until = DateTime.utc().toFormat('yyyy-MM-dd HH:mm:ss');
return processCohortData(cohortData, diffInterval, interval, until);
}
// Number of fully-elapsed periods between a cohort's start and the reference
// date. Periods beyond this haven't happened yet, so they carry no return data
// and must be excluded from the average. Fails open (treats everything as
// mature) when dates are missing/unparseable.
function maturePeriodCount(
cohortInterval: string,
until: string | undefined,
interval: IRetentionInterval
): number {
if (!until) {
return Number.POSITIVE_INFINITY;
}
const unit = LUXON_UNIT[interval];
const cohort = DateTime.fromFormat(
cohortInterval.slice(0, ISO_DATE_LENGTH),
'yyyy-MM-dd',
{
zone: 'utc',
}
);
const ref = DateTime.fromFormat(until, 'yyyy-MM-dd HH:mm:ss', {
zone: 'utc',
});
if (!(cohort.isValid && ref.isValid)) {
return Number.POSITIVE_INFINITY;
}
return Math.floor(ref.diff(cohort, unit).as(unit));
}
// Shapes the raw ClickHouse matrix into per-cohort rows + a leading weighted-
// average row.
//
// The average is a maturity-aware pooled rate: for each period column it pools
// only the cohorts old enough to have actually reached that period
// (cohort_interval + N intervals <= `until`). This keeps not-yet-elapsed cells
// from being read as churn AND keeps genuine zeros in, so the late curve is not
// inflated. Counts are normalised to the representative (mean) cohort size, so
// the row is internally consistent: period 0 equals Total profiles and the
// curve starts at 100%.
export function processCohortData(
data: Array<{
cohort_interval: string;
total_first_event_count: number;
[key: string]: number | string;
}>,
diffInterval: number,
interval: IRetentionInterval = 'day',
until?: string
): IRetentionCohortRow[] {
if (data.length === 0) {
return [];
}
const columns = range(0, diffInterval + 1);
const processed = data.map((row) => {
const sum = row.total_first_event_count;
const values = columns.map(
(index) => (row[`interval_${index}_user_count`] || 0) as number
);
return {
cohort_interval: row.cohort_interval,
sum,
values,
percentages: values.map((value) =>
sum > 0 ? round(value / sum, RETENTION_PERCENTAGE_DECIMALS) : 0
),
};
});
const maturePeriods = processed.map((row) =>
maturePeriodCount(row.cohort_interval, until, interval)
);
const totalSize = processed.reduce((acc, row) => acc + row.sum, 0);
const representativeSize = round(
totalSize / processed.length,
RETENTION_COUNT_DECIMALS
);
const averageRow: IRetentionCohortRow = {
cohort_interval: 'Weighted Average',
sum: representativeSize,
values: [],
percentages: [],
};
for (const index of columns) {
let matureSize = 0;
let retained = 0;
processed.forEach((row, i) => {
if (index <= (maturePeriods[i] as number)) {
matureSize += row.sum;
retained += row.values[index] as number;
}
});
const rate = matureSize > 0 ? retained / matureSize : 0;
averageRow.percentages.push(round(rate, RETENTION_PERCENTAGE_DECIMALS));
averageRow.values.push(
round(rate * representativeSize, RETENTION_COUNT_DECIMALS)
);
}
return [averageRow, ...processed];
}
/** See funnel.service.ts's `createFunnelService` for why each chart
* Sub-module carries its own factory. */
export function createRetentionService(
deps: ServiceDeps,
_services: () => Services
) {
return {
processCohortData,
getRetentionCohort: (
input: Parameters<typeof getRetentionCohort>[1]
): ReturnType<typeof getRetentionCohort> => getRetentionCohort(deps, input),
getRetentionCohortCore: (
projectId: string
): ReturnType<typeof getRetentionCohortCore> =>
getRetentionCohortCore(deps, projectId),
getRetentionSeries: (
input: Parameters<typeof getRetentionSeries>[1]
): ReturnType<typeof getRetentionSeries> => getRetentionSeries(deps, input),
getRetentionLastSeenSeries: (
input: Parameters<typeof getRetentionLastSeenSeries>[1]
): ReturnType<typeof getRetentionLastSeenSeries> =>
getRetentionLastSeenSeries(deps, input),
getRollingActiveUsers: (
input: Parameters<typeof getRollingActiveUsers>[1]
): ReturnType<typeof getRollingActiveUsers> =>
getRollingActiveUsers(deps, input),
getRollingActiveUsersCore: (
input: Parameters<typeof getRollingActiveUsersCore>[1]
): ReturnType<typeof getRollingActiveUsersCore> =>
getRollingActiveUsersCore(deps, input),
getWeeklyRetentionSeriesCore: (
input: IGetWeekRetentionInput
): ReturnType<typeof getWeeklyRetentionSeriesCore> =>
getWeeklyRetentionSeriesCore(deps, input),
getEngagementCore: (
input: IGetWeekRetentionInput
): ReturnType<typeof getEngagementCore> => getEngagementCore(deps, input),
};
}