data.ml
SQL Freelance marketplace

External User Test

External User Test model for freelance marketplace.

source-preserved

Blocks

external_user_test.sql · 30 lines
inferred 
-- models/external_user_test.sql
{{ config(materialized='table') }}

select
    t.test_id
  , t.test_cell_id AS test_cell
  , qtc.name AS cell_name
  , u.id as qt_user_id
  , u.username as username
  , u.visitor_id as visitor_id
  , t.allocated_ts
from qt_user_tests as t
  left join qt_users as u on u.id = t.user_id
  left join {{ this }}.user_data as cu on u.username = cu.uid
  left join qt_test_cells AS qtc ON qtc.id = t.test_cell_id
where t.test_cell_id IS NOT NULL
  and t.test_cell_id <> 0

# models/schema.yml
version: 2

models:
  - name: external_user_test
    columns:
      - name: test_id
      - name: test_cell
      - name: cell_name
      - name: qt_user_id
      - name: username
      - name: visitor_id

Assumptions

  • materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
  • schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.
external_user_test.sql · 14 lines
source-preserved validation: not checked 
select
    t.test_id
  , t.test_cell_id AS test_cell
  , qtc.name AS cell_name
  , u.id as qt_user_id
  , u.username as username
  , u.visitor_id as visitor_id
  , t.allocated_ts
from qt_user_tests as t
  left join qt_users as u on u.id = t.user_id
  left join ${TABLE}.user_data as cu on u.username = cu.uid
  left join qt_test_cells AS qtc ON qtc.id = t.test_cell_id
where t.test_cell_id IS NOT NULL
  and t.test_cell_id <> 0
