stats-dashboard

A serverless Cloudflare Worker that aggregates Cloudflare Web Analytics (RUM traffic + referrers), Google Search Console (keywords, CTR, ranking position), and Discourse forum activity for a portfolio of domains into one self-hosted daily dashboard — no external analytics SaaS, no client-side tracking script. A Cron Trigger pulls everything nightly, writes a snapshot per domain into D1 (SQLite at the edge), and renders instantly from stored history with 14-day sparklines, anomaly detection, and ranked SEO opportunities. Live example: https://stats.davidveksler.com.

• Traffic + referrers (24 h, 7 d, or 30 d) — Cloudflare Web Analytics (RUM) via the GraphQL Analytics API, with previous-period comparisons and anomaly callouts.
• Search performance + queries + landing pages — aggregate Google Search Console clicks, impressions, CTR, and average position, plus ranked query and page detail. GSC data lags ~2 days, so cards show the freshest full 3-day window.
• Traffic source mix — direct, search, social, referral, and unlisted sessions for the selected traffic period.
• A Cron Trigger pulls both every night at 13:00 UTC (~6 am Pacific), writes one snapshot per domain into D1, and sends an ntfy push (topic david-stats-cf-serp).
• The dashboard renders from stored D1 snapshots, so it loads instantly and builds 14-day sparklines over time. Domain filters, traffic/change sorting, mobile disclosure, and light/dark themes are built in.

Architecture

Cron 13:00 UTC ─┐
                ├─> Worker (src/index.js runDaily)
 /run?key=…  ───┘        ├─ pullTraffic()  → Cloudflare GraphQL (all 4 accounts)
                         ├─ querySearchSummary()/queryKeywords()/queryPages() → Google Search Console
                         ├─ write snapshot → D1 (traffic, referrers, queries, pages)
                         └─ sendNtfy()     → ntfy.sh/david-stats-cf-serp

GET /            → loadDashboard() reads D1 → renderDashboard() HTML
GET /api/json    → same data as JSON (`period`, `domain`, and `sort` query params)
GET /health      → "ok"
GET /run?key=…   → manual re-pull (needs REFRESH_KEY secret)

Files: src/config.js (domains + accounts), src/cloudflare.js (RUM pull), src/gsc.js (Search Console + JWT auth), src/render.js (HTML), src/index.js (handlers).

Domains tracked

Edit SITES in src/config.js. host = the Cloudflare Web Analytics requestHost; gsc = the exact Search Console property string (sc-domain:… or a URL prefix). Set gscPageFilter to an RE2 expression when a broad property needs to be limited to specific page URLs. The davidveksler.com card uses this to exclude subdomains.

Google Search Console auth (done — kept for reference)

The nightly job runs headless, so it can't use an interactive Google login. It uses a service account (GSC_SA_KEY, now set). If GSC_SA_KEY is ever missing, traffic/referrers still work fully and keywords are simply skipped (the ntfy push notes it). To rotate the key, repeat these steps and run ./deploy.sh --gsc-key path/to/key.json.

