Skip to content

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

6.8 KB
Raw Blame
// `services.pages` is its own service rather than folded into `overview`: both
// expose a `getTopPages`, over different tables (`events` scoped by search,
// `sessions` scoped by filters) with different inputs.

import type { ServiceDeps, Services } from '../../services';
import { getSettingsForProject } from '../organization/organization.service';
import type { IChartEventFilter, IInterval } from '../report/report.constants';
import {
  getTopPages as getOverviewTopPages,
  getTopEntryExit,
} from './overview.service';
import {
  pageConversionsQuery,
  pageTimeseriesQuery,
  type TopPagesQueryInput,
  topPagesQuery,
} from './src/pages.sql';
import { runQuery } from './src/run-query';

export interface IGetPagesInput extends Omit<TopPagesQueryInput, 'offset'> {
  timezone: string;
}

export interface IGetPagesPageInput extends IGetPagesInput {
  limit: number;
  offset: number;
}

export interface IPageTimeseriesRow {
  origin: string;
  path: string;
  date: string;
  pageviews: number;
  sessions: number;
}

export interface ITopPage {
  origin: string;
  path: string;
  title: string;
  sessions: number;
  pageviews: number;
  avg_duration: number;
  bounce_rate: number;
}

type TopPageRow = ITopPage & { total_count: number };

function queryTopPages(
  deps: ServiceDeps,
  { timezone, ...query }: IGetPagesInput | IGetPagesPageInput
): Promise<TopPageRow[]> {
  return runQuery<TopPageRow>(deps, topPagesQuery(query), timezone);
}

export async function getTopPages(
  deps: ServiceDeps,
  input: IGetPagesInput
): Promise<ITopPage[]> {
  const rows = await queryTopPages(deps, input);
  return rows.map(({ total_count: _total, ...page }) => page);
}

export async function getTopPagesPage(
  deps: ServiceDeps,
  input: IGetPagesPageInput
): Promise<{ data: ITopPage[]; meta: { count: number; take: number } }> {
  const rows = await queryTopPages(deps, input);
  return {
    data: rows.map(({ total_count: _total, ...page }) => page),
    meta: { count: rows[0]?.total_count ?? 0, take: input.limit },
  };
}

export interface IGetPageTimeseriesInput extends IGetPagesInput {
  interval: IInterval;
  filterOrigin?: string;
  filterPath?: string;
  /** Unbounded when omitted; see `PageTimeseriesQueryInput`. */
  topPagesPerBucket?: number;
}

export function getPageTimeseries(
  deps: ServiceDeps,
  {
    projectId,
    startDate,
    endDate,
    timezone,
    interval,
    filterOrigin,
    filterPath,
    topPagesPerBucket,
  }: IGetPageTimeseriesInput
): Promise<IPageTimeseriesRow[]> {
  return runQuery<IPageTimeseriesRow>(
    deps,
    pageTimeseriesQuery({
      projectId,
      startDate,
      endDate,
      interval,
      filterOrigin,
      filterPath,
      topPagesPerBucket,
    }),
    timezone
  );
}

export async function getTopPagesCore(
  deps: ServiceDeps,
  input: {
    projectId: string;
    startDate: string;
    endDate: string;
    limit?: number;
    filters?: IChartEventFilter[];
  }
) {
  const { timezone } = await getSettingsForProject(deps, input.projectId);
  return getOverviewTopPages(deps, {
    projectId: input.projectId,
    filters: input.filters ?? [],
    startDate: input.startDate,
    endDate: input.endDate,
    timezone,
    limit: input.limit,
  });
}

export async function getEntryExitPagesCore(
  deps: ServiceDeps,
  input: {
    projectId: string;
    startDate: string;
    endDate: string;
    mode: 'entry' | 'exit';
    limit?: number;
    filters?: IChartEventFilter[];
  }
) {
  const { timezone } = await getSettingsForProject(deps, input.projectId);
  return getTopEntryExit(deps, {
    projectId: input.projectId,
    filters: input.filters ?? [],
    startDate: input.startDate,
    endDate: input.endDate,
    mode: input.mode,
    timezone,
    limit: input.limit,
  });
}

