Y1dDfHau

1SET @start_date = DATE_SUB(CURDATE(), INTERVAL 10 DAY);
2SET @end_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
3
4DROP TEMPORARY TABLE IF EXISTS
5	tmp_junior_sales_managers;
6
7CREATE TEMPORARY TABLE
8	tmp_junior_sales_managers
9		(KEY (user_id, `date`))
10SELECT
11	umd.`date`,
12	umd.user_id,
13	IFNULL(u.name_eng, u.nickname) AS junior_sales_manager
14FROM
15	rabota_db.npm_user_manager_by_date umd
16		JOIN rabota_db.`user` u
17			ON u.user_id = umd.manager_id 
18WHERE
19	umd.`type` = 'junior_sales_manager'
20	AND umd.`date` BETWEEN DATE(@start_date) AND DATE(@end_date);
21
22DROP TEMPORARY TABLE IF EXISTS
23	tmp_sales_managers;
24
25CREATE TEMPORARY TABLE
26	tmp_sales_managers
27		(KEY (user_id, `date`))
28SELECT
29	umd.`date`,
30	umd.user_id,
31	IFNULL(u.name_eng, u.nickname) AS sales_manager
32FROM
33	rabota_db.npm_user_manager_by_date umd
34		JOIN rabota_db.`user` u
35			ON u.user_id = umd.manager_id 
36WHERE
37	umd.`type` = 'sales_manager'
38	AND umd.`date` BETWEEN DATE(@start_date) AND DATE(@end_date);
39
40DROP TEMPORARY TABLE IF EXISTS
41	tmp_account_managers;
42
43CREATE TEMPORARY TABLE
44	tmp_account_managers
45		(KEY (user_id, `date`))
46SELECT
47	umd.`date`,
48	umd.user_id,
49	IFNULL(u.name_eng, u.nickname) AS account_manager
50FROM
51	rabota_db.npm_user_manager_by_date umd
52		JOIN rabota_db.`user` u
53			ON u.user_id = umd.manager_id
54WHERE
55	umd.`type` = 'account_manager'
56	AND umd.`date` BETWEEN DATE(@start_date) AND DATE(@end_date);
57
58DROP TEMPORARY TABLE IF EXISTS tmp_user_country;
59CREATE TEMPORARY TABLE tmp_user_country (KEY(`user_id`))
60SELECT u.user_id, ccr.`country_short_name` AS user_country
61FROM rabota_db.`user` u
62LEFT JOIN tablo.`country_commercial_region` ccr
63ON u.`country` = ccr.`country_code`;
64
65DROP TEMPORARY TABLE IF EXISTS tmp_adserver_unfilled;
66CREATE TEMPORARY TABLE tmp_adserver_unfilled (KEY (`date`, sa_site_id, demand_id))
67SELECT
68  a.`date`,
69  b.sa_site_id,
70  a.network_id,
71  SUM(
72    a.ad_server_unfilled_impressions
73  ) AS unfilled_impressions,
74  (
75    CASE
76      WHEN network_id = 45470634
77      THEN 64
78      WHEN network_id = 21779055067
79      THEN 85
80    END
81  ) AS demand_id
82FROM
83  tablo.gam_ad_server_data a
84  JOIN rabota_db.site_area_design_24 b
85    ON a.ad_unit_id = b.dfp_adunit_id
86WHERE a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
87GROUP BY a.`date`,
88  b.sa_site_id,
89  a.network_id;
90
91DROP TEMPORARY TABLE IF EXISTS
92	tmp_demand_stat;
93
94CREATE TEMPORARY TABLE
95	tmp_demand_stat
96		(KEY (`date`))
97SELECT
98	sc.`date`,
99	sc.user_id,
100	sc.site_id,
101	IFNULL(s.url_domain, s.url) AS url,
102	IFNULL(sm.sales_manager, 'None') AS sales_manager,
103	IFNULL(am.account_manager, 'None') AS account_manager,
104	IFNULL(jsm.junior_sales_manager, 'None') AS junior_sales_manager,
105	sc.adv_net_id AS demand_id,
106	SUM(sc.external_view_count) AS impressions,
107	SUM(sc.external_first_view_count) AS first_impressions,
108	SUM(sc.external_viewability_measured_impressions) AS viewability_measured,
109	SUM(sc.external_viewability_viewed_impressions) AS viewability_viewed,
110	SUM(sc.adv_expense_gbp) AS adv_expense_gbp,
111	SUM(sc.partner_gain_gbp) AS partner_gain_gbp,
112	SUM(sc.adv_expense_base) AS adv_expense_rub,
113	SUM(sc.partner_gain_base) AS partner_gain_rub,
114	SUM(sc.hit_count) AS clicks,
115	SUM(sc.external_first_request_count) AS requests,
116	unf.unfilled_impressions AS unfilled_impressions,
117	uc.user_country
118FROM
119	rabota_db.npm_site_area_stat_cache sc
120		JOIN rabota_db.`site` s
121			ON s.site_id = sc.site_id
122		LEFT JOIN tmp_sales_managers sm
123			ON sm.user_id = sc.user_id
124			AND sm.`date` = sc.`date`
125		LEFT JOIN tmp_account_managers am
126			ON am.user_id = sc.user_id
127			AND am.`date` = sc.`date`
128		LEFT JOIN tmp_junior_sales_managers jsm
129			ON jsm.user_id = sc.user_id
130			AND jsm.date = sc.date
131		LEFT JOIN tmp_user_country uc
132			ON uc.user_id = sc.user_id
133		LEFT JOIN tmp_adserver_unfilled unf
134			ON sc.date = unf.date
135			AND sc.site_id = unf.sa_site_id
136			AND sc.adv_net_id = unf.demand_id
137WHERE
138	sc.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
139GROUP BY
140	sc.`date`,
141	sc.site_id,
142	sc.adv_net_id
143HAVING
144	SUM(sc.adv_expense) > 0;
145
146DROP TEMPORARY TABLE IF EXISTS
147	tmp_usd_rate;
148
149CREATE TEMPORARY TABLE
150	tmp_usd_rate
151		(KEY (`date`))
152SELECT
153	e.`date`,
154	AVG(e.exrate) AS exrate
155FROM
156	rabota_db.exchange_rate e
157WHERE
158	e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
159	AND e.source_cur_id = 6
160	AND e.destination_cur_id = 2
161GROUP BY
162	e.`date`;
163
164DROP TEMPORARY TABLE IF EXISTS
165	tmp_eur_rate;
166
167CREATE TEMPORARY TABLE
168	tmp_eur_rate
169		(KEY (`date`))
170SELECT
171	e.`date`,
172	AVG(e.exrate) AS exrate
173FROM
174	rabota_db.exchange_rate e
175WHERE
176	e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
177	AND e.source_cur_id = 6
178	AND e.destination_cur_id = 4
179GROUP BY
180	e.`date`;
181
182DROP TEMPORARY TABLE IF EXISTS tmp_rub_rate;
183CREATE TEMPORARY TABLE tmp_rub_rate (KEY (`date`))
184SELECT
185e.`date`,
186AVG(e.exrate) AS exrate
187FROM rabota_db.exchange_rate e
188WHERE e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
189AND e.source_cur_id = 6
190AND e.destination_cur_id = 1
191GROUP BY e.`date`;
192
193DROP TEMPORARY TABLE IF EXISTS
194	tmp_site_create_date;
195
196CREATE TEMPORARY TABLE
197	tmp_site_create_date
198		(KEY (site_id))
199SELECT
200	sc.site_id,
201	MIN(sc.`date`) AS site_create_date
202FROM
203	tablo.activation_dates sc
204GROUP BY
205	sc.site_id;
206
207DROP TEMPORARY TABLE IF EXISTS
208	tmp_user_create_date;
209
210CREATE TEMPORARY TABLE
211	tmp_user_create_date
212		(KEY (user_id))
213SELECT
214	sc.user_id,
215	MIN(sc.`date`) AS user_create_date
216FROM
217	tablo.activation_dates sc
218GROUP BY
219	sc.user_id;
220
221#user_segment
222DROP TEMPORARY TABLE IF EXISTS bob_date_pub;
223CREATE TEMPORARY TABLE bob_date_pub
224SELECT
225  `date`,
226  IFNULL(u.`moved_to_user_id`,r.`publisher_id`) AS publisher_id,
227  SUM(r.`adv_expense_gbp`) AS adv_expense_gbp
228FROM
229  bi.book_of_business_report r
230  JOIN rabota_db.`user` u
231    ON (r.`publisher_id` = u.`user_id`)
232GROUP BY IFNULL(u.`moved_to_user_id`,r.`publisher_id`),
233  `date`
234HAVING SUM(r.`adv_expense_gbp`)>1;
235
236#summary for each publisher, no date filtering
237DROP TEMPORARY TABLE IF EXISTS bob_pub_summary; 
238CREATE TEMPORARY TABLE bob_pub_summary (KEY (publisher_id))
239SELECT
240  r.publisher_id, 
241  CASE 
242   WHEN AVG(`adv_expense_gbp`)>=0 AND AVG(`adv_expense_gbp`)<50 THEN '[1] <50'  
243   WHEN AVG(`adv_expense_gbp`)>=50 AND AVG(`adv_expense_gbp`)<500 THEN '[2] 50-500'
244   WHEN AVG(`adv_expense_gbp`)>=500 AND AVG(`adv_expense_gbp`)<1000 THEN '[3] 500-1000'
245   WHEN AVG(`adv_expense_gbp`)>=1000 AND AVG(`adv_expense_gbp`)<5000 THEN '[4] 1000-5000'
246   WHEN AVG(`adv_expense_gbp`)>=5000 AND AVG(`adv_expense_gbp`)<10000 THEN '[5] 5000-10000'
247   WHEN AVG(`adv_expense_gbp`)>=10000 THEN '[6] 10000+'
248   ELSE 'error'
249  END AS revenue_segment
250FROM
251  bob_date_pub r
252  JOIN rabota_db.`user` u
253    ON (r.`publisher_id` = u.`user_id`)
254  WHERE r.`adv_expense_gbp`>1 AND u.`billing_source`!='nobilling' AND r.`publisher_id`>0
255GROUP BY r.`publisher_id`;
256
257#site_segment
258DROP TEMPORARY TABLE IF EXISTS bob_date_site;
259CREATE TEMPORARY TABLE bob_date_site
260SELECT
261  `date`,
262  site_id,
263  IFNULL(u.`moved_to_user_id`,r.`publisher_id`) AS publisher_id,
264  SUM(r.`adv_expense_gbp`) AS adv_expense_gbp
265FROM
266  bi.book_of_business_report r
267  JOIN rabota_db.`user` u
268    ON (r.`publisher_id` = u.`user_id`)
269GROUP BY IFNULL(u.`moved_to_user_id`,r.`publisher_id`),
270  `date`,
271  site_id
272HAVING SUM(r.`adv_expense_gbp`)>1;
273
274#summary for each publisher, no date filtering
275DROP TEMPORARY TABLE IF EXISTS bob_site_summary; 
276CREATE TEMPORARY TABLE bob_site_summary (KEY (site_id))
277SELECT
278  r.site_id, 
279  CASE 
280   WHEN AVG(`adv_expense_gbp`)>=0 AND AVG(`adv_expense_gbp`)<50 THEN '[1] <50'  
281   WHEN AVG(`adv_expense_gbp`)>=50 AND AVG(`adv_expense_gbp`)<500 THEN '[2] 50-500'
282   WHEN AVG(`adv_expense_gbp`)>=500 AND AVG(`adv_expense_gbp`)<1000 THEN '[3] 500-1000'
283   WHEN AVG(`adv_expense_gbp`)>=1000 AND AVG(`adv_expense_gbp`)<5000 THEN '[4] 1000-5000'
284   WHEN AVG(`adv_expense_gbp`)>=5000 AND AVG(`adv_expense_gbp`)<10000 THEN '[5] 5000-10000'
285   WHEN AVG(`adv_expense_gbp`)>=10000 THEN '[6] 10000+'
286   ELSE 'error'
287  END AS revenue_segment
288FROM
289  bob_date_site r
290  JOIN rabota_db.`user` u
291    ON (r.`publisher_id` = u.`user_id`)
292  WHERE r.`adv_expense_gbp`>1 AND u.`billing_source`!='nobilling' AND r.`publisher_id`>0
293GROUP BY r.`site_id`;
294
295# list of dates for sites when adv_expense_gbp > 0.2
296DROP TEMPORARY TABLE IF EXISTS site_date_expense_02;
297CREATE TEMPORARY TABLE site_date_expense_02 (KEY (site_id))
298SELECT
299  sc.`site_id`,
300  sc.`date`,
301  SUM(sc.`adv_expense_gbp`) AS adv_expense_gbp
302FROM
303  rabota_db.`npm_site_area_stat_cache` sc
304WHERE sc.date BETWEEN DATE(@start_date)
305  AND DATE(@end_date)
306GROUP BY sc.`site_id`,
307  sc.`date`
308HAVING SUM(sc.`adv_expense_gbp`) > 0.2;
309
310#copy of site_date_expense_02 for Update
311DROP TEMPORARY TABLE IF EXISTS site_date_expense_02_b;
312CREATE TEMPORARY TABLE site_date_expense_02_b (KEY (site_id))
313SELECT
314  sc.`site_id`,
315  sc.`date`,
316  SUM(sc.`adv_expense_gbp`) AS adv_expense_gbp
317FROM
318  rabota_db.`npm_site_area_stat_cache` sc
319WHERE sc.date BETWEEN DATE(@start_date)
320  AND DATE(@end_date)
321GROUP BY sc.`site_id`,
322  sc.`date`
323HAVING SUM(sc.`adv_expense_gbp`) > 0.2;
324
325# list of sites with last date when adv_expense_gbp > 1, excluding yesterday; in other words, deactivated sites
326# logic: if yesterday adv_expense_gbp is > 1, then site is active, otherwise it is deactivated
327DROP TEMPORARY TABLE IF EXISTS site_deactiv_1;
328CREATE TEMPORARY TABLE site_deactiv_1 (KEY (site_id))
329SELECT
330  sde02.site_id,
331  MAX(sde02.date) AS site_deactivation_date
332FROM
333  site_date_expense_02 sde02
334WHERE
335  sde02.adv_expense_gbp > 1
336GROUP BY sde02.site_id
337HAVING MAX(sde02.date) < DATE_SUB(CURDATE(), INTERVAL 1 DAY);
338
339# list of sites with last date when adv_expense_gbp > 0.2, excluding yesterday; in other words, deactivated sites
340# logic: if yesterday adv_expense_gbp is > 0.2, then site is active, otherwise it is deactivated
341DROP TEMPORARY TABLE IF EXISTS site_deactiv_02;
342CREATE TEMPORARY TABLE site_deactiv_02 (KEY (site_id))
343SELECT
344  sde02.site_id,
345  MAX(sde02.date) AS site_deactivation_date
346FROM
347  site_date_expense_02 sde02
348GROUP BY sde02.site_id
349HAVING MAX(sde02.date) < DATE_SUB(CURDATE(), INTERVAL 1 DAY);
350
351# list of dates for users when adv_expense_gbp > 1
352DROP TEMPORARY TABLE IF EXISTS user_date_expense_1;
353CREATE TEMPORARY TABLE user_date_expense_1 (KEY (user_id))
354SELECT
355  sc.`user_id`,
356  sc.`date`,
357  SUM(sc.`adv_expense_gbp`) AS adv_expense_gbp
358FROM
359  rabota_db.`npm_site_area_stat_cache` sc
360WHERE sc.date BETWEEN DATE(@start_date)
361  AND DATE(@end_date)
362GROUP BY sc.`user_id`,
363  sc.`date`
364HAVING SUM(sc.`adv_expense_gbp`) > 1;
365
366DROP TEMPORARY TABLE IF EXISTS user_date_expense_1_move;
367CREATE TEMPORARY TABLE user_date_expense_1_move (KEY (real_user))
368SELECT
369  ude1.`user_id`,
370  ude1.`date`,
371  ude1.`adv_expense_gbp`,
372  IFNULL(u.moved_to_user_id, ude1.user_id) AS real_user
373FROM
374  user_date_expense_1 ude1
375  JOIN rabota_db.`user` u ON ude1.user_id = u.user_id;
376
377# list of users with last date when adv_expense_gbp > 1, excluding yesterday; in other words, deactivated users
378# logic: if yesterday adv_expense_gbp is > 1, then user is active, otherwise it is deactivated
379DROP TEMPORARY TABLE IF EXISTS user_deactiv_1;
380CREATE TEMPORARY TABLE user_deactiv_1 (KEY (real_user))
381SELECT
382  ude1m.real_user,
383  MAX(ude1m.date) AS user_deactivation_date
384FROM
385  user_date_expense_1_move ude1m
386GROUP BY ude1m.real_user
387HAVING MAX(ude1m.date) < DATE_SUB(CURDATE(), INTERVAL 1 DAY);
388
389# current status for newly inserted sites
390# otherwise adv_expense_gbp < 1 will remain Null when they may already have deactivation dates
391DROP TEMPORARY TABLE IF EXISTS site_deactiv_current;
392CREATE TEMPORARY TABLE site_deactiv_current (KEY (site_id))
393SELECT DISTINCT
394  c.site_id,
395  c.site_deactivation_date
396FROM
397  tablo.commercial_regions_daily_revenue c;
398
399# current status for newly inserted users
400# otherwise adv_expense_gbp < 1 will remain Null when they may already have deactivation dates
401DROP TEMPORARY TABLE IF EXISTS user_deactiv_current;
402CREATE TEMPORARY TABLE user_deactiv_current (KEY (user_id))
403SELECT DISTINCT
404  c.user_id,
405  c.user_deactivation_date
406FROM
407  tablo.commercial_regions_daily_revenue c
408WHERE demand_id <> -1
409;
410
411# list of current account managers
412DROP TEMPORARY TABLE IF EXISTS curr_acc_manager;
413CREATE TEMPORARY TABLE curr_acc_manager (KEY (user_id))
414SELECT
415	umd.user_id,
416	IFNULL(u.name_eng, u.nickname) AS account_manager
417FROM
418	rabota_db.npm_user_manager_by_date umd
419		JOIN rabota_db.`user` u
420			ON u.user_id = umd.manager_id
421WHERE
422	umd.`type` = 'account_manager'
423	AND umd.`date` = DATE(@end_date);
424
425DELETE FROM
426	tablo.commercial_regions_daily_revenue
427WHERE
428	`date` BETWEEN DATE(@start_date) AND DATE(@end_date);
429
430INSERT INTO
431	tablo.commercial_regions_daily_revenue
432(
433	`date`,
434	user_id,
435	site_id,
436	url,
437	sales_manager,
438	account_manager,
439	junior_sales_manager,
440	demand_id,
441	impressions,
442	first_impressions,
443	viewability_measured,
444	viewability_viewed,
445	adv_expense_gbp,
446	partner_gain_gbp,
447	adv_expense_rub,
448	partner_gain_rub,
449	adv_expense_usd,
450	partner_gain_usd,
451	adv_expense_eur,
452	partner_gain_eur,
453	clicks,
454	requests,
455	unfilled_impressions,
456	user_country
457)
458SELECT
459	d.`date`,
460	d.user_id,
461	d.site_id,
462	d.url,
463	d.sales_manager,
464	d.account_manager,
465	d.junior_sales_manager,
466	d.demand_id,
467	d.impressions,
468	d.first_impressions,
469	d.viewability_measured,
470	d.viewability_viewed,
471	d.adv_expense_gbp,
472	d.partner_gain_gbp,
473	d.adv_expense_rub,
474	d.partner_gain_rub,
475	ROUND(d.adv_expense_gbp * u.exrate, 8),
476	ROUND(d.partner_gain_gbp * u.exrate, 8),
477	ROUND(d.adv_expense_gbp * e.exrate, 8),
478	ROUND(d.partner_gain_gbp * e.exrate, 8),
479	clicks,
480	requests,
481	unfilled_impressions,
482	user_country
483FROM
484	tmp_demand_stat d
485		JOIN tmp_usd_rate u
486			ON u.`date` = d.`date`
487		JOIN tmp_eur_rate e
488			ON e.`date` = d.`date`;
489
490# temporary solution; dioguinho.pt moved to dioguinho.com with old site_id, new site_id is useless, has some residual stats
491DELETE FROM
492	tablo.commercial_regions_daily_revenue
493WHERE
494	site_id = 221255;
495
496
497DROP TEMPORARY TABLE IF EXISTS tmp_reactivation_sites;
498CREATE TEMPORARY TABLE tmp_reactivation_sites (KEY(reactivation_date, site_id))
499SELECT site_id, MAX(reactivation_date) AS reactivation_date
500FROM tablo.`reactivation_sites`
501GROUP BY site_id;
502
503#custom for akket.com
504UPDATE tablo.`commercial_regions_daily_revenue`
505SET site_create_date = '2022-03-18'
506WHERE site_id = 210511;
507
508UPDATE
509    tablo.commercial_regions_daily_revenue c
510        LEFT JOIN rabota_db.demand_source d
511            ON d.cmf_system_id = c.demand_id
512SET
513    c.demand_name = IFNULL(d.name, 'Unknown');
514
515# determine top sites of users
516
517# for active users
518
519# temp tables are created here, because updated user deactivation date is used
520# last 30 days stats of active users by site
521DROP TEMPORARY TABLE IF EXISTS
522	active_user_site_a;
523CREATE TEMPORARY TABLE
524	active_user_site_a
525		(KEY (user_id, url))
526SELECT
527	t.user_id, t.`url`, SUM(t.`adv_expense_gbp`) AS total_adv_expense_gbp
528FROM
529	tablo.commercial_regions_daily_revenue t
530WHERE
531	t.user_deactivation_date IS NULL
532	AND t.date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND DATE(@end_date)
533GROUP BY
534	t.user_id, t.`url`
535HAVING
536	SUM(t.`adv_expense_gbp`) > 0;
537
538# last 30 days stats of active users by site; copy of previous temp table, because temp tables cannot be used twice in one query
539DROP TEMPORARY TABLE IF EXISTS
540	active_user_site_b;
541CREATE TEMPORARY TABLE
542	active_user_site_b
543		(KEY (user_id, url))
544SELECT
545	t.user_id, t.`url`, SUM(t.`adv_expense_gbp`) AS total_adv_expense_gbp
546FROM
547	tablo.commercial_regions_daily_revenue t
548WHERE
549	t.user_deactivation_date IS NULL
550	AND t.date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND DATE(@end_date)
551GROUP BY
552	t.user_id, t.`url`
553HAVING
554	SUM(t.`adv_expense_gbp`) > 0;
555
556# top sites of active users based on last 30 days
557DROP TEMPORARY TABLE IF EXISTS
558	top_user_site_activ;
559CREATE TEMPORARY TABLE
560	top_user_site_activ
561		(KEY (user_id, url))
562SELECT
563	a.user_id, a.url
564FROM
565	active_user_site_a a
566WHERE
567	a.total_adv_expense_gbp = (SELECT MAX(total_adv_expense_gbp) FROM active_user_site_b b WHERE b.user_id = a.user_id);
568
569# for deactivated users
570
571# all time stats of deactivated users by site
572DROP TEMPORARY TABLE IF EXISTS
573	bob_top_user_site_a;
574CREATE TEMPORARY TABLE
575	bob_top_user_site_a
576		(KEY (publisher_id, site_url))
577SELECT
578	t.publisher_id, t.`site_url`, SUM(t.`adv_expense_gbp`) AS total_adv_expense_gbp
579FROM
580	bi.`book_of_business_report` t
581GROUP BY
582	t.publisher_id, t.`site_url`
583HAVING
584	SUM(t.`adv_expense_gbp`) > 0;
585
586# all time stats of deactivated users by site; copy of previous temp table, because temp tables cannot be used twice in one query
587DROP TEMPORARY TABLE IF EXISTS
588	bob_top_user_site_b;
589CREATE TEMPORARY TABLE
590	bob_top_user_site_b
591		(KEY (publisher_id, site_url))
592SELECT
593	t.publisher_id, t.`site_url`, SUM(t.`adv_expense_gbp`) AS total_adv_expense_gbp
594FROM
595	bi.`book_of_business_report` t
596GROUP BY
597	t.publisher_id, t.`site_url`
598HAVING
599	SUM(t.`adv_expense_gbp`) > 0;
600
601# top sites of deactivated users based on all time stats
602DROP TEMPORARY TABLE IF EXISTS
603	top_user_site_deactiv;
604CREATE TEMPORARY TABLE
605	top_user_site_deactiv
606		(KEY (publisher_id, site_url))
607SELECT
608	a.publisher_id, a.site_url
609FROM
610	bob_top_user_site_a a
611WHERE
612	a.total_adv_expense_gbp = (SELECT MAX(total_adv_expense_gbp) FROM bob_top_user_site_b b WHERE b.publisher_id = a.publisher_id);
613
614
615DROP TEMPORARY TABLE IF EXISTS tmp_final_update;
616CREATE TEMPORARY TABLE tmp_final_update (KEY(site_id))
617SELECT 
618s.`site_id`, 
619s.`user_id`,
620IFNULL(am.account_manager, 'None') AS account_manager_current,
621user_activ.user_create_date,
622u.`email` AS user_email,
623IFNULL(u.commercial_region, 'other') AS commercial_region,
624bob_pub.revenue_segment AS user_segment,
625bob_site.revenue_segment AS site_segment,
626site_activ.site_create_date,
627r.`reactivation_date` AS reactivation_month,
628CASE
629WHEN site_deac_curr.site_id IS NOT NULL THEN site_deac_curr.site_deactivation_date
630WHEN 
631(bob_site.revenue_segment = '[1] <50' OR 
632((bob_site.revenue_segment != '[1] <50' OR bob_site.revenue_segment IS NULL)
633AND site_02_b.site_id IS NOT NULL)) THEN NULL 
634WHEN (bob_site.revenue_segment != '[1] <50' OR bob_site.revenue_segment IS NULL) THEN sd1.site_deactivation_date
635WHEN (bob_site.revenue_segment = '[1] <50') THEN sd02.site_deactivation_date
636END AS site_deactivation_date,
637CASE 
638WHEN user_deac_curr.user_id IS NOT NULL THEN user_deac_curr.user_deactivation_date
639WHEN ude1.real_user IS NOT NULL THEN NULL
640WHEN ud1.real_user IS NOT NULL THEN ud1.user_deactivation_date 
641END AS user_deactivation_date,
642IF( 
643CASE 
644WHEN user_deac_curr.user_id IS NOT NULL THEN user_deac_curr.user_deactivation_date
645WHEN ude1.real_user IS NOT NULL THEN NULL
646WHEN ud1.real_user IS NOT NULL THEN ud1.user_deactivation_date 
647END IS NOT NULL, u.`stopping_reason`, NULL) AS user_stop_reason,
648IF( 
649CASE 
650WHEN user_deac_curr.user_id IS NOT NULL THEN user_deac_curr.user_deactivation_date
651WHEN ude1.real_user IS NOT NULL THEN NULL
652WHEN ud1.real_user IS NOT NULL THEN ud1.user_deactivation_date 
653END IS NULL, top_activ.url, top_deac.site_url) AS user_top_site
654FROM rabota_db.`site` s
655JOIN rabota_db.`user` u
656	ON u.`user_id` = s.`user_id`
657LEFT JOIN bob_pub_summary bob_pub
658	ON bob_pub.publisher_id = s.user_id
659LEFT JOIN tmp_user_create_date user_activ
660	ON user_activ.user_id = s.user_id
661LEFT JOIN curr_acc_manager am
662	ON am.user_id = s.user_id
663LEFT JOIN bob_site_summary bob_site
664	ON bob_site.site_id = s.site_id
665LEFT JOIN tmp_site_create_date site_activ
666	ON site_activ.site_id = s.site_id
667LEFT JOIN tmp_reactivation_sites r
668	ON r.`site_id` = s.`site_id`
669LEFT JOIN (SELECT DISTINCT site_id FROM site_date_expense_02) site_02
670	ON site_02.site_id = s.`site_id`
671LEFT JOIN (SELECT DISTINCT site_id FROM site_date_expense_02_b WHERE adv_expense_gbp > 1) site_02_b
672	ON site_02_b.site_id = s.`site_id`
673LEFT JOIN site_deactiv_1 sd1
674	ON sd1.site_id = s.site_id
675LEFT JOIN site_deactiv_02 sd02
676	ON sd02.site_id = s.site_id
677LEFT JOIN (SELECT DISTINCT real_user FROM user_date_expense_1_move) ude1
678	ON ude1.real_user = s.user_id
679LEFT JOIN user_deactiv_1 ud1
680	ON ud1.real_user = s.user_id 
681LEFT JOIN top_user_site_activ top_activ
682	ON top_activ.user_id = s.user_id
683LEFT JOIN top_user_site_deactiv top_deac
684	ON top_deac.publisher_id = s.user_id
685LEFT JOIN site_deactiv_current site_deac_curr
686	ON site_deac_curr.site_id = s.site_id
687LEFT JOIN user_deactiv_current user_deac_curr
688	ON user_deac_curr.user_id = s.user_id
689;
690
691UPDATE tablo.`commercial_regions_daily_revenue` c
692LEFT JOIN tmp_final_update tmp
693ON c.`site_id` = tmp.site_id
694SET 
695c.`account_manager_current` = tmp.account_manager_current,
696c.user_create_date = tmp.user_create_date,
697c.`user_email` = tmp.user_email,
698c.commercial_region = tmp.commercial_region,
699c.user_segment = tmp.user_segment,
700c.site_segment = tmp.site_segment,
701c.`site_create_date` = tmp.site_create_date,
702c.reactivation_month = tmp.reactivation_month,
703c.site_deactivation_date = tmp.site_deactivation_date,
704c.user_deactivation_date = tmp.user_deactivation_date,
705c.user_stop_reason = tmp.user_stop_reason,
706c.user_top_site = tmp.user_top_site
707;
708
709
710
711DELETE FROM
712	tablo.commercial_regions_daily_revenue_no_ds
713WHERE
714	`date` BETWEEN DATE(@start_date) AND DATE(@end_date);
715
716INSERT INTO
717	tablo.commercial_regions_daily_revenue_no_ds
718(
719	`date`,
720	user_id,
721	site_id,
722	url,
723	sales_manager,
724	account_manager,
725	impressions,
726	viewability_measured,
727	viewability_viewed,
728	adv_expense_gbp,
729	partner_gain_gbp,
730	adv_expense_rub,
731	partner_gain_rub,
732	adv_expense_usd,
733	partner_gain_usd,
734	adv_expense_eur,
735	partner_gain_eur,
736	unfilled_impressions
737)
738SELECT
739	c.`date`,
740	c.user_id,
741	c.site_id,
742	c.url,
743	c.sales_manager,
744	c.account_manager,
745	SUM(c.impressions),
746	SUM(c.viewability_measured),
747	SUM(c.viewability_viewed),
748	SUM(c.adv_expense_gbp),
749	SUM(c.partner_gain_gbp),
750	SUM(c.adv_expense_rub),
751	SUM(c.partner_gain_rub),
752	SUM(c.adv_expense_usd),
753	SUM(c.partner_gain_usd),
754	SUM(c.adv_expense_eur),
755	SUM(c.partner_gain_eur),
756	SUM(c.unfilled_impressions)
757FROM
758	tablo.commercial_regions_daily_revenue c
759WHERE
760	c.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
761        AND c.demand_id <> -1
762GROUP BY
763	c.`date`,
764	c.site_id;
765
766
767UPDATE
768	tablo.commercial_regions_daily_revenue_no_ds c
769		LEFT JOIN rabota_db.`user` u
770			ON u.user_id = c.user_id
771		LEFT JOIN tmp_site_create_date s
772			ON s.site_id = c.site_id
773SET
774	c.commercial_region = IFNULL(u.commercial_region, 'other'),
775	c.site_create_date = s.site_create_date;
776
777UPDATE
778	tablo.commercial_regions_daily_revenue a
779		LEFT JOIN rabota_db.site s
780			ON s.site_id = a.site_id
781		LEFT JOIN rabota_db.site_category c
782			ON c.category_id = s.category_id
783SET
784	a.site_category = IFNULL(c.name, 'Unknown')
785WHERE a.`date` BETWEEN @start_date AND @end_date;
786
787DROP TEMPORARY TABLE IF EXISTS pipedrive_data;
788CREATE TEMPORARY TABLE pipedrive_data (KEY(publisher_id))
789SELECT po.organization_custom_clickio_user_id AS publisher_id,
790GROUP_CONCAT(poa.label) AS 'pd_org_lead_channel'
791FROM rabota_db.pipedrive_organization po
792JOIN rabota_db.pipedrive_organization_attrs poa
793ON po.organization_id = poa.organization_id AND poa.attr_name = 'lead_channel'
794WHERE po.organization_custom_clickio_user_id IS NOT NULL
795GROUP BY po.organization_id;
796
797UPDATE tablo.`commercial_regions_daily_revenue` r
798LEFT JOIN pipedrive_data pd
799ON pd.publisher_id = r.user_id
800SET r.lead_channel = pd.pd_org_lead_channel
801WHERE r.`date` BETWEEN @start_date AND @end_date;
802
803#user_bu_id,user_billing_country,user_payment_method
804UPDATE tablo.`commercial_regions_daily_revenue` r USE INDEX (DATE)
805    JOIN bi.user u ON (r.user_id=u.user_id)
806SET
807    r.user_bu_id=IF(u.user_subnet_id=19,13,IF(u.user_subnet_id=21,15,0)),
808    r.user_billing_country = u.user_billing_country,
809    r.user_payment_method=u.payment_method,
810    r.user_tag=u.user_tag
811WHERE r.`date` BETWEEN @start_date AND @end_date;
812
813
814#demand_bu_id
815UPDATE tablo.`commercial_regions_daily_revenue` r USE INDEX (DATE)
816    JOIN rabota_db.cmf_system cs ON (r.demand_id=cs.cmf_system_id)
817SET r.demand_bu_id=cs.business_unit_id
818WHERE r.`date` BETWEEN @start_date AND @end_date;
819
820
821#Jacopo asked to remove publisher deactivation date for these publishers temporarily
822UPDATE tablo.`commercial_regions_daily_revenue`
823SET user_deactivation_date = NULL
824WHERE user_id IN (145101, 146953);
825
826
827SELECT
828	1 AS STATUS,
829	'OK' AS message;