🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import { FastifyReply, FastifyRequest } from "fastify";
import { getTimeStatement } from "./utils/timeWindow.js";
import { FilterParams } from "@rybbit/shared";
import {
analyticsRoute,
getPaginationStatements,
runAnalyticsQuery,
runPaginatedQuery,
} from "./utils/analyticsQuery.js";
import { buildFilteredSessionsCTE } from "./utils/sessionFilters.js";
interface GetPageTitlesRequest {
Params: {
siteId: string;
};
Querystring: FilterParams<{
limit?: number;
page?: number;
}>;
}
// This type represents a single item in the array
export type PageTitleItem = {
value: string;
pathname: string;
count: number;
percentage: number;
pageviews?: number;
bounce_rate?: number;
time_on_page_seconds?: number;
};
// Structure for paginated response (if/when fully paginated)
type PageTitlesPaginatedResponse = {
data: PageTitleItem[];
totalCount: number;
};
export const buildPageTitlesQuery = (
query: GetPageTitlesRequest["Querystring"],
siteId: number,
isCountQuery: boolean = false
) => {
const { filters } = query;
const timeStatement = getTimeStatement(query);
const filteredSessionsCTE = buildFilteredSessionsCTE(filters, siteId, timeStatement);
const sessionJoin = filteredSessionsCTE ? "INNER JOIN FilteredSessions USING (session_id)" : "";
// StandardSection usually shows a small number, e.g., 7 or 10. Default to 10 for non-paginated use.
const { limitStatement, offsetStatement } = getPaginationStatements(query, 10, isCountQuery);
// For page_title, we want to count distinct sessions that viewed this title.
// We also need a representative pathname and calculate average time on page.
// Using argMax to get the pathname from the most recent event for that title in a session.
const baseCteQuery = `
SessionPageCounts AS (
SELECT
session_id,
COUNT() as pageviews_in_session
FROM events
${sessionJoin}
WHERE
site_id = {siteId:Int32}
AND type = 'pageview'
${timeStatement}
GROUP BY session_id
),
EventTimes AS (
SELECT
session_id,
page_title,
pathname,
timestamp,
leadInFrame(timestamp) OVER (PARTITION BY session_id ORDER BY timestamp ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) as next_timestamp
FROM events
${sessionJoin}
WHERE
site_id = {siteId:Int32}
AND type = 'pageview'
${timeStatement}
),
PageDurations AS (
SELECT
e.session_id as session_id,
e.page_title as page_title,
e.pathname as pathname,
e.timestamp as timestamp,
e.next_timestamp as next_timestamp,
if(isNull(e.next_timestamp), 0, dateDiff('second', e.timestamp, e.next_timestamp)) as time_diff_seconds,
spc.pageviews_in_session as pageviews_in_session
FROM EventTimes e
LEFT JOIN SessionPageCounts spc ON e.session_id = spc.session_id
),
PageTitleStats AS (
SELECT
pd.page_title as value,
-- Preserve the existing title grouping, but do not merge every untitled URL into one row.
if(pd.page_title = '', pd.pathname, '') as untitled_pathname,
argMax(pd.pathname, pd.timestamp) as pathname,
count(DISTINCT session_id) as unique_sessions,
count() as pageviews,
countIf(DISTINCT session_id, pageviews_in_session = 1) as bounced_sessions,
avg(if(time_diff_seconds < 0, 0, if(time_diff_seconds > 1800, 1800, time_diff_seconds))) as avg_time_on_page_seconds
FROM PageDurations pd
GROUP BY pd.page_title, untitled_pathname
)
`;
if (isCountQuery) {
return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""} ${baseCteQuery}
SELECT COUNT(*) as totalCount FROM PageTitleStats;
`;
}
return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""} ${baseCteQuery}
SELECT
value,
pathname,
unique_sessions as count,
ROUND(
unique_sessions * 100.0 / SUM(unique_sessions) OVER (),
2
) as percentage,
pageviews,
ROUND(
bounced_sessions * 100.0 / nullIf(unique_sessions, 0),
2
) as bounce_rate,
avg_time_on_page_seconds as time_on_page_seconds
FROM PageTitleStats
ORDER BY count DESC
${limitStatement}
${offsetStatement}
`;
};
export const getPageTitles = analyticsRoute<GetPageTitlesRequest>(
"page titles",
async (req: FastifyRequest<GetPageTitlesRequest>, res: FastifyReply) => {
const siteId = Number(req.params.siteId);
const params = { siteId };
const dataSpec = { query: buildPageTitlesQuery(req.query, siteId, false), params };
if (req.query.page !== undefined) {
const result = await runPaginatedQuery<PageTitleItem>(dataSpec, {
query: buildPageTitlesQuery(req.query, siteId, true),
params,
});
return res.send({ data: result });
}
// For non-paginated (StandardSection default) use, return the simpler structure
const items = await runAnalyticsQuery<PageTitleItem>(dataSpec);
return res.send({ data: items });
}
);