A synthetic apparel commerce analytics case study built with Python, pandas, SQLite and a bilingual Streamlit dashboard. The project connects data generation, SQL/Python analysis and presentation in a reproducible portfolio workflow.
All customer, order, event and marketing data are synthetic. Results demonstrate analytical methods; they do not represent real brand performance, customer preferences or proven business improvements. Brand and product names are contextual references, not an affiliation or endorsement.
- Cumulative user event coverage (unordered event co-occurrence)
- Rule-based customer segmentation and repeat purchase behavior
- Product revenue and estimated merchandise gross margin
- Channel CPO, ROAS and revenue-based ROI
- SQL/Python metric agreement, data consistency tests and a bilingual dashboard smoke test
The tested environment is Python 3.12. requirements.lock pins direct and transitive dependencies; requirements.in is the input used when deliberately updating the lock.
python3.12 -m venv .venv
source .venv/bin/activate
python -m pip install -r requirements.lock
python src/generate_data.py
python src/analyze.py
python -m unittest discover -s tests -v
streamlit run src/dashboard.py --browser.gatherUsageStats falseGeneration replaces only the generated CSV/SQLite files under data/. Analysis refreshes outputs/. The Python generator uses seed 42, dates 2024-01-01 to 2025-12-31, and fixed row counts: 5,000 users, 60 products, 3,000 orders, 30,000 events, 2,193 daily channel marketing rows. Rerunning in the same locked environment produces identical artifacts. Chart rendering can vary across platforms/fonts; CI compares numeric/text outputs to the committed snapshot and checks full artifact determinism within the runner.
The committed sample contains $304,637 completed-order revenue, 2,884 completed orders, 2,133 purchasers, $105.63 AOV and 27.7% repeat purchase rate. These are simulated values, not project impact claims.
| Metric | Definition | Interpretation boundary |
|---|---|---|
| Cumulative event coverage | Unique users with every event type up to a row in page_view, product_view, add_to_cart, checkout_start, purchase |
Whole dataset, any order, session or channel. Counts event co-occurrence, not a timed conversion funnel. |
| Coverage ratio | Row coverage / previous row coverage | Missing when denominator is zero; first row is 100% only when nonempty. Gaps are not customer drop-off. |
| CPO | Marketing spend / completed order count | Cost per order, not CAC. Repeat orders count separately; no new-customer attribution. |
| ROAS | Completed-order revenue / marketing spend | Revenue return on spend; neither profit nor causal incremental return. |
| Revenue ROI | (Completed-order revenue − marketing spend) / marketing spend = ROAS − 1 | Excludes merchandise, fulfilment and other costs. |
| CTR | Synthetic clicks / synthetic impressions | Generator assumptions, not measured advertising traffic. |
| CVR | Completed order count / browsing event count | site_visits is a count of browsing events, not unique users or real sessions; not a probability of session conversion. |
| Repeat purchase rate | Purchasers with at least two completed orders / all purchasers | Full observation period; cohort months have unequal follow-up time. |
| Merchandise gross margin | Discounted item revenue − quantity × modeled product cost | Excludes advertising, fulfilment and other operating costs. |
Zero denominators yield missing values in channel metrics. Marketing purchases/revenue come from completed orders grouped by order date and channel, without multi-touch attribution. Event coverage includes purchase events for later-refunded orders; it therefore differs from completed-sales counts. The generator attaches cart/checkout/purchase events to orders but generates browsing independently; event timestamps are not a validated session journey and can precede signup/launch around order dates. Channel ranking reflects hand-set generation weights and spend formulas, so it cannot justify real budget reallocation.
data/csv/ Generated source tables
data/sqlite/ Generated SQLite database
src/generate_data.py Canonical seeded Python generator
src/analyze.py Analysis and artifact generation
src/dashboard.py EN / 中文 Streamlit presentation
sql/analysis_queries.sql SQL equivalents of key analyses
sql/schema_and_seed.sql Alternate unseeded SQL demo (different data/catalog)
outputs/ Summary CSVs, charts, synthetic findings
tests/ Metrics, SQL, data consistency, dashboard smoke
.github/workflows/ci.yml Reproduction and verification
sql/schema_and_seed.sql is a standalone alternative using SQLite random(); it is not used by the canonical pipeline and will not reproduce the committed snapshot.
The dashboard presents overview KPIs, user segments, repeat cohorts, merchandising and channel metrics. Generated findings state the limits alongside results. Resume copy describes the work without claiming real business outcomes.
Deployment configuration is provided for Streamlit Community Cloud and Render; no live deployment is claimed or linked. Use Python 3.12 and install requirements.lock. See deployment notes.
Original project code is available under the MIT License. Generated synthetic datasets and analysis charts are supplied as demo artifacts. The license does not grant rights to third-party names, trademarks, product designs or other brand material. The catalog references names and assortment ideas associated with Lyricoconut, recorded in the original project as observed on March 26, 2026; this is not a current catalog verification. No product images or third-party fonts are bundled or fetched by the dashboard; it uses system monospace fonts. Dependency licenses remain with their respective authors.