Skip to content

OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.

12.7 KB
Raw Blame
import { ifNaN } from '@openpanel/shared';
import { last, reverse } from 'ramda';
import type { ServiceDeps, Services } from '../../services';
import { toRangeBoundaryLiteral } from '../../shared/ch-dates';
import { expandCustomEvents } from '../custom-event/custom-event.service';
import { getSettingsForProject } from '../organization/organization.service';
import type {
  IChartBreakdown,
  IChartEvent,
  IReportInput,
} from '../report/report.constants';
import { mergeGlobalFilters, onlyReportEvents } from '../report/src/series';
import { collectBreakdownCohortIds } from './src/field-resolution';
import {
  EMPTY_BREAKDOWN_LABEL,
  type FunnelBase,
  type FunnelGroup,
  type FunnelProfilesInput,
  funnelBase,
  funnelChartQuery,
  funnelProfilesQuery,
  funnelSessionsQuery,
  knownFunnelBreakdowns,
} from './src/funnel.sql';
import { runQuery } from './src/run-query';
import { fetchCohortsMetadata } from './src/statement';

export {
  EMPTY_BREAKDOWN_LABEL,
  type FunnelBase,
  type FunnelProfilesInput,
  funnelProfilesQuery,
} from './src/funnel.sql';

/** Default funnel window, in hours, when the report does not set one. */
const DEFAULT_FUNNEL_WINDOW_HOURS = 24;
const MILLISECONDS_PER_HOUR = 3600 * 1000;
const PERCENT = 100;
const ROUND_TO_TWO_DECIMALS = 100;
const ROUND_RATE_TO_TWO_DECIMALS = 10_000;

export interface FunnelStep {
  event: IChartEvent & { displayName: string };
  count: number;
  percent: number;
  dropoffCount: number | null;
  dropoffPercent: number | null;
  previousCount: number;
  nextCount: number | null;
  isHighestDropoff: boolean;
}

interface FunnelRow {
  level: number;
  count: number;
  [key: string]: string | number;
}

interface FunnelSerieRow {
  id: string;
  breakdowns: string[];
  level: number;
  count: number;
}

function normalizeBreakdownValue(value: unknown): string {
  if (value === null || value === undefined || value === '') {
    return EMPTY_BREAKDOWN_LABEL;
  }
  const text = String(value).trim();
  return text === '' ? EMPTY_BREAKDOWN_LABEL : text;
}

/**
 * Returns the grouping strategy for the funnel: whether windowFunnel is
 * computed per session_id or per profile_id. profile_id correctly handles
 * cross-session funnel completions.
 */
export function getFunnelGroup(group?: string): FunnelGroup {
  return group === 'profile_id' ? 'profile_id' : 'session_id';
}

export interface BuildFunnelBaseInput {
  projectId: string;
  startDate: string;
  endDate: string;
  series: IReportInput['series'];
  globalFilters?: IReportInput['globalFilters'];
  breakdowns?: IChartBreakdown[];
  funnelWindow?: number;
  funnelGroup?: string;
  timezone: string;
}

/**
 * Everything the funnel chart and the funnel profile list share: the
 * normalized event series and breakdowns, and the `session_funnel` / `funnel`
 * CTEs with all of their joins wired up. Callers add their own projection.
 */
export async function buildFunnelBase(
  deps: ServiceDeps,
  {
    projectId,
    startDate,
    endDate,
    series,
    globalFilters,
    breakdowns: initialBreakdowns = [],
    funnelWindow = DEFAULT_FUNNEL_WINDOW_HOURS,
    funnelGroup,
    timezone,
  }: BuildFunnelBaseInput
): Promise<FunnelBase> {
  const breakdowns = knownFunnelBreakdowns(initialBreakdowns);
  const eventSeries = onlyReportEvents(
    mergeGlobalFilters(
      await expandCustomEvents(deps, projectId, series),
      globalFilters
    )
  );
  const cohortMetadata = await fetchCohortsMetadata(
    deps,
    projectId,
    collectBreakdownCohortIds(breakdowns)
  );

  return funnelBase({
    projectId,
    startDate,
    endDate,
    eventSeries,
    breakdowns,
    funnelWindowMilliseconds: funnelWindow * MILLISECONDS_PER_HOUR,
    group: getFunnelGroup(funnelGroup),
    funnelGroup,
    cohortMetadata,
    timezone,
  });
}

export function buildSessionsCte(input: {
  projectId: string;
  startDate: string;
  endDate: string;
}) {
  return funnelSessionsQuery(input);
}