1. Google Cloud Console → create (or pick) a project → APIs & Services → Enable APIs → enable Google Search Console API.
2. IAM & Admin → Service Accounts → Create service account. No roles needed.
3. On the new account → Keys → Add key → JSON. Download the key file.
4. In Search Console (https://search.google.com/search-console), for each property → Settings → Users and permissions → Add user → paste the service account's client_email (looks like [email protected]) → Restricted (read) is enough. Do this for every configured property.
5. Store the whole JSON key as the Worker secret (one line):

   export CLOUDFLARE_API_TOKEN=<your-cf-token>
   wrangler secret put GSC_SA_KEY < path/to/key.json
   

6. Trigger a pull to confirm keywords populate:

   curl "https://stats.davidveksler.com/run?key=$REFRESH_KEY"
   # gscOk should be true; the ntfy warning disappears
   

Deploy / operate

Use the deploy script — idempotent, parses the CF token from ~/Projects/.cloudflare.env, ensures secrets, deploys, and smoke-tests:

./deploy.sh                     # install, ensure secrets, deploy, verify /health
./deploy.sh --refresh           # ...then trigger a live pull (/run) and print the result
./deploy.sh --gsc-key key.json  # also (re)set the GSC service-account secret
./deploy.sh --schema            # also (re)apply schema.sql — needs a D1:Edit-scoped token

Refresh the stored stats without deploying. The command reads the gitignored .deploy/refresh_key.txt, sends a browser user-agent to avoid WAF rule 1010, prints the JSON result, and exits nonzero when the request or GSC portion fails:

npm run refresh
./scripts/refresh-stats.sh

.\scripts\refresh-stats.ps1

Use npm run refresh -- --help for endpoint, key-file, timeout, and environment overrides.

From Windows PowerShell, use the Git Bash wrapper. It avoids accidentally selecting WSL's older Node runtime and forwards the same options:

.\deploy.ps1
.\deploy.ps1 -Refresh
.\deploy.ps1 -Schema
.\deploy.ps1 -GscKey C:\path\to\key.json

Manual bits:

export CLOUDFLARE_API_TOKEN=<first token in ~/Projects/.cloudflare.env>
npm run deploy    # raw wrangler deploy
npm run tail      # live logs

Secrets (set once via wrangler secret put, or auto-provisioned by deploy.sh):

• CF_API_TOKEN — Cloudflare token with Account Analytics: Read (the Worker's GraphQL calls). ✅ set
• REFRESH_KEY — protects GET /run and POST /ingest-bing. ✅ set (saved locally in .deploy/refresh_key.txt, gitignored)
• GSC_SA_KEY — Google service-account JSON. ✅ set
• BING_API_KEY — Bing Webmaster Tools API key. Set for /bing-sites and the (expected-to-fail, diagnostic-only) /run-bing — see below for why the real nightly Bing pull doesn't use it from here.

Note: the analytics token deploys the Worker and binds D1 at runtime, but is not scoped for D1 management writes, so d1 execute --remote / --schema can fail with codes 10000 or 7500. Additive dashboard migrations are also applied idempotently through the Worker's runtime D1 binding before a data pull.

Vars (in wrangler.jsonc): NTFY_TOPIC = david-stats-cf-serp.

The Bing pull runs in GitHub Actions, not on a Worker cron (.github/workflows/bing-pull.yml, scripts/bing-pull.mjs) — Bing throttles every call routed through Cloudflare Workers' shared egress IPs (ErrorCode 17 "ThrottleIP"), confirmed 2026-09-11 to be independent of the account, key, or pacing. The Action fetches from Bing and POSTs the results to POST /ingest-bing?key=<REFRESH_KEY>, which only writes them to D1. Requires two GitHub repo secrets (Settings → Secrets and variables → Actions): BING_API_KEY and STATS_REFRESH_KEY (the same value as the Worker's REFRESH_KEY).

The Action posts to the Worker's *.workers.dev URL, not stats.davidveksler.com — that zone has Bot Fight Mode on, which JS-challenges the GitHub runner's IP (a separate issue from the Bing-side throttle above, found right after that fix shipped). workers_dev: true in wrangler.jsonc serves the same Worker there too; REFRESH_KEY still gates /ingest-bing regardless of hostname. See the AGENTS.md gotcha for the full diagnosis.

Resources (created 2026-07-16)

• Worker: stats-dashboard on account David Veksler's Websites (556c237bf8cb62edb8f7b401499bb7a9)
• D1: stats-dashboard (2f5ea431-472e-462f-94a4-b396576c1a5b)
• Custom domain: stats.davidveksler.com
• Cron: 0 13 * * *

Notes / limitations

• Sessions are not unique people — Cloudflare's free RUM tier doesn't expose uniques. Referrer ranks use sessions; internal navigation is excluded.
• The davidveksler.com GSC property is a broad sc-domain: property, but its dashboard query filters the page dimension to root-domain URLs. This keeps its keywords separate from cheatsheets.davidveksler.com without requiring another GSC property.
• D1 grows ~a few KB/day; no pruning needed for years. Add a retention DELETE if desired.