Conversion Funnel

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

WITH dates AS (
  SELECT id FROM date_dim
),
entrances AS (
  SELECT sum(entrances), date_id
  FROM entrance_fact
  GROUP BY date_id
),
actions AS (
  SELECT count(DISTINCT user_id), date_id
  FROM system_action_fact
  JOIN system_action_dim ON system_action_fact.system_action_id = system_action_dim.id
  JOIN user_dim ON system_action_fact.user_id = user_dim.id
  WHERE system_action = 'user_account_created'
    AND user_dim.account_type = 'end_user'
  GROUP BY date_id
),
logins AS (
  WITH new_accounts AS (
    SELECT user_id, date_id
    FROM system_action_fact
    JOIN system_action_dim ON system_action_fact.system_action_id = system_action_dim.id
    WHERE system_action = 'user_account_created'
  )
  SELECT COUNT(DISTINCT session_duration_new.user_id), new_accounts.date_id
  FROM session_duration_scratch_table AS session_duration_new
  JOIN user_dim ON session_duration_new.user_id = user_dim.id
  JOIN new_accounts ON session_duration_new.user_id = new_accounts.user_id
  WHERE user_dim.account_type = 'end_user' AND session_duration_new.sequence_number = 1
  GROUP BY new_accounts.date_id
),
content_sessions AS (
  SELECT COUNT(DISTINCT content_session_dim_new.username), created_date_id date_id
  FROM content_session_scratch_table AS content_session_dim_new
  JOIN user_dim ON content_session_dim_new.username = user_dim.username
  WHERE user_dim.account_type = 'end_user' AND content_session_dim_new.sequence_number = 1
  GROUP BY created_date_id
)
SELECT dates.id, entrances.sum page_views, actions.count accounts_created, logins.count sessions_created, content_sessions.count content_sessions_created FROM dates
LEFT OUTER JOIN entrances ON entrances.date_id = dates.id
LEFT OUTER JOIN actions ON actions.date_id = dates.id
LEFT OUTER JOIN logins ON logins.date_id = dates.id
LEFT OUTER JOIN content_sessions ON content_sessions.date_id = dates.id
ORDER BY dates.id ASC

# models/schema.yml
version: 2

models:
  - name: conversion_funnel
    columns:
      - name: id
      - name: page_views
      - name: accounts_created
      - name: sessions_created
      - name: content_sessions_created

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.

WITH dates AS (
  SELECT id FROM date_dim
),
entrances AS (
  SELECT sum(entrances), date_id
  FROM entrance_fact
  GROUP BY date_id
),
actions AS (
  SELECT count(DISTINCT user_id), date_id
  FROM system_action_fact
  JOIN system_action_dim ON system_action_fact.system_action_id = system_action_dim.id
  JOIN user_dim ON system_action_fact.user_id = user_dim.id
  WHERE system_action = 'user_account_created'
    AND user_dim.account_type = 'end_user'
  GROUP BY date_id
),
logins AS (
  WITH new_accounts AS (
    SELECT user_id, date_id
    FROM system_action_fact
    JOIN system_action_dim ON system_action_fact.system_action_id = system_action_dim.id
    WHERE system_action = 'user_account_created'
  )
  SELECT COUNT(DISTINCT session_duration_new.user_id), new_accounts.date_id
  FROM session_duration_scratch_table AS session_duration_new
  JOIN user_dim ON session_duration_new.user_id = user_dim.id
  JOIN new_accounts ON session_duration_new.user_id = new_accounts.user_id
  WHERE user_dim.account_type = 'end_user' AND session_duration_new.sequence_number = 1
  GROUP BY new_accounts.date_id
),
content_sessions AS (
  SELECT COUNT(DISTINCT content_session_dim_new.username), created_date_id date_id
  FROM content_session_scratch_table AS content_session_dim_new
  JOIN user_dim ON content_session_dim_new.username = user_dim.username
  WHERE user_dim.account_type = 'end_user' AND content_session_dim_new.sequence_number = 1
  GROUP BY created_date_id
)
SELECT dates.id, entrances.sum page_views, actions.count accounts_created, logins.count sessions_created, content_sessions.count content_sessions_created FROM dates
LEFT OUTER JOIN entrances ON entrances.date_id = dates.id
LEFT OUTER JOIN actions ON actions.date_id = dates.id
LEFT OUTER JOIN logins ON logins.date_id = dates.id
LEFT OUTER JOIN content_sessions ON content_sessions.date_id = dates.id
ORDER BY dates.id ASC

