Add per-site Top Country column + time-on-site (avg session duration, 7d/30d); footer skips averaged cols

cc4417abc28b15532b37827f386add08c5f203b1 · 2026-08-04 14:21:25 -0700 · Steve Abrams
Files touched

M etl.py
M server.py
Diff

commit cc4417abc28b15532b37827f386add08c5f203b1
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Aug 4 14:21:25 2026 -0700

    Add per-site Top Country column + time-on-site (avg session duration, 7d/30d); footer skips averaged cols
---
 etl.py    | 29 +++++++++++++++++++----------
 server.py |  6 +++++-
 2 files changed, 24 insertions(+), 11 deletions(-)

diff --git a/etl.py b/etl.py
index 76b1f94..908d543 100644
--- a/etl.py
+++ b/etl.py
@@ -20,7 +20,8 @@ PROP_IDS_JSON = Path.home() / ".config" / "ga-analytics-agent" / "property-ids.j
 CACHE = Path(__file__).parent / "cache" / "data.json"
 WORKERS = 8
 
-METRICS = ["sessions", "activeUsers", "screenPageViews", "conversions"]
+METRICS = ["sessions", "activeUsers", "screenPageViews", "conversions",
+           "averageSessionDuration"]
 
 
 def gid_map() -> dict[str, str]:
@@ -112,14 +113,10 @@ def main() -> int:
         "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")
-
-    # ---- Country pass: fleet-wide user geography (30d) ----
+    # ---- Country pass: fleet geography + per-site top country (30d) ----
     from google.analytics.data_v1beta.types import Dimension
     countries: dict[str, dict] = {}
+    prop_top: dict[str, str] = {}
 
     def pull_country(p: dict):
         try:
@@ -135,16 +132,28 @@ def main() -> int:
                 c = row.dimension_values[0].value or "(not set)"
                 out.append((c, float(row.metric_values[0].value or 0),
                             float(row.metric_values[1].value or 0)))
-            return out
+            return p["property_id"], out
         except Exception:
-            return []
+            return p["property_id"], []
 
     with ThreadPoolExecutor(max_workers=WORKERS) as ex:
-        for res in ex.map(pull_country, props):
+        for pid, res in ex.map(pull_country, props):
             for c, s, u in res:
                 d = countries.setdefault(c, {"sessions": 0, "users": 0})
                 d["sessions"] += s
                 d["users"] += u
+            if res:
+                prop_top[pid] = max(res, key=lambda x: x[1])[0]
+
+    for r in rows:
+        r["top_country"] = prop_top.get(r["property_id"], "")
+
+    # write main data.json (now carries top_country + averageSessionDuration)
+    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")
+
     crows = [{"country": c, "sessions": v["sessions"], "users": v["users"]}
              for c, v in countries.items()]
     crows.sort(key=lambda r: r["sessions"], reverse=True)
diff --git a/server.py b/server.py
index 943c51e..e4d5b66 100644
--- a/server.py
+++ b/server.py
@@ -201,17 +201,21 @@ PAGE = r"""<!DOCTYPE html><html><head><meta charset="utf-8">
 <script>window.__BOOT__=/*BOOTDATA*/null/*/BOOTDATA*/;</script>
 <script>
 const PAL=['#5b8def','#e74c3c','#27ae60','#f39c12','#9b59b6','#16a085','#e67e22','#2c3e50','#c0392b','#7f8c8d'];
+const fmtDur=s=>{s=Math.round(+s||0);return s?`${Math.floor(s/60)}:${String(s%60).padStart(2,'0')}`:'<span class=z>0:00</span>';};
 const COLS=[
  {k:'property',l:'Site',t:'l',def:1,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',def:1,cls:'acct',fmt:r=>r.account.includes('APPS')?'APPS':esc(r.account)},
+ {k:'top_country',l:'Top country',t:'l',def:1,cls:'acct',fmt:r=>esc(r.top_country||'—')},
  {k:'d7.sessions',l:'7d sess',def:1},
  {k:'d7.activeUsers',l:'7d users',def:0},
  {k:'d7.screenPageViews',l:'7d views',def:0},
  {k:'d7.conversions',l:'7d conv',def:0},
+ {k:'d7.averageSessionDuration',l:'7d time',def:0,notot:1,fmt:r=>fmtDur(dig(r,'d7.averageSessionDuration'))},
  {k:'d30.sessions',l:'30d sess',def:1},
  {k:'d30.activeUsers',l:'30d users',def:1},
  {k:'d30.screenPageViews',l:'30d views',def:0},
  {k:'d30.conversions',l:'30d conv',def:0},
+ {k:'d30.averageSessionDuration',l:'30d time',def:1,notot:1,fmt:r=>fmtDur(dig(r,'d30.averageSessionDuration'))},
 ];
 const esc=s=>String(s).replace(/[&<>"]/g,c=>({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;'}[c]));
 const dig=(o,k)=>k.split('.').reduce((a,x)=>a?.[x],o);
@@ -248,7 +252,7 @@ function renderTable(){
  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('');
  const tot=k=>rows.reduce((s,r)=>s+(+dig(r,k)||0),0);
- document.getElementById('tf').innerHTML=vis().map((c,i)=>i===0?`<td class="l">Σ ${rows.length} sites</td>`:(c.t==='l'?'<td class="l"></td>':`<td>${num(tot(c.k))}</td>`)).join('');
+ document.getElementById('tf').innerHTML=vis().map((c,i)=>i===0?`<td class="l">Σ ${rows.length} sites</td>`:(c.t==='l'?'<td class="l"></td>':c.notot?'<td></td>':`<td>${num(tot(c.k))}</td>`)).join('');
 }
 
 /* ---- VISUALS ---- */
← 265e278 Housekeeping: drop server.py.bak, gitignore *.bak + preview · back to Ga Allsites · Add world Map tab (zero-dep SVG choropleth by sessions) + we a26228a →