Background: Weekly reports waste data professionals’ time every week
Every data team writes weekly reports. You manually export data, create charts, and compile insights — spending 2-3 hours per week. If you do this for multiple business lines, the time doubles.
We’ll build an fully automated weekly report system using DuckDB + Python: runs automatically every morning, produces reports, and sends them to Telegram. You save time AND can sell this capability to other teams.
Step 1: Prepare Test Data
Assume you have sales data in CSV format:
date,product,region,revenue
2024-01-01,iPhone,East,120000
2024-01-01,iPad,North,85000
Create a virtual CSV for testing:
import os
os.makedirs('/tmp/duckdb_weekly', exist_ok=True)
with open('/tmp/duckdb_weekly/sales.csv', 'w') as f:
import random
products = ['iPhone', 'iPad', 'MacBook', 'AirPods']
regions = ['East', 'North', 'South', 'West']
f.write('date,product,region,revenue\n')
for day in range(1, 32):
for _ in range(50):
f.write(f'2024-01-{day:02d},{random.choice(products)},{random.choice(regions)},{random.randint(5000, 50000)}\n')
print("Data generated")
Step 2: Core Query — Generate the Weekly Report
DuckDB’s SQL syntax solves 80% of the problem directly:
import duckdb
from datetime import datetime, timedelta
conn = duckdb.connect()
conn.execute("CREATE TABLE sales AS SELECT * FROM read_csv('/tmp/duckdb_weekly/sales.csv')")
end_date = datetime(2024, 1, 31)
start_date = end_date - timedelta(days=6)
weekly_report = conn.execute("""
SELECT
DATE(date) AS report_date,
product,
region,
SUM(revenue) AS total_revenue,
COUNT(*) AS order_count,
AVG(revenue) AS avg_order_value
FROM sales
WHERE date BETWEEN ? AND ?
GROUP BY 1, 2, 3
ORDER BY total_revenue DESC
""", str(start_date.date()), str(end_date.date())).fetchdf()
print(weekly_report.head(10))
The output of this query is the core data for your weekly report: revenue, order count, and average order value grouped by product and region.
Step 3: Generate Visualizations
import pandas as pd
import matplotlib.pyplot as plt
product_summary = weekly_report.groupby('product')['total_revenue'].sum().sort_values(ascending=False)
plt.figure(figsize=(10, 6))
product_summary.plot(kind='bar', color='steelblue')
plt.title('Weekly Revenue by Product')
plt.ylabel('Revenue (CNY)')
plt.xlabel('Product')
plt.tight_layout()
plt.savefig('/tmp/duckdb_weekly/chart.png', dpi=150)
print("Chart generated")
Step 4: Package as a Telegram Message
import telebot
BOT_TOKEN='***'
CHAT_ID = 'YOUR_CHAT_ID'
bot = telebot.TeleBot(BOT_TOKEN)
message = f"""
📊 Weekly Report Generated (2024-01-25 to 2024-01-31)
🏆 Top Products:
{weekly_report.head(3).to_string(index=False)}
📈 Total Revenue: {weekly_report['total_revenue'].sum():,.0f} CNY
📦 Total Orders: {weekly_report['order_count'].sum():,}
💰 Avg Order Value: {weekly_report['avg_order_value'].mean():,.0f} CNY
[View Full Report Attachment]
"""
bot.send_photo(CHAT_ID, open('/tmp/duckdb_weekly/chart.png', 'rb'), caption=message)
print("Report sent to Telegram")
Step 5: Automated Scheduling
Use cron to run automatically every Monday at 8 AM:
crontab -e
# Add this line (runs daily at 8:00 AM)
0 8 * * * cd /root && python3 weekly_report.py >> /tmp/duckdb_weekly/cron.log 2>&1
Or use Python’s APScheduler library for more flexibility:
from apscheduler.schedulers.background import BackgroundScheduler
import time
def run_weekly():
print(f"[{datetime.now()}] Starting weekly report...")
# Call the generation logic above
print("Weekly report generated")
scheduler = BackgroundScheduler()
scheduler.add_job(run_weekly, 'cron', day_of_week='mon-fri', hour=8, minute=0)
scheduler.start()
while True:
time.sleep(60)
Advanced: Multi-Source Data + Parquet Caching
In production, your data isn’t just one CSV. DuckDB supports reading Parquet, SQLite, and even remote HTTP endpoints directly:
# Read Parquet (pre-processed data, queries respond in milliseconds)
conn.execute("CREATE TABLE sales_pq AS SELECT * FROM read_parquet('/data/sales/*.parquet')")
# Read remote CSV (via httpfs extension)
conn.execute("INSTALL httpfs; LOAD httpfs;")
conn.execute("CREATE TABLE remote_sales AS SELECT * FROM read_csv_auto('https://api.example.com/sales.csv')")
# Cross-table JOIN
conn.execute("""
SELECT s.product, s.region, SUM(s.revenue) as revenue
FROM sales_pq s
JOIN remote_sales r ON s.order_id = r.id
GROUP BY 1, 2
""")
Advanced: Web Dashboard with Streamlit
import streamlit as st
import duckdb
st.title('Real-time Weekly Report Dashboard')
df = duckdb.query("SELECT * FROM read_csv('sales.csv')").df()
st.dataframe(df)
st.line_chart(df.set_index('date')['revenue'])
Monetization Paths: From Tool to Product
- Internal Tool: Use it yourself → saves time → 2 CNY/hour × 52 weeks = 100K+ CNY/year saved
- SaaS Product: Multi-tenant service, 99 CNY/month per company → 100 companies = 120K CNY/year
- Outsourcing Service: Build for SMEs, charge 2,000-5,000 CNY per project
- Open Source引流: Open-source the code on GitHub, monetize via consulting and training
Traditional Approach vs DuckDB Approach
Traditional approach requires MySQL + Airflow + Jupyter + email services — long development cycle, high maintenance cost. DuckDB approach: one script, zero operations. Columnar storage makes aggregation 10x faster than pandas, and no database service installation needed.
The complete runnable code and project template are published at duckdblab.org, with more detailed steps and additional cases. Learn more DuckDB practical experience → duckdblab.org