← back to Ga Allsites
GA all-sites dashboard: cache-ETL + live local server (DTD Option 1)
a646572606a98a2c60ebad22d645b63828e460c7 · 2026-08-04 13:39:44 -0700 · Steve Abrams
Files touched
A .gitignoreA .portA etl.pyA server.py
Diff
commit a646572606a98a2c60ebad22d645b63828e460c7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Aug 4 13:39:44 2026 -0700
GA all-sites dashboard: cache-ETL + live local server (DTD Option 1)
---
.gitignore | 10 +++
.port | 1 +
etl.py | 123 +++++++++++++++++++++++++++++++++
server.py | 227 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 files changed, 361 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..5bf391c
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,10 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+.next/
+cache/*.json
+__pycache__/
diff --git a/.port b/.port
new file mode 100644
index 0000000..3c24b43
--- /dev/null
+++ b/.port
@@ -0,0 +1 @@
+9780
diff --git a/etl.py b/etl.py
new file mode 100644
index 0000000..82bedd7
--- /dev/null
+++ b/etl.py
@@ -0,0 +1,123 @@
+#!/usr/bin/env python3
+"""ETL: pull 7d + 30d traffic for EVERY GA4 property the service account can see,
+write a single cache/data.json the dashboard reads. Run on a schedule — the
+dashboard NEVER calls the GA API on page load (DTD verdict, 2026-08-04:
+Option 1 live-local-server + contrarian-adopted cache-ETL).
+
+Auth: reuses the analytics skill's service account key.
+"""
+from __future__ import annotations
+
+import json
+import os
+import time
+from concurrent.futures import ThreadPoolExecutor, as_completed
+from datetime import datetime, timezone
+from pathlib import Path
+
+KEY_PATH = Path.home() / ".config" / "ga-analytics-agent" / "service-account.json"
+PROP_IDS_JSON = Path.home() / ".config" / "ga-analytics-agent" / "property-ids.json"
+CACHE = Path(__file__).parent / "cache" / "data.json"
+WORKERS = 8
+
+METRICS = ["sessions", "activeUsers", "screenPageViews", "conversions"]
+
+
+def gid_map() -> dict[str, str]:
+ """property_id -> G-XXXX measurement id, from the stored map (best-effort)."""
+ out: dict[str, str] = {}
+ try:
+ d = json.loads(PROP_IDS_JSON.read_text())
+ for k, v in d.items():
+ if k.startswith("G-"):
+ out[str(v)] = k
+ except Exception:
+ pass
+ return out
+
+
+def main() -> int:
+ if not KEY_PATH.exists():
+ raise SystemExit(f"ERROR: SA key not found at {KEY_PATH}")
+ os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = str(KEY_PATH)
+
+ from google.analytics.admin import AnalyticsAdminServiceClient
+ from google.analytics.data_v1beta import BetaAnalyticsDataClient
+ from google.analytics.data_v1beta.types import DateRange, Metric, RunReportRequest
+
+ admin = AnalyticsAdminServiceClient()
+ gids = gid_map()
+
+ # Enumerate every property the SA can access, across all accounts.
+ props: list[dict] = []
+ for s in admin.list_account_summaries():
+ acct = s.display_name or s.account.split("/")[-1]
+ for ps in s.property_summaries:
+ pid = ps.property.split("/")[-1]
+ props.append({
+ "account": acct,
+ "property": ps.display_name,
+ "property_id": pid,
+ "gid": gids.get(pid, ""),
+ })
+ print(f"Enumerated {len(props)} properties. Pulling 7d + 30d ...")
+
+ data = BetaAnalyticsDataClient()
+
+ def pull(p: dict) -> dict:
+ req = RunReportRequest(
+ property=f"properties/{p['property_id']}",
+ date_ranges=[
+ DateRange(start_date="7daysAgo", end_date="yesterday", name="d7"),
+ DateRange(start_date="30daysAgo", end_date="yesterday", name="d30"),
+ ],
+ metrics=[Metric(name=m) for m in METRICS],
+ )
+ try:
+ resp = data.run_report(req)
+ # With 2 date ranges + no dimensions, GA returns one row per range,
+ # tagged by a trailing 'dateRange' dimension value (d7 / d30).
+ buckets = {"d7": {m: 0 for m in METRICS}, "d30": {m: 0 for m in METRICS}}
+ for row in resp.rows:
+ tag = row.dimension_values[-1].value if row.dimension_values else "d7"
+ key = "d30" if "d30" in tag or tag.endswith("1") else "d7"
+ for i, m in enumerate(METRICS):
+ try:
+ buckets[key][m] = float(row.metric_values[i].value or 0)
+ except (IndexError, ValueError):
+ buckets[key][m] = 0
+ return {**p, **buckets, "error": ""}
+ except Exception as e:
+ return {**p, "d7": {m: 0 for m in METRICS}, "d30": {m: 0 for m in METRICS},
+ "error": str(e)[:120]}
+
+ rows: list[dict] = []
+ t0 = time.time()
+ with ThreadPoolExecutor(max_workers=WORKERS) as ex:
+ futs = {ex.submit(pull, p): p for p in props}
+ done = 0
+ for f in as_completed(futs):
+ rows.append(f.result())
+ done += 1
+ if done % 20 == 0:
+ print(f" {done}/{len(props)} ...")
+
+ # sort by 30d sessions desc
+ rows.sort(key=lambda r: r["d30"]["sessions"], reverse=True)
+
+ payload = {
+ "generated_at": datetime.now(timezone.utc).isoformat(),
+ "elapsed_s": round(time.time() - t0, 1),
+ "property_count": len(rows),
+ "metrics": METRICS,
+ "rows": rows,
+ }
+ CACHE.parent.mkdir(parents=True, exist_ok=True)
+ CACHE.write_text(json.dumps(payload, indent=2))
+ errs = sum(1 for r in rows if r["error"])
+ print(f"Wrote {CACHE} · {len(rows)} props · {errs} errors · {payload['elapsed_s']}s")
+ return 0
+
+
+if __name__ == "__main__":
+ raise SystemExit(main())
diff --git a/server.py b/server.py
new file mode 100644
index 0000000..fa51858
--- /dev/null
+++ b/server.py
@@ -0,0 +1,227 @@
+#!/usr/bin/env python3
+"""GA All-Sites dashboard — standing local server (DTD verdict 2026-08-04, Option 1).
+Reads cache/data.json ONLY (never calls the GA API on page load). A scheduled
+etl.py run refreshes the cache; a manual Refresh button can trigger it too.
+
+Basic-auth admin/DW2024! (house standard). Zero dependencies (stdlib only).
+"""
+from __future__ import annotations
+
+import base64
+import json
+import subprocess
+import sys
+import threading
+from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
+from pathlib import Path
+
+HERE = Path(__file__).parent
+CACHE = HERE / "cache" / "data.json"
+PORT = int((HERE / ".port").read_text().strip()) if (HERE / ".port").exists() else 9780
+USER, PW = "admin", "DW2024!"
+
+_refresh_lock = threading.Lock()
+_refreshing = {"on": False}
+
+
+def run_etl():
+ with _refresh_lock:
+ if _refreshing["on"]:
+ return
+ _refreshing["on"] = True
+ try:
+ subprocess.run([sys.executable, str(HERE / "etl.py")], cwd=str(HERE),
+ capture_output=True, timeout=300)
+ finally:
+ _refreshing["on"] = False
+
+
+PAGE = r"""<!DOCTYPE html><html><head><meta charset="utf-8">
+<meta name="viewport" content="width=device-width,initial-scale=1">
+<title>GA · All Sites At Once</title>
+<style>
+ :root{--fs:13px;--pad:6px}
+ *{box-sizing:border-box}
+ body{font:var(--fs)/1.4 -apple-system,BlinkMacSystemFont,"Segoe UI",sans-serif;margin:0;color:#1a1a1a;background:#f6f7f9}
+ header{position:sticky;top:0;z-index:5;background:#111;color:#fff;padding:12px 18px;display:flex;flex-wrap:wrap;gap:14px;align-items:center}
+ header h1{font-size:16px;margin:0;font-weight:600;letter-spacing:.2px}
+ header .stat{font-variant-numeric:tabular-nums;color:#cfe;font-size:13px}
+ header .muted{color:#9aa}
+ .bar{display:flex;flex-wrap:wrap;gap:10px;align-items:center;padding:10px 18px;background:#fff;border-bottom:1px solid #e6e8ec;position:sticky;top:44px;z-index:4}
+ .bar input[type=search]{padding:6px 10px;border:1px solid #cbd0d6;border-radius:6px;min-width:220px;font-size:13px}
+ .bar label{font-size:12px;color:#555;display:inline-flex;gap:4px;align-items:center;cursor:pointer}
+ .bar button{padding:6px 12px;border:1px solid #cbd0d6;background:#fafbfc;border-radius:6px;cursor:pointer;font-size:12px}
+ .bar button:hover{background:#eef1f4}
+ .cols{display:flex;flex-wrap:wrap;gap:2px 10px;padding:6px 18px;background:#fbfcfd;border-bottom:1px solid #eef0f3;font-size:11px;color:#667}
+ .wrap{padding:0 0 40px}
+ table{border-collapse:collapse;width:100%;background:#fff}
+ thead th{position:sticky;top:96px;background:#f2f4f7;text-align:right;padding:var(--pad) 12px;border-bottom:2px solid #dde1e6;font-weight:600;cursor:pointer;white-space:nowrap;user-select:none}
+ thead th.l{text-align:left}
+ thead th:hover{background:#e9edf2}
+ tbody td{padding:var(--pad) 12px;text-align:right;border-bottom:1px solid #f0f1f4;font-variant-numeric:tabular-nums;white-space:nowrap}
+ tbody td.l{text-align:left}
+ tbody tr:hover{background:#f8fafc}
+ a{color:#1558d6;text-decoration:none}
+ a:hover{text-decoration:underline}
+ .z{color:#b8bec6}
+ .acct{font-size:11px;color:#7a828c}
+ .badge{display:inline-block;padding:1px 7px;border-radius:10px;font-size:11px;font-weight:600}
+ .fresh{background:#e4f5e9;color:#1a7a3c}
+ .stale{background:#fdecc8;color:#8a5a00}
+ tfoot td{padding:8px 12px;font-weight:700;border-top:2px solid #dde1e6;text-align:right;background:#f7f9fb}
+ tfoot td.l{text-align:left}
+ .err{color:#c0392b;font-size:11px}
+</style></head><body>
+<header>
+ <h1>GA · All Sites At Once</h1>
+ <span class="stat" id="hstat">loading…</span>
+ <span id="hbadge"></span>
+ <span class="stat muted" style="margin-left:auto" id="hgen"></span>
+</header>
+<div class="bar">
+ <input type="search" id="q" placeholder="filter sites…" autocomplete="off">
+ <label>density <input type="range" id="dens" min="10" max="18" value="13"></label>
+ <label><input type="checkbox" id="onlyactive"> only sites with traffic</label>
+ <button id="refresh">↻ Refresh data</button>
+ <span class="muted" id="rmsg"></span>
+</div>
+<div class="cols" id="cols"></div>
+<div class="wrap"><table id="t">
+ <thead><tr id="hr"></tr></thead>
+ <tbody id="tb"></tbody>
+ <tfoot><tr id="tf"></tr></tfoot>
+</table></div>
+<script>
+const COLS=[
+ {k:'property',l:'Site',t:'l',fmt:(r)=>`<a href="https://analytics.google.com/analytics/web/#/p${r.property_id}/reports/intelligenthome" target="_blank" rel="noopener noreferrer">${esc(r.property||'?')}</a>${r.error?` <span class="err" title="${esc(r.error)}">⚠</span>`:''}`},
+ {k:'account',l:'Account',t:'l',cls:'acct',fmt:(r)=>r.account.includes('APPS')?'APPS':esc(r.account)},
+ {k:'d7.sessions',l:'7d sess',def:1},
+ {k:'d7.activeUsers',l:'7d users'},
+ {k:'d7.screenPageViews',l:'7d views'},
+ {k:'d7.conversions',l:'7d conv'},
+ {k:'d30.sessions',l:'30d sess',def:1},
+ {k:'d30.activeUsers',l:'30d users',def:1},
+ {k:'d30.screenPageViews',l:'30d views'},
+ {k:'d30.conversions',l:'30d conv'},
+];
+const esc=s=>String(s).replace(/[&<>"]/g,c=>({'&':'&','<':'<','>':'>','"':'"'}[c]));
+const dig=(o,k)=>k.split('.').reduce((a,x)=>a?.[x],o);
+const num=x=>{x=+x||0;return x?Math.round(x).toLocaleString():'<span class=z>0</span>'};
+let DATA={rows:[]}, sortK=localStorage.gaSortK||'d30.sessions', sortDir=+(localStorage.gaSortDir||-1);
+let hidden=new Set(JSON.parse(localStorage.gaHidden||'[]'));
+
+function renderCols(){
+ document.getElementById('cols').innerHTML='columns: '+COLS.map(c=>
+ `<label><input type=checkbox data-c="${c.k}" ${hidden.has(c.k)?'':'checked'}>${c.l}</label>`).join(' ');
+ document.querySelectorAll('#cols input').forEach(cb=>cb.onchange=()=>{
+ cb.checked?hidden.delete(cb.dataset.c):hidden.add(cb.dataset.c);
+ localStorage.gaHidden=JSON.stringify([...hidden]);render();});
+}
+function vis(){return COLS.filter(c=>!hidden.has(c.k))}
+function render(){
+ const q=document.getElementById('q').value.toLowerCase();
+ const onlyA=document.getElementById('onlyactive').checked;
+ let rows=DATA.rows.filter(r=>(r.property||'').toLowerCase().includes(q));
+ if(onlyA)rows=rows.filter(r=>dig(r,'d30.sessions')>0);
+ rows.sort((a,b)=>{const x=dig(a,sortK),y=dig(b,sortK);
+ if(typeof x==='string')return sortDir*String(x).localeCompare(String(y));
+ return sortDir*((+x||0)-(+y||0));});
+ // header
+ document.getElementById('hr').innerHTML=vis().map(c=>
+ `<th class="${c.t==='l'?'l':''}" data-k="${c.k}">${c.l}${sortK===c.k?(sortDir<0?' ▼':' ▲'):''}</th>`).join('');
+ document.querySelectorAll('#hr th').forEach(th=>th.onclick=()=>{
+ const k=th.dataset.k; if(sortK===k)sortDir*=-1; else{sortK=k;sortDir=k.startsWith('property')||k.startsWith('account')?1:-1;}
+ localStorage.gaSortK=sortK;localStorage.gaSortDir=sortDir;render();});
+ // body
+ document.getElementById('tb').innerHTML=rows.map(r=>'<tr>'+vis().map(c=>{
+ const v=c.fmt?c.fmt(r):num(dig(r,c.k));
+ return `<td class="${c.t==='l'?'l':''} ${c.cls||''}">${v}</td>`;}).join('')+'</tr>').join('');
+ // totals
+ const tot=k=>rows.reduce((s,r)=>s+(+dig(r,k)||0),0);
+ document.getElementById('tf').innerHTML=vis().map((c,i)=>{
+ if(i===0)return `<td class="l">Σ ${rows.length} sites</td>`;
+ if(c.t==='l')return '<td class="l"></td>';
+ return `<td>${num(tot(c.k))}</td>`;}).join('');
+}
+function fmtAge(iso){const s=(Date.now()-new Date(iso))/1000;
+ if(s<90)return 'just now';if(s<3600)return Math.round(s/60)+'m ago';return Math.round(s/3600)+'h ago';}
+async function load(){
+ const r=await fetch('/api/data');DATA=await r.json();
+ const active=DATA.rows.filter(x=>dig(x,'d30.sessions')>0).length;
+ const t7=DATA.rows.reduce((s,x)=>s+dig(x,'d7.sessions'),0);
+ const t30=DATA.rows.reduce((s,x)=>s+dig(x,'d30.sessions'),0);
+ document.getElementById('hstat').textContent=`${DATA.property_count} sites · ${active} with traffic · ${Math.round(t7).toLocaleString()} sess/7d · ${Math.round(t30).toLocaleString()} sess/30d`;
+ const age=(Date.now()-new Date(DATA.generated_at))/60000;
+ const b=document.getElementById('hbadge');
+ b.innerHTML=`<span class="badge ${age>45?'stale':'fresh'}">${age>45?'STALE':'fresh'} · ${fmtAge(DATA.generated_at)}</span>`;
+ document.getElementById('hgen').textContent='cache built '+DATA.generated_at.slice(0,16).replace('T',' ')+'Z ('+DATA.elapsed_s+'s)';
+ render();
+}
+document.getElementById('q').oninput=render;
+document.getElementById('onlyactive').onchange=render;
+document.getElementById('dens').oninput=e=>{document.documentElement.style.setProperty('--fs',e.target.value+'px');
+ document.documentElement.style.setProperty('--pad',Math.max(3,e.target.value-7)+'px');localStorage.gaDens=e.target.value;};
+if(localStorage.gaDens){document.getElementById('dens').value=localStorage.gaDens;document.getElementById('dens').oninput({target:{value:localStorage.gaDens}});}
+document.getElementById('refresh').onclick=async()=>{
+ const m=document.getElementById('rmsg');m.textContent='refreshing… (~70s, pulls all sites)';
+ await fetch('/api/refresh',{method:'POST'});
+ let tries=0;const iv=setInterval(async()=>{tries++;const r=await fetch('/api/status');const j=await r.json();
+ if(!j.refreshing){clearInterval(iv);m.textContent='updated ✓';load();setTimeout(()=>m.textContent='',3000);}
+ else m.textContent=`refreshing… ${tries*3}s`;},3000);
+};
+renderCols();load();
+</script></body></html>"""
+
+
+class H(BaseHTTPRequestHandler):
+ def _auth(self) -> bool:
+ h = self.headers.get("Authorization", "")
+ if h.startswith("Basic "):
+ try:
+ u, p = base64.b64decode(h[6:]).decode().split(":", 1)
+ if u == USER and p == PW:
+ return True
+ except Exception:
+ pass
+ self.send_response(401)
+ self.send_header("WWW-Authenticate", 'Basic realm="GA All Sites"')
+ self.end_headers()
+ return False
+
+ def log_message(self, *a):
+ pass
+
+ def do_GET(self):
+ if not self._auth():
+ return
+ if self.path == "/" or self.path.startswith("/index"):
+ self._send(200, "text/html", PAGE.encode())
+ elif self.path == "/api/data":
+ body = CACHE.read_bytes() if CACHE.exists() else b'{"rows":[],"property_count":0,"generated_at":"1970-01-01T00:00:00","elapsed_s":0}'
+ self._send(200, "application/json", body)
+ elif self.path == "/api/status":
+ self._send(200, "application/json",
+ json.dumps({"refreshing": _refreshing["on"]}).encode())
+ else:
+ self._send(404, "text/plain", b"not found")
+
+ def do_POST(self):
+ if not self._auth():
+ return
+ if self.path == "/api/refresh":
+ threading.Thread(target=run_etl, daemon=True).start()
+ self._send(202, "application/json", b'{"started":true}')
+ else:
+ self._send(404, "text/plain", b"not found")
+
+ def _send(self, code, ctype, body):
+ self.send_response(code)
+ self.send_header("Content-Type", ctype)
+ self.send_header("Content-Length", str(len(body)))
+ self.end_headers()
+ self.wfile.write(body)
+
+
+if __name__ == "__main__":
+ print(f"GA All-Sites dashboard → http://127.0.0.1:{PORT} (admin/DW2024!)")
+ ThreadingHTTPServer(("127.0.0.1", PORT), H).serve_forever()
(oldest)
·
back to Ga Allsites
·
Inject boot data server-side for instant first paint + crede e780b00 →