`build.py`

139 lines
#!/usr/bin/env python3
"""Rebuild the DW GSC report from data/*.json (fetched by refresh.sh)."""
import json, os, datetime
from collections import defaultdict

DIR = os.path.dirname(os.path.abspath(__file__))
def load(name):
    with open(os.path.join(DIR, "data", name)) as f:
        return json.load(f)["rows"]

q  = load("q.json")
qp = load("qp.json")
dt = load("date.json")

# ---- Striking distance ----
strike = []
for r in q:
    kw, pos, impr, clk, ctr = r["keys"][0], r["position"], r["impressions"], r["clicks"], r["ctr"]
    if 4 <= pos <= 15 and impr >= 50:
        opp = max(0, round(impr * (0.18 - ctr)))
        strike.append((kw, round(pos,1), impr, clk, round(ctr*100,1), opp))
strike.sort(key=lambda x: -x[5]); strike = strike[:40]

# ---- Cannibalization ----
byq = defaultdict(list)
for r in qp:
    byq[r["keys"][0]].append((r["keys"][1], r["clicks"], r["impressions"], round(r["position"],1)))
cann = []
for kw, pages in byq.items():
    strong = [p for p in pages if p[2] >= 30]
    tot = sum(p[2] for p in pages)
    if len(strong) >= 2 and tot >= 100:
        strong.sort(key=lambda x:-x[2])
        cann.append((kw, tot, sum(p[1] for p in pages), strong[:4]))
cann.sort(key=lambda x:-x[1]); cann = cann[:18]

topq = sorted(q, key=lambda r:-r["clicks"])[:15]

# ---- Monthly trend from daily rows ----
mon = defaultdict(lambda:[0,0,0.0,0])  # clicks, impr, pos_sum, days
for r in dt:
    m = r["keys"][0][:7]
    mon[m][0]+=r["clicks"]; mon[m][1]+=r["impressions"]; mon[m][2]+=r["position"]; mon[m][3]+=1
months = sorted(mon.keys())
trend=[]
for m in months:
    c,i,ps,d = mon[m]
    trend.append((datetime.datetime.strptime(m,"%Y-%m").strftime("%b"), round(c/d) if d else 0, round(ps/d,1) if d else 0))
trend = trend[-7:]

# ---- static rewrites ----
rewrites = [
 ("Homepage /","designer wallpaper / wall coverings","weak head-term CTR at page-2 positions",
  "Designer Wallcoverings &amp; Luxury Wallpaper | Ralph Lauren, Versace, Lee Jofa",
  "Shop 40+ luxury wallcovering houses — Ralph Lauren, Versace, Lee Jofa, Missoni &amp; more. Designer wallpaper, grasscloth, velvet &amp; custom murals. Trade pricing &amp; samples."),
 ("/collections/ralph-lauren-home","ralph lauren wallpaper","page-1, sub-2.5% CTR",
  "Ralph Lauren Wallpaper &amp; Wallcoverings — Authorized Dealer",
  "Explore the full Ralph Lauren Home wallpaper collection — signature plaids, florals &amp; heritage patterns. Trade pricing, order samples online."),
 ("/collections/versace-wallpaper","versace wallpaper","page-1, ~1.4% CTR",
  "Versace Wallpaper — Official Home Collection",
  "Authentic Versace Home wallpaper — Baroque, Medusa &amp; Greek-key designs. Luxury Italian wallcoverings with trade pricing &amp; fast samples."),
 ("/collections/lee-jofa","lee jofa","page-1, ~1.4% CTR",
  "Lee Jofa Wallpaper &amp; Fabric | Authorized Dealer",
  "Browse Lee Jofa wallcoverings &amp; fabrics — heritage prints, damasks &amp; wovens. Trade discounts and quick samples."),
 ("/collections/flocked-velvet-wallpaper","velvet wallpaper","~0.8% CTR, 5.8k impr",
  "Flocked Velvet Wallpaper | Luxury Textured Wallcoverings",
  "Rich flocked velvet wallpaper in dozens of colorways — damask, floral &amp; geometric. Add depth &amp; texture. Trade pricing, samples ship fast."),
 ("Wolf-Gordon pages","wolf gordon wallcovering","~0.9% CTR at pos 6",
  "Wolf-Gordon Wallcoverings — Contract &amp; Commercial",
  "Wolf-Gordon commercial wallcoverings &amp; textiles — durable Type II vinyl for contract interiors. Request pricing &amp; samples."),
 ("Zuber pages","zuber wallpaper","~0.8% CTR at pos 10",
  "Zuber Wallpaper — Hand-Blocked Scenic Panoramics",
  "Zuber et Cie hand-block-printed scenic wallpaper &amp; panoramics from original woodblocks. Heritage French wallcoverings, made to order."),
]

