Back to Projects
Business Intelligence

Business Intelligence Reporting System

Modular Python/Jupyter pipeline that automates business review report generation from fragmented data sources, transforming newsletter analytics, sales metrics, and website data into executive-ready Weekly, Monthly, and Quarterly reports.

Python Jupyter Notebooks pandas matplotlib python-docx numpy

Skills Demonstrated

Analytics Engineering Report Automation Data Pipeline Design Metric Standardization Business Intelligence Process Automation
December 2025
Version 1.0.0
~21,000 records · 13 source files
English

Project Overview

The Business Intelligence Reporting System eliminates manual report compilation by automating the entire workflow from raw data ingestion through analysis to formatted report generation. Built with a modular analytics engineering approach, this Python-based system transforms fragmented data from email platforms, website analytics, CRM tools, and sales trackers into professional Weekly Business Reviews (WBR), Monthly Business Reviews (MBR), and Quarterly Business Reviews (QBR).

13Jupyter Notebooks
3Report Types Generated
4+Data Sources Integrated
50+Metrics Automated

🎯 Core Innovation: Modular Analytics Engineering Architecture

Instead of monolithic scripts, the system uses 13 specialized notebooks where each handles one data domain (newsletter, sales, forms, etc.). Notebooks output CSV files as intermediate results — a pattern borrowed from modern data warehouses — enabling independent testing, easy debugging, and reproducible report generation. This "One Notebook = One Data Domain = One Set of Outputs" philosophy demonstrates analytics engineering best practices at portfolio scale.

📊 Quantified Business Impact

94%

Time Reduction
From 8 hours to 30 minutes

100%

Eliminated Manual Copy-Paste
Zero manual data entry

50+

Standardized Metrics
Across WBR/MBR/QBR

Same‑Day

Insight Delivery
Down from next-day

Result: Reduced calculation inconsistencies to zero while enabling faster, data-driven decision-making.

⏱️ The Automation Story: 8 Hours → 30 Minutes

❌ Before: Manual Process

~8 hours/week (WBR alone)

  1. Download raw data from 6+ platforms
  2. Clean data manually
  3. Sort & categorize by date and type
  4. Compute metrics in spreadsheets
  5. Create charts manually
  6. Build comparison tables
  7. Assemble final Word document

Error-prone, tedious, inconsistent formulas, copy-paste mistakes

✅ After: Automated Pipeline

~30 minutes/week

  1. Download raw data exports (5–10 min)
  2. Place files in data/ folder (1 min)
  3. Update date config file (1 min)
  4. Run notebooks 01–13 (15 min)

Steps 2–7 above are fully automated. Just run the code.

🤖 What the 13 Notebooks Automate

Notebooks 01–08: Data Processing

  • Load raw CSV/Excel files
  • Clean & normalize data
  • Combine multiple sources by date
  • Categorize by type, series, source
  • Aggregate to daily/weekly/monthly/quarterly
  • Compute all metrics
  • Generate PNG charts for each metric
  • Save processed CSVs to outputs/

Notebooks 09–10: Analysis Tables

  • Create WoW and MoM comparison tables
  • Calculate period-over-period changes
  • Format tables for report insertion

Notebooks 11–13: Report Generators

  • Generate narrative insights
  • Assemble into formatted .docx
  • Output: WBR/MBR/QBR reports ready to share

Business Problems Solved

📋

Fragmented Data Sources

Newsletter analytics in Beehiiv, website metrics in Fathom Analytics, sales data in Google Sheets, call scheduling in Calendly — each system generates its own exports with different formats and structures.

⏱️

Manual Copy-Paste Work

Creating weekly/monthly reports manually requires hours of downloading CSVs, copying metrics into templates, calculating period-over-period changes, and formatting charts.

📉

Inconsistent Analysis

Manual calculations lead to formula errors, inconsistent time period definitions, and varying interpretations of metric definitions across reports.

🐢

Delayed Insights

By the time data is compiled, analyzed, and formatted into a report, business conditions have already changed — making the insights less actionable.

Decisions Enabled by This System

Beyond reporting metrics, this system provides decision-support insights that help leadership take action:

📧

Newsletter Strategy

Identifies churn spikes before they impact monthly growth

  • Which email series has highest unsubscribe rates?
  • Is subscriber growth keeping pace with targets?
  • Are open rates trending up or just fluctuating?
📉

Funnel Optimization

Flags leakage between scheduled → live → offer stages

  • Which funnel step is the weakest link?
  • Is show rate dropping due to scheduling or interest?
  • Where should we invest to improve conversion?
