Adventure Works Marketing Campaign Capstone — Detailed Analysis

Author: Steve Lopez, Data Analyst
Environment: Local MySQL (self-hosted) · Python/Jupyter · Streamlit · Tableau

Executive Summary

Click to expand

This project analyzes marketing campaign performance using the Adventure Works dataset to uncover key profitability drivers, customer segments, and product bundling opportunities. It combines SQL, Python, Tableau, and Streamlit into a complete, repeatable analytics pipeline.

Key Goals

1. Identify top ROI campaigns and reallocate budget.
2. Target high-value customer segments by state and income.
3. Test product bundling strategies to increase margin.

Results Snapshot

• Top 8 campaigns showed ROI between 2.5×–4.1×.
• High/Mid income customers in top-profit states contributed ~65%+ of total profit.
• Bundling simulations showed up to ~$175K incremental margin potential.

Recommendations

• Shift spend to the top 5 ROI campaigns.
• Geo-target high/mid-income segments in top 10 profit states.
• A/B test accessory bundles with bikes (10–30% discount; 20–60% uptake).

Stakeholder	What they need	Deliverable
CMO	Where to shift budget	Campaign ROI leaderboard (+ thresholds)
Growth Mktg	Who to target & how	State × Income band segments; offer ladders
Revenue/Ops	Unit economics	AOV, margin, price/discount bands
Data/IT	Deployment path	Model pipeline + Streamlit app & Tableau

---

Project Navigation: How to Navigate the Repo

• Kickoff Brief → Start here: project goals, objectives, and scope.
• Statement of Work (SOW) → Project contract, deliverables, and responsibilities.
• Data Dictionary → Definitions of all key variables and fields.
• Data Quality & Tests → Data cleaning, validation, and QA process.
• Forecast Lift Analysis → Predictive uplift/ROI analysis.
• Logistic Regression Feature Impact → Top 20 features, Tableau exports, and business interpretation.

adventure_works_marketing_capstone/
│
├── data/
│   ├── raw/                 # Original datasets
│   └── processed/           # Cleaned/aggregated data & exports
├── images/                  # PNGs used in READMEs & decks
├── notebooks/               # Jupyter notebooks (EDA, KPIs, modeling)
├── README_docs/             # This README_detailed, README, data_dictionary.md
├── scripts/                 # Python ETL / utilities
├── sql/                     # MySQL DDL/DML & loaders
└── visualizations/          # Tableau packaged workbooks / exports

---

Architecture

Click to expand full inline table

      [ Raw Data: adventureworks_campaign (Flat Table) ]
      |  -- Single denormalized table from MySQL for Python
                          |
                          v
           +-----------------------------------+
           |   Python EDA / Modeling (Pandas)  |
           |  -- KPIs, visualizations, ML models|
           +-----------------------------------+
                          |
                          v
             [ Streamlit App & Reports ]
             |  -- Interactive data exploration

                          ||
                          ||  (Data reshaped for BI)
                          vv

            [ Star Schema for Tableau ]
            |  -- Optimized for dashboard performance
                   ┌──────────────┐
                   │  dim_date    │ -- Date attributes
                   └──────┬───────┘
                          │
                   ┌──────┴───────┐
                   │  fact_sales  │ -- Transaction metrics
                   └──────┬───────┘
                          │
    ┌─────────────────────┼─────────────────────┐
    │                     │                     │

┌───────┴───────┐ ┌───────┴───────┐ ┌───────┴───────┐ │ dim_customer │ │ dim_product │ │ dim_campaign │ │ -- Customer │ │ -- Product │ │ -- Marketing │ │ attributes │ │ attributes │ │ campaigns │ └───────┬───────┘ └───────────────┘ └───────────────┘ │ ┌───────┴────────┐ │ dim_geography │ -- State & region info └────────────────┘

---

Star Schema Mapping

