Funnel

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

select kp.kissmetrics_person_id, e.employer_id, 'registered'::text event_type
  , e.created_time at time zone 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where not istest_flag
UNION
SELECT kp.kissmetrics_person_id, kp.employer_id, 'visited_site'::text event_type
  , firstvisit_time event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM kissmetrics_person kp
LEFT JOIN employer e on e.employer_id = kp.employer_id
WHERE firstvisit_time IS NOT NULL and (NOT e.istest_flag or e.employer_id is null)
UNION
SELECT kp.kissmetrics_person_id, kp.employer_id, 'start_registration'::text event_type
  , firsthitreg_time event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM kissmetrics_person kp
LEFT JOIN employer e on e.employer_id = kp.employer_id
WHERE  firsthitreg_time IS NOT NULL and (NOT e.istest_flag or e.employer_id is null)
UNION
SELECT kp.kissmetrics_person_id, e.employer_id, 'first_choose_bundle'::TEXT event_type
  , firstchoosebundle_time AT TIME ZONE 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
WHERE NOT istest_flag AND firstchoosebundle_time IS NOT NULL
UNION
SELECT kp.kissmetrics_person_id, e.employer_id, 'first_confirm_order'::TEXT event_type
  , firstconfirmorder_time AT TIME ZONE 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
WHERE NOT istest_flag AND firstconfirmorder_time IS NOT NULL
UNION
select kp.kissmetrics_person_id, e.employer_id, 'created_job'::text event_type, min(j.created_time at time zone 'GMT') event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM job j
join employer e on e.employer_id = j.employer_id and not istest_flag
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where jobtype_text <> 'SampleJob'
and title_text is not null
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'completed_job'::text event_type, min(j.completed_time at time zone 'GMT') event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM job_reporting j
join employer e on e.employer_id = j.employer_id and not istest_flag
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where jobtype_text <> 'SampleJob'
and j.completed_time is not null
--and j.completed_time < e.created_time + interval '3 days'
--and complete_flag
and title_text is not null
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'converted'::text event_type, min(close_date) event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'first time repeat'::text event_type, close_date event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
and opportunity_number = 2
UNION
select kp.kissmetrics_person_id, e.employer_id, 'repeat'::text event_type, close_date event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
and opportunity_number = 3

# models/schema.yml
version: 2

models:
  - name: funnel
    columns:
      - name: kissmetrics_person_id
      - name: employer_id
      - name: event

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 kp.kissmetrics_person_id, e.employer_id, 'registered'::text event_type
  , e.created_time at time zone 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where not istest_flag
UNION
SELECT kp.kissmetrics_person_id, kp.employer_id, 'visited_site'::text event_type
  , firstvisit_time event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM kissmetrics_person kp
LEFT JOIN employer e on e.employer_id = kp.employer_id
WHERE firstvisit_time IS NOT NULL and (NOT e.istest_flag or e.employer_id is null)
UNION
SELECT kp.kissmetrics_person_id, kp.employer_id, 'start_registration'::text event_type
  , firsthitreg_time event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM kissmetrics_person kp
LEFT JOIN employer e on e.employer_id = kp.employer_id
WHERE  firsthitreg_time IS NOT NULL and (NOT e.istest_flag or e.employer_id is null)
UNION
SELECT kp.kissmetrics_person_id, e.employer_id, 'first_choose_bundle'::TEXT event_type
  , firstchoosebundle_time AT TIME ZONE 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
WHERE NOT istest_flag AND firstchoosebundle_time IS NOT NULL
UNION
SELECT kp.kissmetrics_person_id, e.employer_id, 'first_confirm_order'::TEXT event_type
  , firstconfirmorder_time AT TIME ZONE 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
WHERE NOT istest_flag AND firstconfirmorder_time IS NOT NULL
UNION
select kp.kissmetrics_person_id, e.employer_id, 'created_job'::text event_type, min(j.created_time at time zone 'GMT') event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM job j
join employer e on e.employer_id = j.employer_id and not istest_flag
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where jobtype_text <> 'SampleJob'
and title_text is not null
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'completed_job'::text event_type, min(j.completed_time at time zone 'GMT') event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM job_reporting j
join employer e on e.employer_id = j.employer_id and not istest_flag
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where jobtype_text <> 'SampleJob'
and j.completed_time is not null
--and j.completed_time < e.created_time + interval '3 days'
--and complete_flag
and title_text is not null
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'converted'::text event_type, min(close_date) event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'first time repeat'::text event_type, close_date event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
and opportunity_number = 2
UNION
select kp.kissmetrics_person_id, e.employer_id, 'repeat'::text event_type, close_date event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
and opportunity_number = 3

