如何正确基于日期融合数据源，修正Looker看板每预约呼叫成本计算偏差

需求背景 #

需要在Looker看板中制作每预约呼叫成本图表，计算公式为：
Facebook广告花费金额 / 该广告带来的预约呼叫数量

用到两类数据源：

• Facebook Ads数据：包含Ad Name、Date、Amount Spent字段
• CRM数据：包含Created Date、UTM Content（与Ad Name完全匹配）、DidScheduleCall（1代表成功预约，0代表未预约）字段

当前问题 #

采用Ad Name+Date的内连接方式融合数据后，部分仅有广告花费但无CRM数据的日期，其花费会被标记为null并与对应广告脱离，导致计算时总花费被低估。比如示例中20230603的$10花费未被计入广告My Great Ad的总花费。

解决方案 #

核心是采用**左外连接（Left Outer Join）**以Facebook Ads数据为主表，确保所有广告花费记录被保留，再通过聚合函数处理null值完成计算。

步骤1：配置左外连接 #

• 将Facebook Ads数据集设为左表，CRM数据集设为右表
• 关联条件：Facebook.Ad Name = CRM.UTM Content 且 Facebook.Date = CRM.Created Date
• 此配置下，所有Facebook的花费记录都会被保留，无对应CRM数据的行中，CRM相关字段会显示为null，但Ad Name和Amount Spent不会丢失

步骤2：创建聚合计算字段 #

在Looker中创建以下计算字段：

1. 总广告花费：直接对Facebook的Amount Spent求和，无需处理null（左表数据无缺失）

   sum(${facebook_ads.amount_spent})
   

2. 总预约呼叫数：将无CRM数据的情况视为0个预约，对DidScheduleCall求和

   sum(if(${crm_data.did_schedule_call} is null, 0, ${crm_data.did_schedule_call}))
   

3. 每预约呼叫成本：用总花费除以总预约数，同时处理预约数为0的异常情况

   if(${total_scheduled_calls} = 0, null, ${total_spent} / ${total_scheduled_calls})
   

示例验证 #

左连接后的原始数据（简化）：

聚合后结果：

• 总花费：$30
• 总预约数：3
• 每预约呼叫成本：$10.00

LookML建模示例（可选） #

如果通过LookML定义数据模型，可在视图中配置左连接：

view: facebook_ads {
  # 定义Facebook Ads字段...
  dimension: ad_name {
    type: string
    sql: ${TABLE}.ad_name ;;
  }
  dimension: date {
    type: date
    sql: ${TABLE}.date ;;
  }
  measure: amount_spent {
    type: sum
    sql: ${TABLE}.amount_spent ;;
  }

  join: crm_data {
    type: left_outer
    sql_on: ${facebook_ads.ad_name} = ${crm_data.utm_content} AND ${facebook_ads.date} = ${crm_data.created_date} ;;

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

  measure: total_scheduled_calls {
    type: sum
    sql: if(${crm_data.did_schedule_call} is null, 0, ${crm_data.did_schedule_call}) ;;
  }

  measure: cost_per_scheduled_call {
    type: number
    sql: if(${total_scheduled_calls} = 0, null, ${amount_spent} / ${total_scheduled_calls}) ;;
    value_format: "$0.00"
  }
}

内容的提问来源于stack exchange，提问作者Rob