sql talgat

clickio

Feb 13th, 2025

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

text 24.48 KB | None | 0 0

raw download clone embed print report

SET @start_date = '2023-01-01';
#SET @end_date = '2023-08-31';
SET @end_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
SET @user_id = 151256;
DROP TEMPORARY TABLE IF EXISTS
tmp_usd_rate;
CREATE TEMPORARY TABLE
tmp_usd_rate
(KEY (`date`))
SELECT
e.`date`,
AVG(e.exrate) AS exrate
FROM
rabota_db.exchange_rate e
WHERE
e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND e.source_cur_id = 6
AND e.destination_cur_id = 2
GROUP BY
e.`date`;
DROP TEMPORARY TABLE IF EXISTS
tmp_eur_rate;
CREATE TEMPORARY TABLE
tmp_eur_rate
(KEY (`date`))
SELECT
e.`date`,
AVG(e.exrate) AS exrate
FROM
rabota_db.exchange_rate e
WHERE
e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND e.source_cur_id = 6
AND e.destination_cur_id = 4
GROUP BY
e.`date`;
DROP TEMPORARY TABLE IF EXISTS tmp_rub_rate;
CREATE TEMPORARY TABLE tmp_rub_rate (KEY (`date`))
SELECT
e.`date`,
AVG(e.exrate) AS exrate
FROM rabota_db.exchange_rate e
WHERE e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND e.source_cur_id = 6
AND e.destination_cur_id = 1
GROUP BY e.`date`;
DELETE FROM
tablo.unified_site_data
WHERE
`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND user_id = @user_id;
#Insert GA
INSERT INTO
tablo.unified_site_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
advertiser_id,
advertiser_name,
device,
sessions,
pageviews,
users,
content_platform
)
SELECT
ga.`date`,
ci.ga_view_id,
'analytics',
IFNULL(s.url_domain, s.url) AS uri,
s.user_id,
ci.site_id,
0,
'Google Analytics',
CONCAT(UPPER(LEFT(ga.device_category, 1)), SUBSTR(ga.device_category, 2)) AS device,
SUM(ga.sessions),
SUM(ga.pageviews),
SUM(ga.users),
IF(data_source='(not set)' OR data_source='web', 'Web', 'AMP') AS content_platform
FROM
imports.`ga_unified_reporting` ga
JOIN rabota_db.ga_custom_import ci
ON ci.ga_import_id = ga.ga_import_id
JOIN rabota_db.site s
ON s.site_id = ci.site_id
WHERE
ga.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND (ci.field_group='unified_reporting_devices_users')
AND s.`user_id` = @user_id
GROUP BY `date`,ci.ga_view_id,uri,user_id,ci.site_id,device,content_platform;
#Insert GA4 AMP
INSERT INTO
tablo.unified_site_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
advertiser_id,
advertiser_name,
device,
sessions,
pageviews,
users,
content_platform
)
SELECT
ga.`date`,
ci.ga_view_id,
'analytics',
IFNULL(s.url_domain, s.url) AS url,
s.user_id,
ci.site_id,
0,
'Google Analytics',
'Mobile' AS device,
SUM(ga.sessions),
SUM(ga.pageviews),
SUM(ga.users),
'AMP' AS content_platform
FROM
imports.`ga_unified_reporting` ga
JOIN rabota_db.ga_custom_import ci
ON ci.ga_import_id = ga.ga_import_id
JOIN rabota_db.site s
ON s.site_id = ci.site_id
WHERE
ga.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND ci.field_group = 'ga4_unified_reporting_no_devices'
AND s.user_id = @user_id
GROUP BY `date`,ci.ga_view_id,url,user_id,ci.site_id,device,content_platform;
# for subtracting from total in order to get web
DROP TEMPORARY TABLE IF EXISTS tmp_ga4_amp;
CREATE TEMPORARY TABLE tmp_ga4_amp (KEY (`date`, site_id, device_category))
SELECT
ga.`date`,
ci.site_id,
'mobile' AS device_category,
SUM(ga.sessions) AS sessions,
SUM(ga.pageviews) AS pageviews,
SUM(ga.users) AS users
FROM
imports.`ga_unified_reporting` ga
JOIN rabota_db.ga_custom_import ci
ON ci.ga_import_id = ga.ga_import_id
JOIN rabota_db.site s
ON s.site_id = ci.site_id
WHERE
ga.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND ci.field_group = 'ga4_unified_reporting_no_devices'
AND s.user_id = @user_id
GROUP BY `date`, ci.site_id;
#Insert GA4 Web
INSERT INTO
tablo.unified_site_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
advertiser_id,
advertiser_name,
device,
sessions,
pageviews,
users,
content_platform
)
SELECT
ga.`date`,
ci.ga_view_id,
'analytics',
IFNULL(s.url_domain, s.url) AS url,
s.user_id,
ci.site_id,
0,
'Google Analytics',
IF(ga.device_category = 'smart tv', 'Desktop', CONCAT(UPPER(LEFT(ga.device_category, 1)), SUBSTR(ga.device_category, 2))) AS device,
SUM(ga.sessions) - SUM(IFNULL(gaamp.sessions, 0)),
SUM(ga.pageviews) - SUM(IFNULL(gaamp.pageviews, 0)),
SUM(ga.users) - SUM(IFNULL(gaamp.users, 0)),
'Web' AS content_platform
FROM
imports.`ga_unified_reporting` ga
JOIN rabota_db.ga_custom_import ci
ON ci.ga_import_id = ga.ga_import_id
JOIN rabota_db.site s
ON s.site_id = ci.site_id
LEFT JOIN tmp_ga4_amp gaamp
ON ga.date = gaamp.date AND ci.site_id = gaamp.site_id AND ga.device_category = gaamp.device_category
WHERE
ga.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND ci.field_group = 'ga4_unified_reporting_devices'
AND s.user_id = @user_id
GROUP BY `date`,ci.ga_view_id,url,user_id,ci.site_id,device,content_platform;
INSERT INTO
tablo.unified_site_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
advertiser_id,
advertiser_name,
device,
requests,
impressions,
clicks,
pageviews_adsense,
adv_expense_gbp,
partner_gain,
partner_gain_gbp,
content_platform,
viewability_viewed,
viewability_measured,
adv_expense_usd,
partner_gain_usd,
adv_expense_eur,
partner_gain_eur,
adv_expense_rub,
partner_gain_rub
)
SELECT
a.`date`,
a.client_id,
'adsense',
IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
IFNULL(r.clickio_user_id, s.user_id),
r.clickio_site_id,
0,
'Google AdSense',
IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
SUM(a.ad_requests),
SUM(a.impressions),
SUM(a.clicks),
SUM(a.page_views),
SUM(a.earnings_gbp),
SUM(a.earnings),
SUM(a.earnings_gbp),
CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
ELSE a.content_platform END AS content_platform,
SUM(a.measurability * a.impressions * a.viewability),
SUM(a.measurability * a.impressions),
ROUND(SUM(a.earnings_gbp) * usd.exrate, 8),
ROUND(SUM(a.earnings_gbp) * usd.exrate, 8),
ROUND(SUM(a.earnings_gbp) * eur.exrate, 8),
ROUND(SUM(a.earnings_gbp) * eur.exrate, 8),
ROUND(SUM(a.earnings_gbp) * rub.exrate, 8),
ROUND(SUM(a.earnings_gbp) * rub.exrate, 8)
FROM
imports.adsense_raw_data a
JOIN tablo.unified_adsense_domain_relation r
ON r.`domain` = a.`domain`
AND r.client_id = a.client_id
LEFT JOIN rabota_db.site s
ON s.site_id = r.clickio_site_id
JOIN tmp_usd_rate usd
ON usd.`date` = a.`date`
JOIN tmp_eur_rate eur
ON eur.`date` = a.`date`
JOIN tmp_rub_rate rub
ON rub.date = a.date
WHERE
a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND r.clickio_user_id = @user_id
GROUP BY
a.`date`,
a.client_id,
IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
r.clickio_site_id,
IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
ELSE a.content_platform END
HAVING
SUM(a.earnings) > 0;
# ad manager - by sites
INSERT INTO
tablo.unified_site_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
advertiser_id,
advertiser_name,
device,
requests,
impressions,
clicks,
adv_expense_gbp,
partner_gain,
partner_gain_gbp,
content_platform,
adv_expense_usd,
partner_gain_usd,
adv_expense_eur,
partner_gain_eur,
adv_expense_rub,
partner_gain_rub,
demand_channel,
viewability_viewed,
viewability_measured
)
SELECT
a.`date`,
a.network_id,
'admanager',
IFNULL(IFNULL(s.url_domain, s.url), f.`domain`),
s.user_id,
r.clickio_site_id,
0,
'Google Ad Manager',
IF(a.device_category_name = 'Smartphone', 'Mobile', IF(a.device_category_name = 'Tablet', 'Tablet', 'Desktop')),
SUM(a.requests),
SUM(a.impressions),
SUM(a.clicks),
SUM(a.revenue * IFNULL(e.exrate, 1)),
SUM(a.revenue),
SUM(a.revenue * IFNULL(e.exrate, 1)),
IF(a.inventory_types = 'AMP', 'AMP', 'Web'),
ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * usd.exrate, 8),
ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * usd.exrate, 8),
ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * eur.exrate, 8),
ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * eur.exrate, 8),
ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * rub.exrate, 8),
ROUND(SUM(a.revenue * IFNULL(e.exrate, 1)) * rub.exrate, 8),
a.demand_channel,
SUM(a.viewable_impressions),
SUM(a.measurable_impressions)
FROM
tablo.unified_reporting_gam a
JOIN tablo.unified_reporting_gam_site_filter f
ON a.date = f.date
AND a.ad_unit_id = f.ad_unit_id
AND a.network_id = f.network_id
JOIN tablo.unified_gam_domain_relation r
ON r.`domain` = f.`domain`
AND r.network_id = a.network_id
LEFT JOIN rabota_db.site s
ON s.site_id = r.clickio_site_id
LEFT JOIN rabota_db.exchange_rate e
ON a.date = e.date AND r.currency_id = e.source_cur_id AND e.destination_cur_id = 6 AND e.exchange_rate_source_id = 3
JOIN tmp_usd_rate usd
ON usd.`date` = a.`date`
JOIN tmp_eur_rate eur
ON eur.`date` = a.`date`
JOIN tmp_rub_rate rub
ON rub.date = a.date
WHERE
a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND r.clickio_user_id = @user_id
GROUP BY
a.`date`,
a.network_id,
IFNULL(IFNULL(s.url_domain, s.url), f.`domain`),
r.clickio_site_id,
IF(a.device_category_name = 'Smartphone', 'Mobile', IF(a.device_category_name = 'Tablet', 'Tablet', 'Desktop')),
IF(a.inventory_types = 'AMP', 'AMP', 'Web'),
a.demand_channel
#HAVING
# SUM(a.revenue) > 0
;
DROP TEMPORARY TABLE IF EXISTS
tmp_sites_dates;
CREATE TEMPORARY TABLE
tmp_sites_dates
(KEY (site_id, `date`))
SELECT DISTINCT
site_id,
`date`
FROM
tablo.unified_site_data
WHERE
`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND NOT site_id IS NULL
AND user_id = @user_id;
INSERT INTO
tablo.unified_site_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
advertiser_id,
device,
requests,
impressions,
clicks,
adv_expense_gbp,
partner_gain,
partner_gain_gbp,
content_platform,
viewability_viewed,
viewability_measured,
external_view_count,
adv_expense_usd,
partner_gain_usd,
adv_expense_eur,
partner_gain_eur,
adv_expense_rub,
partner_gain_rub,
pub_adserver_cost,
pub_adserver_cost_gbp,
company_adserver_cost,
company_adserver_cost_gbp
)
SELECT
npm.`date`,
s.user_id,
'clickio',
IFNULL(s.url_domain, s.url),
s.user_id,
npm.site_id,
npm.adv_net_id,
IF(npm.device_type IN ('High-end mobile devices', 'Smartphone'), 'Mobile', IF(npm.device_type IN ('Tablets', 'Tablet'), 'Tablet', 'Desktop')),
SUM(npm.external_first_request_count),
SUM(npm.external_first_view_count),
SUM(npm.hit_count),
SUM(npm.adv_expense_gbp),
SUM(npm.partner_gain),
SUM(npm.partner_gain_gbp),
CASE WHEN sd24.is_amp = 1 THEN 'AMP' ELSE 'Web' END,
SUM(npm.external_viewability_viewed_impressions),
SUM(npm.external_viewability_measured_impressions),
SUM(npm.external_view_count),
ROUND(SUM(npm.adv_expense_gbp) * usd.exrate, 8),
ROUND(SUM(npm.partner_gain_gbp) * usd.exrate, 8),
ROUND(SUM(npm.adv_expense_gbp) * eur.exrate, 8),
ROUND(SUM(npm.partner_gain_gbp) * eur.exrate, 8),
ROUND(SUM(npm.adv_expense_gbp) * rub.exrate, 8),
ROUND(SUM(npm.partner_gain_gbp) * rub.exrate, 8),
SUM(npm.`pub_adserver_cost`) AS pub_adserver_cost,
SUM(npm.`pub_adserver_cost_gbp`) AS pub_adserver_cost_gbp,
SUM(npm.`company_adserver_cost`) AS company_adserver_cost,
SUM(npm.`company_adserver_cost_gbp`) AS company_adserver_cost_gbp
FROM
tablo.npm_site_area_device_stat npm
JOIN tmp_sites_dates tmp
ON tmp.site_id = npm.site_id
AND tmp.`date` = npm.`date`
AND npm.site_id <> 230029
JOIN rabota_db.site s
ON s.site_id = npm.site_id
JOIN rabota_db.site_area_design_24 sd24
ON sd24.site_area_id = npm.site_area_id
JOIN tmp_usd_rate usd
ON usd.`date` = npm.`date`
JOIN tmp_eur_rate eur
ON eur.`date` = npm.`date`
JOIN tmp_rub_rate rub
ON rub.date = npm.date
GROUP BY
npm.`date`,
IFNULL(s.url_domain, s.url),
npm.site_id,
npm.adv_net_id,
IF(npm.device_type IN ('High-end mobile devices', 'Smartphone'), 'Mobile', IF(npm.device_type IN ('Tablets', 'Tablet'), 'Tablet', 'Desktop')),
CASE WHEN sd24.is_amp = 1 THEN 'AMP' ELSE 'Web' END
# HAVING
# SUM(npm.partner_gain) > 0
# for inclusion of Direct Campaigns (cmf_id: 123)
;
UPDATE
tablo.unified_site_data u
LEFT JOIN rabota_db.demand_source d
ON d.cmf_system_id = u.advertiser_id
SET
u.advertiser_name = IFNULL(d.name, 'Unknown')
WHERE
u.account_type = 'clickio'
AND u.date BETWEEN DATE(@start_date) AND DATE(@end_date)
AND u.`user_id` = @user_id;
DELETE FROM
tablo.unified_ad_unit_data
WHERE
`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND user_id = @user_id;
INSERT INTO
tablo.unified_ad_unit_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
ad_unit_id,
ad_unit_name,
advertiser_id,
advertiser_name,
device,
requests,
impressions,
clicks,
adv_expense_gbp,
partner_gain,
partner_gain_gbp,
content_platform,
viewability_viewed,
viewability_measured,
adsense_ad_format
)
SELECT
a.`date`,
a.client_id,
'adsense',
IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
IFNULL(r.clickio_user_id, s.user_id),
r.clickio_site_id,
a.unit_id,
a.unit_name,
0,
'Google AdSense',
IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
SUM(a.ad_requests),
SUM(a.impressions),
SUM(a.clicks),
SUM(a.earnings_gbp),
SUM(a.earnings),
SUM(a.earnings_gbp),
CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
ELSE a.content_platform END AS content_platform,
SUM(a.measurability * a.impressions * a.viewability),
SUM(a.measurability * a.impressions),
'In-page'
FROM
imports.adsense_adunit_raw_data a
JOIN tablo.unified_adsense_domain_relation r
ON r.`domain` = a.`domain`
AND r.client_id = a.client_id
LEFT JOIN rabota_db.site s
ON s.site_id = r.clickio_site_id
WHERE
a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND r.clickio_user_id = @user_id
GROUP BY
a.`date`,
a.client_id,
IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
r.clickio_site_id,
a.unit_id,
IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
ELSE a.content_platform END
HAVING
SUM(a.earnings) > 0;
INSERT INTO
tablo.unified_ad_unit_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
ad_unit_id,
ad_unit_name,
advertiser_id,
advertiser_name,
device,
requests,
impressions,
clicks,
adv_expense_gbp,
partner_gain,
partner_gain_gbp,
content_platform,
demand_channel,
viewability_viewed,
viewability_measured
)
SELECT
a.`date`,
a.network_id,
'admanager',
IFNULL(IFNULL(s.url_domain, s.url), f.`domain`),
s.user_id,
r.clickio_site_id,
a.ad_unit_id,
a.ad_unit_name,
0,
'Google Ad Manager',
IF(a.device_category_name = 'Smartphone', 'Mobile', IF(a.device_category_name = 'Tablet', 'Tablet', 'Desktop')),
SUM(a.requests),
SUM(a.impressions),
SUM(a.clicks),
SUM(a.revenue * IFNULL(e.exrate, 1)),
SUM(a.revenue),
SUM(a.revenue * IFNULL(e.exrate, 1)),
IF(a.inventory_types = 'AMP', 'AMP', 'Web'),
a.demand_channel,
SUM(a.viewable_impressions),
SUM(a.measurable_impressions)
FROM
tablo.unified_reporting_gam a
JOIN tablo.unified_reporting_gam_site_filter f
ON a.date = f.date
AND a.ad_unit_id = f.ad_unit_id
AND a.network_id = f.network_id
JOIN tablo.unified_gam_domain_relation r
ON r.`domain` = f.`domain`
AND r.network_id = a.network_id
LEFT JOIN rabota_db.site s
ON s.site_id = r.clickio_site_id
LEFT JOIN rabota_db.exchange_rate e
ON a.date = e.date AND r.currency_id = e.source_cur_id AND e.destination_cur_id = 6 AND e.exchange_rate_source_id = 3
WHERE
a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND r.clickio_user_id = @user_id
GROUP BY
a.`date`,
a.network_id,
IFNULL(IFNULL(s.url_domain, s.url), f.`domain`),
r.clickio_site_id,
a.ad_unit_id,
IF(a.device_category_name = 'Smartphone', 'Mobile', IF(a.device_category_name = 'Tablet', 'Tablet', 'Desktop')),
IF(a.inventory_types = 'AMP', 'AMP', 'Web'),
a.demand_channel
#HAVING
# SUM(a.revenue) > 0
;
INSERT INTO
tablo.unified_ad_unit_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
ad_unit_id,
advertiser_id,
device,
requests,
impressions,
clicks,
adv_expense_gbp,
partner_gain,
partner_gain_gbp,
content_platform,
viewability_viewed,
viewability_measured,
external_view_count,
pub_adserver_cost,
pub_adserver_cost_gbp,
company_adserver_cost,
company_adserver_cost_gbp
)
SELECT
npm.`date`,
s.user_id,
'clickio',
IFNULL(s.url_domain, s.url),
s.user_id,
npm.site_id,
IFNULL(asa.site_area_id, npm.site_area_id),
npm.adv_net_id,
IF(npm.device_type IN ('High-end mobile devices', 'Smartphone'), 'Mobile', IF(npm.device_type IN ('Tablets', 'Tablet'), 'Tablet', 'Desktop')),
SUM(npm.external_first_request_count),
SUM(npm.external_first_view_count),
SUM(npm.hit_count),
SUM(npm.adv_expense_gbp),
SUM(npm.partner_gain),
SUM(npm.partner_gain_gbp),
CASE WHEN sd24.is_amp = 1 THEN 'AMP' ELSE 'Web' END,
SUM(npm.external_viewability_viewed_impressions),
SUM(npm.external_viewability_measured_impressions),
SUM(npm.external_view_count),
SUM(npm.`pub_adserver_cost`) AS pub_adserver_cost,
SUM(npm.`pub_adserver_cost_gbp`) AS pub_adserver_cost_gbp,
SUM(npm.`company_adserver_cost`) AS company_adserver_cost,
SUM(npm.`company_adserver_cost_gbp`) AS company_adserver_cost_gbp
FROM
tablo.npm_site_area_device_stat npm
JOIN tmp_sites_dates tmp
ON tmp.site_id = npm.site_id
AND tmp.`date` = npm.`date`
AND npm.site_id <> 230029
LEFT JOIN rabota_db.attached_site_areas asa
ON asa.attached_site_area_id = npm.site_area_id
JOIN rabota_db.site s
ON s.site_id = npm.site_id
JOIN rabota_db.site_area_design_24 sd24
ON sd24.site_area_id = IFNULL(asa.site_area_id, npm.site_area_id)
GROUP BY
npm.`date`,
IFNULL(s.url_domain, s.url),
npm.site_id,
IFNULL(asa.site_area_id, npm.site_area_id),
npm.adv_net_id,
IF(npm.device_type IN ('High-end mobile devices', 'Smartphone'), 'Mobile', IF(npm.device_type IN ('Tablets', 'Tablet'), 'Tablet', 'Desktop')),
CASE WHEN sd24.is_amp = 1 THEN 'AMP' ELSE 'Web' END
# HAVING
# SUM(npm.partner_gain) > 0
# for inclusion of Direct Campaigns (cmf_id: 123)
;
UPDATE
tablo.unified_ad_unit_data u
LEFT JOIN rabota_db.demand_source d
ON d.cmf_system_id = u.advertiser_id
SET
u.advertiser_name = IFNULL(d.name, 'Unknown')
WHERE
u.account_type = 'clickio'
AND u.date BETWEEN DATE(@start_date) AND DATE(@end_date)
AND u.`user_id` = @user_id;
UPDATE
tablo.unified_ad_unit_data u
LEFT JOIN rabota_db.site_area sa
ON sa.site_area_id = u.ad_unit_id
SET
u.ad_unit_name = IFNULL(sa.name, 'no_ad_unit_name')
WHERE
u.account_type = 'clickio'
AND u.date BETWEEN DATE(@start_date) AND DATE(@end_date)
AND u.`user_id` = @user_id;
INSERT INTO
tablo.unified_ad_unit_data
(
`date`,
account_id,
account_type,
url,
user_id,
site_id,
ad_unit_id,
ad_unit_name,
advertiser_id,
advertiser_name,
device,
requests,
impressions,
clicks,
adv_expense_gbp,
partner_gain,
partner_gain_gbp,
content_platform,
adsense_ad_format,
viewability_viewed,
viewability_measured
)
SELECT
a.`date`,
a.client_id,
'adsense',
IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
IFNULL(r.clickio_user_id, s.user_id),
r.clickio_site_id,
0,
'[auto ads]',
0,
'Google AdSense',
IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
SUM(a.ad_requests),
SUM(a.impressions),
SUM(a.clicks),
SUM(a.earnings_gbp),
SUM(a.earnings),
SUM(a.earnings_gbp),
CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
ELSE a.content_platform END AS content_platform,
a.ad_format_name,
SUM(a.measurability * a.impressions * a.viewability),
SUM(a.measurability * a.impressions)
FROM
imports.`adsense_raw_data` a
JOIN tablo.unified_adsense_domain_relation r
ON r.`domain` = a.`domain`
AND r.client_id = a.client_id
LEFT JOIN rabota_db.site s
ON s.site_id = r.clickio_site_id
WHERE
a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND a.ad_placement_name = 'Auto ads'
AND r.clickio_user_id = @user_id
GROUP BY
a.`date`,
a.client_id,
IFNULL(IFNULL(s.url_domain, s.url), a.`domain`),
r.clickio_site_id,
IF(a.platform_type = 'HighEndMobile', 'Mobile', IF(a.platform_type = 'Tablet', 'Tablet', 'Desktop')),
CASE WHEN a.content_platform = 'Web Story' THEN 'Web'
ELSE a.content_platform END,
a.ad_format_name
HAVING
SUM(a.earnings) > 0;
#update site_data adsense access
DROP TEMPORARY TABLE IF EXISTS tmp_mcm_access;
CREATE TEMPORARY TABLE tmp_mcm_access(KEY(account_id))
SELECT
CONCAT('ca-', child_network_id) AS account_id
FROM rabota_db.`mcm_publisher_date_settings`
WHERE mcm_type = 'adsense'
;
#active API access
DROP TEMPORARY TABLE IF EXISTS tmp_active_api_access;
CREATE TEMPORARY TABLE tmp_active_api_access (KEY(account_id))
SELECT CONCAT("ca-", network_id) AS account_id,
google_user_settings_id
FROM rabota_db.`adsense_custom_import`
WHERE
google_user_settings_id NOT IN (1, 2, 3, 6, 15, 84, 9, 72, 148, 197, 463)
AND last_import_status = 'OK'
AND today_every_minute IS NOT NULL
GROUP BY CONCAT("ca-", network_id)
;
# set area_type
UPDATE tablo.unified_ad_unit_data a
JOIN rabota_db.site_area_type sat ON sat.site_area_id = a.ad_unit_id
SET a.area_type = sat.`site_area_type_pub`
WHERE a.`account_type` = 'clickio' AND a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
AND a.user_id = @user_id;
#Adsense null user_id
DROP TEMPORARY TABLE IF EXISTS tmp_adsense_null;
CREATE TEMPORARY TABLE tmp_adsense_null (KEY(client_id, user_id))
SELECT
a.client_id,
b.user_id
FROM tablo.`unified_adsense_domain_relation` a
JOIN rabota_db.`site` b
ON a.clickio_site_id = b.site_id
GROUP BY a.client_id;
UPDATE tablo.`unified_site_data` a
JOIN tmp_adsense_null b
ON a.`account_id` = b.client_id
SET a.`user_id` = b.user_id
WHERE a.account_type = 'adsense' AND a.`user_id` IS NULL
AND a.`date` BETWEEN @start_date AND @end_date
AND a.user_id = @user_id;
UPDATE tablo.`unified_ad_unit_data` a
JOIN tmp_adsense_null b
ON a.`account_id` = b.client_id
SET a.`user_id` = b.user_id
WHERE a.account_type = 'adsense' AND a.`user_id` IS NULL
AND a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;
#Adsense account name
DROP TEMPORARY TABLE IF EXISTS tmp_adsense_name;
CREATE TEMPORARY TABLE tmp_adsense_name (KEY(client_id))
SELECT
a.client_id,
a.account_name
FROM imports.`adsense_raw_data` a
WHERE DATE >= CURDATE() - INTERVAL 30 DAY
AND a.account_name IS NOT NULL
GROUP BY a.client_id;
UPDATE tablo.`unified_site_data` a
JOIN tmp_adsense_name b
ON a.`account_id` = b.client_id
SET a.`adsense_account` = b.account_name
WHERE account_type = 'adsense'
AND a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;
UPDATE tablo.`unified_ad_unit_data` a
JOIN tmp_adsense_name b
ON a.`account_id` = b.client_id
SET a.`adsense_account` = b.account_name
WHERE account_type = 'adsense'
AND a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;
#Commercial region and publisher currency
DROP TEMPORARY TABLE IF EXISTS tmp_region_cur;
CREATE TEMPORARY TABLE tmp_region_cur (KEY(user_id))
SELECT
a.user_id,
a.commercial_region,
b.code
FROM rabota_db.`user` a
JOIN rabota_db.`currency` b
ON a.cur_id = b.currency_id;
UPDATE tablo.`unified_site_data` a
JOIN tmp_region_cur b
ON a.`user_id` = b.user_id
SET
a.`commercial_region` = b.commercial_region,
a.`currency` = b.code
WHERE a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;
UPDATE tablo.`unified_ad_unit_data` a
JOIN tmp_region_cur b
ON a.`user_id` = b.user_id
SET
a.`commercial_region` = b.commercial_region,
a.`currency` = b.code
WHERE a.`date` BETWEEN @start_date AND @end_date
AND a.`user_id` = @user_id;
# site category
UPDATE tablo.unified_site_data u
JOIN rabota_db.site s ON s.site_id = u.site_id
JOIN rabota_db.site_category c ON s.category_id = c.category_id
SET u.site_category = IFNULL(c.name, 'Unknown')
WHERE u.date BETWEEN @start_date AND @end_date
AND u.`user_id` = @user_id;

Add Comment

Please, Sign In to add comment