SQL Saas crm

Time To Campaign Start

Time To Campaign Start model for saas crm.

source-preserved

Blocks

dbt SQL LKML LookML

time_to_campaign_start.sql · 78 lines

inferred

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

SELECT
  x.user_id AS user_id,
  y.tenant_id,
  y.created_at AS subscription_created_at,
  x.questions_last_updated_at,
  x.campaign_created_at AS campaigns_created_at,
  x.ad_start_date,
  y.id as subscription_id,
  y.subscription_plan_id AS subscription_plan_id
FROM (
  SELECT
    c.user_id AS user_id,
    c.campaign_created_at AS campaign_created_at,
    c.questions_last_updated_at,
    d.ad_start_date
  FROM (
    SELECT
      COALESCE(a.user_id,b.user_id) as user_id,
      a.campaign_created_at,
      b.questions_last_updated_at
    FROM (
      SELECT
        MIN(created_at) AS campaign_created_at,
        user_id
      FROM
        public_schema.spot_campaigns
      WHERE
        term = 1
      GROUP BY
        user_id
      ) a
      FULL OUTER JOIN (
      SELECT
        MIN(last_updated) AS questions_last_updated_at,
        user_id
      FROM
        public_schema.prospecting_questions_groups
      GROUP BY
        user_id
      ) b
      ON
        a.user_id = b.user_id
    ) as c
  JOIN (
    SELECT
      MIN(start_date) as ad_start_date,
      user_id
    FROM
      public_schema.provider_ads
    GROUP BY
      user_id
    ) d
  ON
    c.user_id = d.user_id
  ) as x
JOIN
  public_schema.subscriptions y
ON
  x.user_id = y.paying_user_id

# models/schema.yml
version: 2

models:
  - name: time_to_campaign_start
    columns:
      - name: subscription_id
      - name: user_id
      - name: tenant_id
      - name: subscription_plan_id
      - name: onboarding_hours
      - name: total_campaign_start_hours
      - name: spot_creation_hours
      - name: ad_creation_hours
      - name: spot_creation_hours1

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.

time_to_campaign_start.sql · 59 lines

source-preserved validation: parses

SELECT
  x.user_id AS user_id,
  y.tenant_id,
  y.created_at AS subscription_created_at,
  x.questions_last_updated_at,
  x.campaign_created_at AS campaigns_created_at,
  x.ad_start_date,
  y.id as subscription_id,
  y.subscription_plan_id AS subscription_plan_id
FROM (
  SELECT
    c.user_id AS user_id,
    c.campaign_created_at AS campaign_created_at,
    c.questions_last_updated_at,
    d.ad_start_date
  FROM (
    SELECT
      COALESCE(a.user_id,b.user_id) as user_id,
      a.campaign_created_at,
      b.questions_last_updated_at
    FROM (
      SELECT
        MIN(created_at) AS campaign_created_at,
        user_id
      FROM
        public_schema.spot_campaigns
      WHERE
        term = 1
      GROUP BY
        user_id
      ) a
      FULL OUTER JOIN (
      SELECT
        MIN(last_updated) AS questions_last_updated_at,
        user_id
      FROM
        public_schema.prospecting_questions_groups
      GROUP BY
        user_id
      ) b
      ON
        a.user_id = b.user_id
    ) as c
  JOIN (
    SELECT
      MIN(start_date) as ad_start_date,
      user_id
    FROM
      public_schema.provider_ads
    GROUP BY
      user_id
    ) d
  ON
    c.user_id = d.user_id
  ) as x
JOIN
  public_schema.subscriptions y
ON
  x.user_id = y.paying_user_id

time_to_campaign_start.view.lkml · 173 lines

inferred

