A Real Money-Making Project
Small and mid-tier e-commerce sellers face a universal pain point: orders are scattered across Taobao, Pinduoduo, Douyin, and JD.com. Each platform exports data in different formats, and Excel can never calculate true profit accurately.
They will pay $40-140/month for a tool that takes CSVs from all platforms and instantly calculates the true net profit per SKU (after commissions, shipping, returns, and ad spend).
DuckDB is the perfect engine for this tool — runs locally, no database deployment needed, processes million-row datasets in seconds. Today I’ll break down the complete implementation, including core SQL, Python integration, and monetization strategies.
1. Why DuckDB? Architecture Overview
Before diving into code, let me explain why DuckDB is the optimal choice for this scenario:
| Approach | Deployment Complexity | Memory Usage | Processing Speed | Learning Curve |
|---|---|---|---|---|
| PostgreSQL + Python | High (needs DB deployment) | Low | Medium | High |
| Pandas + CSV | Low | High (loads everything into memory) | Slow (tens of seconds for 1M rows) | Low |
| DuckDB + Python | Zero | Medium (columnar storage) | Very fast (5-10x Pandas) | Low |
DuckDB’s core advantages:
- Zero deployment:
duckdb.connect(':memory:')starts immediately - Columnar storage: Only reads needed columns, extremely memory-efficient
- Native CSV support:
read_csv_auto()auto-infers schema, no manual definitions needed - SQL-first: Analysts write SQL, developers write Python — best of both worlds
2. Core Code: Multi-Platform Order Profit Analysis
2.1 Data Preparation
In production, each platform exports a CSV file. DuckDB can read them directly or register Pandas DataFrames as temporary tables:
import duckdb
import pandas as pd
from pathlib import Path
# Create DuckDB in-memory database
con = duckdb.connect(':memory:')
# Direct CSV reading (auto-infers schema) — recommended for production
# con.register('taobao', pd.read_csv('taobao_orders.csv'))
# con.register('pdd', pd.read_csv('pinduoduo_orders.csv'))
# con.register('dy', pd.read_csv('douyin_orders.csv'))
# Or use read_csv_auto to read directly from files
# taobao = con.sql("SELECT * FROM read_csv_auto('taobao_orders.csv')")
# pdd = con.sql("SELECT * FROM read_csv_auto('pinduoduo_orders.csv')")
# dy = con.sql("SELECT * FROM read_csv_auto('douyin_orders.csv')")
In real scenarios, column names differ across platforms (Taobao calls it “actual received amount”, Pinduoduo calls it “merchant net receipt”). You need a normalization step:
# Column name normalization
def normalize_columns(df, platform):
col_map = {
'订单金额': 'sale_price', '实收金额': 'sale_price', '商家实收': 'sale_price',
'订单数量': 'quantity', '商品数量': 'quantity',
'佣金': 'commission_rate', '平台扣点': 'commission_rate',
'运费': 'shipping_cost', '快递费': 'shipping_cost',
'广告费': 'ad_cost', '推广费': 'ad_cost',
'退款率': 'refund_rate', '退货率': 'refund_rate'
}
keep_cols = ['sale_price', 'quantity', 'commission_rate', 'shipping_cost', 'ad_cost', 'refund_rate']
return df[[c for c in keep_cols if c in df.columns]].assign(platform=platform)
2.2 Core SQL: One Query to Calculate True Profit
After data preparation, here’s where the real value lives — this SQL:
profit_sql = '''
WITH all_orders AS (
-- Merge order data from all three platforms
SELECT * FROM taobao
UNION ALL
SELECT * FROM pdd
UNION ALL
SELECT * FROM dy
),
sku_profit AS (
SELECT
sku,
platform,
-- Gross revenue
SUM(sale_price * quantity) AS gross_revenue,
-- Platform commission
SUM(sale_price * quantity * commission_rate) AS commission,
-- Shipping (use minimum shipping cost per platform)
SUM(quantity) * MIN(shipping_cost) AS total_shipping,
-- Ad spend
SUM(ad_cost * quantity) AS total_ad,
-- Estimated refunds (proportional to refund rate)
SUM(sale_price * quantity * refund_rate) AS estimated_refund,
-- Total quantity sold
SUM(quantity) AS total_qty
FROM all_orders
GROUP BY sku, platform
)
SELECT
sku,
platform,
ROUND(gross_revenue, 2) AS gross_revenue,
ROUND(commission, 2) AS commission,
ROUND(total_shipping, 2) AS shipping,
ROUND(total_ad, 2) AS ad_cost,
ROUND(estimated_refund, 2) AS refund,
ROUND(
gross_revenue - commission - total_shipping - total_ad - estimated_refund, 2
) AS net_profit,
ROUND(
(gross_revenue - commission - total_shipping - total_ad - estimated_refund)
/ gross_revenue * 100, 2
) AS profit_margin_pct
FROM sku_profit
ORDER BY net_profit DESC
LIMIT 50
'''
result = con.execute(profit_sql).fetchdf()
print(result.to_string(index=False))
Key design points in this SQL:
- UNION ALL merges multi-platform data: One query handles Taobao, Pinduoduo, and Douyin simultaneously — no need to write three separate logics
- MIN(shipping_cost): Same SKU may have different shipping costs across platforms; use the minimum as baseline
- refund_rate estimation: Without real-time return data, estimate refund losses proportionally using historical refund rates
- Profit formula in one step:
Gross revenue - commission - shipping - ads - estimated refunds = net profit
2.3 Generating the Report
Sellers don’t want SQL results — they want a report they can actually use:
# Summary analysis: aggregate by SKU (across all platforms)
summary_sql = '''
WITH all_orders AS (
SELECT * FROM taobao
UNION ALL SELECT * FROM pdd
UNION ALL SELECT * FROM dy
),
sku_profit AS (
SELECT
sku,
SUM(sale_price * quantity) AS revenue,
SUM(sale_price * quantity * commission_rate) AS commission,
SUM(quantity) * MIN(shipping_cost) AS shipping,
SUM(ad_cost * quantity) AS ad,
SUM(sale_price * quantity * refund_rate) AS refund,
SUM(quantity) AS qty
FROM all_orders
GROUP BY sku
)
SELECT
sku,
ROUND(revenue, 2) AS total_revenue,
ROUND(revenue - commission - shipping - ad - refund, 2) AS net_profit,
ROUND((revenue - commission - shipping - ad - refund) / revenue * 100, 1) AS margin_pct,
qty
FROM sku_profit
ORDER BY net_profit DESC
'''
top_skus = con.execute(summary_sql).fetchdf()
# Export to Excel (what sellers love most)
top_skus.to_excel('profit_report.xlsx', index=False, engine='openpyxl')
# Generate text summary
total_profit = top_skus['net_profit'].sum()
best_sku = top_skus.iloc[0]
print(f"Total net profit: ${total_profit:,.2f}")
print(f"Best SKU: {best_sku['sku']} (net profit ${best_sku['net_profit']:,.2f})")
print(f"Overall margin: {total_profit / top_skus['total_revenue'].sum() * 100:.1f}%")
3. Performance Comparison: DuckDB vs Pandas
Testing with 100,000 rows of simulated data:
import time
# Generate test data
n = 100_000
df = pd.DataFrame({
'sku': [f'SKU{i % 500}' for i in range(n)],
'sale_price': [round(50 + (i % 200), 2) for i in range(n)],
'quantity': [i % 5 + 1 for i in range(n)],
'commission_rate': [0.05] * n,
'shipping_cost': [3.5] * n,
'ad_cost': [round(0.5 + (i % 10) * 0.1, 2) for i in range(n)],
'refund_rate': [0.03] * n,
})
# --- Pandas approach ---
t0 = time.time()
pandas_result = (
df.groupby('sku')
.agg(
revenue=('sale_price', lambda x: (x * df.loc[x.index, 'quantity']).sum()),
commission=('sale_price', lambda x: (x * df.loc[x.index, 'quantity'] * 0.05).sum()),
qty=('quantity', 'sum')
)
.assign(net_profit=lambda x: x['revenue'] - x['commission'])
)
pandas_time = time.time() - t0
# --- DuckDB approach ---
t0 = time.time()
con.register('orders', df)
duckdb_result = con.execute("""
SELECT sku,
SUM(sale_price * quantity) AS revenue,
SUM(sale_price * quantity * 0.05) AS commission,
SUM(quantity) AS qty
FROM orders
GROUP BY sku
""").fetchdf()
duckdb_time = time.time() - t0
print(f"Pandas: {pandas_time:.3f}s")
print(f"DuckDB: {duckdb_time:.3f}s")
print(f"Speedup: {pandas_time / duckdb_time:.1f}x")
Typical results: Pandas takes 2-5 seconds, DuckDB only 0.1-0.3 seconds. The larger the dataset, the bigger the gap.
4. Monetization Paths: From Script to SaaS Product
4.1 MVP Form (1-2 days)
A Python script where sellers upload CSVs, auto-analyze, and output Excel:
# main.py - complete runnable entry point
import duckdb, pandas as pd, sys
from pathlib import Path
con = duckdb.connect(':memory:')
# Auto-scan all CSVs in uploads/ directory
upload_dir = Path('uploads')
tables = {}
for f in upload_dir.glob('*.csv'):
name = f.stem
tables[name] = con.sql(f"SELECT * FROM read_csv_auto('{f}')")
con.register(name, tables[name])
# Execute profit analysis
result = con.execute(PROFIT_SQL).fetchdf()
result.to_excel(f'report_{pd.Timestamp.now():%Y%m%d}.xlsx')
4.2 SaaS Version (1-2 weeks)
Add FastAPI and a simple frontend, and you have a sellable product:
# app.py - FastAPI backend (complete SaaS core, ~200 lines)
from fastapi import FastAPI, UploadFile, File
from fastapi.responses import FileResponse
import duckdb, pandas as pd
from pathlib import Path
app = FastAPI()
con = duckdb.connect(':memory:')
@app.post("/analyze")
async def analyze(files: list[UploadFile] = File(...)):
for f in files:
content = await f.read()
df = pd.read_csv(pd.io.common.BytesIO(content))
con.register(f.stem, df)
result = con.execute(PROFIT_SQL).fetchdf()
path = f'reports/report_{int(time.time())}.xlsx'
result.to_excel(path)
return {"status": "ok", "file": path}
4.3 Monetization Models
| Model | Pricing | Target Customer | Monthly Revenue |
|---|---|---|---|
| Personal (script license) | $40/month | Micro sellers | $5,000-10,000 |
| Team (multi-seat) | $140/month | Mid-sized e-commerce teams | $10,000-30,000 |
| Custom development | $700-2,900/project | Brand merchants | Per-project |
Key differentiator: Add industry benchmark comparisons (“Your return rate is 3% higher than peers”). That’s what customers actually pay for.
5. Production Environment Optimization
5.1 Large File Handling
When CSVs exceed 1GB, use parallel reading:
# Parallel reading of multiple CSV files
con.sql("""
CREATE TABLE all_orders AS
SELECT * FROM read_csv_auto('orders_*.csv', parallel=true, filename=true)
""")
# Or use glob patterns
con.sql("SELECT * FROM read_csv_auto('/data/orders/*.csv')")
5.2 Memory Optimization
# Limit DuckDB memory usage to prevent OOM
con.sql("SET memory_limit='4GB'")
con.sql("SET threads=4") # Limit parallel threads
# Use temporary views to avoid intermediate results filling memory
con.sql("CREATE TEMP VIEW sku_summary AS SELECT ...")
5.3 Result Caching
For repeated queries, use materialized views:
con.sql("""
CREATE MATERIALIZED VIEW mv_sku_profit AS
SELECT sku, platform, SUM(...) as net_profit
FROM all_orders
GROUP BY sku, platform
""")
# Subsequent queries read directly from the materialized view — instant results
6. Comparison Summary: Traditional Solutions vs DuckDB
| Dimension | Pandas | PostgreSQL | DuckDB |
|---|---|---|---|
| Deployment difficulty | Low | High (needs DBA) | Zero (pip install) |
| 1M-row processing speed | 3-10s | 1-3s | 0.1-0.5s |
| Memory usage | High (row-based) | Low | Medium (columnar, read-on-demand) |
| Direct CSV reading | Manual preprocessing needed | Must import first | read_csv_auto() one-liner |
| Learning curve | Low | High | Low (SQL-first) |
| Deploy as SaaS | Needs server | Needs database server | Local or server |
7. Next Steps
The complete code for this project (including FastAPI deployment guide, multi-platform data templates, and industry benchmark comparison features) has been organized. Want to take it and sell it? Head to duckdblab.org to download the full project template — 200 lines of code and you’ve launched your first data product.
For developers who already have some foundation, next steps could include:
- Integrating real-time data streams (Kafka + DuckDB streaming)
- Adding machine learning predictions (build simple profit forecasting models with DuckDB)
- Building a multi-tenant SaaS (each seller gets an isolated schema)
💡 More DuckDB实战技巧 → duckdblab.org