cpm coef for pub proposal

-- site category
SELECT s.site_id, s.url_domain, sc.name, s.category_id
FROM rabota_db.site s
LEFT JOIN rabota_db.site_category sc ON s.category_id = sc.category_id
WHERE s.site_id = 228243;

-- all categories
SELECT * FROM rabota_db.site_category sc;

-- region, currency
SELECT country, commercial_region, c.code FROM `user` u JOIN currency c ON u.cur_id = c.currency_id WHERE user_id = 113825;

-- coefficients -------------------------------------------------------------------------------------------------------

-- LAST FULL 12 months
SET @start_date = '2023-10-01';
SET @end_date = '2024-09-30';

-- CHANGE AS NEEDED
SET @region = 'italy';

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 1=1
AND e.`date` BETWEEN @start_date AND @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 1=1
AND e.`date` BETWEEN @start_date AND @end_date
AND e.source_cur_id = 6
AND e.destination_cur_id = 4
GROUP BY
e.`date`;

SELECT
YEAR(aus.date) AS `year`,
MONTH(aus.`date`) AS `month`,
ROUND(SUM(adv_expense)/SUM(external_view_count)*1000,8) AS eCPM_GBP,
ROUND(SUM(adv_expense*u.exrate)/SUM(external_view_count)*1000,8) AS eCPM_USD,
ROUND(SUM(adv_expense*e.exrate)/SUM(external_view_count)*1000,8) AS eCPM_EUR
FROM tablo.`ad_unit_stat` aus
JOIN tmp_usd_rate u #gbp to usd
ON u.`date` = aus.`date`
JOIN tmp_eur_rate e #gbp to eur
ON e.`date` = aus.`date`
WHERE
commercial_region = @region
AND aus.date BETWEEN @start_date AND @end_date
GROUP BY
YEAR(aus.date),
MONTH(aus.`date`)
ORDER BY MONTH(aus.`date`)
;

SELECT
-- YEAR(aus.date) AS `year`,
ROUND(SUM(adv_expense)/SUM(external_view_count)*1000,8) AS eCPM_GBP,
ROUND(SUM(adv_expense*u.exrate)/SUM(external_view_count)*1000,8) AS eCPM_USD,
ROUND(SUM(adv_expense*e.exrate)/SUM(external_view_count)*1000,8) AS eCPM_EUR
FROM tablo.`ad_unit_stat` aus
JOIN tmp_usd_rate u #gbp to usd
ON u.`date` = aus.`date`
JOIN tmp_eur_rate e #gbp to eur
ON e.`date` = aus.`date`
WHERE
commercial_region = @region
AND aus.date BETWEEN @start_date AND @end_date
-- GROUP BY
-- YEAR(aus.date)
;

-- coef based on adsense (rarely used)
SET @start_date = '2023-01-01';
SET @end_date = '2023-12-31';

SELECT YEAR(usd.date) AS `year`,
sum(usd.adv_expense_usd),
sum(usd.impressions),
sum(usd.adv_expense_usd)/sum(usd.impressions)*1000 AS eCPM_USD
FROM tablo.unified_site_data usd
WHERE 1=1
AND usd.site_id = 234790
AND usd.account_type = 'adsense'
AND usd.date BETWEEN @start_date AND @end_date
GROUP BY YEAR(usd.date)
;

SELECT YEAR(usd.date) AS `year`, MONTH(usd.`date`) AS `month`,
sum(usd.adv_expense_usd),
sum(usd.impressions),
sum(usd.adv_expense_usd)/sum(usd.impressions)*1000 AS eCPM_USD
FROM tablo.unified_site_data usd
WHERE 1=1
AND usd.site_id = 234790
AND usd.account_type = 'adsense'
AND usd.date BETWEEN @start_date AND @end_date
GROUP BY YEAR(usd.date), MONTH(usd.`date`)
;