OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
import { sql } from '@openpanel/db/src/clickhouse/sql';
import { omit } from 'ramda';
import type { ServiceDeps, Services } from '../../services';
import { expandCustomEvents } from '../custom-event/custom-event.service';
import type { IReportInput } from '../report/report.constants';
import { NOT_SET_VALUE } from '../report/report.constants';
import { mergeGlobalFilters, onlyReportEvents } from '../report/src/series';
import { JOINABLE_PROFILE_COLUMNS } from './chart.constants';
import { compiledText } from './src/compiled';
import { type ConversionGroup, conversionQuery } from './src/conversion.sql';
import { eventMatchCondition, eventNamesOf } from './src/event-match';
import {
buildInlineCohortJoin,
collectBreakdownCohortIds,
extractCohortId,
getSelectPropertyKey,
isKnownEventField,
} from './src/field-resolution';
import {
getEventFiltersWhereClause,
joinFilterClauses,
} from './src/filter-where';
import { runQuery } from './src/run-query';
import { fetchCohortsMetadata } from './src/statement';
/** Default funnel window, in hours, when the report does not set one. */
const DEFAULT_FUNNEL_WINDOW_HOURS = 24;
const SECONDS_PER_HOUR = 3600;
const CONVERSION_STEP_COUNT = 2;
interface ConversionRow {
event_day: string;
total_first: number;
conversions: number;
conversion_rate_percentage: number;
[key: string]: string | number;
}
interface ConversionSeriePoint {
date: string;
total: number;
conversions: number;
rate: number;
}
interface ConversionSerie {
id: string;
breakdowns: string[];
data: ConversionSeriePoint[];
}
export type ConversionChartInput = Omit<
IReportInput,
'range' | 'previous' | 'metric' | 'chartType'
> & { timezone: string };
/**
* The profile columns the LEFT ANY JOIN must expose for the requested
* breakdowns. Empty means no join.
*/
function profileJoinFields(profileBreakdowns: { name: string }[]): string[] {
if (profileBreakdowns.length === 0) {
return [];
}
const fields = new Set<string>(['id']);
for (const breakdown of profileBreakdowns) {
const fieldName = breakdown.name.replace('profile.', '').split('.')[0];
if (fieldName === 'properties') {
fields.add('properties');
} else if (fieldName && JOINABLE_PROFILE_COLUMNS.includes(fieldName)) {
fields.add(fieldName);
}
}
return Array.from(fields);
}
export async function getConversion(
deps: ServiceDeps,
{
projectId,
startDate,
endDate,
options,
series,
globalFilters,
breakdowns = [],
limit,
interval,
timezone,
}: ConversionChartInput
) {
const mergedSeries = mergeGlobalFilters(
await expandCustomEvents(deps, projectId, series),
globalFilters
);
const funnelOptions = options?.type === 'funnel' ? options : undefined;
const funnelGroup = funnelOptions?.funnelGroup;
const funnelWindow =
funnelOptions?.funnelWindow ?? DEFAULT_FUNNEL_WINDOW_HOURS;
const group: ConversionGroup =
funnelGroup === 'profile_id' ? 'profile_id' : 'session_id';
// Same guard as the funnel service / getChartSql — drop breakdowns whose
// name can't be resolved against the events schema.
const knownBreakdowns = breakdowns.filter((breakdown) =>
isKnownEventField(breakdown.name)
);
const cohortIds = collectBreakdownCohortIds(knownBreakdowns);
const cohortMetadata = await fetchCohortsMetadata(deps, projectId, cohortIds);
const cohortJoins = cohortIds.map((id) =>
buildInlineCohortJoin(id, projectId, 'events')
);
const breakdownExpressions = knownBreakdowns.map((breakdown) => {
const cohortId = extractCohortId(breakdown.name);
const cohortName = cohortId
? cohortMetadata.get(cohortId)?.name
: undefined;
return getSelectPropertyKey(
breakdown.name,
projectId,
cohortId ?? undefined,
cohortName
);
});
const breakdownSelects = breakdownExpressions.map(
(expression, index) => sql`${expression} as ${compiledText(`b_${index}`)}`
);
const events = onlyReportEvents(mergedSeries);
const anyBreakdownOnGroup = knownBreakdowns.some((breakdown) =>
breakdown.name.startsWith('group.')
);
const anyFilterOnGroup = events.some((event) =>
event.filters?.some((filter) => filter.name.startsWith('group.'))
);
if (events.length !== CONVERSION_STEP_COUNT) {
throw new Error('events must be an array of two events');
}
if (!(startDate && endDate)) {
throw new Error('startDate and endDate are required');
}
const eventA = events[0] as (typeof events)[number];
const eventB = events[1] as (typeof events)[number];
// Qualify with 'events' so event-level `properties[...]` becomes
// `events.properties[...]` — required when the conversion query also joins
// the profiles table (which exposes a `properties` column). Without the
// qualifier ClickHouse fails with "ambiguous identifier 'properties'"
// whenever a step filters on properties.X while a breakdown is on
// profile.properties.Y.
const whereA = joinFilterClauses(
getEventFiltersWhereClause(eventA.filters, projectId, 'events')
);
const whereB = joinFilterClauses(
getEventFiltersWhereClause(eventB.filters, projectId, 'events')
);
const results = await runQuery<ConversionRow>(
deps,
conversionQuery({
projectId,
startDate,
endDate,
interval,
group,
funnelWindowSeconds: funnelWindow * SECONDS_PER_HOUR,
firstEventMatch: eventMatchCondition(eventA, projectId, 'events'),
secondEventMatch: eventMatchCondition(eventB, projectId, 'events'),
eventNames: [...eventNamesOf(eventA), ...eventNamesOf(eventB)],
firstEventWhere: whereA,
secondEventWhere: whereB,
breakdownSelects,
breakdownExpressions,
profileJoinFields: profileJoinFields(
knownBreakdowns.filter((breakdown) =>
breakdown.name.startsWith('profile.')
)
),
needsGroupArrayJoin: anyBreakdownOnGroup || anyFilterOnGroup,
cohortJoins,
}),
timezone
);
return toSeries(results, knownBreakdowns, limit).map((serie, serieIndex) => ({
...serie,
data: serie.data.map((point, index) => ({
...point,
timestamp: new Date(point.date).getTime(),
serieIndex,
index,
serie: omit(['data'], serie),
})),
}));
}
function toPoint(row: ConversionRow): ConversionSeriePoint {
return {
date: row.event_day,
total: row.total_first,
conversions: row.conversions,
rate: row.conversion_rate_percentage,
};
}
function toSeries(
data: ConversionRow[],
breakdowns: { name: string }[] = [],
limit: number | undefined = undefined
) {
if (breakdowns.length === 0) {
return [
{
id: 'conversion',
breakdowns: [],
data: data.map(toPoint),
},
];
}
const series = data.reduce(
(acc, row) => {
if (limit && Object.keys(acc).length >= limit) {
return acc;
}
const key =
breakdowns.map((_, index) => row[`b_${index}`]).join('|') ||
NOT_SET_VALUE;
if (!acc[key]) {
acc[key] = {
id: key,
breakdowns: breakdowns.map(
(_, index) => (row[`b_${index}`] || NOT_SET_VALUE) as string
),
data: [],
};
}
(acc[key] as ConversionSerie).data.push(toPoint(row));
return acc;
},
{} as Record<string, ConversionSerie>
);
return Object.values(series).map((serie, serieIndex) => ({
...serie,
data: serie.data.map((item, dataIndex) => ({
...item,
dataIndex,
serieIndex,
})),
}));
}
/** See funnel.service.ts's `createFunnelService` for why each chart
* Sub-module carries its own factory. */
export function createConversionService(
deps: ServiceDeps,
_services: () => Services
) {
return {
getConversion: (
input: Parameters<typeof getConversion>[1]
): ReturnType<typeof getConversion> => getConversion(deps, input),
};
}