gen = datetime.datetime.now().strftime("%b %d, %Y, %I:%M %p")
def esc(s): return str(s).replace("&","&amp;").replace("<","&lt;").replace(">","&gt;")

CSS = """:root{--bg:#f6f3ee;--ink:#1c1a17;--muted:#6b6459;--line:#e0d9cd;--card:#fffdf9;--accent:#8a6d3b;--good:#2e7d5b;--bad:#b4472e}*{box-sizing:border-box}body{margin:0;font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Helvetica,Arial,sans-serif;background:var(--bg);color:var(--ink);line-height:1.5}.wrap{max-width:1120px;margin:0 auto;padding:32px 22px 80px}h1{font-family:Georgia,serif;font-weight:600;font-size:30px;margin:0 0 4px}h2{font-family:Georgia,serif;font-weight:600;font-size:20px;margin:40px 0 12px;padding-bottom:8px;border-bottom:1px solid var(--line)}.sub{color:var(--muted);font-size:13px;margin:0 0 24px}.kpis{display:grid;grid-template-columns:repeat(4,1fr);gap:12px;margin:18px 0}.kpi{background:var(--card);border:1px solid var(--line);border-radius:10px;padding:14px 16px}.kpi .n{font-size:26px;font-weight:700;font-family:Georgia,serif}.kpi .l{font-size:11px;text-transform:uppercase;letter-spacing:.7px;color:var(--muted);margin-top:2px}table{width:100%;border-collapse:collapse;background:var(--card);border:1px solid var(--line);border-radius:10px;overflow:hidden;font-size:13px}th,td{padding:8px 11px;text-align:left;border-bottom:1px solid var(--line)}th{background:#efe9df;font-size:11px;text-transform:uppercase;letter-spacing:.5px;color:var(--muted)}td.num,th.num{text-align:right;font-variant-numeric:tabular-nums}tr:last-child td{border-bottom:none}.tag{display:inline-block;font-size:11px;padding:1px 7px;border-radius:20px;background:#efe9df;color:var(--accent)}.opp{color:var(--good);font-weight:600}.warn{color:var(--bad);font-weight:600}.note{background:var(--card);border:1px solid var(--line);border-left:3px solid var(--accent);border-radius:8px;padding:12px 16px;font-size:13px;margin:12px 0}.bars{display:flex;align-items:flex-end;gap:14px;height:150px;padding:16px 8px 0;background:var(--card);border:1px solid var(--line);border-radius:10px}.bar{flex:1;display:flex;flex-direction:column;align-items:center;justify-content:flex-end;height:100%}.bar .col{width:70%;background:linear-gradient(#b89968,#8a6d3b);border-radius:4px 4px 0 0}.bar .v{font-size:11px;font-weight:600;margin-bottom:3px}.bar .m{font-size:11px;color:var(--muted);margin-top:5px}.cann{background:var(--card);border:1px solid var(--line);border-radius:10px;padding:4px 0;margin-bottom:14px}.cann h3{font-size:14px;margin:12px 16px 2px}.cann .pg{font-size:12px;color:var(--muted);padding:3px 16px;display:flex;justify-content:space-between}.rw{background:var(--card);border:1px solid var(--line);border-radius:10px;padding:14px 16px;margin-bottom:12px}.rw .pth{font-family:ui-monospace,Menlo,monospace;font-size:12px;color:var(--accent)}.rw .meta{font-size:12px;color:var(--muted);margin:2px 0 8px}.rw .lbl{font-size:10px;text-transform:uppercase;letter-spacing:.6px;color:var(--muted);margin-top:6px}.rw .val{font-size:13px;background:var(--bg);border:1px solid var(--line);border-radius:6px;padding:6px 9px;margin-top:2px}footer{margin-top:50px;color:var(--muted);font-size:12px;text-align:center}"""

h=["<!doctype html><html><head><meta charset='utf-8'><meta name='viewport' content='width=device-width,initial-scale=1'><title>DW Search Console Report</title><style>"+CSS+"</style></head><body><div class='wrap'>"]
h.append("<h1>Designer Wallcoverings — Search Console Intelligence</h1>")
h.append("<p class='sub'>Live Google Search Console &middot; <b>sc-domain:designerwallcoverings.com</b> &middot; 28-day window + 6-month trend &middot; auto-refreshed weekly &middot; generated "+gen+" &middot; source: GSC (free, $0)</p>")

