# Worked Example: Natsana (E-commerce, Multi-brand)

> Multi-brand nutritional supplements company. Primary channel: Amazon FBA.
> Three brands: Nature Love, Feel Natural, Natural Elements.
> Demonstrates the full Kimball modelling procedure for a multi-brand e-commerce business.

---

## Business Context Brief

| Field | Value |
|---|---|
| Company | Natsana GmbH |
| Website | natsana.com |
| Business type | E-commerce (multi-brand, marketplace-first) |
| Founded | 2019 |
| Headquarters | Monheim am Rhein / Düsseldorf, Germany |
| Revenue | €85M (2022) |
| Employees | ~136 |
| Brands | Nature Love, Feel Natural, Natural Elements |

**Business model:** Multi-brand operator of nutritional supplements. Primary channel is Amazon FBA (Fulfillment by Amazon) in Germany. Expanding to DTC via Shopify Plus stores per brand, and into physical retail (drugstore chains in DE/AT).

**Channel mix:**
| Channel | Type | Fulfillment | Customer identity |
|---|---|---|---|
| Amazon DE | Marketplace | FBA | Anonymised — no PII |
| Amazon other markets | Marketplace | FBA | Anonymised |
| Nature Love Shopify | DTC | 3PL / own | Full PII available |
| Feel Natural Shopify | DTC | 3PL / own | Full PII available |
| Natural Elements Shopify | DTC | 3PL / own | Full PII available |
| Drugstore retail (DM, Rossmann) | Wholesale/Retail | B2B shipment | No individual customer data |

**Tech stack (identified from public sources):**
| System | Tool | Role |
|---|---|---|
| E-commerce (DTC) | Shopify Plus (per brand) | Storefront and order management |
| Marketplace | Amazon Seller Central | Primary sales channel |
| CRM | Salesforce Service, Commerce, Marketing Cloud | Customer service, DTC CX, email marketing |
| Integration | MuleSoft | System integration layer |
| PIM | (unnamed) | Product information management |
| ERP | (unnamed) | Inventory, finance |
| Finance automation | Candis | Invoice processing |

---

## Value Chain

```
Brand Strategy → Product Development → Procurement → PIM / Listing Setup → Inventory (FBA / 3PL) → Marketing & Advertising → Order & Fulfillment → Returns & Refunds → Customer Retention (DTC)
```

| Stage | Candidate fact table | Key metrics |
|---|---|---|
| Procurement | Purchase order fact | Lead time, COGS per unit, inbound quantity |
| Inventory | Inventory snapshot | Units on hand, days of cover, inbound units |
| Marketing | Advertising spend | Ad spend, impressions, clicks, attributed revenue |
| Order & Fulfillment | Orders | Revenue, units, fees, COGS |
| Returns | Returns | Units returned, refund amount, return reason |
| Customer Retention | Customer orders (DTC only) | Repeat rate, LTV, cohort revenue |

---

## Bus Matrix

| Business Process | Date | Customer | Product / SKU | Brand | Channel | Campaign | Warehouse / FBA Node | Promotion |
|---|---|---|---|---|---|---|---|---|
| Orders & Revenue | ✓ | ✓ (DTC) | ✓ | ✓ | ✓ | | | ✓ |
| Advertising Spend | ✓ | | ✓ | ✓ | ✓ | ✓ | | |
| Inventory Snapshot | ✓ | | ✓ | ✓ | ✓ | | ✓ | |
| Returns & Refunds | ✓ | ✓ (DTC) | ✓ | ✓ | ✓ | | ✓ | |
| Customer Retention | ✓ | ✓ | | ✓ | ✓ | ✓ | | |

**Conformed dimensions:** Date, Product/SKU, Brand, Channel
**Customer dimension constraint:** Available for DTC channels only. Amazon order rows will carry a null or degenerate customer key.

---

## Grain Statements

| Fact Table | Grain Statement | Type |
|---|---|---|
| Orders & Revenue | One row per product SKU sold on one order line on one channel on one date | Transaction |
| Advertising Spend | One row per campaign's daily spend and performance on one channel for one product | Periodic Snapshot |
| Inventory Snapshot | One row per SKU at one warehouse or FBA node at end of one day | Periodic Snapshot |
| Returns & Refunds | One row per SKU line item on one return request | Transaction |
| Customer Orders (DTC) | One row per order line per DTC customer — used for cohort and LTV analysis | Transaction |

