← back to Ga Allsites
keywords: organic-search grant-priority queue in ETL + sortable organic-first table on dashboard
a288a2829b7a2954da801f1fc832d0ed05aed8f4 · 2026-08-17 08:41:02 -0700 · Steve Abrams
Files touched
Diff
commit a288a2829b7a2954da801f1fc832d0ed05aed8f4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Aug 17 08:41:02 2026 -0700
keywords: organic-search grant-priority queue in ETL + sortable organic-first table on dashboard
---
etl.py | 36 ++++++++++++++++++++++++++++++++++++
server.py | 34 ++++++++++++++++++++++++++++++++++
2 files changed, 70 insertions(+)
diff --git a/etl.py b/etl.py
index e5d8706..e114930 100644
--- a/etl.py
+++ b/etl.py
@@ -288,6 +288,41 @@ def main() -> int:
except Exception as e:
print(f"GSC pass skipped: {e}")
+ # ---- Grant-priority queue: which properties are worth a GSC grant ----
+ # GSC value tracks ORGANIC-SEARCH traffic, not total sessions, so rank on
+ # 365d organic sessions (floored) and flag domain-verifiability. This is the
+ # ready-to-click grant queue the dashboard + humans read, best-first.
+ ORGANIC_FLOOR = 10 # <10 organic/yr → GSC has nothing to show
+ NONSITE = {"chargeandexplore"} # GA4 props with no standalone storefront
+ total_365 = {r["property"]: float((r.get("d365") or {}).get("sessions") or 0)
+ for r in rows}
+ granted_slugs = ["".join(c for c in s["url"].split(":")[-1].lower()
+ if c.isalnum())
+ for s in gsc_sites]
+
+ def _slug(name: str) -> str:
+ s = name.lower().replace(" - ga4", "").replace("&", "and")
+ return "".join(c for c in s if c.isalnum())
+
+ grant_priority = []
+ for name, org in organic_sites["d365"].items():
+ slug = _slug(name)
+ granted = bool(slug) and any(slug in gs for gs in granted_slugs)
+ verifiable = slug not in NONSITE
+ tot = total_365.get(name, 0.0)
+ grant_priority.append({
+ "property": name,
+ "domain": f"{slug}.com" if slug else "",
+ "organic_365d": round(org),
+ "total_365d": round(tot),
+ "organic_share": round(100 * org / tot, 1) if tot else 0.0,
+ "granted": granted,
+ "verifiable": verifiable,
+ "meets_floor": org >= ORGANIC_FLOOR,
+ "recommend": org >= ORGANIC_FLOOR and verifiable and not granted,
+ })
+ grant_priority.sort(key=lambda r: r["organic_365d"], reverse=True)
+
kpath = CACHE.parent / "keywords.json"
kpath.write_text(json.dumps({
"generated_at": datetime.now(timezone.utc).isoformat(),
@@ -297,6 +332,7 @@ def main() -> int:
"windows": gsc_windows,
"channels": channels,
"organic_sites": org_top,
+ "grant_priority": grant_priority,
}, indent=2))
print(f"Wrote {kpath} · {len(gsc_sites)} GSC sites · "
f"{sum(len(v['queries']) for v in gsc_windows.values())} query rows")
diff --git a/server.py b/server.py
index 2c170f0..d76a743 100644
--- a/server.py
+++ b/server.py
@@ -227,6 +227,13 @@ PAGE = r"""<!DOCTYPE html><html><head><meta charset="utf-8">
<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="kwgptitle">GSC grant priority — ranked by organic search (365d)</h2>
+ <p class="acct" id="kwgpsub"></p>
+ <table id="kwgptbl"><thead><tr>
+ <th class="l" data-k="property">Site</th><th data-k="organic_365d">Organic 365d</th>
+ <th data-k="total_365d">Total 365d</th><th data-k="organic_share">Organic %</th>
+ <th class="l" data-k="status">Status</th></tr></thead>
+ <tbody id="kwgptb"></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>
@@ -486,12 +493,39 @@ async function renderKeywords(){
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>';
+ renderGrant();
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>';
}
+/* grant-priority table — organic-search-first, sortable, organic desc by default */
+let KWGP={k:'organic_365d',dir:-1};
+function grankOrder(r){return r.granted?0:(r.recommend?1:(!r.verifiable?2:3));}
+function grankStatus(r){
+ if(r.granted)return '<span style="color:#27ae60">✅ granted</span>';
+ if(!r.meets_floor)return '<span class="z">— below floor</span>';
+ if(!r.verifiable)return '<span class="z">? verify domain</span>';
+ return '<span style="color:#2d7a34">▷ grant</span>';}
+function renderGrant(){
+ const gp=(KDATA.grant_priority||[]).slice();
+ const q=gp.filter(r=>r.recommend).length;
+ document.getElementById('kwgpsub').textContent=
+ `GSC value tracks organic search, not total traffic — ranked by 365d organic sessions (floor ≥10). ${q} site${q===1?'':'s'} worth granting.`;
+ const k=KWGP.k,dir=KWGP.dir;
+ gp.sort((a,b)=>{if(k==='property')return dir*String(a.property).localeCompare(String(b.property));
+ const x=k==='status'?grankOrder(a):(a[k]||0),y=k==='status'?grankOrder(b):(b[k]||0);return dir*(x-y);});
+ document.querySelectorAll('#kwgptbl th').forEach(th=>{th.style.cursor='pointer';
+ th.style.opacity=th.dataset.k===k?'1':'.7';
+ th.onclick=()=>{KWGP.dir=(KWGP.k===th.dataset.k)?-KWGP.dir:-1;KWGP.k=th.dataset.k;renderGrant();};});
+ document.getElementById('kwgptb').innerHTML=gp.map(r=>
+ `<tr${r.granted?' style="opacity:.6"':''}><td class="l">${esc(r.property)}`+
+ `${r.domain?` <span class="z" style="font-size:11px">${esc(r.domain)}</span>`:''}</td>`+
+ `<td>${(r.organic_365d||0).toLocaleString()}</td><td>${(r.total_365d||0).toLocaleString()}</td>`+
+ `<td>${r.organic_share}%</td><td class="l">${grankStatus(r)}</td></tr>`).join('')
+ ||'<tr><td class="l z" colspan="5" style="padding:14px">organic-search ranking arrives on the next data refresh</td></tr>';
+}
/* ---- MAP (choropleth, zero-dep SVG) ---- */
let WORLD=null;
← c36f7f0 auto-data-snapshot: 2026-08-17T07:02:20 (1 data files) — etl
·
back to Ga Allsites
·
untrack .port (env-specific): prevents Mac2's 9780 clobberin 9e0a952 →