export async function getPagePerformanceCore(
  deps: ServiceDeps,
  input: {
    projectId: string;
    startDate: string;
    endDate: string;
    search?: string;
    sortBy?: 'sessions' | 'pageviews' | 'bounce_rate' | 'avg_duration';
    sortOrder?: 'asc' | 'desc';
    limit?: number;
  }
) {
  const { timezone } = await getSettingsForProject(deps, input.projectId);
  const results = await getTopPages(deps, {
    projectId: input.projectId,
    startDate: input.startDate,
    endDate: input.endDate,
    timezone,
    search: input.search,
    limit: 1000,
  });

  const col = input.sortBy ?? 'sessions';
  const dir = input.sortOrder === 'asc' ? 1 : -1;
  const sorted = [...results].sort(
    (a, b) => dir * ((a[col] ?? 0) < (b[col] ?? 0) ? -1 : 1)
  );
  const limited = sorted.slice(0, input.limit ?? 50);

  const annotated = limited.map((p) => ({
    ...p,
    seo_signals: {
      high_bounce: p.bounce_rate > 70,
      low_engagement: p.avg_duration < 1,
      good_landing_page: p.bounce_rate < 40 && p.avg_duration > 2,
    },
  }));

  return {
    total_pages: results.length,
    shown: annotated.length,
    pages: annotated,
  };
}

export interface IPageConversionRow {
  path: string;
  origin: string;
  unique_converters: number;
  total_visitors: number;
  conversion_rate: number;
}

const DEFAULT_CONVERSION_WINDOW_HOURS = 24;
const DEFAULT_CONVERSION_LIMIT = 100;

export async function getPageConversionsCore(
  deps: ServiceDeps,
  input: {
    projectId: string;
    startDate: string;
    endDate: string;
    conversionEvent: string;
    windowHours?: number;
    limit?: number;
  }
): Promise<IPageConversionRow[]> {
  return runQuery<IPageConversionRow>(
    deps,
    pageConversionsQuery({
      projectId: input.projectId,
      startDate: input.startDate,
      endDate: input.endDate,
      conversionEvent: input.conversionEvent,
      windowHours: input.windowHours ?? DEFAULT_CONVERSION_WINDOW_HOURS,
      limit: input.limit ?? DEFAULT_CONVERSION_LIMIT,
    }),
    'UTC'
  );
}

export function createPagesService(
  deps: ServiceDeps,
  _services: () => Services
) {
  return {
    getTopPages: (input: IGetPagesInput): Promise<ITopPage[]> =>
      getTopPages(deps, input),
    getTopPagesPage: (
      input: IGetPagesPageInput
    ): ReturnType<typeof getTopPagesPage> => getTopPagesPage(deps, input),
    getPageTimeseries: (
      input: IGetPageTimeseriesInput
    ): Promise<IPageTimeseriesRow[]> => getPageTimeseries(deps, input),
    getTopPagesCore: (
      input: Parameters<typeof getTopPagesCore>[1]
    ): ReturnType<typeof getTopPagesCore> => getTopPagesCore(deps, input),
    getEntryExitPagesCore: (
      input: Parameters<typeof getEntryExitPagesCore>[1]
    ): ReturnType<typeof getEntryExitPagesCore> =>
      getEntryExitPagesCore(deps, input),
    getPagePerformanceCore: (
      input: Parameters<typeof getPagePerformanceCore>[1]
    ): ReturnType<typeof getPagePerformanceCore> =>
      getPagePerformanceCore(deps, input),
    getPageConversionsCore: (
      input: Parameters<typeof getPageConversionsCore>[1]
    ): Promise<IPageConversionRow[]> => getPageConversionsCore(deps, input),
  };
}