view: facts_prod_apple_podcasts_content_performance {

  sql_table_name: FACTS.PROD.APPLE_PODCASTS_CONTENT_PERFORMANCE ;;


  dimension: storefront_name {
    type: string
    description: "Territory in which the Podcast was consumed"
    sql: ${TABLE}.STOREFRONT_NAME ;;
  }

  dimension: show_identifier {
    type: number
    description: "The Apple Podcasts Show ID"
    sql: ${TABLE}.SHOW_IDENTIFIER ;;
    value_format: "0"
  }

  dimension: show_name {
    type: string
    description: "The show name"
    sql: ${TABLE}.SHOW_NAME ;;
  }

  dimension: episode_identifier {
    type: number
    description: "The Apple Podcasts Episode ID"
    sql: ${TABLE}.EPISODE_IDENTIFIER ;;
    value_format: "0"
  }

  dimension: episode_name {
    type: string
    description: "The episode name"
    sql: ${TABLE}.EPISODE_NAME ;;
  }

  dimension: episode_guid {
    type: string
    description: "The episode’s global unique identifier. Note that improper episode setups in Apple Podcasts Connect can result in duplicate episode setups in the Apple platform that causes multiple GUIDs for an episode to appear in the data"
    sql: ${TABLE}.EPISODE_GUID ;;
  }

  dimension: episode_type {
    type: string
    description: "Indicates whether the play was on an episode that was Apple hosted ('PSUB'), Apple hosted/free ('Plus'), or from an RSS feed ('STDQ')."
    sql: ${TABLE}.EPISODE_TYPE ;;
  }

  measure: total_listening_hours {
    type: sum
    view_label: "Total Listeners"
    description: "Aggregated listening time displayed in hours"
    sql: ${TABLE}.TOTAL_LISTENING_HOURS ;;
    value_format: "#,##0.00"
  }

  measure: number_of_episodes{
    type: count_distinct
    description: "Based off of unique count of Show Name and Episode GUID. Each podcast episode has a globally unique identifier (GUID) that is used as the unique episode identifier in this data model. Improper episode setups in Apple Podcasts Connect can result in duplicate episode setups in the Apple platform that causes multiple GUIDs for an episode to appear in the data."
    sql: concat(${TABLE}.SHOW_IDENTIFIER, ${TABLE}.EPISODE_GUID);;
  }

  measure: subscriber_listening_hours {
    type: sum
    view_label: "Subscribed Listeners"
    description: "Aggregated listening time of subscribers displayed in hours"
    sql: ${TABLE}.SUBSCRIBER_LISTENING_HOURS ;;
    value_format: "#,##0.00"
  }

  measure: non_subscriber_listening_hours {
    type: sum
    view_label: "Non-Subscribed Listeners"
    description: "Aggregated listening time of nonsubscribers displayed in hours"
    sql: ${TABLE}.NON_SUBSCRIBER_LISTENING_HOURS ;;
    value_format: "#,##0.00"
  }

  measure: total_listeners {
    type: sum
    view_label: "Total Listeners"
    description: "Aggregated count of listeners that have played more than 0 seconds of an episode"
    sql: ${TABLE}.TOTAL_LISTENERS ;;
    value_format: "#,##0"
  }

  measure: subscribed_listeners {
    type: sum
    view_label: "Subscribed Listeners"
    description: "Aggregated count of listeners with a subscription that have played more than 0 seconds of an episode"
    sql: ${TABLE}.SUBSCRIBED_LISTENERS ;;
    value_format: "#,##0"
  }

  measure: average_subscribed_listening_hours_by_total_engaged_subscribers {
    type: average
    view_label: "Subscribed Listeners"
    description: "Average listening hours of subscribers that played at least 20 minutes or 40% of an episode within a single session across a reporting period"
    sql: ${TABLE}.SUBSCRIBER_LISTENING_HOURS / ${TABLE}.SUBSCRIBED_ENGAGED_LISTENERS;;
    value_format: "#,##0.00"
  }

  measure: average_non_subscribed_listening_hours_by_total_engaged_non_subscribers {
    type: average
    view_label: "Non-Subscribed Listeners"
    description: "Average listening hours of nonsubscribers that played at least 20 minutes or 40% of an episode within a single session across a reporting period"
    sql: ${TABLE}.NON_SUBSCRIBER_LISTENING_HOURS / ${TABLE}.NON_SUBSCRIBED_ENGAGED_LISTENERS ;;
    value_format: "#,##0.00"
  }

  measure: average_listening_hours_by_total_listeners {
    type: average
    view_label: "Total Listeners"
    description: "Average total listening hours per listener"
    sql: ${TABLE}.TOTAL_LISTENING_HOURS /  ${TABLE}.TOTAL_LISTENERS;;
    value_format: "#,##0.00"
  }

  measure: average_subscribed_listeners_per_episode {
    type: number
    view_label: "Subscribed Listeners"
    description: "Average subscribed listeners per episode. Based off of unique count of Show Name and Episode GUID. Each podcast episode has a globally unique identifier (GUID) that is used as the unique episode identifier in this data model. Improper episode setups in Apple Podcasts Connect can result in duplicate episode setups in the Apple platform that causes multiple GUIDs for an episode to appear in the data."
    sql: coalesce(div0(sum(${TABLE}.SUBSCRIBED_LISTENERS), count(distinct concat(${TABLE}.SHOW_IDENTIFIER, ${TABLE}.EPISODE_GUID))), 0) ;;
    value_format: "#,##0"
  }

  measure: average_non_subscribed_listeners_per_episode {
    type: number
    view_label: "Non-Subscribed Listeners"
    description: "Average nonsubscribed listeners per episode. Based off of unique count of Show Name and Episode GUID. Each podcast episode has a globally unique identifier (GUID) that is used as the unique episode identifier in this data model. Improper episode setups in Apple Podcasts Connect can result in duplicate episode setups in the Apple platform that causes multiple GUIDs for an episode to appear in the data."
    sql: coalesce(div0(sum(${TABLE}.NON_SUBSCRIBED_LISTENERS), count(distinct concat(${TABLE}.SHOW_IDENTIFIER, ${TABLE}.EPISODE_GUID))), 0) ;;
    value_format: "#,##0"
  }

  measure: non_subscribed_listeners {
    type: sum
    view_label: "Non-Subscribed Listeners"
    description: "Total listeners without a subscription that have played more than 0 seconds of an episode"
    sql: ${TABLE}.NON_SUBSCRIBED_LISTENERS ;;
    value_format: "#,##0"
  }

  measure: total_engaged_listeners {
    type: sum
    view_label: "Total Listeners"
    description: "Total listeners that played at least 20 minutes or 40% of an episode within a single session across the entire reporting period."
    sql: ${TABLE}.TOTAL_ENGAGED_LISTENERS ;;
    value_format: "#,##0"
  }

  measure: subscribed_engaged_listeners {
    type: sum
    view_label: "Subscribed Listeners"
  description: "Total listeners with a subscription that played at least 20 minutes or 40% of an episode within a single session across the entire reporting period."
    sql: ${TABLE}.SUBSCRIBED_ENGAGED_LISTENERS ;;
    value_format: "#,##0"
  }

  measure: non_subscribed_engaged_listeners {
    type: sum
    view_label: "Non-Subscribed Listeners"
    description: "Total listeners without a subscription that played at least 20 minutes or 40% of an episode within a single session across the entire reporting period."
    sql: ${TABLE}.NON_SUBSCRIBED_ENGAGED_LISTENERS ;;
    value_format: "#,##0"
  }

  measure: total_plays {
    type: sum
    view_label: "Total Listeners"
    description: "Total plays where the play duration is greater than 0 seconds"
    sql: ${TABLE}.TOTAL_PLAYS ;;
    value_format: "#,##0"
  }

  measure: subscriber_plays {
    type: sum
    view_label: "Subscribed Listeners"
    description: "Total plays coming from subscribers with a subscription where the play duration is greater than 0 seconds."
    sql: ${TABLE}.SUBSCRIBER_PLAYS ;;
    value_format: "#,##0"
  }

  measure: non_subscriber_plays {
    type: sum
    view_label: "Non-Subscribed Listeners"
    description: "Total plays coming from listeners without a subscription where the play duration is greater than 0 seconds."
    sql: ${TABLE}.NON_SUBSCRIBER_PLAYS ;;
    value_format: "#,##0"
  }

  measure: average_subscribed_plays_per_subscribed_listener {
    type: average
    view_label: "Subscribed Listeners"
    description: "Average plays by subscribers where the play duration is greater than 0 seconds"
    sql: ${TABLE}.SUBSCRIBER_PLAYS /  ${TABLE}.SUBSCRIBED_LISTENERS;;
    value_format: "#,##0.00"
  }

  measure: average_non_subscribed_plays_per_non_subscribed_listener {
    type: average
    view_label: "Non-Subscribed Listeners"
    description: "Average plays coming from listeners without a subscription where the play duration is greater than 0 seconds."
    sql: ${TABLE}.NON_SUBSCRIBER_PLAYS /  ${TABLE}.NON_SUBSCRIBED_LISTENERS;;
    value_format: "#,##0.00"
  }

  measure: average_plays_per_listener {
    type: average
    view_label: "Total Listeners"
    description: "Average plays coming from all listeners where the play duration is greater than 0 seconds."
    sql: ${TABLE}.TOTAL_PLAYS /  ${TABLE}.TOTAL_LISTENERS;;
    value_format: "#,##0.00"
  }

  measure: subscriber_listening_hours_as_share_of_total_listening_hours {
    type: number
    view_label: "Subscribed Listeners"
    description: "Percentage of subscriber listening hours out of total listening hours"
    sql: sum(${TABLE}.SUBSCRIBER_LISTENING_HOURS) / sum(NULLIF(${TABLE}.TOTAL_LISTENING_HOURS, 0));;
    value_format: "0.00%"
  }

  measure: subscriber_plays_as_share_of_total_plays {
    type: number
    view_label: "Subscribed Listeners"
    description: "Percentage of subscriber plays out of total plays"
    sql: sum(${TABLE}.SUBSCRIBER_PLAYS) / sum(NULLIF(${TABLE}.TOTAL_PLAYS, 0));;
    value_format: "0.00%"
  }

  measure: engaged_subscribed_listeners_as_share_of_total_enagaged_listeners {
    type: number
    view_label: "Subscribed Listeners"
    description: "Percentage of engaged subscribed listeners out of total engaged listeners"
    sql: sum(${TABLE}.SUBSCRIBED_ENGAGED_LISTENERS) / sum(NULLIF(${TABLE}.TOTAL_ENGAGED_LISTENERS, 0));;
    value_format: "0.00%"
  }

  measure: engaged_subscribed_listeners_as_share_of_total_listeners {
    type: number
    view_label: "Subscribed Listeners"
    description: "Percentage of subscribed engaged listeners out of total listeners"
    sql: sum(${TABLE}.SUBSCRIBED_ENGAGED_LISTENERS) / sum(NULLIF(${TABLE}.TOTAL_LISTENERS, 0));;
    value_format: "0.00%"
  }

  measure: average_subscribed_hours_per_subscribed_play {
    type: number
    view_label: "Subscribed Listeners"
    description: "Percentage of total subscriber listening hours out of total subscriber plays"
    sql: sum(${TABLE}.SUBSCRIBER_LISTENING_HOURS) / sum(NULLIF(${TABLE}.SUBSCRIBER_PLAYS, 0));;
    value_format: "#,##0.00"
  }

  measure: average_non_subscribed_hours_per_non_subscribed_play {
    type: number
    view_label: "Non-Subscribed Listeners"
    description: "Percentage of total nonsubscriber listening hours out of total nonsubscriber plays"
    sql: sum(${TABLE}.NON_SUBSCRIBER_LISTENING_HOURS) / sum(NULLIF(${TABLE}.NON_SUBSCRIBER_PLAYS, 0));;
    value_format: "#,##0.00"
  }

  dimension_group: download_date {
    view_label: "Date"
    label: "Download"
    description: "Refers to when the report was downloaded from Apple Podcasts Connect"
    type: time
    sql: ${TABLE}.download_date ;;
    timeframes: [year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num
    ]
  }

}