Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- iRev Destination level Plan
- -- Google Analytics data
- WITH google_analytics AS (
- SELECT
- YEAR,
- MONTH,
- brand,
- market,
- destination_code,
- -- @PRABA - LET'S COMMENT OUT SESSION_ID COUNT & USE THE NEXT LINE INSTEAD
- -- COUNT(DISTINCT session_id) sessions,
- -- @PRABA - TRY BELOW
- COUNT(DISTINCT CASE WHEN event_name = 'session_start' THEN concat(user_pseudo_id, CAST(event_timestamp AS string)) END) AS sessions
- COUNT(DISTINCT IF(quality_session_improved = TRUE, session_id, NULL)) qs,
- COUNT(DISTINCT IF(paid = TRUE AND quality_session_improved = TRUE, session_id, NULL)) paid_qs,
- FROM (
- SELECT
- YEAR,
- MONTH,
- brand,
- market,
- CASE
- WHEN country_code = 'PE' THEN 'PE-BO'
- WHEN country_code = 'BO' THEN 'PE-BO'
- WHEN country_code = 'AR' THEN 'PAT'
- WHEN country_code = 'CL' THEN 'PAT'
- ELSE country_code
- END AS destination_code,
- session_id,
- quality_session_improved,
- paid
- FROM `ventura-cluster.Planning.ga_events_destination`
- WHERE country_code IS NOT NULL
- )
- GROUP BY YEAR, MONTH, brand, market, destination_code
- ),
- -- ROAS data (irev forecasted and cpc)
- roas AS (
- SELECT
- YEAR,
- MONTH,
- brand,
- market,
- destination_code,
- SUM(irev) irev_forecasted,
- SUM(cpc) cpc_total
- FROM `ventura-cluster.Planning.roas`
- GROUP BY YEAR, MONTH, brand, market, destination_code
- ),
- -- Destination level plan
- destination_plan AS (
- SELECT
- CONCAT(tp.YEAR, tp.MONTH, tp.brand, tp.market, LOWER(tp.destination)) id,
- tp.YEAR,
- tp.MONTH,
- DATE(tp.YEAR, tp.MONTH, 1) DATE,
- tp.brand,
- tp.market,
- tp.destination,
- ANY_VALUE(tp.ts) ts, -- Same for all destinations
- ANY_VALUE(tp.ts_email) ts_email,
- -- # PLANNED DATA
- SUM(tp.irev_per_pax_plan) irev_per_pax_plan,
- SUM(tp.pax_plan) pax_plan,
- SUM(tp.irev_plan) irev_plan,
- SUM(tp.irev_plan_prev_month) irev_plan_prev_month,
- SUM(tp.irev_plan_adjusted) irev_plan_adjusted,
- ANY_VALUE(tp.performance_plan) performance_plan, -- Same performance for the destination of the trip
- SUM(tp.irev_prev_month_adjusted) irev_prev_month_adjusted,
- SUM(tp.roas_adjusted) roas_adjusted,
- -- # REAL DATA
- AVG(tp.avg_irev_per_booking) avg_irev_per_booking,
- AVG(tp.irev_per_pax) irev_per_pax,
- SUM(tp.irev) irev,
- SUM(tp.irev_prev_month) irev_prev_month,
- -- # Computed
- SUM(tp.irev_projected) irev_projected,
- FROM `ventura-cluster.Planning.ts_trip_plan_real` tp -- Trip Plan
- GROUP BY tp.YEAR, tp.MONTH, tp.brand, tp.market, tp.destination
- )
- SELECT
- dp.*,
- ga_prev_month.qs qs_prev_month,
- ga_prev_month.paid_qs paid_qs_prev_month,
- ga_prev_month.qs - ga_prev_month.paid_qs unpaid_qs_prev_month,
- roas_prev_month.irev_forecasted irev_forecatsed_prev_month,
- roas_prev_month.cpc_total cpc_prev_month
- FROM destination_plan dp
- LEFT JOIN google_analytics ga_prev_month
- ON
- dp.YEAR = ga_prev_month.YEAR AND
- dp.MONTH - 1 = ga_prev_month.MONTH AND
- dp.brand = ga_prev_month.brand AND
- dp.market = ga_prev_month.market AND
- dp.destination = ga_prev_month.destination_code
- LEFT JOIN roas roas_prev_month
- ON
- dp.YEAR = roas_prev_month.YEAR AND
- dp.MONTH - 1 = roas_prev_month.MONTH AND
- dp.brand = roas_prev_month.brand AND
- dp.market = roas_prev_month.market AND
- dp.destination = roas_prev_month.destination_code
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement