Member Acquisition Source

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

select member_id, acquisition_source_type, acquisition_source
from
(select member_id, acquisition_source_type, acquisition_source, row_number() over (partition by member_id order by acq_seq asc) as rn
  from
  (
    SELECT mm.id member_id, 'Organizations' acquisition_source_type, mc.name acquisition_source, 1 acq_seq
    FROM members_genericmember mm
    inner join members_company_members mcm on mcm.member_id = mm.id
    inner join members_company mc on mc.id = mcm.company_id
    UNION
    SELECT mm.id member_id, 'Organizations' acquisition_source_type, mp.name acquisition_source, 2 acq_seq
    FROM members_genericmember mm
    inner join members_medicalpractice_members mmpm on mmpm.member_id = mm.id
    inner join members_medicalpractice mp on mp.id = mmpm.medicalpractice_id
    UNION
    SELECT mm.id member_id, 'Media Source' acquisition_source_type, aae.media_source acquisition_source, 3 acq_seq
    FROM members_genericmember mm
    inner join marketing_event_data aae on aae.member_id = mm.id and aae.event_name = 'registration'
    UNION
    SELECT mm.id member_id, 'Campaigns' acquisition_source_type, cca.name acquisition_source, 4 acq_seq
    FROM members_genericmember mm
    inner join coupons_membercoupon cm on cm.member_id = mm.id
    inner join coupons_coupon cc on cc.id = cm.coupon_id
    inner join coupons_campaign cca on cca.id = cc.campaign_id
    UNION
    SELECT mm.id member_id, 'Promotion' acquisition_source_type, p.name acquisition_source, 5 acq_seq
    FROM members_genericmember mm
    inner join users_user_data du on du.id = mm.user_id
    join calls_promotion p
      on p.start_datetime < du.date_joined AND
          (p.end_datetime > du.date_joined or p.end_datetime is NULL)
    UNION
    /*SELECT mm.id member_id, 'TV Spots' acquisition_source_type, tte.title acquisition_source, 6 acq_seq
    FROM members_genericmember mm
    inner join tveyes_tveyes_event_signups_driven ttesd on ttesd.member_id = mm.id
    inner join tveyes_tveyes_event tte on tte.id = ttesd.tveyes_event_id
    UNION */
    /* SELECT mm.id member_id, 'Organic' acquisition_source_type, ads.code acquisition_source, 7 acq_seq
    FROM members_genericmember mm
    left join contact_info_useraddress ua on ua.object_id = mm.id
    left join address_address aa on aa.id = ua.address_id
    left join address_locality al on al.id = aa.locality_id
    left join address_state ads on ads.id = al.state_id
    where ltrim(rtrim(ads.code)) <> '' and ads.code is not null
    UNION */
    SELECT mm.id member_id, 'Organic' acquisition_source_type, 'Organic' acquisition_source, 8 acq_seq
    FROM members_genericmember mm
    /* left join contact_info_useraddress ua on ua.object_id = mm.id
    left join address_address aa on aa.id = ua.address_id
    left join address_locality al on al.id = aa.locality_id
    left join address_state ads on ads.id = al.state_id
    where ltrim(rtrim(ads.code)) = '' or ads.code is null*/
  ) x
) y
where y.rn = 1

# models/schema.yml
version: 2

models:
  - name: member_acquisition_source
    columns:
      - name: member_id
        tests:
          - unique
          - not_null
      - name: acquisition_source_type
      - name: acquisition_source
      - name: acquisition_source_and_type

Assumptions