function fillFunnel(funnel: { level: number; count: number }[], steps: number) {
  const filled = Array.from({ length: steps }, (_, index) => {
    const level = index + 1;
    const matchingResult = funnel.find((result) => result.level === level);
    return {
      level,
      count: matchingResult ? matchingResult.count : 0,
    };
  });

  // Accumulate counts from the bottom of the funnel upwards.
  for (let index = filled.length - 1; index >= 0; index--) {
    const step = filled[index];
    const nextStep = filled[index + 1];
    if (step && nextStep) {
      step.count += nextStep.count;
    }
  }
  return filled.reverse();
}

export function toSeries(
  funnel: FunnelRow[],
  breakdowns: { name: string }[] = [],
  limit: number | undefined = undefined
): FunnelSerieRow[][] {
  if (breakdowns.length === 0) {
    return [
      funnel.map((row) => ({
        level: row.level,
        count: row.count,
        id: 'none',
        breakdowns: [],
      })),
    ];
  }

  // Group by breakdown values (normalize empty/null to "Not set").
  const series = funnel.reduce(
    (acc, row) => {
      const key = breakdowns
        .map((_, index) => normalizeBreakdownValue(row[`b_${index}`]))
        .join('|');
      if (!acc[key]) {
        // The limit caps how many breakdown series come back, so it must only
        // reject NEW keys. Bailing out of the whole reduce dropped the
        // remaining rows of series already accepted: the query is ordered by
        // level DESC, so those rows are the lower funnel steps, and losing
        // them leaves each series holding only its deepest level. fillFunnel
        // then accumulates that one row into every step, so every step
        // reports an identical count at 100%.
        if (limit && Object.keys(acc).length >= limit) {
          return acc;
        }
        acc[key] = [];
      }
      (acc[key] as FunnelSerieRow[]).push({
        id: key,
        breakdowns: breakdowns.map((_, index) =>
          normalizeBreakdownValue(row[`b_${index}`])
        ),
        level: row.level,
        count: row.count,
      });
      return acc;
    },
    {} as Record<string, FunnelSerieRow[]>
  );

  return Object.values(series);
}

function isHighestDropoff(
  step: { dropoffCount: number | null },
  index: number,
  list: { dropoffCount: number | null }[]
): boolean {
  if (!step?.dropoffCount) {
    return false;
  }
  const maxDropoff = Math.max(
    ...list.map((item) => item.dropoffCount || 0).filter((count) => count > 0)
  );
  return (
    step.dropoffCount === maxDropoff &&
    list.findIndex((item) => item.dropoffCount === maxDropoff) === index
  );
}

function toFunnelSteps(
  data: FunnelSerieRow[],
  eventSeries: IChartEvent[]
): { steps: FunnelStep[]; totalSessions: number } {
  const filledFunnel = fillFunnel(
    data.map((row) => ({ level: row.level, count: row.count })),
    eventSeries.length
  );
  const totalSessions = last(filledFunnel)?.count ?? 0;

  const steps = reverse(filledFunnel)
    .reduce(
      (acc, item, index, list) => {
        const previous = list[index - 1] ?? { count: totalSessions };
        const next = list[index + 1];
        const event = eventSeries[item.level - 1] as IChartEvent;
        acc.push({
          event: {
            ...event,
            displayName: event.displayName || event.name,
          },
          count: item.count,
          percent: (item.count / totalSessions) * PERCENT,
          dropoffCount: next ? item.count - next.count : null,
          dropoffPercent: next
            ? ((item.count - next.count) / item.count) * PERCENT
            : null,
          previousCount: previous.count,
          nextCount: next?.count ?? null,
        });
        return acc;
      },
      [] as Omit<FunnelStep, 'isHighestDropoff'>[]
    )
    .map((step, index, list) => ({
      ...step,
      percent: ifNaN(step.percent, 0),
      dropoffPercent: ifNaN(step.dropoffPercent, 0),
      isHighestDropoff: isHighestDropoff(step, index, list),
    }));

  return { steps, totalSessions };
}

export async function getFunnel(
  deps: ServiceDeps,
  {
    projectId,
    startDate,
    endDate,
    series,
    globalFilters,
    options,
    breakdowns: initialBreakdowns = [],
    limit,
    timezone = 'UTC',
  }: IReportInput & { timezone: string; events?: IChartEvent[] }
) {
  if (!(startDate && endDate)) {
    throw new Error('startDate and endDate are required');
  }

  const funnelOptions = options?.type === 'funnel' ? options : undefined;
  const base = await buildFunnelBase(deps, {
    projectId,
    startDate,
    endDate,
    series,
    globalFilters,
    breakdowns: initialBreakdowns,
    funnelWindow: funnelOptions?.funnelWindow,
    funnelGroup: funnelOptions?.funnelGroup,
    timezone,
  });

  const funnelData = await runQuery<FunnelRow>(
    deps,
    funnelChartQuery(base),
    base.timezone
  );

  return toSeries(funnelData, base.breakdowns, limit)
    .map((data) => {
      const { steps, totalSessions } = toFunnelSteps(data, base.eventSeries);
      return {
        id: data[0]?.id ?? 'none',
        breakdowns: data[0]?.breakdowns ?? [],
        steps,
        totalSessions,
        lastStep: last(steps) as FunnelStep,
        mostDropoffsStep: steps.find(
          (step) => step.isHighestDropoff
        ) as FunnelStep,
      };
    })
    .sort((a, b) => {
      const aTotal = a.steps.reduce((acc, step) => acc + step.count, 0);
      const bTotal = b.steps.reduce((acc, step) => acc + step.count, 0);
      return bTotal - aTotal;
    });
}