view: time_to_campaign_start {
  derived_table: {
    sql: SELECT
  x.user_id AS user_id,
  y.tenant_id,
  y.created_at AS subscription_created_at,
  x.questions_last_updated_at,
  x.campaign_created_at AS campaigns_created_at,
  x.ad_start_date,
  y.id as subscription_id,
  y.subscription_plan_id AS subscription_plan_id
FROM (
  SELECT
    c.user_id AS user_id,
    c.campaign_created_at AS campaign_created_at,
    c.questions_last_updated_at,
    d.ad_start_date
  FROM (
    SELECT
      COALESCE(a.user_id,b.user_id) as user_id,
      a.campaign_created_at,
      b.questions_last_updated_at
    FROM (
      SELECT
        MIN(created_at) AS campaign_created_at,
        user_id
      FROM
        public_schema.spot_campaigns
      WHERE
        term = 1
      GROUP BY
        user_id
      ) a
      FULL OUTER JOIN (
      SELECT
        MIN(last_updated) AS questions_last_updated_at,
        user_id
      FROM
        public_schema.prospecting_questions_groups
      GROUP BY
        user_id
      ) b
      ON
        a.user_id = b.user_id
    ) as c
  JOIN (
    SELECT
      MIN(start_date) as ad_start_date,
      user_id
    FROM
      public_schema.provider_ads
    GROUP BY
      user_id
    ) d
  ON
    c.user_id = d.user_id
  ) as x
JOIN
  public_schema.subscriptions y
ON
  x.user_id = y.paying_user_id ;;
  }

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

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

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

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

  dimension: onboarding_hours {
    type: number
    hidden: yes
    sql: 1.00*DATEDIFF(hours, ${TABLE}.subscription_created_at, ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp) ;;
  }

  dimension: total_campaign_start_hours {
    type: number
    hidden: yes
    sql: CASE WHEN DATEDIFF(hours, ${TABLE}.subscription_created_at, ${TABLE}.ad_start_date) >= 0 THEN 1.00*DATEDIFF(hours, ${TABLE}.subscription_created_at, ${TABLE}.ad_start_date) ELSE 0 END ;;
  }

  dimension: spot_creation_hours {
    type: number
    hidden: yes
    sql: 1.00*DATEDIFF(hours, ${TABLE}.questions_last_updated_at, ${spot_campaign_plan_events.sent_for_review_time}::timestamp) ;;
  }

  dimension: ad_creation_hours {
    type: number
    hidden: yes
    sql: CASE WHEN DATEDIFF(hours, ${spot_campaign_plan_events.spot_campaign_approved_time}::timestamp, ${TABLE}.ad_start_date::timestamp) >= 0 THEN 1.00*DATEDIFF(hours, ${spot_campaign_plan_events.spot_campaign_approved_time}::timestamp, ${TABLE}.ad_start_date::timestamp) ELSE 0 END ;;
  }

  dimension: spot_creation_hours1 {
    type: number
    hidden: yes
    sql: 1.00*DATEDIFF(hours, ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp, ${spot_campaign_plan_events.sent_for_review_time}::timestamp) ;;
  }

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

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

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

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

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

  measure: onboarding_days {
    type: average
    drill_fields: [detail*]
    sql: CASE WHEN extract(week FROM ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp) = extract(week FROM ${TABLE}.subscription_created_at) THEN 1.00*${onboarding_hours}/24 ELSE  1.00*(${onboarding_hours}/24) - ((extract(week FROM ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp) - extract(week FROM ${TABLE}.subscription_created_at))*2) END ;;
  }

  measure: total_campaign_start_days {
    type: average
    drill_fields: [detail*]
    sql: CASE WHEN extract(week FROM ${TABLE}.ad_start_date) = extract(week FROM ${TABLE}.subscription_created_at) THEN 1.00*${total_campaign_start_hours}/24 ELSE  1.00*(${total_campaign_start_hours}/24) - ((extract(week FROM ${TABLE}.ad_start_date) - extract(week FROM ${TABLE}.subscription_created_at))*2) END ;;
  }

  measure: spot_creation_day_old {
    type: average
    hidden: yes
    drill_fields: [detail*]
    sql: 1.00*${spot_creation_hours}/24 ;;
  }

  measure: spot_creation_days {
    type: average
    drill_fields: [detail*]
    sql: CASE WHEN extract(week FROM ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp) = extract(week FROM ${spot_campaign_plan_events.sent_for_review_time}::timestamp) THEN 1.00*${spot_creation_hours1}/24 ELSE  1.00*(${spot_creation_hours1}/24) - ((extract(week FROM ${spot_campaign_plan_events.sent_for_review_time}::timestamp) - extract(week FROM ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp))*2) END ;;
  }

  measure: ad_creation_days {
    type: average
    drill_fields: [detail*]
    sql: CASE WHEN extract(week FROM ${TABLE}.ad_start_date) = extract(week FROM ${spot_campaign_plan_events.spot_campaign_approved_time}::timestamp) THEN 1.00*${ad_creation_hours}/24 ELSE  1.00*(${ad_creation_hours}/24) - ((extract(week FROM ${TABLE}.ad_start_date) - extract(week FROM ${spot_campaign_plan_events.spot_campaign_approved_time}::timestamp))*2) END ;;
  }
}

time_to_campaign_start.view.lookml · 155 lines

source-preserved

- view: time_to_campaign_start
  derived_table:
    sql: SELECT
  x.user_id AS user_id,
  y.tenant_id,
  y.created_at AS subscription_created_at,
  x.questions_last_updated_at,
  x.campaign_created_at AS campaigns_created_at,
  x.ad_start_date,
  y.id as subscription_id,
  y.subscription_plan_id AS subscription_plan_id
