# Luminate Consumption Fact Views

## Overview

Luminate consumption data is organized into 4 types of fact views, each serving a different analytical need. All fact views are daily-grain (keyed by `REPORT_DATE`) except industry analysis and market share which are weekly (`WEEK_ID`).

All datashare views are in `LUMINATE_DB_LISTING_DETAIL.EXTRACT_S` unless noted otherwise.

## Fact View Types at a Glance

| Type | Naming Pattern | Granularity | Market-Level | Provider-Level | Entities Available |
|------|---------------|-------------|--------------|----------------|-------------------|
| **Detail** | `VW_DAILY_FACT_{ENTITY}_DETAIL_DS` | Daily, market | Yes (MARKET_ID) | No | MR, Song, MP, MREL, MRELG, Artist |
| **Summary** | `VW_DAILY_FACT_{ENTITY}_SUMMARY_DS` | Daily, national | No | No | MR, Song, MP, MREL, MRELG, Artist |
| **Provider** | `VW_DAILY_FACT_{ENTITY}_PROVIDER_DS` | Daily, market | Yes (MARKET_ID) | Yes (PROVIDER_ID/NAME) | MR, Song, MP, MREL, MRELG, Artist |
| **Claim** | `VW_DAILY_FACT_{ENTITY}_CLAIM_DS` | Daily, market | Yes (MARKET_ID) | Yes (PROVIDER_ID/NAME) | MR, MP only |

## Detail Fact Views

The most granular consumption data. Contains market-level breakdown for US and CA metro areas.

**Available views:**
- `VW_DAILY_FACT_MR_DETAIL_DS`
- `VW_DAILY_FACT_SONG_DETAIL_DS`
- `VW_DAILY_FACT_MP_DETAIL_DS`
- `VW_DAILY_FACT_MREL_DETAIL_DS`
- `VW_DAILY_FACT_MRELG_DETAIL_DS`
- `VW_DAILY_FACT_ARTIST_DETAIL_DS`

**Key characteristics:**
- Contains `MARKET_ID` and `MARKET_NAME` — **you MUST filter MARKET_ID = -1 for national data** or specify a metro market ID. Without this filter, queries double-count by summing metro + national.
- Contains `TRANSACTION_TYPE` (R=return, S=sale, CMA=complete my album)
- Slower than Summary views due to additional dimensions

**When to use:** Analyst needs metro-area breakdowns, transaction-level detail, or the most complete dimension set.

**Example — metro-level streaming (requires MARKET_ID filter):**
```sql
SELECT
    f.SONG_ID,
    f.MARKET_NAME,
    SUM(f.QUANTITY) AS total_streams
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_DAILY_FACT_SONG_DETAIL_DS f
WHERE f.SONG_ID = '<song_id>'
  AND f.COUNTRY_CODE = 'US'
  AND f.MARKET_ID != -1  -- metro markets only (exclude national to avoid double-count)
  AND f.METRIC_CATEGORY = 'Streams'
  AND f.REPORT_DATE BETWEEN '2024-01-01' AND '2024-01-07'
GROUP BY f.SONG_ID, f.MARKET_NAME;
```

## Summary Fact Views

National-level aggregates. Faster to query because they exclude market and transaction dimensions.

**Available views:**
- `VW_DAILY_FACT_MR_SUMMARY_DS`
- `VW_DAILY_FACT_SONG_SUMMARY_DS`
- `VW_DAILY_FACT_MP_SUMMARY_DS`
- `VW_DAILY_FACT_MREL_SUMMARY_DS`
- `VW_DAILY_FACT_MRELG_SUMMARY_DS`
- `VW_DAILY_FACT_ARTIST_SUMMARY_DS`

**Key characteristics:**
- **No MARKET_ID** — data is already national-level. No risk of double-counting.
- **No TRANSACTION_TYPE** — individual transaction info is not available
- Includes all streaming and sales breakout columns
- Better performance for most analytical queries

**When to use:** National-level analysis where metro breakdowns aren't needed. This should be the **default choice** for most queries.

**Example — national streaming for a song (one week):**
```sql
SELECT
    f.SONG_ID,
    f.COUNTRY_CODE,
    f.METRIC_CATEGORY,
    f.SERVICE_TYPE,
    f.CONTENT_TYPE,
    f.COMMERCIAL_MODEL,
    SUM(f.QUANTITY) AS total_streams
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_DAILY_FACT_SONG_SUMMARY_DS f
WHERE f.SONG_ID = '<song_id>'
  AND f.COUNTRY_CODE = 'US'
  AND f.METRIC_CATEGORY = 'Streams'
  AND f.REPORT_DATE BETWEEN '2024-01-01' AND '2024-01-07'
GROUP BY ALL;
```

## Provider Fact Views

Provider-level activity data. Shows which data provider (e.g., Spotify, Apple Music) reported the activity.