Table	Column Name(s)	Description
fact_sales	OrderID, OrderDate, CustomerID, ProductID, CampaignID, OrderQuantity, UnitCost, UnitPrice, SalesAmount, TotalProductCost, Profit, ProfitMargin	Core transactional metrics and foreign keys to dimensions
dim_date	DateKey, OrderDate, Year, Quarter, Month, DayOfWeek	Calendar attributes for time-based analysis
dim_customer	CustomerID, FirstName, LastName, Gender, YearlyIncome, IncomeBand	Customer demographics and income banding
dim_product	ProductID, Product, ProductCategory, SubCategory	Product details and category hierarchy
dim_campaign	CampaignID, Campaign, StartDate, EndDate, CampaignCost, ROI	Marketing campaign attributes and performance
dim_geography	GeographyID, State, City, Region	Geographic attributes for location-based segmentation

---

1) Data Overview

Core fields analyzed:
OrderID, OrderDate, State, Gender, YearlyIncome, Campaign, Product, ProductCategory, UnitCost, UnitPrice, OrderQuantity, SalesAmount, TotalProductCost, Profit, ProfitMargin, Year

• Profit = SalesAmount − TotalProductCost
• ProfitMargin = Profit ÷ SalesAmount

2) Data Modeling — Flat vs Star Schema

Use Case Model Type Description Python EDA/Modeling Flat Single denormalized table for quick feature engineering, direct Pandas manipulation. Tableau Dashboards Star fact_sales table joined to dim_date, dim_customer, dim_product, dim_campaign, etc.

Star Schema for Tableau

For full column definitions, see the Data Dictionary.

---

3) KPIs — Step by Step

• Orders (unique OrderID)
• Revenue (sum SalesAmount)
• Profit (sum Profit)
• Average Order Value (AOV) = Revenue ÷ Orders
• Profit Margin = Profit ÷ Revenue

---

4) Campaign Performance

1. Aggregated by campaign
2. ROI calculated as Profit ÷ Cost
3. Sorted by ROI → Profit

Top ROI Campaigns

Total Profit by Campaign

---

5) Customer Segmentation

1. Income band classification
2. State-level profitability
3. Product category performance

Profit by Income Band (Top States)

Profit by Income Band (Top States)

1. Customer segmentation by income and profit
2. Segment distribution
3. Segment income and profit ranges

Customer Segmentation by Annual Income and profit

---

6) Predictive Modeling

1. Logistic regression for High-Profit classification
2. Feature engineering & preprocessing
3. Model evaluation (ROC AUC, classification report)

• Logistic Regression (One-Hot + StandardScaler) → P(HighProfit)
• Coefficients interpreted via odds ratios (exp(coef))

Top 15 Coefficients (Drivers)

---

7) Bundling Scenarios

1. Identified high-margin bikes & low-margin accessories
2. Simulated discount/uptake combinations
3. Estimated incremental margin

Top Incremental Margin Scenarios

---

8) Recommendations

• Shift spend to the top 5 ROI campaigns.
• Geo-target high/mid-income segments in top 10 profit states.
• A/B test accessory bundles with bikes (10–30% discount; 20–60% uptake).

---

Data Dictionary

Click to expand full inline table

Column	Description
OrderID	Unique order identifier
OrderDate	Date the order was placed
State	Customer’s U.S. state
Gender	Customer gender
YearlyIncome	Annual customer income
Campaign	Marketing campaign name
Product	Purchased product name
ProductCategory	Product category (e.g., Bikes, Accessories, Apparel)
UnitCost	Per-unit product cost
UnitPrice	Per-unit selling price
OrderQuantity	Number of units purchased
SalesAmount	Total sales amount
TotalProductCost	Total product cost (UnitCost × Quantity)
Profit	SalesAmount − TotalProductCost
ProfitMargin	Profit ÷ SalesAmount
Year	Order year

Star schema note: In Tableau, these fields are split across fact_sales and dimension tables (date, customer, product, campaign, geography).

More robust version: Data Dictionary