# Reporting Validation Checklist

> Step 8: Validate that the data model supports the key reports that run the business.
> For each report, verify the required fact tables and dimensions exist and are conformed.
> Gaps identified here require model revision before sign-off.

---

## Validation summary

| Report | Status | Gaps |
|---|---|---|
| | `✓ Supported / ⚠ Partial / ✗ Not supported` | |

---

## SaaS B2B Report Validation

### 1. MRR Waterfall (New / Expansion / Contraction / Churn)

**Business question:** How did our recurring revenue change this month, and why?

| Required component | Available in model? | Notes |
|---|---|---|
| Subscription fact table | | |
| MRR event type (new / expansion / contraction / churn) | | Stored as dimension or fact attribute? |
| Account dimension | | |
| Subscription Plan dimension | | |
| Date dimension | | |
| Account-level MRR amount (additive) | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 2. Net Revenue Retention (NRR) by cohort

**Business question:** Of the MRR we had from accounts that started in [month], what percentage do we retain 12 months later (including expansions)?

| Required component | Available in model? | Notes |
|---|---|---|
| Subscription fact table | | |
| Account cohort date (first paid date) on Account dim | | SCD Type 2 required? |
| Account-level MRR by month | | |
| Ability to filter to cohort × current period | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 3. Trial-to-paid conversion funnel

**Business question:** What percentage of trials convert to paid, and where do we lose them?

| Required component | Available in model? | Notes |
|---|---|---|
| Trial conversion fact table (or accumulating snapshot) | | |
| Trial start date | | |
| Conversion date (or churn from trial) | | |
| Account dimension | | |
| Plan dimension (which plan they converted to) | | |
| Acquisition channel on Account dim | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 4. Churn analysis (by plan / account age / CSM)

**Business question:** Who is churning, and what do they have in common?

| Required component | Available in model? | Notes |
|---|---|---|
| Churn event on subscription fact | | |
| Account age (first paid date → churn date) | | |
| Plan at time of churn (SCD Type 2) | | |
| CSM / account owner on Account dim | | |
| Number of properties / seats at churn | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 5. Feature adoption by plan tier

**Business question:** Which features are being used by which plan tiers, and which are underused?

| Required component | Available in model? | Notes |
|---|---|---|
| Feature usage fact table | | |
| Feature dimension | | |
| Plan dimension (conformed with subscription fact) | | |
| Account dimension (conformed) | | |
| Usage frequency / count | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 6. LTV / CAC by acquisition channel

**Business question:** How much does it cost to acquire a customer, and how much do they spend over their lifetime?

| Required component | Available in model? | Notes |
|---|---|---|
| Subscription revenue fact | | |
| Marketing spend fact | | |
| Acquisition channel on Account dimension | | |
| Account lifetime (first paid → churn or present) | | |
| CAC calculation (spend / new accounts — by channel) | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

## E-commerce Report Validation

### 1. Contribution Margin Waterfall (Gross → CM I → CM II)

**Business question:** What is our true profitability by brand and channel after all costs?

```
Gross Revenue
- Returns & Refunds         [Returns fact]
= Net Revenue
- Platform Fees / Commission [Orders fact — fee fields]
= Revenue After Platform
- COGS                      [Orders fact — COGS]
= Gross Profit
- Ad Spend                  [Advertising fact]
= Contribution Margin I
- Allocated logistics        [if modelled]
= Contribution Margin II
```

| Required component | Available in model? | Notes |
|---|---|---|
| Orders fact with gross revenue, fees, COGS | | |
| Returns fact (separate, not netted) | | |
| Advertising fact with spend | | |
| Brand dimension (conformed across all three) | | |
| Channel dimension (conformed across all three) | | |
| Date dimension (conformed) | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 2. Revenue and units by brand / channel / SKU

**Business question:** How are our brands and products performing across channels this week?

| Required component | Available in model? | Notes |
|---|---|---|
| Orders fact with revenue and units | | |
| Product / SKU dimension | | |
| Brand dimension (conformed) | | |
| Channel dimension (conformed) | | |
| Date dimension with week / month hierarchy | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 3. ACOS / TACOS by campaign

**Business question:** How efficient is our advertising spend at driving attributed revenue?

| Required component | Available in model? | Notes |
|---|---|---|
| Advertising fact with ad_spend and attributed_revenue | | ACOS = ad_spend / attributed_revenue — store components |
| Total revenue from Orders fact (for TACOS) | | TACOS = ad_spend / total_revenue |
| Campaign dimension | | |
| Product dimension (conformed) | | |
| Brand dimension (conformed) | | |
| Channel dimension (conformed) | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 4. Inventory health (days of cover, stockout rate)

**Business question:** Do we have enough inventory? Where are we at risk of stocking out?

| Required component | Available in model? | Notes |
|---|---|---|
| Inventory snapshot fact (periodic, end-of-day) | | |
| Units on hand (semi-additive — don't sum across time) | | |
| Units inbound | | |
| Average daily sales (derived from Orders fact) | | Days of cover = units on hand / avg daily sales |
| Product dimension (conformed) | | |
| Warehouse / FBA Node dimension | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 5. Return rate by product / channel / reason

**Business question:** Which products have the highest return rates, and why?

| Required component | Available in model? | Notes |
|---|---|---|
| Returns fact (separate from Orders) | | |
| Return reason (dimension or degenerate) | | |
| Product dimension (conformed) | | |
| Channel dimension (conformed) | | |
| Units returned and refund amount | | |
| Orders fact for denominator (units sold) | | |

**Status:** ✓ / ⚠ / ✗
**Gaps:**

---

### 6. Customer cohort LTV (DTC only)

**Business question:** How much revenue do customers acquired in a given month generate over 12 months?

| Required component | Available in model? | Notes |
|---|---|---|
| Orders fact with customer key | | Amazon rows will have null customer key |
| Customer dimension (DTC only) | | |
| Acquisition date / cohort on Customer dim | | |
| Brand dimension (which brand acquired them) | | |
| Channel = DTC filter | | |

**Status:** ✓ / ⚠ / ✗
**Note:** This report is structurally impossible for Amazon marketplace orders (customer anonymised).
**Gaps:**

---

## Gap resolution log

| Gap | Severity | Required model change | Priority |
|---|---|---|---|
| | `High / Medium / Low` | | |