Paid Active Users

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

--SELECT COUNT(userID) AS UserCnt, COUNT(distinct userID) AS UserDistinctCnt, COUNT(officeID) AS OfficeCnt, COUNT(DISTINCT officeID) AS OfficeDistinctCnt
--7086  6947  7086  1651  + 37 =

--If roll up seats (countofuserid) in this query, will not double count multiple domains, Office count should also be correct
--next step: need OfficeName, CompanyName, CompanyID, and test company level users and seats
--Note: OfficeName and CompanyName have to be pulled from Office and Company tables as these fields are null in UserProfile table
--Need another query for Office, rooms, seats, departments hierarchy

--??? Domain question: User can have two emails that are two different domains, <% domain_placeholder_1 %>   <% domain_placeholder_2 %>, however the company only has one domain:
--<% domain_placeholder_1 %>  How to define domain when rolling up lines and users?
--(
SELECT
STRING(UserID) AS UserID,
CompanyID,
CompanyName,
OfficeID,
OfficeName,
GoogleEmail,
GoogleDomain,
PlanState,
DateUserAdded,
PlanLines,
PlanID, UserState, OfficeState, OfficeNumberPaidInvoices, FirstPaidDate, OfficeAddedDate
FROM
  (

  SELECT UserID,
  CompanyID,
  CompanyName,
  OfficeID,
  a.officeName AS OfficeName,
  GoogleEmail,
  GoogleDomain,
  PlanState,
  DateUserAdded,
  PlanLines,
  planID, UserState, OfficeState, OfficeAddedDate, OfficeNumberPaidInvoices
  FROM

    --Users With PlanState
    (
      SELECT
        userID,
        companyID,
        cp.name AS companyName,
        officeID,
        Office.officeName AS officeName,
        GoogleEmail,
        GoogleDomain,
        PlanState,
        DateUserAdded,
        PlanLines,
        planID, UserState, OfficeState, OfficeAddedDate
      FROM
          (
            SELECT users.__key__.id AS userID,
            users.company.id AS companyID,
            users.office.id AS officeID,
            users.google_email AS GoogleEmail,
            users.google_apps_domain AS GoogleDomain,
            plans.state AS PlanState,
            users.date_added AS DateUserAdded,
            plans.lines AS PlanLines,
            plans.planID AS PlanID,
            users.state AS UserState,
            FROM [uv_entities.UserProfile] AS users
              --to get officename:
              --left join [uv_entities.Office] AS o on o.__key__.id=users.office.id
          LEFT JOIN
            (SELECT state, lines, integer( __key__.name ) as planID FROM [uv_entities.Plan]) as plans ON users.office.id = plans.planID
          --to get companyName:

          )

          AS UsersWithPlanState

        LEFT JOIN
          (SELECT __key__.id AS OfficeID2, name AS OfficeName, state AS OfficeState, date_added AS OfficeAddedDate FROM [uv_entities.Office]) AS Office
          ON Office.OfficeID2=UsersWithPlanState.officeID
        LEFT JOIN
          (SELECT __key__.id, name from [uv_entities.Company]) AS cp on cp.__key__.id=UsersWithPlanState.companyID


    ) AS a


    LEFT JOIN
    -- Invoice Paid
      (SELECT INTEGER(plan.name) AS PlanName1,
      SUM(FLOAT(total)) AS OfficeAmountPaidInvoices,
      COUNT(*) AS OfficeNumberPaidInvoices
      FROM [uv_entities.Invoice] WHERE state='paid'
      GROUP BY PlanName1) AS b
      ON a.officeID=b.PlanName1

  ) as c


--FirstPaidDate Lines>0? When taking out this join, query result did not change
LEFT JOIN
  (SELECT INTEGER(plan.name) AS PlanName2, MIN(created_date) AS FirstPaidDate FROM [uv_entities.PlanUpdate] WHERE lines>0 GROUP BY PlanName2)
  as d
  ON c.officeID = d.PlanName2

WHERE OfficeState="active"
  and PlanState in ("paid","hold")
    and Userstate = "active"
  and OfficeID not in (
    380011,
    4545050834894848,
    5245201320771584,
    5342941874749440,
    5545238122725376,
    5570842381516800,
    5799460001021952,
    5816641115914240,
    5858205259268096,
    5908568125472768,
    5950706204803072,
    6018656731398144,
    6030118652739584,
    6180154732707840,
    6462149736005632,
    6603753188753408,
    6637839714353152,
    6722129793908736)
  and OfficeID is not null
 -- and lower(companyName) like "%uber%" )
 -- and companyName like "Stemcell Technologies Inc.%"
  --)
  --and lower(officeName) <>"pasky gruber")
  --group by companyName, GoogleDomain, officeName

