view: facts_prod_apple_podcasts_subscription_events_monthly {

  sql_table_name: FACTS.PROD.APPLE_PODCASTS_SUBSCRIPTION_EVENTS_MONTHLY ;;

  dimension: source {
    type: string
    description: "Refers to whether the data pulls from the Daily, Weekly or Monthly Apple Connect Podcasting files"
    sql: ${TABLE}.source ;;
  }

  dimension_group: report_date {
    view_label: "Date"
    label: "Report"
    description: "Refers to the reporting date that the report was initially generated by Apple"
    type: time
    sql: ${TABLE}.report_date ;;
    timeframes: [year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num
    ]
  }

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

  dimension_group: event_date {
    view_label: "Date"
    label: "Event"
    description: "The date on which the event was logged by Apple"
    type: time
    sql: ${TABLE}.event_date ;;
    timeframes: [year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num
    ]
  }

  dimension: channel_apple_id {
    type: string
    description: "The unique string which Apple uses to label a podcast channel"
    sql: ${TABLE}.channel_apple_id ;;
    value_format: "0"
  }

  dimension: channel_name {
    type: string
    description: "Sony designated name for the channel"
    sql: ${TABLE}.channel_name ;;
  }

  dimension: event_name {
    type: string
    description: "Type of event: Subscribe, Renew, Cancel, etc."
    sql: ${TABLE}.event_name ;;
  }

  dimension: subscription_apple_id {
    type: string
    description: "The unique string which Apple associates with any particular subscription offering"
    sql: ${TABLE}.subscription_apple_id ;;
    value_format: "0"
  }

  dimension: subscription_name {
    type: string
    description: "Sony designated name for the subscription offering and duration"
    sql: ${TABLE}.subscription_name ;;
  }

  dimension: subscription_sub_type {
    type: string
    description: "Used to delineate between subscription states that are free or paid. If a subscriber is in their free trial, that will be indicated in this field. If the subscriber is in a paying state, this field will be blank."
    sql: ${TABLE}.subscription_sub_type ;;
  }

  dimension: subscription_duration {
    type: string
    description: "Duration of subscription"
    sql: ${TABLE}.subscription_duration ;;
  }

  dimension_group: subscription_start_date {
    view_label: "Date"
    label: "Subscription Start"
    description: "Apple reported date referring to the subscription start date"
    type: time
    sql: ${TABLE}.subscription_start_date ;;
    timeframes: [year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num
    ]
  }

  dimension: device {
    type: string
    description: "The type of device where the event occurred"
    sql: ${TABLE}.device ;;
  }

  dimension: prev_subscription_apple_id {
    type: string
    description: "The Apple ID of the previous subscription"
    sql: ${TABLE}.prev_subscription_apple_id ;;
  }

  dimension: prev_subscription_name {
    type: string
    description: "The name of the previous subscription"
    sql: ${TABLE}.prev_subscription_name ;;
  }

  dimension: preserved_pricing {
    type: string
    description: "When a subscription renews and the original price is preserved, this field equals “Yes.” Otherwise, it is blank"
    sql: ${TABLE}.preserved_pricing ;;
  }

  dimension: proceeds_reason {
    type: string
    description: "When a subscription renews and the subscription has been active for more than a year, you receive 85% of the customer price minus applicable taxes, and this field equals “Rate After One Year.” Otherwise, you receive 70% and the field is blank."
    sql: ${TABLE}.proceeds_reason ;;
  }

  dimension: cancellation_reason {
    type: string
    description: "Reason for a cancellation: Billing issue, Price increase, Canceled, Removed from Sale, or Other"
    sql: ${TABLE}.cancellation_reason ;;
  }

  measure: quantity {
    type: sum
    description: "Number of users included in the subscription"
    sql: ${TABLE}.quantity ;;
  }

  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
    ]
  }

  dimension: event_type_grouped {
    type: string
    label: "Event Type Grouped"
    view_label: "Event Type"
    description: "Sony designated event groupings"
    sql:
        CASE
          WHEN ${TABLE}.event_name = 'Subscribe' then 'New Paid Subscribers'
          WHEN ${TABLE}.event_name = 'Start Introductory Offer' and ${TABLE}.subscription_sub_type = 'Free Trial' then 'New Free Trial Subscribers'
          WHEN ${TABLE}.event_name = 'Reactivate' then 'New Paid Subscribers'
          WHEN ${TABLE}.event_name = 'Reactivate to Introductory Offer' and ${TABLE}.subscription_sub_type = 'Free Trial' then 'New Free Trial Subscribers'
          WHEN ${TABLE}.event_name = 'Reactivate with Crossgrade' then 'New Paid Subscribers'
          WHEN ${TABLE}.event_name = 'Reactivate with Crossgrade to Introductory Offer' and ${TABLE}.subscription_sub_type = 'Free Trial' then 'New Free Trial Subscribers'
          WHEN ${TABLE}.event_name = 'Paid Subscription from Introductory Offer' then 'New Paid Subscribers'
          WHEN ${TABLE}.event_name = 'Crossgrade from Introductory Offer' then 'New Paid Subscribers'
          WHEN ${TABLE}.event_name = 'Cancel' then 'Cancel Paid Subscribers'
          WHEN ${TABLE}.event_name = 'Cancel' and ${TABLE}.subscription_sub_type = 'Free Trial' then 'Cancel Free Subscribers'
          WHEN ${TABLE}.event_name = 'Canceled from Billing Retry' then 'Cancel Paid Subscribers'
          WHEN ${TABLE}.event_name = 'Canceled from Billing Retry' and ${TABLE}.subscription_sub_type = 'Free Trial' then 'Cancel Free Subscribers'
          WHEN ${TABLE}.event_name = 'Canceled from Grace Period' then 'Cancel Paid Subscribers'
          WHEN ${TABLE}.event_name = 'Canceled from Grace Period' and ${TABLE}.subscription_sub_type = 'Free Trial' then 'Cancel Free Subscribers'
          WHEN ${TABLE}.event_name = 'Refund' then 'Cancel Paid Subscribers'
          ELSE 'Other'
        END
              ;;
  }

  dimension: apple_vendor_id {
    description: "Apple Podcasts identifier that is associated to the Legal Entity of the Apple Podcasts Connect account transactions"
    type: number
    label: "Apple Vendor ID"
    sql: ${TABLE}.vendor_id ;;
  }

  dimension: apple_vendor_name {
    description: "Description for the Apple Vendor ID that is associated to the Legal Entity of the Apple Podcasts Connect account transactions"
    type: string
    label: "Apple Vendor Name"
    sql: ${TABLE}.vendor_name ;;
  }

}