dbt Marketing platform

Participations Duration

Participations Duration model for marketing platform.

inferred

Blocks

dbt SQL LKML LookML

participations_duration.sql · 61 lines

inferred

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

select * from (select
partner_actions.expires_at as expires_time,
partner_actions.launched_at as launched_time,
partner_actions.closed_at as closed_time,
partner_actions._type as partner_action_type,
member_filters.number_participants as max_participants,
member_filters.unlimited,
partners.name as partner,
partners.id as partner_id,
participations.partner_action_id ,
packages.name as customer_type,
count(*) over (partition by partner_action_id) as num_participations,
first_value(participations.created_at) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as first_participation_time,
last_value(participations.created_at) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as last_participation_time,
nth_value(participations.created_at, 200) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as twohundredth_participation_time,
row_number() over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as rank

from partner_actions, participations, subscriptions, packages, partners, member_filters

where partner_actions.id = participations.partner_action_id and
subscriptions.package_id = packages.id and
partners.id = subscriptions.embedded_in_id and
partner_actions.partner_id = partners.id and
 partner_actions.id = member_filters.embedded_in_id and
  member_filters.embedded_in_table = 'partner_actions' and
  participations.responded = true and
  participations.stage = 'completed' and
  {% condition closed_time_month %} partner_actions.closed_at {% endcondition %} and
  {% condition closed_time_week %} partner_actions.closed_at {% endcondition %} and
  {% condition closed_time_date %} partner_actions.closed_at {% endcondition %} and
  {% condition launched_time_week %} partner_actions.launched_at {% endcondition %} and
  {% condition launched_time_month %} partner_actions.launched_at {% endcondition %} and
  {% condition launched_time_date %} partner_actions.launched_at {% endcondition %} and
  {% condition expires_time_month %} partner_actions.expires_at {% endcondition %} and
  {% condition expires_time_week %} partner_actions.expires_at {% endcondition %} and
  {% condition expires_time_date %} partner_actions.expires_at {% endcondition %} and
  {% condition customer_type %} (select case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
  when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
  when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
  when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
  when packages.name like '%CustomerType5%' then 'CustomerType5'
  else 'Other' end) {% endcondition %}
  ) as participations_duration
  where ((num_participations >= 200 and rank = 200) or (num_participations < 200 and rank = num_participations))

# models/schema.yml
version: 2

models:
  - name: participations_duration
    columns:
      - name: partner_action_type
      - name: max_participants
      - name: partner
      - name: partner_id
      - name: partner_action_id
      - name: customer_type
      - name: rank

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.

participations_duration.sql · 44 lines

source-preserved validation: not checked

select * from (select
partner_actions.expires_at as expires_time,
partner_actions.launched_at as launched_time,
partner_actions.closed_at as closed_time,
partner_actions._type as partner_action_type,
member_filters.number_participants as max_participants,
member_filters.unlimited,
partners.name as partner,
partners.id as partner_id,
participations.partner_action_id ,
packages.name as customer_type,
count(*) over (partition by partner_action_id) as num_participations,
first_value(participations.created_at) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as first_participation_time,
last_value(participations.created_at) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as last_participation_time,
nth_value(participations.created_at, 200) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as twohundredth_participation_time,
row_number() over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as rank

from partner_actions, participations, subscriptions, packages, partners, member_filters

where partner_actions.id = participations.partner_action_id and
subscriptions.package_id = packages.id and
partners.id = subscriptions.embedded_in_id and
partner_actions.partner_id = partners.id and
 partner_actions.id = member_filters.embedded_in_id and
  member_filters.embedded_in_table = 'partner_actions' and
  participations.responded = true and
  participations.stage = 'completed' and
  {% condition closed_time_month %} partner_actions.closed_at {% endcondition %} and
  {% condition closed_time_week %} partner_actions.closed_at {% endcondition %} and
  {% condition closed_time_date %} partner_actions.closed_at {% endcondition %} and
  {% condition launched_time_week %} partner_actions.launched_at {% endcondition %} and
  {% condition launched_time_month %} partner_actions.launched_at {% endcondition %} and
  {% condition launched_time_date %} partner_actions.launched_at {% endcondition %} and
  {% condition expires_time_month %} partner_actions.expires_at {% endcondition %} and
  {% condition expires_time_week %} partner_actions.expires_at {% endcondition %} and
  {% condition expires_time_date %} partner_actions.expires_at {% endcondition %} and
  {% condition customer_type %} (select case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
  when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
  when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
  when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
  when packages.name like '%CustomerType5%' then 'CustomerType5'
  else 'Other' end) {% endcondition %}
  ) as participations_duration
  where ((num_participations >= 200 and rank = 200) or (num_participations < 200 and rank = num_participations))

