Skip to content

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

7.8 KB
Raw Blame
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),
  };
}