\n ${tab(`/dashboard?key=${keyQs}${siteQs}`, \"Overview\", active === \"overview\")}\n ${tab(`/reports?key=${keyQs}${siteQs}`, \"Reports\", active === \"reports\")}\n ${tab(`/events?key=${keyQs}${siteQs}`, \"Events\", active === \"events\")}\n

`;\n}\n\nconst CHART_COLORS = [\n \"#5b8def\",\n \"#34d399\",\n \"#f59e0b\",\n \"#f472b6\",\n \"#a78bfa\",\n \"#22d3ee\",\n \"#fb7185\",\n \"#84cc16\",\n \"#facc15\",\n \"#38bdf8\",\n];\n\n// A dependency-free multi-line chart: one per series on a shared,\n// gap-filled day axis (missing days are zero rather than silently compressing\n// the x-axis). Built for 1..N series so the same renderer covers both the\n// single-site line and the all-sites, one-line-per-site breakdown.\nfunction buildLineChart(\n series: { label: string; color: string; points: { day: string; views: number }[] }[],\n days: number,\n) {\n const dayKeys: string[] = [];\n for (let i = days - 1; i >= 0; i--) {\n dayKeys.push(easternDayKey(new Date(Date.now() - i * 86400000)));\n }\n\n const filled = series.map((s) => {\n const byDay = new Map(s.points.map((p) => [p.day, p.views]));\n return { ...s, values: dayKeys.map((k) => byDay.get(k) ?? 0) };\n });\n\n const W = 720, H = 220, padL = 34, padR = 10, padT = 10, padB = 22;\n const plotW = W - padL - padR, plotH = H - padT - padB;\n const max = Math.max(1, ...filled.flatMap((s) => s.values));\n const slot = dayKeys.length > 1 ? plotW / (dayKeys.length - 1) : 0;\n const xAt = (i: number) => padL + i * slot;\n const yAt = (v: number) => padT + plotH - (v / max) * plotH;\n const multi = filled.length > 1;\n\n const lines = filled.map((s) => {\n const pts = s.values.map((v, i) => `${xAt(i).toFixed(1)},${yAt(v).toFixed(1)}`).join(\" \");\n return ``;\n }).join(\"\");\n\n // A visible dot plus a larger invisible hover target at every data point,\n // each carrying its own tooltip text (date + series + count) via\n // data-tip — the page-level script in page() positions a floating\n // tooltip on hover, so every point is individually inspectable rather\n // than the whole line sharing one title.\n const points = filled.map((s) => {\n return dayKeys.map((k, i) => {\n const v = s.values[i];\n const cx = xAt(i).toFixed(1), cy = yAt(v).toFixed(1);\n const tip = esc(`${multi ? s.label + \" — \" : \"\"}${formatDayLabel(k)}: ${v} view${v === 1 ? \"\" : \"s\"}`);\n return `` +\n ``;\n }).join(\"\");\n }).join(\"\");\n\n const ticks = [0, 0.5, 1].map((f) => {\n const val = Math.round(max * f);\n const ty = padT + plotH - f * plotH;\n return `\n ${val}`;\n }).join(\"\");\n\n const xStep = Math.max(1, Math.ceil(dayKeys.length / 8));\n const lastRegular = Math.floor((dayKeys.length - 1) / xStep) * xStep;\n const xLabels = dayKeys.map((day, i) => {\n const isRegular = i % xStep === 0;\n const isFinal = i === dayKeys.length - 1 && i - lastRegular >= Math.ceil(xStep / 2);\n if (!isRegular && !isFinal) return \"\";\n return `${day.slice(5)}`;\n }).join(\"\");\n\n const svg = ``;\n\n const legend = series.length > 1\n ? `

${\n series.map((s) =>\n `${esc(s.label)}`\n ).join(\"\")\n }

`\n : \"\";\n\n return svg + legend;\n}\n\n// GA4-style \"vs previous period\": a small colored delta under a stat card.\n// previous === 0 has no ratio to show — \"new\" if there's now activity where\n// there was none, an em dash if both periods are genuinely zero.\nfunction deltaBadge(current: number, previous: number): string {\n if (previous === 0) {\n return current === 0\n ? `—`\n : `new`;\n }\n const pct = ((current - previous) / previous) * 100;\n const cls = pct > 0.5 ? \"up\" : pct < -0.5 ? \"down\" : \"flat\";\n const sign = pct > 0 ? \"+\" : \"\";\n return `${sign}${pct.toFixed(0)}% vs ${previous}`;\n}\n\n// Two-line \"this period vs previous period\" chart, aligned by day-offset\n// within each period rather than by calendar date (their calendar dates\n// don't overlap) — the same idea as GA4's comparison chart. Replaces the\n// per-site breakdown chart while comparison mode is on, since overlaying\n// N sites × 2 periods stops being readable.\nfunction buildComparisonChart(\n currentPoints: { day: string; views: number }[],\n previousPoints: { day: string; views: number }[],\n days: number,\n) {\n const curDayKeys: string[] = [];\n const prevDayKeys: string[] = [];\n for (let i = days - 1; i >= 0; i--) {\n curDayKeys.push(easternDayKey(new Date(Date.now() - i * 86400000)));\n prevDayKeys.push(easternDayKey(new Date(Date.now() - (i + days) * 86400000)));\n }\n const curByDay = new Map(currentPoints.map((p) => [p.day, p.views]));\n const prevByDay = new Map(previousPoints.map((p) => [p.day, p.views]));\n const curValues = curDayKeys.map((k) => curByDay.get(k) ?? 0);\n const prevValues = prevDayKeys.map((k) => prevByDay.get(k) ?? 0);\n\n const W = 720, H = 220, padL = 34, padR = 10, padT = 10, padB = 22;\n const plotW = W - padL - padR, plotH = H - padT - padB;\n const max = Math.max(1, ...curValues, ...prevValues);\n const slot = days > 1 ? plotW / (days - 1) : 0;\n const xAt = (i: number) => padL + i * slot;\n const yAt = (v: number) => padT + plotH - (v / max) * plotH;\n const CUR_COLOR = \"#5b8def\", PREV_COLOR = \"#6b7280\";\n\n const lineFor = (values: number[], color: string, dashed: boolean) => {\n const pts = values.map((v, i) => `${xAt(i).toFixed(1)},${yAt(v).toFixed(1)}`).join(\" \");\n return ``;\n };\n const lines = lineFor(prevValues, PREV_COLOR, true) + lineFor(curValues, CUR_COLOR, false);\n\n const points = curDayKeys.map((k, i) => {\n const cv = curValues[i], pv = prevValues[i];\n const cx = xAt(i).toFixed(1);\n const curTip = esc(`This period — ${formatDayLabel(k)}: ${cv} view${cv === 1 ? \"\" : \"s\"}`);\n const prevTip = esc(\n `Previous period — ${formatDayLabel(prevDayKeys[i])}: ${pv} view${pv === 1 ? \"\" : \"s\"}`,\n );\n return `` +\n `` +\n `` +\n ``;\n }).join(\"\");\n\n const ticks = [0, 0.5, 1].map((f) => {\n const val = Math.round(max * f);\n const ty = padT + plotH - f * plotH;\n return `\n ${val}`;\n }).join(\"\");\n\n const xStep = Math.max(1, Math.ceil(curDayKeys.length / 8));\n const lastRegular = Math.floor((curDayKeys.length - 1) / xStep) * xStep;\n const xLabels = curDayKeys.map((day, i) => {\n const isRegular = i % xStep === 0;\n const isFinal = i === curDayKeys.length - 1 && i - lastRegular >= Math.ceil(xStep / 2);\n if (!isRegular && !isFinal) return \"\";\n return `${day.slice(5)}`;\n }).join(\"\");\n\n const svg = ``;\n\n const legend = `

\n This period\n Previous period\n

`;\n\n return svg + legend;\n}\n\nconst REPORT_RANGES = [7, 14, 30, 90];\nconst SOURCE_OPTIONS = [\n [\"\", \"All sources\"],\n [\"Direct\", \"Direct\"],\n [\"Organic Search\", \"Organic Search\"],\n [\"Paid\", \"Paid\"],\n [\"Social\", \"Social\"],\n [\"Social (campaign)\", \"Social (campaign)\"],\n [\"Email\", \"Email\"],\n [\"Referral\", \"Referral (any)\"],\n [\"Campaign\", \"Campaign (any)\"],\n];\nconst DEVICE_OPTIONS = [\n [\"\", \"All devices\"],\n [\"desktop\", \"Desktop\"],\n [\"mobile\", \"Mobile\"],\n [\"tablet\", \"Tablet\"],\n];\n\n// A GET form (no client JS framework needed) so every filter combination is\n// a plain, bookmarkable/shareable URL — ?days=&source=&device= alongside the\n// existing ?site=&key=. Each \n ${site ? `` : \"\"}\n Range\n Traffic source\n Device\n ${\n eventOptions\n ? `Event`\n : \"\"\n }\n Compare to previous period\n ${changed ? `Clear filters` : \"\"}\n `;\n}\n\napp.get(\"/reports\", async (c) => {\n const denied = requireKey(c);\n if (denied) return denied;\n const key = c.req.query(\"key\");\n\n const site = c.req.query(\"site\") || \"\";\n const keyQs = esc(key || \"\");\n const daysParam = parseInt(c.req.query(\"days\") || \"30\", 10);\n const days = REPORT_RANGES.includes(daysParam) ? daysParam : 30;\n const source = c.req.query(\"source\") || \"\";\n const device = c.req.query(\"device\") || \"\";\n const compare = c.req.query(\"compare\") === \"1\";\n const sinceSql = `-${days} day`;\n\n // `base` omits source/device so the two breakdown tables that populate\n // those filter dropdowns (traffic sources, devices) always show the full,\n // unfiltered picture — otherwise picking \"Mobile\" would collapse the\n // device table to a single row with nothing else to switch to.\n const base: ReportFilters = { sinceSql, site: site || undefined };\n const applied: ReportFilters = {\n sinceSql,\n site: site || undefined,\n trafficSource: source || undefined,\n deviceType: device || undefined,\n };\n // The N days immediately before the current period, same filters — GA4's\n // default \"preceding period\" comparison.\n const previous: ReportFilters = {\n ...applied,\n sinceSql: `-${days * 2} day`,\n untilSql: `-${days} day`,\n };\n\n const [\n dailyTimes,\n dailyBySiteRaw,\n sources,\n locations,\n devices,\n campaigns,\n events,\n nvr,\n top30,\n prevDailyTimes,\n prevNvr,\n ] = await Promise.all([\n dailyPageviewTimes(applied),\n site ? Promise.resolve([]) : dailySeriesRaw(applied),\n trafficSources(base),\n topLocations(applied),\n deviceBreakdown(base),\n topCampaigns(applied),\n eventBreakdown(applied),\n newVsReturning(applied),\n site ? topPaths(applied, 15) : Promise.resolve([]),\n compare ? dailyPageviewTimes(previous) : Promise.resolve([]),\n compare ? newVsReturning(previous) : Promise.resolve({ newVisitors: 0, returningVisitors: 0 }),\n ]);\n\n // Bucket into Eastern-time calendar days here in JS (Intl handles the\n // EST/EDT switch) rather than in SQL, which only ever knows UTC.\n const bucketByDay = (times: string[]) => {\n const counts = new Map();\n for (const t of times) {\n const k = easternDayKey(parseSqliteUtc(t));\n counts.set(k, (counts.get(k) ?? 0) + 1);\n }\n return Array.from(counts, ([day, views]) => ({ day, views }));\n };\n\n const daily = bucketByDay(dailyTimes);\n const totalViews = dailyTimes.length;\n const avgPerDay = Math.round(totalViews / days);\n const prevTotalViews = prevDailyTimes.length;\n const prevAvgPerDay = Math.round(prevTotalViews / days);\n const topPagesRows = top30.map((p) =>\n `${esc(p.path)}${p.views}`\n ).join(\"\");\n\n // Comparison mode swaps the by-site breakdown for a single this-vs-previous\n // overlay — a per-site x per-period grid stops being readable, so pick one.\n let chartHtml: string;\n if (compare) {\n chartHtml = buildComparisonChart(daily, bucketByDay(prevDailyTimes), days);\n } else if (site) {\n chartHtml = buildLineChart([{ label: siteLabel(site), color: CHART_COLORS[0], points: daily }], days);\n } else {\n const bySite = new Map();\n for (const r of dailyBySiteRaw) {\n if (isDevHost(r.site)) continue;\n if (!bySite.has(r.site)) bySite.set(r.site, []);\n bySite.get(r.site)!.push(r.created_at);\n }\n const ranked = Array.from(bySite.entries())\n .map(([s, times]) => [s, times.length] as const)\n .sort((a, b) => b[1] - a[1]);\n const chartSeries = ranked.map(([s], i) => ({\n label: siteLabel(s),\n color: CHART_COLORS[i % CHART_COLORS.length],\n points: bucketByDay(bySite.get(s)!),\n }));\n chartHtml = buildLineChart(chartSeries, days);\n }\n\n const filterNote = [\n source && `source: ${SOURCE_OPTIONS.find(([v]) => v === source)?.[1] ?? source}`,\n device && `device: ${DEVICE_OPTIONS.find(([v]) => v === device)?.[1] ?? device}`,\n compare && \"comparing to previous period\",\n ].filter(Boolean).join(\" · \");\n\n return c.html(page(`\n ${navTabs(\"reports\", site, keyQs)}\n ${site ? `

← All sites

` : \"\"}\n

Reports${site ? ` — ${esc(siteLabel(site))}` : \" — all sites\"}

${site ? esc(site) : \"Historical trends across the lead-gen network\"} · last ${days} days${\n filterNote ? ` · ${esc(filterNote)}` : \"\"\n }

\n\n ${filterBar(keyQs, site, days, source, device, compare)}\n\n

${totalViews}

pageviews, ${days}d

${\n compare ? deltaBadge(totalViews, prevTotalViews) : \"\"\n }

${avgPerDay}

avg / day

${\n compare ? deltaBadge(avgPerDay, prevAvgPerDay) : \"\"\n }

${nvr.newVisitors}

new visitors

${\n compare ? deltaBadge(nvr.newVisitors, prevNvr.newVisitors) : \"\"\n }

${nvr.returningVisitors}

returning visitors

${\n compare ? deltaBadge(nvr.returningVisitors, prevNvr.returningVisitors) : \"\"\n }

\n\n

Pageviews by day${compare ? \" — vs previous period\" : site ? \"\" : \", by site\"}

${chartHtml}

\n\n

Traffic sources (${days} days)

${\n trafficRows(sources) || ''\n }

Source	Views
No data yet

\n\n

Top locations (${days} days)

${\n locationRows(locations) || ''\n }

Place	Views
No data yet

\n\n

Devices & browsers (${days} days)

${\n deviceRows(devices) || ''\n }

Device	Browser	OS	Views
No data yet

\n\n

Campaigns (${days} days)

${\n campaignRows(campaigns) || ''\n }

Campaign	Source	Medium	Views
No UTM-tagged traffic yet

\n\n ${\n site\n ? `

Top pages (${days} days)

\n ${\n topPagesRows || ''\n }

Path	Views
No data yet

`\n : \"\"\n }\n\n

Custom events (${days} days)

${\n eventRows(events) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from a page

\n `, false));\n});\n\nfunction eventsBySiteRows(rows: { site: string; n: number }[]) {\n return rows.map((r) =>\n `${esc(siteLabel(r.site))}${r.n}`\n ).join(\"\");\n}\n\napp.get(\"/events\", async (c) => {\n const denied = requireKey(c);\n if (denied) return denied;\n const key = c.req.query(\"key\");\n\n const site = c.req.query(\"site\") || \"\";\n const keyQs = esc(key || \"\");\n const daysParam = parseInt(c.req.query(\"days\") || \"30\", 10);\n const days = REPORT_RANGES.includes(daysParam) ? daysParam : 30;\n const source = c.req.query(\"source\") || \"\";\n const device = c.req.query(\"device\") || \"\";\n const eventName = c.req.query(\"event\") || \"\";\n const compare = c.req.query(\"compare\") === \"1\";\n const sinceSql = `-${days} day`;\n\n // `base` (no source/device/event) keeps the filter dropdowns — traffic\n // source, device, and the event-name list itself — showing every option\n // that exists in the window, the same reasoning as the Reports tab.\n const base: ReportFilters = { sinceSql, site: site || undefined };\n const applied: ReportFilters = {\n sinceSql,\n site: site || undefined,\n trafficSource: source || undefined,\n deviceType: device || undefined,\n };\n const previous: ReportFilters = { ...applied, sinceSql: `-${days * 2} day`, untilSql: `-${days} day` };\n\n const [rowsRaw, allEvents, prevRowsRaw] = await Promise.all([\n eventTimes(applied),\n eventBreakdown(base, 50),\n compare ? eventTimes(previous) : Promise.resolve([]),\n ]);\n const rows = rowsRaw.filter((r) => !isDevHost(r.site) && (!eventName || r.event === eventName));\n const prevRows = prevRowsRaw.filter((r) => !isDevHost(r.site) && (!eventName || r.event === eventName));\n\n const eventOptions = Array.from(new Set(allEvents.map((r) => r.event))).sort();\n\n const bucketByDay = (times: { created_at: string }[]) => {\n const counts = new Map();\n for (const t of times) {\n const k = easternDayKey(parseSqliteUtc(t.created_at));\n counts.set(k, (counts.get(k) ?? 0) + 1);\n }\n return Array.from(counts, ([day, views]) => ({ day, views }));\n };\n\n const totalEvents = rows.length;\n const avgPerDay = Math.round(totalEvents / days);\n const distinctTypes = new Set(rows.map((r) => r.event)).size;\n const prevTotalEvents = prevRows.length;\n const prevAvgPerDay = Math.round(prevTotalEvents / days);\n const prevDistinctTypes = new Set(prevRows.map((r) => r.event)).size;\n\n let chartHtml: string;\n if (compare) {\n chartHtml = buildComparisonChart(bucketByDay(rows), bucketByDay(prevRows), days);\n } else {\n const byEvent = new Map();\n for (const r of rows) {\n if (!byEvent.has(r.event)) byEvent.set(r.event, []);\n byEvent.get(r.event)!.push(r);\n }\n const ranked = Array.from(byEvent.entries())\n .map(([e, times]) => [e, times.length] as const)\n .sort((a, b) => b[1] - a[1]);\n const chartSeries = ranked.map(([e], i) => ({\n label: e,\n color: CHART_COLORS[i % CHART_COLORS.length],\n points: bucketByDay(byEvent.get(e)!),\n }));\n chartHtml = buildLineChart(chartSeries, days);\n }\n\n const bySite = new Map();\n for (const r of rows) bySite.set(r.site, (bySite.get(r.site) ?? 0) + 1);\n const bySiteRows = Array.from(bySite, ([s, n]) => ({ site: s, n })).sort((a, b) => b.n - a.n);\n\n const breakdownRows = allEvents.filter((r) => !eventName || r.event === eventName);\n\n // Outbound clicks get their own table — the generic event breakdown groups\n // by (event, label) already, but \"outbound_click\" rows are the ones worth\n // seeing ranked on their own, since every other event type is a small,\n // fixed set while outbound destinations are open-ended.\n const outboundCounts = new Map();\n for (const r of rows) {\n if (r.event !== \"outbound_click\") continue;\n outboundCounts.set(r.event_label, (outboundCounts.get(r.event_label) ?? 0) + 1);\n }\n const outboundRows = Array.from(outboundCounts, ([label, n]) => ({ label, n }))\n .sort((a, b) => b.n - a.n);\n\n const filterNote = [\n source && `source: ${SOURCE_OPTIONS.find(([v]) => v === source)?.[1] ?? source}`,\n device && `device: ${DEVICE_OPTIONS.find(([v]) => v === device)?.[1] ?? device}`,\n eventName && `event: ${eventName}`,\n compare && \"comparing to previous period\",\n ].filter(Boolean).join(\" · \");\n\n return c.html(page(`\n ${navTabs(\"events\", site, keyQs)}\n ${site ? `

← All sites

` : \"\"}\n

Events${site ? ` — ${esc(siteLabel(site))}` : \" — all sites\"}

${\n site ? esc(site) : \"Custom events across the lead-gen network\"\n } · last ${days} days${filterNote ? ` · ${esc(filterNote)}` : \"\"}

\n\n ${\n filterBar(keyQs, site, days, source, device, compare, {\n basePath: \"/events\",\n eventName,\n eventOptions,\n })\n }\n\n

${totalEvents}

events, ${days}d

${\n compare ? deltaBadge(totalEvents, prevTotalEvents) : \"\"\n }

${avgPerDay}

avg / day

${\n compare ? deltaBadge(avgPerDay, prevAvgPerDay) : \"\"\n }

${distinctTypes}

event types

${\n compare ? deltaBadge(distinctTypes, prevDistinctTypes) : \"\"\n }

\n\n

Events by day${compare ? \" — vs previous period\" : eventName ? \"\" : \", by event\"}

${chartHtml}

\n\n

Event breakdown (${days} days)

${\n eventRows(breakdownRows) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from a page

\n\n

Outbound link clicks (${days} days)

${\n outboundRows.map((r) =>\n ``\n ).join(\"\") || ''\n }

Destination	Clicks
${esc(r.label)}	${r.n}
No outbound clicks yet

\n\n ${\n site\n ? \"\"\n : `

Events by site (${days} days)

${\n eventsBySiteRows(bySiteRows) || ''\n }

Site	Count
No data yet

`\n }\n `, false));\n});\n\napp.get(\"/dashboard\", async (c) => {\n const denied = requireKey(c);\n if (denied) return denied;\n const key = c.req.query(\"key\");\n\n const site = c.req.query(\"site\") || \"\";\n const keyQs = esc(key || \"\");\n\n if (site) {\n const f30: ReportFilters = { sinceSql: \"-30 day\", site };\n const expectedState = SITE_EXPECTED_STATE[site];\n const [d7, d30, top30, recent, live, locations, sources, devices, campaigns, events, nvr, geoRows] =\n await Promise.all([\n siteTotals(\"-7 day\").then((r) => r.find((x) => x.site === site)?.views ?? 0),\n siteTotals(\"-30 day\").then((r) => r.find((x) => x.site === site)?.views ?? 0),\n topPaths(f30, 15),\n recentViews(40, site),\n activeVisits(30, 100, site),\n topLocations(f30),\n trafficSources(f30),\n deviceBreakdown(f30),\n topCampaigns(f30),\n eventBreakdown(f30),\n newVsReturning(f30),\n expectedState ? regionBreakdown(f30) : Promise.resolve([]),\n ]);\n\n const topRows = top30.map((p) =>\n `${esc(p.path)}${p.views}`\n ).join(\"\");\n\n const recentRows = recent.map((r) => {\n const place = [r.region, r.country].filter(Boolean).join(\", \") || \"—\";\n return `${esc(r.path)}${flag(r.country_code)} ${esc(place)}${\n esc(r.referrer || \"—\")\n }${formatEastern(r.created_at)}`;\n }).join(\"\");\n\n const geoMatch = expectedState ? stateMatchStats(geoRows, expectedState) : null;\n\n return c.html(page(`\n ${navTabs(\"overview\", site, keyQs)}\n

← All sites

${esc(siteLabel(site))}

${esc(site)}

${d7}

pageviews, 7 days

${d30}

pageviews, 30 days

${live.length}

active, last 30 min

${nvr.newVisitors}

new visitors, 30d

${nvr.returningVisitors}

returning, 30d

\n\n ${\n geoMatch\n ? `

Geo match — is this traffic from ${esc(expectedState)}? (30 days)

Of ${geoMatch.total} pageviews, ${geoMatch.unknownPct}% carry no geo signal at all.

${geoMatch.matchedPct}%

from ${\n esc(expectedState)\n } (${geoMatch.matched})

${geoMatch.otherUsPct}%

other US (${geoMatch.otherUs})

${geoMatch.nonUsPct}%

non-US (${geoMatch.nonUs})

${geoMatch.unknownPct}%

unknown (${geoMatch.unknown})

`\n : \"\"\n }\n\n

Live — last 30 minutes

${\n liveRows(live, false) || ''\n }

Path	Location	Referrer	When
No active visitors right now

\n\n

Top locations (30 days)

${\n locationRows(locations) || ''\n }

Place	Views
No data yet

\n\n

Traffic sources (30 days)

${\n trafficRows(sources) || ''\n }

Source	Views
No data yet

\n\n

Devices & browsers (30 days)

${\n deviceRows(devices) || ''\n }

Device	Browser	OS	Views
No data yet

\n\n

Campaigns (30 days)

${\n campaignRows(campaigns) ||\n ''\n }

Campaign	Source	Medium	Views
No UTM-tagged traffic yet

\n\n

Custom events (30 days)

${\n eventRows(events) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from the page

\n\n

Top pages (30 days)

\n ${\n topRows || ''\n }

Path	Views
No data yet

Recent pageviews

\n ${\n recentRows || ''\n }

Path	Location	Referrer	Time (ET)
No data yet

\n `, true));\n }\n\n const f30: ReportFilters = { sinceSql: \"-30 day\" };\n const [today, last7, last30, recentRaw, liveRaw, locations, sources, devices, campaigns, events, nvr] =\n await Promise.all([\n siteTotals(\"-1 day\"),\n siteTotals(\"-7 day\"),\n siteTotals(\"-30 day\"),\n recentViews(40),\n activeVisits(30, 200),\n topLocations(f30),\n trafficSources(f30),\n deviceBreakdown(f30),\n topCampaigns(f30),\n eventBreakdown(f30),\n newVsReturning(f30),\n ]);\n const recent = recentRaw.filter((r) => !isDevHost(r.site));\n const live = liveRaw.filter((r) => !isDevHost(r.site));\n\n const bySite = new Map();\n for (const r of last30) if (!isDevHost(r.site)) bySite.set(r.site, { today: 0, d7: 0, d30: r.views });\n for (const r of last7) {\n if (isDevHost(r.site)) continue;\n const e = bySite.get(r.site) || { today: 0, d7: 0, d30: 0 };\n e.d7 = r.views;\n bySite.set(r.site, e);\n }\n for (const r of today) {\n if (isDevHost(r.site)) continue;\n const e = bySite.get(r.site) || { today: 0, d7: 0, d30: 0 };\n e.today = r.views;\n bySite.set(r.site, e);\n }\n const rows = Array.from(bySite.entries()).sort((a, b) => b[1].d30 - a[1].d30);\n\n const siteCards = rows.map(([s, v]) =>\n `\n

\n `\n ).join(\"\");\n\n const recentRows = recent.map((r) => {\n const place = [r.region, r.country].filter(Boolean).join(\", \") || \"—\";\n return `${esc(siteLabel(r.site))}${esc(r.path)}${\n flag(r.country_code)\n } ${esc(place)}${esc(r.referrer || \"—\")}${formatEastern(r.created_at)}`;\n }).join(\"\");\n\n return c.html(page(`\n ${navTabs(\"overview\", site, keyQs)}\n

Lead-gen network — visitor tracking

Self-hosted pageview tracker (1x1 pixel beacon) with approximate, IP-derived location. Click a site for its own page-level breakdown.

${\n siteCards ||\n '

No pageviews recorded yet — add the tracking snippet to a site and visit a page.

'\n }

\n\n

Live — active in the last 30 minutes (${live.length})

This page refreshes every 15 seconds. Location is US-state-level at best (country-only outside the US), derived from the visitor's IP address — not GPS, not exact.

${\n liveRows(live) || ''\n }

Site	Path	Location	Referrer	When
No active visitors right now

\n\n

Top locations (30 days, all sites)

${\n locationRows(locations) || ''\n }

Place	Views
No data yet

\n\n

New vs. returning visitors (30 days, all sites)

${nvr.newVisitors}

new

${nvr.returningVisitors}

returning

Only counts visits carrying a visitor ID — rows recorded before this feature existed aren't included.

\n\n

Traffic sources (30 days, all sites)

${\n trafficRows(sources) || ''\n }

Source	Views
No data yet

\n\n

Devices & browsers (30 days, all sites)

${\n deviceRows(devices) || ''\n }

Device	Browser	OS	Views
No data yet

\n\n

Campaigns (30 days, all sites)

${\n campaignRows(campaigns) || ''\n }

Campaign	Source	Medium	Views
No UTM-tagged traffic yet

\n\n

Custom events (30 days, all sites)

${\n eventRows(events) ||\n ''\n }

Event	Label	Count
No custom events tracked yet — call window.tribecaTrack(name, label) from any page

\n\n

Recent pageviews, all sites

\n ${\n recentRows || ''\n }

Site	Path	Location	Referrer	Time (ET)
No data yet

\n `, true));\n});\n\nfunction page(body: string, liveRefresh = false) {\n return `\n\n${liveRefresh ? '' : \"\"}\nSite Analytics\n\n${body}\n

\n\n`;\n}\n\nexport default app.fetch;\n","deleted","label",[19],{"_36":94,"_13":95,"_60":96,"_62":-5,"_63":64,"_65":97,"_67":68,"_38":98,"_17":99,"_71":86,"_73":100,"_75":52,"_76":-5,"_88":101,"_18":102},{"_36":59,"_13":19,"_60":61,"_62":-5,"_63":64,"_65":66,"_67":68,"_38":91,"_17":70,"_71":72,"_73":74,"_75":52,"_76":-5,"_88":92,"_18":93},{"_36":81,"_13":82,"_60":83,"_62":-5,"_63":64,"_65":84,"_67":68,"_38":85,"_17":32,"_71":86,"_73":87,"_75":52,"_76":-5,"_88":89,"_18":90},"9d071f30-1c68-46f1-bdd0-d2ce310d5897","util.ts","01a09600-c477-775d-b26d-9a1ecb77d313",1,["D",1789223027834],"script","// Shared, dependency-free parsing helpers used both when recording a live\n// event (main.tsx) and when backfilling historical rows (db.ts) — kept here\n// once so the two never drift apart.\n\nexport function parseUA(ua: string): { deviceType: string; browser: string; os: string } {\n const isTablet = /iPad|Tablet|(Android(?!.*Mobile))/i.test(ua);\n const isMobile = !isTablet && /Mobi|Android|iPhone|iPod/i.test(ua);\n const deviceType = isTablet ? \"tablet\" : isMobile ? \"mobile\" : \"desktop\";\n\n // Checked first, not last: plenty of real crawlers (Googlebot's mobile UA\n // among them) also carry \"Chrome/x.y\" in the string to get treated like a\n // modern browser by sites that sniff for it, so a browser check ahead of\n // the bot check would misfile them as a person on Chrome.\n let browser = \"Other\";\n if (\n /bot|crawler|spider|bingpreview|facebookexternalhit|headlesschrome|phantomjs|puppeteer|playwright|curl\\/|wget\\/|python-requests|python-urllib|go-http-client|libwww-perl|scrapy/i\n .test(ua)\n ) browser = \"Bot\";\n else if (/Edg\\//.test(ua)) browser = \"Edge\";\n else if (/OPR\\//.test(ua) || /Opera/.test(ua)) browser = \"Opera\";\n else if (/Chrome\\//.test(ua) && !/Chromium/.test(ua)) browser = \"Chrome\";\n else if (/Firefox\\//.test(ua)) browser = \"Firefox\";\n else if (/Safari\\//.test(ua) && !/Chrome/.test(ua)) browser = \"Safari\";\n\n // iPhone/iPad UAs contain \"like Mac OS X\", so the iOS check must run before\n // the macOS check or every iPhone gets misfiled as a Mac.\n let os = \"Other\";\n if (/Windows/.test(ua)) os = \"Windows\";\n else if (/iPhone|iPad|iPod/.test(ua)) os = \"iOS\";\n else if (/Mac OS X/.test(ua)) os = \"macOS\";\n else if (/Android/.test(ua)) os = \"Android\";\n else if (/Linux/.test(ua)) os = \"Linux\";\n\n return { deviceType, browser, os };\n}\n\nconst SEARCH_ENGINES = /google|bing|yahoo|duckduckgo|baidu|yandex|ecosia/i;\nconst SOCIAL_NETWORKS = /facebook|instagram|twitter|x\\.com|t\\.co|linkedin|tiktok|pinterest|reddit|threads\\.net/i;\n\nexport function classifyTrafficSource(\n referrer: string,\n utmMedium?: string | null,\n utmSource?: string | null,\n): string {\n if (utmMedium) {\n if (/cpc|ppc|paid|display/i.test(utmMedium)) return \"Paid\";\n if (/social/i.test(utmMedium)) return \"Social (campaign)\";\n if (/email/i.test(utmMedium)) return \"Email\";\n return `Campaign (${utmMedium})`;\n }\n if (utmSource) return `Campaign (${utmSource})`;\n if (!referrer) return \"Direct\";\n try {\n const host = new URL(referrer).hostname.replace(/^www\\./, \"\");\n if (SEARCH_ENGINES.test(host)) return \"Organic Search\";\n if (SOCIAL_NETWORKS.test(host)) return \"Social\";\n return `Referral (${host})`;\n } catch {\n return \"Direct\";\n }\n}\n\n// All display times are Eastern (the business's own timezone), even though\n// everything is stored in SQLite as UTC (`datetime('now')`). Intl's\n// timeZone handling covers the EST/EDT switch automatically, which a fixed\n// UTC offset would get wrong for half the year.\nconst ET_TZ = \"America/New_York\";\nconst ET_DAY_KEY = new Intl.DateTimeFormat(\"en-CA\", {\n timeZone: ET_TZ,\n year: \"numeric\",\n month: \"2-digit\",\n day: \"2-digit\",\n});\nconst ET_DATETIME = new Intl.DateTimeFormat(\"en-US\", {\n timeZone: ET_TZ,\n month: \"short\",\n day: \"numeric\",\n year: \"numeric\",\n hour: \"numeric\",\n minute: \"2-digit\",\n second: \"2-digit\",\n hour12: true,\n});\nconst DAY_LABEL = new Intl.DateTimeFormat(\"en-US\", {\n timeZone: \"UTC\",\n month: \"short\",\n day: \"numeric\",\n year: \"numeric\",\n});\n\n// SQLite stores \"YYYY-MM-DD HH:MM:SS\" with an implicit UTC offset.\nexport function parseSqliteUtc(s: string): Date {\n return new Date(s.replace(\" \", \"T\") + \"Z\");\n}\n\n// en-CA formats as YYYY-MM-DD, which doubles as a sortable day key.\nexport function easternDayKey(d: Date): string {\n return ET_DAY_KEY.format(d);\n}\n\nexport function formatEastern(s: string): string {\n return ET_DATETIME.format(parseSqliteUtc(s)) + \" ET\";\n}\n\n// dayKey is a plain \"YYYY-MM-DD\" calendar label (already Eastern), not an\n// instant — format it at noon UTC so no further timezone shift can move it\n// to a different calendar day.\nexport function formatDayLabel(dayKey: string): string {\n return DAY_LABEL.format(new Date(dayKey + \"T12:00:00Z\"));\n}\n","children",[],[82],["D",1789224326437],[],[19],"222265c3-893b-4870-bd10-a2dc943222c7","db.ts","01a09600-c477-775d-b26d-92451e701351",21,["D",1789224113720],6,"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { classifyTrafficSource, parseUA } from \"./util.ts\";\n\nexport interface Pageview {\n id: number;\n site: string;\n path: string;\n referrer: string;\n ua: string;\n country: string | null;\n country_code: string | null;\n region: string | null;\n utm_source: string | null;\n utm_medium: string | null;\n utm_campaign: string | null;\n utm_term: string | null;\n utm_content: string | null;\n device_type: string | null;\n browser: string | null;\n os: string | null;\n viewport: string | null;\n visitor_id: string | null;\n traffic_source: string | null;\n event: string;\n event_label: string | null;\n created_at: string;\n}\n\nexport interface Geo {\n country: string | null;\n countryCode: string | null;\n region: string | null;\n}\n\nexport interface EventFields {\n site: string;\n path: string;\n referrer: string;\n ua: string;\n geo: Geo | null;\n utmSource?: string | null;\n utmMedium?: string | null;\n utmCampaign?: string | null;\n utmTerm?: string | null;\n utmContent?: string | null;\n deviceType?: string | null;\n browser?: string | null;\n os?: string | null;\n viewport?: string | null;\n visitorId?: string | null;\n trafficSource?: string | null;\n event?: string;\n eventLabel?: string | null;\n}\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS pageviews (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n site TEXT NOT NULL,\n path TEXT NOT NULL,\n referrer TEXT NOT NULL DEFAULT '',\n ua TEXT NOT NULL DEFAULT '',\n created_at TEXT NOT NULL DEFAULT (datetime('now'))\n)`);\n\nawait sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_pageviews_site_time ON pageviews (site, created_at)`,\n);\n\n// Added after the table already existed in production — ALTER TABLE has no\n// \"IF NOT EXISTS\" for columns in SQLite, so add each one and swallow the\n// \"duplicate column\" error on every run after the first.\nasync function ensureColumn(name: string, type: string) {\n try {\n await sqlite.execute(`ALTER TABLE pageviews ADD COLUMN ${name} ${type}`);\n } catch (err) {\n if (!String(err).includes(\"duplicate column\")) throw err;\n }\n}\nawait ensureColumn(\"country\", \"TEXT\");\nawait ensureColumn(\"country_code\", \"TEXT\");\nawait ensureColumn(\"region\", \"TEXT\");\nawait ensureColumn(\"utm_source\", \"TEXT\");\nawait ensureColumn(\"utm_medium\", \"TEXT\");\nawait ensureColumn(\"utm_campaign\", \"TEXT\");\nawait ensureColumn(\"utm_term\", \"TEXT\");\nawait ensureColumn(\"utm_content\", \"TEXT\");\nawait ensureColumn(\"device_type\", \"TEXT\");\nawait ensureColumn(\"browser\", \"TEXT\");\nawait ensureColumn(\"os\", \"TEXT\");\nawait ensureColumn(\"viewport\", \"TEXT\");\nawait ensureColumn(\"visitor_id\", \"TEXT\");\nawait ensureColumn(\"traffic_source\", \"TEXT\");\nawait ensureColumn(\"event\", \"TEXT\");\nawait ensureColumn(\"event_label\", \"TEXT\");\n\nawait sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_pageviews_visitor ON pageviews (visitor_id)`,\n);\n\n// IP-prefix cache for the region lookup (see lookupGeo in main.tsx). Keyed\n// on a /24 (v4) or /64 (v6) prefix rather than the full IP, so a shared\n// network's visitors — the common case — cost one external call, not one\n// per visitor. `fetched_at` backs the TTL check; there is no cron sweep of\n// stale rows because the table stays small (one row per prefix ever seen,\n// not per hit) and a stale-but-unexpired row is simply re-fetched on its\n// next miss, not deleted.\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS geo_cache (\n ip_prefix TEXT PRIMARY KEY,\n region TEXT,\n fetched_at TEXT NOT NULL DEFAULT (datetime('now'))\n)`);\n\n// Region cache reads/writes for lookupGeo (main.tsx). Kept here, not in\n// main.tsx, so this file stays the only one that touches sqlite directly.\nexport async function getCachedRegion(\n prefix: string,\n): Promise<{ region: string | null; fetchedAt: string } | null> {\n const result = await sqlite.execute({\n sql: `SELECT region, fetched_at FROM geo_cache WHERE ip_prefix = ?`,\n args: [prefix],\n });\n const row = result.rows[0] as unknown as { region: string | null; fetched_at: string } | undefined;\n return row ? { region: row.region, fetchedAt: row.fetched_at } : null;\n}\n\nexport async function setCachedRegion(prefix: string, region: string | null): Promise {\n await sqlite.execute({\n sql:\n `INSERT INTO geo_cache (ip_prefix, region, fetched_at) VALUES (?, ?, datetime('now'))\n ON CONFLICT(ip_prefix) DO UPDATE SET region = excluded.region, fetched_at = excluded.fetched_at`,\n args: [prefix, region],\n });\n}\n\nexport async function recordEvent(f: EventFields) {\n await sqlite.execute({\n sql: `INSERT INTO pageviews (\n site, path, referrer, ua,\n country, country_code, region,\n utm_source, utm_medium, utm_campaign, utm_term, utm_content,\n device_type, browser, os, viewport, visitor_id, traffic_source, event, event_label\n ) VALUES (?,?,?,?, ?,?,?, ?,?,?,?,?, ?,?,?,?,?,?,?,?)`,\n args: [\n f.site,\n f.path,\n f.referrer,\n f.ua,\n f.geo?.country ?? null,\n f.geo?.countryCode ?? null,\n f.geo?.region ?? null,\n f.utmSource ?? null,\n f.utmMedium ?? null,\n f.utmCampaign ?? null,\n f.utmTerm ?? null,\n f.utmContent ?? null,\n f.deviceType ?? null,\n f.browser ?? null,\n f.os ?? null,\n f.viewport ?? null,\n f.visitorId ?? null,\n f.trafficSource ?? null,\n f.event ?? \"pageview\",\n f.eventLabel ?? null,\n ],\n });\n}\n\n// One-time (idempotent) backfill for rows recorded before this schema\n// existed. `ua` and `referrer` were captured from the very first version of\n// this tracker, so device/browser/OS and traffic-source CAN be recovered for\n// old rows by re-parsing them now. What can NOT be recovered: geolocation\n// (no IP was ever stored — deliberately, for privacy — so there's nothing to\n// re-look-up) and UTM/visitor-id (the old beacon never sent them; the data\n// simply doesn't exist). Only touches rows that haven't been backfilled yet.\n// `all=true` reclassifies every row instead of only ones never touched —\n// for when the parsing logic itself changed (e.g. the bot-detection fix)\n// and existing rows may have been classified wrong the first time, not just\n// left unclassified.\nexport async function backfillHistoricalRows(all = false): Promise {\n const rows = await sqlite.execute(\n all\n ? `SELECT id, ua, referrer, utm_source, utm_medium FROM pageviews LIMIT 2000`\n : `SELECT id, ua, referrer, utm_source, utm_medium FROM pageviews WHERE device_type IS NULL LIMIT 2000`,\n );\n const list = rows.rows as unknown as {\n id: number;\n ua: string;\n referrer: string;\n utm_source: string | null;\n utm_medium: string | null;\n }[];\n for (const r of list) {\n const { deviceType, browser, os } = parseUA(r.ua || \"\");\n // Re-derive from the row's own utm columns (not null,null) — a forced\n // `all` pass must not clobber a newer row's already-correct UTM-based\n // classification (Paid/Campaign/etc.) by falling back to referrer-only.\n const trafficSource = classifyTrafficSource(r.referrer || \"\", r.utm_medium, r.utm_source);\n await sqlite.execute({\n sql:\n `UPDATE pageviews SET device_type = ?, browser = ?, os = ?, traffic_source = ?, event = COALESCE(event, 'pageview') WHERE id = ?`,\n args: [deviceType, browser, os, trafficSource, r.id],\n });\n }\n return list.length;\n}\n\nexport async function siteTotals(sinceSql: string) {\n const result = await sqlite.execute({\n sql:\n `SELECT site, COUNT(*) as views FROM pageviews WHERE created_at >= datetime('now', ?) GROUP BY site ORDER BY views DESC`,\n args: [sinceSql],\n });\n return result.rows as unknown as { site: string; views: number }[];\n}\n\n// A GA4-style filter set: every Reports-tab query takes one of these instead\n// of a growing list of positional params, so adding a new filter dimension\n// (traffic source, device) doesn't mean touching every function's signature\n// again. `trafficSource` of \"Referral\" or \"Campaign\" matches as a prefix,\n// since the real values carry the host/source too (e.g. \"Referral (x.com)\")\n// and there's no fixed small set to offer exact-match options for.\nexport interface ReportFilters {\n sinceSql: string;\n // Optional upper bound (created_at < datetime('now', untilSql)) — unset\n // means \"through now\". Only needed to carve out a bounded window, e.g.\n // the *previous* period for a GA4-style comparison (sinceSql=-60 day,\n // untilSql=-30 day gives the 30 days immediately before the current one).\n untilSql?: string;\n site?: string;\n trafficSource?: string;\n deviceType?: string;\n}\n\nfunction buildWhere(f: ReportFilters): { where: string; args: unknown[] } {\n const clauses = [`created_at >= datetime('now', ?)`];\n const args: unknown[] = [f.sinceSql];\n if (f.untilSql) {\n clauses.push(`created_at < datetime('now', ?)`);\n args.push(f.untilSql);\n }\n if (f.site) {\n clauses.push(`site = ?`);\n args.push(f.site);\n }\n if (f.trafficSource) {\n if (f.trafficSource === \"Referral\" || f.trafficSource === \"Campaign\") {\n clauses.push(`traffic_source LIKE ?`);\n args.push(f.trafficSource + \"%\");\n } else {\n clauses.push(`traffic_source = ?`);\n args.push(f.trafficSource);\n }\n }\n if (f.deviceType) {\n clauses.push(`device_type = ?`);\n args.push(f.deviceType);\n }\n return { where: clauses.join(\" AND \"), args };\n}\n\nexport async function topPaths(f: ReportFilters, limit = 10) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql: `SELECT path, COUNT(*) as views FROM pageviews WHERE ${where} GROUP BY path ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { path: string; views: number }[];\n}\n\nexport async function recentViews(limit = 50, site?: string) {\n const result = await sqlite.execute({\n sql: site\n ? `SELECT * FROM pageviews WHERE site = ? ORDER BY id DESC LIMIT ?`\n : `SELECT * FROM pageviews ORDER BY id DESC LIMIT ?`,\n args: site ? [site, limit] : [limit],\n });\n return result.rows as unknown as Pageview[];\n}\n\n// \"Live\" view — visits within the last N minutes, most recent first. This\n// page auto-refreshes every 15s (plain , no websocket), so this\n// query is what actually drives the near-real-time feel.\nexport async function activeVisits(minutes = 10, limit = 100, site?: string) {\n const result = await sqlite.execute({\n sql: site\n ? `SELECT * FROM pageviews WHERE site = ? AND created_at >= datetime('now', ?) ORDER BY id DESC LIMIT ?`\n : `SELECT * FROM pageviews WHERE created_at >= datetime('now', ?) ORDER BY id DESC LIMIT ?`,\n args: site ? [site, `-${minutes} minute`, limit] : [`-${minutes} minute`, limit],\n });\n return result.rows as unknown as Pageview[];\n}\n\nexport async function topLocations(f: ReportFilters, limit = 12) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(region, country, 'Unknown') as place, country_code, COUNT(*) as views\n FROM pageviews WHERE ${where} GROUP BY place, country_code ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { place: string; country_code: string | null; views: number }[];\n}\n\n// Exhaustive (no LIMIT) country_code/region counts for a site, used to\n// compute the \"Geo match\" card — what fraction of a site's traffic actually\n// comes from the state it targets. Needs every bucket, not just the top N,\n// since the match/other-US/non-US/unknown split has to add up to 100%.\nexport async function regionBreakdown(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(country_code, '') as country_code, COALESCE(region, '') as region, COUNT(*) as n\n FROM pageviews WHERE ${where} GROUP BY country_code, region`,\n args,\n });\n return result.rows as unknown as { country_code: string; region: string; n: number }[];\n}\n\nexport async function trafficSources(f: ReportFilters, limit = 10) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(traffic_source, 'Unknown') as source, COUNT(*) as views FROM pageviews WHERE ${where} GROUP BY source ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { source: string; views: number }[];\n}\n\nexport async function deviceBreakdown(f: ReportFilters, limit = 20) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT COALESCE(device_type,'unknown') as device_type, COALESCE(browser,'unknown') as browser, COALESCE(os,'unknown') as os, COUNT(*) as views\n FROM pageviews WHERE ${where} GROUP BY device_type, browser, os ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { device_type: string; browser: string; os: string; views: number }[];\n}\n\nexport async function topCampaigns(f: ReportFilters, limit = 10) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT utm_source, utm_medium, utm_campaign, COUNT(*) as views FROM pageviews\n WHERE ${where} AND utm_campaign IS NOT NULL\n GROUP BY utm_source, utm_medium, utm_campaign ORDER BY views DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as {\n utm_source: string | null;\n utm_medium: string | null;\n utm_campaign: string | null;\n views: number;\n }[];\n}\n\nexport async function eventBreakdown(f: ReportFilters, limit = 15) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT event, COALESCE(event_label, '') as event_label, COUNT(*) as n FROM pageviews\n WHERE ${where} AND event IS NOT NULL AND event != 'pageview'\n GROUP BY event, event_label ORDER BY n DESC LIMIT ?`,\n args: [...args, limit],\n });\n return result.rows as unknown as { event: string; event_label: string; n: number }[];\n}\n\n// Of visitors active in the window, how many are new (their very first-ever\n// hit falls inside the window) vs returning (they'd already been seen\n// before it started). Only counts rows carrying a visitor_id, so it's\n// meaningless for data recorded before that field existed. The filters\n// (site/source/device) scope which visits count as \"active\"; whether that\n// visitor is new-or-returning is still judged against their entire history.\nexport async function newVsReturning(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const sql = `\n WITH active AS (\n SELECT DISTINCT visitor_id FROM pageviews\n WHERE visitor_id IS NOT NULL AND ${where}\n ),\n first_seen AS (\n SELECT visitor_id, MIN(created_at) as fs FROM pageviews\n WHERE visitor_id IN (SELECT visitor_id FROM active)\n GROUP BY visitor_id\n )\n SELECT\n SUM(CASE WHEN fs >= datetime('now', ?) THEN 1 ELSE 0 END) as new_visitors,\n SUM(CASE WHEN fs < datetime('now', ?) THEN 1 ELSE 0 END) as returning_visitors\n FROM first_seen`;\n const result = await sqlite.execute({ sql, args: [...args, f.sinceSql, f.sinceSql] });\n const row = result.rows[0] as unknown as\n | { new_visitors: number | null; returning_visitors: number | null }\n | undefined;\n return { newVisitors: row?.new_visitors ?? 0, returningVisitors: row?.returning_visitors ?? 0 };\n}\n\n// Raw timestamps for the Reports tab's trend chart, one row per pageview in\n// the window. Bucketing into calendar days happens in main.tsx using\n// Eastern-time day keys — SQLite's date() only knows UTC, and the\n// business's own timezone is what \"by day\" should mean.\nexport async function dailyPageviewTimes(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql: `SELECT created_at FROM pageviews WHERE ${where}`,\n args,\n });\n return (result.rows as unknown as { created_at: string }[]).map((r) => r.created_at);\n}\n\nexport async function dailySeriesRaw(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql: `SELECT site, created_at FROM pageviews WHERE ${where}`,\n args,\n });\n return result.rows as unknown as { site: string; created_at: string }[];\n}\n\n// Raw rows for custom (non-pageview) events, for the Events report's trend\n// chart and per-event-name breakdown — same shape idea as\n// dailyPageviewTimes/dailySeriesRaw, just scoped to real events.\nexport async function eventTimes(f: ReportFilters) {\n const { where, args } = buildWhere(f);\n const result = await sqlite.execute({\n sql:\n `SELECT created_at, event, COALESCE(event_label, '') as event_label, site FROM pageviews\n WHERE ${where} AND event IS NOT NULL AND event != 'pageview'`,\n args,\n });\n return result.rows as unknown as {\n created_at: string;\n event: string;\n event_label: string;\n site: string;\n }[];\n}\n",[],[95],["D",1789161858464],"legacyEnvVars","httpPrivacy","description","Pageview tracker + dashboard for the lead-gen site network","imageUrl","userId","c5acd3b3-60a7-4deb-bc6d-528f83012ea9","pinnedFileId","25c3e054-ae27-11f1-936e-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount","tursoName","784adc29f9cf46efb3adc77fef19f98f","tursoHostname","784adc29f9cf46efb3adc77fef19f98f-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789516253896],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_11":12,"_150":146},"org",{"_11":12,"_143":144,"_145":146,"_109":110,"_147":148,"_149":52},"branch",{"_17":66,"_13":16,"_36":64,"_43":44,"_46":37,"_47":139,"_140":54,"_141":44,"_142":57,"_58":14,"_55":12},90,"forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","displayName","M A","avatar","https://lh3.googleusercontent.com/a/ACg8ocI9znhXn2hWp1wjjyWzeuOyoO_JrDgJo8aD0umFraR7bAG3UAmc=s96-c","tier","free","canRestrictVals","profileImageUrl","adoptOrgId",{"_36":44,"_13":14,"_38":193,"_50":-5,"_104":52,"_53":54,"_105":54,"_106":107,"_108":-5,"_109":110,"_111":112,"_113":68,"_114":-5,"_115":-5,"_116":32,"_56":57,"_117":118,"_119":120,"_121":194,"_123":-5,"_124":125,"_126":125,"_127":128,"_129":128,"_130":128,"_131":125,"_132":125,"_133":195,"_135":196,"_137":197,"_51":52},[184,185,186],"files",[184,185,186],"counts",{"_174":32,"_175":176,"_177":32,"_35":178,"_179":180},[],{"_36":59,"_13":19,"_60":61,"_62":-5,"_63":64,"_65":66,"_67":68,"_38":69,"_17":70,"_71":72,"_73":74,"_75":52,"_76":-5,"_18":173},"upstreamProject","upstreamBranch",{"_36":37,"_38":171,"_40":172,"_13":42,"_43":44,"_17":45,"_46":-5,"_47":-5,"_48":49,"_50":-5,"_51":52,"_53":54,"_55":12,"_56":57,"_58":14},"emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},["D",1789161858464],["D",1789238052434],[19],"pulls","branches",2,"forks",{"_181":84,"_183":32},"connections",{"_181":32,"_182":32},"count","unfilledCount","emptyCount",{"_36":94,"_13":95,"_60":96,"_62":-5,"_63":64,"_65":97,"_67":68,"_38":98,"_17":99,"_71":86,"_73":100,"_75":52,"_76":-5,"_88":191,"_18":192},{"_36":59,"_13":19,"_60":61,"_62":-5,"_63":64,"_65":66,"_67":68,"_38":91,"_17":70,"_71":72,"_73":74,"_75":52,"_76":-5,"_88":189,"_18":190},{"_36":81,"_13":82,"_60":83,"_62":-5,"_63":64,"_65":84,"_67":68,"_38":85,"_17":32,"_71":86,"_73":87,"_75":52,"_76":-5,"_88":187,"_18":188},[],[82],[],[19],[],[95],["D",1789161858464],["D",1789516253896],{"_11":12,"_150":146},{"_11":12,"_143":144,"_145":146,"_109":110,"_147":148,"_149":52},{"_17":66,"_13":16,"_36":64,"_43":44,"_46":37,"_47":139,"_140":54,"_141":44,"_142":57,"_58":14,"_55":12},"ctx",{"_133":-5,"_168":231,"_232":233,"_234":235,"_236":52,"_237":52,"_238":52,"_239":68,"_240":-5,"_241":-7,"_242":-7,"_243":32,"_244":52,"_245":52,"_246":-5,"_247":52,"_248":52,"_249":52,"_250":-5},"ENV",{"_202":203,"_204":205,"_206":42,"_207":208,"_209":210,"_211":212,"_213":214,"_215":216,"_217":218,"_219":220,"_221":222,"_223":224,"_225":226,"_227":228,"_229":230},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]