• 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 member_id, acquisition_source_type, acquisition_source
from
(select member_id, acquisition_source_type, acquisition_source, row_number() over (partition by member_id order by acq_seq asc) as rn
  from
  (
    SELECT mm.id member_id, 'Organizations' acquisition_source_type, mc.name acquisition_source, 1 acq_seq
    FROM members_genericmember mm
    inner join members_company_members mcm on mcm.member_id = mm.id
    inner join members_company mc on mc.id = mcm.company_id
    UNION
    SELECT mm.id member_id, 'Organizations' acquisition_source_type, mp.name acquisition_source, 2 acq_seq
    FROM members_genericmember mm
    inner join members_medicalpractice_members mmpm on mmpm.member_id = mm.id
    inner join members_medicalpractice mp on mp.id = mmpm.medicalpractice_id
    UNION
    SELECT mm.id member_id, 'Media Source' acquisition_source_type, aae.media_source acquisition_source, 3 acq_seq
    FROM members_genericmember mm
    inner join marketing_event_data aae on aae.member_id = mm.id and aae.event_name = 'registration'
    UNION
    SELECT mm.id member_id, 'Campaigns' acquisition_source_type, cca.name acquisition_source, 4 acq_seq
    FROM members_genericmember mm
    inner join coupons_membercoupon cm on cm.member_id = mm.id
    inner join coupons_coupon cc on cc.id = cm.coupon_id
    inner join coupons_campaign cca on cca.id = cc.campaign_id
    UNION
    SELECT mm.id member_id, 'Promotion' acquisition_source_type, p.name acquisition_source, 5 acq_seq
    FROM members_genericmember mm
    inner join users_user_data du on du.id = mm.user_id
    join calls_promotion p
      on p.start_datetime < du.date_joined AND
          (p.end_datetime > du.date_joined or p.end_datetime is NULL)
    UNION
    /*SELECT mm.id member_id, 'TV Spots' acquisition_source_type, tte.title acquisition_source, 6 acq_seq
    FROM members_genericmember mm
    inner join tveyes_tveyes_event_signups_driven ttesd on ttesd.member_id = mm.id
    inner join tveyes_tveyes_event tte on tte.id = ttesd.tveyes_event_id
    UNION */
    /* SELECT mm.id member_id, 'Organic' acquisition_source_type, ads.code acquisition_source, 7 acq_seq
    FROM members_genericmember mm
    left join contact_info_useraddress ua on ua.object_id = mm.id
    left join address_address aa on aa.id = ua.address_id
    left join address_locality al on al.id = aa.locality_id
    left join address_state ads on ads.id = al.state_id
    where ltrim(rtrim(ads.code)) <> '' and ads.code is not null
    UNION */
    SELECT mm.id member_id, 'Organic' acquisition_source_type, 'Organic' acquisition_source, 8 acq_seq
    FROM members_genericmember mm
    /* left join contact_info_useraddress ua on ua.object_id = mm.id
    left join address_address aa on aa.id = ua.address_id
    left join address_locality al on al.id = aa.locality_id
    left join address_state ads on ads.id = al.state_id
    where ltrim(rtrim(ads.code)) = '' or ads.code is null*/
  ) x
) y
where y.rn = 1

view: member_acquisition_source {
  derived_table: {
    sql: select member_id, acquisition_source_type, acquisition_source
from
(select member_id, acquisition_source_type, acquisition_source, row_number() over (partition by member_id order by acq_seq asc) as rn
  from
  (
    SELECT mm.id member_id, 'Organizations' acquisition_source_type, mc.name acquisition_source, 1 acq_seq
    FROM members_genericmember mm
    inner join members_company_members mcm on mcm.member_id = mm.id
    inner join members_company mc on mc.id = mcm.company_id
    UNION
    SELECT mm.id member_id, 'Organizations' acquisition_source_type, mp.name acquisition_source, 2 acq_seq
    FROM members_genericmember mm
    inner join members_medicalpractice_members mmpm on mmpm.member_id = mm.id
    inner join members_medicalpractice mp on mp.id = mmpm.medicalpractice_id
    UNION
    SELECT mm.id member_id, 'Media Source' acquisition_source_type, aae.media_source acquisition_source, 3 acq_seq
    FROM members_genericmember mm
    inner join marketing_event_data aae on aae.member_id = mm.id and aae.event_name = 'registration'
    UNION
    SELECT mm.id member_id, 'Campaigns' acquisition_source_type, cca.name acquisition_source, 4 acq_seq
    FROM members_genericmember mm
    inner join coupons_membercoupon cm on cm.member_id = mm.id
    inner join coupons_coupon cc on cc.id = cm.coupon_id
    inner join coupons_campaign cca on cca.id = cc.campaign_id
    UNION
    SELECT mm.id member_id, 'Promotion' acquisition_source_type, p.name acquisition_source, 5 acq_seq
    FROM members_genericmember mm
    inner join users_user_data du on du.id = mm.user_id
    join calls_promotion p
      on p.start_datetime < du.date_joined AND
          (p.end_datetime > du.date_joined or p.end_datetime is NULL)
    UNION
    /*SELECT mm.id member_id, 'TV Spots' acquisition_source_type, tte.title acquisition_source, 6 acq_seq
    FROM members_genericmember mm
    inner join tveyes_tveyes_event_signups_driven ttesd on ttesd.member_id = mm.id
    inner join tveyes_tveyes_event tte on tte.id = ttesd.tveyes_event_id
    UNION */
    /* SELECT mm.id member_id, 'Organic' acquisition_source_type, ads.code acquisition_source, 7 acq_seq
    FROM members_genericmember mm
    left join contact_info_useraddress ua on ua.object_id = mm.id
    left join address_address aa on aa.id = ua.address_id
    left join address_locality al on al.id = aa.locality_id
    left join address_state ads on ads.id = al.state_id
    where ltrim(rtrim(ads.code)) <> '' and ads.code is not null
    UNION */
    SELECT mm.id member_id, 'Organic' acquisition_source_type, 'Organic' acquisition_source, 8 acq_seq
    FROM members_genericmember mm
    /* left join contact_info_useraddress ua on ua.object_id = mm.id
    left join address_address aa on aa.id = ua.address_id
    left join address_locality al on al.id = aa.locality_id
    left join address_state ads on ads.id = al.state_id
    where ltrim(rtrim(ads.code)) = '' or ads.code is null*/
  ) x
) y
where y.rn = 1 ;;
  }

  dimension: member_id {
    primary_key: yes
    sql: ${TABLE}.member_id ;;
  }

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

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

  dimension: acquisition_source_and_type {
    sql: ${acquisition_source_type} || ' - ' || COALESCE(${acquisition_source}, '') ;;
  }

  measure: acquisition_source_count {
    type: count_distinct
    drill_fields: [acquisition_source_set*]
    sql: ${acquisition_source} ;;
  }
}

