Build an Automated Daily Report System with DuckDB and Earn 5000 RMB Extra per Month

In the data analysis industry, the most valuable skill isn’t writing SQL — it’s turning repetitive labor into automated products. Today we’ll break down a real, actionable side hustle: building an automated daily report system with DuckDB + Python, selling it to small and medium businesses, and collecting monthly subscription fees.

The Business Opportunity

Many small and medium businesses need to track daily sales, traffic, and conversion metrics. Currently, either the boss blindly reads Excel spreadsheets, or they hire an assistant to manually export data. Your opportunity: build them a fully automated system that produces reports daily and pushes them to WeChat, email, or Telegram.

Traditional Approach vs. DuckDB Approach

Traditional Approach:

• Export data to local Excel
• Process with Excel or hand-written VBA
• Takes hours, prone to errors
• Requires manual intervention every time

DuckDB Approach:

• Connect directly to databases, compute in place
• Python script handles scheduling automatically
• Aggregate millions of rows in 1-2 seconds
• Zero manual intervention, runs 24/7

The core advantage is in-place computation — DuckDB can connect directly to MySQL, PostgreSQL, SQLite and other data sources without moving data locally. Millions of rows aggregate in 1-2 seconds in memory.

System Architecture

┌─────────────┐     ┌──────────────┐     ┌─────────────┐
│  MySQL/PG   │────▶│  DuckDB      │────▶│  Python     │
│  Data Source│     │  Compute     │     │  Schedule & │
└─────────────┘     └──────────────┘     │  Push       │
                                         └──────┬──────┘
                                                │
                                        ┌───────▼───────┐
                                        │  Email/Telegram│
                                        └───────────────┘

Core architecture:

• Data Source: MySQL / PostgreSQL / CSV files
• Compute Engine: DuckDB (seconds for millions of rows)
• Scheduler: Python schedule library
• Output: Markdown + charts, pushed to Telegram groups or email

Step 1: Connect to Data Source and Aggregate with DuckDB

Assume you’ve connected to an e-commerce order table with about 500,000 rows. Using DuckDB for daily aggregation requires just a few lines of code:

import duckdb
from datetime import datetime, timedelta

# Connect directly to MySQL, no data export needed
con = duckdb.connect('mysql://user:***@host:3306/shop', read_only=True)

# 7-day summary analysis
daily_report = con.execute("""
    SELECT 
        DATE(order_time) AS dt,
        COUNT(*) AS orders,
        SUM(amount) AS revenue,
        AVG(amount) AS avg_order_value,
        COUNT(DISTINCT user_id) AS active_users
    FROM orders
    WHERE order_time >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY DATE(order_time)
    ORDER BY dt DESC
""").fetchdf()

print(daily_report)

Key Tip: DuckDB natively supports connections to multiple databases with a unified connection string format. MySQL requires the duckdb-mysql extension, PostgreSQL similarly. For smaller datasets, you can also read CSV files directly: duckdb.read_csv('orders.csv').

Sample output:

           dt  orders   revenue  avg_order_value  active_users
0  2026-08-01   1250  89500.00        71.60           890
1  2026-07-31   1180  82300.00        69.75           845
2  2026-07-30   1320  95600.00        72.42           920
...

Step 2: Generate Visual Charts

With the data ready, use Matplotlib to generate charts:

import matplotlib.pyplot as plt
import matplotlib
matplotlib.use('Agg')  # Headless mode, server-friendly

fig, axes = plt.subplots(1, 2, figsize=(12, 5))

# Left: 7-day revenue trend
axes[0].plot(daily_report['dt'], daily_report['revenue'], 
             marker='o', color='#2E86AB', linewidth=2)
axes[0].fill_between(daily_report['dt'], daily_report['revenue'], 
                     alpha=0.2, color='#2E86AB')
axes[0].set_title('7-Day Revenue Trend')
axes[0].set_xlabel('Date')
axes[0].set_ylabel('Revenue (¥)')
axes[0].tick_params(axis='x', rotation=45)

# Right: Orders vs Avg Order Value scatter (bubble size = active users)
scatter = axes[1].scatter(
    daily_report['orders'], 
    daily_report['avg_order_value'], 
    s=daily_report['active_users'] * 2, 
    alpha=0.6, 
    c=daily_report['revenue'],
    cmap='viridis'
)
axes[1].set_title('Orders vs Avg Order Value')
axes[1].set_xlabel('Order Count')
axes[1].set_ylabel('Avg Order Value')
plt.colorbar(scatter, ax=axes[1], label='Revenue')

plt.tight_layout()
plt.savefig('/tmp/daily_report.png', dpi=120, bbox_inches='tight')

Step 3: Format the Report and Push

from datetime import datetime
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

