admin: per-product click analytics column (total, 7d, last-clicked date)

82f2795ad7c76fe9ccde9650fac680de4f51eeb5 · 2026-08-03 10:22:20 -0700 · Steve

Fold click aggregates into the products query via one grouped LEFT JOIN and
render a Clicks column per row — lifetime total, a 7-day sub-count, and the
last-clicked timestamp (dated with the standard when() helper). No-click
products show a muted — instead of fake zeros.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

M routes/admin.js

Diff

commit 82f2795ad7c76fe9ccde9650fac680de4f51eeb5
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Aug 3 10:22:20 2026 -0700

    admin: per-product click analytics column (total, 7d, last-clicked date)
    
    Fold click aggregates into the products query via one grouped LEFT JOIN and
    render a Clicks column per row — lifetime total, a 7-day sub-count, and the
    last-clicked timestamp (dated with the standard when() helper). No-click
    products show a muted — instead of fake zeros.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 routes/admin.js | 33 ++++++++++++++++++++++++++++++---
 1 file changed, 30 insertions(+), 3 deletions(-)

diff --git a/routes/admin.js b/routes/admin.js
index 4968b83..5805978 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -128,6 +128,18 @@ function when(ts) {
   return `<span class="when" title="${esc(d.toISOString())}">🕓 ${esc(label)}</span>`;
 }
 
+// Per-product click analytics cell: lifetime total, a 7-day sub-count, and the
+// last-clicked date (dating the analytics). No clicks → a muted "—" so the
+// mostly-quiet catalog reads honestly instead of showing fake zeros everywhere.
+function clickCell(p) {
+  const n = Number(p.clicks) || 0;
+  if (!n) return '<span class="subtle">—</span>';
+  const wk = Number(p.clicks7) || 0;
+  return `<b>${n}</b> click${n === 1 ? '' : 's'}`
+    + (wk ? ` <span class="subtle">(${wk} · 7d)</span>` : '')
+    + (p.last_click ? `<div>${when(p.last_click)}</div>` : '');
+}
+
 const shell = (body) => `<!doctype html><html lang="en"><head><meta charset="utf-8">
 <meta name="viewport" content="width=device-width,initial-scale=1"><title>IDS Admin</title>
 <style>
@@ -188,8 +200,22 @@ router.get('/admin', async (req, res, next) => {
       db.query(`SELECT p.id,p.title,p.advertiser,count(c.*) AS n
         FROM clicks c JOIN products p ON p.id=c.product_id
         GROUP BY p.id,p.title,p.advertiser ORDER BY n DESC LIMIT 10`),
-      db.query(`SELECT id,title,advertiser,network,external_id,brand,category,price,sale_price,image_url,featured,suppressed,in_stock,room,created_at,updated_at
-        FROM products ORDER BY featured DESC, suppressed, created_at DESC LIMIT 200`),
+      // Per-product click analytics folded in via one grouped LEFT JOIN: lifetime
+      // clicks, a 7-day window, and the last-clicked timestamp (the date we stamp
+      // on the analytics). Products with no clicks stay NULL → rendered as "—".
+      db.query(`SELECT p.id,p.title,p.advertiser,p.network,p.external_id,p.brand,p.category,p.price,p.sale_price,p.image_url,p.featured,p.suppressed,p.in_stock,p.room,p.created_at,p.updated_at,
+          COALESCE(cl.clicks,0)  AS clicks,
+          COALESCE(cl.clicks7,0) AS clicks7,
+          cl.last_click
+        FROM products p
+        LEFT JOIN (
+          SELECT product_id,
+                 count(*) AS clicks,
+                 count(*) FILTER (WHERE clicked_at > now() - interval '7 days') AS clicks7,
+                 max(clicked_at) AS last_click
+          FROM clicks GROUP BY product_id
+        ) cl ON cl.product_id = p.id
+        ORDER BY p.featured DESC, p.suppressed, p.created_at DESC LIMIT 200`),
       db.query(`SELECT id,slug,title,published,created_at FROM guides ORDER BY created_at DESC`),
     ]);
     const c = counts.rows[0];
@@ -216,6 +242,7 @@ router.get('/admin', async (req, res, next) => {
       <td>${esc(p.room || '')}</td>
       <td>${money(p.sale_price || p.price)}</td>
       <td>${when(p.created_at)}</td>
+      <td>${clickCell(p)}</td>
       <td><span class="pill ${p.featured ? 'on' : 'off'}">${p.featured ? 'Featured' : '—'}</span>${p.suppressed ? ' <span class="pill off">Hidden</span>' : ''}</td>
       ${details ? `<td class="subtle">${esc(p.brand || '')}</td><td class="subtle">${esc(p.category || '')}</td><td class="subtle">${esc(p.external_id || '')}</td><td class="subtle">${p.in_stock === false ? 'out' : 'in'}</td><td>${when(p.updated_at)}</td>` : ''}
       <td style="white-space:nowrap">
@@ -239,7 +266,7 @@ router.get('/admin', async (req, res, next) => {
       <h2>Products — curate featured (${products.rows.length})
         <a href="/admin?details=${details ? 0 : 1}" style="float:right;font-size:.75rem;font-weight:400;text-decoration:none;color:${details ? '#b1483c' : '#8a7250'}">${details ? '− Hide details' : '+ Show details'}</a>
       </h2>
-      <table><tr><th></th><th>Product</th><th>Room</th><th>Price</th><th>Added</th><th>Status</th>${detailHead}<th></th></tr>${prodRows}</table>
+      <table><tr><th></th><th>Product</th><th>Room</th><th>Price</th><th>Added</th><th>Clicks</th><th>Status</th>${detailHead}<th></th></tr>${prodRows}</table>
       <h2>Guides</h2>
       <table><tr><th>Guide</th><th>Created</th><th>Status</th><th></th></tr>${guideRows}</table>
     `));

← 5b5102f seo: breadcrumbs (nav + BreadcrumbList) across facet/room/gu · back to Interiordesignershowroom · admin: keyword/id suppression rules (Suppress page) + Vacuum 72708fc →