data.ml
dbt Saas legislation

Funnel

Funnel model for saas legislation.

inferred

Blocks

funnel.sql · 63 lines
inferred 
-- models/funnel.sql
{{ config(materialized='view') }}

SELECT
  o.created_date as event
  , 'opportunity' as event_type
  , o.account_id as account_id
  , o.id as opportunity_id
  , NULL as lead_id
  , NULL as task_id
  , NULL as event_id
FROM salesforce.opportunity o
WHERE
{% condition event_date %} o.created_date {% endcondition %}
UNION
SELECT
   l.created_date as event
  , 'lead' as event_type
  , l.converted_account_id as account_id
  , l.converted_opportunity_id as opportunity_id
  , l.id as lead_id
  , NULL as task_id
  , NULL as event_id
FROM salesforce._lead as l
WHERE
{% condition event_date %} l.created_date {% endcondition %}
UNION
SELECT
  t.created_date as event
  , 'task' as event_type
  , t.account_id as account_id
  , NULL as opportunity_id
  , NULL as lead_id
  , t.id as task_id
  , NULL as event_id
FROM salesforce.task as t
WHERE
{% condition event_date %} t.created_date {% endcondition %}
UNION
SELECT
  e.created_date as event
  , 'meeting' as event_type
  , e.account_id as account_id
  , NULL as opportunity_id
  , NULL as lead_id
  , NULL as task_id
  , e.id as event_id
FROM salesforce.event as e
WHERE
{% condition event_date %} e.created_date {% endcondition %}

# models/schema.yml
version: 2

models:
  - name: funnel
    columns:
      - name: event_type
      - name: account_id
      - name: opportunity_id
      - name: lead_id
      - name: task_id
      - name: event_id

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.
funnel.sql · 47 lines
source-preserved validation: not checked 
SELECT
  o.created_date as event
  , 'opportunity' as event_type
  , o.account_id as account_id
  , o.id as opportunity_id
  , NULL as lead_id
  , NULL as task_id
  , NULL as event_id
FROM salesforce.opportunity o
WHERE
{% condition event_date %} o.created_date {% endcondition %}
UNION
SELECT
   l.created_date as event
  , 'lead' as event_type
  , l.converted_account_id as account_id
  , l.converted_opportunity_id as opportunity_id
  , l.id as lead_id
  , NULL as task_id
  , NULL as event_id
FROM salesforce._lead as l
WHERE
{% condition event_date %} l.created_date {% endcondition %}
UNION
SELECT
  t.created_date as event
  , 'task' as event_type
  , t.account_id as account_id
  , NULL as opportunity_id
  , NULL as lead_id
  , t.id as task_id
  , NULL as event_id
FROM salesforce.task as t
WHERE
{% condition event_date %} t.created_date {% endcondition %}
UNION
SELECT
  e.created_date as event
  , 'meeting' as event_type
  , e.account_id as account_id
  , NULL as opportunity_id
  , NULL as lead_id
  , NULL as task_id
  , e.id as event_id
FROM salesforce.event as e
WHERE
{% condition event_date %} e.created_date {% endcondition %}
funnel.view.lkml · 95 lines
inferred 
view: funnel {
  derived_table: {
    sql: SELECT
  o.created_date as event
  , 'opportunity' as event_type
  , o.account_id as account_id
  , o.id as opportunity_id
  , NULL as lead_id
  , NULL as task_id
  , NULL as event_id
FROM salesforce.opportunity o
WHERE
{% condition event_date %} o.created_date {% endcondition %}
UNION
SELECT
   l.created_date as event
  , 'lead' as event_type
  , l.converted_account_id as account_id
  , l.converted_opportunity_id as opportunity_id
  , l.id as lead_id
  , NULL as task_id
  , NULL as event_id
FROM salesforce._lead as l
WHERE
{% condition event_date %} l.created_date {% endcondition %}
UNION
SELECT
  t.created_date as event
  , 'task' as event_type
  , t.account_id as account_id
  , NULL as opportunity_id
  , NULL as lead_id
  , t.id as task_id
  , NULL as event_id
FROM salesforce.task as t
WHERE
{% condition event_date %} t.created_date {% endcondition %}
UNION
SELECT
  e.created_date as event
  , 'meeting' as event_type
  , e.account_id as account_id
  , NULL as opportunity_id
  , NULL as lead_id
  , NULL as task_id
  , e.id as event_id
FROM salesforce.event as e
WHERE
{% condition event_date %} e.created_date {% endcondition %} ;;
  }

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

  dimension: account_id {
    hidden: yes
    sql: ${TABLE}.account_id ;;
  }

  dimension: opportunity_id {
    hidden: yes
    sql: ${TABLE}.opportunity_id ;;
  }

  dimension: lead_id {
    hidden: yes
    sql: ${TABLE}.lead_id ;;
  }

  dimension: task_id {
    hidden: yes
    sql: ${TABLE}.task_id ;;
  }

  dimension: event_id {
    hidden: yes
    sql: ${TABLE}.event_id ;;
  }

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

  measure: count {
    type: count
  }

  measure: amount_per_meeting {
    type: number
    sql: ${_opportunity.total_amount} / ${_event.count} ;;
  }
}
funnel.view.lookml · 80 lines
source-preserved 
- view: funnel
  derived_table:
    sql: SELECT
  o.created_date as event
  , 'opportunity' as event_type
  , o.account_id as account_id
  , o.id as opportunity_id
  , NULL as lead_id
  , NULL as task_id
  , NULL as event_id
FROM salesforce.opportunity o
WHERE
{% condition event_date %} o.created_date {% endcondition %}
UNION
SELECT
   l.created_date as event
  , 'lead' as event_type
  , l.converted_account_id as account_id
  , l.converted_opportunity_id as opportunity_id
  , l.id as lead_id
  , NULL as task_id
  , NULL as event_id
FROM salesforce._lead as l
WHERE
{% condition event_date %} l.created_date {% endcondition %}
UNION
SELECT
  t.created_date as event
  , 'task' as event_type
  , t.account_id as account_id
  , NULL as opportunity_id
  , NULL as lead_id
  , t.id as task_id
  , NULL as event_id
FROM salesforce.task as t
WHERE
{% condition event_date %} t.created_date {% endcondition %}
UNION
SELECT
  e.created_date as event
  , 'meeting' as event_type
  , e.account_id as account_id
  , NULL as opportunity_id
  , NULL as lead_id
  , NULL as task_id
  , e.id as event_id
FROM salesforce.event as e
WHERE
{% condition event_date %} e.created_date {% endcondition %}
  fields:
  - dimension: event_type
    sql: ${TABLE}.event_type
  - dimension: account_id
    hidden: true
    sql: ${TABLE}.account_id
  - dimension: opportunity_id
    hidden: true
    sql: ${TABLE}.opportunity_id
  - dimension: lead_id
    hidden: true
    sql: ${TABLE}.lead_id
  - dimension: task_id
    hidden: true
    sql: ${TABLE}.task_id
  - dimension: event_id
    hidden: true
    sql: ${TABLE}.event_id
  - dimension_group: event
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.event
  - measure: count
    type: count
  - measure: amount_per_meeting
    type: number
    sql: ${_opportunity.total_amount} / ${_event.count}

Contents

  • funnel.sql
  • funnel.sql
  • funnel.view.lkml
  • funnel.view.lookml