# View: cpm_fill_rate

**View Name:** cpm_fill_rate
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Youtube_Channel, Dbt_Prod.Awal_Tier_Groups, Dbt_Prod.Youtube_Ad_Supported_Mrr_Update, Intelligence.Dbt_Prod, Latest_Ad_Month + 3 more`
**File Path:** `views/yt_performance_report/cpm_fill_rate.view.lkml`

## Overview

- **File Size:** 2779 bytes
- **Lines of Code:** 95
- **Dimensions:** 0
- **Measures:** 3
- **Dimension Groups:** 1
- **Filters:** 0

## Measures

| Name | Type |
|------|------|
| `estimated_cpm` | average |
| `estimated_monetized_playbacks` | sum |
| `avod_views` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `download_date` | time |

## Derived Table

```sql
sql:
  WITH latest_ad_month AS (
  SELECT DATE_TRUNC('month', MAX(download_date)) AS max_month
  FROM INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE
),

valid_cpm_months AS (
  SELECT DISTINCT DATE_TRUNC('month', download_activity_date) AS month
  FROM facts.prod.fact_youtube_video_analytics
),

shared_latest_month AS (
  SELECT lam.max_month
  FROM latest_ad_month lam
  INNER JOIN valid_cpm_months vcm
    ON lam.max_month = vcm.month
),

cpm_data AS (
  SELECT
    DATE_TRUNC('month', download_activity_date) AS download_date,
    AVG(estimated_cpm) AS estimated_cpm,
    SUM(estimated_monetized_playbacks) AS estimated_monetized_playbacks
  FROM facts.prod.fact_youtube_video_analytics
  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.youtube_channel AS yt_channel
    ON facts.prod.fact_youtube_video_analytics.channelid = yt_channel.youtube_channel_id
  LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS AS tier
    ON yt_channel.vendor_id = tier.labelid
  WHERE DATE_TRUNC('month', download_activity_date) <= (SELECT max_month FROM shared_latest_month)
    AND DATE_TRUNC('month', download_activity_date) >= DATEADD(month, -12, (SELECT max_month FROM shared_latest_month))
    AND (tier.brand_name NOT IN ('awal','altafonte') OR tier.brand_name IS NULL)
  GROUP BY 1
),

ad_views AS (
  SELECT
    download_date,
    SUM(owned_views) AS owned_views
  FROM
    INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE ad
  LEFT JOIN facts.prod.dim_release AS dim_release
    ON CAST(ad.current_upc AS VARCHAR) = CAST(dim_release.releaseid AS VARCHAR)
  LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS AS tier
    ON dim_release.labelid = tier.labelid
  WHERE download_date <= (SELECT max_month FROM shared_latest_month)
    AND download_date >= DATEADD(month, -12, (SELECT max_month FROM shared_latest_month))
    AND (tier.brand_name NOT IN ('awal','altafonte') OR tier.brand_name IS NULL)
  GROUP BY 1
)

SELECT
  cpm.download_date,
  cpm.estimated_cpm,
  cpm.estimated_monetized_playbacks,
  ad_views.owned_views AS avod_views
FROM cpm_data cpm
JOIN ad_views
  ON cpm.download_date = ad_views.download_date
ORDER BY cpm.download_date DESC

    ;;
```