mercury.kennethreitz.org/scripts/umami-daily-report.py

#!/usr/bin/env python3
"""Umami daily email digest.

Queries the Umami Postgres directly (via `docker exec`) for yesterday's traffic
across all sites, compares to the prior day, renders an HTML email and sends it
via SMTP. Day boundaries are in America/New_York so "yesterday" matches local time.

Config is read from /etc/umami-report.env (KEY=VALUE per line):
  SMTP_HOST=smtp.gmail.com
  SMTP_PORT=587
  SMTP_USER=thepythonist@gmail.com
  SMTP_PASS=<google app password>
  MAIL_TO=thepythonist@gmail.com
  MAIL_FROM=thepythonist@gmail.com

Run with --dry-run to print the HTML to stdout without sending (no creds needed).
"""
import subprocess, os, sys, smtplib
from datetime import datetime, timedelta
from email.mime.text import MIMEText
from email.utils import formatdate
from zoneinfo import ZoneInfo

DB_CONTAINER = "umami-avvc5k-umami-db-1"
TZ = ZoneInfo("America/New_York")
CONFIG = "/etc/umami-report.env"


def psql(sql):
    out = subprocess.run(
        ["docker", "exec", DB_CONTAINER, "psql", "-U", "umami", "-d", "umami",
         "-t", "-A", "-F", "\t", "-c", sql],
        capture_output=True, text=True, check=True).stdout
    return [ln.split("\t") for ln in out.splitlines() if ln.strip()]


def utc(dt):
    return dt.astimezone(ZoneInfo("UTC")).strftime("%Y-%m-%d %H:%M:%S+00")


def ranges():
    now = datetime.now(TZ)
    today = now.replace(hour=0, minute=0, second=0, microsecond=0)
    return utc(today - timedelta(days=1)), utc(today), utc(today - timedelta(days=2))


def delta(cur, prev):
    cur, prev = int(cur), int(prev)
    if prev == 0:
        return ("new", "#2563eb") if cur else ("-", "#9ca3af")
    pct = round((cur - prev) / prev * 100)
    if pct > 0:
        return (f"▲ {pct}%", "#16a34a")
    if pct < 0:
        return (f"▼ {abs(pct)}%", "#dc2626")
    return ("0%", "#9ca3af")


def fetch():
    y0, y1, p0 = ranges()
    sites = psql(f"""
      select w.name,
        count(*) filter (where e.event_type=1 and e.created_at>='{y0}' and e.created_at<'{y1}') pv,
        count(distinct e.session_id) filter (where e.created_at>='{y0}' and e.created_at<'{y1}') vis,
        count(distinct e.visit_id) filter (where e.created_at>='{y0}' and e.created_at<'{y1}') visits,
        count(*) filter (where e.event_type=1 and e.created_at>='{p0}' and e.created_at<'{y0}') pv_prev,
        count(distinct e.session_id) filter (where e.created_at>='{p0}' and e.created_at<'{y0}') vis_prev
      from website w
      left join website_event e on e.website_id=w.website_id and e.created_at>='{p0}' and e.created_at<'{y1}'
      group by w.name order by pv desc;""")
    pages = psql(f"""
      select w.name, e.url_path, count(*) c
      from website_event e join website w on w.website_id=e.website_id
      where e.event_type=1 and e.created_at>='{y0}' and e.created_at<'{y1}'
      group by w.name, e.url_path order by c desc limit 10;""")
    return sites, pages


