🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import { FastifyReply, FastifyRequest } from "fastify";
import { FilterParameter } from "./types.js";
import { getTimeStatement } from "./utils/timeWindow.js";
import { getSqlParam } from "./utils/getFilterStatement.js";
import { SESSION_CHANNEL_AGG } from "./utils/sessionAttribution.js";
import { FilterParams } from "@rybbit/shared";
import { analyticsRoute, getPaginationStatements, runPaginatedQuery } from "./utils/analyticsQuery.js";
import { buildSessionAndRowFilterFragments } from "./utils/sessionFilters.js";
interface GetMetricRequest {
Params: {
siteId: string;
};
Querystring: FilterParams<{
parameter: FilterParameter;
limit?: number;
page?: number;
}>;
}
type GetMetricResponse = {
value: string;
// title is only used for pathname
title?: string;
// hostname from ClickHouse events data
hostname?: string;
// count means sessions where this page was the entry/exit
count: number;
percentage: number;
pageviews?: number; // pageviews of this specific page when it was an entry/exit
pageviews_percentage?: number;
time_on_page_seconds?: number; // avg time on this page when it was an entry/exit
bounce_rate?: number; // percentage of sessions that were bounces (single pageview)
}[];
// This type represents a single item in the array returned *within* the data property
type MetricItem = {
value: string;
title?: string;
pathname?: string;
hostname?: string;
count: number;
percentage: number;
pageviews?: number;
pageviews_percentage?: number;
time_on_page_seconds?: number;
bounce_rate?: number;
};
// This is the structure the API will now send
type GetMetricPaginatedResponse = {
data: MetricItem[];
totalCount: number;
};
export const buildMetricQuery = (
query: GetMetricRequest["Querystring"],
siteId: number,
isCountQuery: boolean = false
) => {
const { filters, parameter } = query;
const timeStatement = getTimeStatement(query);
const { filteredSessionsCTE, rowFilterStatement } = buildSessionAndRowFilterFragments(
filters,
siteId,
timeStatement,
parameter === "event_name" ? ["event_name"] : []
);
const sessionJoin = filteredSessionsCTE ? "INNER JOIN FilteredSessions USING (session_id)" : "";
const aliasedSessionJoin = filteredSessionsCTE
? "INNER JOIN FilteredSessions fs ON e.session_id = fs.session_id"
: "";
const withFilteredSessions = filteredSessionsCTE ? `WITH ${filteredSessionsCTE}` : "";
const { limitStatement, offsetStatement } = getPaginationStatements(query, 100, isCountQuery);
if (parameter === "event_name") {
if (isCountQuery) {
return `
${withFilteredSessions}
SELECT COUNT(DISTINCT event_name) as totalCount
FROM events
${sessionJoin}
WHERE
site_id = {siteId:Int32}
AND event_name IS NOT NULL
AND event_name <> ''
${rowFilterStatement}
${timeStatement}
AND type = 'custom_event';
`;
}
return `
${withFilteredSessions}
SELECT
event_name as value,
COUNT(*) as count,
ROUND(COUNT(distinct(session_id)) * 100.0 / SUM(COUNT(distinct(session_id))) OVER (), 2) as percentage
FROM events
${sessionJoin}
WHERE
site_id = {siteId:Int32}
AND event_name IS NOT NULL
AND event_name <> ''
${rowFilterStatement}
${timeStatement}
AND type = 'custom_event'
GROUP BY event_name ORDER BY count desc, event_name asc
${limitStatement}
${offsetStatement};
`;
}
if (parameter === "page_title") {
const corePageTitleLogic = `
SELECT
page_title as value,
argMax(pathname, timestamp) as pathname,
COUNT(DISTINCT session_id) as unique_sessions
FROM events
${sessionJoin}
WHERE
site_id = {siteId:Int32}
AND page_title IS NOT NULL
AND page_title <> ''
AND type = 'pageview'
${timeStatement}
GROUP BY page_title
`;
if (isCountQuery) {
return `${withFilteredSessions} SELECT COUNT(*) as totalCount FROM (${corePageTitleLogic});`;
}
return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""} SessionPageCounts AS (
SELECT
session_id,
COUNT() as pageviews_in_session
FROM events
WHERE
site_id = {siteId:Int32}
AND type = 'pageview'
${timeStatement}
GROUP BY session_id
),
TitleStatsWithSessions AS (
SELECT
e.page_title as value,
e.pathname as pathname,
e.session_id AS session_id,
spc.pageviews_in_session
FROM events e
${aliasedSessionJoin}
LEFT JOIN SessionPageCounts spc ON e.session_id = spc.session_id
WHERE
e.site_id = {siteId:Int32}
AND e.page_title IS NOT NULL
AND e.page_title <> ''
AND e.type = 'pageview'
${timeStatement}
)
SELECT
value, -- This is page_title
any(pathname) as pathname, -- This is the representative pathname
COUNT(DISTINCT session_id) as count,
ROUND(
COUNT(DISTINCT session_id) * 100.0 / SUM(COUNT(DISTINCT session_id)) OVER (),
2
) as percentage,
ROUND(
countIf(DISTINCT session_id, pageviews_in_session = 1) * 100.0 / nullIf(COUNT(DISTINCT session_id), 0),
2
) as bounce_rate
FROM TitleStatsWithSessions
GROUP BY value
ORDER BY count DESC, value ASC
${limitStatement}
${offsetStatement};
`;
}
if (parameter === "exit_page" || parameter === "entry_page") {
const isEntry = parameter === "entry_page";
const orderDirection = isEntry ? "ASC" : "DESC";
const baseCteQuery = `
SessionPageCounts AS (
SELECT
session_id,
COUNT() as pageviews_in_session
FROM events
WHERE
site_id = {siteId:Int32}
AND type = 'pageview'
${timeStatement}
GROUP BY session_id
),
RelevantEvents AS (
SELECT
e.session_id AS session_id,
e.pathname AS pathname,
e.hostname AS hostname,
e.timestamp_ms AS timestamp_ms,
spc.pageviews_in_session
FROM events e
${aliasedSessionJoin}
LEFT JOIN SessionPageCounts spc ON e.session_id = spc.session_id
WHERE
e.site_id = {siteId:Int32}
AND e.type = 'pageview'
${timeStatement}
),
EventTimes AS (
SELECT
session_id,
pathname,
hostname,
timestamp_ms AS timestamp,
pageviews_in_session,
leadInFrame(timestamp_ms) OVER (PARTITION BY session_id ORDER BY timestamp_ms ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) as next_timestamp,
row_number() OVER (PARTITION BY session_id ORDER BY timestamp_ms ${orderDirection}) as row_num
FROM RelevantEvents
),
PageDurations AS (
SELECT
session_id,
pathname,
hostname,
timestamp,
next_timestamp,
row_num,
pageviews_in_session,
if(isNull(next_timestamp), 0, dateDiff('second', timestamp, next_timestamp)) as time_diff_seconds
FROM EventTimes
),
FilteredDurations AS (
SELECT *
FROM PageDurations
WHERE row_num = 1
),
PathStats AS (
SELECT
pathname,
anyHeavy(hostname) as top_hostname,
count(DISTINCT session_id) as unique_sessions,
count() as visits,
avg(if(time_diff_seconds < 0, 0, if(time_diff_seconds > 1800, 1800, time_diff_seconds))) as avg_time_on_page_seconds,
countIf(DISTINCT session_id, pageviews_in_session = 1) as bounced_sessions
FROM FilteredDurations
WHERE pathname IS NOT NULL AND pathname <> ''
GROUP BY pathname
)
`;
if (isCountQuery) {
return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""} ${baseCteQuery}
SELECT COUNT(DISTINCT pathname) as totalCount FROM PathStats;
`;
}
return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""} ${baseCteQuery}
SELECT
pathname as value,
top_hostname as hostname,
unique_sessions as count,
round((unique_sessions / sum(unique_sessions) OVER ()) * 100, 2) as percentage,
visits as pageviews,
round((visits / sum(visits) OVER ()) * 100, 2) as pageviews_percentage,
avg_time_on_page_seconds as time_on_page_seconds,
round((bounced_sessions / nullIf(unique_sessions, 0)) * 100, 2) as bounce_rate
FROM PathStats
ORDER BY unique_sessions DESC, pathname ASC
${limitStatement}
${offsetStatement};`;
}
if (parameter === "pathname") {
const baseCteQuery = `
SessionPageCounts AS (
SELECT
session_id,
COUNT() as pageviews_in_session
FROM events
WHERE
site_id = {siteId:Int32}
AND type = 'pageview'
${timeStatement}
GROUP BY session_id
),
EventTimes AS (
SELECT
e.session_id AS session_id,
e.pathname,
e.hostname,
e.timestamp,
spc.pageviews_in_session,
leadInFrame(e.timestamp) OVER (PARTITION BY e.session_id ORDER BY e.timestamp ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) as next_timestamp
FROM events e
${aliasedSessionJoin}
LEFT JOIN SessionPageCounts spc ON e.session_id = spc.session_id
WHERE
e.site_id = {siteId:Int32}
AND e.type = 'pageview'
${timeStatement}
),
PageDurations AS (
SELECT
session_id,
pathname,
hostname,
timestamp,
next_timestamp,
pageviews_in_session,
if(isNull(next_timestamp), 0, dateDiff('second', timestamp, next_timestamp)) as time_diff_seconds
FROM EventTimes
),
PathStats AS (
SELECT
pathname,
anyHeavy(hostname) as top_hostname,
count() as visits,
count(DISTINCT session_id) as unique_sessions,
avg(if(time_diff_seconds < 0, 0, if(time_diff_seconds > 1800, 1800, time_diff_seconds))) as avg_time_on_page_seconds,
countIf(DISTINCT session_id, pageviews_in_session = 1) as bounced_sessions
FROM PageDurations
GROUP BY pathname
)
`;
if (isCountQuery) {
return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""} ${baseCteQuery}
SELECT COUNT(DISTINCT pathname) as totalCount FROM PathStats;
`;
}
return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""} ${baseCteQuery}
SELECT
pathname as value,
top_hostname as hostname,
unique_sessions as count,
round((unique_sessions / sum(unique_sessions) OVER ()) * 100, 2) as percentage,
visits as pageviews,
round((visits / sum(visits) OVER ()) * 100, 2) as pageviews_percentage,
avg_time_on_page_seconds as time_on_page_seconds,
round((bounced_sessions / nullIf(unique_sessions, 0)) * 100, 2) as bounce_rate
FROM PathStats
ORDER BY unique_sessions DESC, pathname ASC
${limitStatement}
${offsetStatement};
`;
}
// Default case for other parameters
const sqlParam = getSqlParam(parameter);
// Sessions are attributed to their first attributed channel (matching the
// sessions views), not the first event's channel, which is often 'Direct'.
const valueExpression = parameter === "channel" ? SESSION_CHANNEL_AGG : `argMin(${sqlParam}, e.timestamp)`;
if (isCountQuery) {
return `
${withFilteredSessions}
SELECT COUNT(DISTINCT value) as totalCount
FROM (
SELECT
${valueExpression} as value
FROM events e
${aliasedSessionJoin}
WHERE
e.site_id = {siteId:Int32}
AND ${sqlParam} IS NOT NULL
AND ${sqlParam} <> ''
${timeStatement}
GROUP BY e.session_id
);
`;
}
return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""} SessionPageCounts AS (
SELECT
session_id,
COUNT() as pageviews_in_session
FROM events
WHERE
site_id = {siteId:Int32}
AND type = 'pageview'
${timeStatement}
GROUP BY session_id
),
SessionData AS (
SELECT
${valueExpression} as value,
e.session_id AS session_id,
any(spc.pageviews_in_session) as pageviews_in_session
FROM events e
${aliasedSessionJoin}
LEFT JOIN SessionPageCounts spc ON e.session_id = spc.session_id
WHERE
e.site_id = {siteId:Int32}
AND ${sqlParam} IS NOT NULL
AND ${sqlParam} <> ''
${timeStatement}
GROUP BY e.session_id
)
SELECT
value,
COUNT(DISTINCT session_id) as count,
round((COUNT(DISTINCT session_id) / sum(COUNT(DISTINCT session_id)) OVER ()) * 100, 2) as percentage,
-- SessionData holds one row per session, so COUNT() here is sessions;
-- pageviews are the attributed sessions' pageview totals.
sum(pageviews_in_session) as pageviews,
round((sum(pageviews_in_session) / nullIf(sum(sum(pageviews_in_session)) OVER (), 0)) * 100, 2) as pageviews_percentage,
round((countIf(DISTINCT session_id, pageviews_in_session = 1) / nullIf(COUNT(DISTINCT session_id), 0)) * 100, 2) as bounce_rate
FROM SessionData
GROUP BY value
ORDER BY count desc, value asc
${limitStatement}
${offsetStatement};
`;
};
export const getMetric = analyticsRoute<GetMetricRequest>(
req => req.query.parameter,
async (req: FastifyRequest<GetMetricRequest>, res: FastifyReply) => {
const siteId = Number(req.params.siteId);
const params = { siteId };
const result = await runPaginatedQuery<MetricItem>(
{ query: buildMetricQuery(req.query, siteId, false), params },
{ query: buildMetricQuery(req.query, siteId, true), params }
);
return res.send({ data: result });
}
);