WITH
YearMonthSeries AS (
WITH RECURSIVE
dates AS (
SELECT
DATE '2022-01-01' AS DATE
UNION ALL
SELECT
DATE_ADD(DATE, INTERVAL 1 MONTH)
FROM
dates
WHERE
DATE_ADD(DATE, INTERVAL 1 MONTH) <= CURRENT_DATE
)
SELECT
EXTRACT(
YEAR
FROM
DATE
) AS YEAR,
EXTRACT(
MONTH
FROM
DATE
) AS MONTH
FROM
dates
),
LatestCampaignName AS (
SELECT
campaign_id,
ARRAY_AGG(
campaign_name
ORDER BY
DATE(date_platform) DESC
LIMIT
1
) [OFFSET(0)] AS latest_campaign_name
FROM
dwh-production -352519.unified.unified_revenue
WHERE
revenue > 0
AND DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
ModifiedData AS (
SELECT
vertical,
transaction_type,
account_id,
campaign_id,
call_category,
sale_type,
sale_model,
revenue,
EXTRACT(
YEAR
FROM
date_platform
) AS YEAR,
EXTRACT(
MONTH
FROM
date_platform
) AS MONTH,
state,
timestamp_platform,
call_transfers,
paid_calls,
clicks,
lead_id,
CONCAT(
COALESCE(call_category, ''),
COALESCE(sale_type, '')
) AS transaction_subtype,
MAX(
CASE
WHEN revenue > 0 THEN DATE(date_platform)
ELSE NULL
END
) OVER (
PARTITION BY
campaign_id
) AS last_revenue_date,
MIN(
CASE
WHEN revenue > 0 THEN DATE(date_platform)
ELSE NULL
END
) OVER (
PARTITION BY
campaign_id
) AS first_purchasing_date,
SUM(
CASE
WHEN transaction_type = "Lead" THEN revenue
ELSE 0
END
) AS lead_revenue,
SUM(
CASE
WHEN transaction_type = "Call" THEN revenue
ELSE 0
END
) AS call_revenue,
SUM(
CASE
WHEN transaction_type = "Click" THEN revenue
ELSE 0
END
) AS click_revenue,
CASE
WHEN tier > 3 THEN 'T2'
ELSE CONCAT('T', CAST(tier AS STRING))
END AS modified_tier_string
FROM
dwh-production -352519.unified.unified_revenue
WHERE
DATE(date_platform) >= '2022-01-01'
AND (
sale_model <> "Open Exchange"
OR campaign_name <> "Excel Impact Proxy (Affiliate Routing)"
)
GROUP BY
vertical,
transaction_type,
campaign_id,
call_category,
sale_type,
sale_model,
revenue,
state,
YEAR,
MONTH,
timestamp_platform,
call_transfers,
paid_calls,
clicks,
lead_id,
modified_tier_string,
account_id
),
PurchaseFlags AS (
SELECT
campaign_id,
MAX(
CASE
WHEN media_type IN (
"Network Partner Calls",
"Network Partner Clicks",
"Network Partners"
)
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_network_partner,
MAX(
CASE
WHEN media_type = "Traffic Partners"
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_traffic_partner,
MAX(
CASE
WHEN media_type IN (
"Contextual",
"Display",
"Internal Email",
"Native",
"SEM",
"SEO",
"SMS",
"Social"
)
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_internal_traffic
FROM
dwh-production -352519.unified.unified_revenue
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
FirstDate AS (
SELECT
campaign_id,
MIN(
CASE
WHEN revenue > 0 THEN DATE(date_platform)
ELSE NULL
END
) AS first_revenue_date
FROM
dwh-production -352519.unified.unified_revenue
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
NewlyLaunched AS (
SELECT
campaign_id,
CASE
WHEN DATE_DIFF(CURRENT_DATE, first_revenue_date, DAY) <= 90 THEN TRUE
ELSE FALSE
END AS newly_launched_campaigns
FROM
FirstDate
),
DaysCount AS (
SELECT
campaign_id,
COUNT(
DISTINCT CASE
WHEN revenue > 0 THEN DATE(date_platform)
END
) AS purchasing_days,
COUNT(
DISTINCT CASE
WHEN (
COALESCE(call_transfers, 0) + COALESCE(clicks, 0) + COALESCE(
CASE
WHEN REGEXP_CONTAINS(lead_id, r'^-?[0-9]+$') THEN CAST(lead_id AS INT64)
ELSE NULL
END,
0
)
) > 0 THEN DATE(date_platform)
END
) AS active_days
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
StateStats AS (
WITH
DailyActiveStates AS (
SELECT
campaign_id,
DATE(date_platform) AS active_date,
COUNT(DISTINCT state) AS num_states
FROM
ModifiedData
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id,
DATE(date_platform)
)
SELECT
campaign_id,
AVG(num_states) AS avg_active_states,
MAX(
CASE
WHEN state = 'TX'
AND DATE_DIFF(CURRENT_DATE, DATE(date_platform), DAY) <= 7 THEN TRUE
ELSE FALSE
END
) AS TX_currently_active,
MAX(
CASE
WHEN state = 'CA'
AND DATE_DIFF(CURRENT_DATE, DATE(date_platform), DAY) <= 7 THEN TRUE
ELSE FALSE
END
) AS CA_currently_active,
MAX(
CASE
WHEN state = 'FL'
AND DATE_DIFF(CURRENT_DATE, DATE(date_platform), DAY) <= 7 THEN TRUE
ELSE FALSE
END
) AS FL_currently_active,
MAX(
CASE
WHEN state = 'NY'
AND DATE_DIFF(CURRENT_DATE, DATE(date_platform), DAY) <= 7 THEN TRUE
ELSE FALSE
END
) AS NY_currently_active
FROM
ModifiedData
LEFT JOIN DailyActiveStates USING (campaign_id)
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
TimeStats AS (
SELECT
campaign_id,
COUNT(
DISTINCT CASE
WHEN revenue > 0 THEN EXTRACT(
HOUR
FROM
timestamp_platform
)
END
) AS hours_active_count,
MAX(
CASE
WHEN EXTRACT(
DAYOFWEEK
FROM
DATE(timestamp_platform)
) IN (1, 7)
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_weekends,
MAX(
CASE
WHEN (
(
EXTRACT(
HOUR
FROM
timestamp_platform
) BETWEEN 20 AND 23
)
OR (
EXTRACT(
HOUR
FROM
timestamp_platform
) BETWEEN 0 AND 7
)
)
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_off_hours,
MAX(
CASE
WHEN revenue > 0 THEN TRUE
ELSE FALSE
END
) AS currently_active
FROM
ModifiedData
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
StateAggregation AS (
WITH
DistinctStates AS (
SELECT DISTINCT
campaign_id,
state
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
)
SELECT
campaign_id,
STRING_AGG(state, ';') AS states_current_month
FROM
DistinctStates
GROUP BY
campaign_id
),
RevenueByMediaType AS (
SELECT
campaign_id,
SUM(
CASE
WHEN media_type IN (
"Network Partner Calls",
"Network Partner Clicks",
"Network Partners"
) THEN revenue
ELSE 0
END
) AS revenue_network_partner_currentMonth,
SUM(
CASE
WHEN media_type = "Traffic Partners" THEN revenue
ELSE 0
END
) AS revenue_traffic_partner_currentMonth,
SUM(
CASE
WHEN media_type IN (
"Contextual",
"Display",
"Internal Email",
"Native",
"SEM",
"SEO",
"SMS",
"Social"
) THEN revenue
ELSE 0
END
) AS revenue_internal_traffic_currentMonth,
SUM(revenue) AS total_revenue_current_month
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
CampaignRanking AS (
SELECT
campaign_id,
ROW_NUMBER() OVER (
ORDER BY
SUM(revenue) DESC,
campaign_id
) AS MA_Campaign_Rank
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
)
SELECT
STRING_AGG(DISTINCT m.vertical, ';') AS verticals,
STRING_AGG(DISTINCT m.transaction_type, ';') AS transaction_types,
lcn.latest_campaign_name,
m.campaign_id,
m.account_id,
STRING_AGG(DISTINCT m.modified_tier_string, ';') AS aggregated_tiers,
STRING_AGG(
DISTINCT CONCAT(
COALESCE(call_category, ''),
COALESCE(sale_type, '')
),
';'
) AS transaction_subtype,
m.sale_model,
SUM(m.call_transfers) AS transferred_call_volume_currentMonth,
SUM(m.paid_calls) AS paid_call_volume_currentMonth,
SUM(m.clicks) AS click_volume_currentMonth,
CASE
WHEN COUNT(m.lead_id) = 0 THEN NULL
ELSE COUNT(m.lead_id)
END AS lead_volume_currentMonth,
COALESCE(CAST(SUM(m.call_transfers) AS INT64), 0) + COALESCE(CAST(SUM(m.clicks) AS INT64), 0) + COALESCE(COUNT(m.lead_id), 0) AS overall_volume_value_currentMonth,
SUM(m.revenue) AS overall_revenue_currentMonth,
CASE
WHEN (
COALESCE(CAST(SUM(m.call_transfers) AS INT64), 0) + COALESCE(CAST(SUM(m.clicks) AS INT64), 0) + COUNT(m.lead_id)
) = 0 THEN NULL
ELSE SUM(m.revenue) / (
COALESCE(CAST(SUM(m.call_transfers) AS INT64), 0) + COALESCE(CAST(SUM(m.clicks) AS INT64), 0) + COUNT(m.lead_id)
)
END AS RPTransaction_currentMonth,
FORMAT_DATE("%m/%d/%Y", m.last_revenue_date) AS last_purchasing_date,
FORMAT_DATE("%m/%d/%Y", m.first_purchasing_date) AS first_purchasing_date,
DATE_DIFF(
m.last_revenue_date,
m.first_purchasing_date,
MONTH
) AS months_between,
d.purchasing_days,
d.active_days,
ss.avg_active_states,
ss.TX_currently_active,
ss.CA_currently_active,
ss.FL_currently_active,
ss.NY_currently_active,
ts.hours_active_count,
ts.purchasing_weekends,
ts.purchasing_off_hours,
ts.currently_active,
sa.states_current_month,
fd.first_revenue_date,
nl.newly_launched_campaigns,
COALESCE(
NULLIF(SUM(r.revenue_network_partner_currentMonth), 0) / NULLIF(SUM(r.total_revenue_current_month), 0),
0
) AS percent_network_partner_currentMonth,
COALESCE(
NULLIF(SUM(r.revenue_traffic_partner_currentMonth), 0) / NULLIF(SUM(r.total_revenue_current_month), 0),
0
) AS percent_traffic_partner_currentMonth,
COALESCE(
NULLIF(SUM(r.revenue_internal_traffic_currentMonth), 0) / NULLIF(SUM(r.total_revenue_current_month), 0),
0
) AS percent_internal_traffic_currentMonth,
cr.MA_Campaign_Rank,
pf.purchasing_network_partner,
pf.purchasing_traffic_partner,
pf.purchasing_internal_traffic,
SUM(m.lead_revenue) AS lead_revenue_currentMonth,
SUM(m.call_revenue) AS call_revenue_currentMonth,
SUM(m.click_revenue) AS click_revenue_currentMonth,
COALESCE(
NULLIF(SUM(m.call_revenue), 0) / NULLIF(SUM(m.call_transfers), 0),
0
) AS RPTransfer_currentMonth,
COALESCE(
NULLIF(SUM(m.call_revenue), 0) / NULLIF(SUM(m.paid_calls), 0),
0
) AS RPQCalls_currentMonth,
COALESCE(
NULLIF(SUM(m.click_revenue), 0) / NULLIF(SUM(m.clicks), 0),
0
) AS RPClick_currentMonth,
COALESCE(
NULLIF(SUM(m.lead_revenue), 0) / NULLIF(COUNT(m.lead_id), 0),
0
) AS RPLead_currentMonth
FROM
ModifiedData m
LEFT JOIN LatestCampaignName lcn ON m.campaign_id = lcn.campaign_id
LEFT JOIN DaysCount d ON m.campaign_id = d.campaign_id
LEFT JOIN StateStats ss ON m.campaign_id = ss.campaign_id
LEFT JOIN TimeStats ts ON m.campaign_id = ts.campaign_id
LEFT JOIN RevenueByMediaType r ON m.campaign_id = r.campaign_id
LEFT JOIN CampaignRanking cr ON m.campaign_id = cr.campaign_id
LEFT JOIN PurchaseFlags pf ON m.campaign_id = pf.campaign_id
LEFT JOIN FirstDate fd ON m.campaign_id = fd.campaign_id
LEFT JOIN NewlyLaunched nl ON m.campaign_id = nl.campaign_id
LEFT JOIN StateAggregation sa ON m.campaign_id = sa.campaign_id
GROUP BY
lcn.latest_campaign_name,
m.campaign_id,
m.account_id,
m.sale_model,
m.last_revenue_date,
m.first_purchasing_date,
d.purchasing_days,
d.active_days,
ss.avg_active_states,
ss.TX_currently_active,
ss.CA_currently_active,
ss.FL_currently_active,
ss.NY_currently_active,
ts.hours_active_count,
ts.purchasing_weekends,
ts.purchasing_off_hours,
ts.currently_active,
fd.first_revenue_date,
nl.newly_launched_campaigns,
pf.purchasing_network_partner,
pf.purchasing_traffic_partner,
pf.purchasing_internal_traffic,
cr.MA_Campaign_Rank,
sa.states_current_month,
CrossJoinedData AS (
SELECT
*
FROM
ModifiedData,
YearMonthSeries
WHERE
EXTRACT(
YEAR
FROM
ModifiedData.date_platform
) = YearMonthSeries.year
AND EXTRACT(
MONTH
FROM
ModifiedData.date_platform
) = YearMonthSeries.month
),
LatestCampaignName AS (
SELECT
campaign_id,
ARRAY_AGG(
campaign_name
ORDER BY
DATE(date_platform) DESC
LIMIT
1
) [OFFSET(0)] AS latest_campaign_name
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
revenue > 0
AND DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
ModifiedData AS (
SELECT
vertical,
transaction_type,
account_id,
campaign_id,
call_category,
sale_type,
sale_model,
revenue,
EXTRACT(
YEAR
FROM
date_platform
),
EXTRACT(
MONTH
FROM
date_platform
),
state,
timestamp_platform,
call_transfers,
paid_calls,
clicks,
lead_id,
CONCAT(
COALESCE(call_category, ''),
COALESCE(sale_type, '')
) AS transaction_subtype,
MAX(
CASE
WHEN revenue > 0 THEN DATE(date_platform)
ELSE NULL
END
) OVER (
PARTITION BY
campaign_id
) AS last_revenue_date,
MIN(
CASE
WHEN revenue > 0 THEN DATE(date_platform)
ELSE NULL
END
) OVER (
PARTITION BY
campaign_id
) AS first_purchasing_date,
SUM(
CASE
WHEN transaction_type = "Lead" THEN revenue
ELSE 0
END
) AS lead_revenue,
SUM(
CASE
WHEN transaction_type = "Call" THEN revenue
ELSE 0
END
) AS call_revenue,
SUM(
CASE
WHEN transaction_type = "Click" THEN revenue
ELSE 0
END
) AS click_revenue,
CASE
WHEN tier > 3 THEN 'T2'
ELSE CONCAT('T', CAST(tier AS STRING))
END AS modified_tier_string
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
AND (
sale_model <> "Open Exchange"
OR campaign_name <> "Excel Impact Proxy (Affiliate Routing)"
)
GROUP BY
vertical,
transaction_type,
campaign_id,
call_category,
sale_type,
sale_model,
revenue,
state,
EXTRACT(
YEAR
FROM
date_platform
),
EXTRACT(
MONTH
FROM
date_platform
),
timestamp_platform,
call_transfers,
paid_calls,
clicks,
lead_id,
modified_tier_string,
account_id
),
PurchaseFlags AS (
SELECT
campaign_id,
MAX(
CASE
WHEN media_type IN (
"Network Partner Calls",
"Network Partner Clicks",
"Network Partners"
)
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_network_partner,
MAX(
CASE
WHEN media_type = "Traffic Partners"
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_traffic_partner,
MAX(
CASE
WHEN media_type IN (
"Contextual",
"Display",
"Internal Email",
"Native",
"SEM",
"SEO",
"SMS",
"Social"
)
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_internal_traffic
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
FirstDate AS (
SELECT
campaign_id,
MIN(
CASE
WHEN revenue > 0 THEN DATE(date_platform)
END
) AS first_revenue_date
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
NewlyLaunched AS (
SELECT
campaign_id,
CASE
WHEN DATE_DIFF(CURRENT_DATE, first_revenue_date, DAY) <= 90 THEN TRUE
ELSE FALSE
END AS newly_launched_campaigns
FROM
FirstDate
),
DaysCount AS (
SELECT
campaign_id,
COUNT(
DISTINCT CASE
WHEN revenue > 0 THEN DATE(date_platform)
END
) AS purchasing_days,
COUNT(
DISTINCT CASE
WHEN (
COALESCE(call_transfers, 0) + COALESCE(clicks, 0) + COALESCE(
CASE
WHEN REGEXP_CONTAINS(lead_id, r'^-?[0-9]+$') THEN CAST(lead_id AS INT64)
ELSE NULL
END,
0
)
) > 0 THEN DATE(date_platform)
END
) AS active_days
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
StateStats AS (
WITH
DailyActiveStates AS (
SELECT
campaign_id,
DATE(date_platform) AS active_date,
COUNT(DISTINCT state) AS num_states
FROM
CrossJoinedData
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id,
DATE(date_platform)
)
SELECT
campaign_id,
AVG(num_states) AS avg_active_states,
MAX(
CASE
WHEN state = 'TX'
AND DATE_DIFF(CURRENT_DATE, DATE(date_platform), DAY) <= 7 THEN TRUE
ELSE FALSE
END
) AS TX_currently_active,
MAX(
CASE
WHEN state = 'CA'
AND DATE_DIFF(CURRENT_DATE, DATE(date_platform), DAY) <= 7 THEN TRUE
ELSE FALSE
END
) AS CA_currently_active,
MAX(
CASE
WHEN state = 'FL'
AND DATE_DIFF(CURRENT_DATE, DATE(date_platform), DAY) <= 7 THEN TRUE
ELSE FALSE
END
) AS FL_currently_active,
MAX(
CASE
WHEN state = 'NY'
AND DATE_DIFF(CURRENT_DATE, DATE(date_platform), DAY) <= 7 THEN TRUE
ELSE FALSE
END
) AS NY_currently_active
FROM
CrossJoinedData
LEFT JOIN DailyActiveStates USING (campaign_id)
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
TimeStats AS (
SELECT
campaign_id,
COUNT(
DISTINCT CASE
WHEN revenue > 0 THEN EXTRACT(
HOUR
FROM
timestamp_platform
)
END
) AS hours_active_count,
MAX(
CASE
WHEN EXTRACT(
DAYOFWEEK
FROM
DATE(timestamp_platform)
) IN (1, 7)
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_weekends,
MAX(
CASE
WHEN (
(
EXTRACT(
HOUR
FROM
timestamp_platform
) BETWEEN 20 AND 23
)
OR (
EXTRACT(
HOUR
FROM
timestamp_platform
) BETWEEN 0 AND 7
)
)
AND revenue > 0 THEN TRUE
ELSE FALSE
END
) AS purchasing_off_hours,
MAX(
CASE
WHEN revenue > 0 THEN TRUE
ELSE FALSE
END
) AS currently_active
FROM
CrossJoinedData
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
StateAggregation AS (
WITH
DistinctStates AS (
SELECT DISTINCT
campaign_id,
state
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
)
SELECT
campaign_id,
STRING_AGG(state, ';') AS states_current_month
FROM
DistinctStates
GROUP BY
campaign_id
),
RevenueByMediaType AS (
SELECT
campaign_id,
SUM(
CASE
WHEN media_type IN (
"Network Partner Calls",
"Network Partner Clicks",
"Network Partners"
) THEN revenue
ELSE 0
END
) AS revenue_network_partner_currentMonth,
SUM(
CASE
WHEN media_type = "Traffic Partners" THEN revenue
ELSE 0
END
) AS revenue_traffic_partner_currentMonth,
SUM(
CASE
WHEN media_type IN (
"Contextual",
"Display",
"Internal Email",
"Native",
"SEM",
"SEO",
"SMS",
"Social"
) THEN revenue
ELSE 0
END
) AS revenue_internal_traffic_currentMonth,
SUM(revenue) AS total_revenue_current_month
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
),
CampaignRanking AS (
SELECT
campaign_id,
ROW_NUMBER() OVER (
ORDER BY
SUM(revenue) DESC,
campaign_id
) AS MA_Campaign_Rank
FROM
`dwh-production-352519.unified.unified_revenue`
WHERE
DATE(date_platform) >= '2022-01-01'
GROUP BY
campaign_id
)
SELECT
STRING_AGG(DISTINCT m.vertical, ';') AS verticals,
STRING_AGG(DISTINCT m.transaction_type, ';') AS transaction_types,
lcn.latest_campaign_name,
m.campaign_id,
m.account_id,
STRING_AGG(DISTINCT m.modified_tier_string, ';') AS aggregated_tiers,
STRING_AGG(
DISTINCT CONCAT(
COALESCE(call_category, ''),
COALESCE(sale_type, '')
),
';'
) AS transaction_subtype,
m.sale_model,
SUM(m.call_transfers) AS transferred_call_volume_currentMonth,
SUM(m.paid_calls) AS paid_call_volume_currentMonth,
SUM(m.clicks) AS click_volume_currentMonth,
CASE
WHEN COUNT(m.lead_id) = 0 THEN NULL
ELSE COUNT(m.lead_id)
END AS lead_volume_currentMonth,
COALESCE(CAST(SUM(m.call_transfers) AS INT64), 0) + COALESCE(CAST(SUM(m.clicks) AS INT64), 0) + COALESCE(COUNT(m.lead_id), 0) AS overall_volume_value_currentMonth,
SUM(m.revenue) AS overall_revenue_currentMonth,
CASE
WHEN (
COALESCE(CAST(SUM(m.call_transfers) AS INT64), 0) + COALESCE(CAST(SUM(m.clicks) AS INT64), 0) + COUNT(m.lead_id)
) = 0 THEN NULL
ELSE SUM(m.revenue) / (
COALESCE(CAST(SUM(m.call_transfers) AS INT64), 0) + COALESCE(CAST(SUM(m.clicks) AS INT64), 0) + COUNT(m.lead_id)
)
END AS RPTransaction_currentMonth,
FORMAT_DATE("%m/%d/%Y", m.last_revenue_date) AS last_purchasing_date,
FORMAT_DATE("%m/%d/%Y", m.first_purchasing_date) AS first_purchasing_date,
DATE_DIFF(
m.last_revenue_date,
m.first_purchasing_date,
MONTH
) AS months_between,
d.purchasing_days,
d.active_days,
ss.avg_active_states,
ss.TX_currently_active,
ss.CA_currently_active,
ss.FL_currently_active,
ss.NY_currently_active,
ts.hours_active_count,
ts.purchasing_weekends,
ts.purchasing_off_hours,
ts.currently_active,
sa.states_current_month,
fd.first_revenue_date,
nl.newly_launched_campaigns,
COALESCE(
NULLIF(SUM(r.revenue_network_partner_currentMonth), 0) / NULLIF(SUM(r.total_revenue_current_month), 0),
0
) AS percent_network_partner_currentMonth,
COALESCE(
NULLIF(SUM(r.revenue_traffic_partner_currentMonth), 0) / NULLIF(SUM(r.total_revenue_current_month), 0),
0
) AS percent_traffic_partner_currentMonth,
COALESCE(
NULLIF(SUM(r.revenue_internal_traffic_currentMonth), 0) / NULLIF(SUM(r.total_revenue_current_month), 0),
0
) AS percent_internal_traffic_currentMonth,
cr.MA_Campaign_Rank,
pf.purchasing_network_partner,
pf.purchasing_traffic_partner,
pf.purchasing_internal_traffic,
SUM(m.lead_revenue) AS lead_revenue_currentMonth,
SUM(m.call_revenue) AS call_revenue_currentMonth,
SUM(m.click_revenue) AS click_revenue_currentMonth,
COALESCE(
NULLIF(SUM(m.call_revenue), 0) / NULLIF(SUM(m.call_transfers), 0),
0
) AS RPTransfer_currentMonth,
COALESCE(
NULLIF(SUM(m.call_revenue), 0) / NULLIF(SUM(m.paid_calls), 0),
0
) AS RPQCalls_currentMonth,
COALESCE(
NULLIF(SUM(m.click_revenue), 0) / NULLIF(SUM(m.clicks), 0),
0
) AS RPClick_currentMonth,
COALESCE(
NULLIF(SUM(m.lead_revenue), 0) / NULLIF(COUNT(m.lead_id), 0),
0
) AS RPLead_currentMonth
FROM
CrossJoinedData m
LEFT JOIN LatestCampaignName lcn ON m.campaign_id = lcn.campaign_id
LEFT JOIN DaysCount d ON m.campaign_id = d.campaign_id
LEFT JOIN StateStats ss ON m.campaign_id = ss.campaign_id
LEFT JOIN TimeStats ts ON m.campaign_id = ts.campaign_id
LEFT JOIN RevenueByMediaType r ON m.campaign_id = r.campaign_id
LEFT JOIN CampaignRanking cr ON m.campaign_id = cr.campaign_id
LEFT JOIN PurchaseFlags pf ON m.campaign_id = pf.campaign_id
LEFT JOIN FirstDate fd ON m.campaign_id = fd.campaign_id
LEFT JOIN NewlyLaunched nl ON m.campaign_id = nl.campaign_id
LEFT JOIN StateAggregation sa ON m.campaign_id = sa.campaign_id
GROUP BY
lcn.latest_campaign_name,
m.campaign_id,
m.account_id,
m.sale_model,
m.last_revenue_date,
m.first_purchasing_date,
d.purchasing_days,
d.active_days,
ss.avg_active_states,
ss.TX_currently_active,
ss.CA_currently_active,
ss.FL_currently_active,
ss.NY_currently_active,
ts.hours_active_count,
ts.purchasing_weekends,
ts.purchasing_off_hours,
ts.currently_active,
fd.first_revenue_date,
nl.newly_launched_campaigns,
pf.purchasing_network_partner,
pf.purchasing_traffic_partner pf.purchasing_internal_traffic cr.MA_Campaign_Rank sa.states_current_month
Telegram: DB developers channel