01 / Revenue
Monthly Revenue Trend
Monthly sales fluctuate; July 2024 records the highest value at about PHP 200.4K.
01 / OVERVIEW
Executive Summary
How is the store performing, and what drives revenue? I used Python to turn a seven-file training dataset into a consistent view of sales, product contribution, geography, customer purchasing patterns, and cancellations.
- Loaded and assessed seven retail/e-commerce CSV files.
- Removed 387 exact duplicate records across the source tables.
- Standardized product labels from 18 variations to 8 categories.
- Created a 37,501-row order-item-level fact table without multiplying rows during joins.
- Applied a documented quality filter to identify 9,612 eligible Completed orders.
- Calculated PHP 4,383,742.63 in merchandise revenue and a PHP 456.07 AOV.
- Compared monthly trends, products, categories, and customer regions.
- Separated 2,873 repeat and 1,403 one-order matched customers.
- Measured 1,235 cancelled orders among all 15,000 cleaned orders.
- Delivered six standalone charts and a verified 1920 × 1080 dashboard.
02 / REPORT VIEW
Dashboard Preview
Final Python dashboard built from the validated sales and cancellation analysis.
Click to enlarge
Dashboard image: 1920 × 1080 pixels (16:9). KPIs use eligible Completed orders; cancellation figures use all cleaned orders.
03 / BUSINESS QUESTION
Context & Objectives
The capstone uses a synthetic retail/e-commerce dataset with deliberate quality issues. The assignment asked for an analyst-style explanation of store performance and revenue drivers, supported by a reproducible notebook and documented cleaning decisions.
“How is the store performing, and what drives revenue?”
Project objectives
- Prepare inconsistent, partially missing, and duplicated transaction data for analysis.
- Connect orders, order items, products, and customers without changing the intended item-level grain.
- Measure merchandise revenue, completed-order count, and average order value.
- Compare monthly sales, leading products and categories, and geographic contributions.
- Understand observed one-order versus repeat-customer patterns and the order cancellation trend.
04 / SOURCE DATA
Dataset Overview
Seven source tables were loaded. The figures below are row counts after exact duplicate removal, not the number of customers or sales orders used in the final KPI population.
| Source file | Cleaned rows | Role in analysis |
|---|---|---|
customers.csv | 5,000 | Customer profiles and geography |
products.csv | 800 | Product information and standardized categories |
orders.csv | 15,000 | Status, date, customer and discount |
order_items.csv | 37,501 | Product quantity and purchase-time price |
payments.csv | 15,000 | Reviewed during cleaning; not used as merchandise revenue |
shipments.csv | 15,000 | Reviewed during data preparation |
returns.csv | 1,200 | Reviewed during preparation; cancellation was chosen as the trend |
Data dictionary describes these records as synthetic training data. The 25-month analysis window spans January 2024 through January 2026.
05 / DATA QUALITY
Data Cleaning & Validation
The raw files intentionally contained category variations, nulls, duplicates, mixed dates, currency strings, and orphan references. Each major change was checked, and unresolved issues were retained or flagged rather than silently replaced.
01
Duplicates
Removed 387 exact duplicate rows. Primary keys were then checked for missing or repeated IDs.
02
Standardization
Consolidated 18 product category labels into eight categories; converted mixed dates and numeric/currency fields.
03
Missing values
Used documented rules for missing discounts and labels; preserved unresolved fields as missing where no defensible value existed.
04
Join integrity
Flagged 43 orders with an unmatched customer ID and 129 items with an unmatched product ID.
05
Price checks
Recovered 2,227 of 2,236 missing purchase prices from the product master; nine remained unresolved.
06
Sales eligibility
Flagged 378 orders with item-quality issues. Of these, 238 Completed orders were excluded from merchandise KPIs.
Why not delete every imperfect row? The all-record fact table retains documented orphan records. Only the separately defined eligible-sales table excludes Completed orders whose item quantities or prices make a reliable full-order total impossible.
06 / ANALYTICAL APPROACH
Fact Table & Methodology
Fact table grain: one row per order_item_id. Left joins to orders, products, and customers preserved all 37,501 cleaned item rows. Many-to-one validation ensured that the joins did not multiply line items.
BASE TABLEOrder Items37,501 lines
JOINOrdersStatus & discount
JOINProductsCategory & product
JOINCustomersRegion & city
Revenue and inclusion rules
Line revenue = quantity × unit_price_at_purchase × (1 − discount_pct)
- Sales population: orders with status
Completed, excluding any Completed order with an unusable item quantity or unresolved purchase price. - Included: 9,612 eligible Completed orders across 23,876 usable sales item rows.
- Excluded from merchandise revenue: shipping fees; refunds were not subtracted.
- Repeat customer: a matched customer with 2+ eligible orders within the observed dataset. One eligible order is labeled “New (1 order)” for this analysis, not a proven first-ever purchase.
- Cancellation rate: cancelled orders divided by all cleaned orders, grouped by order-placement month because no separate cancellation event date was supplied.
- Unknowns: unmatched product and customer geography remain in totals as clearly labeled unknown groups.
07 / PERFORMANCE MEASURES
Key Performance Indicators
Total merchandise revenuePHP 4,383,742.63Eligible Completed orders
Total eligible orders9,612One count per order ID
Average order valuePHP 456.07Revenue / eligible orders
Matched customers4,2762,873 repeat · 1,403 one-order
Cancellation rate8.23%1,235 / 15,000 cleaned orders
Top revenue monthJul 2024PHP 200,411.55
The final merchandise revenue was reconciled from the item level to the order level; month, product, category, region, and city revenue breakdowns also reconcile to the same total.
08 / CHART LIBRARY
Visual Analysis
Six exported PNG charts. Use the filters or open any chart for a closer look.
02 / Revenue
Revenue by Category
Toys leads at PHP 598.9K, followed closely by Electronics at PHP 591.5K.
03 / Revenue
Top 10 Products
KidJoy Toys 607 generated the most product revenue at PHP 22.3K.
04 / Revenue
Revenue by Region
Central Luzon contributes PHP 763.5K, about 17.4% of eligible sales revenue.
05 / Customers
Customer Spend Distribution
The distribution is right-skewed; fewer matched customers reach higher spending levels.
06 / Cancellations
Cancellation Trend
October 2024 and January 2025 are tied at 60 cancelled orders each.
09 / AT A GLANCE
Key Takeaways
01
Revenue is uneven over time. July 2024 peaked at PHP 200.4K; January 2025 was the lowest at PHP 146.5K.
02
No one category dominates. Toys contributed about 13.66% and Electronics 13.49% of total revenue.
03
Geographic contribution is distributed. Central Luzon led at 17.42%, with several other regions close behind.
04
Repeat customers generate most matched-customer revenue. Their contribution was about 85.4%.
05
Customer spend is right-skewed. Most matched customers fall in lower spending ranges.
06
Cancellations warrant review. The overall rate was 8.23%, reaching 10.77% for the January 2025 order cohort.
10 / FINDINGS
Eight Evidence-Based Insights
- PHP 4.38M revenue across 9,612 eligible orders. The calculated AOV was PHP 456.07 under the defined Completed-order and data-quality rules.
- Sales fluctuated rather than growing steadily. Monthly revenue ranged from about PHP 146.5K in January 2025 to PHP 200.4K in July 2024.
- Toys and Electronics are close category leaders. Toys generated PHP 598.9K versus Electronics at PHP 591.5K; neither alone accounts for even 14% of revenue.
- Product revenue is dispersed. The top identified product, KidJoy Toys 607, contributed PHP 22.25K, about 0.51% of total eligible merchandise revenue.
- Central Luzon ranks first geographically. It generated PHP 763.5K (17.42%); Olongapo was the highest-revenue city at PHP 270.0K.
- Repeat customers drive most matched-customer sales. Repeat customers were 67.2% of matched customers but generated 85.4% of that group's revenue.
- Customer spending is right-skewed. A smaller group of matched customers has substantially higher cumulative spending than the main concentration.
- Cancellations vary across order cohorts. Of 15,000 cleaned orders, 1,235 were cancelled (8.23%). January 2025 had the highest cancellation rate at 10.77%.
These are descriptive observations. The dataset does not establish why the changes happened; potential causes would need separate investigation.
11 / SYNTHESIS
Summary & Conclusions
Summary
The project turned seven inconsistent CSV datasets into a validated item-level analysis model and a defined population of Completed sales orders. Revenue totals reconcile across multiple business breakdowns, making the KPI results and visuals internally consistent.
Conclusions
Revenue is spread across product categories and regions, while repeat customers account for most sales among matched customers. Monthly revenue and cancellation counts both fluctuate, and the analysis points to questions worth investigating further—not to proven causal explanations.
12 / NEXT ACTIONS
Two Recommendations
01
Retain repeat customers and encourage second purchases
Maintain a focus on repeat buyers, then test appropriate follow-up or cross-sell efforts for customers with only one observed eligible order. Measure subsequent order frequency and incremental revenue before scaling.
02
Investigate high-cancellation order cohorts
Review the January 2025 and other elevated cancellation cohorts. Check fulfillment, product availability, customer requests, and order handling with operational data before deciding on a preventive action.
13 / FILES & REPRODUCIBILITY
Project Deliverables
This case study includes the notebook, the two specifically requested cleaned datasets, exported chart PNGs, and concise written summaries. All data files were taken from the synthetic training project.
Python NotebookJupyter analysis and reproducible steps · IPYNB
Cleaned Customers5,000 records · CSV
Cleaned Products800 records · CSV
Executive Summary10 concise, evidence-based statements · TXT
One-Page Insights SummaryEight insights and two recommendations · Markdown
Dashboard ScreenshotVerified 1920 × 1080 · PNG
The six standalone chart images are available in the Visual Analysis gallery. The full synthetic raw dataset is included in the repository's Data/Raw folder so the notebook can be rerun. The separately required capstone submission PDF is not part of this web case study.