← Files DataslayerARCHIVED FILE
scripts/generate_report.py
20.6 KB · Sep 30, 2026 · 22:55 UTC
from reportlab.lib.pagesizes import A4
from reportlab.lib import colors
from reportlab.lib.units import mm
from reportlab.lib.styles import ParagraphStyle
from reportlab.lib.enums import TA_LEFT, TA_CENTER, TA_RIGHT
from reportlab.platypus import (
SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle,
HRFlowable, PageBreak, KeepTogether, Image
)
from reportlab.platypus import Flowable
from reportlab.lib.utils import ImageReader
import os
from datetime import date
W, H = A4
DATA = {
"period": "11–17 March 2026",
"prev_period": "4–10 March 2026",
"search_console": {
"this": {"impressions": 657897, "clicks": 2062, "ctr": 0.31, "position": 7.35},
"prev": {"impressions": 775741, "clicks": 2162, "ctr": 0.28, "position": 7.23},
},
"google_ads": {
"combined_14d": {"spend": 511.56, "clicks": 1158,
"impressions": 10356, "conversions": 62, "cpa": 8.25},
},
"top_queries": [
("dataslayer", 383, 663, 57.77, 2.21),
("pomelli", 88, 16856, 0.52, 6.51),
("data slayer", 47, 131, 35.88, 1.18),
("pomelli google", 21, 4848, 0.43, 6.76),
("dataslayer pricing", 17, 19, 89.47, 1.0),
("looker studio templates", 11, 387, 2.84, 9.86),
("dataslayer login", 11, 11, 100.0, 1.0),
("google pomelli", 9, 3403, 0.26, 7.99),
],
}
# ── Palette — minimal, one accent ────────────────────────────────────────────
ACCENT = colors.HexColor("#8f00ff") # Dataslayer purple
DARK = colors.HexColor("#111111")
MID = colors.HexColor("#555555")
LIGHT_GRAY = colors.HexColor("#f4f4f4")
BORDER = colors.HexColor("#e0e0e0")
WHITE = colors.white
SUCCESS = colors.HexColor("#1a7f4e")
SUCCESS_L = colors.HexColor("#edf7f2")
WARNING = colors.HexColor("#a05c00")
WARNING_L = colors.HexColor("#fdf5e8")
DANGER = colors.HexColor("#b83232")
DANGER_L = colors.HexColor("#fdf0f0")
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
REPO_ROOT = os.path.abspath(os.path.join(SCRIPT_DIR, "..", "..", ".."))
LOGO_MAIN = os.path.join(REPO_ROOT, "dataslayer_logo_main.png")
LOGO_SMALL = os.path.join(REPO_ROOT, "dataslayer_logo_small.png")
def ps(name, **kw):
return ParagraphStyle(name, **kw)
S = {
"section": ps("sec", fontName="Helvetica-Bold", fontSize=7,
textColor=ACCENT, leading=10, spaceBefore=16, spaceAfter=3,
letterSpacing=1.6),
"h2": ps("h2", fontName="Helvetica-Bold", fontSize=15,
textColor=DARK, leading=19, spaceAfter=5),
"h3": ps("h3", fontName="Helvetica-Bold", fontSize=10,
textColor=DARK, leading=14, spaceBefore=10, spaceAfter=4),
"body": ps("body", fontName="Helvetica", fontSize=9,
textColor=MID, leading=14, spaceAfter=4),
"body_b": ps("bodyb", fontName="Helvetica-Bold", fontSize=9,
textColor=DARK, leading=14),
"small": ps("small", fontName="Helvetica", fontSize=7.5,
textColor=colors.HexColor("#888"), leading=11),
"footer": ps("footer", fontName="Helvetica", fontSize=7.5,
textColor=colors.HexColor("#aaa"), leading=10, alignment=TA_CENTER),
"metric_val": ps("mv", fontName="Helvetica-Bold", fontSize=22,
textColor=DARK, leading=26, alignment=TA_CENTER),
"metric_lbl": ps("ml", fontName="Helvetica", fontSize=7.5,
textColor=MID, leading=10, alignment=TA_CENTER),
}
def vs(a, b):
if b == 0: return "—"
pct = (a - b) / b * 100
sign = "+" if pct >= 0 else ""
return f"{sign}{pct:.1f}%"
# ── Cover — clean, minimal ────────────────────────────────────────────────────
class Cover(Flowable):
def wrap(self, aW, aH): return aW, aH
def draw(self):
c = self.canv
# Clean white background
c.setFillColor(WHITE)
c.rect(0, 0, W, H, fill=1, stroke=0)
# Single accent bar — thin, left edge
c.setFillColor(ACCENT)
c.rect(0, 0, 4, H, fill=1, stroke=0)
# Accent block — bottom right, very restrained
c.setFillColor(colors.HexColor("#f8f4ff"))
c.rect(W * 0.6, 0, W * 0.4, H * 0.30, fill=1, stroke=0)
# Logo — left-aligned, sits just above the title block
# Position: H * 0.755 — branded anchor, clear visual hierarchy
if os.path.exists(LOGO_MAIN):
img = ImageReader(LOGO_MAIN)
logo_w = 58*mm
logo_h = logo_w * (33 / 195)
c.drawImage(img, 18*mm, H * 0.755,
width=logo_w, height=logo_h, mask='auto')
# Thin separator below logo
c.setStrokeColor(colors.HexColor("#eeeeee"))
c.setLineWidth(0.5)
c.line(18*mm, H * 0.735, 80*mm, H * 0.735)
# Report type label
c.setFillColor(ACCENT)
c.setFont("Helvetica-Bold", 8)
c.drawString(18*mm, H * 0.695, "WEEKLY CHANNEL REPORT")
# Main title
c.setFillColor(DARK)
c.setFont("Helvetica-Bold", 38)
c.drawString(18*mm, H * 0.60, "Performance")
c.drawString(18*mm, H * 0.52, "Overview")
# Period
c.setFillColor(MID)
c.setFont("Helvetica", 13)
c.drawString(18*mm, H * 0.45, DATA["period"])
# Thin divider
c.setStrokeColor(colors.HexColor("#dddddd"))
c.setLineWidth(0.5)
c.line(18*mm, H * 0.42, 80*mm, H * 0.42)
# Meta line
c.setFillColor(colors.HexColor("#999"))
c.setFont("Helvetica", 8.5)
c.drawString(18*mm, H * 0.38,
"Organic Search · Google Ads · Key Findings · Actions")
# Bottom — generated date left, URL right
c.setFillColor(colors.HexColor("#bbb"))
c.setFont("Helvetica", 8)
c.drawString(18*mm, 18*mm,
f"Generated {date.today().strftime('%d %B %Y')}")
c.drawRightString(W - 18*mm, 18*mm, "dataslayer.ai")
# Bottom accent line
c.setFillColor(ACCENT)
c.rect(18*mm, 14*mm, W - 36*mm, 1.5, fill=1, stroke=0)
def hr_line(col=BORDER, t=0.5):
return HRFlowable(width="100%", thickness=t, color=col,
spaceAfter=5, spaceBefore=2)
def sp(h=6): return Spacer(1, h)
def section(label, title):
return [
Paragraph(label.upper(), S["section"]),
Paragraph(title, S["h2"]),
hr_line(ACCENT, 1.0),
sp(4),
]
def metric_card(value, label, change_str, good):
chg_col = SUCCESS if good else DANGER
chg_sty = ps("cs", fontName="Helvetica-Bold",
fontSize=8, textColor=chg_col, leading=11, alignment=TA_CENTER)
t = Table([
[Paragraph(value, S["metric_val"])],
[Paragraph(label, S["metric_lbl"])],
[Paragraph(change_str, chg_sty)],
], colWidths=[43*mm])
t.setStyle(TableStyle([
("BACKGROUND", (0,0), (-1,-1), LIGHT_GRAY),
("TOPPADDING", (0,0), (-1,-1), 10),
("BOTTOMPADDING", (0,0), (-1,-1), 10),
("LEFTPADDING", (0,0), (-1,-1), 4),
("RIGHTPADDING", (0,0), (-1,-1), 4),
("LINEBELOW", (0,0), (-1,0), 2, ACCENT),
("ALIGN", (0,0), (-1,-1), "CENTER"),
("ROUNDEDCORNERS", [6]),
]))
return t
def badge(status):
cfg = {
"Green": (SUCCESS_L, SUCCESS, "● Healthy"),
"Amber": (WARNING_L, WARNING, "● Watch"),
"Red": (DANGER_L, DANGER, "● Critical"),
}
bg, fg, txt = cfg[status]
sty = ps("b", fontName="Helvetica-Bold", fontSize=7.5,
textColor=fg, leading=10)
t = Table([[Paragraph(txt, sty)]], colWidths=[22*mm])
t.setStyle(TableStyle([
("BACKGROUND", (0,0), (-1,-1), bg),
("TOPPADDING", (0,0), (-1,-1), 3),
("BOTTOMPADDING", (0,0), (-1,-1), 3),
("LEFTPADDING", (0,0), (-1,-1), 7),
("RIGHTPADDING", (0,0), (-1,-1), 7),
("ROUNDEDCORNERS", [4]),
]))
return t
def finding(num, title, what, why, action, status="Amber"):
cfg = {
"Green": (SUCCESS_L, SUCCESS),
"Amber": (WARNING_L, WARNING),
"Red": (DANGER_L, DANGER),
}
bg, accent_col = cfg[status]
num_sty = ps("fn", fontName="Helvetica-Bold", fontSize=13,
textColor=accent_col, leading=17)
title_sty = ps("ft", fontName="Helvetica-Bold", fontSize=9.5,
textColor=DARK, leading=13)
rows = [
[Paragraph(f"{num}", num_sty),
Paragraph(title, title_sty)],
["", Paragraph(f"<b>What happened:</b> {what}", S["body"])],
["", Paragraph(f"<b>Why it matters:</b> {why}", S["body"])],
["", Paragraph(f"<b>Action:</b> {action}", S["body"])],
]
t = Table(rows, colWidths=[12*mm, 164*mm])
t.setStyle(TableStyle([
("BACKGROUND", (0,0), (-1,-1), bg),
("TOPPADDING", (0,0), (-1,-1), 6),
("BOTTOMPADDING", (0,0), (-1,-1), 6),
("LEFTPADDING", (0,0), (0,-1), 10),
("LEFTPADDING", (1,0), (-1,-1), 8),
("RIGHTPADDING", (0,0), (-1,-1), 10),
("LINEBEFORE", (0,0), (0,-1), 3, accent_col),
("VALIGN", (0,0), (0,-1), "TOP"),
("ROUNDEDCORNERS", [6]),
]))
return t
def on_page(canvas, doc):
canvas.saveState()
# Thin top line
canvas.setFillColor(ACCENT)
canvas.rect(0, H - 1.5, W, 1.5, fill=1, stroke=0)
# Header text
canvas.setFillColor(colors.HexColor("#aaa"))
canvas.setFont("Helvetica", 7)
canvas.drawString(18*mm, H - 8*mm, "Dataslayer · Weekly Channel Report")
canvas.drawCentredString(W/2, H - 8*mm, DATA["period"])
canvas.drawRightString(W - 18*mm, H - 8*mm, f"Page {doc.page}")
# Footer
canvas.setStrokeColor(BORDER)
canvas.setLineWidth(0.4)
canvas.line(18*mm, 14*mm, W - 18*mm, 14*mm)
canvas.setFillColor(colors.HexColor("#bbb"))
canvas.setFont("Helvetica", 7)
canvas.drawString(18*mm, 8*mm, "dataslayer.ai")
canvas.drawCentredString(W/2, 8*mm, "Data via Dataslayer MCP")
canvas.drawRightString(W - 18*mm, 8*mm,
date.today().strftime("%d %b %Y"))
canvas.restoreState()
def build():
reports_dir = os.path.join(REPO_ROOT, "reports")
os.makedirs(reports_dir, exist_ok=True)
out = os.path.join(reports_dir, "Dataslayer_Weekly_Report_Mar11_17_2026_v3.pdf")
doc = SimpleDocTemplate(out, pagesize=A4,
leftMargin=18*mm, rightMargin=18*mm,
topMargin=18*mm, bottomMargin=20*mm)
story = []
# ── COVER ─────────────────────────────────────────────────────────────────
story.append(Cover())
story.append(PageBreak())
# ── ORGANIC ───────────────────────────────────────────────────────────────
sc = DATA["search_console"]
t, p = sc["this"], sc["prev"]
story += section("01", "Organic search performance")
story.append(Paragraph(
"Search Console · 11–17 March vs 4–10 March 2026. "
"Impressions fell 15% week over week while CTR improved — "
"fewer total impressions but a higher proportion of clicks.",
S["body"]))
story.append(sp(10))
cards = [
metric_card(f"{t['impressions']:,}", "Impressions",
vs(t['impressions'], p['impressions']),
t['impressions'] >= p['impressions']),
metric_card(f"{t['clicks']:,}", "Clicks",
vs(t['clicks'], p['clicks']),
t['clicks'] >= p['clicks']),
metric_card(f"{t['ctr']:.2f}%", "CTR",
vs(t['ctr'], p['ctr']),
t['ctr'] >= p['ctr']),
metric_card(f"{t['position']:.1f}", "Avg. position",
vs(t['position'], p['position']),
t['position'] <= p['position']),
]
ct = Table([cards], colWidths=[43*mm]*4, hAlign="LEFT")
ct.setStyle(TableStyle([
("LEFTPADDING", (0,0), (-1,-1), 3),
("RIGHTPADDING", (0,0), (-1,-1), 3),
]))
story.append(ct)
story.append(sp(16))
story.append(Paragraph("Top queries by clicks — 11–17 March 2026", S["h3"]))
q_rows = [["Query", "Clicks", "Impressions", "CTR", "Position"]]
for row in DATA["top_queries"]:
q_rows.append([row[0], str(row[1]), f"{row[2]:,}",
f"{row[3]:.2f}%", f"{row[4]:.1f}"])
qt = Table(q_rows, colWidths=[72*mm, 22*mm, 32*mm, 24*mm, 26*mm])
qt.setStyle(TableStyle([
("BACKGROUND", (0,0), (-1,0), DARK),
("TEXTCOLOR", (0,0), (-1,0), WHITE),
("FONTNAME", (0,0), (-1,0), "Helvetica-Bold"),
("FONTNAME", (0,1), (-1,-1), "Helvetica"),
("FONTSIZE", (0,0), (-1,-1), 8.5),
("TOPPADDING", (0,0), (-1,-1), 6),
("BOTTOMPADDING", (0,0), (-1,-1), 6),
("LEFTPADDING", (0,0), (-1,-1), 8),
("ROWBACKGROUNDS", (0,1), (-1,-1), [WHITE, LIGHT_GRAY]),
("GRID", (0,0), (-1,-1), 0.3, BORDER),
("ALIGN", (1,0), (-1,-1), "CENTER"),
("VALIGN", (0,0), (-1,-1), "MIDDLE"),
("BACKGROUND", (0,2), (-1,2), colors.HexColor("#fff5f5")),
("TEXTCOLOR", (3,2), (3,2), DANGER),
("FONTNAME", (3,2), (3,2), "Helvetica-Bold"),
("ROUNDEDCORNERS", [4]),
]))
story.append(qt)
story.append(sp(10))
anom = Table([[
Paragraph("Note", ps("n", fontName="Helvetica-Bold",
fontSize=8.5, textColor=WARNING, leading=12)),
Paragraph(
'"pomelli" and variants: 25,000+ impressions at under 0.4% CTR. '
'High visibility, almost no traffic. Likely an intent mismatch — '
'the ranking page may not match what these searchers are looking for.',
S["body"]),
]], colWidths=[18*mm, 158*mm])
anom.setStyle(TableStyle([
("BACKGROUND", (0,0), (-1,-1), WARNING_L),
("TOPPADDING", (0,0), (-1,-1), 8),
("BOTTOMPADDING", (0,0), (-1,-1), 8),
("LEFTPADDING", (0,0), (-1,-1), 10),
("RIGHTPADDING", (0,0), (-1,-1), 10),
("VALIGN", (0,0), (-1,-1), "MIDDLE"),
("LINEBEFORE", (0,0), (0,-1), 2.5, WARNING),
("ROUNDEDCORNERS", [5]),
]))
story.append(anom)
# ── PAID ──────────────────────────────────────────────────────────────────
story.append(PageBreak())
story += section("02", "Paid media performance")
sr = Table([[
badge("Red"),
Paragraph(
"Both PMax campaigns paused since early March 2026. "
"No spend recorded for 11–17 March. "
"Combined 14-day data (4–17 March) shown below. "
"When active: <b>62 conversions at €8.25 CPA</b>.",
S["body"]),
]], colWidths=[28*mm, 148*mm])
sr.setStyle(TableStyle([
("VALIGN", (0,0), (-1,-1), "MIDDLE"),
("LEFTPADDING", (0,0), (0,-1), 0),
("LEFTPADDING", (1,0), (-1,-1), 10),
("TOPPADDING", (0,0), (-1,-1), 0),
("BOTTOMPADDING",(0,0), (-1,-1), 0),
]))
story.append(sr)
story.append(sp(12))
ads = DATA["google_ads"]["combined_14d"]
ads_rows = [
["Metric", "4–17 March 2026 (14 days)", "Note"],
["Total spend", f"€{ads['spend']:,.2f}", "Campaigns now paused"],
["Clicks", f"{ads['clicks']:,}", ""],
["Impressions", f"{ads['impressions']:,}", ""],
["Conversions", f"{ads['conversions']}", ""],
["Cost per conversion", f"€{ads['cpa']:.2f}", "Below typical target — strong signal"],
]
at = Table(ads_rows, colWidths=[68*mm, 72*mm, 36*mm])
at.setStyle(TableStyle([
("BACKGROUND", (0,0), (-1,0), DARK),
("TEXTCOLOR", (0,0), (-1,0), WHITE),
("FONTNAME", (0,0), (-1,0), "Helvetica-Bold"),
("FONTNAME", (0,1), (-1,-1), "Helvetica"),
("FONTSIZE", (0,0), (-1,-1), 8.5),
("TOPPADDING", (0,0), (-1,-1), 7),
("BOTTOMPADDING", (0,0), (-1,-1), 7),
("LEFTPADDING", (0,0), (-1,-1), 8),
("ROWBACKGROUNDS", (0,1), (-1,-1), [WHITE, LIGHT_GRAY]),
("GRID", (0,0), (-1,-1), 0.3, BORDER),
("TEXTCOLOR", (2,5), (2,5), SUCCESS),
("FONTNAME", (2,5), (2,5), "Helvetica-Bold"),
("ALIGN", (1,0), (-1,-1), "CENTER"),
("VALIGN", (0,0), (-1,-1), "MIDDLE"),
("ROUNDEDCORNERS", [4]),
]))
story.append(at)
story.append(sp(18))
# ── FINDINGS ──────────────────────────────────────────────────────────────
story += section("03", "Key findings")
story.append(finding(
"1", "Impressions dropped 15% — CTR improved",
"Impressions fell from 775,741 to 657,897 (−15.2%). CTR improved "
"from 0.28% to 0.31% (+10.7%). Net clicks down just 4.6%.",
"The site is reaching fewer people but a larger proportion is clicking. "
"The impression drop may be affecting low-intent queries.",
'Check which queries lost impressions. If they are "pomelli" variants, '
"the drop is working in your favour.",
"Amber"))
story.append(sp(8))
story.append(finding(
"2", '"pomelli" — 16,856 impressions at 0.52% CTR',
'"pomelli" and its variants generated 25,000+ impressions '
"with a combined CTR below 0.4%. High visibility, almost no traffic.",
"These impressions distort your average position and consume crawl budget "
"without delivering meaningful traffic.",
"Identify which URL ranks for these queries. If it is a generic page, "
"assess whether ranking for this term is worth pursuing.",
"Red"))
story.append(sp(8))
story.append(finding(
"3", "Paid paused — organic is the sole acquisition channel",
"Both PMax campaigns paused since early March. "
"Pre-pause: 62 conversions at €8.25 CPA over 14 days.",
"With no paid channel active, any organic dip directly impacts registrations. "
"The €8.25 CPA confirms campaigns were working.",
"Set a reactivation date and define the audience signal threshold "
"required before relaunching. Spain historically outperformed Europe on CPA.",
"Red"))
story.append(sp(18))
# ── ACTIONS ───────────────────────────────────────────────────────────────
story += section("04", "Recommended actions")
act_data = [
["#", "Action", "Expected outcome", "By when"],
["1", "Reactivate Spain PMax with revised audience signals",
"Restore paid acquisition — Spain CPA was lower than Europe",
"This week"],
["2", 'Audit "pomelli" query pages — evaluate strategic fit',
"Reduce wasted impressions, improve overall CTR toward 0.40%+",
"This week"],
["3", "Run ds-paid-audit once campaigns are live again",
"Catch audience signal issues before they compound",
"Post-reactivation"],
]
at2 = Table(act_data, colWidths=[8*mm, 84*mm, 62*mm, 22*mm])
at2.setStyle(TableStyle([
("BACKGROUND", (0,0), (-1,0), DARK),
("TEXTCOLOR", (0,0), (-1,0), WHITE),
("FONTNAME", (0,0), (-1,0), "Helvetica-Bold"),
("FONTNAME", (0,1), (-1,-1), "Helvetica"),
("FONTSIZE", (0,0), (-1,-1), 8.5),
("TOPPADDING", (0,0), (-1,-1), 8),
("BOTTOMPADDING", (0,0), (-1,-1), 8),
("LEFTPADDING", (0,0), (-1,-1), 8),
("ROWBACKGROUNDS", (0,1), (-1,-1), [WHITE, LIGHT_GRAY]),
("GRID", (0,0), (-1,-1), 0.3, BORDER),
("FONTNAME", (0,1), (0,-1), "Helvetica-Bold"),
("TEXTCOLOR", (0,1), (0,-1), ACCENT),
("FONTSIZE", (0,1), (0,-1), 12),
("ALIGN", (3,0), (3,-1), "CENTER"),
("VALIGN", (0,0), (-1,-1), "TOP"),
("ROUNDEDCORNERS", [4]),
]))
story.append(at2)
story.append(sp(16))
story.append(hr_line())
story.append(Paragraph(
"Data fetched live via Dataslayer MCP · "
"Search Console: sc-domain:dataslayer.ai · "
"Google Ads: account 9549318854",
S["footer"]))
doc.build(story,
onFirstPage=lambda c, d: None,
onLaterPages=on_page)
print("Done:", out)
build()
SHA-256: 160438a0f9a90a79ce91b2cd5ea8818441745842e4448c35339197b71b9f8019