# models/schema.yml
version: 2

models:
  - name: paid_active_users
    columns:
      - name: userid
      - name: companyid
      - name: companyname
      - name: officeid
      - name: officename
      - name: googleemail
      - name: googledomain
      - name: planstate
      - name: planlines
      - name: planid
      - name: userstate
      - name: officestate
      - name: officenumberpaidinvoices
      - name: days_between_signup_and_paid
      - name: days_between_signup_and_paid_bucketed

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 COUNT(userID) AS UserCnt, COUNT(distinct userID) AS UserDistinctCnt, COUNT(officeID) AS OfficeCnt, COUNT(DISTINCT officeID) AS OfficeDistinctCnt
--7086  6947  7086  1651  + 37 =

--If roll up seats (countofuserid) in this query, will not double count multiple domains, Office count should also be correct
--next step: need OfficeName, CompanyName, CompanyID, and test company level users and seats
--Note: OfficeName and CompanyName have to be pulled from Office and Company tables as these fields are null in UserProfile table
--Need another query for Office, rooms, seats, departments hierarchy

--??? Domain question: User can have two emails that are two different domains, <% domain_placeholder_1 %>   <% domain_placeholder_2 %>, however the company only has one domain:
--<% domain_placeholder_1 %>  How to define domain when rolling up lines and users?
--(
SELECT
STRING(UserID) AS UserID,
CompanyID,
CompanyName,
OfficeID,
OfficeName,
GoogleEmail,
GoogleDomain,
PlanState,
DateUserAdded,
PlanLines,
PlanID, UserState, OfficeState, OfficeNumberPaidInvoices, FirstPaidDate, OfficeAddedDate
FROM
  (

  SELECT UserID,
  CompanyID,
  CompanyName,
  OfficeID,
  a.officeName AS OfficeName,
  GoogleEmail,
  GoogleDomain,
  PlanState,
  DateUserAdded,
  PlanLines,
  planID, UserState, OfficeState, OfficeAddedDate, OfficeNumberPaidInvoices
  FROM

    --Users With PlanState
    (
      SELECT
        userID,
        companyID,
        cp.name AS companyName,
        officeID,
        Office.officeName AS officeName,
        GoogleEmail,
        GoogleDomain,
        PlanState,
        DateUserAdded,
        PlanLines,
        planID, UserState, OfficeState, OfficeAddedDate
      FROM
          (
            SELECT users.__key__.id AS userID,
            users.company.id AS companyID,
            users.office.id AS officeID,
            users.google_email AS GoogleEmail,
            users.google_apps_domain AS GoogleDomain,
            plans.state AS PlanState,
            users.date_added AS DateUserAdded,
            plans.lines AS PlanLines,
            plans.planID AS PlanID,
            users.state AS UserState,
            FROM [uv_entities.UserProfile] AS users
              --to get officename:
              --left join [uv_entities.Office] AS o on o.__key__.id=users.office.id
          LEFT JOIN
            (SELECT state, lines, integer( __key__.name ) as planID FROM [uv_entities.Plan]) as plans ON users.office.id = plans.planID
          --to get companyName:

          )

          AS UsersWithPlanState

        LEFT JOIN
          (SELECT __key__.id AS OfficeID2, name AS OfficeName, state AS OfficeState, date_added AS OfficeAddedDate FROM [uv_entities.Office]) AS Office
          ON Office.OfficeID2=UsersWithPlanState.officeID
        LEFT JOIN
          (SELECT __key__.id, name from [uv_entities.Company]) AS cp on cp.__key__.id=UsersWithPlanState.companyID


    ) AS a


    LEFT JOIN
    -- Invoice Paid
      (SELECT INTEGER(plan.name) AS PlanName1,
      SUM(FLOAT(total)) AS OfficeAmountPaidInvoices,
      COUNT(*) AS OfficeNumberPaidInvoices
      FROM [uv_entities.Invoice] WHERE state='paid'
      GROUP BY PlanName1) AS b
      ON a.officeID=b.PlanName1

  ) as c


--FirstPaidDate Lines>0? When taking out this join, query result did not change
LEFT JOIN
  (SELECT INTEGER(plan.name) AS PlanName2, MIN(created_date) AS FirstPaidDate FROM [uv_entities.PlanUpdate] WHERE lines>0 GROUP BY PlanName2)
  as d
  ON c.officeID = d.PlanName2

WHERE OfficeState="active"
  and PlanState in ("paid","hold")
    and Userstate = "active"
  and OfficeID not in (
    380011,
    4545050834894848,
    5245201320771584,
    5342941874749440,
    5545238122725376,
    5570842381516800,
    5799460001021952,
    5816641115914240,
    5858205259268096,
    5908568125472768,
    5950706204803072,
    6018656731398144,
    6030118652739584,
    6180154732707840,
    6462149736005632,
    6603753188753408,
    6637839714353152,
    6722129793908736)
  and OfficeID is not null
 -- and lower(companyName) like "%uber%" )
 -- and companyName like "Stemcell Technologies Inc.%"
  --)
  --and lower(officeName) <>"pasky gruber")
  --group by companyName, GoogleDomain, officeName

