Work Items Detail

view: work_items_detail {
  sql_table_name: tasks ;;

  dimension: has_offers {
    type: yesno
    sql: ${number_of_offers} > 0 ;;
  }

  dimension: number_of_offers {
    type: number
    sql: (SELECT COUNT(*)
FROM offers o
WHERE o.task_id=${TABLE}.id) ;;
  }

  dimension: average_number_of_offers {
    type: avg
    sql: ${number_of_offers} ;;
  }

  dimension: category_id {
    type: int
  }

  dimension: take_rate {
    type: number
    sql: ${service_fee_cents}/${charge_price_cents} ;;
  }

  dimension: service_fee_cents {
    type: number
    sql: CASE
  WHEN ${TABLE}.state IN ('closed', 'married', 'divorced')
  THEN ${charge_price_cents} - ${task_fee_cents}
  ELSE 0
END ;;
  }

  dimension: potential_service_fee_cents {
    type: number
    sql: ${charge_price_cents} - ${task_fee_cents} ;;
  }

  dimension: charge_price_cents {
    type: number
    sql: (${TABLE}.charge_price * 100) ;;
  }

  dimension: task_fee_cents {
    type: number
    sql: (${TABLE}.runner_fee * 100) ;;
  }

  dimension: close_latency {
    type: int
    sql: DATEDIFF(${closed_state_date},${opened_state_date}) ;;
  }

  dimension: close_latency_tier {
    type: tier
    sql: ${close_latency} ;;
  }

  dimension: continuing_parent_id {
    type: int
  }

  dimension: credits_free_cents {
    type: number
    sql: (${TABLE}.credits_free * 100) ;;
  }

  dimension: description {
  }

  dimension: discounted_promotion_id {
    type: int
  }

  dimension: geography_id {
    type: int
  }

  dimension: id {
    type: int
  }

  dimension: opened_state_at {
  }

  dimension: admin_url {
    sql: ${id} ;;
  }

  dimension: marketplace {
  }

  dimension: assisted_groomed {
    type: yesno
  }

  dimension: assisted_reviewed {
    type: yesno
  }

  dimension: metro_id {
    type: int
  }

  dimension: title {
    sql: ${TABLE}.name ;;
  }

  dimension: number_tasks_required {
    type: int
    sql: ${TABLE}.number_runners_required ;;
  }

  dimension: number_tasks_to_fill {
    type: int
    sql: ${TABLE}.number_runners_to_fill ;;
  }

  dimension: parent_task_id {
    type: int
  }

  dimension: posted_at {
  }

  dimension: posted_month_number {
    type: number
    sql: ${posted_month_num} ;;
  }

  dimension: price_units {
  }

  dimension: promotion_id {
    type: int
  }

  dimension: review_runners {
    type: yesno
  }

  dimension: tasker_id {
    type: int
    sql: ${TABLE}.runner_id ;;
  }

  dimension: state {
  }

  dimension: general_state {
  }

  dimension: state_derived {
  }

  dimension: suppress_level {
    type: int
  }

  dimension: task_type_id {
    type: int
  }

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

  dimension: earnings_cents {
    type: number
    sql: ${tip_in_cents}+${task_fee_cents} ;;
  }

  dimension: poster_price_cents {
    type: number
    sql: ${service_fee_cents}+${earnings_cents} ;;
  }

  dimension: town_id {
    type: int
  }

  dimension: user_id {
    type: int
  }

  dimension: assisted_marketplace_processed_at {
    type: time
    sql: (select processed_at from adminProd_review_items where remote_type = 'task' and score_type = 'assisted-marketplace' and remote_id = ${TABLE}.id) ;;
  }

  dimension: user_closed_before_count {
    type: int
  }

  dimension: fttp_tier {
    type: tier
    sql: ${user_closed_before_count} ;;
  }

  dimension: sequence_number {
    type: int
    sql: (SELECT COUNT(*)
FROM tasks t
WHERE t.created_at < ${TABLE}.created_at
  AND t.user_id=${TABLE}.user_id) + 1 ;;
  }

  dimension: posted_sequence_number {
    type: int
    sql: CASE
  WHEN ${TABLE}.state <> 'started'
  THEN
    (SELECT COUNT(*)
    FROM tasks t
    WHERE t.created_at < ${TABLE}.created_at
      AND t.user_id=${TABLE}.user_id
      AND t.state <> 'started') + 1
  ELSE NULL
END ;;
  }

  dimension: repeat_customer {
    type: yesno
    sql: ${posted_sequence_number} > 1 ;;
  }

  dimension: sequence_number_tier {
    type: tier
    sql: ${sequence_number} ;;
  }

  dimension: completed_sequence_number {
    type: int
    sql: (SELECT COUNT(*)
FROM tasks t
WHERE t.created_at < ${TABLE}.created_at
  AND t.user_id=${TABLE}.user_id
  AND t.state IN ('closed', 'completed', 'married')) + 1 ;;
  }

  dimension: completed_sequence_number_tier {
    type: tier
    sql: ${completed_sequence_number} ;;
  }

  dimension: recurring_parent {
    type: yesno
    sql: (SELECT COUNT(*)
FROM tasks t
WHERE t.continuing_parent_id=${TABLE}.id) > 0 ;;
  }

  dimension: multiple_parent {
    type: yesno
    sql: (SELECT COUNT(*)
FROM tasks t
WHERE t.parent_task_id=${TABLE}.id) > 0 ;;
  }

  dimension: parent_job {
    type: yesno
    sql: (SELECT COUNT(*)
FROM tasks t
LEFT JOIN badge_sets bs
ON bs.id=t.badge_set_id
WHERE t.id = ${TABLE}.continuing_parent_id
  AND bs.id = t.badge_set_id
  AND bs.badge_jobs_enabled = true) > 0 ;;
  }

  dimension: work_item_type {
  }

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

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

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

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

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

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

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

  dimension_group: opened_state {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: COALESCE(${TABLE}.opened_state_at, ${TABLE}.assigned_state_at, ${TABLE}.completed_state_at) ;;
  }

  dimension_group: posted {
    type: time
    timeframes: [time, date, week, month, month_num, year]
    sql: ${TABLE}.posted_at ;;
  }

  measure: has_bids_count {
    type: count_distinct
    filters: [opened_state_at: "-NULL"]
    filters: [number_of_offers: "1 to"]
    sql: ${id} ;;
  }

  measure: has_bids_rate {
    type: number
    sql: 100.0 * ${has_bids_count}/${opened_count} ;;
  }

  measure: opened_count {
    type: count_distinct
    filters: [opened_state_at: "-NULL"]
    sql: ${id} ;;
  }

  measure: organic.count {
    type: count_distinct
    filters: [work_item_type: "organic"]
    filters: [posted_at: "-NULL"]
    sql: ${id} ;;
  }

  measure: job.count {
    type: count_distinct
    filters: [work_item_type: "job"]
    filters: [posted_at: "-NULL"]
    sql: ${id} ;;
  }

  measure: multiple.count {
    type: count_distinct
    filters: [work_item_type: "multiple"]
    filters: [posted_at: "-NULL"]
    sql: ${id} ;;
  }

  measure: recurring.count {
    type: count_distinct
    filters: [work_item_type: "recurring"]
    filters: [posted_at: "-NULL"]
    sql: ${id} ;;
  }

  measure: organic.closed_count {
    type: count_distinct
    filters: [work_item_type: "organic"]
    filters: [state: "married, divorced, closed, filled"]
    sql: ${id} ;;
  }

  measure: job.closed_count {
    type: count_distinct
    filters: [work_item_type: "job"]
    filters: [state: "married, divorced, closed"]
    sql: ${id} ;;
  }

  measure: multiple.closed_count {
    type: count_distinct
    filters: [work_item_type: "multiple"]
    filters: [state: "filled"]
    sql: ${id} ;;
  }

  measure: recurring.closed_count {
    type: count_distinct
    filters: [work_item_type: "recurring"]
    filters: [state: "married, divorced"]
    sql: ${id} ;;
  }

  measure: organic.close_rate {
    type: number
    sql: CASE
  WHEN ${organic.count} = 0
  THEN NULL
  ELSE 100.0 * ${organic.closed_count}/${organic.count}
END ;;
  }

  measure: job.close_rate {
    type: number
    sql: CASE
  WHEN ${job.count} = 0
  THEN NULL
  ELSE 100.0 * ${job.closed_count}/${job.count}
END ;;
  }

  measure: multiple.close_rate {
    type: number
    sql: CASE
  WHEN ${multiple.count} = 0
  THEN NULL
  ELSE 100.0 * ${multiple.closed_count}/${multiple.count}
END ;;
  }

  measure: recurring.close_rate {
    type: number
    sql: CASE
  WHEN ${recurring.count} = 0
  THEN NULL
  ELSE 100.0 * ${recurring.closed_count}/${recurring.count}
END ;;
  }

  measure: reimbursement_cents {
    type: number
    sql: (select (SUM(reimbursement_in_cents)) from offers where offers.task_id = work_items.local_id) ;;
  }

  measure: average_service_fee_cents {
    type: avg
    sql: ${service_fee_cents} ;;
  }

  measure: average_tip_cents {
    type: avg
    sql: ${tip_in_cents} ;;
  }

  measure: total_tip_cents {
    type: sum
    sql: ${tip_in_cents} ;;
  }

  measure: task_earnings_cents_per_task {
    type: number
    sql: ${earnings_cents}/${work_items.count} ;;
  }

  measure: poster_price_cents_per_task {
    type: number
    sql: ${poster_price_cents}/${work_items.count} ;;
  }

  measure: total_service_fee_cents {
    type: sum
    sql: ${service_fee_cents} ;;
  }

  measure: total_potential_service_fee_cents {
    type: sum
    sql: ${potential_service_fee_cents} ;;
  }

  measure: average_potential_service_fee_cents {
    type: avg
    sql: ${potential_service_fee_cents} ;;
  }

  measure: average_take_rate {
    type: avg
    sql: ${take_rate} ;;
  }
}

