OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
// The ClickHouse queries live in src/overview.sql.ts. Every function here
// takes `ServiceDeps` first; the client is `deps.ch`, which is what puts the
// request's id on the query's log line.
import type { SqlFragment } from '@openpanel/db/src/clickhouse/sql';
import { average, sum } from '@openpanel/shared';
import { z } from 'zod';
import type { ServiceDeps, Services } from '../../services';
import { convertClickhouseDateToJs } from '../chart/src/dates';
import {
getEventFiltersWhereClause,
joinFilterClauses,
} from '../chart/src/filter-where';
import { getSettingsForProject } from '../organization/organization.service';
import type { IChartEventFilter, IInterval } from '../report/report.constants';
import { chartColors, zTimeInterval } from '../report/report.constants';
import {
distinctSessionsQuery,
liveMinuteCountsQuery,
liveMinuteReferrersQuery,
liveReferrersQuery,
liveTotalSessionsQuery,
mapDataQuery,
metricsWithPageFilterQuery,
revenueQuery,
sessionMetricsQuery,
topEntriesQuery,
topEntryExitQuery,
topEventsQuery,
topGenericQuery,
topGenericSeriesTimeSeriesQuery,
topGenericSeriesTopItemsQuery,
topLinkOutQuery,
topPagesQuery,
transitionsQuery,
} from './src/overview.sql';
import { runQuery } from './src/run-query';
// Maximum number of records to return (for detail modals)
const MAX_RECORDS_LIMIT = 1000;
const ROLLUP_DATE_PREFIX = '1970-01-01';
function isClickhouseDefaultMinDate(date: string): boolean {
return date.startsWith(ROLLUP_DATE_PREFIX) || date.startsWith('1969-12-31');
}
const COLUMN_PREFIX_MAP: Record<string, string> = {
region: 'country',
city: 'country',
browser_version: 'browser',
os_version: 'os',
};
const WHITELISTED_FILTERS = [
'os',
'path',
'city',
'brand',
'model',
'origin',
'region',
'device',
'country',
'browser',
'referrer',
'os_version',
'referrer_name',
'browser_version',
'referrer_type',
'utm_source',
'utm_medium',
'utm_campaign',
'utm_term',
'utm_content',
];
// Columns that exist on the sessions table but not on events — on events
// they're stored inside the properties map under __query.utm_*.
const UTM_COLUMNS = [
'utm_source',
'utm_medium',
'utm_campaign',
'utm_term',
'utm_content',
];
const UTM_PROPERTY_PREFIX = 'properties.__query.';
// A session has no `path`/`origin` of its own; its entry page stands in.
const SESSION_COLUMN_FOR_FILTER: Record<string, string> = {
path: 'entry_path',
origin: 'entry_origin',
};
// These compare against nothing, so they apply without a value.
const VALUELESS_OPERATORS: ReadonlySet<string> = new Set([
'isNull',
'isNotNull',
]);
interface MetricsRow {
bounce_rate: number;
unique_visitors: number;
total_sessions: number;
avg_session_duration: number;
total_screen_views: number;
views_per_session: number;
}
type MetricsSeriesRow = MetricsRow & { date: string; total_revenue: number };
export const zGetMetricsInput = z.object({
projectId: z.string(),
filters: z.array(z.any()),
startDate: z.string(),
endDate: z.string(),
interval: zTimeInterval,
});
export type IGetMetricsInput = z.infer<typeof zGetMetricsInput> & {
timezone: string;
};
export const zGetTopPagesInput = z.object({
projectId: z.string(),
filters: z.array(z.any()),
startDate: z.string(),
endDate: z.string(),
limit: z.number().min(1).max(1000).optional(),
});
export type IGetTopPagesInput = z.infer<typeof zGetTopPagesInput> & {
timezone: string;
};
export const zGetTopEntryExitInput = z.object({
projectId: z.string(),
filters: z.array(z.any()),
startDate: z.string(),
endDate: z.string(),
mode: z.enum(['entry', 'exit']),
limit: z.number().min(1).max(1000).optional(),
});
export type IGetTopEntryExitInput = z.infer<typeof zGetTopEntryExitInput> & {
timezone: string;
};
export const zGetTopGenericInput = z.object({
projectId: z.string(),
filters: z.array(z.any()),
startDate: z.string(),
endDate: z.string(),
column: z.enum([
// Referrers
'referrer',
'referrer_name',
'referrer_type',
'utm_source',
'utm_medium',
'utm_campaign',
'utm_term',
'utm_content',
// Geo
'region',
'country',
'city',
// Device
'device',
'brand',
'model',
'browser',
'browser_version',
'os',
'os_version',
]),
});
export type IGetTopGenericInput = z.infer<typeof zGetTopGenericInput> & {
timezone: string;
};
export const zGetTopGenericSeriesInput = zGetTopGenericInput.extend({
interval: zTimeInterval,
});
export type IGetTopGenericSeriesInput = z.infer<
typeof zGetTopGenericSeriesInput
> & {
timezone: string;
};
export const zGetUserJourneyInput = z.object({
projectId: z.string(),
filters: z.array(z.any()),
startDate: z.string(),
endDate: z.string(),
steps: z.number().min(2).max(10).default(5),
});
export type IGetUserJourneyInput = z.infer<typeof zGetUserJourneyInput> & {
timezone: string;
};
export const zGetTopEventsInput = z.object({
projectId: z.string(),
filters: z.array(z.any()),
startDate: z.string(),
endDate: z.string(),
excludeEvents: z.array(z.string()).optional(),
});
export type IGetTopEventsInput = z.infer<typeof zGetTopEventsInput> & {
timezone: string;
};
export const zGetTopLinkOutInput = z.object({
projectId: z.string(),
filters: z.array(z.any()),
startDate: z.string(),
endDate: z.string(),
});
export type IGetTopLinkOutInput = z.infer<typeof zGetTopLinkOutInput> & {
timezone: string;
};
export const zGetMapDataInput = z.object({
projectId: z.string(),
filters: z.array(z.any()),
startDate: z.string(),
endDate: z.string(),
});
export type IGetMapDataInput = z.infer<typeof zGetMapDataInput> & {
timezone: string;
};
export interface ILiveMinuteCount {
minute: string;
sessionCount: number;
visitorCount: number;
timestamp: number;
time: string;
referrers: Array<{ referrer: string; count: number }>;
}
export interface ILiveData {
totalSessions: number;
minuteCounts: ILiveMinuteCount[];
referrers: Array<{ referrer: string; count: number }>;
}
async function createRevenueQuery(
deps: ServiceDeps,
{
projectId,
startDate,
endDate,
interval,
timezone,
filters,
}: {
projectId: string;
startDate: string;
endDate: string;
interval: IInterval;
timezone: string;
filters: IChartEventFilter[];
}
): Promise<{ date: string; total_revenue: number }[]> {
const rows = await runQuery<{ date: string; total_revenue: number }>(
deps,
revenueQuery({
projectId,
startDate,
endDate,
interval,
rawFilterWhere: getRawWhereClause('events', filters),
}),
timezone
);
return rows.map((row) => ({
...row,
date: convertClickhouseDateToJs(row.date).toISOString(),
}));
}
function mergeRevenueIntoSeries<T extends { date: string }>(
series: T[],
revenueData: { date: string; total_revenue: number }[]
): (T & { total_revenue: number })[] {
const revenueByDate = new Map(
revenueData
.filter((r) => !isClickhouseDefaultMinDate(r.date))
.map((r) => [r.date, r.total_revenue])
);
return series.map((row) => ({
...row,
total_revenue: revenueByDate.get(row.date) ?? 0,
}));
}
function getOverallRevenue(
revenueData: { date: string; total_revenue: number }[]
): number {
return (
revenueData.find((r) => isClickhouseDefaultMinDate(r.date))
?.total_revenue ?? 0
);
}
/**
* A page (`path`) filter has no column on `sessions`, so it's resolved
* against `events` first and the session ids that match are intersected in.
* Returns the mutually-exclusive pair every `sessions`-scoped query builder
* takes.
*/
function sessionsFilterMode(params: {
filters: IChartEventFilter[];
projectId: string;
startDate: string;
endDate: string;
}): {
rawFilterWhere: SqlFragment | null;
distinctSessionsCte: SqlFragment | null;
} {
if (!isPageFilter(params.filters)) {
return {
rawFilterWhere: getRawWhereClause('sessions', params.filters),
distinctSessionsCte: null,
};
}
return {
rawFilterWhere: null,
distinctSessionsCte: distinctSessionsQuery({
projectId: params.projectId,
startDate: params.startDate,
endDate: params.endDate,
rawFilterWhere: getRawWhereClause('events', params.filters),
}),
};
}
/** A filter still being edited in the UI has no value yet and filters nothing. */
function isFilterApplicable(filter: IChartEventFilter): boolean {
return (
(filter.value?.length ?? 0) > 0 || VALUELESS_OPERATORS.has(filter.operator)
);
}
export function isPageFilter(filters: IChartEventFilter[]) {
return filters.some(
(filter) => filter.name === 'path' && isFilterApplicable(filter)
);
}
export async function getMetrics(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
interval,
timezone,
}: IGetMetricsInput
): Promise<{
metrics: {
bounce_rate: number;
unique_visitors: number;
total_sessions: number;
avg_session_duration: number;
total_screen_views: number;
views_per_session: number;
total_revenue: number;
};
series: {
date: string;
bounce_rate: number;
unique_visitors: number;
total_sessions: number;
avg_session_duration: number;
total_screen_views: number;
views_per_session: number;
total_revenue: number;
}[];
}> {
return isPageFilter(filters)
? getMetricsWithPageFilter(deps, {
projectId,
filters,
startDate,
endDate,
interval,
timezone,
})
: getMetricsFromSessions(deps, {
projectId,
filters,
startDate,
endDate,
interval,
timezone,
});
}
async function getMetricsFromSessions(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
interval,
timezone,
}: IGetMetricsInput
): Promise<{
metrics: MetricsRow & { total_revenue: number };
series: MetricsSeriesRow[];
}> {
const [sessionRes, revenueRes] = await Promise.all([
runQuery<{
date: string;
bounce_rate: number;
unique_visitors: number;
total_sessions: number;
avg_session_duration: number;
total_screen_views: number;
views_per_session: number;
}>(
deps,
sessionMetricsQuery({
projectId,
startDate,
endDate,
interval,
rawFilterWhere: getRawWhereClause('sessions', filters),
}),
timezone
).then((rows) =>
rows.map((row) => ({ ...row, date: new Date(row.date).toISOString() }))
),
createRevenueQuery(deps, {
projectId,
startDate,
endDate,
interval,
timezone,
filters,
}),
]);
const overallRevenue = getOverallRevenue(revenueRes);
// A window without sessions has no rollup row (`HAVING` drops it), so the
// totals row is found by its date rather than assumed to be first.
const totals = sessionRes.find((row) => isClickhouseDefaultMinDate(row.date));
const series = mergeRevenueIntoSeries(
sessionRes.filter((row) => !isClickhouseDefaultMinDate(row.date)),
revenueRes
);
return {
metrics: {
bounce_rate: totals?.bounce_rate ?? 0,
unique_visitors: totals?.unique_visitors ?? 0,
total_sessions: totals?.total_sessions ?? 0,
avg_session_duration: totals?.avg_session_duration ?? 0,
total_screen_views: totals?.total_screen_views ?? 0,
views_per_session: totals?.views_per_session ?? 0,
total_revenue: overallRevenue,
},
series,
};
}
async function getMetricsWithPageFilter(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
interval,
timezone,
}: IGetMetricsInput
): Promise<{
metrics: MetricsRow & { total_revenue: number };
series: MetricsSeriesRow[];
}> {
const rawEventFilterWhere = getRawWhereClause('events', filters);
const rawSessionFilterWhere = getRawWhereClause('sessions', filters);
const [mainRes, revenueRes] = await Promise.all([
runQuery<{
date: string;
bounce_rate: number;
unique_visitors: number;
total_sessions: number;
avg_session_duration: number;
total_screen_views: number;
views_per_session: number;
overall_unique_visitors: number;
overall_total_sessions: number;
overall_bounce_rate: number;
}>(
deps,
metricsWithPageFilterQuery({
projectId,
startDate,
endDate,
interval,
rawSessionFilterWhere,
rawEventFilterWhere,
}),
timezone
).then((rows) =>
rows.map((row) => ({ ...row, date: new Date(row.date).toISOString() }))
),
createRevenueQuery(deps, {
projectId,
startDate,
endDate,
interval,
timezone,
filters,
}),
]);
const overallRevenue = getOverallRevenue(revenueRes);
const series = mergeRevenueIntoSeries(mainRes, revenueRes);
const anyRowWithData = mainRes.find(
(item) =>
item.overall_bounce_rate !== null ||
item.overall_total_sessions !== null ||
item.overall_unique_visitors !== null
);
return {
metrics: {
bounce_rate: anyRowWithData?.overall_bounce_rate ?? 0,
unique_visitors: anyRowWithData?.overall_unique_visitors ?? 0,
total_sessions: anyRowWithData?.overall_total_sessions ?? 0,
avg_session_duration: average(
mainRes.map((item) => item.avg_session_duration)
),
total_screen_views: sum(mainRes.map((item) => item.total_screen_views)),
views_per_session: average(mainRes.map((item) => item.views_per_session)),
total_revenue: overallRevenue,
},
series,
};
}
/**
* The sources widget names a UTM filter by its events spelling
* (`properties.__query.utm_source`), saved filters may use the bare column;
* both resolve to the bare name before the whitelist sees them.
*/
function toOverviewFilterName(name: string): string {
const bareName = name.startsWith(UTM_PROPERTY_PREFIX)
? name.slice(UTM_PROPERTY_PREFIX.length)
: name;
return UTM_COLUMNS.includes(bareName) ? bareName : name;
}
function toTableFilterName(type: 'events' | 'sessions', name: string): string {
if (type === 'events') {
return UTM_COLUMNS.includes(name) ? `${UTM_PROPERTY_PREFIX}${name}` : name;
}
return SESSION_COLUMN_FOR_FILTER[name] ?? name;
}
export function getRawWhereClause(
type: 'events' | 'sessions',
filters: IChartEventFilter[]
): SqlFragment | null {
const where = getEventFiltersWhereClause(
filters.flatMap((item) => {
const name = toOverviewFilterName(item.name);
if (!WHITELISTED_FILTERS.includes(name)) {
return [];
}
return [{ ...item, name: toTableFilterName(type, name) }];
}),
undefined,
undefined,
type
);
return joinFilterClauses(where);
}
export async function getTopPages(
deps: ServiceDeps,
{ projectId, filters, startDate, endDate, timezone, limit }: IGetTopPagesInput
) {
return runQuery<{
origin: string;
path: string;
sessions: number;
pageviews: number;
revenue?: number;
}>(
deps,
topPagesQuery({
projectId,
startDate,
endDate,
rawFilterWhere: getRawWhereClause('events', filters),
limit: Math.min(limit ?? MAX_RECORDS_LIMIT, MAX_RECORDS_LIMIT),
}),
timezone
);
}
export async function getTopEntryExit(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
mode,
timezone,
limit,
}: IGetTopEntryExitInput
) {
const sessionsFilter = sessionsFilterMode({
filters,
projectId,
startDate,
endDate,
});
return runQuery<{
origin: string;
path: string;
sessions: number;
pageviews: number;
revenue?: number;
}>(
deps,
topEntryExitQuery({
projectId,
startDate,
endDate,
mode,
limit: Math.min(limit ?? MAX_RECORDS_LIMIT, MAX_RECORDS_LIMIT),
rawFilterWhere: sessionsFilter.rawFilterWhere,
distinctSessionsCte: sessionsFilter.distinctSessionsCte,
}),
timezone
);
}
export async function getTopGeneric(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
column,
timezone,
}: IGetTopGenericInput
) {
if (!WHITELISTED_FILTERS.includes(column)) {
return [];
}
const prefixColumn = COLUMN_PREFIX_MAP[column] ?? null;
const sessionsFilter = sessionsFilterMode({
filters,
projectId,
startDate,
endDate,
});
return runQuery<{
prefix?: string;
name: string;
sessions: number;
pageviews: number;
revenue?: number;
}>(
deps,
topGenericQuery({
projectId,
startDate,
endDate,
column,
prefixColumn,
limit: MAX_RECORDS_LIMIT,
rawFilterWhere: sessionsFilter.rawFilterWhere,
distinctSessionsCte: sessionsFilter.distinctSessionsCte,
}),
timezone
);
}
export async function getTopGenericSeries(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
column,
interval,
timezone,
}: IGetTopGenericSeriesInput
): Promise<{
items: Array<{
name: string;
prefix?: string;
data: Array<{
date: string;
sessions: number;
pageviews: number;
revenue?: number;
}>;
total: { sessions: number; pageviews: number; revenue?: number };
}>;
}> {
const prefixColumn = COLUMN_PREFIX_MAP[column] ?? null;
const TOP_LIMIT = 500;
const topItemsSessionsFilter = sessionsFilterMode({
filters,
projectId,
startDate,
endDate,
});
const topItems = await runQuery<{
prefix?: string;
name: string;
sessions: number;
pageviews: number;
revenue?: number;
}>(
deps,
topGenericSeriesTopItemsQuery({
projectId,
startDate,
endDate,
column,
prefixColumn,
limit: TOP_LIMIT,
rawFilterWhere: topItemsSessionsFilter.rawFilterWhere,
distinctSessionsCte: topItemsSessionsFilter.distinctSessionsCte,
}),
timezone
);
if (topItems.length === 0) {
return { items: [] };
}
// Unlike the top-items query above, the sessions rawWhere is applied here
// and additionally wrapped in distinct_sessions on a page filter — see
// overview.sql.ts's topGenericSeriesTimeSeriesQuery header.
const timeSeriesSessionsFilter = isPageFilter(filters)
? distinctSessionsQuery({
projectId,
startDate,
endDate,
rawFilterWhere: getRawWhereClause('events', filters),
})
: null;
const timeSeriesData = await runQuery<{
date: string;
prefix?: string;
name: string;
sessions: number;
pageviews: number;
revenue?: number;
}>(
deps,
topGenericSeriesTimeSeriesQuery({
projectId,
startDate,
endDate,
interval,
column,
prefixColumn,
rawFilterWhere: getRawWhereClause('sessions', filters),
distinctSessionsCte: timeSeriesSessionsFilter,
}),
timezone
).then((rows) =>
rows.map((row) => ({ ...row, date: new Date(row.date).toISOString() }))
);
const itemsMap = new Map<
string,
{
name: string;
prefix?: string;
data: Array<{
date: string;
sessions: number;
pageviews: number;
revenue?: number;
}>;
total: { sessions: number; pageviews: number; revenue?: number };
}
>();
for (const item of topItems) {
const key = `${item.prefix || ''}:${item.name}`;
itemsMap.set(key, {
name: item.name,
prefix: item.prefix,
data: [],
total: {
sessions: item.sessions,
pageviews: item.pageviews,
revenue: item.revenue ?? 0,
},
});
}
for (const row of timeSeriesData) {
const key = `${row.prefix || ''}:${row.name}`;
const item = itemsMap.get(key);
if (item) {
item.data.push({
date: row.date,
sessions: row.sessions,
pageviews: row.pageviews,
revenue: row.revenue,
});
}
}
return {
items: Array.from(itemsMap.values()),
};
}
export async function getUserJourney(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
steps = 5,
timezone,
}: IGetUserJourneyInput
): Promise<{
nodes: Array<{
id: string;
label: string;
nodeColor: string;
percentage?: number;
value?: number;
step?: number;
}>;
links: Array<{ source: string; target: string; value: number }>;
}> {
const TOP_ENTRIES = 3;
const TOP_DESTINATIONS_PER_NODE = 3;
const COLORS = chartColors.map((color) => color.main);
const rawFilterWhere = getRawWhereClause('events', filters);
const orderedEventsInput = { projectId, startDate, endDate, rawFilterWhere };
const topEntries = await runQuery<{ entry_page: string; count: number }>(
deps,
topEntriesQuery({ ...orderedEventsInput, steps, topEntries: TOP_ENTRIES }),
timezone
);
if (topEntries.length === 0) {
return { nodes: [], links: [] };
}
const topEntryPages = topEntries.map((e) => e.entry_page);
const totalSessions = topEntries.reduce((total, e) => total + e.count, 0);
const transitions = await runQuery<{
source: string;
target: string;
step: number;
value: number;
}>(
deps,
transitionsQuery({ ...orderedEventsInput, steps, topEntryPages }),
timezone
);
if (transitions.length === 0) {
return { nodes: [], links: [] };
}
// Node ids combine path with step, so a repeated path across steps
// doesn't collapse into one node.
const nodes = new Map<
string,
{ path: string; value: number; step: number; color: string }
>();
const links: Array<{ source: string; target: string; value: number }> = [];
const getNodeId = (path: string, step: number) => `${path}::step${step}`;
// Keyed by step, then source, so each active node reads only its own edges.
const transitionsByStepAndSource = new Map<
number,
Map<string, typeof transitions>
>();
for (const t of transitions) {
let bySource = transitionsByStepAndSource.get(t.step);
if (!bySource) {
bySource = new Map();
transitionsByStepAndSource.set(t.step, bySource);
}
const fromSource = bySource.get(t.source);
if (fromSource) {
fromSource.push(t);
} else {
bySource.set(t.source, [t]);
}
}
const activeNodes = new Map<string, string>(); // path -> nodeId
topEntries.forEach((entry, idx) => {
const nodeId = getNodeId(entry.entry_page, 1);
nodes.set(nodeId, {
path: entry.entry_page,
value: entry.count,
step: 1,
color: COLORS[idx % COLORS.length]!,
});
activeNodes.set(entry.entry_page, nodeId);
});
for (let step = 1; step < steps; step++) {
const stepTransitions = transitionsByStepAndSource.get(step);
const nextActiveNodes = new Map<string, string>();
for (const [sourcePath, sourceNodeId] of activeNodes) {
const fromSource = (stepTransitions?.get(sourcePath) ?? [])
.sort((a, b) => b.value - a.value)
.slice(0, TOP_DESTINATIONS_PER_NODE);
for (const t of fromSource) {
if (t.source === t.target) {
continue;
}
const targetNodeId = getNodeId(t.target, step + 1);
links.push({
source: sourceNodeId,
target: targetNodeId,
value: t.value,
});
const existing = nodes.get(targetNodeId);
if (existing) {
existing.value += t.value;
} else {
const sourceData = nodes.get(sourceNodeId);
nodes.set(targetNodeId, {
path: t.target,
value: t.value,
step: step + 1,
color: sourceData?.color || COLORS[nodes.size % COLORS.length]!,
});
}
nextActiveNodes.set(t.target, targetNodeId);
}
}
activeNodes.clear();
for (const [path, nodeId] of nextActiveNodes) {
activeNodes.set(path, nodeId);
}
if (activeNodes.size === 0) {
break;
}
}
const MIN_LINK_PERCENT = 0.25;
const minLinkValue = Math.ceil((totalSessions * MIN_LINK_PERCENT) / 100);
const filteredLinks = links.filter((link) => link.value >= minLinkValue);
const referencedNodeIds = new Set<string>();
filteredLinks.forEach((link) => {
referencedNodeIds.add(link.source);
referencedNodeIds.add(link.target);
});
const nodeValuesFromLinks = new Map<string, number>();
filteredLinks.forEach((link) => {
const current = nodeValuesFromLinks.get(link.target) || 0;
nodeValuesFromLinks.set(link.target, current + link.value);
});
nodes.forEach((nodeData, nodeId) => {
if (nodeData.step === 1) {
const hasOutgoing = filteredLinks.some((l) => l.source === nodeId);
if (!hasOutgoing) {
referencedNodeIds.delete(nodeId);
}
}
});
const finalNodes = Array.from(nodes.entries())
.filter(([id]) => referencedNodeIds.has(id))
.map(([id, data]) => {
const value =
data.step === 1
? data.value
: nodeValuesFromLinks.get(id) || data.value;
return {
id,
label: data.path,
nodeColor: data.color,
percentage: (value / totalSessions) * 100,
value,
step: data.step,
};
})
.sort((a, b) => {
if (a.step !== b.step) {
return a.step - b.step;
}
return b.value - a.value;
});
const nodeIds = new Set(finalNodes.map((n) => n.id));
const invalidLinks = filteredLinks.filter(
(link) => !(nodeIds.has(link.source) && nodeIds.has(link.target))
);
if (invalidLinks.length > 0) {
deps.logger.warn(
{ projectId, invalidLinkCount: invalidLinks.length },
'UserJourney: found links with missing nodes'
);
const validLinks = filteredLinks.filter(
(link) => nodeIds.has(link.source) && nodeIds.has(link.target)
);
return {
nodes: finalNodes,
links: validLinks,
};
}
const stepsValid = finalNodes.every((node, idx, arr) => {
if (idx === 0) {
return true;
}
return node.step! >= arr[idx - 1]!.step!;
});
if (!stepsValid) {
deps.logger.warn({ projectId }, 'UserJourney: steps are not monotonic');
}
return {
nodes: finalNodes,
links: filteredLinks,
};
}
export async function getTopEvents(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
timezone,
excludeEvents = ['session_start', 'session_end', 'screen_view'],
}: {
projectId: string;
filters: IChartEventFilter[];
startDate: string;
endDate: string;
timezone: string;
excludeEvents?: string[];
}
): Promise<Array<{ name: string; count: number }>> {
return runQuery<{ name: string; count: number }>(
deps,
topEventsQuery({
projectId,
startDate,
endDate,
rawFilterWhere: getRawWhereClause('events', filters),
excludeEvents,
}),
timezone
);
}
export async function getTopLinkOut(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
timezone,
}: {
projectId: string;
filters: IChartEventFilter[];
startDate: string;
endDate: string;
timezone: string;
}
): Promise<Array<{ href: string; count: number }>> {
return runQuery<{ href: string; count: number }>(
deps,
topLinkOutQuery({
projectId,
startDate,
endDate,
rawFilterWhere: getRawWhereClause('events', filters),
}),
timezone
);
}
export async function getMapData(
deps: ServiceDeps,
{
projectId,
filters,
startDate,
endDate,
timezone,
}: {
projectId: string;
filters: IChartEventFilter[];
startDate: string;
endDate: string;
timezone: string;
}
): Promise<
Array<{
country: string;
region?: string;
city?: string;
lat: number;
lng: number;
count: number;
}>
> {
const results = await runQuery<{
country: string;
region: string | null;
city: string | null;
count: number;
}>(
deps,
mapDataQuery({
projectId,
startDate,
endDate,
rawFilterWhere: getRawWhereClause('events', filters),
}),
timezone
);
// Placeholder lat/lng — geocoding is not implemented yet.
return results.map((row) => ({
country: row.country,
region: row.region ?? undefined,
city: row.city ?? undefined,
lat: 0,
lng: 0,
count: row.count,
}));
}
/** The dashboard's live/30-minute-window widget. */
export async function getLiveData(
deps: ServiceDeps,
projectId: string
): Promise<ILiveData> {
const [totalSessions, minuteCounts, minuteReferrers, referrers] =
await Promise.all([
runQuery<{ total_sessions: number }>(
deps,
liveTotalSessionsQuery({ projectId }),
'UTC'
),
runQuery<{
minute: string;
session_count: number;
visitor_count: number;
}>(deps, liveMinuteCountsQuery({ projectId }), 'UTC'),
runQuery<{ minute: string; referrer_name: string; count: number }>(
deps,
liveMinuteReferrersQuery({ projectId }),
'UTC'
),
runQuery<{ referrer: string; count: number }>(
deps,
liveReferrersQuery({ projectId }),
'UTC'
),
]);
const referrersByMinute = new Map<
string,
Array<{ referrer: string; count: number }>
>();
for (const item of minuteReferrers) {
if (!referrersByMinute.has(item.minute)) {
referrersByMinute.set(item.minute, []);
}
referrersByMinute.get(item.minute)!.push({
referrer: item.referrer_name,
count: item.count,
});
}
return {
totalSessions: totalSessions[0]?.total_sessions || 0,
minuteCounts: minuteCounts.map((item) => ({
minute: item.minute,
sessionCount: item.session_count,
visitorCount: item.visitor_count,
timestamp: new Date(item.minute).getTime(),
time: new Date(item.minute).toLocaleTimeString([], {
hour: '2-digit',
minute: '2-digit',
}),
referrers: referrersByMinute.get(item.minute) || [],
})),
referrers: referrers.map((item) => ({
referrer: item.referrer,
count: item.count,
})),
};
}
export type TrafficColumn =
| 'referrer'
| 'referrer_name'
| 'referrer_type'
| 'utm_source'
| 'utm_medium'
| 'utm_campaign'
| 'country'
| 'region'
| 'city'
| 'device'
| 'browser'
| 'os';
export async function getTrafficBreakdownCore(
deps: ServiceDeps,
input: {
projectId: string;
startDate: string;
endDate: string;
column: TrafficColumn;
filters?: IChartEventFilter[];
}
) {
const { timezone } = await getSettingsForProject(deps, input.projectId);
return getTopGeneric(deps, {
projectId: input.projectId,
filters: input.filters ?? [],
startDate: input.startDate,
endDate: input.endDate,
column: input.column,
timezone,
});
}
// Columns whose daily series we can derive from the sessions table. Page/entry
// insights (path/origin) live on the events table and aren't covered here — the
// caller degrades to no series for those.
const SEGMENT_SERIES_COLUMNS: ReadonlySet<string> = new Set<TrafficColumn>([
'referrer',
'referrer_name',
'referrer_type',
'utm_source',
'utm_medium',
'utm_campaign',
'country',
'region',
'city',
'device',
'browser',
'os',
]);
export interface SegmentDailyPoint {
date: string;
sessions: number;
pageviews: number;
}
// Daily breakdown for a single segment value (e.g. the "Twitter" referrer),
// so the explainer can see the *shape* of a change (one-off spike vs sustained
// growth) instead of only current-vs-baseline totals. Returns one point per day
// with zero-filled gaps; empty when the column isn't session-derived or the
// value never appears in the window.
export async function getSegmentDailySeriesCore(
deps: ServiceDeps,
input: {
projectId: string;
column: string;
value: string;
startDate: string;
endDate: string;
}
): Promise<SegmentDailyPoint[]> {
if (!SEGMENT_SERIES_COLUMNS.has(input.column)) {
return [];
}
const { timezone } = await getSettingsForProject(deps, input.projectId);
const { items } = await getTopGenericSeries(deps, {
projectId: input.projectId,
filters: [],
startDate: input.startDate,
endDate: input.endDate,
column: input.column as TrafficColumn,
interval: 'day',
timezone,
});
// getTopGenericSeries reports empty values as null name; insights store the
// empty referrer as "direct". Match the segment leniently.
const target = input.value.toLowerCase();
const matched = items.find((item) => {
const name = (item.name ?? '').toLowerCase();
return name === target || (name === '' && target === 'direct');
});
return (matched?.data ?? []).map((point) => ({
date: point.date,
sessions: Number(point.sessions ?? 0),
pageviews: Number(point.pageviews ?? 0),
}));
}
export interface GetAnalyticsOverviewInput {
projectId: string;
startDate: string;
endDate: string;
interval?: 'hour' | 'day' | 'week' | 'month';
filters?: IChartEventFilter[];
}
export async function getAnalyticsOverviewCore(
deps: ServiceDeps,
input: GetAnalyticsOverviewInput
) {
const { timezone } = await getSettingsForProject(deps, input.projectId);
const interval = input.interval ?? 'day';
const result = await getMetrics(deps, {
projectId: input.projectId,
filters: input.filters ?? [],
startDate: input.startDate,
endDate: input.endDate,
interval,
timezone,
});
return {
summary: result.metrics,
series: result.series,
interval,
startDate: input.startDate,
endDate: input.endDate,
};
}
export function createOverviewService(
deps: ServiceDeps,
_services: () => Services
) {
return {
isPageFilter,
getRawWhereClause,
getMetrics: (input: IGetMetricsInput): ReturnType<typeof getMetrics> =>
getMetrics(deps, input),
getTopPages: (input: IGetTopPagesInput): ReturnType<typeof getTopPages> =>
getTopPages(deps, input),
getTopEntryExit: (
input: IGetTopEntryExitInput
): ReturnType<typeof getTopEntryExit> => getTopEntryExit(deps, input),
getTopGeneric: (
input: IGetTopGenericInput
): ReturnType<typeof getTopGeneric> => getTopGeneric(deps, input),
getTopGenericSeries: (
input: IGetTopGenericSeriesInput
): ReturnType<typeof getTopGenericSeries> =>
getTopGenericSeries(deps, input),
getUserJourney: (
input: IGetUserJourneyInput
): ReturnType<typeof getUserJourney> => getUserJourney(deps, input),
getTopEvents: (
input: Parameters<typeof getTopEvents>[1]
): ReturnType<typeof getTopEvents> => getTopEvents(deps, input),
getTopLinkOut: (
input: Parameters<typeof getTopLinkOut>[1]
): ReturnType<typeof getTopLinkOut> => getTopLinkOut(deps, input),
getMapData: (
input: Parameters<typeof getMapData>[1]
): ReturnType<typeof getMapData> => getMapData(deps, input),
getLiveData: (projectId: string): Promise<ILiveData> =>
getLiveData(deps, projectId),
getTrafficBreakdownCore: (
input: Parameters<typeof getTrafficBreakdownCore>[1]
): ReturnType<typeof getTrafficBreakdownCore> =>
getTrafficBreakdownCore(deps, input),
getSegmentDailySeriesCore: (
input: Parameters<typeof getSegmentDailySeriesCore>[1]
): Promise<SegmentDailyPoint[]> => getSegmentDailySeriesCore(deps, input),
getAnalyticsOverviewCore: (
input: GetAnalyticsOverviewInput
): ReturnType<typeof getAnalyticsOverviewCore> =>
getAnalyticsOverviewCore(deps, input),
};
}