Retail Pricing & Margin Analysis Dashboard

An end-to-end pricing analytics project for a fictional Canadian office-supplies retailer, OfficeSupplyCo. It analyzes product prices, costs, discounts, customer segments, regions, and competitor prices to find where the business is losing margin and recommends concrete pricing actions.

Built as a portfolio project for Pricing Analyst / Business Analyst / Revenue Analyst / Data Analyst roles. Data is synthetic but follows realistic retail pricing logic.

---

1. Project Overview

OfficeSupplyCo sells office supplies, paper, ink & toner, furniture, technology, and business services to customers across Canada. This project takes raw transaction data and turns it into:

• A clean set of KPIs (revenue, profit, margin, discount, price index)
• Performance breakdowns by category, customer segment, and region
• A pricing-risk model that flags problem products
• Rule-based recommendations for each product
• An Excel report, a set of charts, and an interactive dashboard

2. Business Problem

Leadership wants to improve gross margin and make smarter pricing decisions. Specifically:

1. Which product categories generate the most revenue?
2. Which categories have the lowest gross margin?
3. Which customer segments receive the highest discounts?
4. Which products are priced below competitors?
5. Which products have high revenue but low margin?
6. Which products are risky (low margin, high discount, or below-market price)?
7. What pricing actions should the business take?
8. Which products should be reviewed for price increases, discount changes, or supplier cost negotiation?

3. Dataset Description

data/retail_pricing_data.csv — ~5,000 transaction rows, generated by src/generate_data.py. Key columns:

Column	Meaning
order_id, order_date	Transaction identifier and date
customer_id, customer_segment	Customer and their segment (Consumer, Small Business, Corporate, Education, Government)
region	Canadian province (Ontario, Quebec, BC, Alberta, Manitoba, Nova Scotia)
product_category, product_id, product_name	What was sold
quantity	Units sold
unit_cost	Our cost per unit
list_price	Sticker price before discount
discount_rate	Discount applied (0–1)
final_price	Price actually charged per unit
competitor_price	Competitor's price for a similar item
revenue, gross_profit, gross_margin	Sales performance metrics
price_index	Our price ÷ competitor price

4. Tools Used

• Python — pandas, NumPy (data + analysis)
• Matplotlib — static charts for the report
• openpyxl — multi-sheet Excel export
• Streamlit + Plotly — interactive dashboard
• Excel — analyst-ready output tables

5. Key Metrics Explained

• Revenue = final_price × quantity. Total money earned from sales.
• Gross Profit = (final_price − unit_cost) × quantity. Money left after the cost of the product itself.
• Gross Margin = gross_profit ÷ revenue. Profit as a % of sales — the core health metric for pricing.
• Discount Rate = how much we cut off the list price. High discounts erode margin.
• Price Index = final_price ÷ competitor_price. Below 1.0 means we're cheaper than competitors; above 1.0 means we're more expensive.

6. Analysis Workflow

1. Generate data — src/generate_data.py creates the synthetic CSV.
2. Analyze — src/analysis.py builds six summary tables, writes them to outputs/summary_tables.xlsx, and saves six charts to outputs/charts/.
3. Explore — src/dashboard.py launches an interactive Streamlit dashboard.

7. Key Findings (example)

These come from the default synthetic data (seed = 42). Re-run the scripts to reproduce them.

• Technology and Furniture drive the most revenue (high ticket prices).
• Paper and Technology show the lowest gross margins (high cost ratio).
• Government, Corporate, and Education segments receive the highest discounts.
• A meaningful share of transactions are priced below competitors (price index < 1.0), driven by discounts.
• Several products combine high revenue with low margin — the priority list for management review.

8. Business Recommendations

Per-product, rule-based actions (see outputs/summary_tables.xlsx → Recommendations):

• Increase price — low margin and priced below competitors.
• Review discount policy — discount rate is too high.
• Negotiate supplier cost — low margin but priced at/above competitors.
• Management review required — high revenue but low margin.
• Maintain current pricing — healthy products.

9. How to Run the Project

# 1. (Recommended) create and activate a virtual environment
python -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scripts\activate

# 2. Install dependencies
pip install -r requirements.txt

# 3. Generate the synthetic dataset
python src/generate_data.py

# 4. Run the analysis (creates Excel + charts)
python src/analysis.py

# 5. Launch the interactive dashboard
streamlit run src/dashboard.py

10. Why This Project Is Relevant to Pricing Analyst Roles

It mirrors the core day-to-day of a pricing/revenue analyst: measuring margin and discount leakage, benchmarking against competitors, identifying at-risk products, and translating data into clear pricing actions for stakeholders — delivered through both automated reporting (Excel/charts) and a self-serve dashboard.

---

Project Structure

pricing-margin-analysis/
├── data/
│   └── retail_pricing_data.csv      # generated dataset
├── src/
│   ├── generate_data.py             # Step 1: create synthetic data
│   ├── analysis.py                  # Step 2+3: tables + Excel + charts
│   └── dashboard.py                 # Step 4: Streamlit dashboard
├── outputs/
│   ├── summary_tables.xlsx          # six analyst tables (one per sheet)
│   └── charts/                      # six PNG charts
├── README.md
├── requirements.txt
└── resume_bullets.md