participations_duration.view.lkml · 155 lines

inferred

view: participations_duration {
  derived_table: {
    sql: select * from (select
partner_actions.expires_at as expires_time,
partner_actions.launched_at as launched_time,
partner_actions.closed_at as closed_time,
partner_actions._type as partner_action_type,
member_filters.number_participants as max_participants,
member_filters.unlimited,
partners.name as partner,
partners.id as partner_id,
participations.partner_action_id ,
packages.name as customer_type,
count(*) over (partition by partner_action_id) as num_participations,
first_value(participations.created_at) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as first_participation_time,
last_value(participations.created_at) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as last_participation_time,
nth_value(participations.created_at, 200) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as twohundredth_participation_time,
row_number() over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as rank

from partner_actions, participations, subscriptions, packages, partners, member_filters

where partner_actions.id = participations.partner_action_id and
subscriptions.package_id = packages.id and
partners.id = subscriptions.embedded_in_id and
partner_actions.partner_id = partners.id and
 partner_actions.id = member_filters.embedded_in_id and
  member_filters.embedded_in_table = 'partner_actions' and
  participations.responded = true and
  participations.stage = 'completed' and
  {% condition closed_time_month %} partner_actions.closed_at {% endcondition %} and
  {% condition closed_time_week %} partner_actions.closed_at {% endcondition %} and
  {% condition closed_time_date %} partner_actions.closed_at {% endcondition %} and
  {% condition launched_time_week %} partner_actions.launched_at {% endcondition %} and
  {% condition launched_time_month %} partner_actions.launched_at {% endcondition %} and
  {% condition launched_time_date %} partner_actions.launched_at {% endcondition %} and
  {% condition expires_time_month %} partner_actions.expires_at {% endcondition %} and
  {% condition expires_time_week %} partner_actions.expires_at {% endcondition %} and
  {% condition expires_time_date %} partner_actions.expires_at {% endcondition %} and
  {% condition customer_type %} (select case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
  when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
  when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
  when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
  when packages.name like '%CustomerType5%' then 'CustomerType5'
  else 'Other' end) {% endcondition %}
  ) as participations_duration
  where ((num_participations >= 200 and rank = 200) or (num_participations < 200 and rank = num_participations)) ;;
  }

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

  dimension: max_participants {
    type: number
    sql: ${TABLE}.max_participants ;;
  }

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

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

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

  dimension: customer_type {
    sql: case when ${TABLE}.customer_type like '%CustomerType5erType1%' then 'CustomerType5erType1s' when ${TABLE}.customer_type like '%CustomerType5erType2%' then 'CustomerType5erType2' when ${TABLE}.customer_type like '%CustomerType5erType3%' then 'CustomerType5erType3' when ${TABLE}.customer_type like '%CustomerType5erType4%' then 'CustomerType5erType3' when ${TABLE}.customer_type like '%CustomerType5%' then 'CustomerType5' else 'Other' end ;;
  }

  dimension: rank {
    type: number
    hidden: yes
    sql: ${TABLE}.rank ;;
  }

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

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

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

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

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: unlimited {
    type: sum
    sql: ${TABLE}.unlimited::int ;;
  }

  measure: num_participations {
    type: sum
    sql: ${TABLE}.num_participations ;;
  }

  measure: participations_duration_days {
    type: avg
    sql: round( CAST(EXTRACT(epoch from (${TABLE}.last_participation_time - ${TABLE}.first_participation_time))/86400 as numeric),2) ;;
  }

  measure: partner_action_duration_days {
    type: avg
    sql: round( CAST(EXTRACT(epoch from (${TABLE}.closed_time - ${TABLE}.launched_time))/86400 as numeric),2) ;;
  }

  measure: closed_under_one_day {
    type: sum
    sql: ((${TABLE}.closed_time - ${TABLE}.launched_time) < '1 days'::interval)::int ;;
  }

  measure: closed_early {
    type: sum
    sql: (${TABLE}.closed_time < ${TABLE}.expires_time)::int ;;
  }

  measure: twohundred_participations_in_one_day {
    type: sum
    sql: coalesce(((${TABLE}.twohundredth_participation_time - ${TABLE}.launched_time) < '1 days'::interval)::int,0) ;;
  }
}

participations_duration.view.lookml · 137 lines

source-preserved

