渠道-10086营业厅访问情况日月报表-测试申请单

1、需求编号NG3.0渠道应用模块类别RPT需求说明10086营业厅访问情况日/月报表：以地市与品牌为维度，分析10086热线运营指标数据，为渠道管理人员制定并完善10086电话营业厅运营管理办法提供依据。需求设计1、10086营业厅访问情况日报表： 源表：MID_CCC_CALL_DAILYMID_SUBS_INFO_DAILY 目标表：RPT_CHL_10086_D2、10086营业厅访问情况月报表： 源表： MID_CCC_CALL_DAILYMID_SUBS_INFO_DAILY 目标表：RPT_CHL_10086_M模块调整的对其他模块的影响分析新增报表，对其他模块没有影响。模块测试用例

2、及测试步骤一、10086营业厅访问情况日报表：1、从MID_CCC_CALL_DAILY (客服呼叫日中间表)表中统计呼入总量、呼入总户数，eqpt_type_code设备类型代码的1值表示队列、2值表示人工、3值表示自动、0值表示未知；called_num字段是被叫号码值；Call_Type_Code呼叫类型字段的10表示是用户主叫、20表示用户是被叫。SELECT COALESCE(b.City_ID,a.City_ID),CASE WHEN b.Channel_ID IS NOT NULL THEN b.Channel_ID ELSE 99 END AS Channel_ID_Tmp -

3、他网,CASE WHEN b.cust_brand_id in (11,12,13) THEN 1 -全球通WHEN b.cust_brand_id in (31,32) THEN 2 -动感地带WHEN Channel_ID_Tmp 99 THEN 3 -神州行 ELSE 99 END AS cust_brand_id -他网 ,COUNT(DISTINCT a.INIT_CDR_NUM) AS call_in_quan -呼入总量,COUNT(DISTINCT CASE WHEN b.MSISDN IS NOT NULL THEN a.MSISDN ELSE NULL END) AS ca

4、ll_in_subs_quan -呼入总户数FROM (SELECT City_ID,INIT_CDR_NUM,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date = 20110714 AND eqpt_type_code 0 AND SUBSTR(called_num,1,5) = 10086 AND Call_Type_Code = 10 ) AS a -主叫LEFT JOIN ( SELECT MSISDN,CITY_ID,Cust_Brand_Id,Channel_id FROM RVIEW.VW_MID_SUBS_INFO_D

5、AILY WHERE CAL_date = 20110714 GROUP BY 1 QUALIFY CSUM(1,Innet_Date DESC)=1 ) AS bON a.MSISDN = b.MSISDN GROUP BY 1,2,3 ;结果：呼入总量2308384 、呼入总户数1383378KPI指标呼入总客户数(0A4104)： SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUE WHERE kpi_city = 101 AND kpi_dim1 = 00 AND kpi_id = 0A4104 AND kpi_date = 2011

6、0714 ; 结果：系统呼入总客户数1403817呼入总量没有对应的日KPI值。呼入总户数与KPI指标的系统呼入总客户数(01A4104)相比较说明：KPI(0A4104)的值为1403817，把用户拨打10086和10086拨打用户的数据同时进行统计，设备类型为未知的数据(eqpt_type_code =0)也进行了统计，10086营业厅访问情况日报表中的呼入总户数为用户对10086进行呼叫的且设备类型为队列、人工或是自动呼叫的数据，KPI(0A4104)值比10086营业厅访问情况日报表报表中呼入总户数值大。2、从MID_SUBS_INFO_DAILY(用户信息日中间表)中统计客户到达数，

7、用户状态为在网SUBS_STS_Code IN (10,11,12,13,20,30,60)的用户数。SELECT city_id ,channel_id ,CASE WHEN cust_brand_id in (11,12,13) THEN 1 -全球通WHEN cust_brand_id in (31,32) THEN 2 -动感地带ELSE 3 END AS cust_brand_id -神州行 ,COUNT(subs_id) AS arrive_subs_quanFROM RVIEW.VW_MID_SUBS_INFO_DAILYWHERE CAL_date = 20110714AND