view: funnel {
  derived_table: {
    sql: select kp.kissmetrics_person_id, e.employer_id, 'registered'::text event_type
  , e.created_time at time zone 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where not istest_flag
UNION
SELECT kp.kissmetrics_person_id, kp.employer_id, 'visited_site'::text event_type
  , firstvisit_time event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM kissmetrics_person kp
LEFT JOIN employer e on e.employer_id = kp.employer_id
WHERE firstvisit_time IS NOT NULL and (NOT e.istest_flag or e.employer_id is null)
UNION
SELECT kp.kissmetrics_person_id, kp.employer_id, 'start_registration'::text event_type
  , firsthitreg_time event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM kissmetrics_person kp
LEFT JOIN employer e on e.employer_id = kp.employer_id
WHERE  firsthitreg_time IS NOT NULL and (NOT e.istest_flag or e.employer_id is null)
UNION
SELECT kp.kissmetrics_person_id, e.employer_id, 'first_choose_bundle'::TEXT event_type
  , firstchoosebundle_time AT TIME ZONE 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
WHERE NOT istest_flag AND firstchoosebundle_time IS NOT NULL
UNION
SELECT kp.kissmetrics_person_id, e.employer_id, 'first_confirm_order'::TEXT event_type
  , firstconfirmorder_time AT TIME ZONE 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
WHERE NOT istest_flag AND firstconfirmorder_time IS NOT NULL
UNION
select kp.kissmetrics_person_id, e.employer_id, 'created_job'::text event_type, min(j.created_time at time zone 'GMT') event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM job j
join employer e on e.employer_id = j.employer_id and not istest_flag
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where jobtype_text <> 'SampleJob'
and title_text is not null
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'completed_job'::text event_type, min(j.completed_time at time zone 'GMT') event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM job_reporting j
join employer e on e.employer_id = j.employer_id and not istest_flag
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where jobtype_text <> 'SampleJob'
and j.completed_time is not null
--and j.completed_time < e.created_time + interval '3 days'
--and complete_flag
and title_text is not null
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'converted'::text event_type, min(close_date) event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'first time repeat'::text event_type, close_date event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
and opportunity_number = 2
UNION
select kp.kissmetrics_person_id, e.employer_id, 'repeat'::text event_type, close_date event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
and opportunity_number = 3 ;;
  }

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

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

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

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

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

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

  measure: event_count {
    type: count
    hidden: yes
    drill_fields: [drill_fields*]
  }

  measure: registration {
    type: count
    filters: [event: "registered"]
    drill_fields: [drill_fields*]
  }

  measure: start_registration {
    type: count
    filters: [event: "start_registration"]
    drill_fields: [drill_fields*]
  }

  measure: visits {
    type: count
    filters: [event: "visited_site"]
    drill_fields: [drill_fields*]
  }

  measure: created_job {
    type: count
    filters: [event: "created_job"]
    drill_fields: [drill_fields*]
  }

  measure: completed_job {
    type: count
    filters: [event: "completed_job"]
    drill_fields: [drill_fields*]
  }

  measure: conversion {
    type: count
    filters: [event: "converted"]
    drill_fields: [drill_fields*]
  }

  measure: first_time_repeat {
    type: count
    filters: [event: "first_time_repeat"]
    drill_fields: [drill_fields*]
  }

  measure: repeat {
    type: count
    filters: [event: "repeat"]
    drill_fields: [drill_fields*]
  }

  measure: first_choose_bundle {
    type: count
    filters: [event: "first_choose_bundle"]
    drill_fields: [drill_fields*]
  }

  measure: first_confirm_order {
    type: count
    filters: [event: "first_confirm_order"]
    drill_fields: [drill_fields*]
  }

  measure: registration_to_pay {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${conversion}::float / nullif(${registration}::float,0) ;;
  }

  measure: registration_to_job_created {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${created_job}::float/ nullif(${registration}::float,0) ;;
  }

  measure: registration_to_job_completed {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${completed_job}::float / nullif(${registration}::float,0) ;;
  }

  measure: job_completion_rate {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${completed_job}::float / nullif(${created_job}::float,0) ;;
  }

  measure: job_completion_to_payment {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${conversion}::float / nullif(${completed_job}::float,0) ;;
  }

  measure: visits_to_start_reg {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${start_registration}::float / nullif(${visits}::float,0) ;;
  }

  measure: start_reg_to_reg {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${registration}::float / nullif(${start_registration}::float,0) ;;
  }

  measure: job_completion_to_first_choose_bundle {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${first_choose_bundle}::float / nullif(${completed_job}::float,0) ;;
  }

  measure: first_choose_bundle_to_first_confirm_order {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${first_confirm_order}::float / nullif(${first_choose_bundle}::float,0) ;;
  }

  measure: first_confirm_order_to_conversion {
    type: number
    value_format: "#.00%"
    drill_fields: [main*]
    sql: ${conversion}::float / nullif(${first_confirm_order}::float,0) ;;
  }
}

- view: funnel
  derived_table:
    sql: select kp.kissmetrics_person_id, e.employer_id, 'registered'::text event_type
  , e.created_time at time zone 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where not istest_flag
