Building an Automated Market Research Report Generator with DuckDB
Have you noticed a lucrative opportunity? Many small e-commerce sellers, independent brands, and even traditional enterprises spend thousands of yuan every month on market research. But they don’t need McKinsey-level deep analysis — they need fast, accurate, and cost-effective market trend analysis.
This is your chance.
Today I’ll teach you how to build an “Automated Market Research Report Generator” using DuckDB. You just need to provide raw data (from public APIs, web scraping, or user-uploaded CSVs), and DuckDB will automatically handle data cleaning, trend analysis, competitor comparison, and finally generate a professional Markdown/PDF report that’s ready to sell.
This approach has been proven — developers have built “cross-border e-commerce product selection reports” as a service, selling each report for 99-499 yuan and earning tens of thousands monthly.
Why Choose DuckDB?
The traditional market research workflow is: collect data → import into Excel/Python → manual analysis → write report. This process takes days and is error-prone.
With DuckDB, the approach is completely different:
- Data sources (CSV/JSON/API) → read directly in one line
- All analysis (trends, comparisons, statistics) → pure SQL queries
- Report generation → SQL results concatenated into Markdown
The entire process can be completed in 5 minutes, and it’s fully automated every time. Compared to traditional approaches, efficiency improves by more than 10x.
| Dimension | Traditional Excel/Manual | Pandas + Jupyter | DuckDB |
|---|---|---|---|
| Data processing speed | Slow (single-threaded) | Medium | Very fast (vectorized) |
| Memory usage | High (Excel limits) | Medium (RAM-limited) | Low (streaming) |
| Learning curve | Low | Medium | Low (SQL only) |
| Automation level | Manual | Semi-automated | Fully automated |
| Deployment difficulty | High | High | Minimal |
Step 1: Prepare Sample Data
First, let’s simulate an e-commerce sales dataset (you can replace this with real data):
import duckdb
from datetime import datetime, timedelta
import random
# Create DuckDB database
con = duckdb.connect("market_research.db")
# Generate simulated monthly category sales data (past 12 months)
data = []
categories = ["Digital Accessories", "Home Goods", "Beauty & Skincare",
"Sports & Outdoors", "Food & Beverages"]
months = [(datetime(2025, 8, 1) + timedelta(days=30*i)).strftime("%Y-%m") for i in range(12)]
random.seed(42)
for cat in categories:
base_sales = random.randint(50000, 200000)
for month in months:
growth = random.uniform(-0.1, 0.25) # -10% to +25% growth
sales = int(base_sales * (1 + growth))
orders = random.randint(sales // 200, sales // 50)
avg_price = round(sales / max(orders, 1), 2)
data.append((month, cat, sales, orders, avg_price))
# Write to table
con.execute("""
CREATE TABLE monthly_sales AS
SELECT * FROM data
WITH (month VARCHAR, category VARCHAR, total_sales BIGINT,
order_count BIGINT, avg_price DOUBLE)
""")
print("✅ Sample data generated")
con.close()
This code generates simulated sales data for 5 categories × 12 months, including three core metrics: total sales, order count, and average unit price.
Step 2: Core Analysis Queries
This is the soul of the system — completing all key analyses with a few SQL queries.
1. Monthly Sales Trends by Category
SELECT
month,
category,
total_sales,
ROUND(total_sales / LAG(total_sales) OVER (PARTITION BY category ORDER BY month) * 100 - 100, 1) AS mom_growth_pct
FROM monthly_sales
ORDER BY category, month;
Here we use the LAG() window function to calculate month-over-month growth rates — the core metric for identifying trends.
2. Monthly Category Rankings and Share Analysis
SELECT
category,
total_sales,
ROUND(100.0 * total_sales / SUM(total_sales) OVER (), 2) AS share_pct,
RANK() OVER (ORDER BY total_sales DESC) AS rank_num
FROM monthly_sales
WHERE month = (SELECT MAX(month) FROM monthly_sales);
SUM() OVER () calculates total sales, while RANK() performs rankings. These window functions make complex aggregation analysis concise.
3. High-Growth Category Identification
WITH growth_calc AS (
SELECT
category,
month,
total_sales,
ROUND(total_sales / LAG(total_sales) OVER (PARTITION BY category ORDER BY month) * 100 - 100, 1) AS mom_growth
FROM monthly_sales
),
consecutive_growth AS (
SELECT
category,
COUNT(*) AS consecutive_months_positive
FROM growth_calc
WHERE mom_growth > 0
GROUP BY category
HAVING COUNT(*) >= 3
)
SELECT
c.category,
c.consecutive_months_positive,
g.total_sales AS latest_sales,
ROUND(AVG(g.mom_growth), 1) AS avg_growth_pct
FROM consecutive_growth c
JOIN growth_calc g ON c.category = g.category
WHERE g.month = (SELECT MAX(month) FROM monthly_sales)
ORDER BY g.mom_growth DESC;
Using CTEs (Common Table Expressions) to combine multi-layer logic, identifying “potential tracks” with positive growth for 3+ consecutive months.
4. Average Unit Price Trend
SELECT
category,
ROUND(AVG(avg_price), 2) AS current_avg_price,
ROUND(MIN(avg_price), 2) AS min_price_12m,
ROUND(MAX(avg_price), 2) AS max_price_12m,
ROUND(STDDEV(avg_price), 2) AS price_volatility
FROM monthly_sales
GROUP BY category
ORDER BY price_volatility DESC;
Categories with the highest price volatility often signal market opportunities or risk indicators.
Step 3: Auto-Generate Markdown Reports
This is where the real value lies — automatically assembling analysis results into a professional research report:
import duckdb
from datetime import datetime
from pathlib import Path
def generate_report(db_path="market_research.db", output_dir="./reports"):
con = duckdb.connect(db_path)
# Get latest month
latest_month = con.execute(
"SELECT MAX(month) FROM monthly_sales"
).fetchone()[0]
prev_month = con.execute(
"SELECT month FROM monthly_sales WHERE month < '{}' ORDER BY month DESC LIMIT 1".format(latest_month)
).fetchone()[0]
# Build report content
report = f"""# 📊 Market Trend Research Report
**Report Period**: {prev_month} ~ {latest_month}
**Generated**: {datetime.now().strftime('%Y-%m-%d %H:%M')}
**Data Source**: Public e-commerce sales data
---
## Key Findings
### 🔥 Three Key Trends This Period
"""
# Trend 1: Top growing categories
top_growth = con.execute("""
SELECT category, total_sales, mom_growth_pct
FROM (
SELECT *,
ROUND(total_sales / LAG(total_sales) OVER (PARTITION BY category ORDER BY month) * 100 - 100, 1) AS mom_growth_pct
FROM monthly_sales
WHERE month = '{latest}'
)
ORDER BY mom_growth_pct DESC
LIMIT 3
""".format(latest=latest_month)).fetchall()
report += "#### 1. Top 3 High-Growth Categories\n\n"
for row in top_growth:
report += f"- **{row[0]}**: Monthly sales {row[1]:,}, MoM growth {row[2]}%\n"
report += "\n"
# Trend 2: Market share distribution
shares = con.execute("""
SELECT category, share_pct, rank_num
FROM (
SELECT
category,
ROUND(100.0 * total_sales / SUM(total_sales) OVER (), 2) AS share_pct,
RANK() OVER (ORDER BY total_sales DESC) AS rank_num
FROM monthly_sales
WHERE month = '{}'
)
ORDER BY rank_num
""".format(latest_month)).fetchall()
report += "#### 2. Market Share Distribution\n\n"
for row in shares:
bar = "█" * int(row[1] / 2)
report += f"- {row[0]}: {row[1]}% {bar}\n"
report += "\n"
# Trend 3: Price volatility signal
volatility = con.execute("""
SELECT category, ROUND(AVG(avg_price), 2) AS current_price, price_volatility
FROM (
SELECT category, avg_price,
ROUND(STDDEV(avg_price), 2) AS price_volatility
FROM monthly_sales
GROUP BY category
)
ORDER BY price_volatility DESC
LIMIT 1
""").fetchone()
if volatility:
report += f"#### 3. Price Volatility Signal\n\n"
report += f"- **{volatility[0]}** has the highest price volatility (stddev {volatility[2]}), pricing strategy changes should be monitored\n\n"
# Detailed data appendix
report += "---\n\n## Appendix: Complete Data Summary\n\n"
# Category summary
summary = con.execute("""
SELECT
category,
SUM(total_sales) AS total_12m_sales,
AVG(mom_growth_pct) AS avg_mom_growth,
ROUND(AVG(avg_price), 2) AS avg_unit_price
FROM (
SELECT *,
ROUND(total_sales / LAG(total_sales) OVER (PARTITION BY category ORDER BY month) * 100 - 100, 1) AS mom_growth_pct
FROM monthly_sales
)
GROUP BY category
ORDER BY total_12m_sales DESC
""").fetchall()
report += "| Category | 12-Month Total | Avg MoM Growth | Avg Unit Price |\n"
report += "|----------|---------------|----------------|----------------|\n"
for row in summary:
report += f"| {row[0]} | {row[1]:,} | {row[2]:.1f}% | ¥{row[3]} |\n"
# Save report
Path(output_dir).mkdir(parents=True, exist_ok=True)
report_path = Path(output_dir) / f"report_{latest_month}.md"
report_path.write_text(report, encoding="utf-8")
print(f"✅ Report saved: {report_path}")
con.close()
generate_report()
Advanced: Extending to Real Data Sources
The above example uses simulated data, but in practice, you can easily integrate various data sources:
Reading from CSV Files
# Read local CSV directly without loading into memory
con.read_csv_auto("sales_data.csv")
Fetching JSON Data from HTTP API
import requests
response = requests.get("https://api.example.com/sales")
data = response.json()
# Register JSON data as DuckDB table
con.register('api_data', data)
Querying Parquet Files
# Parquet is ideal for storing large-scale historical data
con.execute("SELECT * FROM read_parquet('sales_*.parquet')")
Monetization Ideas
The commercial value of this automated report generator is significant:
- Cross-border E-commerce Product Selection Reports: Sell each for 99-499 yuan to Amazon/eBay/Shopify sellers
- Industry Trend Weekly Subscriptions: Subscription model at 299-999 yuan/month for investment firms
- Enterprise Internal Data Products: Provide monthly business analysis reports for traditional companies
- SaaS Backend Engine: Use as the analysis engine for data products, charging per call
The key is to identify your target customer group, then use DuckDB to quickly build a minimum viable product (MVP). The entire system from development to deployment can be completed by an experienced developer in one day.
Summary
Building an automated market research report generator with DuckDB offers these core advantages:
- SQL-driven: All analysis logic expressed in SQL — concise and maintainable
- High performance: Vectorized execution engine responds in seconds for millions of records
- Easy deployment: A single Python script runs everything, no complex infrastructure needed
- Extensible: Supports CSV/JSON/Parquet/HTTP and multiple data sources
If you’re looking for a direction to monetize your technical skills, this solution is worth exploring deeply.
💡 Want to dive deeper into DuckDB for data products? duckdblab.org has a complete tutorial series covering everything from beginner to advanced实战.