# Polaroid: BQ queries

## Table of Contents

[TOC]

### polaroid_four_tables_union

WITH polaroid_facebook AS(
SELECT 
    date_start AS gadate,
    IFNULL(impressions, 0) AS impressions,
    campaign_name AS campaign_name,
    IFNULL(clicks, 0) AS clicks,
    IFNULL(spend, 0) AS spend,
    'facebook / cpc' AS source_medium,
FROM `dept-data-management.adverity_dmnl_main_workspace.polaroid_facebookads_campaign_fordashboard` 
WHERE date_start >= '2021-01-01'
),
polaroid_bing AS (
SELECT 
    timeperiod AS gadate,
    IFNULL(impressions, 0) AS impressions,
    campaignname AS campaign_name,
    IFNULL(clicks, 0) AS clicks,
    CASE 
        WHEN campaignname LIKE '%us,%' THEN ROUND(spend*0.8365, 5)
        WHEN campaignname LIKE '%uk,%' THEN ROUND(spend*1.1581, 5)
        ELSE spend
    END AS spend,
    'bing / cpc' AS source_medium
FROM `dept-data-management.adverity_dmnl_main_workspace.polaroid_bingads_campaign`
WHERE timeperiod >= '2021-01-01' 
),
polaroid_google AS (
SELECT 
    day AS gadate,
    IFNULL(impressions, 0) AS impressions,
    campaign AS campaign_name,
    IFNULL(clicks, 0) AS clicks,
    IFNULL(cost, 0) AS spend,
    'google / cpc' AS source_medium,
FROM `dept-data-management.adverity_dmnl_main_workspace.polaroid_googleads_campaign`
WHERE day >= '2021-01-01'
),
polaroid_affiliate AS (
SELECT 
    conversion_date AS gadate,
    null AS impressions,
    null AS campaign_name,
    null AS clicks,
    IFNULL(commission*0.75, 0) AS spend,
    'affiliate' AS source_medium,
FROM `dept-data-management.adverity_dmnl_main_workspace.polaroid_partnerize_conversions` 
WHERE conversion_date >= '2021-01-01'
),
total_view AS (
SELECT * FROM polaroid_facebook
UNION ALL 
SELECT * FROM polaroid_bing
UNION ALL 
SELECT * FROM polaroid_google
UNION ALL 
SELECT 
    gadate,
    null AS impressions,
    null AS campaign_name,
    null AS clicks,
    spend,
    source_medium,
FROM polaroid_affiliate
)
SELECT *
FROM total_view

---

### polaroid_gacampaign_merged

WITH polaroid_gacampaign AS (
SELECT 
    gadate, 
    gasourcemedium,
    gacampaign,
    gasessions,
    gausers,
    gapageviews,
    gatransactions, 
    gatransactionrevenue
FROM `dept-data-management.adverity_dmnl_main_workspace.polaroid_googleanalytics_campaign` 
WHERE gadate >= "2021-01-01" AND gasourcemedium IN ('facebook / cpc', 'google / cpc', 'bing / cpc')
),
polaroid_union AS (
SELECT *
FROM `dept-data-management.polaroid_targets2021.polaroid_four_tables_union`
WHERE source_medium != 'affiliate' AND gadate >= "2021-01-01"
)
SELECT 
    COALESCE(polaroid_gacampaign.gadate, polaroid_union.gadate) AS date_daily,
    COALESCE(polaroid_gacampaign.gasourcemedium, polaroid_union.source_medium) AS source_medium,
    COALESCE(polaroid_gacampaign.gacampaign, polaroid_union.campaign_name) AS campaign_name,
    COALESCE(polaroid_gacampaign.gasessions, 0) AS sessions,
    COALESCE(polaroid_gacampaign.gausers, 0) AS users,
    COALESCE(polaroid_gacampaign.gapageviews, 0) AS pageviews,
    COALESCE(polaroid_gacampaign.gatransactions, 0) AS transactions,
    COALESCE(polaroid_gacampaign.gatransactionrevenue, 0) AS transactionsrevenue,
    COALESCE(polaroid_union.clicks, 0) AS clicks,
    COALESCE(polaroid_union.impressions, 0) AS impressions,
    COALESCE(polaroid_union.spend, 0) AS spend,
FROM polaroid_gacampaign 
FULL OUTER JOIN polaroid_union 
ON polaroid_gacampaign.gadate = polaroid_union.gadate AND polaroid_gacampaign.gasourcemedium = polaroid_union.source_medium AND polaroid_gacampaign.gacampaign = polaroid_union.campaign_name
ORDER BY polaroid_gacampaign.gadate DESC

---
### polaroid_merged_spend