tc=sum(r["clicks"] for r in q); ti=sum(r["impressions"] for r in q)
ac=(tc/ti*100) if ti else 0; ap=sum(r["position"] for r in q)/len(q) if q else 0
h.append("<div class='kpis'>")
h.append("<div class='kpi'><div class='n'>%s</div><div class='l'>Clicks (28d, queries)</div></div>"%f"{tc:,}")
h.append("<div class='kpi'><div class='n'>%s</div><div class='l'>Impressions (28d)</div></div>"%f"{ti:,}")
h.append("<div class='kpi'><div class='n'>%.1f%%</div><div class='l'>Avg CTR</div></div>"%ac)
h.append("<div class='kpi'><div class='n'>%.1f</div><div class='l'>Avg position</div></div>"%ap)
h.append("</div>")

# trend
if len(trend)>=2:
    first,last=trend[0][1],trend[-1][1]
    delta = round((last-first)/first*100) if first else 0
    word = ("down %d%%"%abs(delta)) if delta<0 else ("up %d%%"%delta)
    cls = "warn" if delta<0 else "opp"
    h.append("<h2>6-Month Trend</h2>")
    h.append("<div class='note'>Avg organic clicks/day <b class='%s'>%s</b> from %s to %s (%.1f&rarr;%.1f avg position). Watch this reverse as the fixes land.</div>"%(cls,word,trend[0][0],trend[-1][0],trend[0][2],trend[-1][2]))
    mx=max(t[1] for t in trend) or 1
    h.append("<div class='bars'>")
    for m,c,p in trend:
        h.append("<div class='bar'><div class='v'>%d</div><div class='col' style='height:%d%%'></div><div class='m'>%s<br>pos %.1f</div></div>"%(c,int(c/mx*100),m,p))
    h.append("</div><p class='sub'>Bars = avg organic clicks/day per month.</p>")

h.append("<h2>Striking-Distance Keywords — fastest wins (pos 4–15)</h2>")
h.append("<div class='note'>Page-1 but not top-3. “Est. missed clicks/mo” = impressions × (18% target CTR − current CTR). Sorted by opportunity.</div>")
h.append("<table><tr><th>Keyword</th><th class='num'>Pos</th><th class='num'>Impr</th><th class='num'>Clicks</th><th class='num'>CTR</th><th class='num'>Missed/mo</th></tr>")
for kw,pos,impr,clk,ctr,opp in strike:
    h.append("<tr><td>%s</td><td class='num'>%.1f</td><td class='num'>%s</td><td class='num'>%d</td><td class='num'>%.1f%%</td><td class='num opp'>+%d</td></tr>"%(esc(kw),pos,f"{impr:,}",clk,ctr,opp))
h.append("</table>")

h.append("<h2>Cannibalization — queries split across pages</h2>")
h.append("<div class='note'>2+ of your pages competing for the same term. Consolidate/internal-link to one canonical page each.</div>")
for kw,tot,tclk,pages in cann:
    h.append("<div class='cann'><h3>%s <span class='tag'>%s impr &middot; %d clicks &middot; %d pages</span></h3>"%(esc(kw),f"{tot:,}",tclk,len(pages)))
    for pg,c,i,p in pages:
        h.append("<div class='pg'><span>%s</span><span>pos %.1f &middot; %s impr &middot; %d clk</span></div>"%(esc(pg.replace('https://www.designerwallcoverings.com','')),p,f"{i:,}",c))
    h.append("</div>")

h.append("<h2>Top Queries by Clicks (28d)</h2>")
h.append("<table><tr><th>Query</th><th class='num'>Clicks</th><th class='num'>Impr</th><th class='num'>CTR</th><th class='num'>Pos</th></tr>")
for r in topq:
    h.append("<tr><td>%s</td><td class='num'>%d</td><td class='num'>%s</td><td class='num'>%.1f%%</td><td class='num'>%.1f</td></tr>"%(esc(r["keys"][0]),r["clicks"],f'{r["impressions"]:,}',r["ctr"]*100,r["position"]))
h.append("</table>")

h.append("<h2>Drafted Title / Meta Rewrites</h2>")
h.append("<div class='note'>Keyword-front-loaded, benefit-led SEO fields to lift CTR on low-CTR page-1 pages. Paste into each page’s SEO fields (or apply via Shopify — gated).</div>")
for path,kw,stat,title,meta in rewrites:
    h.append("<div class='rw'><span class='pth'>%s</span> &middot; <span class='tag'>%s</span><div class='meta'>%s</div><div class='lbl'>Title tag</div><div class='val'>%s</div><div class='lbl'>Meta description</div><div class='val'>%s</div></div>"%(esc(path),esc(kw),esc(stat),title,meta))

h.append("<footer>OpenSEO self-hosted + Google Search Console &middot; read-only, $0 &middot; auto-refreshed weekly &middot; "+gen+"</footer></div></body></html>")

with open(os.path.join(DIR,"index.html"),"w") as f:
    f.write("".join(h))
print("rebuilt index.html | strike=%d cann=%d trend=%d 28d_clicks=%d"%(len(strike),len(cann),len(trend),tc))