Waitlist New

-- models/waitlist_new.sql
{{ config(materialized='view') }}

select
  w.id,
  w.email,
  w.zip,
  w.partner_id,
  w.created_at,
  DATE_FORMAT(w.created_at,'%Y-%v') AS created_number_week,
  users1.user_id
from waitlist w
left join {{ ref('users') }} as users1 on w.email = users1.email_id

UNION ALL

select
  w.id,
  w.email,
  w.zip,
  w.partner_id,
  w.created_at,
  DATE_FORMAT(w.created_at,'%Y-%v') AS created_number_week,
  users1.user_id
from waitlist w
right join {{ ref('users') }} as users1 on w.email = users1.email_id
where w.email is NULL

# models/schema.yml
version: 2

models:
  - name: waitlist_new
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: user_id
      - name: created_number_week
      - name: email
      - name: partner_id
      - name: zip
      - name: paid_lead
      - name: paid_purhcase
      - name: <% parameter promo_trial %>_purchase
      - name: month_converted
      - name: days_until_conversion

Assumptions

• Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

select
  w.id,
  w.email,
  w.zip,
  w.partner_id,
  w.created_at,
  DATE_FORMAT(w.created_at,'%Y-%v') AS created_number_week,
  users1.user_id
from waitlist w
left join ${users.SQL_TABLE_NAME} as users1 on w.email = users1.email_id

UNION ALL

select
  w.id,
  w.email,
  w.zip,
  w.partner_id,
  w.created_at,
  DATE_FORMAT(w.created_at,'%Y-%v') AS created_number_week,
  users1.user_id
from waitlist w
right join ${users.SQL_TABLE_NAME} as users1 on w.email = users1.email_id
where w.email is NULL

view: waitlist_new {
  derived_table: {
    sql: select
  w.id,
  w.email,
  w.zip,
  w.partner_id,
  w.created_at,
  DATE_FORMAT(w.created_at,'%Y-%v') AS created_number_week,
  users1.user_id
from waitlist w
left join ${users.SQL_TABLE_NAME} as users1 on w.email = users1.email_id

UNION ALL

select
  w.id,
  w.email,
  w.zip,
  w.partner_id,
  w.created_at,
  DATE_FORMAT(w.created_at,'%Y-%v') AS created_number_week,
  users1.user_id
from waitlist w
right join ${users.SQL_TABLE_NAME} as users1 on w.email = users1.email_id
where w.email is NULL ;;
  }

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: user_id {
    sql: ${TABLE}.user_id ;;
  }

  dimension: created_number_week {
    sql: ${TABLE}.created_number_week ;;
  }

  dimension: email {
    sql: ${TABLE}.email ;;
  }

  dimension: partner_id {
    type: int
    sql: ${TABLE}.partner_id ;;
  }

  dimension: zip {
    sql: ${TABLE}.zip ;;
  }

  dimension: paid_lead {
    type: yesno
    sql: ${conversions_campaigns_waitlist.utm_medium} in ('cpc', 'ocpm', 'affiliate', 'cpr', 'print', 'display', 'mobile', 'paidsocial', 'mail') ;;
  }

  dimension: paid_purhcase {
    type: yesno
    sql: ${conversions_campaigns.utm_medium} in ('cpc', 'ocpm', 'affiliate', 'cpr', 'print', 'display', 'mobile', 'paidsocial', 'mail') ;;
  }

  dimension: <% parameter promo_trial %>_purchase {
    type: yesno
    sql: ${promotions_table.category} = '<% parameter promo_trial %>' ;;
  }

  dimension: month_converted {
    type: int
    sql: TIMESTAMPDIFF(MONTH, ${TABLE}.created_at, ${users.created_date}) ;;
  }

  dimension: days_until_conversion {
    type: int
    sql: TIMESTAMPDIFF(DAY, ${TABLE}.created_at, ${users.created_date}) ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.created_at ;;
  }

  measure: average_days_until_conversion {
    type: avg
    value_format: "0.0"
    sql: ${days_until_conversion} ;;
  }

  measure: count {
    type: count
    drill_fields: [id]
  }

  measure: percent_converted {
    type: number
    hidden: yes
    value_format: "0.0%"
    sql: ${users.total_converted}/${waitlist_count.total_waitlist_users} ;;
  }

  measure: running_percent_converted {
    description: "The number of people converted divided by the number of waitlist users in that row."
    type: number
    value_format: "0.0%"
    sql: ${waitlist_new.total_converted}/${waitlist_new.count} ;;
  }

  measure: percent_converted_monthly {
    type: number
    hidden: yes
    value_format: "0.0%"
    sql: ${users.count}/${waitlist_count_monthly.total_waitlist_users_monthly} ;;
  }

  measure: share_percent_of_leads_weekly {
    description: "The number of waitlist users created that week for the medium category divided by the total number of waitlist users created that week."
    type: number
    value_format: "0.0%"
    sql: ${waitlist_new.count}/${waitlist_count_weekly.total_waitlist_users} ;;
  }

  measure: count_of_buy_now_from_waitlist {
    description: "Number of users who had an organic medium on their waitlist UTM code and has a purchase conversion"
    type: count_distinct
    filters: [waitlist_new.buy_now_from_waitlist_yesno: "True"]
    sql: ${user_id} ;;
  }

  measure: total_converted {
    description: "The number of users converted from the waitlist, excluding any users that were organic Buy Now purchases."
    type: number
    sql: ${users.count} - ${waitlist_new.count_of_buy_now_from_waitlist} ;;
  }
}