📊

Signal vs. Noise

Shows whether performance changes are real trends or variance

  • Is this week's drop a trend or just weekly fluctuation?
  • Rolling averages smooth noise to reveal patterns
  • Trend lines show directional movement over time
🎯

Goal Tracking

Helps leadership decide if targets are realistically on track

  • Are we ahead or behind monthly goals?
  • Which metrics need immediate attention?
  • What's the gap between actual and target performance?

🌟 Key Differentiator: Metrics That Explain Themselves

Unlike dashboards that require interpretation, every chart in this system is paired with an auto-generated narrative explaining what happened, why it matters, and whether action is needed.

Each insight block automatically includes:

Result: Stakeholders receive actionable insights, not just charts — enabling faster decision-making without requiring deep data expertise.

Solution: Automated Report Generation

The system generates three types of business review reports, each with consistent structure but different time horizons:

Report Type Frequency Time Comparison Rolling Average Primary Focus
WBR (Weekly Business Review) Weekly Week-over-Week (WoW) 6 weeks Operational performance, short-term trends
MBR (Monthly Business Review) Monthly Month-over-Month (MoM) 6 months Goal tracking, medium-term trends
QBR (Quarterly Business Review) Quarterly Quarter-over-Quarter (QoQ) 6 quarters Strategic review, long-term performance

📝 Report Structure (Consistent Across WBR/MBR/QBR)

  1. Cover Page — Report type, date range, generation timestamp
  2. Executive Summary — Key metrics table with favorable/unfavorable indicators
  3. Newsletter Section — Landing page funnel, engagement metrics, subscriber growth/churn, series breakdown
  4. Sales Section — Lead time tracking, conversion funnel charts, call metrics, pipeline performance
  5. Insight Blocks — Each chart includes narrative explaining current vs. previous period, rolling average, trend direction, and favorability

How Each Notebook Automates the Process

These 13 notebooks transform raw, messy data into polished reports automatically. Here's what each one does:

01

Newsletter Series

Input: Raw email campaign data from Beehiiv

  • Categorizes emails into series (Deal, Off-Market, Podcast, Case Study)
  • Computes open rates, CTR, unsubscribe rates per series
  • Aggregates daily → weekly → monthly → quarterly → annually
  • Generates trend charts and saves categorized CSVs

Before: Manually sorting 100s of emails → After: Run one notebook

02

Newsletter Landing Page

Input: Fathom Analytics export (timestamped pageviews)

  • Combines multiple timestamp entries into daily totals
  • Computes pageviews, unique visits, session duration, bounce rate
  • Creates weekly/monthly/quarterly aggregations and charts

Before: Excel pivot tables & manual charts → After: Automated

03

Deal Upgrade Page

Input: Two Fathom files (page visits + conversion events)

  • Merges visit data and event data by date
  • Calculates conversion rate (events / visits)
  • Creates conversion funnel charts across all time periods
04

Pro Site

Input: Two Fathom files (Pro site visits + events)

Same logic as notebook 03 but for the Pro tier — tracks Pro site visits and upgrade conversions independently.

05

Discovery / Intro Calls

Input: Calendly event data

  • Categorizes calls by type (Discovery vs Intro)
  • Computes booked calls, completed calls, no-shows
  • Calculates average lead time (booking → call date)
  • Generates charts for call metrics
06

Sales Tracker

Input: Sales pipeline Google Sheet export

  • Processes full sales funnel: Scheduled → Live → Offers → Closes
  • Calculates show rate, offer rate, close rate
  • Aggregates revenue and deal counts
  • Creates pipeline performance charts
07

Typeform Submissions

Input: Raw Typeform export (messy, many columns)

  • Removes unnecessary rows/columns, fixes date formats
  • Extracts needed metrics, categorizes by completion status
  • Aggregates to all time periods and saves clean CSV

Before: Manual Excel cleanup → After: Clean CSV in seconds

08

General Newsletter Metrics

Input: Beehiiv subscriber & unsubscribe data

  • Calculates subscriber growth rate, net adds, leak rate
  • Aggregates across all time periods and creates growth/churn charts
09

Sales Metrics Analysis

Input: Processed CSVs from notebooks 03–06

  • Creates WoW and MoM comparison tables
  • Formats consolidated sales section ready for WBR/MBR
10

Newsletter Analysis

Input: Processed CSVs from notebooks 01, 02, 08

  • Creates WoW and MoM comparison tables
  • Consolidated newsletter section ready for reports
11

WBR Generator