**Available views:**
- `VW_DAILY_FACT_MR_PROVIDER_DS`
- `VW_DAILY_FACT_SONG_PROVIDER_DS`
- `VW_DAILY_FACT_MP_PROVIDER_DS`
- `VW_DAILY_FACT_MREL_PROVIDER_DS`
- `VW_DAILY_FACT_MRELG_PROVIDER_DS`
- `VW_DAILY_FACT_ARTIST_PROVIDER_DS`

**Key characteristics:**
- Contains `PROVIDER_ID` and `PROVIDER_NAME` — identifies the reporting service
- Non-owned providers default to PROVIDER_ID=0 and PROVIDER_NAME='PROVIDER'
- Also contains `MARKET_ID` — same MARKET_ID=-1 rule applies
- Also contains `TRANSACTION_TYPE`
- **Requires participation** — consumers must report activity to Luminate to access this data

**When to use:** Analyst needs to break down activity by streaming service or sales platform.

## Claim Fact Views

Owned/distributed content data. Shows consumption for content where your organization holds rights.

**Database:** `LUMINATE_DB_LISTING_CLAIM.EXTRACT_S`

**Available views:**
- `VW_DAILY_FACT_MR_CLAIM_DS`
- `VW_DAILY_FACT_MP_CLAIM_DS`

**Key characteristics:**
- Only available for MR and MP entities (no Song, Artist, MREL, MRELG)
- **US and CA only**
- Contains `PROVIDER_ID`, `PROVIDER_NAME`
- Contains `CONTENT_CREATION_CATEGORY` — classifies streams as Official, UGC (user-generated), or Non-Song UGC
- Non-owned content shows generic "Total" for CONTENT_CREATION_CATEGORY
- Contains `MARKET_ID` — same rules apply

**When to use:** Analyst needs consumption data filtered to owned/distributed catalog, or needs UGC classification.

## Airplay Fact Views

Airplay monitoring for radio/broadcast consumption.

**Available views:**
- `VW_DAILY_FACT_SONG_AIRPLAY_DETAIL_DS`
- `VW_DAILY_FACT_ARTIST_AIRPLAY_DETAIL_DS`
- `VW_DAILY_FACT_MRELG_AIRPLAY_DETAIL_DS`

**Key characteristics:**
- Only Song, Artist, and MRELG entities have airplay views
- Contains `STATION_ID` — join to `VW_STATION_DS` for station details
- Contains `AUDIENCE` field for reach/impression data
- Use `VW_STATION_FORMAT_MAP_DS` to get format assignments (point-in-time — filter by REPORT_DATE)

## Industry Analysis View

`VW_WEEKLY_FACT_ANALYSIS_DS` — industry-level aggregated data at the weekly level.

**Key characteristics:**
- Weekly grain: keyed by `WEEK_ID` (YYYYWW format) instead of REPORT_DATE
- Market-level: contains MARKET_ID
- Contains `RELEASE_AGE` — Current (0-36 months), Catalog (36-72), Deep Catalog (72+)
- Contains `GENRE_ID` and `GENRE_CLIENT_DOMAIN`
- Contains `REPORTED_QUANTITY` — pre-modeling quantity (differs from QUANTITY for ProductSales)
- Updated daily but limited to current quarter and last complete quarter

## Market Share Views

`VW_WEEKLY_FACT_MARKET_SHARE_DS` — in `LUMINATE_DB_LISTING_DETAIL.EXTRACT_S`
`VW_WEEKLY_FACT_MARKET_SHARE_LEGACY_DS` — in `LUMINATE_DB_LISTING_CLAIM.EXTRACT_S`

**Key characteristics:**
- Weekly grain: keyed by `WEEK_ID`
- Reports by `OWNER_BU_ID` and `DISTRIBUTOR_BU_ID` — join to `VW_BUSINESS_UNIT_DS` in claim database
- Contains `CONFLICT_TYPE` — indicates ownership conflicts (internal/external)
- Contains `RELEASE_AGE` and `GENRE_ID`
- Next Gen vs Legacy methodology (use Next Gen unless specifically asked for Legacy)

## EQUIVALENT_QUANTITY Column

- **US and CA**: Converts streams and sales into album-equivalent sales
- **All other territories**: Converts into premium-stream equivalents
- Useful for cross-format comparison (e.g., comparing streaming impact vs physical sales)
- Available in Detail, Summary, and Provider fact views

## Choosing the Right View — Quick Decision Tree

1. **Need metro-area data?** → Detail view
2. **Need provider breakdown?** → Provider view (if you have access) 
3. **Need owned-catalog + UGC split?** → Claim view (MR/MP only, US/CA only)
4. **Need airplay data?** → Airplay view (Song/Artist/MRELG only)
5. **None of the above?** → **Summary view** (default — fastest)
6. **Need period-end or monthly pre-aggregates?** → Internal models (see `internal-models.md`)