- view: work_items_detail
  sql_table_name: tasks
  fields:
  - dimension: has_offers
    type: yesno
    sql: ${number_of_offers} > 0
  - dimension: number_of_offers
    type: number
    sql: (SELECT COUNT(*)
FROM offers o
WHERE o.task_id=${TABLE}.id)
  - dimension: average_number_of_offers
    type: avg
    sql: ${number_of_offers}
  - dimension: category_id
    type: int
  - dimension: take_rate
    type: number
    sql: ${service_fee_cents}/${charge_price_cents}
  - dimension: service_fee_cents
    type: number
    sql: CASE
  WHEN ${TABLE}.state IN ('closed', 'married', 'divorced')
  THEN ${charge_price_cents} - ${task_fee_cents}
  ELSE 0
END
  - dimension: potential_service_fee_cents
    type: number
    sql: ${charge_price_cents} - ${task_fee_cents}
  - dimension: charge_price_cents
    type: number
    sql: (${TABLE}.charge_price * 100)
  - dimension: task_fee_cents
    type: number
    sql: (${TABLE}.runner_fee * 100)
  - dimension: close_latency
    type: int
    sql: DATEDIFF(${closed_state_date},${opened_state_date})
  - dimension: close_latency_tier
    type: tier
    sql: ${close_latency}
  - dimension: continuing_parent_id
    type: int
  - dimension: credits_free_cents
    type: number
    sql: (${TABLE}.credits_free * 100)
  - dimension: description
  - dimension: discounted_promotion_id
    type: int
  - dimension: geography_id
    type: int
  - dimension: id
    type: int
  - dimension: opened_state_at
  - dimension: admin_url
    sql: ${id}
  - dimension: marketplace
  - dimension: assisted_groomed
    type: yesno
  - dimension: assisted_reviewed
    type: yesno
  - dimension: metro_id
    type: int
  - dimension: title
    sql: ${TABLE}.name
  - dimension: number_tasks_required
    type: int
    sql: ${TABLE}.number_runners_required
  - dimension: number_tasks_to_fill
    type: int
    sql: ${TABLE}.number_runners_to_fill
  - dimension: parent_task_id
    type: int
  - dimension: posted_at
  - dimension: posted_month_number
    type: number
    sql: ${posted_month_num}
  - dimension: price_units
  - dimension: promotion_id
    type: int
  - dimension: review_runners
    type: yesno
  - dimension: tasker_id
    type: int
    sql: ${TABLE}.runner_id
  - dimension: state
  - dimension: general_state
  - dimension: state_derived
  - dimension: suppress_level
    type: int
  - dimension: task_type_id
    type: int
  - dimension: tip_in_cents
    type: number
    sql: ${TABLE}.tip_in_cents
  - dimension: earnings_cents
    type: number
    sql: ${tip_in_cents}+${task_fee_cents}
  - dimension: poster_price_cents
    type: number
    sql: ${service_fee_cents}+${earnings_cents}
  - dimension: town_id
    type: int
  - dimension: user_id
    type: int
  - dimension: assisted_marketplace_processed_at
    type: time
    sql: (select processed_at from adminProd_review_items where remote_type = 'task' and score_type = 'assisted-marketplace' and remote_id = ${TABLE}.id)
  - dimension: user_closed_before_count
    type: int
  - dimension: fttp_tier
    type: tier
    sql: ${user_closed_before_count}
  - dimension: sequence_number
    type: int
    sql: (SELECT COUNT(*)
FROM tasks t
WHERE t.created_at < ${TABLE}.created_at
  AND t.user_id=${TABLE}.user_id) + 1
  - dimension: posted_sequence_number
    type: int
    sql: CASE
  WHEN ${TABLE}.state <> 'started'
  THEN
    (SELECT COUNT(*)
    FROM tasks t
    WHERE t.created_at < ${TABLE}.created_at
      AND t.user_id=${TABLE}.user_id
      AND t.state <> 'started') + 1
  ELSE NULL
