Retail & e-commerce
Orion Retail
A fictional omnichannel retailer with 8 physical stores and an online store across the Caucasus, Turkey and Europe, 2023–2025. Includes seasonality, campaigns, store openings/closures (for like-for-like analysis), a sales plan, inventory and a deliberately messy customer extract.
Log in to query this database in your browser.
Data dictionary
customers · 4,000 rows
One row per registered customer.
| Column | Type | Description |
|---|---|---|
| customer_id | INTEGER | Primary key |
| first_name | TEXT | |
| last_name | TEXT | |
| TEXT | ||
| city | TEXT | |
| country | TEXT | |
| segment | TEXT | Consumer / Small Business / Corporate |
| signup_date | TEXT (ISO date) | Date the customer registered |
| birth_year | INTEGER | Year of birth (self-reported) |
| gender | TEXT | |
| acquisition_channel | TEXT | Channel through which the customer was first acquired |
products · 108 rows
Product catalogue. Each product exists in Basic/Plus/Pro variants.
| Column | Type | Description |
|---|---|---|
| product_id | INTEGER | Primary key |
| sku | TEXT | |
| product_name | TEXT | |
| category | TEXT | |
| subcategory | TEXT | Product family |
| unit_cost | REAL | Purchase cost per unit (USD) |
| list_price | REAL | 2023 shelf price per unit (USD); prices rise ~4% per year |
| supplier | TEXT |
stores · 9 rows
Physical stores plus the online channel (store_id 8).
| Column | Type | Description |
|---|---|---|
| store_id | INTEGER | Primary key |
| store_name | TEXT | |
| city | TEXT | |
| region | TEXT | |
| open_date | TEXT (ISO date) | Date the store opened |
| close_date | TEXT (ISO date) | Date the store closed (NULL if still open) |
| size_sqm | REAL | Selling area in square metres (NULL for online) |
| channel | TEXT |
orders · 37,651 rows
One row per order (header).
| Column | Type | Description |
|---|---|---|
| order_id | INTEGER | Primary key |
| customer_id | INTEGER | FK → customers |
| store_id | INTEGER | FK → stores |
| order_date | TEXT (ISO date) | |
| channel | TEXT | |
| status | TEXT | completed / returned / cancelled — only completed orders count as revenue |
| payment_method | TEXT | |
| discount_pct | REAL | Order-level discount as a fraction (0.15 = 15%) |
| campaign_id | TEXT | FK → campaigns if the order date fell within a campaign (NULL otherwise) |
order_items · 75,600 rows
One row per product line within an order.
| Column | Type | Description |
|---|---|---|
| order_item_id | INTEGER | Primary key |
| order_id | INTEGER | FK → orders |
| product_id | INTEGER | FK → products |
| quantity | INTEGER | Units purchased |
| unit_price | REAL | Price per unit before discount (USD) |
| discount_pct | REAL | Discount applied to the line (fraction) |
campaigns · 12 rows
Marketing campaigns.
| Column | Type | Description |
|---|---|---|
| campaign_id | INTEGER | |
| campaign_name | TEXT | |
| channel | TEXT | email / social / search / multi |
| start_date | TEXT (ISO date) | |
| end_date | TEXT (ISO date) | |
| budget | INTEGER | Total campaign spend (USD) |
sales_targets · 195 rows
Monthly revenue plan per store for 2024–2025 (Plan vs Fact).
| Column | Type | Description |
|---|---|---|
| store_id | INTEGER | |
| year_month | TEXT | YYYY-MM |
| target_revenue | REAL | Planned net revenue for the month (USD) |
inventory_monthly · 3,888 rows
Month-end stock position per product (all locations combined).
| Column | Type | Description |
|---|---|---|
| product_id | INTEGER | |
| year_month | TEXT | |
| units_sold | INTEGER | Units sold in the month (all statuses) |
| units_received | INTEGER | Units received from suppliers |
| closing_stock | INTEGER | Units on hand at month end |
calendar · 1,096 rows
Date dimension covering 2023-01-01 to 2025-12-31.
| Column | Type | Description |
|---|---|---|
| date | TEXT (ISO date) | Calendar date (primary key) |
| year | INTEGER | |
| quarter | INTEGER | |
| month | TEXT (ISO date) | |
| month_name | TEXT | |
| year_month | TEXT | |
| iso_week | INTEGER | |
| weekday | TEXT | |
| is_weekend | INTEGER | |
| is_holiday | INTEGER | 1 if a public holiday in the company calendar |
customers_raw · 645 rows
A messy CRM extract used for data-cleaning practice: duplicate rows, missing emails, inconsistent city casing/whitespace and mixed date formats.
| Column | Type | Description |
|---|---|---|
| customer_id | INTEGER | |
| first_name | TEXT | |
| last_name | TEXT | |
| TEXT | ||
| city | TEXT | |
| country | TEXT | |
| segment | TEXT | |
| signup_date | TEXT (ISO date) | Mixed formats: YYYY-MM-DD, YYYY/MM/DD, DD.MM.YYYY |
| birth_year | INTEGER | |
| gender | TEXT | |
| acquisition_channel | TEXT |