[Home] [Help]
SELECT /* Fix for bug # 4751315 */
-- This select block is to get visit, repeated visits, visit duration, page views,
-- web registrations, web inquiries info from ibw_site_visit_mv for a site and customer.
1 umarker
, site_id site_id
, null header_id -- Added on 7-JUN-2006
, customer_id customer_id
, transaction_date transaction_date
, null currency_cd_f
, sum(visits) visits
, SUM(repeat_visits ) repeat_visits
, sum(visit_duration) visit_duration
, sum(page_views) page_views
, null daily_uniq_visitors
, null new_web_customers
, null new_web_customers_all
, sum(orders_site_visits) orders_site_visits
, sum(web_registrations) web_registrations
, null carts
, null orders
, null repeat_web_orders
, null anonymous_carts
, sum(total_order_inquiries) total_order_inquiries
, sum(total_payment_inquiries) total_payment_inquiries
, sum(total_invoice_inquiries) total_invoice_inquiries
, null a_leads
, null booked_amt_f
, null booked_amt_g
, null booked_amt_g1
, null booked_web_orders
, null assisted_web_orders
, null total_booked_amt_f
, null total_booked_amt_g
, null total_booked_amt_g1
, null total_booked_orders
, sum(opt_outs) opt_outs
, count(visits) d_visits
, count(repeat_visits) d_repeat_visits
, count(visit_duration) d_visit_duration
, count(page_views) d_page_views
, count(orders_site_visits) d_orders_site_visits
, count(web_registrations) d_web_registrations
, null d_carts
, null d_orders
, null d_anonymous_carts
, count(total_order_inquiries) d_total_order_inquiries
, count(total_payment_inquiries) d_total_payment_inquiries
, count(total_invoice_inquiries) d_total_invoice_inquiries
, null d_a_leads
, null d_booked_amt_f
, null d_booked_amt_g
, null d_booked_amt_g1
, null d_total_booked_amt_f
, null d_total_booked_amt_g
, null d_total_booked_amt_g1
, count(opt_outs) d_opt_outs
, count(*) d_total_cnt
from ibw_site_visit_mv
group by
site_id
,customer_id
,transaction_date
UNION ALL
SELECT
-- This select block is to get daily unique visitor count form ibw_vst_cmp_dlyuniqvstr_mv
-- for a given site and party id.
2 umarker
, site_id site_id
, null header_id -- Added on 7-JUN-2006
, customer_id customer_id
, transaction_date transaction_date
, NULL currency_cd_f
, null visits
, null repeat_visits
, null visit_duration
, null page_views
, count(party_visitor_id) daily_uniq_visitors
, null new_web_customers
, null new_web_customers_all
, null orders_site_visits
, null web_registrations
, null carts
, null orders
, null repeat_web_orders
, null anonymous_carts
, null total_order_inquiries
, null total_payment_inquiries
, null total_invoice_inquiries
, null a_leads
, null booked_amt_f
, null booked_amt_g
, null booked_amt_g1
, null booked_web_orders
, null assisted_web_orders
, null total_booked_amt_f
, null total_booked_amt_g
, null total_booked_amt_g1
, null total_booked_orders
, null opt_outs
, null d_visits
, null d_repeat_visits
, null d_visit_duration
, null d_page_views
, null d_orders_site_visits
, null d_web_registrations
, null d_carts
, null d_orders
, null d_anonymous_carts
, null d_total_order_inquiries
, null d_total_payment_inquiries
, null d_total_invoice_inquiries
, null d_a_leads
, null d_booked_amt_f
, null d_booked_amt_g
, null d_booked_amt_g1
, null d_total_booked_amt_f
, null d_total_booked_amt_g
, null d_total_booked_amt_g1
, null d_opt_outs
, count(*) d_total_cnt
FROM ibw_vst_cmp_dlyuniqvstr_mv
GROUP BY site_id,
customer_id,
transaction_date
UNION ALL
SELECT
-- This select block is to get iStore related order amounts, count of assisted web orders
-- from ibe_bi_orders_mv for a given site, customer id, functional currency and booked date.
3 umarker
, minisite_id site_id
, null header_id -- Added on 7-JUN-2006
, customer_id customer_id
, booked_date transaction_date
, currency_cd_f currency_cd_f
, null visits
, null repeat_visits
, null visit_duration
, null page_views
, null daily_uniq_visitors
, null new_web_customers
, null new_web_customers_all
, null orders_site_visits
, null web_registrations
, null carts
, null orders
, null repeat_web_orders
, null anonymous_carts
, null total_order_inquiries
, null total_payment_inquiries
, null total_invoice_inquiries
, null a_leads
, sum(amount_f) booked_amt_f
, sum(amount_g) booked_amt_g
, sum(amount_g1) booked_amt_g1
, count(header_id ) booked_web_orders
, count(decode(resource_flag,'Y',header_id,null)) assisted_web_orders
, null total_booked_amt_f
, null total_booked_amt_g
, null total_booked_amt_g1
, null total_booked_orders
, null opt_outs
, null d_visits
, null d_repeat_visits
, null d_visit_duration
, null d_page_views
, null d_orders_site_visits
, null d_web_registrations
, null d_carts
, null d_orders
, null d_anonymous_carts
, null d_total_order_inquiries
, null d_total_payment_inquiries
, null d_total_invoice_inquiries
, null d_a_leads
, count(amount_f) d_booked_amt_f
, count(amount_g) d_booked_amt_g
, count(amount_g1) d_booked_amt_g1
, null d_total_booked_amt_f
, null d_total_booked_amt_g
, null d_total_booked_amt_g1
, null d_opt_outs
, count(*) d_total_cnt
FROM
ibe_bi_orders_mv
WHERE
published_quote_flag = 'N' -- To exclude published quotes
GROUP BY
minisite_id
,customer_id
,booked_date
,currency_cd_f
UNION ALL
SELECT
-- This is to get total carts, orders, anonymous carts count from ibw_cart_base_mv
-- for a given site id, party id and creation date.
4 umarker
, site_id site_id
, null header_id -- Added on 7-JUN-2006
, customer_id customer_id
, transaction_date transaction_date
, null currency_cd_f
, null visits
, null repeat_visits
, null visit_duration
, null page_views
, null daily_uniq_visitors
, null new_web_customers
, null new_web_customers_all
, null orders_site_visits
, null web_registrations
, sum(carts) carts
, sum(orders) orders
, null repeat_web_orders
, sum(anonymous_carts) anonymous_carts
, null total_order_inquiries
, null total_payment_inquiries
, null total_invoice_inquiries
, sum(lead_rank) a_leads
, null booked_amt_f
, null booked_amt_g
, null booked_amt_g1
, null booked_web_orders
, null assisted_web_orders
, null total_booked_amt_f
, null total_booked_amt_g
, null total_booked_amt_g1
, null total_booked_orders
, null opt_outs
, null d_visits
, null d_repeat_visits
, null d_visit_duration
, null d_page_views
, null d_orders_site_visits
, null d_web_registrations
, count(carts) d_carts
, count(orders) d_orders
, count(anonymous_carts) d_anonymous_carts
, null d_total_order_inquiries
, null d_total_payment_inquiries
, null d_total_invoice_inquiries
, count(lead_rank) d_a_leads
, null d_booked_amt_f
, null d_booked_amt_g
, null d_booked_amt_g1
, null d_total_booked_amt_f
, null d_total_booked_amt_g
, null d_total_booked_amt_g1
, null d_opt_outs
, count(*) d_total_cnt
FROM
ibw_cart_base_mv a
GROUP BY
site_id
,customer_id
,transaction_date
UNION ALL
SELECT
-- This select block is to get unassigned site records from isc_book_sum2_f
-- for a customer id, functional currency and booked date.
-- We are getting denominator(order amounts from all channels) for percent web orders
5 umarker
, -9999 site_id
, header_id header_id -- Added this on 7-JUN-2006.
, customer_id customer_id
, booked_date transaction_date
, currency_func_id currency_cd_f
, null visits
, null repeat_visits
, null visit_duration
, null page_views
, null daily_uniq_visitors
, null new_web_customers
, null new_web_customers_all
, null orders_site_visits
, null web_registrations
, null carts
, null orders
, null repeat_web_orders
, null anonymous_carts
, null total_order_inquiries
, null total_payment_inquiries
, null total_invoice_inquiries
, null a_leads
, null booked_amt_f
, null booked_amt_g
, null booked_amt_g1
, null booked_web_orders
, null assisted_web_orders
, SUM(NVL(book.booked_amt_f, null)) total_booked_amt_f
, SUM(NVL(book.booked_amt_g, null)) total_booked_amt_g
, SUM(NVL(book.booked_amt_g1, null)) total_booked_amt_g1
, 1 total_booked_orders -- changed count(book.header_id) to 1 to get
-- total booked orders. count(book.header_id) would give the total order lines
, null opt_outs
, null d_visits
, null d_repeat_visits
, null d_visit_duration
, null d_page_views
, null d_orders_site_visits
, null d_web_registrations
, null d_carts
, null d_orders
, null d_anonymous_carts
, null d_total_order_inquiries
, null d_total_payment_inquiries
, null d_total_invoice_inquiries
, null d_a_leads
, null d_booked_amt_f
, null d_booked_amt_g
, null d_booked_amt_g1
, count(NVL(book.booked_amt_f, null)) d_total_booked_amt_f
, count(NVL(book.booked_amt_g, null)) d_total_booked_amt_g
, count(NVL(book.booked_amt_g1,null)) d_total_booked_amt_g1
, null d_opt_outs
, count(*) d_total_cnt
FROM
ISC.ISC_BOOK_SUM2_F book
WHERE
book.order_category_id IN ('ORDER')
AND book.line_category_code = 'ORDER'
AND book.flow_status_code <> 'CANCELLED'
AND book.charge_periodicity_code IS NULL -- Added as per Bug # 4589076 to remove recurring charge lines
GROUP BY
-9999
, header_id -- Added this on 7-JUN-2006. This was included to count orders in same day by same customers.
, customer_id
, booked_date
, currency_func_id
UNION ALL
SELECT
-- This select block is to get New Web Customers for Customer Acquisition Report
-- This select block queries IBE_BI_CUST_MV and IBE_BI_ORDERS_MV to get the customers who
-- have placed first orders through the Web channel.
6 umarker
,minisite_id site_id
, null header_id -- Added on 7-JUN-2006
,cust.customer_id customer_id
,orders.booked_date transaction_date
, null currency_cd_f
, null visits
, null repeat_visits
, null visit_duration
, null page_views
, null daily_uniq_visitors
, 1 new_web_customers
, null new_web_customers_all
, null orders_site_visits
, null web_registrations
, null carts
, null orders
, null repeat_web_orders
, null anonymous_carts
, null total_order_inquiries
, null total_payment_inquiries
, null total_invoice_inquiries
, null a_leads
, null booked_amt_f
, null booked_amt_g
, null booked_amt_g1
, null booked_web_orders
, null assisted_web_orders
, null total_booked_amt_f
, null total_booked_amt_g
, null total_booked_amt_g1
, null total_booked_orders
, null opt_outs
, null d_visits
, null d_repeat_visits
, null d_visit_duration
, null d_page_views
, null d_orders_site_visits
, null d_web_registrations
, null d_carts
, null d_orders
, null d_anonymous_carts
, null d_total_order_inquiries
, null d_total_payment_inquiries
, null d_total_invoice_inquiries
, null d_a_leads
, null d_booked_amt_f
, null d_booked_amt_g
, null d_booked_amt_g1
, null d_total_booked_amt_f
, null d_total_booked_amt_g
, null d_total_booked_amt_g1
, null d_opt_outs
, count(*) d_total_cnt
FROM
IBE_BI_CUST_MV cust
,IBE_BI_ORDERS_MV orders
WHERE
cust.customer_id = orders.customer_id AND
cust.booked_date = orders.booked_date AND
orders.published_quote_flag = 'N'
GROUP BY
minisite_id
, cust.customer_id
, orders.booked_date
union all
SELECT
-- This select block is to get repeat web orders metric for Vsistor Conversion Report
-- This select block self joins IBE_BI_ORDERS_MV to get the customers who
-- have placed repeat orders the Web channel.
7 umarker
,orders2.minisite_id site_id
, null header_id -- Added on 7-JUN-2006
,orders2.customer_id customer_id
,orders2.booked_date transaction_date
, null currency_cd_f
, null visits
, null repeat_visits
, null visit_duration
, null page_views
, null daily_uniq_visitors
, null new_web_customers
, null new_web_customers_all
, null orders_site_visits
, null web_registrations
, null carts
, null orders
, 1 repeat_web_orders -- This is to count the repeat web orders.
, null anonymous_carts
, null total_order_inquiries
, null total_payment_inquiries
, null total_invoice_inquiries
, null a_leads
, null booked_amt_f
, null booked_amt_g
, null booked_amt_g1
, null booked_web_orders
, null assisted_web_orders
, null total_booked_amt_f
, null total_booked_amt_g
, null total_booked_amt_g1
, null total_booked_orders
, null opt_outs
, null d_visits
, null d_repeat_visits
, null d_visit_duration
, null d_page_views
, null d_orders_site_visits
, null d_web_registrations
, null d_carts
, null d_orders
, null d_anonymous_carts
, null d_total_order_inquiries
, null d_total_payment_inquiries
, null d_total_invoice_inquiries
, null d_a_leads
, null d_booked_amt_f
, null d_booked_amt_g
, null d_booked_amt_g1
, null d_total_booked_amt_f
, null d_total_booked_amt_g
, null d_total_booked_amt_g1
, null d_opt_outs
, count(*) d_total_cnt
FROM
IBE_BI_ORDERS_MV orders1
,IBE_BI_ORDERS_MV orders2
WHERE
orders1.customer_id = orders2.customer_id AND
orders1.booked_date < orders2.booked_date AND
orders2.published_quote_flag = 'N'
GROUP BY
orders2.minisite_id
, orders2.customer_id
, orders2.booked_date
UNION ALL
SELECT
-- This select block is to get new web customers through all channels to calculate Percent New Web Custometrs
-- metric for Customer Acquisition Report. This select block queries IBE_BI_CUST_MV to get the customers
-- who have placed their first order through any channel
8 umarker
, -9999 site_id
, null header_id -- Added on 7-JUN-2006
, cust.customer_id customer_id
, cust.booked_date transaction_date
, null currency_cd_f
, null visits
, null repeat_visits
, null visit_duration
, null page_views
, null daily_uniq_visitors
, null new_web_customers
, 1 new_web_customers_all
, null orders_site_visits
, null web_registrations
, null carts
, null orders
, null repeat_web_orders
, null anonymous_carts
, null total_order_inquiries
, null total_payment_inquiries
, null total_invoice_inquiries
, null a_leads
, null booked_amt_f
, null booked_amt_g
, null booked_amt_g1
, null booked_web_orders
, null assisted_web_orders
, null total_booked_amt_f
, null total_booked_amt_g
, null total_booked_amt_g1
, null total_booked_orders
, null opt_outs
, null d_visits
, null d_repeat_visits
, null d_visit_duration
, null d_page_views
, null d_orders_site_visits
, null d_web_registrations
, null d_carts
, null d_orders
, null d_anonymous_carts
, null d_total_order_inquiries
, null d_total_payment_inquiries
, null d_total_invoice_inquiries
, null d_a_leads
, null d_booked_amt_f
, null d_booked_amt_g
, null d_booked_amt_g1
, null d_total_booked_amt_f
, null d_total_booked_amt_g
, null d_total_booked_amt_g1
, null d_opt_outs
, count(*) d_total_cnt
from
ibe_bi_cust_mv cust
group by
cust.customer_id
,cust.booked_date
,-9999
|
|
|
|