Advertisement
prabapro

Untitled

May 27th, 2022
4,476
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 3.71 KB | None | 0 0
  1. -- iRev Destination level Plan
  2.  
  3. -- Google Analytics data
  4. WITH google_analytics AS (
  5.     SELECT
  6.         YEAR,
  7.         MONTH,
  8.         brand,
  9.         market,
  10.         destination_code,
  11.         -- @PRABA - LET'S COMMENT OUT SESSION_ID COUNT & USE THE NEXT LINE INSTEAD
  12.         -- COUNT(DISTINCT session_id) sessions,
  13.         -- @PRABA - TRY BELOW
  14.         COUNT(DISTINCT CASE WHEN event_name = 'session_start' THEN concat(user_pseudo_id, CAST(event_timestamp AS string)) END) AS sessions
  15.         COUNT(DISTINCT IF(quality_session_improved = TRUE, session_id, NULL)) qs,
  16.         COUNT(DISTINCT IF(paid = TRUE AND quality_session_improved = TRUE, session_id, NULL)) paid_qs,
  17.     FROM (
  18.         SELECT
  19.             YEAR,
  20.             MONTH,
  21.             brand,
  22.             market,
  23.             CASE
  24.                 WHEN country_code = 'PE' THEN 'PE-BO'
  25.                 WHEN country_code = 'BO' THEN 'PE-BO'
  26.                 WHEN country_code = 'AR' THEN 'PAT'
  27.                 WHEN country_code = 'CL' THEN 'PAT'
  28.                 ELSE country_code
  29.             END AS destination_code,
  30.             session_id,
  31.             quality_session_improved,
  32.             paid
  33.         FROM `ventura-cluster.Planning.ga_events_destination`
  34.         WHERE country_code IS NOT NULL
  35.     )
  36.     GROUP BY YEAR, MONTH, brand, market, destination_code
  37. ),
  38. -- ROAS data (irev forecasted and cpc)
  39. roas AS (
  40.     SELECT
  41.         YEAR,
  42.         MONTH,
  43.         brand,
  44.         market,
  45.         destination_code,
  46.         SUM(irev) irev_forecasted,
  47.         SUM(cpc) cpc_total
  48.     FROM `ventura-cluster.Planning.roas`
  49.     GROUP BY YEAR, MONTH, brand, market, destination_code
  50. ),
  51. -- Destination level plan
  52. destination_plan AS (
  53.     SELECT
  54.         CONCAT(tp.YEAR, tp.MONTH, tp.brand, tp.market, LOWER(tp.destination)) id,
  55.         tp.YEAR,
  56.         tp.MONTH,
  57.         DATE(tp.YEAR, tp.MONTH, 1) DATE,
  58.         tp.brand,
  59.         tp.market,
  60.         tp.destination,
  61.         ANY_VALUE(tp.ts) ts, -- Same for all destinations
  62.         ANY_VALUE(tp.ts_email) ts_email,
  63.         -- # PLANNED DATA
  64.         SUM(tp.irev_per_pax_plan) irev_per_pax_plan,
  65.         SUM(tp.pax_plan) pax_plan,
  66.         SUM(tp.irev_plan) irev_plan,
  67.         SUM(tp.irev_plan_prev_month) irev_plan_prev_month,
  68.         SUM(tp.irev_plan_adjusted) irev_plan_adjusted,
  69.         ANY_VALUE(tp.performance_plan) performance_plan, -- Same performance for the destination of the trip
  70.         SUM(tp.irev_prev_month_adjusted) irev_prev_month_adjusted,
  71.         SUM(tp.roas_adjusted) roas_adjusted,
  72.         -- # REAL DATA
  73.         AVG(tp.avg_irev_per_booking) avg_irev_per_booking,
  74.         AVG(tp.irev_per_pax) irev_per_pax,
  75.         SUM(tp.irev) irev,
  76.         SUM(tp.irev_prev_month) irev_prev_month,
  77.         -- # Computed
  78.         SUM(tp.irev_projected) irev_projected,
  79.     FROM `ventura-cluster.Planning.ts_trip_plan_real` tp -- Trip Plan
  80.     GROUP BY tp.YEAR, tp.MONTH, tp.brand, tp.market, tp.destination
  81. )
  82.  
  83. SELECT
  84.     dp.*,
  85.     ga_prev_month.qs qs_prev_month,
  86.     ga_prev_month.paid_qs paid_qs_prev_month,
  87.     ga_prev_month.qs - ga_prev_month.paid_qs unpaid_qs_prev_month,
  88.     roas_prev_month.irev_forecasted irev_forecatsed_prev_month,
  89.     roas_prev_month.cpc_total cpc_prev_month
  90. FROM destination_plan dp
  91. LEFT JOIN google_analytics ga_prev_month
  92. ON
  93.     dp.YEAR = ga_prev_month.YEAR AND
  94.     dp.MONTH - 1 = ga_prev_month.MONTH AND
  95.     dp.brand = ga_prev_month.brand AND
  96.     dp.market = ga_prev_month.market AND
  97.     dp.destination = ga_prev_month.destination_code
  98. LEFT JOIN roas roas_prev_month
  99. ON
  100.     dp.YEAR = roas_prev_month.YEAR AND
  101.     dp.MONTH - 1 = roas_prev_month.MONTH AND
  102.     dp.brand = roas_prev_month.brand AND
  103.     dp.market = roas_prev_month.market AND
  104.     dp.destination = roas_prev_month.destination_code
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement