dbt Saas crm life sciences

Users With Sources

Users With Sources model for saas crm life sciences.

inferred

Blocks

dbt SQL LKML LookML

users_with_sources.sql · 35 lines

inferred

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

SELECT
  users.email AS email
, MAX(date(users.signed_up_at)) AS signup
, MAX(CASE WHEN sources.remote_account_type = 'SalesforceRemoteAccount' THEN 1 ELSE 0 END) AS salesforce
, MAX(CASE WHEN sources.remote_account_type = 'ExchangeRemoteAccount' THEN 1 ELSE 0 END) AS exchange
, MAX(CASE WHEN sources.remote_account_type = 'GoogleRemoteAccount' THEN 1 ELSE 0 END) AS google
, MAX(CASE WHEN sources.remote_account_type = 'LinkedinRemoteAccount' THEN 1 ELSE 0 END) AS linkedin
, MAX(CASE WHEN sources.created_at > '2014-04-22' AND sources.remote_account_type = 'SalesforceRemoteAccount' THEN 1 ELSE 0 END) AS received_salesforce_email
, MAX(CASE WHEN sources.remote_account_type = 'SalesforceRemoteAccount' THEN sources.created_at ELSE NULL END) AS sfdc_added_date
FROM users, sources
WHERE users.uuid = sources.user_id
AND users.email NOT LIKE '%onboarding%'
AND users.email NOT LIKE '%tombstoned%'
GROUP BY email

# models/schema.yml
version: 2

models:
  - name: users_with_sources
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: email
      - name: email_connected
      - name: exchange
      - name: google
      - name: linkedin
      - name: salesforce
      - name: received_salesforce_email

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.

users_with_sources.sql · 14 lines

source-preserved validation: parses

SELECT
  users.email AS email
, MAX(date(users.signed_up_at)) AS signup
, MAX(CASE WHEN sources.remote_account_type = 'SalesforceRemoteAccount' THEN 1 ELSE 0 END) AS salesforce
, MAX(CASE WHEN sources.remote_account_type = 'ExchangeRemoteAccount' THEN 1 ELSE 0 END) AS exchange
, MAX(CASE WHEN sources.remote_account_type = 'GoogleRemoteAccount' THEN 1 ELSE 0 END) AS google
, MAX(CASE WHEN sources.remote_account_type = 'LinkedinRemoteAccount' THEN 1 ELSE 0 END) AS linkedin
, MAX(CASE WHEN sources.created_at > '2014-04-22' AND sources.remote_account_type = 'SalesforceRemoteAccount' THEN 1 ELSE 0 END) AS received_salesforce_email
, MAX(CASE WHEN sources.remote_account_type = 'SalesforceRemoteAccount' THEN sources.created_at ELSE NULL END) AS sfdc_added_date
FROM users, sources
WHERE users.uuid = sources.user_id
AND users.email NOT LIKE '%onboarding%'
AND users.email NOT LIKE '%tombstoned%'
GROUP BY email

users_with_sources.view.lkml · 119 lines

inferred

view: users_with_sources {
  derived_table: {
    sql: SELECT
  users.email AS email
, MAX(date(users.signed_up_at)) AS signup
, MAX(CASE WHEN sources.remote_account_type = 'SalesforceRemoteAccount' THEN 1 ELSE 0 END) AS salesforce
, MAX(CASE WHEN sources.remote_account_type = 'ExchangeRemoteAccount' THEN 1 ELSE 0 END) AS exchange
, MAX(CASE WHEN sources.remote_account_type = 'GoogleRemoteAccount' THEN 1 ELSE 0 END) AS google
, MAX(CASE WHEN sources.remote_account_type = 'LinkedinRemoteAccount' THEN 1 ELSE 0 END) AS linkedin
, MAX(CASE WHEN sources.created_at > '2014-04-22' AND sources.remote_account_type = 'SalesforceRemoteAccount' THEN 1 ELSE 0 END) AS received_salesforce_email
, MAX(CASE WHEN sources.remote_account_type = 'SalesforceRemoteAccount' THEN sources.created_at ELSE NULL END) AS sfdc_added_date
FROM users, sources
WHERE users.uuid = sources.user_id
AND users.email NOT LIKE '%onboarding%'
AND users.email NOT LIKE '%tombstoned%'
GROUP BY email ;;
  }

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

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

  dimension: email_connected {
    type: yesno
    sql: (${exchange} OR ${google}) ;;
  }

  dimension: exchange {
    type: yesno
    sql: ${TABLE}.exchange ;;
  }

  dimension: google {
    type: yesno
    sql: ${TABLE}.google ;;
  }

  dimension: linkedin {
    type: yesno
    sql: ${TABLE}.linkedin ;;
  }

  dimension: salesforce {
    type: yesno
    sql: ${TABLE}.salesforce = 1 ;;
  }

  dimension: received_salesforce_email {
    type: yesno
    sql: ${TABLE}.received_salesforce_email = 1 ;;
  }

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

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

  measure: exchange_connected_count {
    type: count_distinct
    filters: [exchange: "True"]
    sql: ${TABLE}.email ;;
  }

  measure: percent_users_with_exchange {
    type: number
    sql: 100.0 * ${exchange_connected_count}/${count} ;;
  }

  measure: google_connected_count {
    type: count_distinct
    filters: [google: "True"]
    sql: ${TABLE}.email ;;
  }

  measure: percent_users_with_google {
    type: number
    sql: 100.0 * ${google_connected_count}/${count} ;;
  }

  measure: linkedin_connected_count {
    type: count_distinct
    filters: [linkedin: "True"]
    sql: ${TABLE}.email ;;
  }

  measure: percent_users_with_linkedin {
    type: number
    sql: 100.0 * ${linkedin_connected_count}/${count} ;;
  }

  measure: salesforce_connected_count {
    type: count_distinct
    filters: [salesforce: "True"]
    sql: ${TABLE}.email ;;
  }

  measure: percent_users_with_salesforce {
    type: number
    sql: 100.0 * ${salesforce_connected_count}/${count} ;;
  }

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

users_with_sources.view.lookml · 91 lines

source-preserved

- view: users_with_sources
  derived_table:
    sql: SELECT
  users.email AS email
, MAX(date(users.signed_up_at)) AS signup
, MAX(CASE WHEN sources.remote_account_type = 'SalesforceRemoteAccount' THEN 1 ELSE 0 END) AS salesforce
, MAX(CASE WHEN sources.remote_account_type = 'ExchangeRemoteAccount' THEN 1 ELSE 0 END) AS exchange
, MAX(CASE WHEN sources.remote_account_type = 'GoogleRemoteAccount' THEN 1 ELSE 0 END) AS google
, MAX(CASE WHEN sources.remote_account_type = 'LinkedinRemoteAccount' THEN 1 ELSE 0 END) AS linkedin
, MAX(CASE WHEN sources.created_at > '2014-04-22' AND sources.remote_account_type = 'SalesforceRemoteAccount' THEN 1 ELSE 0 END) AS received_salesforce_email
, MAX(CASE WHEN sources.remote_account_type = 'SalesforceRemoteAccount' THEN sources.created_at ELSE NULL END) AS sfdc_added_date
FROM users, sources
WHERE users.uuid = sources.user_id
AND users.email NOT LIKE '%onboarding%'
AND users.email NOT LIKE '%tombstoned%'
GROUP BY email
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: email
    sql: ${TABLE}.email
  - dimension: email_connected
    type: yesno
    sql: (${exchange} OR ${google})
  - dimension: exchange
    type: yesno
    sql: ${TABLE}.exchange
  - dimension: google
    type: yesno
    sql: ${TABLE}.google
  - dimension: linkedin
    type: yesno
    sql: ${TABLE}.linkedin
  - dimension: salesforce
    type: yesno
    sql: ${TABLE}.salesforce = 1
  - dimension: received_salesforce_email
    type: yesno
    sql: ${TABLE}.received_salesforce_email = 1
  - dimension_group: signup
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.signup
  - dimension_group: sfdc_added
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.sfdc_added_date
  - measure: exchange_connected_count
    type: count_distinct
    filters:
    - exchange: True
    sql: ${TABLE}.email
  - measure: percent_users_with_exchange
    type: number
    sql: 100.0 * ${exchange_connected_count}/${count}
  - measure: google_connected_count
    type: count_distinct
    filters:
    - google: True
    sql: ${TABLE}.email
  - measure: percent_users_with_google
    type: number
    sql: 100.0 * ${google_connected_count}/${count}
  - measure: linkedin_connected_count
    type: count_distinct
    filters:
    - linkedin: True
    sql: ${TABLE}.email
  - measure: percent_users_with_linkedin
    type: number
    sql: 100.0 * ${linkedin_connected_count}/${count}
  - measure: salesforce_connected_count
    type: count_distinct
    filters:
    - salesforce: True
    sql: ${TABLE}.email
  - measure: percent_users_with_salesforce
    type: number
    sql: 100.0 * ${salesforce_connected_count}/${count}
  - measure: count
    type: count
    drill_fields:
    - detail*

users_with_sources.sql
users_with_sources.sql
users_with_sources.view.lkml
users_with_sources.view.lookml

Users With Sources

Blocks

Assumptions

Contents