数仓客服响应率如何计算

最新推荐文章于 2024-10-16 17:27:38 发布

钱多多5566

最新推荐文章于 2024-10-16 17:27:38 发布

阅读量507

点赞数 1

分类专栏：大数据开发

本文链接：https://blog.csdn.net/abcde5566/article/details/124696822

版权

linq c# hive 数据仓库大数据

大数据开发专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1、建立明细表，增加上一条和下一条是否是顾客的字段。

2、建立处理过得波峰波谷明细临时表

-- 已知客户第一次发起的是波峰，规划师响应的是波谷

-- 如果上一条是客户，当前这条是规划师，那么就是波谷,

-- 如果上一条是规划师，当前这条是客户，那么就是波峰，

-- 如果上一条是客户和规划师都是空，然后这条是客户，则是波峰

-- 如果上一条是客户和规划师都是空，然后这条是规划师，则是波谷

-- 如果是客户首发，则以最后一个结束的波谷来算，波谷的数量则是对话轮次 later

-- 如果是规划师首发，则以最后一个结束的波峰来算，波峰的数量则是对话轮次 later

-- 规划师响应时长=波谷-波峰的时间

select

t1.msg_date,

UNIX_TIMESTAMP(t1.msg_time) as msg_timestamp,

case when t1.is_cus_prev_msg is null and t1.is_plnr_prev_msg is null and t1.is_plnr = 1 then '波谷'

when t1.is_cus_prev_msg is null and t1.is_plnr_prev_msg is null and t1.is_customer = 1 then '波峰'

when t1.is_cus_prev_msg = 1 and t1.is_plnr = 1 then '波谷'

when t1.is_plnr_prev_msg= 1 and t1.is_customer = 1 then '波峰'

else '未知' end as feature

from msg_record t1

-- 3、已知客户第一次发起的是波峰，规划师响应的是波谷

-- 可以增加响应时长计算的步骤

-- 如果是客户首发，则以最后一个结束的波谷来算，波谷的数量则是对话轮次 later

-- 如果是规划师首发，则以最后一个结束的波峰来算，波峰的数量则是对话轮次 later

-- 规划师响应时长=波谷-波峰的时间

以下方式计算对话轮次

select
					m2.group_id,
				  m2.feature,
				  m2.msg_date,
				  m2.msg_time,
				  m2.msg_timestamp_prev_msg,
				  m2.effictive_rk,
				  m2.effictive_feature_rk,
case when m2.feature='波谷' and m2.msg_timestamp_prev_msg is not null
				  		      then (m2.msg_timestamp-m2.msg_timestamp_prev_msg)
				  		 else 0  end as effictive_inteval,
				  m2.fst_sender_inclued_sys,
				  case when m2.fst_sender_inclued_sys = '客户' and m2.feature='波谷'
				  		 then 1
				  		 when m2.fst_sender_inclued_sys = '规划师' and m2.feature='波峰'
				  		 then 1
				  		 else 0
				  		end as effective_round
from(
					select
                                                m1.group_id,
                                                m1.feature,
                                                m1.msg_date,
                                                m1.msg_time,
                                                UNIX_TIMESTAMP(m1.msg_time) as msg_timestamp,
                                                lag(m1.msg_timestamp) over(partition by m1.group_id,m1.msg_date order by m1.msg_timestamp asc) msg_timestamp_prev_msg,
                                                lag(m1.feature) over(partition by m1.group_id,m1.msg_date order by m1.msg_time asc) feature_prev_msg,
                                                row_number() over(partition by m1.group_id,m1.msg_date order by m1.msg_time asc) as effictive_rk,
                                                row_number() over(partition by m1.group_id,m1.msg_date,m1.feature  order by m1.msg_time asc) as effictive_feature_rk,
                                                m1.fst_sender_inclued_sys
					from  im_d_tmp2 m1
					where m1.feature in('波峰','波谷')
) m2;

本次轮次是以谁先发来计算的轮次，各个业务可能要求不一样。可以根据具体情况来计算即可。