---

## Dimension Attribute Lists

### Product / SKU
| Attribute | Business label | SCD Type | Notes |
|---|---|---|---|
| product_id | Product ID | — | Surrogate key |
| sku | SKU code | 1 | Amazon ASIN / internal SKU |
| product_name | Product name | 1 | |
| brand_id | Brand (FK) | 1 | |
| category | Category | 2 | Vitamins, Minerals, Protein, etc. |
| subcategory | Subcategory | 2 | |
| form_factor | Form factor | 1 | Capsule, powder, liquid, tablet |
| size_units | Pack size | 1 | 60 capsules, 500g, etc. |
| is_bundle_flag | Bundle product? | 1 | |
| is_subscription_eligible | Subscribe & Save eligible? | 1 | Amazon-specific |
| launch_date | Launch date | 1 | |
| status | Status | 1 | Active / Discontinued / Seasonal |
| cogs_per_unit | COGS per unit (€) | 2 | History needed for accurate historical margins |

### Brand
| Attribute | Business label | SCD Type | Notes |
|---|---|---|---|
| brand_id | Brand ID | — | Surrogate key |
| brand_name | Brand name | 1 | Nature Love / Feel Natural / Natural Elements |
| brand_tier | Brand tier | 2 | Hero brand / Incubator |
| target_segment | Target segment | 2 | Mass market / Premium / Clinical |
| brand_manager | Brand manager | 2 | |
| shopify_store_url | Shopify store | 1 | DTC store per brand |
| launch_date | Brand launch date | 1 | |

### Channel
| Attribute | Business label | SCD Type | Notes |
|---|---|---|---|
| channel_id | Channel ID | — | Surrogate key |
| channel_name | Channel name | 1 | Amazon DE, Amazon UK, Nature Love DTC, etc. |
| channel_type | Channel type | 1 | Marketplace / DTC / Retail / Wholesale |
| fulfillment_type | Fulfillment type | 1 | FBA / FBM / 3PL / Self-fulfilled |
| commission_rate | Commission rate | 2 | Amazon referral fee varies by category |
| customer_pii_available | Customer PII available? | 1 | False for all marketplace channels |
| currency | Currency | 1 | EUR, GBP, etc. |

### Customer (DTC only)
| Attribute | Business label | SCD Type | Notes |
|---|---|---|---|
| customer_id | Customer ID | — | Surrogate key — DTC only |
| acquisition_channel | Acquisition channel | 1 | Paid social, organic, email, etc. |
| acquisition_date | First order date | 1 | |
| first_brand | First brand purchased | 1 | Which brand acquired them |
| country | Country | 2 | |
| is_subscriber_flag | Subscribe & Save? | 1 | |
| email_opt_in | Email opt-in | 1 | For GDPR compliance note |

**Note:** Amazon customers must NOT be in this dimension. Use a degenerate customer key (or null) on Amazon order rows.

### Campaign
| Attribute | Business label | SCD Type | Notes |
|---|---|---|---|
| campaign_id | Campaign ID | — | |
| campaign_name | Campaign name | 1 | |
| campaign_type | Campaign type | 1 | Sponsored Product, Sponsored Brand, Meta, Google, Influencer |
| channel_id | Channel (FK) | 1 | |
| objective | Objective | 1 | Awareness / Conversion / Retargeting |
| attribution_window_days | Attribution window | 1 | 14 (Amazon), 7 (Meta click), 1 (Meta view) |
| start_date | Start date | 1 | |
| end_date | End date | 1 | |
| budget_eur | Budget (€) | 1 | |

### Warehouse / FBA Node
| Attribute | Business label | SCD Type | Notes |
|---|---|---|---|
| node_id | Node ID | — | |
| node_name | Node name | 1 | Amazon FBA DE, Own Warehouse, 3PL Partner |
| node_type | Node type | 1 | FBA / Own / 3PL |
| country | Country | 1 | |
| lead_time_days | Lead time (days) | 2 | Days from order to node receipt |

---

## Fact Candidate Tables

### Orders & Revenue (Transaction)

