Dt User Campaigns Facts

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

select
  a.id as user_id,
  case when b.name = 'PlatformApp iPhone' then 'iOS'
       when a.api_user_id = '1' then 'PlatformDomain.com'
       else 'API' end as user_type,
  a.is_partial,
  a.registrationdate,
  c.creationdate as campaign_ts,
  c.id as campaign_id,
  c.status,
  case when c.creationdate is not null then rank() over(partition by a.id order by c.creationdate) end as rank
from users a
join api_users b
on a.api_user_id = b.id
left outer join campaigns c
on a.id = c.user_id and a.registrationdate < c.creationdate

# models/schema.yml
version: 2

models:
  - name: dt_user_campaigns_facts
    columns:
      - name: user_id
      - name: is_partial
      - name: user_type
      - name: rank
      - name: campaign_id
      - name: status
      - name: days_since_first_creation

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
  a.id as user_id,
  case when b.name = 'PlatformApp iPhone' then 'iOS'
       when a.api_user_id = '1' then 'PlatformDomain.com'
       else 'API' end as user_type,
  a.is_partial,
  a.registrationdate,
  c.creationdate as campaign_ts,
  c.id as campaign_id,
  c.status,
  case when c.creationdate is not null then rank() over(partition by a.id order by c.creationdate) end as rank
from users a
join api_users b
on a.api_user_id = b.id
left outer join campaigns c
on a.id = c.user_id and a.registrationdate < c.creationdate

view: dt_user_campaigns_facts {
  derived_table: {
    sql: select
  a.id as user_id,
  case when b.name = 'PlatformApp iPhone' then 'iOS'
       when a.api_user_id = '1' then 'PlatformDomain.com'
       else 'API' end as user_type,
  a.is_partial,
  a.registrationdate,
  c.creationdate as campaign_ts,
  c.id as campaign_id,
  c.status,
  case when c.creationdate is not null then rank() over(partition by a.id order by c.creationdate) end as rank
from users a
join api_users b
on a.api_user_id = b.id
left outer join campaigns c
on a.id = c.user_id and a.registrationdate < c.creationdate ;;
  }

  dimension: user_id {
  }

  dimension: is_partial {
  }

  dimension: user_type {
  }

  dimension: rank {
  }

  dimension: campaign_id {
  }

  dimension: status {
  }

  dimension: days_since_first_creation {
    type: int
    sql: coalesce(campaign_ts::date - registrationdate::date, -1) ;;
  }

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

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

  measure: count_users {
    type: count
    sql: ${TABLE}.user_id ;;
  }

  measure: first_time_admins {
    type: count_distinct
    filters: [rank: "1"]
    sql: ${TABLE}.user_id ;;
  }

  measure: percent_creators {
    type: number
    sql: 100.0 * ${first_time_admins}/${count_users} ;;
  }

  measure: percent_creators_with_contribution {
    type: number
    sql: 100.0 * ${dt_valid_contributions.count_admins}/${count_users} ;;
  }
}

- view: dt_user_campaigns_facts
  derived_table:
    sql: select
  a.id as user_id,
  case when b.name = 'PlatformApp iPhone' then 'iOS'
       when a.api_user_id = '1' then 'PlatformDomain.com'
       else 'API' end as user_type,
  a.is_partial,
  a.registrationdate,
  c.creationdate as campaign_ts,
  c.id as campaign_id,
  c.status,
  case when c.creationdate is not null then rank() over(partition by a.id order by c.creationdate) end as rank
from users a
join api_users b
on a.api_user_id = b.id
left outer join campaigns c
on a.id = c.user_id and a.registrationdate < c.creationdate
  fields:
  - dimension: user_id
  - dimension: is_partial
  - dimension: user_type
  - dimension: rank
  - dimension: campaign_id
  - dimension: status
  - dimension: days_since_first_creation
    type: int
    sql: coalesce(campaign_ts::date - registrationdate::date, -1)
  - dimension_group: registration
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.registrationdate
  - dimension_group: campaign_creation
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.campaign_ts
  - measure: count_users
    type: count
    sql: ${TABLE}.user_id
  - measure: first_time_admins
    type: count_distinct
    filters:
    - rank: 1
    sql: ${TABLE}.user_id
  - measure: percent_creators
    type: number
    sql: 100.0 * ${first_time_admins}/${count_users}
  - measure: percent_creators_with_contribution
    type: number
    sql: 100.0 * ${dt_valid_contributions.count_admins}/${count_users}