8、SUBS_STS_Code IN (10,11,12,13,20,30,60)GROUP BY 1,2,3 ;结果：22373941与KPI指标相比较：SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUE WHERE kpi_city = 101 AND kpi_dim1 = 00 AND kpi_id = 010001 AND kpi_date = 20110714 ;结果：22373941与kpi指标值相等。3、从MID_CCC_CALL_DAILY (客服呼叫日中间表)表中统计人工服务次数、人工服务客户数，eqpt_type_code设备

9、类型代码的1值表示队列、2值表示人工、3值表示自动、0值表示未知；Call_Type_Code呼叫类型字段的10表示是用户主叫、20表示用户是被叫；(Comm_DUR 0 OR ANS_DUR 0 )表示应答成功量。SELECT COALESCE(b.City_ID,a.City_ID),CASE WHEN b.Channel_ID IS NOT NULL THEN b.Channel_ID ELSE 99 END AS Channel_ID_Tmp,CASE WHEN b.cust_brand_id in (11,12,13) THEN 1 -全球通WHEN b.cust_brand_id

10、in (31,32) THEN 2 -动感地带WHEN Channel_ID_Tmp 99 THEN 3 -神州行 ELSE 99 END AS cust_brand_id ,SUM(a.EQPT_Quan) AS pers_service_quan -人工服务次数,COUNT(DISTINCT a.MSISDN) AS pers_subs_quan -人工服务客户数FROM (SELECT City_ID,EQPT_Quan,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date = 20110714 AND Call_Type_Code

11、 = 10 -主叫 AND EQPT_Type_Code = 2 -人工 AND (Comm_DUR 0 OR ANS_DUR 0 ) AS a LEFT JOIN ( SELECT MSISDN,CITY_ID,Cust_Brand_Id,Channel_id FROM RVIEW.VW_MID_SUBS_INFO_DAILY WHERE CAL_date = 20110714 GROUP BY 1 QUALIFY CSUM(1,Innet_Date DESC)=1 ) AS bON a.MSISDN = b.MSISDNGROUP BY 1,2,3 ; 结果: 人工服务次数166939、人

12、工服务客户数124454 与KPI值比较：SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUEWHERE kpi_city = 101 AND kpi_dim1 = 00AND kpi_id = 0A4105 AND kpi_date = 20110714 ; 结果：请求人工服务客户数140908人工服务次数没有对应的日KPI指标。人工服务客户数与KPI指标的请求人工服务客户数(0A4105)相比较说明：KPI(0A4105)的值为140908，统计用户拨打过10086的次数，即包括未应答的请求量，10086营业厅访问情况日报表中的人工服务客户

13、数对10086进行呼叫的且应答的用户数，KPI(0A4105)值比10086营业厅访问情况日报表报表中人工服务客户数值大。4、从MID_CCC_CALL_DAILY (客服呼叫日中间表)表中统计外呼量、外呼用户数，eqpt_type_code设备类型代码的1值表示队列、2值表示人工、3值表示自动、0值表示未知；Call_Type_Code呼叫类型字段的10表示是用户主叫、20表示用户是被叫。SELECT COALESCE(b.City_ID,a.City_ID),CASE WHEN b.Channel_ID IS NOT NULL THEN b.Channel_ID ELSE 99 END A

14、S Channel_ID_Tmp,CASE WHEN b.cust_brand_id in (11,12,13) THEN 1 -全球通WHEN b.cust_brand_id in (31,32) THEN 2 -动感地带WHEN Channel_ID_Tmp 99 THEN 3 -神州行ELSE 99 END AS cust_brand_id ,COUNT(a.INIT_CDR_NUM) AS call_out_quan -外呼量,COUNT(DISTINCT a.MSISDN) AS call_out_subs_quan -外呼用户数FROM (SELECT City_ID,INIT_C

15、DR_NUM,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date = 20110714 AND EQPT_Type_Code = 2 AND Call_Type_Code = 20 )AS a LEFT JOIN ( SELECT MSISDN,CITY_ID,Cust_Brand_Id,Channel_id FROM RVIEW.VW_MID_SUBS_INFO_DAILY WHERE CAL_date = 20110714 GROUP BY 1 QUALIFY CSUM(1,Innet_Date DESC)=1 ) AS bON a

16、.MSISDN = b.MSISDNGROUP BY 1,2,3 ;结果：外呼量28042 、外呼用户数22878外呼量与前端报表：渠道应用专题分析服务效率- HYPERLINK 9:8080/nmcccimweb/globalForward?app_id=94&module_id=94020105 10086客服热线外呼量分析报表中外呼量指标值相等。外呼用户数在 HYPERLINK 9:8080/nmcccimweb/globalForward?app_id=94&module_id=94020105 10086客服热线外呼量分析报表中没有指标，KPI指标中没有对应的指标。二、10086营业

17、厅访问情况月报表：10086营业厅访问情况日报表：1、从MID_CCC_CALL_DAILY (客服呼叫日中间表)表中统计呼入总量、呼入总户数，eqpt_type_code设备类型代码的1值表示队列、2值表示人工、3值表示自动、0值表示未知；called_num字段是被叫号码值；Call_Type_Code呼叫类型字段的10表示是用户主叫、20表示用户是被叫。SELECT COALESCE(b.City_ID,a.City_ID),CASE WHEN b.Channel_ID IS NOT NULL THEN b.Channel_ID ELSE 99 END AS Channel_ID_Tmp

18、 -他网,CASE WHEN b.cust_brand_id in (11,12,13) THEN 1 -全球通 WHEN b.cust_brand_id in (31,32) THEN 2 -动感地带 WHEN Channel_ID_Tmp 99 THEN 3 -神州行 ELSE 99 END AS cust_brand_id -他网 ,COUNT(DISTINCT a.INIT_CDR_NUM) AS call_in_quan -呼入总量,COUNT(DISTINCT CASE WHEN b.MSISDN IS NOT NULL THEN a.MSISDN ELSE NULL END) A

19、S call_in_subs_quan -呼入总户数FROM (SELECT City_ID,INIT_CDR_NUM,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date between 20110601 and 20110630 AND eqpt_type_code 0 AND SUBSTR(called_num,1,5) = 10086 AND Call_Type_Code = 10 ) AS a -主叫LEFT JOIN ( SELECT MSISDN,CITY_ID,Cust_Brand_Id,Channel_id FROM R

20、VIEW.VW_MID_SUBS_INFO_DAILY WHERE CAL_date = 20110630 GROUP BY 1 QUALIFY CSUM(1,Innet_Date DESC)=1 ) AS bON a.MSISDN = b.MSISDN GROUP BY 1,2,3 ;结果：呼入总量68198388、呼入总户数10794926对应的KPI指标值： SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUE WHERE kpi_city = 101AND kpi_dim1 = 00 AND kpi_id IN (1A4101,1A41

21、04 ) AND kpi_date = 20110600 ;结果：总呼叫量 68198381、系统呼入总客户数11085173呼入总户数与KPI指标的系统呼入总客户数(1A4104)相比较说明：KPI(1A4104)的值为11085173，把用户拨打10086和10086拨打用户的数据同时进行统计，设备类型为未知的数据(eqpt_type_code =0)也进行了统计，10086营业厅访问情况月报表中的呼入总户数为用户对10086进行呼叫的且设备类型为队列、人工或是自动呼叫的数据，KPI(1A4104)值比10086营业厅访问情况月报表报表中呼入总户数值大。2、从MID_SUBS_INFO_D

22、AILY(用户信息日中间表)中统计客户到达数，用户状态为在网SUBS_STS_Code IN (10,11,12,13,20,30,60)的用户数。SELECT city_id ,channel_id ,CASE WHEN cust_brand_id in (11,12,13) THEN 1 -全球通WHEN cust_brand_id in (31,32) THEN 2 -动感地带ELSE 3 END AS cust_brand_id -神州行 ,COUNT(subs_id) AS arrive_subs_quanFROM RVIEW.VW_MID_SUBS_INFO_DAILYWHERE

23、CAL_date = 20110630AND SUBS_STS_Code IN (10,11,12,13,20,30,60)GROUP BY 1,2,3 ;结果：22302613与KPI指标相比较：SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUE WHERE kpi_city = 101AND kpi_dim1 = 00 AND kpi_id = 110001AND kpi_date = 20110600 ;结果：22302613与kpi指标值相等。 3、从MID_CCC_CALL_DAILY (客服呼叫日中间表)表中统计人工服务次数、人工

24、服务客户数，eqpt_type_code设备类型代码的1值表示队列、2值表示人工、3值表示自动、0值表示未知；Call_Type_Code呼叫类型字段的10表示是用户主叫、20表示用户是被叫；(Comm_DUR 0 OR ANS_DUR 0 )表示应答成功量。SELECT COALESCE(b.City_ID,a.City_ID),CASE WHEN b.Channel_ID IS NOT NULL THEN b.Channel_ID ELSE 99 END AS Channel_ID_Tmp,CASE WHEN b.cust_brand_id in (11,12,13) THEN 1 -全球

25、通WHEN b.cust_brand_id in (31,32) THEN 2 -动感地带WHEN Channel_ID_Tmp 99 THEN 3 -神州行 ELSE 99 END AS cust_brand_id ,SUM(a.EQPT_Quan) AS pers_service_quan -人工服务次数,COUNT(DISTINCT a.MSISDN) AS pers_subs_quan -人工服务客户数FROM (SELECT City_ID,EQPT_Quan,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date between

26、 20110601 and 20110630 AND Call_Type_Code = 10 -主叫 AND EQPT_Type_Code = 2 -人工 AND (Comm_DUR 0 OR ANS_DUR 0 ) AS a LEFT JOIN ( SELECT MSISDN,CITY_ID,Cust_Brand_Id,Channel_id FROM RVIEW.VW_MID_SUBS_INFO_DAILY WHERE CAL_date = 20110630 GROUP BY 1 QUALIFY CSUM(1,Innet_Date DESC)=1 ) AS bON a.MSISDN = b.

27、MSISDNGROUP BY 1,2,3 ; 结果: 人工服务次数4890405、人工服务客户数2458918 与KPI值比较：SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUEWHERE kpi_city = 101 AND kpi_dim1 = 00AND kpi_id in(1A4105, 1A4103)AND kpi_date = 20110600 ; 结果：人工服务应答量4734265、请求人工服务客户数2883759人工服务次数与KPI(1A4103)人工服务应答量相比较说明：KPI(1A4103)中只统计本省12个地市的用户，1

28、0086营业厅访问情况月报表中人工服务次数统计本省12个地市以及他网用户，KPI(1A4103)值比10086营业厅访问情况月报表报表中人工服务次数值小。人工服务客户数与KPI指标的请求人工服务客户数(1A4105)相比较说明：KPI(1A4105)的值为，统计用户拨打过10086的次数，即包括未应答的请求量，10086营业厅访问情况月报表中的人工服务客户数对10086进行呼叫的且应答的用户数，KPI(1A4105)值比10086营业厅访问情况月报表报表中人工服务客户数值大。4、从MID_CCC_CALL_DAILY (客服呼叫日中间表)表中统计外呼量、外呼用户数，eqpt_type_code

29、设备类型代码的1值表示队列、2值表示人工、3值表示自动、0值表示未知；Call_Type_Code呼叫类型字段的10表示是用户主叫、20表示用户是被叫。SELECT COALESCE(b.City_ID,a.City_ID),CASE WHEN b.Channel_ID IS NOT NULL THEN b.Channel_ID ELSE 99 END AS Channel_ID_Tmp,CASE WHEN b.cust_brand_id in (11,12,13) THEN 1 -全球通WHEN b.cust_brand_id in (31,32) THEN 2 -动感地带WHEN Channel_ID_Tmp 99 THEN 3 -神州行ELSE 99 END AS cust_brand_id ,COUNT(a.INIT_CDR_NUM) AS call_out_quan -外呼量,COUNT(DISTINCT a.MSISDN) AS ca