view: conversion_funnel {
  derived_table: {
    sql: WITH dates AS (
  SELECT id FROM date_dim
),
entrances AS (
  SELECT sum(entrances), date_id
  FROM entrance_fact
  GROUP BY date_id
),
actions AS (
  SELECT count(DISTINCT user_id), date_id
  FROM system_action_fact
  JOIN system_action_dim ON system_action_fact.system_action_id = system_action_dim.id
  JOIN user_dim ON system_action_fact.user_id = user_dim.id
  WHERE system_action = 'user_account_created'
    AND user_dim.account_type = 'end_user'
  GROUP BY date_id
),
logins AS (
  WITH new_accounts AS (
    SELECT user_id, date_id
    FROM system_action_fact
    JOIN system_action_dim ON system_action_fact.system_action_id = system_action_dim.id
    WHERE system_action = 'user_account_created'
  )
  SELECT COUNT(DISTINCT session_duration_new.user_id), new_accounts.date_id
  FROM session_duration_scratch_table AS session_duration_new
  JOIN user_dim ON session_duration_new.user_id = user_dim.id
  JOIN new_accounts ON session_duration_new.user_id = new_accounts.user_id
  WHERE user_dim.account_type = 'end_user' AND session_duration_new.sequence_number = 1
  GROUP BY new_accounts.date_id
),
content_sessions AS (
  SELECT COUNT(DISTINCT content_session_dim_new.username), created_date_id date_id
  FROM content_session_scratch_table AS content_session_dim_new
  JOIN user_dim ON content_session_dim_new.username = user_dim.username
  WHERE user_dim.account_type = 'end_user' AND content_session_dim_new.sequence_number = 1
  GROUP BY created_date_id
)
SELECT dates.id, entrances.sum page_views, actions.count accounts_created, logins.count sessions_created, content_sessions.count content_sessions_created FROM dates
LEFT OUTER JOIN entrances ON entrances.date_id = dates.id
LEFT OUTER JOIN actions ON actions.date_id = dates.id
LEFT OUTER JOIN logins ON logins.date_id = dates.id
LEFT OUTER JOIN content_sessions ON content_sessions.date_id = dates.id
ORDER BY dates.id ASC ;;
  }

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

  dimension: page_views {
    type: int
    hidden: yes
    sql: ${TABLE}.page_views ;;
  }

  dimension: accounts_created {
    type: int
    hidden: yes
    sql: ${TABLE}.accounts_created ;;
  }

  dimension: sessions_created {
    type: int
    hidden: yes
    sql: ${TABLE}.sessions_created ;;
  }

  dimension: content_sessions_created {
    type: int
    hidden: yes
    sql: ${TABLE}.content_sessions_created ;;
  }

  dimension_group: funnel {
    type: time
    timeframes: [date, week, month, month_num, day_of_week, day_of_week_index, week_of_year, day_of_month]
    sql: (SUBSTRING(${id}, 1, 4) || '-' || SUBSTRING(${id}, 5, 2) || '-' || SUBSTRING(${id}, 7, 2)|| ' ' || '00:00')::timestamp ;;
  }

  measure: page_view_sum {
    type: sum
    sql: ${page_views} ;;
  }

  measure: accounts_created_sum {
    type: sum
    sql: ${accounts_created} ;;
  }

  measure: sessions_created_sum {
    type: sum
    sql: ${sessions_created} ;;
  }

  measure: content_sessions_created_sum {
    type: sum
    sql: ${content_sessions_created} ;;
  }
}

- view: conversion_funnel
  derived_table:
    sql: WITH dates AS (
  SELECT id FROM date_dim
),
entrances AS (
  SELECT sum(entrances), date_id
  FROM entrance_fact
  GROUP BY date_id
),
actions AS (
  SELECT count(DISTINCT user_id), date_id
  FROM system_action_fact
  JOIN system_action_dim ON system_action_fact.system_action_id = system_action_dim.id
  JOIN user_dim ON system_action_fact.user_id = user_dim.id
  WHERE system_action = 'user_account_created'
    AND user_dim.account_type = 'end_user'
  GROUP BY date_id
),
logins AS (
  WITH new_accounts AS (
    SELECT user_id, date_id
    FROM system_action_fact
    JOIN system_action_dim ON system_action_fact.system_action_id = system_action_dim.id
    WHERE system_action = 'user_account_created'
  )
  SELECT COUNT(DISTINCT session_duration_new.user_id), new_accounts.date_id
  FROM session_duration_scratch_table AS session_duration_new
  JOIN user_dim ON session_duration_new.user_id = user_dim.id
  JOIN new_accounts ON session_duration_new.user_id = new_accounts.user_id
  WHERE user_dim.account_type = 'end_user' AND session_duration_new.sequence_number = 1
  GROUP BY new_accounts.date_id
),
content_sessions AS (
  SELECT COUNT(DISTINCT content_session_dim_new.username), created_date_id date_id
  FROM content_session_scratch_table AS content_session_dim_new
  JOIN user_dim ON content_session_dim_new.username = user_dim.username
  WHERE user_dim.account_type = 'end_user' AND content_session_dim_new.sequence_number = 1
  GROUP BY created_date_id
)
SELECT dates.id, entrances.sum page_views, actions.count accounts_created, logins.count sessions_created, content_sessions.count content_sessions_created FROM dates
LEFT OUTER JOIN entrances ON entrances.date_id = dates.id
LEFT OUTER JOIN actions ON actions.date_id = dates.id
LEFT OUTER JOIN logins ON logins.date_id = dates.id
LEFT OUTER JOIN content_sessions ON content_sessions.date_id = dates.id
ORDER BY dates.id ASC
  fields:
  - dimension: id
    type: int
    hidden: true
    sql: ${TABLE}.id
  - dimension: page_views
    type: int
    hidden: true
    sql: ${TABLE}.page_views
  - dimension: accounts_created
    type: int
    hidden: true
    sql: ${TABLE}.accounts_created
  - dimension: sessions_created
    type: int
    hidden: true
    sql: ${TABLE}.sessions_created
  - dimension: content_sessions_created
    type: int
    hidden: true
    sql: ${TABLE}.content_sessions_created
  - dimension_group: funnel
    type: time
    timeframes:
    - date
    - week
    - month
    - month_num
    - day_of_week
    - day_of_week_index
    - week_of_year
    - day_of_month
    sql: (SUBSTRING(${id}, 1, 4) || '-' || SUBSTRING(${id}, 5, 2) || '-' || SUBSTRING(${id}, 7, 2)|| ' ' || '00:00')::timestamp
  - measure: page_view_sum
    type: sum
    sql: ${page_views}
  - measure: accounts_created_sum
    type: sum
    sql: ${accounts_created}
  - measure: sessions_created_sum
    type: sum
    sql: ${sessions_created}
  - measure: content_sessions_created_sum
    type: sum
    sql: ${content_sessions_created}