data.ml
LKML Virtual call center

Lead

Lead model for virtual call center.

inferred

Blocks

lead.view.lkml · 226 lines
inferred 
view: lead {
  sql_table_name: tblLead ;;

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

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

  dimension: first_name {
    sql: ${TABLE}.LeadCRMFName ;;
  }

  dimension: last_name {
    sql: ${TABLE}.LeadCRMLName ;;
  }

  dimension: full_name {
    sql: ${TABLE}.LeadCRMFName + ' ' + ${TABLE}.LeadCRMLName ;;
  }

  dimension: address {
    sql: ${TABLE}.LeadCRMAddr ;;
  }

  dimension: address_2 {
    sql: ${TABLE}.LeadCRMAddr2 ;;
  }

  dimension: city {
    sql: ${TABLE}.LeadCRMCity ;;
  }

  dimension: state {
    sql: ${TABLE}.LeadCRMState ;;
  }

  dimension: zip_code {
    sql: ${TABLE}.LeadCRMZip ;;
  }

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

  dimension: purchase_history {
    sql: ${TABLE}.LeadCRMPurHist ;;
  }

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

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

  dimension: is_wireless {
    type: yesno
    sql: ${TABLE}.LeadIsWireless = 'true' ;;
  }

  dimension: five9_lead_list {
    sql: ${TABLE}.LeadList ;;
  }

  dimension: lead_source {
    sql: ${TABLE}.LeadSource ;;
  }

  dimension: received_hour_of_day {
    type: date_hour_of_day
    sql: ${TABLE}.LeadRcvd ;;
  }

  dimension: received_day_of_week {
    type: date_day_of_week_index
    sql: ${TABLE}.LeadRcvd ;;
  }

  dimension: received_quarter_of_hour {
    type: int
    sql: ${TABLE}.LeadRcvd15 ;;
  }

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

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

  dimension_group: age_in_days {
    type: date
    sql: DATEDIFF(day, ${received_date}, CONVERT(date, CURRENT_TIMESTAMP)) ;;
  }

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

  measure: calls_count {
    type: sum
    sql: ${lead_facts.num_calls} ;;
  }

  measure: opportunity_count {
    type: sum
    sql: ${lead_facts.opportunities} ;;
  }

  measure: not_dialed_count {
    type: sum
    sql: CASE WHEN ${lead_facts.num_calls} IS NULL
THEN 1
ELSE 0
END ;;
  }

  measure: max_attempts_count {
    type: sum
    sql: CASE WHEN (${lead_facts.max_reached} = 1 AND ${lead_facts.completed} = 0)
THEN 1
ELSE 0
END ;;
  }

  measure: completed_count {
    type: sum
    sql: CASE WHEN ${lead_facts.completed} > 0
THEN 1
ELSE 0
END ;;
  }

  measure: expired_count {
    type: sum
    sql: CASE WHEN ${age_in_days} > 4 AND ((${lead_facts.completed} = 0 AND ${lead_facts.max_reached} = 0) OR ${lead_facts.num_calls} IS NULL)
THEN 1
ELSE 0
END ;;
  }

  measure: penetrated_count {
    type: number
    sql: ${completed_count} + ${max_attempts_count} ;;
  }

  measure: orders {
    type: sum
    sql: ${lead_facts.orders} ;;
  }

  measure: total_revenue {
    type: number
    sql: sum(${lead_facts.shiprushtax} + ${item_total.total_amount}) ;;
  }

  measure: penetration_rate {
    type: number
    sql: CASE WHEN ${imported_count} > 0
THEN 100.0 * ${penetrated_count} / ${imported_count}
ELSE NULL
END ;;
  }

  measure: opportunity_rate {
    type: number
    sql: CASE WHEN ${calls_count} > 0
THEN 100.0 * ${opportunity_count} / ${calls_count}
ELSE NULL
END ;;
  }

  measure: conversion_on_opportunities {
    type: number
    sql: CASE WHEN ${opportunity_count} > 0
THEN 100.0 * ${orders} / ${opportunity_count}
ELSE NULL
END ;;
  }

  measure: conversion_on_leads {
    type: number
    sql: CASE WHEN ${imported_count} > 0
THEN 100.0 * ${orders} / ${imported_count}
ELSE NULL
END ;;
  }

  measure: rev_per_opportunity {
    type: number
    sql: CASE WHEN ${opportunity_count} > 0
THEN 1.00 * ${total_revenue} / ${opportunity_count}
ELSE NULL
END ;;
  }

  measure: rev_per_lead {
    type: number
    sql: CASE WHEN ${imported_count} > 0
THEN 1.00 * ${total_revenue} / ${imported_count}
ELSE NULL
END ;;
  }

  measure: average_order_value {
    type: number
    sql: CASE WHEN ${orders} > 0
THEN 1.00 * ${total_revenue} / ${orders}
ELSE NULL
END ;;
  }
}
lead.view.lookml · 155 lines
source-preserved 
- view: lead
  sql_table_name: tblLead
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.pk_LeadID
  - dimension: dialed_phone
    sql: SUBSTRING(${TABLE}.LeadCallANI, 1, 3) + '-' + SUBSTRING(${TABLE}.LeadCallANI, 4, 3) + '-' + SUBSTRING(${TABLE}.LeadCallANI, 7, 4)
  - dimension: first_name
    sql: ${TABLE}.LeadCRMFName
  - dimension: last_name
    sql: ${TABLE}.LeadCRMLName
  - dimension: full_name
    sql: ${TABLE}.LeadCRMFName + ' ' + ${TABLE}.LeadCRMLName
  - dimension: address
    sql: ${TABLE}.LeadCRMAddr
  - dimension: address_2
    sql: ${TABLE}.LeadCRMAddr2
  - dimension: city
    sql: ${TABLE}.LeadCRMCity
  - dimension: state
    sql: ${TABLE}.LeadCRMState
  - dimension: zip_code
    sql: ${TABLE}.LeadCRMZip
  - dimension: email
    sql: ${TABLE}.LeadCRMEmail
  - dimension: purchase_history
    sql: ${TABLE}.LeadCRMPurHist
  - dimension: is_junk
    type: yesno
    sql: ${TABLE}.LeadFlagJunk = 1
  - dimension: is_state_restricted
    type: yesno
    sql: ${TABLE}.LeadFlagRest = 1
  - dimension: is_wireless
    type: yesno
    sql: ${TABLE}.LeadIsWireless = 'true'
  - dimension: five9_lead_list
    sql: ${TABLE}.LeadList
  - dimension: lead_source
    sql: ${TABLE}.LeadSource
  - dimension: received_hour_of_day
    type: date_hour_of_day
    sql: ${TABLE}.LeadRcvd
  - dimension: received_day_of_week
    type: date_day_of_week_index
    sql: ${TABLE}.LeadRcvd
  - dimension: received_quarter_of_hour
    type: int
    sql: ${TABLE}.LeadRcvd15
  - dimension_group: original_callcenter
    type: time
    timeframes:
    - time
    - hour
    - date
    - week
    - month
    sql: ${TABLE}.LeadCallDate
  - dimension_group: received
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.LeadRcvd
  - dimension_group: age_in_days
    type: date
    sql: DATEDIFF(day, ${received_date}, CONVERT(date, CURRENT_TIMESTAMP))
  - measure: imported_count
    type: count_distinct
    sql: ${id}
  - measure: calls_count
    type: sum
    sql: ${lead_facts.num_calls}
  - measure: opportunity_count
    type: sum
    sql: ${lead_facts.opportunities}
  - measure: not_dialed_count
    type: sum
    sql: CASE WHEN ${lead_facts.num_calls} IS NULL