def format_report(df):
    """Format DataFrame into a readable daily report"""
    today = df.iloc[0]
    yesterday = df.iloc[1] if len(df) > 1 else None
    
    report = f"""📊 Daily Sales Report · {today['dt'].strftime('%Y-%m-%d')}

Revenue: ¥{today['revenue']:,.2f}
Orders: {int(today['orders'])}
Avg Order Value: ¥{today['avg_order_value']:.2f}
Active Users: {int(today['active_users'])}

"""
    if yesterday is not None:
        rev_change = (today['revenue'] - yesterday['revenue']) / yesterday['revenue'] * 100
        sign = '📈' if rev_change > 0 else '📉'
        report += f"⚡ vs Yesterday: {sign} {abs(rev_change):.1f}%"
    
    return report

def send_report(report_text, image_path):
    """Send email (can be replaced with Telegram Bot API)"""
    msg = MIMEMultipart()
    msg['Subject'] = f"Daily Report · {datetime.now().strftime('%m-%d')}"
    msg.attach(MIMEText(report_text, 'plain', 'utf-8'))
    
    with open(image_path, 'rb') as f:
        from email.mime.image import MIMEImage
        img = MIMEImage(f.read())
        img.add_header('Content-ID', '<report>')
        msg.attach(img)
    
    # SMTP sending logic omitted, replace with your own config
    print(f"Report ready → {image_path}")
    print(report_text)

# Execute
report_text = format_report(daily_report)
send_report(report_text, '/tmp/daily_report.png')

Replace with Telegram Bot Push

If email feels too slow, use the Telegram Bot API instead:

import requests

def send_telegram(chat_id, text, photo_path=None):
    bot_token = "YOUR_BOT_TOKEN"
    url = f"https://api.telegram.org/bot{bot_token}"
    
    # Send text
    requests.post(f"{url}/sendMessage", json={
        "chat_id": chat_id,
        "text": text,
        "parse_mode": "HTML"
    })
    
    # Send photo
    if photo_path:
        with open(photo_path, 'rb') as f:
            requests.post(f"{url}/sendPhoto", json={"chat_id": chat_id}, files={"photo": f})

Step 4: Scheduled Execution + Multi-Client Isolation

import schedule
import time

def generate_daily_report(client_id):
    """Generate daily report for a single client"""
    con = duckdb.connect(f'mysql://user:***@host:3306/{client_id}', read_only=True)
    # ... execute query, generate charts, send report ...
    con.close()

# Auto-generate at 9:00 AM daily
schedule.every().day.at("09:00").do(generate_daily_report, client_id="client_001")
schedule.every().day.at("09:05").do(generate_daily_report, client_id="client_002")
schedule.every().day.at("09:10").do(generate_daily_report, client_id="client_003")

# Poll every 60 seconds, low overhead
while True:
    schedule.run_pending()
    time.sleep(60)

Each client gets an independent DuckDB connection config. Report content is filtered by client ID. Code reusability is extremely high.

DuckDB vs Traditional Approaches

Business Implementation Guide

Pricing Strategy

Customer Acquisition Channels

1. Xianyu/Taobao: List “custom data reports” services, drive traffic to private channels
2. Tech Communities: Post technical notes on V2EX, Jike, show effect screenshots
3. Free Trial: Do one free project for a local e-commerce store, get case studies and referrals
4. Community Ops: Share automated report screenshots in industry groups for organic leads

Cost Analysis

• Server: Aliyun/Tencent Cloud 1-core 1GB ≈ ¥50/month
• Development Time: 2-3 days for initial build, 1-2 hours per new client
• Maintenance Time: < 30 minutes per client per month
• Profit Margin: Basic tier ~90%, Advanced tier ~95%

In practice, a client takes 3-5 days from first contact to payment. Maintenance costs are extremely low (server + DuckDB runs locally, no third-party dependencies).

Advanced Directions

• Multi-source Fusion: Use DuckDB’s mysql_scanner() and postgres_scanner() to pull ERP and CRM data simultaneously for customer LTV analysis
• Auto Anomaly Detection: Use DuckDB’s built-in statistical functions for threshold alerts — notify the boss automatically when revenue drops unexpectedly
• PDF Export: Use reportlab to convert reports to formal PDFs, enhancing the premium feel
• Web Dashboard: Use Streamlit to build a visual dashboard where clients can query data themselves
• Docker Deployment: Package the entire system into a container for one-click deployment to client servers

Complete Code Repository

This project is open source, including:

• Multi-data source adapters (MySQL/PostgreSQL/CSV)
• Daily/weekly/monthly report templates
• Telegram Bot push integration
• Docker deployment configuration
• Multi-client isolation patterns

Visit duckdblab.org for the complete code and technical documentation.

💡 Monetization Summary: The core logic of this project is “trade technology for time, trade time for income.” One client at ¥299/month, 10 clients = ¥2990/month, while your maintenance cost is nearly zero. The key is finding small business owners who have data but don’t know how to use it — they’re willing to pay for “peace of mind.”

📖 The full version of this article (including Docker deployment, Telegram Bot push, multi-client templates) is published at duckdblab.org. Ready to use straight away.

🔍 Want to systematically learn DuckDB? Complete tutorial series available at duckdblab.org