view: paid_active_users {
  derived_table: {
    sql: --SELECT COUNT(userID) AS UserCnt, COUNT(distinct userID) AS UserDistinctCnt, COUNT(officeID) AS OfficeCnt, COUNT(DISTINCT officeID) AS OfficeDistinctCnt
--7086  6947  7086  1651  + 37 =

--If roll up seats (countofuserid) in this query, will not double count multiple domains, Office count should also be correct
--next step: need OfficeName, CompanyName, CompanyID, and test company level users and seats
--Note: OfficeName and CompanyName have to be pulled from Office and Company tables as these fields are null in UserProfile table
--Need another query for Office, rooms, seats, departments hierarchy

--??? Domain question: User can have two emails that are two different domains, <% domain_placeholder_1 %>   <% domain_placeholder_2 %>, however the company only has one domain:
--<% domain_placeholder_1 %>  How to define domain when rolling up lines and users?
--(
SELECT
STRING(UserID) AS UserID,
CompanyID,
CompanyName,
OfficeID,
OfficeName,
GoogleEmail,
GoogleDomain,
PlanState,
DateUserAdded,
PlanLines,
PlanID, UserState, OfficeState, OfficeNumberPaidInvoices, FirstPaidDate, OfficeAddedDate
FROM
  (

  SELECT UserID,
  CompanyID,
  CompanyName,
  OfficeID,
  a.officeName AS OfficeName,
  GoogleEmail,
  GoogleDomain,
  PlanState,
  DateUserAdded,
  PlanLines,
  planID, UserState, OfficeState, OfficeAddedDate, OfficeNumberPaidInvoices
  FROM

    --Users With PlanState
    (
      SELECT
        userID,
        companyID,
        cp.name AS companyName,
        officeID,
        Office.officeName AS officeName,
        GoogleEmail,
        GoogleDomain,
        PlanState,
        DateUserAdded,
        PlanLines,
        planID, UserState, OfficeState, OfficeAddedDate
      FROM
          (
            SELECT users.__key__.id AS userID,
            users.company.id AS companyID,
            users.office.id AS officeID,
            users.google_email AS GoogleEmail,
            users.google_apps_domain AS GoogleDomain,
            plans.state AS PlanState,
            users.date_added AS DateUserAdded,
            plans.lines AS PlanLines,
            plans.planID AS PlanID,
            users.state AS UserState,
            FROM [uv_entities.UserProfile] AS users
              --to get officename:
              --left join [uv_entities.Office] AS o on o.__key__.id=users.office.id
          LEFT JOIN
            (SELECT state, lines, integer( __key__.name ) as planID FROM [uv_entities.Plan]) as plans ON users.office.id = plans.planID
          --to get companyName:

          )

          AS UsersWithPlanState

        LEFT JOIN
          (SELECT __key__.id AS OfficeID2, name AS OfficeName, state AS OfficeState, date_added AS OfficeAddedDate FROM [uv_entities.Office]) AS Office
          ON Office.OfficeID2=UsersWithPlanState.officeID
        LEFT JOIN
          (SELECT __key__.id, name from [uv_entities.Company]) AS cp on cp.__key__.id=UsersWithPlanState.companyID


    ) AS a


    LEFT JOIN
    -- Invoice Paid
      (SELECT INTEGER(plan.name) AS PlanName1,
      SUM(FLOAT(total)) AS OfficeAmountPaidInvoices,
      COUNT(*) AS OfficeNumberPaidInvoices
      FROM [uv_entities.Invoice] WHERE state='paid'
      GROUP BY PlanName1) AS b
      ON a.officeID=b.PlanName1

  ) as c


--FirstPaidDate Lines>0? When taking out this join, query result did not change
LEFT JOIN
  (SELECT INTEGER(plan.name) AS PlanName2, MIN(created_date) AS FirstPaidDate FROM [uv_entities.PlanUpdate] WHERE lines>0 GROUP BY PlanName2)
  as d
  ON c.officeID = d.PlanName2

WHERE OfficeState="active"
  and PlanState in ("paid","hold")
    and Userstate = "active"
  and OfficeID not in (
    380011,
    4545050834894848,
    5245201320771584,
    5342941874749440,
    5545238122725376,
    5570842381516800,
    5799460001021952,
    5816641115914240,
    5858205259268096,
    5908568125472768,
    5950706204803072,
    6018656731398144,
    6030118652739584,
    6180154732707840,
    6462149736005632,
    6603753188753408,
    6637839714353152,
    6722129793908736)
  and OfficeID is not null
 -- and lower(companyName) like "%uber%" )
 -- and companyName like "Stemcell Technologies Inc.%"
  --)
  --and lower(officeName) <>"pasky gruber")
  --group by companyName, GoogleDomain, officeName ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: days_between_signup_and_paid {
    type: int
    sql: DATEDIFF(${TABLE}.firstpaiddate, ${events.complete_sign_up_date}) ;;
  }

  dimension: days_between_signup_and_paid_bucketed {
    type: tier
    sql: ${days_between_signup_and_paid} ;;
  }

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

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

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

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

  measure: paid_user_count {
    type: count_distinct
    sql: ${userid} ;;
  }

  measure: paid_users_converted_1_day {
    type: count_distinct
    drill_fields: [people.profile*]
    sql: CASE WHEN ${days_between_signup_and_paid} = 1 THEN ${userid} ELSE NULL END ;;
  }

  measure: paid_users_converted_3_day {
    type: count_distinct
    drill_fields: [people.profile*]
    sql: CASE WHEN ${days_between_signup_and_paid} > 2 AND ${days_between_signup_and_paid} < 7 THEN ${userid} ELSE NULL END ;;
  }

  measure: paid_users_converted_7_day {
    type: count_distinct
    drill_fields: [people.profile*]
    sql: CASE WHEN ${days_between_signup_and_paid} > 6 THEN ${userid} ELSE NULL END ;;
  }
}

