How to Build a Daily Metrics Email with SQL and Python
Daily emails are the most underrated data tool. Everyone checks email. Not everyone opens a dashboard.
I've worked on teams with gorgeous Looker dashboards that got maybe three visits a week. Then someone set up a janky daily email with yesterday's numbers, and suddenly the entire leadership team was talking about metrics at standup. The difference isn't the data — it's the delivery channel. A dashboard requires intent. An email just shows up.
Here's how to build one that actually works: a single email every morning with yesterday's key metrics, how they compare to the day before, and how they compare to last week. Red when something's off, green when things are trending up. No BI tool required.
The SQL: one row per metric with context
The goal is a query that returns a neat table: metric name, yesterday's value, previous day's value, same day last week, and the percentage changes. One row per metric, ready for a script to consume.
WITH yesterday AS (
SELECT
current_date - 1 AS report_date,
SUM(CASE WHEN e.event_type = 'purchase' THEN e.amount ELSE 0 END) AS revenue,
COUNT(DISTINCT CASE WHEN u.created_at::date = current_date - 1 THEN u.id END) AS signups,
COUNT(DISTINCT e.user_id) AS active_users
FROM events e
LEFT JOIN users u ON u.id = e.user_id
WHERE e.created_at::date = current_date - 1
),
day_before AS (
SELECT
SUM(CASE WHEN e.event_type = 'purchase' THEN e.amount ELSE 0 END) AS revenue,
COUNT(DISTINCT CASE WHEN u.created_at::date = current_date - 2 THEN u.id END) AS signups,
COUNT(DISTINCT e.user_id) AS active_users
FROM events e
LEFT JOIN users u ON u.id = e.user_id
WHERE e.created_at::date = current_date - 2
),
last_week AS (
SELECT
SUM(CASE WHEN e.event_type = 'purchase' THEN e.amount ELSE 0 END) AS revenue,
COUNT(DISTINCT CASE WHEN u.created_at::date = current_date - 8 THEN u.id END) AS signups,
COUNT(DISTINCT e.user_id) AS active_users
FROM events e
LEFT JOIN users u ON u.id = e.user_id
WHERE e.created_at::date = current_date - 8
)
SELECT
metric_name,
current_value,
previous_value,
CASE WHEN previous_value = 0 THEN NULL
ELSE ROUND((current_value - previous_value) / previous_value * 100, 1)
END AS dod_pct_change,
wow_value,
CASE WHEN wow_value = 0 THEN NULL
ELSE ROUND((current_value - wow_value) / wow_value * 100, 1)
END AS wow_pct_change
FROM (
SELECT 'Revenue' AS metric_name, y.revenue AS current_value,
d.revenue AS previous_value, w.revenue AS wow_value
FROM yesterday y, day_before d, last_week w
UNION ALL
SELECT 'Signups', y.signups, d.signups, w.signups
FROM yesterday y, day_before d, last_week w
UNION ALL
SELECT 'Active Users', y.active_users, d.active_users, w.active_users
FROM yesterday y, day_before d, last_week w
) metrics;Three CTEs, one UNION ALL to stack the metrics vertically. You get back something like:
| metric_name | current_value | previous_value | dod_pct_change | wow_value | wow_pct_change |
|---|---|---|---|---|---|
| Revenue | 4280 | 3910 | 9.5 | 3650 | 17.3 |
| Signups | 34 | 41 | -17.1 | 28 | 21.4 |
| Active Users | 812 | 798 | 1.8 | 745 | 9.0 |
Want more metrics? Add another row to the UNION ALL. Failed payments, support tickets, trial conversions — whatever your team actually cares about.
The Python script: query, format, send
Here's a complete, runnable script. It connects to Postgres, runs the query above, builds an HTML email with conditional formatting, and sends it via SMTP.
# daily_metrics_email.py
import os
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from datetime import date
import psycopg2
DB_URL = os.environ["DATABASE_URL"]
SMTP_HOST = os.environ.get("SMTP_HOST", "smtp.gmail.com")
SMTP_PORT = int(os.environ.get("SMTP_PORT", "587"))
SMTP_USER = os.environ["SMTP_USER"]
SMTP_PASS = os.environ["SMTP_PASS"]
EMAIL_FROM = os.environ["EMAIL_FROM"]
EMAIL_TO = os.environ["EMAIL_TO"].split(",") # comma-separated list
METRICS_SQL = open("metrics_query.sql").read() # the SQL from above
def color_for_change(pct_change):
"""Red if down >10%, green if up >10%, gray otherwise."""
if pct_change is None:
return "#666"
if pct_change <= -10:
return "#d32f2f"
if pct_change >= 10:
return "#2e7d32"
return "#666"
def arrow_for_change(pct_change):
if pct_change is None:
return "—"
return "▲" if pct_change >= 0 else "▼"
def build_html(rows, report_date):
table_rows = ""
for name, current, previous, dod_pct, wow_val, wow_pct in rows:
dod_color = color_for_change(dod_pct)
wow_color = color_for_change(wow_pct)
dod_arrow = arrow_for_change(dod_pct)
wow_arrow = arrow_for_change(wow_pct)
dod_display = f"{dod_arrow} {abs(dod_pct):.1f}%" if dod_pct is not None else "—"
wow_display = f"{wow_arrow} {abs(wow_pct):.1f}%" if wow_pct is not None else "—"
current_fmt = f"${current:,.0f}" if name == "Revenue" else f"{current:,}"
table_rows += f"""
<tr>
<td style="padding:8px 12px;font-weight:600">{name}</td>
<td style="padding:8px 12px;text-align:right">{current_fmt}</td>
<td style="padding:8px 12px;text-align:right;color:{dod_color}">
{dod_display}
</td>
<td style="padding:8px 12px;text-align:right;color:{wow_color}">
{wow_display}
</td>
</tr>"""
return f"""
<div style="font-family:system-ui,sans-serif;max-width:520px;margin:0 auto">
<h2 style="margin-bottom:4px">Daily Metrics — {report_date.strftime('%b %d, %Y')}</h2>
<p style="color:#666;margin-top:0">Compared to previous day and same day last week</p>
<table style="border-collapse:collapse;width:100%">
<thead>
<tr style="border-bottom:2px solid #e0e0e0">
<th style="padding:8px 12px;text-align:left">Metric</th>
<th style="padding:8px 12px;text-align:right">Yesterday</th>
<th style="padding:8px 12px;text-align:right">vs Prev Day</th>
<th style="padding:8px 12px;text-align:right">vs Last Week</th>
</tr>
</thead>
<tbody>{table_rows}</tbody>
</table>
</div>"""
def send_email(html, report_date):
msg = MIMEMultipart("alternative")
msg["Subject"] = f"Daily Metrics — {report_date.strftime('%b %d')}"
msg["From"] = EMAIL_FROM
msg["To"] = ", ".join(EMAIL_TO)
msg.attach(MIMEText(html, "html"))
with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
server.starttls()
server.login(SMTP_USER, SMTP_PASS)
server.sendmail(EMAIL_FROM, EMAIL_TO, msg.as_string())
def main():
conn = psycopg2.connect(DB_URL)
cursor = conn.cursor()
cursor.execute(METRICS_SQL)
rows = cursor.fetchall()
conn.close()
report_date = date.today()
html = build_html(rows, report_date)
send_email(html, report_date)
print(f"Sent daily metrics email for {report_date} to {', '.join(EMAIL_TO)}")
if __name__ == "__main__":
main()The conditional formatting logic is intentionally simple: anything that moved more than 10% gets colored. Red for drops, green for gains. Below that threshold, it stays gray. You could refine this per-metric (a 10% revenue swing means something different than 10% in active users), but this gets you 90% of the way.
One thing to watch: Gmail's SMTP has a 500-email-per-day limit. For a team digest that's plenty, but if you're sending to a big distribution list, use SES or Resend instead.
Fastero
Connect your database. Ask questions. Get dashboards.
Postgres, BigQuery, Snowflake, and 10+ sources — live-connected, AI-powered, no dashboard builder learning curve.
Try free →The Slack variant
Some teams live in Slack, not email. Swapping out the delivery is straightforward — replace send_email with a webhook call and format the message with Slack's block kit.
import requests
SLACK_WEBHOOK = os.environ["SLACK_WEBHOOK_URL"]
def post_to_slack(rows, report_date):
lines = [f"*Daily Metrics — {report_date.strftime('%b %d, %Y')}*\n"]
for name, current, previous, dod_pct, wow_val, wow_pct in rows:
dod_arrow = "▲" if (dod_pct or 0) >= 0 else "▼"
wow_arrow = "▲" if (wow_pct or 0) >= 0 else "▼"
current_fmt = f"${current:,.0f}" if name == "Revenue" else f"{current:,}"
dod_str = f"{dod_arrow} {abs(dod_pct):.1f}%" if dod_pct is not None else "—"
wow_str = f"{wow_arrow} {abs(wow_pct):.1f}%" if wow_pct is not None else "—"
# bold the metric if day-over-day change exceeds 10%
highlight = "*" if dod_pct is not None and abs(dod_pct) > 10 else ""
lines.append(
f"{highlight}{name}{highlight}: {current_fmt} "
f"(DoD: {dod_str} | WoW: {wow_str})"
)
requests.post(SLACK_WEBHOOK, json={"text": "\n".join(lines)})Call post_to_slack(rows, report_date) instead of send_email(html, report_date). Or call both — the daily email for executives who live in Outlook, and the Slack post for the team that lives in channels. Fastero's Slack integration handles this natively if you'd rather not maintain webhook scripts.
Scheduling: cron vs. something that won't silently break
The classic approach: throw it on a cron job.
# Run at 7:00am UTC every day
0 7 * * * cd /home/deploy/metrics && /usr/bin/python3 daily_metrics_email.pyThis works until it doesn't. The three ways it fails:
Your server reboots and nobody re-enables the cron. The database connection string rotates and the script starts throwing OperationalError at 7am every day while nobody checks the cron logs. Or the simplest one — the person who set it up leaves, and six months later nobody knows where it runs or how to modify the query.
If you want something more durable, you have a few options. A GitHub Actions workflow on a cron schedule gives you logs and notifications on failure. AWS Lambda with EventBridge is serverless but you're managing deployment artifacts. Airflow is overkill for a single daily email.
Fastero takes a different approach. You paste the SQL into a scheduled report, pick the schedule, pick the recipients, and it handles the rest — execution, formatting, delivery, and retry on failure. The execution history is visible in the UI, so when someone asks "did the email go out yesterday?" you can answer without SSHing into anything. You can also run the Python script directly on Fastero's execution engine if you want the full custom formatting, with real compute resources and log streaming.
When to go beyond email
The daily email works best as a heartbeat — a quick pulse check that tells you whether things are roughly on track. If something looks off, you go dig in. It's not the place for deep analysis.
For anomaly-specific alerts (revenue dropped 20% in the last hour), you want real-time triggers that fire immediately, not a digest you read the next morning. The email is for "here's how yesterday went." The trigger is for "something is wrong right now."
A good setup uses both. The daily email covers trending context — day-over-day, week-over-week, directional health. The real-time alert covers acute problems. Between the two, you don't need anyone to manually check a dashboard to know whether the business is healthy.
You could also build a live KPI dashboard for the people who do want to drill in. But the email is what surfaces the signal to everyone else — the CFO who won't learn Looker, the sales lead who wants to know if signups are up, the support manager tracking ticket volume. Meet people where they already are.
Try Fastero free — schedule SQL reports to email and Slack with built-in formatting, conditional alerts, and zero infrastructure to manage. No credit card required.