WITH polarioid_daily AS (
SELECT *
FROM `dept-data-management.polaroid_targets2021.polaroid_view` 
),
four_tables AS (
SELECT 
    gadate,
    source_medium,
    IFNULL(SUM(spend), 0) as merged_spend
FROM `dept-data-management.polaroid_targets2021.polaroid_four_tables_union` 
GROUP BY 1, 2
)
SELECT 
    COALESCE(four_tables.gadate, polarioid_daily.date_daily) AS date_daily,
    polarioid_daily.month AS month,
    COALESCE(four_tables.source_medium, TRIM(polarioid_daily.source_medium)) AS source_medium,
    COALESCE(polarioid_daily.ga_transaction_revenue, 0) AS ga_transaction_revenue,
    COALESCE(polarioid_daily.month_days, 0) AS month_days,
    COALESCE(polarioid_daily.month_number, 0) AS month_number,
    IFNULL(four_tables.merged_spend, 0) AS merged_spend,
    COALESCE(polarioid_daily.target_spent, 0) AS target_spent,
    COALESCE(polarioid_daily.target_revenue, 0) AS target_revenue,
    COALESCE(polarioid_daily.daily_target_spent, 0) AS daily_target_spent,
    COALESCE(polarioid_daily.daily_target_revenue, 0) AS daily_target_revenue,
    COALESCE(polarioid_daily.Target_roas, 0) AS target_roas
FROM polarioid_daily
FULL OUTER JOIN four_tables
ON polarioid_daily.date_daily = four_tables.gadate AND TRIM(polarioid_daily.source_medium) = four_tables.source_medium
ORDER BY 1

---
### polaroid_revenue_targets_merged

WITH ga_data AS (
    SELECT
        FORMAT_DATE("%B", gadate) AS month,
        gasourcemedium,
        IFNULL(SUM(gatransactionrevenue), 0) AS transaction_revenue
    FROM `dept-data-management.adverity_dmnl_main_workspace.polaroid_analytics_revenue`
    WHERE gasourcemedium IN ('facebook / cpc', 'google / cpc', 'bing / cpc')
    GROUP BY 1, 2

    UNION ALL

    SELECT
        FORMAT_DATE("%B", gadate) AS month,
        "affiliate" AS gasourcemedium,
        IFNULL(SUM(gatransactionrevenue), 0) AS transaction_revenue
    FROM `dept-data-management.adverity_dmnl_main_workspace.polaroid_analytics_revenue`
    WHERE gasourcemedium LIKE "%affiliate"
    GROUP BY 1, 2
), csv_data AS (
    SELECT
        Month,
        Month_number,
        Month_days,
        Source_medium,
        Target_Revenue,
    FROM `dept-data-management.polaroid_targets2021.polaroid_targets2021_2022_daily`
)
SELECT
    COALESCE(ga_data.month, csv_data.Month) AS month,
    COALESCE(ga_data.gasourcemedium, TRIM(csv_data.Source_medium)) AS source_medium,
    COALESCE(ga_data.transaction_revenue, 0) AS ga_transaction_revenue,
    COALESCE(csv_data.Month_days, 0) AS month_days,
    COALESCE(csv_data.Month_number, 0) AS month_number,
    COALESCE(csv_data.Target_Revenue, 0) AS target_revenue
FROM ga_data
FULL OUTER JOIN csv_data
ON ga_data.month = csv_data.Month AND ga_data.gasourcemedium = TRIM(csv_data.Source_medium)
ORDER BY month_number

---
### polaroid_view

WITH ga_data AS (
    SELECT
        gadate,
        FORMAT_DATE("%B", gadate) AS month,
        gasourcemedium,
        IFNULL(SUM(gatransactionrevenue), 0) AS transaction_revenue
    FROM `dept-data-management.adverity_dmnl_main_workspace.polaroid_analytics_revenue`
    WHERE gasourcemedium IN ('facebook / cpc', 'google / cpc', 'bing / cpc')
    GROUP BY 1, 2, 3

    UNION ALL

    SELECT
        gadate,
        FORMAT_DATE("%B", gadate) AS month,
        "affiliate" AS gasourcemedium,
        IFNULL(SUM(gatransactionrevenue), 0) AS transaction_revenue
    FROM `dept-data-management.adverity_dmnl_main_workspace.polaroid_analytics_revenue`
    WHERE gasourcemedium LIKE "%affiliate"
    GROUP BY 1, 2, 3
), csv_data AS (
    SELECT
        Date AS csv_date, 
        Month,
        Month_number,
        Month_days,
        Source_medium,
        Target_Spent,
        Target_Revenue,
        Daily_Target_Spent,
        Daily_Target_Revenue,
        Target_ROAS
    FROM `dept-data-management.polaroid_targets2021.polaroid_targets2021_2022_daily`
)
SELECT
    COALESCE(ga_data.gadate, csv_data.csv_date) AS date_daily,
    COALESCE(ga_data.month, csv_data.Month) AS month,
    COALESCE(ga_data.gasourcemedium, TRIM(csv_data.Source_medium)) AS source_medium,
    COALESCE(ga_data.transaction_revenue, 0) AS ga_transaction_revenue,
    COALESCE(csv_data.Month_days, 0) AS month_days,
    COALESCE(csv_data.Month_number, 0) AS month_number,
    COALESCE(csv_data.Target_Spent, 0) AS target_spent,
    COALESCE(csv_data.Target_Revenue, 0) AS target_revenue,
    COALESCE(csv_data.Daily_Target_Spent, 0) AS daily_target_spent,
    COALESCE(csv_data.Daily_Target_Revenue, 0) AS daily_target_revenue,
    COALESCE(csv_data.Target_ROAS, 0) AS Target_roas
FROM ga_data
FULL OUTER JOIN csv_data
ON ga_data.gadate = csv_data.csv_date AND ga_data.month = csv_data.Month AND ga_data.gasourcemedium = TRIM(csv_data.Source_medium)
ORDER BY date_daily 

---