Untitled
unknown
plain_text
a year ago
1.6 kB
4
Indexable
Never
select toDate(utc_event_time, 'Europe/Moscow') AS event_date_MSC, decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_source')) as utm_source, decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_medium')) as utm_medium, decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_campaign')) as utm_campaign, decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_term')) as utm_term, decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_content')) as utm_content, splitByString('-id-', decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_campaign')))[1] as utm_seller ,splitByChar('|', ecom.affiliation)[1] as wb_supplier ,ecom.nm ,counter_id ,countIf(event_type = 'ec.purchase') as cnt_purch ,countIf(event_type = 'ec.add_to_cart') as cnt_add_to_cart ,countIf(event_type = 'ec.view_item') as cnt_view_item ,sum(curr(ecom.grand_total100, ecom.currency, utc_event_date)) as sum_purch from analytics.events array join ecom where 1=1 and utc_event_date in( toDate('2023-10-15 23:30:00'),toDate('2023-10-16 00:00:00')) and (utc_event_time >='2023-10-15 23:30:00' and utc_event_time <'2023-10-16 00:00:00') --and (utc_event_time >='2023-10-15 14:00:00' and utc_event_time<'2023-10-15 14:30:00') and counter_id in (4,50,1000,100) and event_type in( 'ec.add_to_cart','ec.purchase','ec.view_item') AND match(utm_campaign, '^\d+-id-') group by event_date_MSC, utm_source,utm_medium,utm_campaign,utm_term,utm_content,utm_seller,wb_supplier,ecom.nm, counter_id;