version: 2
models:
  - name: EMAIL_ANALYTICS_BENCHMARK_BY_ACCOUNT_ARTIST_V2_DBT
    description: Benchmarks collected from Sendgrid, grouped by artist and email type, for finished campaigns. Extends V1 with EMAIL_TYPE dimension using INT_EMAIL_ANALYTICS_V3_DBT as source.
    tests:
      - dbt_utils.unique_combination_of_columns:
          arguments:
            combination_of_columns:
              - VENDOR_ID
              - SUBACCOUNT_ID
              - GLOBAL_PARTICIPANT_ID
              - CUSTOM_LIST_ID
              - EMAIL_TYPE
      - dbt_utils.expression_is_true:
          arguments:
            expression: "global_participant_id is not null"
            config:
              where: "custom_list_id is null"
      - dbt_utils.expression_is_true:
          arguments:
            expression: "custom_list_id is not null"
            config:
              where: "global_participant_id is null"
    columns:
      - name: GLOBAL_PARTICIPANT_ID
        description: Id representing artist(global participant)
      - name: CUSTOM_LIST_ID
        description: Id representing custom list from CRM
      - name: VENDOR_ID
        description: Id representing vendor
        tests:
          - not_null
      - name: SUBACCOUNT_ID
        description: Id representing subaccount
        tests:
          - not_null
      - name: EMAIL_TYPE
        description: Type of email (campaign or automated)
        tests:
          - not_null
      - name: AVG_OPEN_RATE
        description: Weighted average open rate
        tests:
          - not_null
      - name: AVG_CLICK_THROUGH_RATE
        description: Weighted average click through rate
        tests:
          - not_null
      - name: AVG_BOUNCE_RATE
        description: Weighted average bounce rate
        tests:
          - not_null
      - name: AVG_DELIVERED_RATE
        description: Weighted average delivery rate
        tests:
          - not_null
      - name: AVG_UNIQUE_CLICKS
        description: Weighted average unique clicks
        tests:
          - not_null
      - name: AVG_CLICK_TO_OPEN_RATE
        description: Weighted average click to open rate
        tests:
          - not_null
      - name: AVG_SPAM_COMPLAINTS_RATE
        description: Weighted average spam complaints rate
        tests:
          - not_null
      - name: AVG_UNSUBSCRIBE_RATE
        description: Weighted average unsubscribe rate
        tests:
          - not_null
  - name: EMAIL_ANALYTICS_BY_CAMPAIGN_FAN_DBT
    description: Metrics collected from Sendgrid regarding email events.
    tests:
      - dbt_utils.unique_combination_of_columns:
          arguments:
            combination_of_columns:
              - CAMPAIGN_ID
              - FAN_ID
    columns:
      - name: CAMPAIGN_ID
        description: Email campaign ID
        tests:
          - not_null
      - name: FAN_ID
        description: Fan ID
        tests:
          - not_null
      - name: DELIVERED
        description: Message delivered mark
        tests:
          - not_null
      - name: OPENED
        description: Message opened mark
        tests:
          - not_null
      - name: CLICKED
        description: Message clicked mark
        tests:
          - not_null
  - name: V_EMAIL_ANALYTICS_V3_DBT
    description: View for metrics collected from Sendgrid regarding email clicks statistics.
    columns:
      - name: EMAIL_ID
        description: Email campaign/automated unique identifier.
        tests:
          - unique
      - name: EMAIL_TYPE
        description: Email campaign/automated type.
        tests:
          - not_null
      - name: LAST_EVENT_AT
        description: Last event time
        tests:
          - not_null
      - name: SENDS
        description: Send events count
        tests:
          - not_null
      - name: DROPS
        description: Drop events count
        tests:
          - not_null
      - name: DELIVERED
        description: Delivered events count
        tests:
          - not_null
      - name: OPENS
        description: Open events count
        tests:
          - not_null
      - name: CLICKS
        description: Click events count
        tests:
          - not_null
      - name: UNIQUE_OPENS
        description: Unique open events count
        tests:
          - not_null
      - name: UNIQUE_CLICKS
        description: Unique click events count
        tests:
          - not_null
      - name: UNIQUE_BOUNCES
        description: Unique bounce events count
        tests:
          - not_null
      - name: UNIQUE_SPAM_COMPLAINTS
        description: Unique spam complaint events count
        tests:
          - not_null
      - name: UNSUBSCRIBES
        description: Unsubscribe events count
        tests:
          - not_null
      - name: OPEN_RATE
        description: Open rate
      - name: DELIVERED_RATE
        description: Delivered rate
      - name: CLICK_THROUGH_RATE
        description: Click through rate
      - name: CLICK_TO_OPEN_RATE
        description: Click to open rate
      - name: BOUNCE_RATE
        description: Bounce rate
      - name: SPAM_COMPLAINTS_RATE
        description: Spam complaints rate
      - name: UNSUBSCRIBE_RATE
        description: Unsubscribe rate
      - name: UNIQUE_OPENS_DROPOFF
        description: Unique opens dropoff
      - name: UNIQUE_CLICKS_DROPOFF
        description: Unique clicks dropoff
  - name: V_EMAIL_ANALYTICS_BY_LINK_V3_DBT
    description: View for email campaign url click analytics.
    columns:
      - name: EMAIL_ID
        description: Email campaign/automated identifier
        tests:
          - not_null
      - name: EMAIL_TYPE
        description: Email campaign/automated type
        tests:
          - not_null
      - name: URL
        description: link url
        tests:
          - not_null
      - name: TOTAL_CLICKS
        description: Total clicks count for campaigns url
        tests:
          - not_null
      - name: UNIQUE_CLICKS
        description: Unique clicks count for campaigns url
        tests:
          - not_null
      - name: PERCENTAGE_OF_TOTAL_CLICKS
        description: Percentage of total clicks for this campaign
        tests:
          - not_null