· 9 years ago · Dec 08, 2016, 12:26 PM
1with all_sessions as (
2 select
3 created_at as created_at,
4 session_id,
5 json_get(data,'search_id') as search_id,
6 goal,
7 url,
8 referer
9
10 from mamka.events
11 where
12
13 created_at {{#if yes_no == 'yes'}} AT TIME ZONE ('MSK') {{#endif}} >= {{start_date}}
14 AND created_at {{#if yes_no == 'yes'}} AT TIME ZONE ('MSK') {{#endif}} < {{end_date}}
15
16 AND project_name IN ( 'aviasales_helios','aviasales_www','explosion','aviasales_calendar','aviasales_hot_tickets','aviasales_map','engine_latest_prices', 'm_ru')
17
18 group by created_at, session_id, search_id, goal,url,referer
19),
20
21first_events as (
22 select
23 distinct on (session_id) url, referer, created_at {{#if yes_no == 'yes'}} AT TIME ZONE ('MSK') as created_at {{#endif}}, session_id
24
25 from all_sessions
26 where
27 url IS NOT null
28
29 ORDER BY session_id, created_at
30),
31
32 organic_sessions as (
33 select
34 created_at,
35 session_id,
36 case
37 when (referer LIKE '%//www.google%' OR referer LIKE 'android-app://com.google.android.googlequicksearchbox') then 'google'
38 when referer LIKE '%//yandex.ru%' then 'yandex'
39 end as source
40 from first_events
41 where
42 url LIKE '%'
43 {{#if page_type == 'main_page'}}
44 AND ( url LIKE '%//www.aviasales.ru/' )
45 {{#endif}}
46
47 {{#if page_type == 'routes'}}
48 AND url LIKE '%//www.aviasales.ru/routes%'
49 {{#endif}}
50
51 {{#if page_type == 'city-city'}}
52 AND url LIKE '%//www.aviasales.ru/routes/___/___%'
53 {{#endif}}
54
55 {{#if page_type == 'city-country'}}
56 AND url LIKE '%//www.aviasales.ru/routes/___/__%'
57 AND url NOT LIKE '%//www.aviasales.ru/routes/___/___%'
58 {{#endif}}
59
60 {{#if page_type == 'to_city'}}
61 AND url LIKE '%//www.aviasales.ru/cities/%'
62 {{#endif}}
63
64 {{#if page_type == 'from_city'}}
65 AND ( url LIKE '%//www.aviasales.ru/routes/___' OR
66 url LIKE '%//www.aviasales.ru/routes/___?%')
67 {{#endif}}
68
69 {{#if page_type == 'countries'}}
70 AND url LIKE '%//www.aviasales.ru/countries/%'
71 {{#endif}}
72
73 {{#if page_type == 'airlines'}}
74 AND url LIKE '%//www.aviasales.ru/airlines/%'
75 {{#endif}}
76
77 {{#if page_type == 'offers'}}
78 AND url LIKE '%//www.aviasales.ru/offers%'
79 {{#endif}}
80
81 {{#if page_type == 'mag'}}
82 AND url LIKE '%//www.aviasales.ru/mag%'
83 {{#endif}}
84
85 {{#if page_type == 'airports'}}
86 AND url LIKE '%//www.aviasales.ru/airports%'
87 {{#endif}}
88
89 {{#if page_type == 'flights'}}
90 AND url LIKE '%//www.aviasales.ru/flights/%'
91 {{#endif}}
92
93 GROUP BY 1, 2, 3
94),
95
96
97sessions_with_searches as (
98 select
99 session_id as session_id,
100 search_id
101
102 from all_sessions
103 where
104 goal ='SEARCH_REALLY_STARTED'
105 GROUP BY session_id, search_id
106),
107
108 sessions_with_clicks as (
109 select
110 sws.session_id as session_id,
111 c.yasen_id as click_id
112 from
113 clicks as c
114 left join sessions_with_searches as sws ON (c.search_yasen_uuid = sws.search_id
115 AND c.created_at >={{start_date}}::timestamp - interval '1 day'
116 AND c.created_at < {{end_date}}::timestamp + interval '1 day')
117
118 GROUP BY session_id, click_id
119),
120
121 sessions_with_bookings as (
122 select
123 swc.session_id as session_id
124 from
125 bookings as b
126 left join sessions_with_clicks as swc ON (swc.click_id = b.click_fuzzy_id)
127
128 GROUP BY session_id
129)
130
131 select
132
133 {{#if grouping == 'day'}}
134 date(created_at) as date,
135 {{#endif}}
136 {{#if grouping == 'week'}}
137 date_part('week',created_at) as date,
138 {{#endif}}
139 {{#if grouping == 'month'}}
140 date_part('month',created_at) as date,
141 {{#endif}}
142
143
144 count(distinct os.session_id) as organic_sessions,
145 (count(distinct sws.session_id)*1.0 / nullif( (count(distinct os.session_id) * 1.0 ), 0 ) ) as all_to_s,
146 count(distinct sws.session_id) as sessions_with_search,
147
148 (count(distinct swc.session_id)*1.0 / nullif( (count(distinct sws.session_id) * 1.0), 0 ) ) as s_to_c,
149 count(distinct swc.session_id) as sessions_with_clicks,
150
151 (count(distinct swb.session_id)*1.0 / nullif( (count(distinct swc.session_id)*1.0), 0 ) ) as c_to_b,
152 count(distinct swb.session_id) as session_with_bookings,
153
154 (count(distinct swb.session_id)*1.0/ nullif( (count(distinct os.session_id)*1.0), 0 ) ) as all_to_b
155
156
157
158from organic_sessions as os
159left join sessions_with_searches as sws ON os.session_id = sws.session_id
160left join sessions_with_clicks as swc ON os.session_id = swc.session_id
161left join sessions_with_bookings as swb ON os.session_id = swb.session_id
162
163
164 GROUP BY date
165 ORDER BY date desc