Conversion Data

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

(
select account_id, 'reg' as conversion_type, registration_date as conversion_date
from central_data.account_funnel_status
)
UNION
(
select account_id, 'cc' as conversion_type, cc_date_added as conversion_date
from central_data.account_funnel_status
where cc_date_added is not null
)
UNION
(
select account_id, 'mql' as conversion_type, adj_mql_date as conversion_date
from central_data.account_funnel_status
where adj_mql_date is not null
)
UNION
(
select account_id, 'churn' as conversion_type, churn_date as conversion_date
from central_data.account_funnel_status
where churn_date is not null
)
UNION
(
SELECT account_id, acset.value as conversion_type, min(acse.date_added) as conversion_date
FROM
  central_data.customer_service_event acse,
  central_data.customer_service_event_type acset
WHERE
  acse.active = 1
  and acset.id = acse.type
GROUP BY account_id, acset.value
)
UNION
(
SELECT account_id, 'core' as conversion_type, min(acse.date_added) as conversion_date
FROM
  central_data.customer_service_event acse,
  central_data.customer_service_event_type acset
WHERE
  acse.active = 1
  and acset.id = acse.type
GROUP BY account_id, acset.value
)

# models/schema.yml
version: 2

models:
  - name: conversion_data
    columns:
      - name: account_id
      - name: conversion_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 account_id, 'reg' as conversion_type, registration_date as conversion_date
from central_data.account_funnel_status
)
UNION
(
select account_id, 'cc' as conversion_type, cc_date_added as conversion_date
from central_data.account_funnel_status
where cc_date_added is not null
)
UNION
(
select account_id, 'mql' as conversion_type, adj_mql_date as conversion_date
from central_data.account_funnel_status
where adj_mql_date is not null
)
UNION
(
select account_id, 'churn' as conversion_type, churn_date as conversion_date
from central_data.account_funnel_status
where churn_date is not null
)
UNION
(
SELECT account_id, acset.value as conversion_type, min(acse.date_added) as conversion_date
FROM
  central_data.customer_service_event acse,
  central_data.customer_service_event_type acset
WHERE
  acse.active = 1
  and acset.id = acse.type
GROUP BY account_id, acset.value
)
UNION
(
SELECT account_id, 'core' as conversion_type, min(acse.date_added) as conversion_date
FROM
  central_data.customer_service_event acse,
  central_data.customer_service_event_type acset
WHERE
  acse.active = 1
  and acset.id = acse.type
GROUP BY account_id, acset.value
)

view: conversion_data {
  derived_table: {
    sql: (
select account_id, 'reg' as conversion_type, registration_date as conversion_date
from central_data.account_funnel_status
)
UNION
(
select account_id, 'cc' as conversion_type, cc_date_added as conversion_date
from central_data.account_funnel_status
where cc_date_added is not null
)
UNION
(
select account_id, 'mql' as conversion_type, adj_mql_date as conversion_date
from central_data.account_funnel_status
where adj_mql_date is not null
)
UNION
(
select account_id, 'churn' as conversion_type, churn_date as conversion_date
from central_data.account_funnel_status
where churn_date is not null
)
UNION
(
SELECT account_id, acset.value as conversion_type, min(acse.date_added) as conversion_date
FROM
  central_data.customer_service_event acse,
  central_data.customer_service_event_type acset
WHERE
  acse.active = 1
  and acset.id = acse.type
GROUP BY account_id, acset.value
)
UNION
(
SELECT account_id, 'core' as conversion_type, min(acse.date_added) as conversion_date
FROM
  central_data.customer_service_event acse,
  central_data.customer_service_event_type acset
WHERE
  acse.active = 1
  and acset.id = acse.type
GROUP BY account_id, acset.value
) ;;
  }

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

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

  dimension_group: conversion_date {
    type: time
    timeframes: [time, date, week, month, day_of_month, month_num, day_of_week_index, year, quarter, quarter_of_year]
    sql: ${TABLE}.conversion_date ;;
  }

  measure: count_conversion {
    type: count_distinct
    sql: ${account_id} ;;
  }
}

- view: conversion_data
  derived_table:
    sql: (
select account_id, 'reg' as conversion_type, registration_date as conversion_date
from central_data.account_funnel_status
)
UNION
(
select account_id, 'cc' as conversion_type, cc_date_added as conversion_date
from central_data.account_funnel_status
where cc_date_added is not null
)
UNION
(
select account_id, 'mql' as conversion_type, adj_mql_date as conversion_date
from central_data.account_funnel_status
where adj_mql_date is not null
)
UNION
(
select account_id, 'churn' as conversion_type, churn_date as conversion_date
from central_data.account_funnel_status
where churn_date is not null
)
UNION
(
SELECT account_id, acset.value as conversion_type, min(acse.date_added) as conversion_date
FROM
  central_data.customer_service_event acse,
  central_data.customer_service_event_type acset
WHERE
  acse.active = 1
  and acset.id = acse.type
GROUP BY account_id, acset.value
)
UNION
(
SELECT account_id, 'core' as conversion_type, min(acse.date_added) as conversion_date
FROM
  central_data.customer_service_event acse,
  central_data.customer_service_event_type acset
WHERE
  acse.active = 1
  and acset.id = acse.type
GROUP BY account_id, acset.value
)
  fields:
  - dimension: account_id
    type: int
    sql: ${TABLE}.account_id
  - dimension: conversion_type
    sql: ${TABLE}.conversion_type
  - dimension_group: conversion_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - day_of_month
    - month_num
    - day_of_week_index
    - year
    - quarter
    - quarter_of_year
    sql: ${TABLE}.conversion_date
  - measure: count_conversion
    type: count_distinct
    sql: ${account_id}