- view: waitlist_new
  derived_table:
    sql: select
  w.id,
  w.email,
  w.zip,
  w.partner_id,
  w.created_at,
  DATE_FORMAT(w.created_at,'%Y-%v') AS created_number_week,
  users1.user_id
from waitlist w
left join ${users.SQL_TABLE_NAME} as users1 on w.email = users1.email_id

UNION ALL

select
  w.id,
  w.email,
  w.zip,
  w.partner_id,
  w.created_at,
  DATE_FORMAT(w.created_at,'%Y-%v') AS created_number_week,
  users1.user_id
from waitlist w
right join ${users.SQL_TABLE_NAME} as users1 on w.email = users1.email_id
where w.email is NULL
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: created_number_week
    sql: ${TABLE}.created_number_week
  - dimension: email
    sql: ${TABLE}.email
  - dimension: partner_id
    type: int
    sql: ${TABLE}.partner_id
  - dimension: zip
    sql: ${TABLE}.zip
  - dimension: paid_lead
    type: yesno
    sql: ${conversions_campaigns_waitlist.utm_medium} in ('cpc', 'ocpm', 'affiliate', 'cpr', 'print', 'display', 'mobile', 'paidsocial', 'mail')
  - dimension: paid_purhcase
    type: yesno
    sql: ${conversions_campaigns.utm_medium} in ('cpc', 'ocpm', 'affiliate', 'cpr', 'print', 'display', 'mobile', 'paidsocial', 'mail')
  - dimension: <% parameter promo_trial %>_purchase
    type: yesno
    sql: ${promotions_table.category} = '<% parameter promo_trial %>'
  - dimension: month_converted
    type: int
    sql: TIMESTAMPDIFF(MONTH, ${TABLE}.created_at, ${users.created_date})
  - dimension: days_until_conversion
    type: int
    sql: TIMESTAMPDIFF(DAY, ${TABLE}.created_at, ${users.created_date})
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - measure: average_days_until_conversion
    type: avg
    value_format: 0.0
    sql: ${days_until_conversion}
  - measure: count
    type: count
    drill_fields:
    - id
  - measure: percent_converted
    type: number
    value_format: '0.0%'
    sql: ${users.total_converted}/${waitlist_count.total_waitlist_users}
  - measure: running_percent_converted
    type: number
    description: The number of people converted divided by the number of waitlist users in that row.
    value_format: '0.0%'
    sql: ${waitlist_new.total_converted}/${waitlist_new.count}
  - measure: percent_converted_monthly
    type: number
    value_format: '0.0%'
    sql: ${users.count}/${waitlist_count_monthly.total_waitlist_users_monthly}
  - measure: share_percent_of_leads_weekly
    type: number
    description: The number of waitlist users created that week for the medium category divided by the total number of waitlist users created that week.
    value_format: '0.0%'
    sql: ${waitlist_new.count}/${waitlist_count_weekly.total_waitlist_users}
  - measure: count_of_buy_now_from_waitlist
    type: count_distinct
    description: Number of users who had an organic medium on their waitlist UTM code and has a purchase conversion
    filters:
    - waitlist_new.buy_now_from_waitlist_yesno: True
    sql: ${user_id}
  - measure: total_converted
    type: number
    description: The number of users converted from the waitlist, excluding any users that were organic Buy Now purchases.
    sql: ${users.count} - ${waitlist_new.count_of_buy_now_from_waitlist}