Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | 5x 5x 4x 5x 4x 4x 4x 1x 1x | /**
* Analytics D1 Writers
* Functions for writing aggregated analytics data to D1
*/
import type { ProStats, ProjectStats } from "./types";
/**
* Write pro daily stats to D1
* Uses INSERT OR REPLACE for idempotency (can re-run for same date)
*/
export async function writeProDaily(
db: D1Database,
stats: ProStats[],
date: string,
): Promise<void> {
for (const row of stats) {
await db
.prepare(
`
INSERT OR REPLACE INTO analytics_pro_daily
(id, pro_id, date, page_views, project_clicks, image_clicks,
whatsapp_clicks, call_clicks, inquiry_clicks, unique_sessions,
sources, devices, cities, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, unixepoch())
`,
)
.bind(
`${row.pro_id}_${date}`,
row.pro_id,
date,
row.page_views || 0,
row.project_clicks || 0,
row.image_clicks || 0,
row.whatsapp_clicks || 0,
row.call_clicks || 0,
row.inquiry_clicks || 0,
row.unique_sessions || 0,
JSON.stringify(row.sources || {}),
JSON.stringify(row.devices || {}),
JSON.stringify(row.cities || {}),
)
.run();
}
}
/**
* Write project daily stats to D1
*/
export async function writeProjectDaily(
db: D1Database,
stats: ProjectStats[],
date: string,
): Promise<void> {
for (const row of stats) {
await db
.prepare(
`
INSERT OR REPLACE INTO analytics_project_daily
(id, project_id, pro_id, date, page_views, image_clicks,
unique_sessions, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, unixepoch())
`,
)
.bind(
`${row.project_id}_${date}`,
row.project_id,
row.pro_id,
date,
row.page_views || 0,
row.image_clicks || 0,
row.unique_sessions || 0,
)
.run();
}
}
/**
* Update pro summary table with rolling totals
* This table is optimized for fast dashboard queries
*
* NOTE: total_inquiries here represents "inquiry button clicks" from analytics,
* NOT the actual inquiry count. The stats API uses the inquiries table directly
* for the real inquiry count. This field is kept for historical click tracking.
*/
export async function updateProSummaries(db: D1Database): Promise<void> {
const sql = `
INSERT OR REPLACE INTO analytics_pro_summary
(pro_id, total_page_views, total_project_clicks, total_whatsapp_clicks,
total_call_clicks, total_inquiries, views_7d, views_30d, clicks_7d, clicks_30d,
last_view_at, last_click_at, updated_at)
SELECT
pro_id,
SUM(page_views) as total_page_views,
SUM(project_clicks) as total_project_clicks,
SUM(whatsapp_clicks) as total_whatsapp_clicks,
SUM(call_clicks) as total_call_clicks,
SUM(inquiry_clicks) as total_inquiries,
SUM(CASE WHEN date >= date('now', '-7 days') THEN page_views ELSE 0 END) as views_7d,
SUM(CASE WHEN date >= date('now', '-30 days') THEN page_views ELSE 0 END) as views_30d,
SUM(CASE WHEN date >= date('now', '-7 days') THEN project_clicks + whatsapp_clicks + call_clicks ELSE 0 END) as clicks_7d,
SUM(CASE WHEN date >= date('now', '-30 days') THEN project_clicks + whatsapp_clicks + call_clicks ELSE 0 END) as clicks_30d,
MAX(CASE WHEN page_views > 0 THEN date ELSE NULL END) as last_view_at,
MAX(CASE WHEN (project_clicks + whatsapp_clicks + call_clicks) > 0 THEN date ELSE NULL END) as last_click_at,
unixepoch() as updated_at
FROM analytics_pro_daily
GROUP BY pro_id
`;
try {
await db.prepare(sql).run();
} catch (error) {
console.error("Error updating pro summaries:", error);
throw error;
}
}
|