- view: participations_duration
  derived_table:
    sql: select * from (select
partner_actions.expires_at as expires_time,
partner_actions.launched_at as launched_time,
partner_actions.closed_at as closed_time,
partner_actions._type as partner_action_type,
member_filters.number_participants as max_participants,
member_filters.unlimited,
partners.name as partner,
partners.id as partner_id,
participations.partner_action_id ,
packages.name as customer_type,
count(*) over (partition by partner_action_id) as num_participations,
first_value(participations.created_at) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as first_participation_time,
last_value(participations.created_at) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as last_participation_time,
nth_value(participations.created_at, 200) over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as twohundredth_participation_time,
row_number() over (partition by partner_action_id order by participations.created_at asc rows between unbounded preceding and unbounded following) as rank

from partner_actions, participations, subscriptions, packages, partners, member_filters

where partner_actions.id = participations.partner_action_id and
subscriptions.package_id = packages.id and
partners.id = subscriptions.embedded_in_id and
partner_actions.partner_id = partners.id and
 partner_actions.id = member_filters.embedded_in_id and
  member_filters.embedded_in_table = 'partner_actions' and
  participations.responded = true and
  participations.stage = 'completed' and
  {% condition closed_time_month %} partner_actions.closed_at {% endcondition %} and
  {% condition closed_time_week %} partner_actions.closed_at {% endcondition %} and
  {% condition closed_time_date %} partner_actions.closed_at {% endcondition %} and
  {% condition launched_time_week %} partner_actions.launched_at {% endcondition %} and
  {% condition launched_time_month %} partner_actions.launched_at {% endcondition %} and
  {% condition launched_time_date %} partner_actions.launched_at {% endcondition %} and
  {% condition expires_time_month %} partner_actions.expires_at {% endcondition %} and
  {% condition expires_time_week %} partner_actions.expires_at {% endcondition %} and
  {% condition expires_time_date %} partner_actions.expires_at {% endcondition %} and
  {% condition customer_type %} (select case when packages.name like '%CustomerType5erType1%' then 'CustomerType5erType1s'
  when packages.name like '%CustomerType5erType2%' then 'CustomerType5erType2'
  when packages.name like '%CustomerType5erType3%' then 'CustomerType5erType3'
  when packages.name like '%CustomerType5erType4%' then 'CustomerType5erType3'
  when packages.name like '%CustomerType5%' then 'CustomerType5'
  else 'Other' end) {% endcondition %}
  ) as participations_duration
  where ((num_participations >= 200 and rank = 200) or (num_participations < 200 and rank = num_participations))
  fields:
  - dimension: partner_action_type
    sql: ${TABLE}.partner_action_type
  - dimension: max_participants
    type: number
    sql: ${TABLE}.max_participants
  - dimension: partner
    sql: ${TABLE}.partner
  - dimension: partner_id
    sql: ${TABLE}.partner_id
  - dimension: partner_action_id
    sql: ${TABLE}.partner_action_id
  - dimension: customer_type
    sql: case when ${TABLE}.customer_type like '%CustomerType5erType1%' then 'CustomerType5erType1s' when ${TABLE}.customer_type like '%CustomerType5erType2%' then 'CustomerType5erType2' when ${TABLE}.customer_type like '%CustomerType5erType3%' then 'CustomerType5erType3' when ${TABLE}.customer_type like '%CustomerType5erType4%' then 'CustomerType5erType3' when ${TABLE}.customer_type like '%CustomerType5%' then 'CustomerType5' else 'Other' end
  - dimension: rank
    type: number
    hidden: true
    sql: ${TABLE}.rank
  - dimension_group: expires_time
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.expires_time
  - dimension_group: launched_time
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.launched_time
  - dimension_group: closed_time
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.closed_time
  - dimension_group: first_participation_time
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_participation_time
  - dimension_group: last_participation_time
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.last_participation_time
  - dimension_group: twohundredth_participation_time
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.twohundredth_participation_time
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: unlimited
    type: sum
    sql: ${TABLE}.unlimited::int
  - measure: num_participations
    type: sum
    sql: ${TABLE}.num_participations
  - measure: participations_duration_days
    type: avg
    sql: round( CAST(EXTRACT(epoch from (${TABLE}.last_participation_time - ${TABLE}.first_participation_time))/86400 as numeric),2)
  - measure: partner_action_duration_days
    type: avg
    sql: round( CAST(EXTRACT(epoch from (${TABLE}.closed_time - ${TABLE}.launched_time))/86400 as numeric),2)
  - measure: closed_under_one_day
    type: sum
    sql: ((${TABLE}.closed_time - ${TABLE}.launched_time) < '1 days'::interval)::int
  - measure: closed_early
    type: sum
    sql: (${TABLE}.closed_time < ${TABLE}.expires_time)::int
  - measure: twohundred_participations_in_one_day
    type: sum
    sql: coalesce(((${TABLE}.twohundredth_participation_time - ${TABLE}.launched_time) < '1 days'::interval)::int,0)

participations_duration.sql
participations_duration.sql
participations_duration.view.lkml
participations_duration.view.lookml

Participations Duration

Blocks

Assumptions

Contents