Input: All CSVs and charts from notebooks 01–10

  • Loads weekly aggregated data and charts
  • Generates narrative insights for each metric
  • Assembles into formatted Word document
  • Output: Weekly_Business_Review_{date}.docx

Before: 2 hours assembling report → After: 2 minutes

12

MBR Generator

Same logic as notebook 11 but using monthly aggregated data.

Output: Monthly_Business_Review_{date}.docx

13

QBR Generator

Same logic as notebook 11 but using quarterly aggregated data.

Output: Quarterly_Business_Review_{date}.docx

Technical Stack & Implementation

Technologies used:

Python 3.8+ Jupyter Notebooks pandas matplotlib seaborn python-docx numpy scipy

1. Modular Architecture

"One Notebook = One Data Domain = One Set of Outputs"

Each notebook handles a single responsibility, writing cleaned CSVs as outputs. This enables independent testing, easier debugging, and the ability to re-run specific metrics without affecting others.

2. CSVs as Intermediate Storage

Traceability & Reproducibility

Every notebook writes CSVs to outputs/. This provides a paper trail for every metric, enables inspection at any stage, and ensures identical outputs given the same inputs — mirroring modern data warehouse patterns.

3. Programmatic Report Generation

No Manual Document Editing

Reports are generated entirely via python-docx code. This ensures consistent formatting, reproducible outputs, easy updates when metrics change, and version-controlled report logic.

4. Semantic Consistency

Same Metrics, Different Time Horizons

WBR, MBR, and QBR use identical metric definitions but at different time scales. This consistency enables direct comparison across reports while providing appropriate granularity for each audience.

Who This Project Is For (And Who It's Not For)

✅ Ideal For

  • Small to mid-sized businesses without a BI team
  • Founders/operators needing recurring WBR/MBR/QBR
  • Analytics engineers building automated reporting pipelines
  • Data analysts transitioning into analytics engineering roles
  • Portfolio projects demonstrating data engineering skills

❌ Not Ideal For

  • Real-time dashboards (this is batch reporting)
  • Large enterprises with full data warehouses
  • Ad-hoc exploratory analytics
  • Self-service BI (no drill-down capability)
  • Multi-user collaboration (single-user design)
Scope Note: This project demonstrates analytics engineering fundamentals at a realistic portfolio scale, including modular data pipelines, standardized metric definitions, and reproducible reporting workflows. It is intentionally scoped as a proof-of-concept rather than production infrastructure. The focus is on system design, data modeling, and analytical methodology — all transferable across BI and analytics stacks.

All data used is either anonymized, simulated, or structurally representative.

Key Takeaways & Skills Demonstrated

  • Analytics engineering: Modular transformations, semantic consistency, intermediate data modeling using CSV layers — patterns that translate directly to modern data warehouses.
  • Data pipeline engineering: Designed end-to-end ETL pipeline from raw data ingestion through aggregation to report generation with 13 modular components.
  • Python & pandas mastery: Built complex data transformations handling date normalization, multi-source joins, period-over-period calculations, rolling averages, and linear regression.
  • Process automation: Reduced 8-hour manual workflow to 30-minute automated pipeline — 94% time savings through intelligent system design.
  • Report automation: Programmatically generated professional Word documents with charts, tables, and narrative insights using python-docx — eliminating 100% of manual report creation.
  • Metric standardization: Established consistent definitions across time horizons (WBR/MBR/QBR) enabling apples-to-apples comparison and reproducible analysis.

Roadmap & Future Improvements

🔗

API Integration (Next Version)

Current: Manual CSV downloads from platforms

Future: Direct API connections to Beehiiv, Fathom Analytics, and Calendly — enabling fully automated ingestion without manual downloads.

Impact: 30 min → 5 min (just run the code)

⏰

Scheduled Automation

Deploy to cloud (AWS Lambda, GitHub Actions) with cron scheduling to automatically generate reports weekly/monthly/quarterly without any manual intervention.

📊

Interactive Dashboard

Build Streamlit or Dash app for real-time metric exploration, allowing stakeholders to filter by date ranges, drill into specific metrics, and export custom views.

🤖

ML-Based Anomaly Detection

Implement machine learning models (Isolation Forest, LSTM) for sophisticated pattern recognition, trend forecasting, and automated identification of unusual metric behavior.

View Full Project Code

All code, data, and documentation available on GitHub

View Repository
Note: This is a portfolio case study built around a real client engagement. Data has been anonymized and platform names are used descriptively. The pipeline architecture, metric definitions, and automation logic are real and fully functional. This project demonstrates analytics engineering methodology transferable to any recurring reporting use case.