external_user_test.view.lkml · 217 lines
inferred 
view: external_user_test {
  derived_table: {
    sql: select
    t.test_id
  , t.test_cell_id AS test_cell
  , qtc.name AS cell_name
  , u.id as qt_user_id
  , u.username as username
  , u.visitor_id as visitor_id
  , t.allocated_ts
from qt_user_tests as t
  left join qt_users as u on u.id = t.user_id
  left join ${TABLE}.user_data as cu on u.username = cu.uid
  left join qt_test_cells AS qtc ON qtc.id = t.test_cell_id
where t.test_cell_id IS NOT NULL
  and t.test_cell_id <> 0 ;;
    persist_for: "12 hours"
  }

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

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

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

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

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

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

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

  measure: count {
    type: count
  }

  measure: allocated_count {
    type: count_distinct
    sql: ${qt_user_id} ;;
  }

  measure: allocated_count_running_total {
    label: "Total allocated (Cumulative)"
    type: running_total
    sql: ${allocated_count} ;;
  }

  measure: started_client_reg_count {
    type: count_distinct
    filters: [client_started_reg: "True"]
    sql: ${qt_user_id} ;;
  }

  measure: client_unverified_signup_rate {
    type: number
    sql: 100.0 * ${started_client_reg_count} / NULLIF(${allocated_count},0) ;;
  }

  measure: client_unverified_signup_rate_use_w_qt_744 {
    type: number
    sql: 13.0 * ${started_client_reg_count} / NULLIF(${allocated_count},0) ;;
  }

  measure: verified_client_reg_count {
    type: count_distinct
    filters: [client_verified_reg: "True"]
    sql: ${qt_user_id} ;;
  }

  measure: client_verified_signup_rate {
    type: number
    sql: 100.0*${verified_client_reg_count} / NULLIF( ${allocated_count},0) ;;
  }

  measure: started_freelancer_reg_count {
    type: count_distinct
    filters: [started_freelancer_reg: "True"]
    sql: ${qt_user_id} ;;
  }

  measure: freelancer_unverified_signup_rate {
    type: number
    sql: 100.0 * ${started_freelancer_reg_count} / NULLIF(${allocated_count},0) ;;
  }

  measure: verified_freelancer_reg_count {
    type: count_distinct
    filters: [freelancer_verified_reg: "True"]
    sql: ${qt_user_id} ;;
  }

  measure: freelancer_verified_signup_rate {
    type: number
    sql: 100.0 * ${verified_freelancer_reg_count} / NULLIF(${allocated_count},0) ;;
  }

  measure: cfjp_count {
    type: count_distinct
    filters: [has_cfjp: "True"]
    sql: ${qt_user_id} ;;
  }

  measure: cfjp_rate {
    type: number
    sql: 100.0 * ${cfjp_count} / NULLIF(${allocated_count},0) ;;
  }

  measure: first_start_count {
    type: count_distinct
    filters: [has_1st_start: "True"]
    sql: ${qt_user_id} ;;
  }

  measure: first_start_count_running_total {
    label: "Total started (Cumulative)"
    type: running_total
    sql: ${first_start_count} ;;
  }

  measure: start_rate {
    type: number
    sql: 100.0 * ${first_start_count} / NULLIF(${allocated_count},0) ;;
  }

  measure: liquid_test {
    type: running_total
    sql: ${first_start_count} ;;
  }

  measure: fjp_count {
    type: count_distinct
    filters: [has_fjp: "True"]
    sql: ${qt_user_id} ;;
  }

  measure: total_hr_asg_with_billing {
    type: sum
    sql: ${${TABLE}.employer_ranking.hr_assignments_with_billing} ;;
  }

  measure: total_fp_asg_with_billing {
    type: sum
    sql: ${${TABLE}.employer_ranking.fp_assignments_with_billing} ;;
  }

  measure: total_hr_openings_posted {
    type: sum
    sql: ${${TABLE}.employer_ranking.count_hr_openings_posted} ;;
  }

  measure: total_fp_openings_posted {
    type: sum
    sql: ${${TABLE}.employer_ranking.count_fp_openings_posted} ;;
  }

  measure: total_hours_billed {
    type: sum
    sql: ${${TABLE}.employer_ranking.hours} ;;
  }

  measure: total_charges {
    type: sum
    sql: ${${TABLE}.employer_ranking.total_charge} ;;
  }

  measure: total_hourly_charges {
    type: sum
    sql: ${${TABLE}.employer_ranking.hr_charge} ;;
  }

  measure: total_fp_charges {
    type: sum
    sql: ${${TABLE}.employer_ranking.fp_charge} ;;
  }

  measure: client_social_unverified_signups {
    type: count_distinct
    filters: [client_started_reg_social: "True"]
    sql: ${qt_user_id} ;;
  }

  measure: contractor_social_unverified_signups {
    type: count_distinct
    filters: [contractor_started_reg_social: "True"]
    sql: ${qt_user_id} ;;
  }

  measure: contractor_social_unverified_signup_rate {
    type: number
    sql: 100.0*${contractor_social_unverified_signups} / NULLIF(${allocated_count},0) ;;
  }

  measure: client_social_unverified_signup_rate {
    type: number
    sql: 100.0*${client_social_unverified_signups} / NULLIF(${allocated_count},0) ;;
  }
}
external_user_test.view.lookml · 153 lines
source-preserved 
- view: external_user_test
  derived_table:
    sql: select
    t.test_id
  , t.test_cell_id AS test_cell
  , qtc.name AS cell_name
  , u.id as qt_user_id
  , u.username as username
  , u.visitor_id as visitor_id
  , t.allocated_ts
from qt_user_tests as t
  left join qt_users as u on u.id = t.user_id
  left join ${TABLE}.user_data as cu on u.username = cu.uid
  left join qt_test_cells AS qtc ON qtc.id = t.test_cell_id
