# View: tiktok_carveout_monetization

**View Name:** tiktok_carveout_monetization
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Orchadmin_Users, Art_Relations_Prod_Art_Relations.Owner, Art_Relations_Prod_Art_Relations.Vendor_Closers, Dbt_Prod.Awal_Tier_Groups, Dbt_Prod.Restrictions_Label_Store + 3 more`
**File Path:** `views/tiktok_conflicts/tiktok_carveout_monetization.view.lkml`

## Overview

- **File Size:** 11819 bytes
- **Lines of Code:** 250
- **Dimensions:** 8
- **Measures:** 2
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `label_id` | string |
| `label_tier` | string |
| `label_name` | string |
| `owner` | string |
| `brand` | string |
| `closer` | string |
| `relationship_manager` | string |
| `monetization_territories` | string |

## Measures

| Name | Type |
|------|------|
| `monetization_territory_count` | sum |
| `gross_revenue_usd` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `label_created_date` | time |

## Derived Table

```sql
sql:
    with tiktok_carveout as (
    WITH int_prod_vw_restrictions_label_store AS
        (SELECT * FROM intelligence.dbt_prod.restrictions_label_store WHERE 1=1)

      SELECT DISTINCT
      (TO_CHAR(TO_DATE(orch_app_ar_vendor.date_created ), 'YYYY-MM-DD')) AS label_created_date,
      orch_app_ar_vendor.vendor_id  AS label_id,
      IFF(orch_app_ar_vendor.company IS NULL OR orch_app_ar_vendor.company  = '', orch_app_ar_vendor.name, orch_app_ar_vendor.company)  AS label_name,
      int_dbt_prod_awal_tier_groups.service_tier_name as label_tier,
      int_dbt_prod_awal_tier_groups.owner  AS owner,
      int_dbt_prod_awal_tier_groups.brand_name  AS brand,
      LISTAGG(DISTINCT closer.f_name||' '||closer.l_name, ', ')  AS closer,
      label_manager.f_name||' '||label_manager.l_name  AS relationship_manager
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  AS orch_app_ar_owner
      INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor  AS orch_app_ar_vendor
      ON lower(orch_app_ar_owner.owner_abbrivation) = lower(orch_app_ar_vendor.owner)
      LEFT JOIN int_prod_vw_restrictions_label_store
      ON orch_app_ar_vendor.vendor_id=int_prod_vw_restrictions_label_store.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS label_manager
      ON orch_app_ar_vendor.assigned_to = label_manager.id
      LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VENDOR_CLOSERS  AS orch_app_ar_vendor_closers
      ON orch_app_ar_vendor_closers.VENDOR_ID = orch_app_ar_vendor.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS closer
      ON orch_app_ar_vendor_closers.ORCHADMIN_USER_ID = closer.id
      LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
      ON orch_app_ar_vendor.vendor_id= int_dbt_prod_awal_tier_groups.labelid
      WHERE ((UPPER(( int_prod_vw_restrictions_label_store.list_aggregate_restrictions  )) LIKE UPPER('%TikTok (Audio Library)%')))
      group by all
      ),

      no_tiktok_carveout as (
      WITH int_prod_vw_restrictions_label_store AS
      (SELECT * FROM intelligence.dbt_prod.restrictions_label_store WHERE 1=1)

      SELECT DISTINCT
      (TO_CHAR(TO_DATE(orch_app_ar_vendor.date_created ), 'YYYY-MM-DD')) AS label_created_date,
      orch_app_ar_vendor.vendor_id  AS label_id,
      IFF(orch_app_ar_vendor.company IS NULL OR orch_app_ar_vendor.company  = '', orch_app_ar_vendor.name, orch_app_ar_vendor.company)  AS label_name,
      int_dbt_prod_awal_tier_groups.service_tier_name as label_tier,
      int_dbt_prod_awal_tier_groups.owner  AS owner,
      int_dbt_prod_awal_tier_groups.brand_name  AS brand,
      LISTAGG(DISTINCT closer.f_name||' '||closer.l_name, ', ')  AS closer,
      label_manager.f_name||' '||label_manager.l_name  AS relationship_manager
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  AS orch_app_ar_owner
      INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor  AS orch_app_ar_vendor
      ON lower(orch_app_ar_owner.owner_abbrivation) = lower(orch_app_ar_vendor.owner)
      LEFT JOIN int_prod_vw_restrictions_label_store
      ON orch_app_ar_vendor.vendor_id=int_prod_vw_restrictions_label_store.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS label_manager
      ON orch_app_ar_vendor.assigned_to = label_manager.id
      LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VENDOR_CLOSERS  AS orch_app_ar_vendor_closers
      ON orch_app_ar_vendor_closers.VENDOR_ID = orch_app_ar_vendor.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS closer
      ON orch_app_ar_vendor_closers.ORCHADMIN_USER_ID = closer.id
      LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
      ON orch_app_ar_vendor.vendor_id= int_dbt_prod_awal_tier_groups.labelid
      WHERE ((UPPER(( int_prod_vw_restrictions_label_store.list_aggregate_restrictions  )) NOT LIKE UPPER('%TikTok (Audio Library)%')))
      group by all
      ),

      territory_carveout as (
      SELECT
      orch_app_ar_vendor.vendor_id  AS label_id,
      IFF(orch_app_ar_vendor.company IS NULL OR orch_app_ar_vendor.company  = '', orch_app_ar_vendor.name, orch_app_ar_vendor.company)  AS label_name,
      int_dbt_prod_awal_tier_groups.service_tier_name as label_tier,
      int_dbt_prod_awal_tier_groups.owner  AS owner,
      int_dbt_prod_awal_tier_groups.brand_name  AS brand,
      listagg(distinct closer.f_name||' '||closer.l_name)  AS closer,
      label_manager.f_name||' '||label_manager.l_name  AS relationship_manager,
      int_prod_vw_restrictions_label_territory.territory_carveout  AS territory_restrictions,
      country.name as country_name,
      country.country_code as country_code,
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  AS orch_app_ar_owner
      INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor  AS orch_app_ar_vendor
      ON lower(orch_app_ar_owner.owner_abbrivation) = lower(orch_app_ar_vendor.owner)
      LEFT JOIN (
      SELECT
      r.vendor_id,
      TRIM(f.value) AS territory_carveout
      FROM intelligence.dbt_prod.restrictions_label_territory r,
      LATERAL FLATTEN(
      INPUT => SPLIT(r.list_aggregate_restrictions, ';'),
      OUTER => TRUE) f
      ) AS int_prod_vw_restrictions_label_territory
      ON orch_app_ar_vendor.vendor_id=int_prod_vw_restrictions_label_territory.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS label_manager
      ON orch_app_ar_vendor.assigned_to = label_manager.id
      LEFT JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor_contract  AS orch_app_ar_vendor_contract
      ON orch_app_ar_vendor.vendor_id = orch_app_ar_vendor_contract.vendor_id
      LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VENDOR_CLOSERS  AS orch_app_ar_vendor_closers
      ON orch_app_ar_vendor_closers.VENDOR_ID = orch_app_ar_vendor.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS closer
      ON orch_app_ar_vendor_closers.ORCHADMIN_USER_ID = closer.id
      LEFT JOIN intelligence.dbt_prod.active_vendor_contracts  AS active_label
      ON orch_app_ar_vendor.vendor_id = active_label.vendor_id and orch_app_ar_vendor_contract.id = active_label.vendor_contract_id
      LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
      ON orch_app_ar_vendor.vendor_id= int_dbt_prod_awal_tier_groups.labelid
      LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.COUNTRY as country
      on country.name = int_prod_vw_restrictions_label_territory.territory_carveout
      WHERE (IFF(active_label.vendor_id IS NULL, FALSE, TRUE) )
      AND (int_prod_vw_restrictions_label_territory.territory_carveout ) IS NOT NULL
      group by all
      ),

      territory_carveout_flattened as (
      select label_id,
      label_name,
      label_tier,
      owner,
      brand,
      closer,
      relationship_manager,
      listagg(distinct country_code, ',') within group (order by country_code) country_code_list
      from territory_carveout
      group by all
      ),

      monetization as (
      SELECT
      ORCHARD_OBJ_ID AS label_id,
      LISTAGG(TERRITORY, ',') AS monetization_territories,
      count(territory) as monetization_territory_count
      FROM FACTS.PROD.HAS_FINGERPRINT_RULE
      WHERE SERVICE = 'tiktok'
      AND ORCHARD_OBJ_TYPE = 'Vendor'
      AND policy = 'monetize'
      GROUP BY 1
      ),

      no_monetization as (
      SELECT
      ORCHARD_OBJ_ID AS label_id,
      LISTAGG(TERRITORY, ',') within group (order by territory) AS no_monetization_territories,
      count(territory) as no_monetization_territory_count
      FROM FACTS.PROD.HAS_FINGERPRINT_RULE
      WHERE SERVICE = 'tiktok'
      AND ORCHARD_OBJ_TYPE = 'Vendor'
      AND policy = 'carveout'
      GROUP BY 1
      ),

      ltm_revenue as (
      SELECT
      facts_prod_dim_label.labelid  AS label_id,
      COALESCE(SUM(case
      when vw_abacus_fact_sales_distro.sale_currency_code = 'USD' then vw_abacus_fact_sales_distro.gross_revenue_sale_currency
      when vw_abacus_fact_sales_distro.sale_currency_code != 'USD' and  orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code  = 'USD' then vw_abacus_fact_sales_distro.gross_revenue_sale_currency *  orch_app_ar_prod_royalty_accounting_exchange_rate.rate
      else 0
      end), 0) AS gross_revenue_usd
      FROM royalty_accounting.prod.abacus_fact_sales_unified_dbt  AS vw_abacus_fact_sales_distro
      LEFT JOIN facts.prod.dim_label  AS facts_prod_dim_label ON vw_abacus_fact_sales_distro.label_id = facts_prod_dim_label.labelid
      LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD  AS orch_app_ar_royalty_statement_period ON vw_abacus_fact_sales_distro.statement_period_id = orch_app_ar_royalty_statement_period.statement_period_id
      LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.EXCHANGE_RATE AS orch_app_ar_prod_royalty_accounting_exchange_rate ON vw_abacus_fact_sales_distro.statement_period_id= orch_app_ar_prod_royalty_accounting_exchange_rate.statement_period_id and
      vw_abacus_fact_sales_distro.sale_currency_code=orch_app_ar_prod_royalty_accounting_exchange_rate.from_currency_code
      and orch_app_ar_prod_royalty_accounting_exchange_rate.to_currency_code = 'USD'
      WHERE ((( DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01')   ) >= ((DATEADD('month', -12, DATE_TRUNC('month', CURRENT_DATE())))) AND ( DATE_FROM_PARTS(orch_app_ar_royalty_statement_period.statement_year, orch_app_ar_royalty_statement_period.statement_month, '01')   ) < ((DATEADD('month', 13, DATEADD('month', -12, DATE_TRUNC('month', CURRENT_DATE())))))))
      GROUP BY ALL
      )


      select tc.*,
      m.* exclude (label_id),
      r.gross_revenue_usd
      from tiktok_carveout tc
      inner join monetization m
      on tc.label_id = m.label_id
      left join ltm_revenue r
      on tc.label_id = r.label_id
      ;;
```