- view: member_acquisition_source
  derived_table:
    sql: select member_id, acquisition_source_type, acquisition_source
from
(select member_id, acquisition_source_type, acquisition_source, row_number() over (partition by member_id order by acq_seq asc) as rn
  from
  (
    SELECT mm.id member_id, 'Organizations' acquisition_source_type, mc.name acquisition_source, 1 acq_seq
    FROM members_genericmember mm
    inner join members_company_members mcm on mcm.member_id = mm.id
    inner join members_company mc on mc.id = mcm.company_id
    UNION
    SELECT mm.id member_id, 'Organizations' acquisition_source_type, mp.name acquisition_source, 2 acq_seq
    FROM members_genericmember mm
    inner join members_medicalpractice_members mmpm on mmpm.member_id = mm.id
    inner join members_medicalpractice mp on mp.id = mmpm.medicalpractice_id
    UNION
    SELECT mm.id member_id, 'Media Source' acquisition_source_type, aae.media_source acquisition_source, 3 acq_seq
    FROM members_genericmember mm
    inner join marketing_event_data aae on aae.member_id = mm.id and aae.event_name = 'registration'
    UNION
    SELECT mm.id member_id, 'Campaigns' acquisition_source_type, cca.name acquisition_source, 4 acq_seq
    FROM members_genericmember mm
    inner join coupons_membercoupon cm on cm.member_id = mm.id
    inner join coupons_coupon cc on cc.id = cm.coupon_id
    inner join coupons_campaign cca on cca.id = cc.campaign_id
    UNION
    SELECT mm.id member_id, 'Promotion' acquisition_source_type, p.name acquisition_source, 5 acq_seq
    FROM members_genericmember mm
    inner join users_user_data du on du.id = mm.user_id
    join calls_promotion p
      on p.start_datetime < du.date_joined AND
          (p.end_datetime > du.date_joined or p.end_datetime is NULL)
    UNION
    /*SELECT mm.id member_id, 'TV Spots' acquisition_source_type, tte.title acquisition_source, 6 acq_seq
    FROM members_genericmember mm
    inner join tveyes_tveyes_event_signups_driven ttesd on ttesd.member_id = mm.id
    inner join tveyes_tveyes_event tte on tte.id = ttesd.tveyes_event_id
    UNION */
    /* SELECT mm.id member_id, 'Organic' acquisition_source_type, ads.code acquisition_source, 7 acq_seq
    FROM members_genericmember mm
    left join contact_info_useraddress ua on ua.object_id = mm.id
    left join address_address aa on aa.id = ua.address_id
    left join address_locality al on al.id = aa.locality_id
    left join address_state ads on ads.id = al.state_id
    where ltrim(rtrim(ads.code)) <> '' and ads.code is not null
    UNION */
    SELECT mm.id member_id, 'Organic' acquisition_source_type, 'Organic' acquisition_source, 8 acq_seq
    FROM members_genericmember mm
    /* left join contact_info_useraddress ua on ua.object_id = mm.id
    left join address_address aa on aa.id = ua.address_id
    left join address_locality al on al.id = aa.locality_id
    left join address_state ads on ads.id = al.state_id
    where ltrim(rtrim(ads.code)) = '' or ads.code is null*/
  ) x
) y
where y.rn = 1
  fields:
  - dimension: member_id
    primary_key: true
    sql: ${TABLE}.member_id
  - dimension: acquisition_source_type
    sql: ${TABLE}.acquisition_source_type
  - dimension: acquisition_source
    sql: ${TABLE}.acquisition_source
  - dimension: acquisition_source_and_type
    sql: ${acquisition_source_type} || ' - ' || COALESCE(${acquisition_source}, '')
  - measure: acquisition_source_count
    type: count_distinct
    drill_fields:
    - acquisition_source_set*
    sql: ${acquisition_source}