- view: paid_active_users
  derived_table:
    sql: --SELECT COUNT(userID) AS UserCnt, COUNT(distinct userID) AS UserDistinctCnt, COUNT(officeID) AS OfficeCnt, COUNT(DISTINCT officeID) AS OfficeDistinctCnt
--7086  6947  7086  1651  + 37 =

--If roll up seats (countofuserid) in this query, will not double count multiple domains, Office count should also be correct
--next step: need OfficeName, CompanyName, CompanyID, and test company level users and seats
--Note: OfficeName and CompanyName have to be pulled from Office and Company tables as these fields are null in UserProfile table
--Need another query for Office, rooms, seats, departments hierarchy

--??? Domain question: User can have two emails that are two different domains, <% domain_placeholder_1 %>   <% domain_placeholder_2 %>, however the company only has one domain:
--<% domain_placeholder_1 %>  How to define domain when rolling up lines and users?
--(
SELECT
STRING(UserID) AS UserID,
CompanyID,
CompanyName,
OfficeID,
OfficeName,
GoogleEmail,
GoogleDomain,
PlanState,
DateUserAdded,
PlanLines,
PlanID, UserState, OfficeState, OfficeNumberPaidInvoices, FirstPaidDate, OfficeAddedDate
FROM
  (

  SELECT UserID,
  CompanyID,
  CompanyName,
  OfficeID,
  a.officeName AS OfficeName,
  GoogleEmail,
  GoogleDomain,
  PlanState,
  DateUserAdded,
  PlanLines,
  planID, UserState, OfficeState, OfficeAddedDate, OfficeNumberPaidInvoices
  FROM

    --Users With PlanState
    (
      SELECT
        userID,
        companyID,
        cp.name AS companyName,
        officeID,
        Office.officeName AS officeName,
        GoogleEmail,
        GoogleDomain,
        PlanState,
        DateUserAdded,
        PlanLines,
        planID, UserState, OfficeState, OfficeAddedDate
      FROM
          (
            SELECT users.__key__.id AS userID,
            users.company.id AS companyID,
            users.office.id AS officeID,
            users.google_email AS GoogleEmail,
            users.google_apps_domain AS GoogleDomain,
            plans.state AS PlanState,
            users.date_added AS DateUserAdded,
            plans.lines AS PlanLines,
            plans.planID AS PlanID,
            users.state AS UserState,
            FROM [uv_entities.UserProfile] AS users
              --to get officename:
              --left join [uv_entities.Office] AS o on o.__key__.id=users.office.id
          LEFT JOIN
            (SELECT state, lines, integer( __key__.name ) as planID FROM [uv_entities.Plan]) as plans ON users.office.id = plans.planID
          --to get companyName:

          )

          AS UsersWithPlanState

        LEFT JOIN
          (SELECT __key__.id AS OfficeID2, name AS OfficeName, state AS OfficeState, date_added AS OfficeAddedDate FROM [uv_entities.Office]) AS Office
          ON Office.OfficeID2=UsersWithPlanState.officeID
        LEFT JOIN
          (SELECT __key__.id, name from [uv_entities.Company]) AS cp on cp.__key__.id=UsersWithPlanState.companyID


    ) AS a


    LEFT JOIN
    -- Invoice Paid
      (SELECT INTEGER(plan.name) AS PlanName1,
      SUM(FLOAT(total)) AS OfficeAmountPaidInvoices,
      COUNT(*) AS OfficeNumberPaidInvoices
      FROM [uv_entities.Invoice] WHERE state='paid'
      GROUP BY PlanName1) AS b
      ON a.officeID=b.PlanName1

  ) as c


--FirstPaidDate Lines>0? When taking out this join, query result did not change
LEFT JOIN
  (SELECT INTEGER(plan.name) AS PlanName2, MIN(created_date) AS FirstPaidDate FROM [uv_entities.PlanUpdate] WHERE lines>0 GROUP BY PlanName2)
  as d
  ON c.officeID = d.PlanName2

WHERE OfficeState="active"
  and PlanState in ("paid","hold")
    and Userstate = "active"
  and OfficeID not in (
    380011,
    4545050834894848,
    5245201320771584,
    5342941874749440,
    5545238122725376,
    5570842381516800,
    5799460001021952,
    5816641115914240,
    5858205259268096,
    5908568125472768,
    5950706204803072,
    6018656731398144,
    6030118652739584,
    6180154732707840,
    6462149736005632,
    6603753188753408,
    6637839714353152,
    6722129793908736)
  and OfficeID is not null
 -- and lower(companyName) like "%uber%" )
 -- and companyName like "Stemcell Technologies Inc.%"
  --)
  --and lower(officeName) <>"pasky gruber")
  --group by companyName, GoogleDomain, officeName
  fields:
  - dimension: userid
    type: number
    sql: ${TABLE}.userid
  - dimension: companyid
    type: number
    sql: ${TABLE}.companyid
  - dimension: companyname
    sql: ${TABLE}.companyname
  - dimension: officeid
    type: number
    sql: ${TABLE}.officeid
  - dimension: officename
    sql: ${TABLE}.officename
  - dimension: googleemail
    sql: ${TABLE}.googleemail
  - dimension: googledomain
    sql: ${TABLE}.googledomain
  - dimension: planstate
    sql: ${TABLE}.planstate
  - dimension: planlines
    type: number
    sql: ${TABLE}.planlines
  - dimension: planid
    type: number
    sql: ${TABLE}.planid
  - dimension: userstate
    sql: ${TABLE}.userstate
  - dimension: officestate
    sql: ${TABLE}.officestate
  - dimension: officenumberpaidinvoices
    type: number
    sql: ${TABLE}.officenumberpaidinvoices
  - dimension: days_between_signup_and_paid
    type: int
    sql: DATEDIFF(${TABLE}.firstpaiddate, ${events.complete_sign_up_date})
  - dimension: days_between_signup_and_paid_bucketed
    type: tier
    sql: ${days_between_signup_and_paid}
  - dimension_group: dateuseradded
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.dateuseradded
  - dimension_group: firstpaid
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.firstpaiddate
  - dimension_group: officeaddeddate
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.officeaddeddate
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: paid_user_count
    type: count_distinct
    sql: ${userid}
  - measure: paid_users_converted_1_day
    type: count_distinct
    drill_fields:
    - people.profile*
    sql: CASE WHEN ${days_between_signup_and_paid} = 1 THEN ${userid} ELSE NULL END
  - measure: paid_users_converted_3_day
    type: count_distinct
    drill_fields:
    - people.profile*
    sql: CASE WHEN ${days_between_signup_and_paid} > 2 AND ${days_between_signup_and_paid} < 7 THEN ${userid} ELSE NULL END
  - measure: paid_users_converted_7_day
    type: count_distinct
    drill_fields:
    - people.profile*
    sql: CASE WHEN ${days_between_signup_and_paid} > 6 THEN ${userid} ELSE NULL END