UNION
SELECT kp.kissmetrics_person_id, kp.employer_id, 'visited_site'::text event_type
  , firstvisit_time event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM kissmetrics_person kp
LEFT JOIN employer e on e.employer_id = kp.employer_id
WHERE firstvisit_time IS NOT NULL and (NOT e.istest_flag or e.employer_id is null)
UNION
SELECT kp.kissmetrics_person_id, kp.employer_id, 'start_registration'::text event_type
  , firsthitreg_time event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM kissmetrics_person kp
LEFT JOIN employer e on e.employer_id = kp.employer_id
WHERE  firsthitreg_time IS NOT NULL and (NOT e.istest_flag or e.employer_id is null)
UNION
SELECT kp.kissmetrics_person_id, e.employer_id, 'first_choose_bundle'::TEXT event_type
  , firstchoosebundle_time AT TIME ZONE 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
WHERE NOT istest_flag AND firstchoosebundle_time IS NOT NULL
UNION
SELECT kp.kissmetrics_person_id, e.employer_id, 'first_confirm_order'::TEXT event_type
  , firstconfirmorder_time AT TIME ZONE 'GMT' event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM employer e
left join kissmetrics_person kp on e.employer_id = kp.employer_id
WHERE NOT istest_flag AND firstconfirmorder_time IS NOT NULL
UNION
select kp.kissmetrics_person_id, e.employer_id, 'created_job'::text event_type, min(j.created_time at time zone 'GMT') event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM job j
join employer e on e.employer_id = j.employer_id and not istest_flag
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where jobtype_text <> 'SampleJob'
and title_text is not null
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'completed_job'::text event_type, min(j.completed_time at time zone 'GMT') event_time
  , kp.firstvisit_time
  , e.created_time registration_time
FROM job_reporting j
join employer e on e.employer_id = j.employer_id and not istest_flag
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where jobtype_text <> 'SampleJob'
and j.completed_time is not null
--and j.completed_time < e.created_time + interval '3 days'
--and complete_flag
and title_text is not null
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'converted'::text event_type, min(close_date) event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
group by 1,2,5,6
UNION
select kp.kissmetrics_person_id, e.employer_id, 'first time repeat'::text event_type, close_date event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
and opportunity_number = 2
UNION
select kp.kissmetrics_person_id, e.employer_id, 'repeat'::text event_type, close_date event_time
  , kp.firstvisit_time
  , e.created_time registration_time
from opportunity_reporting o
join salesforce_contact c on c.external_contact_id = o.external_contact_id
join employer e on e.employer_id = c.employer_id
left join kissmetrics_person kp on e.employer_id = kp.employer_id
where stage in ('Closed Won','Refunded')
and (refund_reason not in ('Fraud','Terms of Service Violation') or refund_reason is null)
and not e.istest_flag
and opportunity_number = 3
  fields:
  - dimension: kissmetrics_person_id
    type: number
    sql: ${TABLE}.kissmetrics_person_id
  - dimension: employer_id
    type: number
    sql: ${TABLE}.employer_id
  - dimension: event
    sql: ${TABLE}.event_type
  - dimension_group: event
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.event_time
  - dimension_group: registration
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.registration_time
  - dimension_group: first_visit
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.firstvisit_time
  - measure: event_count
    type: count
    drill_fields:
    - drill_fields*
  - measure: registration
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: registered
  - measure: start_registration
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: start_registration
  - measure: visits
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: visited_site
  - measure: created_job
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: created_job
  - measure: completed_job
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: completed_job
  - measure: conversion
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: converted
  - measure: first_time_repeat
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: first_time_repeat
  - measure: repeat
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: repeat
  - measure: first_choose_bundle
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: first_choose_bundle
  - measure: first_confirm_order
    type: count
    drill_fields:
    - drill_fields*
    filters:
    - event: first_confirm_order
  - measure: registration_to_pay
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${conversion}::float / nullif(${registration}::float,0)
  - measure: registration_to_job_created
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${created_job}::float/ nullif(${registration}::float,0)
  - measure: registration_to_job_completed
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${completed_job}::float / nullif(${registration}::float,0)
  - measure: job_completion_rate
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${completed_job}::float / nullif(${created_job}::float,0)
  - measure: job_completion_to_payment
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${conversion}::float / nullif(${completed_job}::float,0)
  - measure: visits_to_start_reg
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${start_registration}::float / nullif(${visits}::float,0)
  - measure: start_reg_to_reg
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${registration}::float / nullif(${start_registration}::float,0)
  - measure: job_completion_to_first_choose_bundle
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${first_choose_bundle}::float / nullif(${completed_job}::float,0)
  - measure: first_choose_bundle_to_first_confirm_order
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${first_confirm_order}::float / nullif(${first_choose_bundle}::float,0)
  - measure: first_confirm_order_to_conversion
    type: number
    value_format: '#.00%'
    drill_fields:
    - main*
    sql: ${conversion}::float / nullif(${first_confirm_order}::float,0)