[sql] clickstream_stg_ing_INCR.hql
Viewer
*** This page was generated with the meta tag "noindex, nofollow". This happened because you selected this option before saving or the system detected it as spam. This means that this page will never get into the search engines and the search bot will not crawl it. There is nothing to worry about, you can still share it with anyone.
- INSERT INTO pimdlkp01_dlkp.ces_tbl_clickstream_stg_ing
- SELECT
- DOMAIN
- , CASE WHEN COALESCE(post_evar1,'')='' THEN
- CASE WHEN COALESCE(evar1,'')='' THEN
- CASE WHEN COALESCE(click_action,'')='' THEN
- CASE WHEN COALESCE(post_pagename,'')='' THEN
- COALESCE(pagename,'')
- ELSE post_pagename
- END
- ELSE click_action
- END
- ELSE evar1
- END
- ELSE post_evar1
- END pagename
- , CASE WHEN SUBSTRING(page_url,LENGTH(COALESCE(post_page_url,'')))=COALESCE(post_page_url,'') THEN
- page_url
- ELSE
- CASE WHEN COALESCE(post_page_url,'')='' THEN
- COALESCE(post_evar128, evar128)
- ELSE
- post_page_url
- END
- END page_url
- , concat_ws('.',post_visid_high,post_visid_low) visitor_id
- , concat_ws('.',post_visid_high,post_visid_low,visit_start_time_gmt,visit_num) visit_id
- , visit_start_pagename
- , CAST(visit_num AS INT) visit_num
- , CAST(visit_page_num AS INT) visit_page_num
- , post_channel channel
- , post_evar2 evar2
- , post_evar4 evar4
- , post_evar9 evar9
- , post_evar10 evar10
- , post_evar11 evar11
- , post_evar12 evar12
- , post_evar13 evar13
- , post_evar14 evar14
- , post_evar64 evar64
- , post_evar68 evar68
- , post_evar69 evar69
- , post_evar70 evar70
- , post_evar71 evar71
- , post_evar72 evar72
- , post_evar73 evar73
- , post_evar74 evar74
- , post_evar75 evar75
- , post_evar76 evar76
- , post_evar77 evar77
- , post_evar89 Video_Name
- , post_evar90 File_Name
- , post_evar91 File_Type
- , post_evar99 evar99
- , post_evar198 evar198
- , post_evar199 evar199
- , post_evar209 evar209
- , post_evar210 evar210
- , post_prop1 prop1
- , paid_search
- , user_agent
- , ref_domain
- , ref_type
- , referrer
- , visit_ref_type
- , post_event_list event_list
- , post_page_event_var2 page_event_var2
- , click_action
- , click_context
- , click_tag
- , browser
- , resolution
- , nonnullvalue(DOMAIN) AS is_pru_network
- , geo_country country
- , geo_city city
- , geo_region region
- -- ------------MCES columns
- , post_visid_high visid_high
- , post_visid_low visid_low
- , date_timestamp
- , new_visit new_visit
- , post_evar67 Form_Name
- , post_evar111 Event_Name
- , post_prop15 Percent_Page_Viewed
- , visit_start_page_url visit_start_page_url
- -- ----- Partition columns
- , date_time AS hit_time_gmt_dt
- , COALESCE(CASE
- WHEN LOWER(post_channel) LIKE '%pgim.com' THEN CASE
- WHEN LOWER(post_evar1) LIKE 'pgim:pgim-investments%' THEN 'Investments'
- WHEN LOWER(post_evar1) LIKE 'pgim:ml%' THEN 'Investments'
- WHEN LOWER(post_evar1) LIKE 'pgim:ucits%' THEN 'UCITS'
- WHEN LOWER(post_evar1) LIKE 'pgim:pgim-fixed-income%' THEN 'Fixed Income'
- --Adding below case functions as requested in REQUEST0009956
- WHEN LOWER(post_evar1) LIKE 'pgim:real-estate%' AND date_time >= '2020-07-20' THEN 'Real Estate' -- DPeters 8/14/20 fix for Drupal migration new URL 7/20 was day urls changed pre launch
- WHEN LOWER(post_evar1) LIKE 'pgim:fixed-income%' AND date_time >= '2020-07-20' THEN 'Fixed Income' -- DPeters 8/14/20 fix for Drupal migration new URL 7/20 was day urls changed pre launch
- WHEN LOWER(post_evar1) LIKE 'pgim:investments%' AND date_time >= '2020-07-20' THEN 'Investments' -- DPeters 8/14/20 fix for Drupal migration new URL
- WHEN LOWER(post_evar1) LIKE 'pgim:personal:investments%' AND date_time >= '2020-07-20' THEN 'Investments' -- DPeters 8/24/20 fix for Drupal migration new URL REQUEST0010039
- WHEN LOWER(post_evar1) LIKE 'pgim:pgim-japan%' AND date_time >= '2020-07-20' THEN 'Japan' -- DPeters 8/14/20 Add New for Drupal migration new URL
- WHEN LOWER(post_evar1) LIKE 'pgim:pcom6%' AND date_time >= '2020-07-20' THEN 'Investments' -- DPeters 10/23/20 fix for new evar1 values
- WHEN LOWER(post_evar1) LIKE 'prucom:real-estate%' AND date_time >= '2020-07-20' THEN 'Real Estate' -- DPeters 10/23/20 fix for new evar1 values
- WHEN post_evar1='pgim:error404' THEN NULL
- ELSE 'Center'
- END
- WHEN LOWER(post_channel) IN ('pgimref.com','www.pgimref.com','pgimrealestatefinance.com','www.pgimrealestatefinance.com') THEN 'Real Estate Finance'
- WHEN LOWER(post_channel)='www.jennison.com' THEN 'Jennison'
- WHEN LOWER(post_channel) IN ('pgimrealestate.com','www.pgimrealestate.com') THEN 'Real Estate'
- WHEN LOWER(post_channel) IN ('pgimfixedincome.com','pgimfixedincome.com') THEN 'Fixed Income'
- WHEN LOWER(post_channel)='www.qma.com' THEN 'PGIM Quant Solutions' -- added on 9/24/21 as request by Dennis/Cris
- WHEN LOWER(post_channel)='www.qmaw.com' THEN 'PGIM Wadhwani' -- added on 9/24/21 as request by Dennis/Cris
- WHEN LOWER(post_channel)='www.pgimwadhwani.com' THEN 'PGIM Wadhwani' -- added on 9/24/21 as request by Dennis/Cris
- WHEN LOWER(post_channel)='www.pgimquantitativesolutions.com' THEN 'PGIM Quant Solutions' -- added on 9/24/21 as request by Dennis/Cris
- WHEN LOWER(post_channel)='www.pricoaprivatecapital.com' THEN 'Pricoa PC' -- added on 9/24/21 as request by Dennis/Cris
- WHEN LOWER(post_channel)='www.prudentialprivatecapital.com' THEN 'Prudential PC' -- added on 9/24/21 as request by Dennis/Cris
- --Modifying QMA when conditions as requested in REQUEST0014314
- WHEN LOWER(post_channel) LIKE '%pgimselect.com' THEN 'PGIM Quant Select' -- 3/29/21
- WHEN LOWER(post_channel) LIKE '%pgimquantselect.com' THEN 'PGIM Quant Select' -- 3/29/21
- WHEN LOWER(post_channel) LIKE '%prudential.com' THEN 'Prudential'
- END,'Unknown') AS Business_Unit
- , exclude_hit
- , runid
- , post_evar67 evar67
- , post_evar146 evar146
- -- Adding column as requested by Umesh in REQUEST0007519
- , mcvisid
- -- Adding column as requested by Cris in REQUEST0015289
- , evar130
- , evar65
- -- Adding column as requested by Cris in REQUEST0015458
- , evar221
- -- Adding column as requested by Cris in REQUEST0019027
- ,evar3
- ,evar102
- -- Adding column as requested by Cris in REQUEST0020452
- ,eVar63
- -- Adding column as requested by Cris on May 25 2022
- ,post_page_event_var2
- ,evar64 link_text
- ,evar67 evar67_new
- ,evar68 form_page
- ,evar69 form_type
- -- Adding column as requested by Cris in REQUEST0023620
- ,evar15 taxonomy
- ,evar124 NewOrRepeat_Visitor
- FROM ucomktgp01_mktdata.clickstream
- WHERE date_time > (SELECT COALESCE(MAX(hit_time_gmt_dt),'') AS max_date FROM pimdlkp01_dlkp.ces_tbl_clickstream_stg_ing);
- -- To process for a AD-HOC dates, modify the above line to have => where date_time > <the date earlier than missing date> => where date_time > '2022-11-25'
Editor
You can edit this paste and save as new: