data.ml
LKML Virtual call center

Call

Call model for virtual call center.

inferred

Blocks

call.view.lkml · 669 lines
inferred 
view: call {
  sql_table_name: tblCallData ;;

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

  dimension: dialed_phone_number {
    sql: SUBSTRING(${TABLE}.CallDNIS, 1, 3) + '-' + SUBSTRING(${TABLE}.CallDNIS, 4, 3) + '-' + SUBSTRING(${TABLE}.CallDNIS, 7, 4) ;;
  }

  dimension: callers_phone_number {
    sql: CASE
  WHEN ${TABLE}.CallType LIKE 'Inbound%'
  THEN SUBSTRING(${TABLE}.CallANI, 1, 3) + '-' + SUBSTRING(${TABLE}.CallANI, 4, 3) + '-' + SUBSTRING(${TABLE}.CallANI, 7, 4)
  ELSE SUBSTRING(${TABLE}.CallDNIS, 1, 3) + '-' + SUBSTRING(${TABLE}.CallDNIS, 4, 3) + '-' + SUBSTRING(${TABLE}.CallDNIS, 7, 4)
END ;;
  }

  dimension: five9_session_id {
    sql: ${TABLE}.CallSession ;;
  }

  dimension: type {
    sql: ${TABLE}.CallType ;;
  }

  dimension: original_call_id {
    type: int
    sql: ${TABLE}.OriginalCallID ;;
  }

  dimension: five9_extension {
    type: int
    sql: ${TABLE}.CallExtension ;;
  }

  dimension: hour_of_day {
    type: date_hour_of_day
    sql: ${TABLE}.CallDateTime ;;
  }

  dimension: quarter_of_hour {
    type: int
    sql: ${TABLE}.CallTime15 ;;
  }

  dimension: result {
    sql: ${TABLE}.CallResult ;;
  }

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

  dimension: was_an_order {
    type: yesno
    sql: ${orders.count} > 0 ;;
  }

  dimension: queue_time_tier {
    type: tier
    sql: ${TABLE}.CallDurQueueSec ;;
  }

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

  measure: count {
    type: count_distinct
    sql: ${id} ;;
  }

  measure: greater_than_20_sec_count {
    type: count_distinct
    filters: [talk_time_in_sec: "20 to"]
    sql: ${id} ;;
  }

  measure: answered_count {
    type: count_distinct
    filters: [was_answered: "True"]
    sql: ${id} ;;
  }

  measure: not_answered_count {
    type: number
    sql: ${count}-${answered_count} ;;
  }

  measure: lead_count {
    type: count_distinct
    sql: ${callers_phone_number} ;;
  }

  measure: leads_greater_than_20_sec_count {
    type: count_distinct
    filters: [talk_time_in_sec: "20 to"]
    sql: ${callers_phone_number} ;;
  }

  measure: answered_leads_count {
    type: count_distinct
    filters: [was_answered: "True"]
    sql: ${callers_phone_number} ;;
  }

  measure: opportunities {
    type: sum
    sql: CASE
  WHEN ${result} IN (
    'Mailing In Check Or Money Order',
    'Mailed In Check or Money Order',
    'Not comfortable giving pay info',
    'No method of payment',
    'Not Interested',
    'Product Too Expensive',
    'Sale',
    'Sale with AR',
    'Sale with Dental Protector',
    'Sale with Encore and Synapse',
    'Sale with Health Connection',
    'Sale with Perfect Home',
    'Sale with Synapse',
    'Sale with VIP Plus',
    'Sale with VIP Plus and Dental Protector',
    'Sale with VIP Plus and Health Connection',
    'Sale with VIP Plus and Perfect Home',
    'Scheduled Callback'
  )
  THEN 1
  ELSE 0
END ;;
  }

  measure: answered_percent {
    type: number
    sql: CASE
  WHEN ${count} = 0
  THEN NULL
  ELSE 100.0 * ${answered_count}/${count}
END ;;
  }

  measure: not_answered_percent {
    type: number
    sql: CASE
  WHEN ${count} = 0
  THEN NULL
  ELSE 100.0 * ${not_answered_count}/${count}
END ;;
  }

  measure: answered_leads_percent {
    type: number
    sql: CASE
  WHEN ${lead_count} = 0
  THEN NULL
  ELSE 100.0 * ${answered_leads_count}/${lead_count}
END ;;
  }

  measure: avg_speed_to_answer_in_sec {
    type: avg
    sql: ${TABLE}.CallDurQueueSec ;;
  }

  measure: avg_time_before_hang_up_in_sec {
    type: avg
    sql: ${TABLE}.CallDurQueueSec ;;
  }

  measure: times_on_hold {
    type: sum
    sql: ${TABLE}.CallHolds ;;
  }

  measure: subtotal {
    type: sum
    sql: ${order_details.total_amount} ;;
  }

  measure: shipping_with_rush {
    type: number
    sql: (${shipping_without_rush}+${rush}) ;;
  }

  measure: shipping_without_rush {
    type: sum
    sql: ${orders.shipping_amount} ;;
  }

  measure: rush {
    type: sum
    sql: ${orders.rush_amount} ;;
  }

  measure: tax {
    type: sum
    sql: ${orders.tax_amount} ;;
  }

  measure: total {
    label: "Total Revenue"
    type: number
    sql: (${subtotal}+${shipping_without_rush}+${rush}+${tax}) ;;
  }

  measure: regular_revenue {
    type: sum
    sql: ${order_details.regular_amount} ;;
  }

  measure: continuity_revenue {
    type: sum
    sql: ${order_details.auto_amount} ;;
  }

  measure: club_revenue {
    type: sum
    sql: ${order_details.club_amount} ;;
  }

  measure: upsell_revenue {
    type: sum
    sql: ${order_details.upsell_amount} ;;
  }

  measure: xsell_revenue {
    type: sum
    sql: ${order_details.xsell_amount} ;;
  }

  measure: total_item_qty {
    type: sum
    sql: ${order_details.total_count} ;;
  }

  measure: reg_item_qty {
    type: sum
    sql: ${order_details.regular_count} ;;
  }

  measure: continuity_item_qty {
    type: sum
    sql: ${order_details.auto_count} ;;
  }

  measure: club_item_qty {
    type: sum
    sql: ${order_details.club_count} ;;
  }

  measure: upsell_item_qty {
    type: sum
    sql: ${order_details.upsell_count} ;;
  }

  measure: xsell_item_qty {
    type: sum
    sql: ${order_details.xsell_count} ;;
  }

  measure: club_1_qty {
    type: sum
    sql: ${order_details.club_1_count} ;;
  }

  measure: club_2_qty {
    type: sum
    sql: ${order_details.club_2_count} ;;
  }

  measure: club_3_qty {
    type: sum
    sql: ${order_details.club_3_count} ;;
  }

  measure: club_4_qty {
    type: sum
    sql: ${order_details.club_4_count} ;;
  }

  measure: adult_dvd_qty {
    type: sum
    sql: ${order_details.adult_dvd_count} ;;
  }

  measure: aragon_qty {
    type: sum
    sql: ${order_details.aragon_count} ;;
  }

  measure: feecation_qty {
    type: sum
    sql: ${order_details.feecation_count} ;;
  }

  measure: free_rewards_plus_qty {
    type: sum
    sql: ${order_details.free_rewards_plus_count} ;;
  }

  measure: free_shipping_qty {
    type: sum
    sql: ${order_details.free_shipping_count} ;;
  }

  measure: great_fun_qty {
    type: sum
    sql: ${order_details.great_fun_count} ;;
  }

  measure: myshopping_and_mydiscount_qty {
    type: sum
    sql: ${order_details.myshopping_count} ;;
  }

  measure: playboy_qty {
    type: sum
    sql: ${order_details.playboy_count} ;;
  }

  measure: savings_central_qty {
    type: sum
    sql: ${order_details.savings_central_count} ;;
  }

  measure: magazine_direct_qty {
    type: sum
    sql: ${order_details.mag_direct_count} ;;
  }

  measure: magazine_rewards_qty {
    type: sum
    sql: ${order_details.mag_rewards_count} ;;
  }

  measure: american_savings_pass_qty {
    type: sum
    sql: ${order_details.american_savings_pass_count} ;;
  }

  measure: daily_savings_solutions_qty {
    type: sum
    sql: ${order_details.daily_savings_solutions_count} ;;
  }

  measure: sweep_360_qty {
    type: sum
    sql: ${order_details.sweep_360_count} ;;
  }

  measure: main_offer_hso_and_sso_canada_qty {
    type: sum
    sql: ${order_details.main_offer_hso_and_sso_canada_count} ;;
  }

  measure: main_offer_hso_and_sso_us_qty {
    type: sum
    sql: ${order_details.main_offer_hso_and_sso_us_count} ;;
  }

  measure: backup_offer_msc_and_mdt_aragon_qty {
    type: sum
    sql: ${order_details.backup_offer_msc_and_mdt_aragon_count} ;;
  }

  measure: travel_leisure_qty {
    type: sum
    sql: ${order_details.travel_leisure_count} ;;
  }

  measure: magazine_direct_olb_qty {
    type: sum
    sql: ${order_details.magazine_direct_olb_count} ;;
  }

  measure: fun_source_qty {
    type: sum
    sql: ${order_details.fun_source} ;;
  }

  measure: value_access_qty {
    type: sum
    sql: ${order_details.value_access} ;;
  }

  measure: funsource_and_valueaccess_qty {
    label: "Fun Source and Value Access Qty"
    type: number
    sql: ${fun_source_qty} + ${value_access_qty} ;;
  }

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

  measure: conversion_on_answered_calls {
    type: number
    sql: CASE
  WHEN ${answered_count} = 0
  THEN NULL
  ELSE 100.0 * ${orders.count}/${answered_count}
END ;;
  }

  measure: conversion_on_every_lead {
    type: number
    sql: CASE
  WHEN ${lead_count} = 0
  THEN NULL
  ELSE 100.0 * ${orders.count}/${lead_count}
END ;;
  }

  measure: conversion_on_answered_leads {
    type: number
    sql: CASE
  WHEN ${answered_leads_count} = 0
  THEN NULL
  ELSE 100.0 * ${orders.count}/${answered_leads_count}
END ;;
  }

  measure: continuity_conversion_on_orders {
    type: number
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 100.0 * ${continuity_item_qty}/${orders.count}
END ;;
  }

  measure: club_conversion_on_orders {
    type: number
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 100.0 * ${club_item_qty}/${orders.count}
END ;;
  }

  measure: upsell_conversion_on_orders {
    type: number
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 100.0 * ${upsell_item_qty}/${orders.count}
END ;;
  }

  measure: xsell_conversion_on_orders {
    type: number
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 100.0 * ${xsell_item_qty}/${orders.count}
END ;;
  }

  measure: avg_order {
    label: "Avg Order Value"
    type: number
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 1.00 * ${total}/${orders.count}
END ;;
  }

  measure: revenue_per_answered_call {
    type: number
    sql: CASE
  WHEN ${answered_count} = 0
  THEN NULL
  ELSE 1.00 * ${total}/${answered_count}
END ;;
  }

  measure: orders_per_payroll_hour {
    type: number
    sql: CASE
  WHEN ${payroll_time_in_hours} = 0
  THEN NULL
  ELSE 1.00 * ${orders.count}/${payroll_time_in_hours}
END ;;
  }

  measure: revenue_per_payroll_hour {
    type: number
    sql: CASE
  WHEN ${payroll_time_in_hours} = 0
  THEN NULL
  ELSE 1.00 * ${total}/${payroll_time_in_hours}
END ;;
  }

  measure: talk_time_in_sec {
    type: sum
    sql: ${TABLE}.CallDurAgentSec ;;
  }

  measure: avg_talk_time_in_sec {
    type: avg
    sql: ${TABLE}.CallDurAgentSec ;;
  }

  measure: talk_time_in_min {
    type: number
    sql: SUM(${TABLE}.CallDurAgentSec)/60.0 ;;
  }

  measure: avg_talk_time_in_min {
    type: number
    sql: AVG(${TABLE}.CallDurAgentSec)/60.00 ;;
  }

  measure: talk_time_in_hours {
    type: number
    sql: SUM(${TABLE}.CallDurAgentSec)/3600.00 ;;
  }

  measure: dial_time_in_sec {
    type: sum
    sql: ${TABLE}.CallDurDialSec ;;
  }

  measure: avg_dial_time_in_sec {
    type: avg
    sql: ${TABLE}.CallDurDialSec ;;
  }

  measure: dial_time_in_min {
    type: number
    sql: SUM(${TABLE}.CallDurDialSec)/60.0 ;;
  }

  measure: avg_dial_time_in_min {
    type: number
    sql: AVG(${TABLE}.CallDurDialSec)/60.00 ;;
  }

  measure: dial_time_in_hours {
    type: number
    sql: SUM(${TABLE}.CallDurDialSec)/3600.00 ;;
  }

  measure: payroll_time_in_sec {
    type: sum
    sql: ${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec ;;
  }

  measure: avg_payroll_time_in_sec {
    type: avg
    sql: ${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec ;;
  }

  measure: payroll_time_in_min {
    type: number
    sql: SUM(${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec)/60.0 ;;
  }

  measure: avg_payroll_time_in_min {
    type: number
    sql: AVG(${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec)/60.00 ;;
  }

  measure: payroll_time_in_hours {
    type: number
    sql: SUM(${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec)/3600.00 ;;
  }

  measure: billing_time_in_sec {
    type: sum
    sql: ${TABLE}.CallDurBillSec ;;
  }

  measure: avg_billing_time_in_sec {
    type: avg
    sql: ${TABLE}.CallDurBillSec ;;
  }

  measure: billing_time_in_min {
    type: number
    sql: SUM(${TABLE}.CallDurBillSec)/60.0 ;;
  }

  measure: avg_billing_time_in_min {
    type: number
    sql: AVG(${TABLE}.CallDurBillSec)/60.00 ;;
  }

  measure: billing_time_in_hours {
    type: number
    sql: SUM(${TABLE}.CallDurBillSec)/3600.00 ;;
  }

  measure: queue_time_in_sec {
    type: sum
    sql: ${TABLE}.CallDurQueueSec ;;
  }

  measure: avg_queue_time_in_sec {
    type: avg
    sql: ${TABLE}.CallDurQueueSec ;;
  }

  measure: queue_time_in_min {
    type: number
    sql: SUM(${TABLE}.CallDurQueueSec)/60.0 ;;
  }

  measure: avg_queue_time_in_min {
    type: number
    sql: AVG(${TABLE}.CallDurQueueSec)/60.00 ;;
  }

  measure: hold_time_in_sec {
    type: sum
    sql: ${TABLE}.CallDurHoldSec ;;
  }

  measure: avg_hold_time_in_sec {
    type: avg
    sql: ${TABLE}.CallDurHoldSec ;;
  }

  measure: hold_time_in_min {
    type: number
    sql: SUM(${TABLE}.CallDurHoldSec)/60.0 ;;
  }

  measure: avg_hold_time_in_min {
    type: number
    sql: AVG(${TABLE}.CallDurHoldSec)/60.00 ;;
  }

  measure: acw_time_in_sec {
    type: sum
    sql: ${TABLE}.CallDurWrapSec ;;
  }

  measure: avg_acw_time_in_sec {
    type: avg
    sql: ${TABLE}.CallDurWrapSec ;;
  }

  measure: acw_time_in_min {
    type: number
    sql: SUM(${TABLE}.CallDurWrapSec)/60.0 ;;
  }

  measure: avg_acw_time_in_min {
    type: number
    sql: AVG(${TABLE}.CallDurWrapSec)/60.00 ;;
  }
}
call.view.lookml · 448 lines
source-preserved 
- view: call
  sql_table_name: tblCallData
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.pk_CallID
  - dimension: dialed_phone_number
    sql: SUBSTRING(${TABLE}.CallDNIS, 1, 3) + '-' + SUBSTRING(${TABLE}.CallDNIS, 4, 3) + '-' + SUBSTRING(${TABLE}.CallDNIS, 7, 4)
  - dimension: callers_phone_number
    sql: CASE
  WHEN ${TABLE}.CallType LIKE 'Inbound%'
  THEN SUBSTRING(${TABLE}.CallANI, 1, 3) + '-' + SUBSTRING(${TABLE}.CallANI, 4, 3) + '-' + SUBSTRING(${TABLE}.CallANI, 7, 4)
  ELSE SUBSTRING(${TABLE}.CallDNIS, 1, 3) + '-' + SUBSTRING(${TABLE}.CallDNIS, 4, 3) + '-' + SUBSTRING(${TABLE}.CallDNIS, 7, 4)
END
  - dimension: five9_session_id
    sql: ${TABLE}.CallSession
  - dimension: type
    sql: ${TABLE}.CallType
  - dimension: original_call_id
    type: int
    sql: ${TABLE}.OriginalCallID
  - dimension: five9_extension
    type: int
    sql: ${TABLE}.CallExtension
  - dimension: hour_of_day
    type: date_hour_of_day
    sql: ${TABLE}.CallDateTime
  - dimension: quarter_of_hour
    type: int
    sql: ${TABLE}.CallTime15
  - dimension: result
    sql: ${TABLE}.CallResult
  - dimension: was_answered
    type: yesno
    sql: ${TABLE}.CallAnswered=1
  - dimension: was_an_order
    type: yesno
    sql: ${orders.count} > 0
  - dimension: queue_time_tier
    type: tier
    sql: ${TABLE}.CallDurQueueSec
  - dimension_group: call
    type: time
    timeframes:
    - time
    - hour
    - date
    - week
    - month
    sql: ${TABLE}.CallDateTime
  - measure: count
    type: count_distinct
    sql: ${id}
  - measure: greater_than_20_sec_count
    type: count_distinct
    filters:
    - talk_time_in_sec: 20 to
    sql: ${id}
  - measure: answered_count
    type: count_distinct
    filters:
    - was_answered: True
    sql: ${id}
  - measure: not_answered_count
    type: number
    sql: ${count}-${answered_count}
  - measure: lead_count
    type: count_distinct
    sql: ${callers_phone_number}
  - measure: leads_greater_than_20_sec_count
    type: count_distinct
    filters:
    - talk_time_in_sec: 20 to
    sql: ${callers_phone_number}
  - measure: answered_leads_count
    type: count_distinct
    filters:
    - was_answered: True
    sql: ${callers_phone_number}
  - measure: opportunities
    type: sum
    sql: CASE
  WHEN ${result} IN (
    'Mailing In Check Or Money Order',
    'Mailed In Check or Money Order',
    'Not comfortable giving pay info',
    'No method of payment',
    'Not Interested',
    'Product Too Expensive',
    'Sale',
    'Sale with AR',
    'Sale with Dental Protector',
    'Sale with Encore and Synapse',
    'Sale with Health Connection',
    'Sale with Perfect Home',
    'Sale with Synapse',
    'Sale with VIP Plus',
    'Sale with VIP Plus and Dental Protector',
    'Sale with VIP Plus and Health Connection',
    'Sale with VIP Plus and Perfect Home',
    'Scheduled Callback'
  )
  THEN 1
  ELSE 0
END
  - measure: answered_percent
    type: number
    sql: CASE
  WHEN ${count} = 0
  THEN NULL
  ELSE 100.0 * ${answered_count}/${count}
END
  - measure: not_answered_percent
    type: number
    sql: CASE
  WHEN ${count} = 0
  THEN NULL
  ELSE 100.0 * ${not_answered_count}/${count}
END
  - measure: answered_leads_percent
    type: number
    sql: CASE
  WHEN ${lead_count} = 0
  THEN NULL
  ELSE 100.0 * ${answered_leads_count}/${lead_count}
END
  - measure: avg_speed_to_answer_in_sec
    type: avg
    sql: ${TABLE}.CallDurQueueSec
  - measure: avg_time_before_hang_up_in_sec
    type: avg
    sql: ${TABLE}.CallDurQueueSec
  - measure: times_on_hold
    type: sum
    sql: ${TABLE}.CallHolds
  - measure: subtotal
    type: sum
    sql: ${order_details.total_amount}
  - measure: shipping_with_rush
    type: number
    sql: (${shipping_without_rush}+${rush})
  - measure: shipping_without_rush
    type: sum
    sql: ${orders.shipping_amount}
  - measure: rush
    type: sum
    sql: ${orders.rush_amount}
  - measure: tax
    type: sum
    sql: ${orders.tax_amount}
  - measure: total
    type: number
    label: Total Revenue
    sql: (${subtotal}+${shipping_without_rush}+${rush}+${tax})
  - measure: regular_revenue
    type: sum
    sql: ${order_details.regular_amount}
  - measure: continuity_revenue
    type: sum
    sql: ${order_details.auto_amount}
  - measure: club_revenue
    type: sum
    sql: ${order_details.club_amount}
  - measure: upsell_revenue
    type: sum
    sql: ${order_details.upsell_amount}
  - measure: xsell_revenue
    type: sum
    sql: ${order_details.xsell_amount}
  - measure: total_item_qty
    type: sum
    sql: ${order_details.total_count}
  - measure: reg_item_qty
    type: sum
    sql: ${order_details.regular_count}
  - measure: continuity_item_qty
    type: sum
    sql: ${order_details.auto_count}
  - measure: club_item_qty
    type: sum
    sql: ${order_details.club_count}
  - measure: upsell_item_qty
    type: sum
    sql: ${order_details.upsell_count}
  - measure: xsell_item_qty
    type: sum
    sql: ${order_details.xsell_count}
  - measure: club_1_qty
    type: sum
    sql: ${order_details.club_1_count}
  - measure: club_2_qty
    type: sum
    sql: ${order_details.club_2_count}
  - measure: club_3_qty
    type: sum
    sql: ${order_details.club_3_count}
  - measure: club_4_qty
    type: sum
    sql: ${order_details.club_4_count}
  - measure: adult_dvd_qty
    type: sum
    sql: ${order_details.adult_dvd_count}
  - measure: aragon_qty
    type: sum
    sql: ${order_details.aragon_count}
  - measure: feecation_qty
    type: sum
    sql: ${order_details.feecation_count}
  - measure: free_rewards_plus_qty
    type: sum
    sql: ${order_details.free_rewards_plus_count}
  - measure: free_shipping_qty
    type: sum
    sql: ${order_details.free_shipping_count}
  - measure: great_fun_qty
    type: sum
    sql: ${order_details.great_fun_count}
  - measure: myshopping_and_mydiscount_qty
    type: sum
    sql: ${order_details.myshopping_count}
  - measure: playboy_qty
    type: sum
    sql: ${order_details.playboy_count}
  - measure: savings_central_qty
    type: sum
    sql: ${order_details.savings_central_count}
  - measure: magazine_direct_qty
    type: sum
    sql: ${order_details.mag_direct_count}
  - measure: magazine_rewards_qty
    type: sum
    sql: ${order_details.mag_rewards_count}
  - measure: american_savings_pass_qty
    type: sum
    sql: ${order_details.american_savings_pass_count}
  - measure: daily_savings_solutions_qty
    type: sum
    sql: ${order_details.daily_savings_solutions_count}
  - measure: sweep_360_qty
    type: sum
    sql: ${order_details.sweep_360_count}
  - measure: main_offer_hso_and_sso_canada_qty
    type: sum
    sql: ${order_details.main_offer_hso_and_sso_canada_count}
  - measure: main_offer_hso_and_sso_us_qty
    type: sum
    sql: ${order_details.main_offer_hso_and_sso_us_count}
  - measure: backup_offer_msc_and_mdt_aragon_qty
    type: sum
    sql: ${order_details.backup_offer_msc_and_mdt_aragon_count}
  - measure: travel_leisure_qty
    type: sum
    sql: ${order_details.travel_leisure_count}
  - measure: magazine_direct_olb_qty
    type: sum
    sql: ${order_details.magazine_direct_olb_count}
  - measure: fun_source_qty
    type: sum
    sql: ${order_details.fun_source}
  - measure: value_access_qty
    type: sum
    sql: ${order_details.value_access}
  - measure: funsource_and_valueaccess_qty
    type: number
    label: Fun Source and Value Access Qty
    sql: ${fun_source_qty} + ${value_access_qty}
  - measure: conversion_on_every_call
    type: number
    sql: CASE
  WHEN ${count} = 0
  THEN NULL
  ELSE 100.0 * ${orders.count}/${count}
END
  - measure: conversion_on_answered_calls
    type: number
    sql: CASE
  WHEN ${answered_count} = 0
  THEN NULL
  ELSE 100.0 * ${orders.count}/${answered_count}
END
  - measure: conversion_on_every_lead
    type: number
    sql: CASE
  WHEN ${lead_count} = 0
  THEN NULL
  ELSE 100.0 * ${orders.count}/${lead_count}
END
  - measure: conversion_on_answered_leads
    type: number
    sql: CASE
  WHEN ${answered_leads_count} = 0
  THEN NULL
  ELSE 100.0 * ${orders.count}/${answered_leads_count}
END
  - measure: continuity_conversion_on_orders
    type: number
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 100.0 * ${continuity_item_qty}/${orders.count}
END
  - measure: club_conversion_on_orders
    type: number
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 100.0 * ${club_item_qty}/${orders.count}
END
  - measure: upsell_conversion_on_orders
    type: number
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 100.0 * ${upsell_item_qty}/${orders.count}
END
  - measure: xsell_conversion_on_orders
    type: number
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 100.0 * ${xsell_item_qty}/${orders.count}
END
  - measure: avg_order
    type: number
    label: Avg Order Value
    sql: CASE
  WHEN ${orders.count} = 0
  THEN NULL
  ELSE 1.00 * ${total}/${orders.count}
END
  - measure: revenue_per_answered_call
    type: number
    sql: CASE
  WHEN ${answered_count} = 0
  THEN NULL
  ELSE 1.00 * ${total}/${answered_count}
END
  - measure: orders_per_payroll_hour
    type: number
    sql: CASE
  WHEN ${payroll_time_in_hours} = 0
  THEN NULL
  ELSE 1.00 * ${orders.count}/${payroll_time_in_hours}
END
  - measure: revenue_per_payroll_hour
    type: number
    sql: CASE
  WHEN ${payroll_time_in_hours} = 0
  THEN NULL
  ELSE 1.00 * ${total}/${payroll_time_in_hours}
END
  - measure: talk_time_in_sec
    type: sum
    sql: ${TABLE}.CallDurAgentSec
  - measure: avg_talk_time_in_sec
    type: avg
    sql: ${TABLE}.CallDurAgentSec
  - measure: talk_time_in_min
    type: number
    sql: SUM(${TABLE}.CallDurAgentSec)/60.0
  - measure: avg_talk_time_in_min
    type: number
    sql: AVG(${TABLE}.CallDurAgentSec)/60.00
  - measure: talk_time_in_hours
    type: number
    sql: SUM(${TABLE}.CallDurAgentSec)/3600.00
  - measure: dial_time_in_sec
    type: sum
    sql: ${TABLE}.CallDurDialSec
  - measure: avg_dial_time_in_sec
    type: avg
    sql: ${TABLE}.CallDurDialSec
  - measure: dial_time_in_min
    type: number
    sql: SUM(${TABLE}.CallDurDialSec)/60.0
  - measure: avg_dial_time_in_min
    type: number
    sql: AVG(${TABLE}.CallDurDialSec)/60.00
  - measure: dial_time_in_hours
    type: number
    sql: SUM(${TABLE}.CallDurDialSec)/3600.00
  - measure: payroll_time_in_sec
    type: sum
    sql: ${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec
  - measure: avg_payroll_time_in_sec
    type: avg
    sql: ${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec
  - measure: payroll_time_in_min
    type: number
    sql: SUM(${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec)/60.0
  - measure: avg_payroll_time_in_min
    type: number
    sql: AVG(${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec)/60.00
  - measure: payroll_time_in_hours
    type: number
    sql: SUM(${TABLE}.CallDurAgentSec + ${TABLE}.CallDurDialSec)/3600.00
  - measure: billing_time_in_sec
    type: sum
    sql: ${TABLE}.CallDurBillSec
  - measure: avg_billing_time_in_sec
    type: avg
    sql: ${TABLE}.CallDurBillSec
  - measure: billing_time_in_min
    type: number
    sql: SUM(${TABLE}.CallDurBillSec)/60.0
  - measure: avg_billing_time_in_min
    type: number
    sql: AVG(${TABLE}.CallDurBillSec)/60.00
  - measure: billing_time_in_hours
    type: number
    sql: SUM(${TABLE}.CallDurBillSec)/3600.00
  - measure: queue_time_in_sec
    type: sum
    sql: ${TABLE}.CallDurQueueSec
  - measure: avg_queue_time_in_sec
    type: avg
    sql: ${TABLE}.CallDurQueueSec
  - measure: queue_time_in_min
    type: number
    sql: SUM(${TABLE}.CallDurQueueSec)/60.0
  - measure: avg_queue_time_in_min
    type: number
    sql: AVG(${TABLE}.CallDurQueueSec)/60.00
  - measure: hold_time_in_sec
    type: sum
    sql: ${TABLE}.CallDurHoldSec
  - measure: avg_hold_time_in_sec
    type: avg
    sql: ${TABLE}.CallDurHoldSec
  - measure: hold_time_in_min
    type: number
    sql: SUM(${TABLE}.CallDurHoldSec)/60.0
  - measure: avg_hold_time_in_min
    type: number
    sql: AVG(${TABLE}.CallDurHoldSec)/60.00
  - measure: acw_time_in_sec
    type: sum
    sql: ${TABLE}.CallDurWrapSec
  - measure: avg_acw_time_in_sec
    type: avg
    sql: ${TABLE}.CallDurWrapSec
  - measure: acw_time_in_min
    type: number
    sql: SUM(${TABLE}.CallDurWrapSec)/60.0
  - measure: avg_acw_time_in_min
    type: number
    sql: AVG(${TABLE}.CallDurWrapSec)/60.00

Contents

  • call.view.lkml
  • call.view.lookml