THEN 1
ELSE 0
END
  - measure: max_attempts_count
    type: sum
    sql: CASE WHEN (${lead_facts.max_reached} = 1 AND ${lead_facts.completed} = 0)
THEN 1
ELSE 0
END
  - measure: completed_count
    type: sum
    sql: CASE WHEN ${lead_facts.completed} > 0
THEN 1
ELSE 0
END
  - measure: expired_count
    type: sum
    sql: CASE WHEN ${age_in_days} > 4 AND ((${lead_facts.completed} = 0 AND ${lead_facts.max_reached} = 0) OR ${lead_facts.num_calls} IS NULL)
THEN 1
ELSE 0
END
  - measure: penetrated_count
    type: number
    sql: ${completed_count} + ${max_attempts_count}
  - measure: orders
    type: sum
    sql: ${lead_facts.orders}
  - measure: total_revenue
    type: number
    sql: sum(${lead_facts.shiprushtax} + ${item_total.total_amount})
  - measure: penetration_rate
    type: number
    sql: CASE WHEN ${imported_count} > 0
THEN 100.0 * ${penetrated_count} / ${imported_count}
ELSE NULL
END
  - measure: opportunity_rate
    type: number
    sql: CASE WHEN ${calls_count} > 0
THEN 100.0 * ${opportunity_count} / ${calls_count}
ELSE NULL
END
  - measure: conversion_on_opportunities
    type: number
    sql: CASE WHEN ${opportunity_count} > 0
THEN 100.0 * ${orders} / ${opportunity_count}
ELSE NULL
END
  - measure: conversion_on_leads
    type: number
    sql: CASE WHEN ${imported_count} > 0
THEN 100.0 * ${orders} / ${imported_count}
ELSE NULL
END
  - measure: rev_per_opportunity
    type: number
    sql: CASE WHEN ${opportunity_count} > 0
THEN 1.00 * ${total_revenue} / ${opportunity_count}
ELSE NULL
END
  - measure: rev_per_lead
    type: number
    sql: CASE WHEN ${imported_count} > 0
THEN 1.00 * ${total_revenue} / ${imported_count}
ELSE NULL
END
  - measure: average_order_value
    type: number
    sql: CASE WHEN ${orders} > 0
THEN 1.00 * ${total_revenue} / ${orders}
ELSE NULL
END

Contents

  • lead.view.lkml
  • lead.view.lookml