def render(sites, pages):
    day = (datetime.now(TZ) - timedelta(days=1)).strftime("%A, %B %-d, %Y")
    active = [s for s in sites if int(s[1]) > 0 or int(s[4]) > 0]
    tot_pv = sum(int(s[1]) for s in sites)
    tot_vis = sum(int(s[2]) for s in sites)
    tot_pv_p = sum(int(s[4]) for s in sites)
    tot_vis_p = sum(int(s[5]) for s in sites)
    pvd, pvc = delta(tot_pv, tot_pv_p)
    visd, visc = delta(tot_vis, tot_vis_p)

    rows = ""
    for name, pv, vis, visits, pvp, visp in active:
        d1, c1 = delta(pv, pvp)
        d2, c2 = delta(vis, visp)
        rows += f"""<tr>
          <td style="padding:10px 12px;border-bottom:1px solid #eee;font-weight:600;color:#111">{name}</td>
          <td style="padding:10px 12px;border-bottom:1px solid #eee;text-align:right;color:#111">{int(pv):,}
            <span style="color:{c1};font-size:12px">&nbsp;{d1}</span></td>
          <td style="padding:10px 12px;border-bottom:1px solid #eee;text-align:right;color:#111">{int(vis):,}
            <span style="color:{c2};font-size:12px">&nbsp;{d2}</span></td>
          <td style="padding:10px 12px;border-bottom:1px solid #eee;text-align:right;color:#555">{int(visits):,}</td>
        </tr>"""

    toprows = "".join(
        f"""<tr>
          <td style="padding:6px 12px;border-bottom:1px solid #f1f1f1;color:#555">{n}</td>
          <td style="padding:6px 12px;border-bottom:1px solid #f1f1f1;color:#111">{p}</td>
          <td style="padding:6px 12px;border-bottom:1px solid #f1f1f1;text-align:right;color:#111">{int(c):,}</td>
        </tr>""" for n, p, c in pages)

    return f"""<!doctype html><html><body style="margin:0;background:#f6f7f9;font-family:-apple-system,Segoe UI,Roboto,Helvetica,Arial,sans-serif">
<div style="max-width:640px;margin:0 auto;padding:24px">
  <div style="background:linear-gradient(135deg,#6366f1,#8b5cf6);border-radius:14px;padding:22px 24px;color:#fff">
    <div style="font-size:13px;opacity:.85;letter-spacing:.4px;text-transform:uppercase">Umami Daily Report</div>
    <div style="font-size:20px;font-weight:700;margin-top:2px">{day}</div>
  </div>
  <div style="display:flex;gap:12px;margin:16px 0">
    <div style="flex:1;background:#fff;border-radius:12px;padding:16px 18px;box-shadow:0 1px 3px rgba(0,0,0,.06)">
      <div style="font-size:12px;color:#888;text-transform:uppercase;letter-spacing:.4px">Pageviews</div>
      <div style="font-size:26px;font-weight:700;color:#111">{tot_pv:,} <span style="font-size:13px;color:{pvc}">{pvd}</span></div>
    </div>
    <div style="flex:1;background:#fff;border-radius:12px;padding:16px 18px;box-shadow:0 1px 3px rgba(0,0,0,.06)">
      <div style="font-size:12px;color:#888;text-transform:uppercase;letter-spacing:.4px">Visitors</div>
      <div style="font-size:26px;font-weight:700;color:#111">{tot_vis:,} <span style="font-size:13px;color:{visc}">{visd}</span></div>
    </div>
  </div>
  <div style="background:#fff;border-radius:12px;padding:6px 4px;box-shadow:0 1px 3px rgba(0,0,0,.06)">
    <table style="width:100%;border-collapse:collapse;font-size:14px">
      <thead><tr style="color:#888;font-size:11px;text-transform:uppercase;letter-spacing:.4px">
        <th style="padding:10px 12px;text-align:left">Site</th>
        <th style="padding:10px 12px;text-align:right">Pageviews</th>
        <th style="padding:10px 12px;text-align:right">Visitors</th>
        <th style="padding:10px 12px;text-align:right">Visits</th>
      </tr></thead><tbody>{rows}</tbody>
    </table>
  </div>
  <div style="font-size:12px;color:#888;text-transform:uppercase;letter-spacing:.4px;margin:20px 0 8px;padding-left:4px">Top pages yesterday</div>
  <div style="background:#fff;border-radius:12px;padding:6px 4px;box-shadow:0 1px 3px rgba(0,0,0,.06)">
    <table style="width:100%;border-collapse:collapse;font-size:13px"><tbody>{toprows}</tbody></table>
  </div>
  <div style="text-align:center;color:#aab;font-size:12px;margin-top:20px">
    <a href="https://analytics.kennethreitz.org" style="color:#8b5cf6;text-decoration:none">Open Umami &rarr;</a>
  </div>
</div></body></html>"""


def load_config():
    cfg = {}
    if os.path.exists(CONFIG):
        for ln in open(CONFIG):
            ln = ln.strip()
            if ln and not ln.startswith("#") and "=" in ln:
                k, v = ln.split("=", 1)
                cfg[k.strip()] = v.strip()
    return cfg


def main():
    dry = "--dry-run" in sys.argv
    sites, pages = fetch()
    html = render(sites, pages)
    if dry:
        sys.stdout.write(html)
        return
    cfg = load_config()
    missing = [k for k in ("SMTP_HOST", "SMTP_PORT", "SMTP_USER", "SMTP_PASS", "MAIL_TO") if not cfg.get(k)]
    if missing:
        sys.exit(f"missing config in {CONFIG}: {', '.join(missing)}")
    day = (datetime.now(TZ) - timedelta(days=1)).strftime("%b %-d")
    msg = MIMEText(html, "html", "utf-8")
    msg["Subject"] = f"Umami daily report — {day}"
    msg["From"] = cfg.get("MAIL_FROM", cfg["SMTP_USER"])
    msg["To"] = cfg["MAIL_TO"]
    msg["Date"] = formatdate(localtime=True)
    with smtplib.SMTP(cfg["SMTP_HOST"], int(cfg["SMTP_PORT"])) as s:
        s.starttls()
        s.login(cfg["SMTP_USER"], cfg["SMTP_PASS"])
        s.sendmail(msg["From"], [a.strip() for a in cfg["MAIL_TO"].split(",")], msg.as_string())
    print(f"sent to {cfg['MAIL_TO']}")


if __name__ == "__main__":
    main()