/*CASE
WHEN(creative_name like '%Video_Electronics%') then 'Video_Electronics'
WHEN(creative_name like '%Video_HomeDecor%') then 'Video_HomeDecor'
WHEN(creative_name like '%Video_Kids%') then 'Video_Kids'
WHEN(creative_name like '%Video_MenFashion%' | creative_name like
'%Video_MenFashion-1to4%' | creative_name like '%Video_MenFashion-Branding1point1%'
|
creative_name like '%Video_MenFashion-Branding1point2%' | creative_name
like '%Video_MenFashion-Onlybranding%') then 'Video_MenFashion'
WHEN(creative_name like '%Video_Mengrooming%') then 'Video_Mengrooming'
WHEN(creative_name like '%Video_Saleiscoming-1to4%' | creative_name like )
then 'Video_Saleiscoming'
WHEN(creative_name like '%Video_Saleislive%') then 'Video_Saleislive'
WHEN(creative_name like '%Video_WomenFashion%') then 'Video_WomenFashion'
END AS creative_name,*/
******************************************************************
F2C
Report Parameters
Parameter Configuration "InMobi_Campaign_ids:INT64[]:52109321
Gaming_Campaign_ids:INT64[]:52131459
XMP_Campaign_ids:INT64[]:52105406
Conversion_ids:INT64[]:12270436
Lookback_days:INT64:15"
Filtered Row Summary Configuration "impressions:SUM
unique_reach:SUM
Conversions:SUM"
Query Text "CREATE TABLE YT_Impressions AS (
SELECT
device_id_md5,
1 AS imp,
query_id.time_usec AS brand_time,
FROM adh.dv360_youtube_impressions_rdid
);
CREATE TABLE DT_Impressions AS (
SELECT
device_id_md5,
1 AS imp,
event.event_time AS brand_time,
FROM adh.dv360_dt_impressions_rdid
);
CREATE TABLE Conversions AS (
SELECT
device_id_md5,
1 AS conv,
event.event_time AS conv_time,
FROM adh.cm_dt_activities_attributed_rdid
WHERE event.activity_id IN UNNEST(@Conversion_ids)
);
CREATE TABLE Union_impressions AS (
SELECT * FROM tmp.YT_Impressions
UNION ALL
SELECT * FROM tmp.DT_Impressions
);
CREATE TABLE Overall_Impressions AS (
SELECT
device_id_md5,
SUM(imp) AS impressions_frequency,
MIN(brand_time) AS brand_time,
FROM tmp.Union_impressions
GROUP BY 1
);
CREATE TABLE Conversions_Imps AS (
SELECT
device_id_md5,
impressions_frequency,
brand_time,
conv_time,
conv,
FROM tmp.Overall_Impressions
LEFT JOIN [Link] USING(device_id_md5)
);
CREATE TABLE Time_Conversion AS (
SELECT
device_id_md5,
impressions_frequency,
CASE WHEN brand_time <= conv_time THEN conv else 0 END AS conversions,
FROM tmp.Conversions_Imps
);
SELECT
CASE WHEN impressions_frequency >=10 THEN ""10+"" ELSE
CAST(impressions_frequency AS STRING) END AS Frequency,
COUNT(DISTINCT device_id_md5) AS Reach,
SUM(impressions_frequency) AS Impressions,
COUNT(device_id_md5) AS devices,
SUM(conversions) AS conversions,
FROM tmp.Time_Conversion
GROUP BY 1"
*********************************************************************
T2C
"CREATE TABLE YT_Impressions AS (
SELECT
device_id_md5,
1 AS imp,
query_id.time_usec AS brand_time,
CAST(FORMAT_TIMESTAMP('%F', TIMESTAMP_MICROS(query_id.time_usec),
@time_zone) AS DATE) AS day_i,
FROM adh.dv360_youtube_impressions_rdid
);
CREATE TABLE DT_Impressions AS (
SELECT
device_id_md5,
1 AS imp,
event.event_time AS brand_time,
CAST(FORMAT_TIMESTAMP('%F', TIMESTAMP_MICROS(event.event_time), @time_zone)
AS DATE) AS day_i,
FROM adh.dv360_dt_impressions_rdid
);
CREATE TABLE Conversions AS (
SELECT
device_id_md5,
1 AS conv,
event.event_time AS conv_time,
CAST(FORMAT_TIMESTAMP('%F', TIMESTAMP_MICROS(event.event_time), @time_zone)
AS DATE) AS day_c,
FROM adh.cm_dt_activities_events_rdid
WHERE event.activity_id IN UNNEST(@Conversion_ids)
);
CREATE TABLE Union_impressions AS (
SELECT * FROM tmp.YT_Impressions
UNION ALL
SELECT * FROM tmp.DT_Impressions
);
CREATE TABLE Overall_Impressions AS (
SELECT
device_id_md5,
SUM(imp) AS impressions_frequency,
MIN(brand_time) AS first_impression_time,
MAX(brand_time) AS last_impression_time,
MIN(day_i) AS first_imp_day,
MAX(day_i) AS last_imp_day,
FROM tmp.Union_impressions
GROUP BY 1
);
CREATE TABLE Conversions_Imps AS (
SELECT
device_id_md5,
impressions_frequency,
first_impression_time,
last_impression_time,
first_imp_day,
last_imp_day,
conv_time,
conv,
day_c,
FROM tmp.Overall_Impressions
LEFT JOIN [Link] USING(device_id_md5)
);
CREATE TABLE Time_Conversion AS (
SELECT
device_id_md5,
impressions_frequency,
conv AS conversions,
CASE WHEN first_impression_time <= conv_time THEN conv else 0 END AS
conversions2,
#(conv_time - first_impression_time)/(1e6*60*60*24) AS Days2C,
DATE_DIFF(day_c,first_imp_day,DAY) AS Days2C,
FROM tmp.Conversions_Imps
);
SELECT
Days2C,
COUNT(DISTINCT device_id_md5) AS Reach,
SUM(impressions_frequency) AS Impressions,
COUNT(device_id_md5) AS devices,
SUM(conversions) AS conversions,
SUM(conversions2) AS conversions2,
FROM tmp.Time_Conversion
GROUP BY 1"