Function getPageConversionsCore

packages/db/src/services/pages.service.ts:270–321

(input: {
  projectId: string;
  startDate: string;
  endDate: string;
  conversionEvent: string;
  windowHours?: number;
  limit?: number;
})

}

export async function getPageConversionsCore(input: {
  projectId: string;
  startDate: string;
  endDate: string;
  conversionEvent: string;
  windowHours?: number;
  limit?: number;
}): Promise<IPageConversionRow[]> {
  const { projectId, startDate, endDate, conversionEvent, windowHours = 24, limit = 100 } = input;
  const sql = `
    WITH
    conversion_events AS (
      SELECT profile_id, created_at AS conv_time
      FROM events
      WHERE project_id = ${sqlstring.escape(projectId)}
        AND name = ${sqlstring.escape(conversionEvent)}
        AND created_at BETWEEN toDateTime(${sqlstring.escape(startDate)}) AND toDateTime(${sqlstring.escape(endDate)})
    ),
    views_before_conversions AS (
      SELECT DISTINCT e.profile_id, e.path, e.origin
      FROM events AS e
      INNER JOIN conversion_events AS c ON e.profile_id = c.profile_id
      WHERE e.project_id = ${sqlstring.escape(projectId)}
        AND e.name = 'screen_view'
        AND e.path != ''
        AND e.created_at BETWEEN toDateTime(${sqlstring.escape(startDate)}) AND toDateTime(${sqlstring.escape(endDate)})
        AND e.created_at < c.conv_time
        AND e.created_at >= c.conv_time - INTERVAL ${Number(windowHours)} HOUR
    ),
    total_visitors AS (
      SELECT path, origin, uniq(session_id) AS visitors
      FROM events
      WHERE project_id = ${sqlstring.escape(projectId)}
        AND name = 'screen_view'
        AND path != ''
        AND created_at BETWEEN toDateTime(${sqlstring.escape(startDate)}) AND toDateTime(${sqlstring.escape(endDate)})
      GROUP BY path, origin
    )
    SELECT
      vbc.path,
      vbc.origin,
      count() AS unique_converters,
      any(tv.visitors) AS total_visitors,
      round(100.0 * count() / any(tv.visitors), 2) AS conversion_rate
    FROM views_before_conversions AS vbc
    LEFT JOIN total_visitors AS tv ON vbc.path = tv.path AND vbc.origin = tv.origin
    GROUP BY vbc.path, vbc.origin
    ORDER BY unique_converters DESC
    LIMIT ${Number(limit)}
  `;
  return chQuery<IPageConversionRow>(sql);
}