where t.test_cell_id IS NOT NULL
  and t.test_cell_id <> 0
    persist_for: 12 hours
  fields:
  - dimension: test_id
    type: number
    sql: ${TABLE}.test_id
  - dimension: test_cell
    type: number
    sql: ${TABLE}.test_cell
  - dimension: cell_name
    sql: ${TABLE}.cell_name
  - dimension: qt_user_id
    type: number
    sql: ${TABLE}.qt_user_id
  - dimension: username
    sql: ${TABLE}.username
  - dimension: visitor_id
    sql: ${TABLE}.visitor_id
  - dimension_group: allocated_ts
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.allocated_ts
  - measure: count
    type: count
  - measure: allocated_count
    type: count_distinct
    sql: ${qt_user_id}
  - measure: allocated_count_running_total
    type: running_total
    label: Total allocated (Cumulative)
    sql: ${allocated_count}
  - measure: started_client_reg_count
    type: count_distinct
    filters:
    - client_started_reg: True
    sql: ${qt_user_id}
  - measure: client_unverified_signup_rate
    type: number
    sql: 100.0 * ${started_client_reg_count} / NULLIF(${allocated_count},0)
  - measure: client_unverified_signup_rate_use_w_qt_744
    type: number
    sql: 13.0 * ${started_client_reg_count} / NULLIF(${allocated_count},0)
  - measure: verified_client_reg_count
    type: count_distinct
    filters:
    - client_verified_reg: True
    sql: ${qt_user_id}
  - measure: client_verified_signup_rate
    type: number
    sql: 100.0*${verified_client_reg_count} / NULLIF( ${allocated_count},0)
  - measure: started_freelancer_reg_count
    type: count_distinct
    filters:
    - started_freelancer_reg: True
    sql: ${qt_user_id}
  - measure: freelancer_unverified_signup_rate
    type: number
    sql: 100.0 * ${started_freelancer_reg_count} / NULLIF(${allocated_count},0)
  - measure: verified_freelancer_reg_count
    type: count_distinct
    filters:
    - freelancer_verified_reg: True
    sql: ${qt_user_id}
  - measure: freelancer_verified_signup_rate
    type: number
    sql: 100.0 * ${verified_freelancer_reg_count} / NULLIF(${allocated_count},0)
  - measure: cfjp_count
    type: count_distinct
    filters:
    - has_cfjp: True
    sql: ${qt_user_id}
  - measure: cfjp_rate
    type: number
    sql: 100.0 * ${cfjp_count} / NULLIF(${allocated_count},0)
  - measure: first_start_count
    type: count_distinct
    filters:
    - has_1st_start: True
    sql: ${qt_user_id}
  - measure: first_start_count_running_total
    type: running_total
    label: Total started (Cumulative)
    sql: ${first_start_count}
  - measure: start_rate
    type: number
    sql: 100.0 * ${first_start_count} / NULLIF(${allocated_count},0)
  - measure: liquid_test
    type: running_total
    sql: ${first_start_count}
  - measure: fjp_count
    type: count_distinct
    filters:
    - has_fjp: True
    sql: ${qt_user_id}
  - measure: total_hr_asg_with_billing
    type: sum
    sql: ${${TABLE}.employer_ranking.hr_assignments_with_billing}
  - measure: total_fp_asg_with_billing
    type: sum
    sql: ${${TABLE}.employer_ranking.fp_assignments_with_billing}
  - measure: total_hr_openings_posted
    type: sum
    sql: ${${TABLE}.employer_ranking.count_hr_openings_posted}
  - measure: total_fp_openings_posted
    type: sum
    sql: ${${TABLE}.employer_ranking.count_fp_openings_posted}
  - measure: total_hours_billed
    type: sum
    sql: ${${TABLE}.employer_ranking.hours}
  - measure: total_charges
    type: sum
    sql: ${${TABLE}.employer_ranking.total_charge}
  - measure: total_hourly_charges
    type: sum
    sql: ${${TABLE}.employer_ranking.hr_charge}
  - measure: total_fp_charges
    type: sum
    sql: ${${TABLE}.employer_ranking.fp_charge}
  - measure: client_social_unverified_signups
    type: count_distinct
    filters:
    - client_started_reg_social: True
    sql: ${qt_user_id}
  - measure: contractor_social_unverified_signups
    type: count_distinct
    filters:
    - contractor_started_reg_social: True
    sql: ${qt_user_id}
  - measure: contractor_social_unverified_signup_rate
    type: number
    sql: 100.0*${contractor_social_unverified_signups} / NULLIF(${allocated_count},0)
  - measure: client_social_unverified_signup_rate
    type: number
    sql: 100.0*${client_social_unverified_signups} / NULLIF(${allocated_count},0)

Contents

  • external_user_test.sql
  • external_user_test.sql
  • external_user_test.view.lkml
  • external_user_test.view.lookml