export async function getFunnelCore(
  deps: ServiceDeps,
  input: {
    projectId: string;
    startDate: string;
    endDate: string;
    steps: string[];
    windowHours?: number;
    groupBy?: FunnelGroup;
  }
) {
  const { timezone } = await getSettingsForProject(deps, input.projectId);
  const eventSeries = input.steps.map((name, index) => ({
    id: String(index + 1),
    type: 'event' as const,
    name,
    displayName: name,
    segment: 'user' as const,
    filters: [],
  }));

  const result = await getFunnel(deps, {
    projectId: input.projectId,
    startDate: toRangeBoundaryLiteral(input.startDate, 'start'),
    endDate: toRangeBoundaryLiteral(input.endDate, 'end'),
    series: eventSeries,
    breakdowns: [],
    chartType: 'funnel',
    interval: 'day',
    range: 'custom',
    previous: false,
    metric: 'sum',
    options: {
      type: 'funnel',
      funnelWindow: input.windowHours ?? DEFAULT_FUNNEL_WINDOW_HOURS,
      funnelGroup: input.groupBy ?? 'session_id',
    },
    timezone,
  });

  const primarySeries = result[0];
  if (!primarySeries) {
    return {
      steps: [],
      totalUsers: 0,
      completedUsers: 0,
      overallConversionRate: 0,
    };
  }

  const steps = primarySeries.steps.map((step, index) => ({
    step: index + 1,
    eventName: step.event.displayName || step.event.name,
    users: step.count,
    conversionRateFromStart:
      Math.round(step.percent * ROUND_TO_TWO_DECIMALS) / ROUND_TO_TWO_DECIMALS,
    dropoffPercent:
      step.dropoffPercent === null
        ? null
        : Math.round(step.dropoffPercent * ROUND_TO_TWO_DECIMALS) /
          ROUND_TO_TWO_DECIMALS,
    isHighestDropoff: step.isHighestDropoff,
  }));

  const totalUsers = steps[0]?.users ?? 0;
  const completedUsers = steps.at(-1)?.users ?? 0;

  return {
    steps,
    totalUsers,
    completedUsers,
    overallConversionRate:
      totalUsers > 0
        ? Math.round(
            (completedUsers / totalUsers) * ROUND_RATE_TO_TWO_DECIMALS
          ) / ROUND_TO_TWO_DECIMALS
        : 0,
  };
}

export async function getFunnelProfileIds(
  deps: ServiceDeps,
  input: BuildFunnelBaseInput &
    Omit<FunnelProfilesInput, 'breakdownValues'> & {
      breakdownValues: (string | undefined)[];
    }
): Promise<string[]> {
  const base = await buildFunnelBase(deps, input);
  const rows = await runQuery<{ profile_id: string }>(
    deps,
    funnelProfilesQuery(base, {
      targetLevel: input.targetLevel,
      showDropoffs: input.showDropoffs,
      breakdownValues: input.breakdownValues,
      limit: input.limit,
    }),
    base.timezone
  );
  return rows.map((row) => row.profile_id).filter(Boolean);
}

/**
 * `ctx.services.chart`'s funnel half, as its own module factory, composed by
 * `createChartService`.
 */
export function createFunnelService(
  deps: ServiceDeps,
  _services: () => Services
) {
  return {
    getFunnelGroup,
    toSeries,
    getFunnel: (
      input: Parameters<typeof getFunnel>[1]
    ): ReturnType<typeof getFunnel> => getFunnel(deps, input),
    getFunnelCore: (
      input: Parameters<typeof getFunnelCore>[1]
    ): ReturnType<typeof getFunnelCore> => getFunnelCore(deps, input),
    buildFunnelBase: (
      input: Parameters<typeof buildFunnelBase>[1]
    ): ReturnType<typeof buildFunnelBase> => buildFunnelBase(deps, input),
    getFunnelProfileIds: (
      input: Parameters<typeof getFunnelProfileIds>[1]
    ): ReturnType<typeof getFunnelProfileIds> =>
      getFunnelProfileIds(deps, input),
  };
}