FROM (
  SELECT
    c.user_id AS user_id,
    c.campaign_created_at AS campaign_created_at,
    c.questions_last_updated_at,
    d.ad_start_date
  FROM (
    SELECT
      COALESCE(a.user_id,b.user_id) as user_id,
      a.campaign_created_at,
      b.questions_last_updated_at
    FROM (
      SELECT
        MIN(created_at) AS campaign_created_at,
        user_id
      FROM
        public_schema.spot_campaigns
      WHERE
        term = 1
      GROUP BY
        user_id
      ) a
      FULL OUTER JOIN (
      SELECT
        MIN(last_updated) AS questions_last_updated_at,
        user_id
      FROM
        public_schema.prospecting_questions_groups
      GROUP BY
        user_id
      ) b
      ON
        a.user_id = b.user_id
    ) as c
  JOIN (
    SELECT
      MIN(start_date) as ad_start_date,
      user_id
    FROM
      public_schema.provider_ads
    GROUP BY
      user_id
    ) d
  ON
    c.user_id = d.user_id
  ) as x
JOIN
  public_schema.subscriptions y
ON
  x.user_id = y.paying_user_id
  fields:
  - dimension: subscription_id
    type: int
    sql: ${TABLE}.subscription_id
  - dimension: user_id
    type: int
    sql: ${TABLE}.user_id
  - dimension: tenant_id
    type: int
    sql: ${TABLE}.tenant_id
  - dimension: subscription_plan_id
    type: int
    sql: ${TABLE}.subscription_plan_id
  - dimension: onboarding_hours
    type: number
    hidden: true
    sql: 1.00*DATEDIFF(hours, ${TABLE}.subscription_created_at, ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp)
  - dimension: total_campaign_start_hours
    type: number
    hidden: true
    sql: CASE WHEN DATEDIFF(hours, ${TABLE}.subscription_created_at, ${TABLE}.ad_start_date) >= 0 THEN 1.00*DATEDIFF(hours, ${TABLE}.subscription_created_at, ${TABLE}.ad_start_date) ELSE 0 END
  - dimension: spot_creation_hours
    type: number
    hidden: true
    sql: 1.00*DATEDIFF(hours, ${TABLE}.questions_last_updated_at, ${spot_campaign_plan_events.sent_for_review_time}::timestamp)
  - dimension: ad_creation_hours
    type: number
    hidden: true
    sql: CASE WHEN DATEDIFF(hours, ${spot_campaign_plan_events.spot_campaign_approved_time}::timestamp, ${TABLE}.ad_start_date::timestamp) >= 0 THEN 1.00*DATEDIFF(hours, ${spot_campaign_plan_events.spot_campaign_approved_time}::timestamp, ${TABLE}.ad_start_date::timestamp) ELSE 0 END
  - dimension: spot_creation_hours1
    type: number
    hidden: true
    sql: 1.00*DATEDIFF(hours, ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp, ${spot_campaign_plan_events.sent_for_review_time}::timestamp)
  - dimension_group: campaign_created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.campaigns_created_at
  - dimension_group: subscription_created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.subscription_created_at
  - dimension_group: questions_last_updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.questions_last_updated_at
  - dimension_group: ad_start
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.ad_start_date
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: onboarding_days
    type: average
    drill_fields:
    - detail*
    sql: CASE WHEN extract(week FROM ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp) = extract(week FROM ${TABLE}.subscription_created_at) THEN 1.00*${onboarding_hours}/24 ELSE  1.00*(${onboarding_hours}/24) - ((extract(week FROM ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp) - extract(week FROM ${TABLE}.subscription_created_at))*2) END
  - measure: total_campaign_start_days
    type: average
    drill_fields:
    - detail*
    sql: CASE WHEN extract(week FROM ${TABLE}.ad_start_date) = extract(week FROM ${TABLE}.subscription_created_at) THEN 1.00*${total_campaign_start_hours}/24 ELSE  1.00*(${total_campaign_start_hours}/24) - ((extract(week FROM ${TABLE}.ad_start_date) - extract(week FROM ${TABLE}.subscription_created_at))*2) END
  - measure: spot_creation_day_old
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${spot_creation_hours}/24
  - measure: spot_creation_days
    type: average
    drill_fields:
    - detail*
    sql: CASE WHEN extract(week FROM ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp) = extract(week FROM ${spot_campaign_plan_events.sent_for_review_time}::timestamp) THEN 1.00*${spot_creation_hours1}/24 ELSE  1.00*(${spot_creation_hours1}/24) - ((extract(week FROM ${spot_campaign_plan_events.sent_for_review_time}::timestamp) - extract(week FROM ${spot_campaign_plan_events.spot_campaign_new_time}::timestamp))*2) END
  - measure: ad_creation_days
    type: average
    drill_fields:
    - detail*
    sql: CASE WHEN extract(week FROM ${TABLE}.ad_start_date) = extract(week FROM ${spot_campaign_plan_events.spot_campaign_approved_time}::timestamp) THEN 1.00*${ad_creation_hours}/24 ELSE  1.00*(${ad_creation_hours}/24) - ((extract(week FROM ${TABLE}.ad_start_date) - extract(week FROM ${spot_campaign_plan_events.spot_campaign_approved_time}::timestamp))*2) END

time_to_campaign_start.sql
time_to_campaign_start.sql
time_to_campaign_start.view.lkml
time_to_campaign_start.view.lookml

Time To Campaign Start

Blocks

Assumptions

Contents