[Home] [Help]
MATERIALIZED VIEW: APPS.IBW_CMPANALYSIS_MV
Source
SELECT 1 umarker /* Fix for bug # 4751315 */
,site_id site_id
,transaction_date transaction_date
,null currency_cd_f
,campaign_source_code_id campaign_source_code_id
,sum(visits) visits -- Number of Visits
,sum(page_views) page_views -- for avg page views (Numerator)
,sum(visit_duration) visit_duration -- for avg visit duration (Numerator)
,sum(web_registrations) web_registrations -- Web Registrations
,null daily_uniq_visitors
,null a_leads
,null carts
,null orders
,null booked_amt_f
,null booked_amt_g
,null booked_amt_g1
,count(visits) d_visits
,count(page_views) d_page_views
,count(visit_duration) d_visit_duration
,count(web_registrations) d_web_registrations
,null d_lead_rank
,null d_carts
,null d_orders
,null d_booked_amt_f
,null d_booked_amt_g
,null d_booked_amt_g1
,count(*) d_total_cnt
FROM
ibw_site_visit_mv
WHERE
campaign_source_code_id is not null
GROUP BY
site_id,
campaign_source_code_id,
transaction_date
UNION ALL
--###### Daily Unique Visitors
SELECT 2 umarker
, site_id site_id
, transaction_date transaction_date
, null currency_cd_f
, campaign_source_code_id campaign_source_code_id
, null visits
, null page_views
, null visit_duration
, null web_registrations
, count(party_visitor_id) daily_uniq_visitors --Daily Unique Visitors
, null a_leads
, null carts
, null orders
, null booked_amt_f
, null booked_amt_g
, null booked_amt_g1
, null d_visits
, null d_page_views
, null d_visit_duration
, null d_web_registrations
, null d_lead_rank
, null d_carts
, null d_orders
, null d_booked_amt_f
, null d_booked_amt_g
, null d_booked_amt_g1
, count(*) d_total_cnt
FROM
ibw_vst_cmp_dlyuniqvstr_mv
WHERE
campaign_source_code_id is not null
GROUP BY
site_id
, campaign_source_code_id
, transaction_date
UNION ALL
--###### A Leads, Carts and Orders(Cart Conversion Metric)
SELECT 3 umarker
,site_id site_id
,transaction_date transaction_date
,null currency_cd_f
,marketing_source_code_id_hdr campaign_source_code_id
,null visits
,null page_views
,null visit_duration
,null web_registrations
,null daily_uniq_visitors
,SUM(lead_rank) a_leads --A Leads
,SUM(carts) carts --Carts
,SUM(ORDERS) orders --Orders
,null booked_amt_f
,null booked_amt_g
,null booked_amt_g1
,null d_visits
,null d_page_views
,null d_visit_duration
,null d_web_registrations
,COUNT(lead_rank) d_lead_rank
,COUNT(carts) d_carts
,COUNT(orders) d_orders
,null d_booked_amt_f
,null d_booked_amt_g
,null d_booked_amt_g1
,COUNT(*) d_total_cnt
FROM
ibw_cart_base_mv
WHERE
marketing_source_code_id_hdr is not null
GROUP BY
site_id,
transaction_date,
marketing_source_code_id_hdr
--###### Booked Orders Amount
UNION ALL
SELECT
4 umarker
,minisite_id site_id
,time_booked_date_id transaction_date --Bug#:4654866 Issue#14. Changed booked_date to time_booked_date_id
,currency_cd_f currency_cd_f
,marketing_source_code_id campaign_source_code_id
,null visits
,null page_views
,null visit_duration
,null web_registrations
,null daily_uniq_visitors
,null a_leads
,null carts
,null orders
,SUM(amount_f) booked_amt_f --Booked Orders Amount in Functional currency
,SUM(amount_g) booked_amt_g --Booked Orders Amount in Global Primary currency
,SUM(amount_g1) booked_amt_g1 --Booked Orders Amount in Global Secondary currency
,null d_visits
,null d_page_views
,null d_visit_duration
,null d_web_registrations
,null d_lead_rank
,null d_carts
,null d_orders
,COUNT(amount_f) d_booked_amt_f
,COUNT(amount_g) d_booked_amt_g
,COUNT(amount_g1) d_booked_amt_g1
,COUNT(*) d_total_cnt
FROM
ibe_bi_orders_mv
WHERE
marketing_source_code_id is not null
and published_quote_flag = 'N' --Added this condition to exclude Published Quotes
GROUP BY
minisite_id,
time_booked_date_id,
currency_cd_f,
marketing_source_code_id