END
  - dimension: repeat_customer
    type: yesno
    sql: ${posted_sequence_number} > 1
  - dimension: sequence_number_tier
    type: tier
    sql: ${sequence_number}
  - dimension: completed_sequence_number
    type: int
    sql: (SELECT COUNT(*)
FROM tasks t
WHERE t.created_at < ${TABLE}.created_at
  AND t.user_id=${TABLE}.user_id
  AND t.state IN ('closed', 'completed', 'married')) + 1
  - dimension: completed_sequence_number_tier
    type: tier
    sql: ${completed_sequence_number}
  - dimension: recurring_parent
    type: yesno
    sql: (SELECT COUNT(*)
FROM tasks t
WHERE t.continuing_parent_id=${TABLE}.id) > 0
  - dimension: multiple_parent
    type: yesno
    sql: (SELECT COUNT(*)
FROM tasks t
WHERE t.parent_task_id=${TABLE}.id) > 0
  - dimension: parent_job
    type: yesno
    sql: (SELECT COUNT(*)
FROM tasks t
LEFT JOIN badge_sets bs
ON bs.id=t.badge_set_id
WHERE t.id = ${TABLE}.continuing_parent_id
  AND bs.id = t.badge_set_id
  AND bs.badge_jobs_enabled = true) > 0
  - dimension: work_item_type
  - dimension_group: assigned_state
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.assigned_state_at
  - dimension_group: began_state
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.began_state_at
  - dimension_group: cancel_request
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.cancel_request_at
  - dimension_group: closed_state
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.closed_state_at
  - dimension_group: complete_by
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.complete_by_time
  - dimension_group: completed_state
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.completed_state_at
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - dimension_group: opened_state
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: COALESCE(${TABLE}.opened_state_at, ${TABLE}.assigned_state_at, ${TABLE}.completed_state_at)
  - dimension_group: posted
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - month_num
    - year
    sql: ${TABLE}.posted_at
  - measure: has_bids_count
    type: count_distinct
    filters:
    - opened_state_at: -NULL
    - number_of_offers: 1 to
    sql: ${id}
  - measure: has_bids_rate
    type: number
    sql: 100.0 * ${has_bids_count}/${opened_count}
  - measure: opened_count
    type: count_distinct
    filters:
    - opened_state_at: -NULL
    sql: ${id}
  - measure: organic.count
    type: count_distinct
    filters:
    - work_item_type: organic
    - posted_at: -NULL
    sql: ${id}
  - measure: job.count
    type: count_distinct
    filters:
    - work_item_type: job
    - posted_at: -NULL
    sql: ${id}
  - measure: multiple.count
    type: count_distinct
    filters:
    - work_item_type: multiple
    - posted_at: -NULL
    sql: ${id}
  - measure: recurring.count
    type: count_distinct
    filters:
    - work_item_type: recurring
    - posted_at: -NULL
    sql: ${id}
  - measure: organic.closed_count
    type: count_distinct
    filters:
    - work_item_type: organic
    - state: married, divorced, closed, filled
    sql: ${id}
  - measure: job.closed_count
    type: count_distinct
    filters:
    - work_item_type: job
    - state: married, divorced, closed
    sql: ${id}
  - measure: multiple.closed_count
    type: count_distinct
    filters:
    - work_item_type: multiple
    - state: filled
    sql: ${id}
  - measure: recurring.closed_count
    type: count_distinct
    filters:
    - work_item_type: recurring
    - state: married, divorced
    sql: ${id}
  - measure: organic.close_rate
    type: number
    sql: CASE
  WHEN ${organic.count} = 0
  THEN NULL
  ELSE 100.0 * ${organic.closed_count}/${organic.count}
END
  - measure: job.close_rate
    type: number
    sql: CASE
  WHEN ${job.count} = 0
  THEN NULL
  ELSE 100.0 * ${job.closed_count}/${job.count}
END
  - measure: multiple.close_rate
    type: number
    sql: CASE
  WHEN ${multiple.count} = 0
  THEN NULL
  ELSE 100.0 * ${multiple.closed_count}/${multiple.count}
END
  - measure: recurring.close_rate
    type: number
    sql: CASE
  WHEN ${recurring.count} = 0
  THEN NULL
  ELSE 100.0 * ${recurring.closed_count}/${recurring.count}
END
  - measure: reimbursement_cents
    type: number
    sql: (select (SUM(reimbursement_in_cents)) from offers where offers.task_id = work_items.local_id)
  - measure: average_service_fee_cents
    type: avg
    sql: ${service_fee_cents}
  - measure: average_tip_cents
    type: avg
    sql: ${tip_in_cents}
  - measure: total_tip_cents
    type: sum
    sql: ${tip_in_cents}
  - measure: task_earnings_cents_per_task
    type: number
    sql: ${earnings_cents}/${work_items.count}
  - measure: poster_price_cents_per_task
    type: number
    sql: ${poster_price_cents}/${work_items.count}
  - measure: total_service_fee_cents
    type: sum
    sql: ${service_fee_cents}
  - measure: total_potential_service_fee_cents
    type: sum
    sql: ${potential_service_fee_cents}
  - measure: average_potential_service_fee_cents
    type: avg
    sql: ${potential_service_fee_cents}
  - measure: average_take_rate
    type: avg
    sql: ${take_rate}