渠道流量解析流程
2016-06-13 10:55:57 0 举报AI智能生成
解析流程是将复杂的问题或任务分解为更简单、更易理解的步骤的过程。这个过程通常涉及到识别关键活动、确定这些活动的执行顺序，以及确定哪些资源（如时间、人力和物资）需要用于完成每个步骤。解析流程的目的是提高效率和效果，通过消除不必要的步骤或活动，减少错误的可能性，以及提供一种可重复的方法来完成任务。这个过程可以应用于各种领域，包括项目管理、生产流程、服务交付等。
作者其他创作
大纲/内容
1.每个小时的05分会解析/ctr_log/ser1/年/月/日/access_ctr_360kad_com_年-月-日_小时.log日志，然后通过com.log.analysis.visitpath.AnalysisCookieVisitPathDayHour将UV级别的数据存在/result/www/AnalysisCookieVisitPathDayHour/dayhour路径下，保留10天，每天23小时把最终的日志放到天目录
2.通过com.log.hive.visit.ImportPageViewDayHour，将uv级别的日志数据切分成pv级别并覆盖TABLE DW_LOG_PAGE_VIE
W_FDT0 PARTITION (DATE='${datestr}）这个表
3.每个小时都会进行删除操作：delete from dw_log_channel_keyword_fdt0 where data_date>=TO_DATE('2015-12-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and data_date=TO_DATE('2015-12-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and data_date=TO_DATE('2015-12-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and data_date=TO_DATE('2015-12-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and data_date=TO_DATE('2015-12-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and data_date
4.通过DW_LOG_CHANNEL_TRAFFIC_HOUR_FDT0_V2_年-月-日.hql计算渠道流量后导入oracle中的dw_channel_hour_v2_fdt0表，通过DW_LOG_PAGETYPE_TRAFFIC_HOUR_FDT0_年-月-日.hql计算页面类型流量后导入dw_log_pagetype_traffic_fdt0表（APP也一样，渠道关键词也一样）
insert overwrite directory '/result/hive/DW_LOG_CHANNEL_TRAFFIC_HOUR_FDT0'
select lpv.*,case when cha.channel_id is null then 99 else cha.channel_id end
from
(
select CONCAT(datetime,':00:00'),filter_id,site_id,channel1,channel2,channel3,
count(distinct uvuid) as uvnum,
sum(pvnum) as pvnum,
sum(br_uv) as br_uv,
sum(visits_new) as visits_new,
sum(tq_online),
sum(tq_box),
sum(tq_r_box),
sum(fax_call_back),
sum(biz_box),
sum(biz_r_box),
sum(tq_cs_box),
sum(tq_box_wap),
sum(fax_call_back_wap),
sum(my_wl_wap),
sum(page_rx_400_wap),
sum(page_nrx_400_wap),
sum(home_400_wap),
sum(add_to_card_wap),
0,0,0,0,0,0,0,
sum(demand_registration_wap),
sum(add_to_card),
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
sum(demand_registration),
sum(free_button),
sum(demand_reg_pc),
sum(join_now_wap),
sum(action_other)
from
(
select substr(uvbegindate,0,13) as datetime,filter_id,site_id,channel1,channel2,channel3,uvuid,
sum(case when pvoffset>0 and actiontype='view' then 1 else 0 end) as pvnum,
sum(case when totalpv=1 and actiontype='view' then 1 else 0 end) as br_uv,
(case when isnew is null then 0 else isnew end) as visits_new,
sum(case when actiontype='tq_online' then 1 else 0 end) as tq_online,
sum(case when actiontype='tq_box' then 1 else 0 end) as tq_box,
sum(case when actiontype='tq_r_box' then 1 else 0 end) as tq_r_box,
sum(case when actiontype='fax_call_back' then 1 else 0 end) as fax_call_back,
sum(case when actiontype='biz_box' then 1 else 0 end) as biz_box,
sum(case when actiontype='biz_r_box' then 1 else 0 end) as biz_r_box,
sum(case when actiontype='tq_cs_box' then 1 else 0 end) as tq_cs_box,
sum(case when actiontype='tq_box_wap' then 1 else 0 end) as tq_box_wap,
sum(case when actiontype='fax_call_back_wap' then 1 else 0 end) as fax_call_back_wap,
sum(case when actiontype='my_wl_wap' then 1 else 0 end) as my_wl_wap,
sum(case when actiontype='page_rx_400_wap' then 1 else 0 end) as page_rx_400_wap,
sum(case when actiontype='page_nrx_400_wap' then 1 else 0 end) as page_nrx_400_wap,
sum(case when actiontype='home_400_wap' then 1 else 0 end) as home_400_wap,
sum(case when actiontype='add_to_card_wap' then 1 else 0 end) as add_to_card_wap,
sum(case when actiontype='demand_registration_wap' then 1 else 0 end) as demand_registration_wap,
sum(case when actiontype='add_to_card' then 1 else 0 end) as add_to_card,
sum(case when actiontype='demand_registration' then 1 else 0 end) as demand_registration,
sum(case when actiontype='free_button' then 1 else 0 end) as free_button,
sum(case when actiontype='demand_reg_pc' then 1 else 0 end) as demand_reg_pc,
sum(case when actiontype='join_now_wap' then 1 else 0 end) as join_now_wap,
sum(case when actiontype='action_other' then 1 else 0 end) as action_other
from dw_log_page_view_fdt0
where date='2016-06-13'
and site_id in (1,10,20)
and substr(uvbegindate,0,3)='201'
group by substr(uvbegindate,0,13),filter_id,site_id,channel1,channel2,channel3,uvuid,isnew
) as dlp
group by dlp.datetime,dlp.filter_id,dlp.site_id,dlp.channel1,dlp.channel2,dlp.channel3
) lpv
left join dim_channel cha
on lpv.channel1=cha.channel_code
and lpv.channel2=cha.channel_name;
insert overwrite directory '/result/hive/DW_LOG_PAGETYPE_TRAFFIC_HOUR_FDT0'
select case when cha.channel_id is null then 99 else cha.channel_id end,lpv.*
from
(
select CONCAT('2016-06-13',' 00:00:00'),CONCAT(datetime,':00:00'),COALESCE(dlp.filter_id,0),COALESCE(dlp.site_id,0),dlp.channel1,dlp.channel2,
COALESCE(case when dlp.visit_pagetype_id>=0 and dlp.visit_pagetype_id<=999 then dlp.visit_pagetype_id else 0 end,0) as pagetype_id,
COALESCE(case when dlp.visit_pageid is null then 0 else dlp.visit_pageid end,0),
COALESCE(count(distinct uvuid),0) as uv,
COALESCE(sum(pvnum),0) as pvnum,
COALESCE(sum(br_uv),0) as br_uv,
COALESCE(sum(landing_uv),0) as landing_uv,
COALESCE(sum(landing_visits_new),0) as landing_visits_new,
COALESCE(sum(exit_uv),0) as exit_uv,
COALESCE(sum(visittime_pv),0) as visittime_pv,
COALESCE(sum(visittime),0) as visittime,
COALESCE(sum(tq_online),0),
COALESCE(sum(tq_box),0),
COALESCE(sum(tq_r_box),0),
COALESCE(sum(fax_call_back),0),
COALESCE(sum(biz_box),0),
COALESCE(sum(biz_r_box),0),
COALESCE(sum(tq_cs_box),0),
COALESCE(sum(tq_box_wap),0),
COALESCE(sum(fax_call_back_wap),0),
COALESCE(sum(my_wl_wap),0),
COALESCE(sum(page_rx_400_wap),0),
COALESCE(sum(page_nrx_400_wap),0),
COALESCE(sum(home_400_wap),0),
COALESCE(sum(add_to_card_wap),0),
0,0,0,0,0,0,0,
COALESCE(sum(demand_registration_wap),0),
COALESCE(sum(add_to_card),0),
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
COALESCE(sum(demand_registration),0),
COALESCE(sum(free_button),0),
COALESCE(sum(demand_reg_pc),0),
COALESCE(sum(join_now_wap),0),
COALESCE(sum(action_other),0),
dlp.visit_pagekeyword
from
(
select substr(uvbegindate,0,13) as datetime,filter_id,site_id,channel1,channel2,visit_pagekeyword,visit_pagetype_id,visit_pageid,uvuid,
sum(case when pvoffset=1 and actionoffset=0 then 1 else 0 end) as landing_uv,
sum(case when pvoffset=1 and actionoffset=0 and isnew=1 then 1 else 0 end) as landing_visits_new,
sum(case when pvoffset=totalpv and actionoffset=0 then 1 else 0 end) as exit_uv,
sum(case when pvoffset>0 and actiontype='view' then 1 else 0 end) as pvnum,
sum(case when visitlength>0 and actiontype='view' then 1 else 0 end) as visittime_pv,
sum(case when visitlength is null then 0 else visitlength end) as visittime,
(case when isnew is null then 0 else isnew end) as visits_new,
sum(case when totalpv=1 and actiontype='view' then 1 else 0 end) as br_uv,
sum(case when actiontype='tq_online' then 1 else 0 end) as tq_online,
sum(case when actiontype='tq_box' then 1 else 0 end) as tq_box,
sum(case when actiontype='tq_r_box' then 1 else 0 end) as tq_r_box,
sum(case when actiontype='fax_call_back' then 1 else 0 end) as fax_call_back,
sum(case when actiontype='biz_box' then 1 else 0 end) as biz_box,
sum(case when actiontype='biz_r_box' then 1 else 0 end) as biz_r_box,
sum(case when actiontype='tq_cs_box' then 1 else 0 end) as tq_cs_box,
sum(case when actiontype='tq_box_wap' then 1 else 0 end) as tq_box_wap,
sum(case when actiontype='fax_call_back_wap' then 1 else 0 end) as fax_call_back_wap,
sum(case when actiontype='my_wl_wap' then 1 else 0 end) as my_wl_wap,
sum(case when actiontype='page_rx_400_wap' then 1 else 0 end) as page_rx_400_wap,
sum(case when actiontype='page_nrx_400_wap' then 1 else 0 end) as page_nrx_400_wap,
sum(case when actiontype='home_400_wap' then 1 else 0 end) as home_400_wap,
sum(case when actiontype='add_to_card_wap' then 1 else 0 end) as add_to_card_wap,
sum(case when actiontype='demand_registration_wap' then 1 else 0 end) as demand_registration_wap,
sum(case when actiontype='add_to_card' then 1 else 0 end) as add_to_card,
sum(case when actiontype='demand_registration' then 1 else 0 end) as demand_registration,
sum(case when actiontype='free_button' then 1 else 0 end) as free_button,
sum(case when actiontype='demand_reg_pc' then 1 else 0 end) as demand_reg_pc,
sum(case when actiontype='join_now_wap' then 1 else 0 end) as join_now_wap,
sum(case when actiontype='action_other' then 1 else 0 end) as action_other
from dw_log_page_view_fdt0
where date='2016-06-13'
and site_id in (1,10,20)
and substr(uvbegindate,0,3)='201'
group by substr(uvbegindate,0,13),filter_id,site_id,channel1,channel2,uvuid,isnew,visit_pagekeyword,visit_pagetype_id,visit_pageid
) as dlp
group by dlp.datetime,dlp.filter_id,dlp.site_id,dlp.channel1,dlp.channel2,dlp.visit_pagekeyword,dlp.visit_pagetype_id,dlp.visit_pageid
) lpv
left join dim_channel cha
on lpv.channel1=cha.channel_code
and lpv.channel2=cha.channel_name;
5.问题1.抽取主要字段 问题2：流程需要简化 问题3.数据库表设计
0 条评论
下一页
图形选择
思维导图
主题
补充说明
AI生成
提示
关闭后当前内容将不会保存，是否继续？
取消
确定