← back to Ga Allsites
auto-save: 2026-08-06T07:49:47 (2 files) — etl.py server.py
a05a60f92434fb1dbff247c95582d07e30ba15cb · 2026-08-06 07:49:49 -0700 · Steve Abrams
Files touched
Diff
commit a05a60f92434fb1dbff247c95582d07e30ba15cb
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Aug 6 07:49:49 2026 -0700
auto-save: 2026-08-06T07:49:47 (2 files) — etl.py server.py
---
etl.py | 165 +++++++++++++++++++++++++++++++++++++++++++------
server.py | 207 ++++++++++++++++++++++++++++++++++++++++++++------------------
2 files changed, 297 insertions(+), 75 deletions(-)
diff --git a/etl.py b/etl.py
index b45833f..e5d8706 100644
--- a/etl.py
+++ b/etl.py
@@ -1,8 +1,12 @@
#!/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).
+"""ETL: pull 1d + 7d + 30d + 365d 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).
+
+Also writes cache/keywords.json — Search Console query stats per window (only
+populates once the SA is granted access in GSC) + GA4 channel-group stats
+(Organic Search etc.) which work with GA4 access alone.
Auth: reuses the analytics skill's service account key.
"""
@@ -22,6 +26,16 @@ WORKERS = 8
METRICS = ["sessions", "activeUsers", "screenPageViews", "conversions",
"averageSessionDuration"]
+# GA4 allows max 4 date ranges per request — exactly our 4 windows, so the
+# day/week/month/year pull costs the same one request per property as before.
+WINDOWS = {"d1": "yesterday", "d7": "7daysAgo", "d30": "30daysAgo", "d365": "365daysAgo"}
+RANGE_INDEX = {f"date_range_{i}": k for i, k in enumerate(WINDOWS)}
+
+
+def _win_of(tag: str) -> str:
+ if tag in WINDOWS:
+ return tag
+ return RANGE_INDEX.get(tag, "d30")
def gid_map() -> dict[str, str]:
@@ -61,27 +75,28 @@ def main() -> int:
"property_id": pid,
"gid": gids.get(pid, ""),
})
- print(f"Enumerated {len(props)} properties. Pulling 7d + 30d ...")
+ print(f"Enumerated {len(props)} properties. Pulling 1d + 7d + 30d + 365d ...")
data = BetaAnalyticsDataClient()
+ ranges = [DateRange(start_date=s, end_date="yesterday", name=k)
+ for k, s in WINDOWS.items()]
+
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"),
- ],
+ date_ranges=ranges,
metrics=[Metric(name=m) for m in METRICS],
)
+ empty = {w: {m: 0 for m in METRICS} for w in WINDOWS}
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}}
+ # With N date ranges + no dimensions, GA returns one row per range,
+ # tagged by a trailing 'dateRange' dimension value (the range name).
+ buckets = {w: {m: 0 for m in METRICS} for w in WINDOWS}
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"
+ tag = row.dimension_values[-1].value if row.dimension_values else "d30"
+ key = _win_of(tag)
for i, m in enumerate(METRICS):
try:
buckets[key][m] = float(row.metric_values[i].value or 0)
@@ -89,8 +104,7 @@ def main() -> int:
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]}
+ return {**p, **empty, "error": str(e)[:120]}
rows: list[dict] = []
t0 = time.time()
@@ -170,6 +184,123 @@ def main() -> int:
}, indent=2))
print(f"Wrote {cpath} · {len(crows)} countries")
+ # ---- Keywords pass: GA4 channel groups (works now) + GSC queries ----
+ # Channels: one extra request per property, all 4 windows tagged in one call.
+ channels: dict[str, dict[str, float]] = {w: {} for w in WINDOWS}
+ organic_sites: dict[str, dict[str, float]] = {w: {} for w in WINDOWS}
+
+ def pull_channels(p: dict):
+ try:
+ resp = data.run_report(RunReportRequest(
+ property=f"properties/{p['property_id']}",
+ date_ranges=ranges,
+ dimensions=[Dimension(name="sessionDefaultChannelGroup")],
+ metrics=[Metric(name="sessions")],
+ limit=100,
+ ))
+ out = []
+ for row in resp.rows:
+ ch = row.dimension_values[0].value or "(other)"
+ win = _win_of(row.dimension_values[-1].value or "d30")
+ out.append((win, ch, float(row.metric_values[0].value or 0)))
+ return p, out
+ except Exception:
+ return p, []
+
+ with ThreadPoolExecutor(max_workers=WORKERS) as ex:
+ for p, res in ex.map(pull_channels, props):
+ for win, ch, s in res:
+ channels[win][ch] = channels[win].get(ch, 0) + s
+ if ch == "Organic Search" and s > 0:
+ organic_sites[win][p["property"]] = \
+ organic_sites[win].get(p["property"], 0) + s
+ org_top = {w: sorted(({"property": k, "sessions": v} for k, v in d.items()),
+ key=lambda r: r["sessions"], reverse=True)[:25]
+ for w, d in organic_sites.items()}
+
+ # GSC: real organic queries. Needs the SA added as a user in Search Console;
+ # until then sites.list returns empty and the dashboard shows the how-to.
+ gsc_sites: list[dict] = []
+ gsc_windows: dict[str, dict] = {w: {"queries": [], "totals": {}} for w in WINDOWS}
+ sa_email = ""
+ try:
+ import urllib.parse
+ import urllib.request
+ from datetime import date, timedelta
+
+ import google.auth.transport.requests as _gareq
+ from google.oauth2 import service_account as _sa
+ sa_email = json.loads(KEY_PATH.read_text()).get("client_email", "")
+ creds = _sa.Credentials.from_service_account_file(
+ str(KEY_PATH),
+ scopes=["https://www.googleapis.com/auth/webmasters.readonly"])
+ creds.refresh(_gareq.Request())
+ hdrs = {"Authorization": f"Bearer {creds.token}",
+ "Content-Type": "application/json"}
+
+ def gsc(url, payload=None):
+ req = urllib.request.Request(
+ url, headers=hdrs,
+ data=json.dumps(payload).encode() if payload else None,
+ method="POST" if payload else "GET")
+ with urllib.request.urlopen(req, timeout=30) as r:
+ return json.loads(r.read().decode() or "{}")
+
+ base = "https://searchconsole.googleapis.com/webmasters/v3"
+ gsc_sites = [{"url": s["siteUrl"], "permission": s["permissionLevel"]}
+ for s in gsc(f"{base}/sites").get("siteEntry", [])
+ if s.get("permissionLevel") != "siteUnverifiedUser"]
+
+ # GSC data lags ~2 days; end every window at today-2 so numbers are final.
+ end = date.today() - timedelta(days=2)
+ spans = {"d1": 0, "d7": 6, "d30": 29, "d365": 364}
+ for w, back in spans.items():
+ agg: dict[str, dict] = {}
+ for site in gsc_sites:
+ try:
+ q = urllib.parse.quote(site["url"], safe="")
+ resp = gsc(f"{base}/sites/{q}/searchAnalytics/query", {
+ "startDate": str(end - timedelta(days=back)),
+ "endDate": str(end),
+ "dimensions": ["query"], "rowLimit": 250,
+ })
+ for row in resp.get("rows", []):
+ k = row["keys"][0]
+ d = agg.setdefault(k, {"query": k, "clicks": 0,
+ "impressions": 0, "pos_w": 0.0})
+ d["clicks"] += row.get("clicks", 0)
+ d["impressions"] += row.get("impressions", 0)
+ d["pos_w"] += row.get("position", 0) * row.get("impressions", 0)
+ except Exception:
+ continue
+ qrows = []
+ for d in agg.values():
+ impr = d["impressions"] or 1
+ qrows.append({"query": d["query"], "clicks": round(d["clicks"]),
+ "impressions": round(d["impressions"]),
+ "ctr": round(100 * d["clicks"] / impr, 1),
+ "position": round(d["pos_w"] / impr, 1)})
+ qrows.sort(key=lambda r: (r["clicks"], r["impressions"]), reverse=True)
+ gsc_windows[w] = {"queries": qrows[:300], "totals": {
+ "clicks": sum(r["clicks"] for r in qrows),
+ "impressions": sum(r["impressions"] for r in qrows),
+ "query_count": len(qrows)}}
+ except Exception as e:
+ print(f"GSC pass skipped: {e}")
+
+ kpath = CACHE.parent / "keywords.json"
+ kpath.write_text(json.dumps({
+ "generated_at": datetime.now(timezone.utc).isoformat(),
+ "sa_email": sa_email,
+ "gsc_site_count": len(gsc_sites),
+ "gsc_sites": gsc_sites,
+ "windows": gsc_windows,
+ "channels": channels,
+ "organic_sites": org_top,
+ }, indent=2))
+ print(f"Wrote {kpath} · {len(gsc_sites)} GSC sites · "
+ f"{sum(len(v['queries']) for v in gsc_windows.values())} query rows")
+
# Freshness push: if the flag file exists, rsync the fresh caches to the public
# Kamatera instance (analytics.agentabrams.com). The server reads cache files per
# request, so no restart is needed. Flag-gated so local dev runs never push.
@@ -177,7 +308,7 @@ def main() -> int:
import subprocess
try:
r = subprocess.run(
- ["rsync", "-az", str(CACHE), str(cpath),
+ ["rsync", "-az", str(CACHE), str(cpath), str(kpath),
"root@45.61.58.125:/root/Projects/ga-allsites/cache/"],
capture_output=True, timeout=90)
print("Pushed cache → Kamatera" if r.returncode == 0
diff --git a/server.py b/server.py
index f497f61..2c170f0 100644
--- a/server.py
+++ b/server.py
@@ -21,6 +21,7 @@ from pathlib import Path
HERE = Path(__file__).parent
CACHE = HERE / "cache" / "data.json"
COUNTRIES = HERE / "cache" / "countries.json"
+KEYWORDS = HERE / "cache" / "keywords.json"
KEY = Path.home() / ".config" / "ga-analytics-agent" / "service-account.json"
PORT = int((HERE / ".port").read_text().strip()) if (HERE / ".port").exists() else 9780
USER, PW = "admin", "DW2024!"
@@ -101,6 +102,10 @@ PAGE = r"""<!DOCTYPE html><html><head><meta charset="utf-8">
header h1{font-size:15px;margin:0;font-weight:600}
header .stat{font-variant-numeric:tabular-nums;color:#cfe;font-size:13px}
header .muted{color:#9aa}
+ .winsel{display:inline-flex;border:1px solid #3a3f4a;border-radius:8px;overflow:hidden}
+ .winsel button{background:#1a1d24;color:#aeb6c2;border:0;padding:5px 13px;font-size:12px;cursor:pointer}
+ .winsel button:hover{color:#fff}
+ .winsel button.on{background:#e74c3c;color:#fff;font-weight:700}
.bar{display:flex;flex-wrap:wrap;gap:10px;align-items:center;padding:10px 18px;background:#fff;border-bottom:1px solid #e6e8ec}
.bar input[type=search]{padding:6px 10px;border:1px solid #cbd0d6;border-radius:6px;min-width:200px;font-size:13px}
.bar label{font-size:12px;color:#555;display:inline-flex;gap:4px;align-items:center;cursor:pointer}
@@ -171,6 +176,7 @@ PAGE = r"""<!DOCTYPE html><html><head><meta charset="utf-8">
<button data-tab="sites" class="on"><span class="ic">📊</span>Sites</button>
<button data-tab="global"><span class="ic">🌊</span>Global</button>
<button data-tab="charts"><span class="ic">📈</span>Charts</button>
+ <button data-tab="keywords"><span class="ic">🔎</span>Keywords</button>
<button data-tab="visuals"><span class="ic">🔺</span>Visuals</button>
<button data-tab="countries"><span class="ic">🌍</span>Country</button>
<button data-tab="map"><span class="ic">🗺️</span>Map</button>
@@ -179,6 +185,7 @@ PAGE = r"""<!DOCTYPE html><html><head><meta charset="utf-8">
<div class="main">
<header>
<h1 id="htitle">All Sites</h1>
+ <span class="winsel" id="winsel" title="Stats window — applies to Sites, Global, Charts, Visuals and Keywords"></span>
<span class="stat" id="hstat">loading…</span>
<span id="hbadge"></span>
<label style="margin-left:auto;color:#bcd;font-size:12px;display:inline-flex;gap:5px;align-items:center;cursor:pointer" title="Hide the main DW store (bot-inflated) so the sister-site fleet is visible"><input type="checkbox" id="exstore"> exclude main store</label>
@@ -209,14 +216,26 @@ PAGE = r"""<!DOCTYPE html><html><head><meta charset="utf-8">
<!-- CHARTS overview -->
<div class="view panel" id="v-charts" hidden>
<div class="kpis" id="kpis"></div>
- <div class="card"><h2>Top domains by sessions — 30 days</h2><div id="domainbars"></div></div>
+ <div class="card"><h2 id="dbtitle">Top domains by sessions</h2><div id="domainbars"></div></div>
<div class="card"><h2>Sessions by country — 30 days</h2><div class="piewrap"><div class="pie" id="cpie"></div><ul class="legend" id="cpielegend"></ul></div></div>
</div>
+ <!-- KEYWORDS -->
+ <div class="view panel" id="v-keywords" hidden>
+ <div class="kpis" id="kwkpis"></div>
+ <div class="card" id="kwsetup" hidden></div>
+ <div class="card"><h2 id="kwqtitle">Top search queries</h2>
+ <table><thead><tr><th class="l">Query</th><th>Clicks</th><th>Impressions</th><th>CTR</th><th>Avg pos</th></tr></thead>
+ <tbody id="kwtb"></tbody></table></div>
+ <div class="card"><h2 id="kwchtitle">Traffic by channel</h2>
+ <div class="piewrap"><div class="pie" id="kwpie"></div><ul class="legend" id="kwpielegend"></ul></div></div>
+ <div class="card"><h2 id="kworgtitle">Top sites by Organic Search sessions</h2><div id="kworg"></div></div>
+ </div>
+
<!-- VISUALS -->
<div class="view panel" id="v-visuals" hidden>
- <div class="card"><h2>Fleet mind map — top sites by 30-day sessions</h2><div id="mindmap"></div></div>
- <div class="card"><h2>Traffic pyramid — top 10 sites (30d sessions)</h2><div class="pyr" id="pyr"></div></div>
+ <div class="card"><h2 id="mmtitle">Fleet mind map — top sites by sessions</h2><div id="mindmap"></div></div>
+ <div class="card"><h2 id="pyrtitle">Traffic pyramid — top 10 sites</h2><div class="pyr" id="pyr"></div></div>
</div>
<!-- COUNTRIES -->
@@ -255,31 +274,40 @@ PAGE = r"""<!DOCTYPE html><html><head><meta charset="utf-8">
const PAL=['#5b8def','#e74c3c','#27ae60','#f39c12','#9b59b6','#16a085','#e67e22','#2c3e50','#c0392b','#7f8c8d'];
const fmtDurP=s=>{s=Math.round(+s||0);return `${Math.floor(s/60)}:${String(s%60).padStart(2,'0')}`;};
const fmtDur=s=>{s=Math.round(+s||0);return s?fmtDurP(s):'<span class=z>0:00</span>';};
+/* Time window: Day / Week / Month / Year → d1 / d7 / d30 / d365 buckets.
+ Metric columns use 'm.<metric>' keys resolved against the selected window,
+ so one column set serves all four windows. */
+const WINS=[['d1','Day'],['d7','Week'],['d30','Month'],['d365','Year']];
+const WLBL=k=>(WINS.find(w=>w[0]===k)||['','?'])[1];
+let WIN=localStorage.gaWin||'d30';if(!WINS.some(w=>w[0]===WIN))WIN='d30';
+const esc=s=>String(s).replace(/[&<>"]/g,c=>({'&':'&','<':'<','>':'>','"':'"'}[c]));
+const dig=(o,k)=>k.split('.').reduce((a,x)=>a?.[x],o);
+const val=(r,k)=>k.startsWith('m.')?dig(r,WIN+'.'+k.slice(2)):dig(r,k);
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'))},
+ {k:'m.sessions',l:'Sessions',def:1},
+ {k:'m.activeUsers',l:'Users',def:1},
+ {k:'m.screenPageViews',l:'Views',def:1},
+ {k:'m.conversions',l:'Conv',def:0},
+ {k:'m.averageSessionDuration',l:'Time',def:1,notot:1,fmt:r=>fmtDur(val(r,'m.averageSessionDuration'))},
];
-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:[]},CDATA=null;
+let DATA={rows:[]},CDATA=null,KDATA=null;
const STORE_ID='294178030';
let exStore=localStorage.gaExStore==='1';
function activeRows(){return exStore?DATA.rows.filter(r=>r.property_id!==STORE_ID):DATA.rows;}
-let sortK=localStorage.gaSortK||'d30.sessions',sortDir=+(localStorage.gaSortDir||-1);
+let sortK=localStorage.gaSortK2||'m.sessions',sortDir=+(localStorage.gaSortDir||-1);
// field selector: default from COLS[].def, then user override persists
-let hidden=new Set(JSON.parse(localStorage.gaHidden||'null')||COLS.filter(c=>!c.def).map(c=>c.k));
+let hidden=new Set(JSON.parse(localStorage.gaHidden2||'null')||COLS.filter(c=>!c.def).map(c=>c.k));
+function renderWinSel(){
+ document.getElementById('winsel').innerHTML=WINS.map(([k,l])=>`<button data-w="${k}" class="${k===WIN?'on':''}">${l}</button>`).join('');
+ document.querySelectorAll('#winsel button').forEach(b=>b.onclick=()=>{
+ WIN=b.dataset.w;localStorage.gaWin=WIN;renderWinSel();header();
+ const cur=document.querySelector('.side button.on').dataset.tab;
+ ({sites:renderTable,global:renderGlobal,charts:renderCharts,visuals:renderVisuals,keywords:renderKeywords}[cur]||renderTable)();});
+}
/* ---- field selector (persists on load) ---- */
function renderCols(){
@@ -298,16 +326,17 @@ function renderTable(){
const q=document.getElementById('q').value.toLowerCase();
const onlyA=document.getElementById('onlyactive').checked;
let rows=activeRows().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(onlyA)rows=rows.filter(r=>val(r,'m.sessions')>0);
+ rows.sort((a,b)=>{const x=val(a,sortK),y=val(b,sortK);
return typeof x==='string'?sortDir*String(x).localeCompare(String(y)):sortDir*((+x||0)-(+y||0));});
- 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('');
+ const wl=WLBL(WIN);
+ document.getElementById('hr').innerHTML=vis().map(c=>`<th class="${c.t==='l'?'l':''}" data-k="${c.k}">${c.k.startsWith('m.')?wl+' '+c.l: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==='property'||k==='account')?1:-1;}
- localStorage.gaSortK=sortK;localStorage.gaSortDir=sortDir;renderTable();});
+ localStorage.gaSortK2=sortK;localStorage.gaSortDir=sortDir;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);
+ const v=c.fmt?c.fmt(r):num(val(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+(+val(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>':c.notot?'<td></td>':`<td>${num(tot(c.k))}</td>`)).join('');
}
@@ -332,37 +361,41 @@ function liquidOrb(level,center,label,sub,color,i){
}
async function renderGlobal(){
await ensureCountries();
- const rows=activeRows(),total=rows.length,active=rows.filter(r=>dig(r,'d30.sessions')>0).length;
- const t7=rows.reduce((s,r)=>s+dig(r,'d7.sessions'),0),t30=rows.reduce((s,r)=>s+dig(r,'d30.sessions'),0);
- const pv=rows.reduce((s,r)=>s+dig(r,'d30.screenPageViews'),0);
- const wsum=rows.reduce((s,r)=>s+(dig(r,'d30.averageSessionDuration')||0)*(dig(r,'d30.sessions')||0),0),wavg=t30?wsum/t30:0;
+ const wl=WLBL(WIN);
+ const rows=activeRows(),total=rows.length,active=rows.filter(r=>val(r,'m.sessions')>0).length;
+ const tw=rows.reduce((s,r)=>s+(val(r,'m.sessions')||0),0),ty=rows.reduce((s,r)=>s+(dig(r,'d365.sessions')||0),0);
+ const pv=rows.reduce((s,r)=>s+(val(r,'m.screenPageViews')||0),0);
+ const wsum=rows.reduce((s,r)=>s+(val(r,'m.averageSessionDuration')||0)*(val(r,'m.sessions')||0),0),wavg=tw?wsum/tw:0;
const topC=((CDATA&&CDATA.rows)||[])[0]||{country:'—',share:0};
document.getElementById('orbs').innerHTML=[
- liquidOrb(active/Math.max(1,total),active,'Sites with traffic','of '+total+' total','#27ae60',0),
- liquidOrb((topC.share||0)/100,(topC.share||0)+'%','Top country',topC.country,'#5b8def',1),
- liquidOrb(t30?t7/t30:0,Math.round(t7).toLocaleString(),'7-day share','of 30d ('+Math.round(t30).toLocaleString()+')','#8a5bef',2),
- liquidOrb(Math.min(1,wavg/90),fmtDurP(wavg),'Avg time on site','vs 1:30 benchmark','#e67e22',3),
+ liquidOrb(active/Math.max(1,total),active,'Sites with traffic','of '+total+' total · '+wl.toLowerCase(),'#27ae60',0),
+ liquidOrb((topC.share||0)/100,(topC.share||0)+'%','Top country',topC.country+' · 30d','#5b8def',1),
+ liquidOrb(ty?tw/ty:0,Math.round(tw).toLocaleString(),wl+' sessions','of year ('+Math.round(ty).toLocaleString()+')','#8a5bef',2),
+ liquidOrb(Math.min(1,wavg/90),fmtDurP(wavg),'Avg time on site',wl.toLowerCase()+' · vs 1:30 benchmark','#e67e22',3),
].join('');
- const kpi=[['Domains',total],['Sessions · 7d',Math.round(t7).toLocaleString()],['Sessions · 30d',Math.round(t30).toLocaleString()],['Pageviews · 30d',Math.round(pv).toLocaleString()],['Top country',topC.country]];
+ const kpi=[['Domains',total],['Sessions · '+wl,Math.round(tw).toLocaleString()],['Sessions · Year',Math.round(ty).toLocaleString()],['Pageviews · '+wl,Math.round(pv).toLocaleString()],['Top country',topC.country]];
document.getElementById('gkpis').innerHTML=kpi.map((k,i)=>`<div class="kpi"><div class="kv" style="color:${PAL[i%PAL.length]}">${k[1]}</div><div class="kl">${k[0]}</div></div>`).join('');
}
/* ---- VISUALS ---- */
function renderVisuals(){
- const top=DATA.rows.filter(r=>dig(r,'d30.sessions')>0).slice(0,10);
- const max=top.length?dig(top[0],'d30.sessions'):1;
+ const sorted=[...DATA.rows].sort((a,b)=>(val(b,'m.sessions')||0)-(val(a,'m.sessions')||0));
+ const top=sorted.filter(r=>val(r,'m.sessions')>0).slice(0,10);
+ const max=top.length?val(top[0],'m.sessions'):1;
+ document.getElementById('pyrtitle').textContent=`Traffic pyramid — top 10 sites (${WLBL(WIN)} sessions)`;
+ document.getElementById('mmtitle').textContent=`Fleet mind map — top sites by ${WLBL(WIN)} sessions`;
document.getElementById('pyr').innerHTML=top.map(r=>{
- const s=dig(r,'d30.sessions');const w=Math.max(6,Math.round(100*s/max));
+ const s=val(r,'m.sessions');const w=Math.max(6,Math.round(100*s/max));
return `<div class="row"><span class="lbl">${esc(r.property)}</span><span class="bar" style="width:${w}%">${Math.round(s)}</span><span class="val"></span></div>`;}).join('')||'<div class="z">no traffic yet</div>';
// mind map (SVG radial): center → top 8 sites
- const nodes=DATA.rows.filter(r=>dig(r,'d30.sessions')>0).slice(0,8);
+ const nodes=sorted.filter(r=>val(r,'m.sessions')>0).slice(0,8);
const W=820,H=460,cx=W/2,cy=H/2,R=170;
- const t30=DATA.rows.reduce((s,r)=>s+dig(r,'d30.sessions'),0);
+ const t30=DATA.rows.reduce((s,r)=>s+(val(r,'m.sessions')||0),0);
let svg=`<svg viewBox="0 0 ${W} ${H}" width="100%" style="max-height:480px">`;
nodes.forEach((n,i)=>{const a=-Math.PI/2+2*Math.PI*i/nodes.length;const x=cx+R*Math.cos(a),y=cy+R*Math.sin(a);
svg+=`<line x1="${cx}" y1="${cy}" x2="${x}" y2="${y}" stroke="#cdd4dd" stroke-width="2"/>`;});
nodes.forEach((n,i)=>{const a=-Math.PI/2+2*Math.PI*i/nodes.length;const x=cx+R*Math.cos(a),y=cy+R*Math.sin(a);
- const c=PAL[i%PAL.length];const lbl=(n.property||'').slice(0,16);const s=Math.round(dig(n,'d30.sessions'));
+ const c=PAL[i%PAL.length];const lbl=(n.property||'').slice(0,16);const s=Math.round(val(n,'m.sessions'));
svg+=`<g><rect x="${x-64}" y="${y-16}" width="128" height="32" rx="8" fill="#fff" stroke="${c}" stroke-width="2"/>`+
`<text x="${x}" y="${y-1}" text-anchor="middle" fill="#1a1a1a">${esc(lbl)}</text>`+
`<text x="${x}" y="${y+12}" text-anchor="middle" fill="${c}" font-weight="700">${s} sess</text></g>`;});
@@ -393,22 +426,73 @@ async function renderCountries(){
/* ---- CHARTS overview (all domains on one page) ---- */
async function renderCharts(){
await ensureCountries();
+ const wl=WLBL(WIN);
const rows=activeRows();
- const active=rows.filter(r=>dig(r,'d30.sessions')>0).length;
- const t7=rows.reduce((s,r)=>s+dig(r,'d7.sessions'),0),t30=rows.reduce((s,r)=>s+dig(r,'d30.sessions'),0);
- const pv=rows.reduce((s,r)=>s+dig(r,'d30.screenPageViews'),0);
- const wsum=rows.reduce((s,r)=>s+(dig(r,'d30.averageSessionDuration')||0)*(dig(r,'d30.sessions')||0),0);
- const wavg=t30?wsum/t30:0;
+ const active=rows.filter(r=>val(r,'m.sessions')>0).length;
+ const tw=rows.reduce((s,r)=>s+(val(r,'m.sessions')||0),0),ty=rows.reduce((s,r)=>s+(dig(r,'d365.sessions')||0),0);
+ const pv=rows.reduce((s,r)=>s+(val(r,'m.screenPageViews')||0),0);
+ const wsum=rows.reduce((s,r)=>s+(val(r,'m.averageSessionDuration')||0)*(val(r,'m.sessions')||0),0);
+ const wavg=tw?wsum/tw:0;
const topC=((CDATA&&CDATA.rows)||[])[0]||{country:'—',share:0};
- const kpi=[['Domains',rows.length],['Sessions · 7d',Math.round(t7).toLocaleString()],['Sessions · 30d',Math.round(t30).toLocaleString()],
- ['Pageviews · 30d',Math.round(pv).toLocaleString()],['Avg time on site',fmtDurP(wavg)],['Top country',topC.country+(topC.share?' · '+topC.share+'%':'')],['Sites w/ traffic',active+' / '+rows.length]];
+ const kpi=[['Domains',rows.length],['Sessions · '+wl,Math.round(tw).toLocaleString()],['Sessions · Year',Math.round(ty).toLocaleString()],
+ ['Pageviews · '+wl,Math.round(pv).toLocaleString()],['Avg time on site',fmtDurP(wavg)],['Top country',topC.country+(topC.share?' · '+topC.share+'%':'')],['Sites w/ traffic',active+' / '+rows.length]];
document.getElementById('kpis').innerHTML=kpi.map((k,i)=>`<div class="kpi"><div class="kv" style="color:${PAL[i%PAL.length]}">${k[1]}</div><div class="kl">${k[0]}</div></div>`).join('');
- const top=rows.filter(r=>dig(r,'d30.sessions')>0).slice(0,15),mx=Math.max(1,...top.map(r=>dig(r,'d30.sessions')));
- document.getElementById('domainbars').innerHTML=top.map(r=>{const v=Math.round(dig(r,'d30.sessions'));
+ document.getElementById('dbtitle').textContent=`Top domains by sessions — ${wl}`;
+ const sorted=[...rows].sort((a,b)=>(val(b,'m.sessions')||0)-(val(a,'m.sessions')||0));
+ const top=sorted.filter(r=>val(r,'m.sessions')>0).slice(0,15),mx=Math.max(1,...top.map(r=>val(r,'m.sessions')));
+ document.getElementById('domainbars').innerHTML=top.map(r=>{const v=Math.round(val(r,'m.sessions'));
return `<div class="lrow"><span class="nm"><a href="https://analytics.google.com/analytics/web/#/p${r.property_id}/reports/intelligenthome" target="_blank" rel="noopener noreferrer">${esc(r.property)}</a></span><div class="lbar" style="width:${Math.max(6,Math.round(100*v/mx))}%;background:linear-gradient(90deg,#5b8def,#8a5bef)">${v}</div></div>`;}).join('');
renderPie('cpie','cpielegend');
}
+/* ---- KEYWORDS (GSC queries + GA4 channels, per window) ---- */
+async function ensureKeywords(){if(KDATA)return;try{KDATA=await(await fetch(location.origin+'/api/keywords')).json();}catch(e){KDATA={gsc_site_count:0,windows:{},channels:{},organic_sites:{}};}}
+async function renderKeywords(){
+ await ensureKeywords();
+ const wl=WLBL(WIN);
+ const wd=(KDATA.windows||{})[WIN]||{queries:[],totals:{}};
+ const t=wd.totals||{};
+ const ch=(KDATA.channels||{})[WIN]||{};
+ const org=(KDATA.organic_sites||{})[WIN]||[];
+ const orgTot=Math.round(ch['Organic Search']||0);
+ const kpi=[['Search queries',(t.query_count||0).toLocaleString()],['Clicks · '+wl,(t.clicks||0).toLocaleString()],
+ ['Impressions · '+wl,(t.impressions||0).toLocaleString()],['Organic sessions · '+wl,orgTot.toLocaleString()],
+ ['GSC sites',KDATA.gsc_site_count||0]];
+ document.getElementById('kwkpis').innerHTML=kpi.map((k,i)=>`<div class="kpi"><div class="kv" style="color:${PAL[i%PAL.length]}">${k[1]}</div><div class="kl">${k[0]}</div></div>`).join('');
+ const setup=document.getElementById('kwsetup');
+ if(!(KDATA.gsc_site_count>0)){
+ setup.hidden=false;
+ setup.innerHTML=`<h2>Unlock real search keywords (one-time, ~2 min)</h2>
+ <p>Google only exposes organic search <b>queries</b> through Search Console — GA4 alone can't see them. The dashboard is fully wired for them; it just needs to be let in:</p>
+ <ol style="line-height:1.9">
+ <li>Open <a href="https://search.google.com/search-console" target="_blank" rel="noopener noreferrer">Search Console</a> with the Google account that owns your sites.</li>
+ <li>Pick a property → ⚙ Settings → <b>Users and permissions</b> → <b>Add user</b>.</li>
+ <li>Add <code>${esc(KDATA.sa_email||'the dashboard service account')}</code> (Restricted access is enough).</li>
+ <li>Repeat per property — designerwallcoverings.com first. Queries appear on the next auto-refresh (≤30 min).</li>
+ </ol>
+ <p class="acct">Meanwhile the channel + organic-sessions stats below are live from GA4 — real search-traffic volume, just without the query strings.</p>`;
+ } else setup.hidden=true;
+ document.getElementById('kwqtitle').textContent=`Top search queries — ${wl}`;
+ document.getElementById('kwtb').innerHTML=(wd.queries||[]).slice(0,200).map(r=>
+ `<tr><td class="l">${esc(r.query)}</td><td>${(+r.clicks).toLocaleString()}</td><td>${(+r.impressions).toLocaleString()}</td><td>${r.ctr}%</td><td>${r.position}</td></tr>`).join('')
+ ||`<tr><td class="l z" colspan="5" style="text-align:left;padding:14px">${KDATA.gsc_site_count>0?'no query data for this window yet':'no Search Console access yet — see the card above'}</td></tr>`;
+ document.getElementById('kwchtitle').textContent=`Traffic by channel — ${wl} (GA4, fleet-wide)`;
+ const segs=Object.entries(ch).sort((a,b)=>b[1]-a[1]);
+ const tot=segs.reduce((s,x)=>s+x[1],0)||1;
+ let acc=0;const col=(n,i)=>n==='Organic Search'?'#27ae60':PAL[i%PAL.length];
+ const stops=segs.map((s,i)=>{const a=100*acc/tot,b=100*(acc+s[1])/tot;acc+=s[1];return `${col(s[0],i)} ${a.toFixed(2)}% ${b.toFixed(2)}%`;}).join(',');
+ const pie=document.getElementById('kwpie');
+ pie.style.background=segs.length?`conic-gradient(${stops})`:'#eef1f4';
+ document.getElementById('kwpielegend').innerHTML=segs.map((s,i)=>
+ `<li><span class="sw" style="background:${col(s[0],i)}"></span>${esc(s[0])}<span class="v">${Math.round(s[1]).toLocaleString()} · ${(100*s[1]/tot).toFixed(1)}%</span></li>`).join('')
+ ||'<li class="z">channel data arrives on the next data refresh</li>';
+ document.getElementById('kworgtitle').textContent=`Top sites by Organic Search sessions — ${wl}`;
+ const mx=Math.max(1,...org.map(r=>r.sessions));
+ document.getElementById('kworg').innerHTML=org.map(r=>
+ `<div class="lrow"><span class="nm">${esc(r.property)}</span><div class="lbar" style="width:${Math.max(6,Math.round(100*r.sessions/mx))}%;background:linear-gradient(90deg,#27ae60,#7bd88a)">${Math.round(r.sessions)}</div></div>`).join('')
+ ||'<div class="z" style="padding:14px">no organic-search sessions in this window yet</div>';
+}
+
/* ---- MAP (choropleth, zero-dep SVG) ---- */
let WORLD=null;
async function renderMap(){
@@ -471,12 +555,13 @@ async function pollLive(){
}
/* ---- tabs ---- */
-const TITLES={sites:'All Sites',global:'Global',charts:'Charts — all domains',visuals:'Visuals',countries:'User Country',map:'User Map',live:'Live'};
+const TITLES={sites:'All Sites',global:'Global',charts:'Charts — all domains',keywords:'Keywords',visuals:'Visuals',countries:'User Country',map:'User Map',live:'Live'};
function showTab(tab){
document.querySelectorAll('.side button').forEach(b=>b.classList.toggle('on',b.dataset.tab===tab));
- ['sites','global','charts','visuals','countries','map','live'].forEach(t=>document.getElementById('v-'+t).hidden=(t!==tab));
+ ['sites','global','charts','keywords','visuals','countries','map','live'].forEach(t=>document.getElementById('v-'+t).hidden=(t!==tab));
document.getElementById('htitle').textContent=TITLES[tab];
clearInterval(liveTimer);liveTimer=null;
+ if(tab==='keywords')renderKeywords();
if(tab==='global')renderGlobal();
if(tab==='charts')renderCharts();
if(tab==='visuals')renderVisuals();
@@ -489,12 +574,13 @@ document.querySelectorAll('.side button').forEach(b=>b.onclick=()=>showTab(b.dat
/* ---- header + load ---- */
function fmtAge(iso){const s=(Date.now()-new Date(iso))/1000;return s<90?'just now':s<3600?Math.round(s/60)+'m ago':Math.round(s/3600)+'h ago';}
function header(){
- const R=activeRows();
- const active=R.filter(x=>dig(x,'d30.sessions')>0).length;
- const t7=R.reduce((s,x)=>s+dig(x,'d7.sessions'),0),t30=R.reduce((s,x)=>s+dig(x,'d30.sessions'),0);
- const wsum=R.reduce((s,x)=>s+(dig(x,'d30.averageSessionDuration')||0)*(dig(x,'d30.sessions')||0),0);
- const wavg=t30?wsum/t30:0;
- document.getElementById('hstat').textContent=`${R.length} sites${exStore?' (store hidden)':''} · ${active} with traffic · ${Math.round(t7).toLocaleString()} sess/7d · ${Math.round(t30).toLocaleString()} sess/30d · ⌀ ${fmtDurP(wavg)} on site`;
+ const R=activeRows(),wl=WLBL(WIN).toLowerCase();
+ const active=R.filter(x=>val(x,'m.sessions')>0).length;
+ const tw=R.reduce((s,x)=>s+(val(x,'m.sessions')||0),0);
+ const pv=R.reduce((s,x)=>s+(val(x,'m.screenPageViews')||0),0);
+ const wsum=R.reduce((s,x)=>s+(val(x,'m.averageSessionDuration')||0)*(val(x,'m.sessions')||0),0);
+ const wavg=tw?wsum/tw:0;
+ document.getElementById('hstat').textContent=`${R.length} sites${exStore?' (store hidden)':''} · ${active} with traffic this ${wl} · ${Math.round(tw).toLocaleString()} sessions · ${Math.round(pv).toLocaleString()} views · ⌀ ${fmtDurP(wavg)} on site`;
const age=(Date.now()-new Date(DATA.generated_at))/60000;
document.getElementById('hbadge').innerHTML=`<span class="badge ${age>45?'stale':'fresh'}">${age>45?'STALE':'fresh'} · ${fmtAge(DATA.generated_at)}</span>`;
document.getElementById('hgen').textContent='cache '+DATA.generated_at.slice(0,16).replace('T',' ')+'Z';
@@ -505,6 +591,7 @@ async function load(){
header();renderTable();
const cur=document.querySelector('.side button.on').dataset.tab;
if(cur==='visuals')renderVisuals();if(cur==='countries'){CDATA=null;renderCountries();}
+ if(cur==='keywords'){KDATA=null;renderKeywords();}
}
document.getElementById('q').oninput=renderTable;
document.getElementById('onlyactive').onchange=renderTable;
@@ -514,8 +601,8 @@ if(localStorage.gaDens){const el=document.getElementById('dens');el.value=localS
document.getElementById('refresh').onclick=async()=>{const m=document.getElementById('rmsg');m.textContent='refreshing… (~50s)';
await fetch(location.origin+'/api/refresh',{method:'POST'});
let n=0;const iv=setInterval(async()=>{n++;const j=await(await fetch(location.origin+'/api/status')).json();
- if(!j.refreshing){clearInterval(iv);m.textContent='updated ✓';CDATA=null;load();setTimeout(()=>m.textContent='',3000);}else m.textContent=`refreshing… ${n*3}s`;},3000);};
-renderCols();load();
+ if(!j.refreshing){clearInterval(iv);m.textContent='updated ✓';CDATA=null;KDATA=null;load();setTimeout(()=>m.textContent='',3000);}else m.textContent=`refreshing… ${n*3}s`;},3000);};
+renderWinSel();renderCols();load();
</script></body></html>"""
@@ -549,6 +636,10 @@ class H(BaseHTTPRequestHandler):
elif self.path == "/api/countries":
self._send(200, "application/json",
COUNTRIES.read_bytes() if COUNTRIES.exists() else b'{"rows":[],"total_sessions":0}')
+ elif self.path == "/api/keywords":
+ self._send(200, "application/json",
+ KEYWORDS.read_bytes() if KEYWORDS.exists()
+ else b'{"gsc_site_count":0,"windows":{},"channels":{},"organic_sites":{}}')
elif self.path == "/api/world":
wp = HERE / "cache" / "world.geojson"
self._send(200, "application/json",
← 4130c3a auto-save: 2026-08-05T23:16:37 (1 files) — etl.err
·
back to Ga Allsites
·
auto-data-snapshot: 2026-08-06T21:11:53 (1 data files) — etl b190447 →