| Fact | Label | Calculation | Additive? | Source |
|---|---|---|---|---|
| gross_revenue_eur | Gross revenue (€) | Units × selling price | Fully additive | Amazon / Shopify |
| units_sold | Units sold | Quantity on order line | Fully additive | Amazon / Shopify |
| amazon_referral_fee_eur | Amazon referral fee (€) | % of revenue per category | Fully additive | Amazon |
| amazon_fba_fee_eur | FBA fulfilment fee (€) | Per unit fee | Fully additive | Amazon |
| total_platform_fees_eur | Total platform fees (€) | Sum of all marketplace fees | Fully additive | Amazon |
| discount_amount_eur | Discount applied (€) | Promotional discount | Fully additive | Amazon / Shopify |
| net_revenue_eur | Net revenue (€) | Gross - fees - discounts | Fully additive | Calculated |
| cogs_eur | COGS (€) | Units × cogs_per_unit | Fully additive | ERP |
| gross_profit_eur | Gross profit (€) | Net revenue - COGS | Fully additive | Calculated |

### Advertising Spend (Periodic Snapshot — daily)

| Fact | Label | Calculation | Additive? | Source |
|---|---|---|---|---|
| ad_spend_eur | Ad spend (€) | Daily spend on campaign | Fully additive | Amazon Ads / Meta / Google |
| impressions | Impressions | Ad impressions | Fully additive | Ad platforms |
| clicks | Clicks | Ad clicks | Fully additive | Ad platforms |
| attributed_revenue_eur | Attributed revenue (€) | Revenue within attribution window | Fully additive | Ad platforms |
| attributed_orders | Attributed orders | Orders credited to campaign | Fully additive | Ad platforms |
| *acos* | *ACOS* | *ad_spend / attributed_revenue* | **NON-ADDITIVE — derive, do not store** | |
| *roas* | *ROAS* | *attributed_revenue / ad_spend* | **NON-ADDITIVE — derive, do not store** | |

### Inventory Snapshot (Periodic Snapshot — end of day)

| Fact | Label | Calculation | Additive? | Source |
|---|---|---|---|---|
| units_on_hand | Units on hand | Physical units available | Semi-additive (not across time) | Amazon / WMS |
| units_inbound | Units inbound | Units in transit to node | Semi-additive | Amazon / WMS |
| units_reserved | Units reserved | Units allocated to pending orders | Semi-additive | Amazon |
| *days_of_cover* | *Days of cover* | *units_on_hand / avg_daily_sales* | **NON-ADDITIVE — derive** | |

### Returns & Refunds (Transaction)

| Fact | Label | Calculation | Additive? | Source |
|---|---|---|---|---|
| units_returned | Units returned | Quantity returned | Fully additive | Amazon / Shopify |
| refund_amount_eur | Refund amount (€) | Amount refunded | Fully additive | Amazon / Shopify |
| return_reason | Return reason | Categorical — store as degenerate dim | — | Amazon |

---

## Glossary (key terms)

| Term | Agreed definition | Conflict to resolve |
|---|---|---|
| Net Revenue | Gross revenue minus Amazon fees, refunds, and promotional discounts | Marketing excludes refunds from their revenue — needs alignment |
| ACOS | Ad spend / attributed revenue within each platform's attribution window | Amazon 14-day vs. Meta 7-day click / 1-day view — NOT comparable across platforms |
| Active SKU | A SKU with at least one unit sold in the last 90 days | Ops uses "listed and in stock" regardless of sales |
| Customer | A person who placed at least one DTC (Shopify) order with identifiable PII | Amazon customers are pseudonymous — "customer" in Amazon context = order-level only |
| Return Rate | Units returned / units sold in the same calendar period | Some teams use shipped units as denominator — different populations |
| Brand | A named product line with its own positioning, domain, and Shopify store | Legal entity structure may not match brand structure |

---

## Reporting Validation

| Report | Supported? | Notes |
|---|---|---|
| Contribution margin waterfall | ✓ | Orders + Returns + Advertising facts with conformed Brand and Channel dims |
| Revenue by brand / channel / SKU | ✓ | Orders fact + Product + Brand + Channel dims |
| ACOS / TACOS by campaign | ✓ | Advertising fact (store components, derive ratio in BI) |
| Inventory health (days of cover) | ✓ | Inventory snapshot fact (derive days of cover from units and sales) |
| Return rate by product / reason | ✓ | Returns fact + Product dim + Channel dim |
| Customer cohort LTV (DTC only) | ✓ (DTC only) | Customer dim only for Shopify channels — Amazon structurally excluded |
| Cross-platform ACOS comparison | ✗ Not supported | Attribution windows differ by platform — flag this prominently in BI layer |