SHARE
TWEET

Untitled

a guest Jul 18th, 2019 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ------------------------ Weekly
  2. SELECT
  3.   ad_unit                                                     AS "Adunit",
  4.   application_id                                              AS "Application id",
  5.   instance_id                                                 AS "Instance id",
  6.   device_country                                              AS "Country",
  7.   ROUND(1000.0 * flat_net_value, 2)                           AS "Flat eCPM",
  8.   split_part(target_margin_latest, '=', 2)                    AS "Target Margin Setting",
  9.   case when target_margin_change > 1 then 'Yes' else 'No' end AS "Target Margin Change",
  10.   fill_rate_target                                            AS "DS min Fill Setting",
  11.   min_request_date                                            AS "Date",
  12.   requests                                                    AS "Requests",
  13.   ds_no_eligible_ads                                          AS "No Eligible Ads",
  14.   ds_no_positive_ev                                           AS "Eligible Ads with NO Positive EV",
  15.   ds_fill                                                     AS "Eligible Ads with Positive EV",
  16.   ds_fill_pct                                                 AS "DS Fill Rate",
  17.   impressions                                                 AS "Impressions",
  18.   impressions_flat_los                                        AS "Losing impressions",
  19.   impressions_promote                                         AS "Promoted impressions",
  20.   promote_pct                                                 AS "Promote Rate",
  21.   expected_revenue                                            AS "Predicted Revenue",
  22.   revenue                                                     AS "Actual Revenue",
  23.   profit                                                      AS "Actual Profit",
  24.   revenue_calibration                                         AS "Revenue Calibration",
  25.   actual_margin                                               AS "Actual Margin",
  26.   --Business Reason
  27.   CASE
  28.   when reason_code = 10 then 'Target Margin is not set, serving NLI'
  29.   when reason_code = 20 then 'Margin gap within ±5% is normal'
  30.   when reason_code = 30 then 'Not enough daily impressions, need at least 2500 impressions per day'
  31.   when reason_code = 40 then 'Not enough data accross the day, more than 20% of impressions were served NLI'
  32.   when reason_code = 51 then 'Model Calibration'
  33.   when reason_code = 52 then 'Model Calibration'
  34.   when reason_code = 61 then 'Model Prediction - Received more revenue than expected'
  35.   when reason_code = 62 then 'Model Prediction - Received less revenue than expected'
  36.   when reason_code = 70 then 'Allocator Calibration'
  37.   when reason_code = 81 then 'Providing maximum fill on the given flat / No more room to add losing impressions'
  38.   when reason_code = 82 then 'More losing impressions by promotion than the allocator served losing impressions'
  39.   when reason_code = 83 then 'Target Margin too high, working almost NLI on all impressions'
  40.   when reason_code = 90
  41.     then 'Allocator Prediction - Received more revenue than expected and serving lots of losing impressions'
  42.   when reason_code = 100 then 'Allocator Calibration'
  43.   when reason_code = 110 then 'Rare cases where allocator accuracy drops'
  44.   END                                                         AS "Reason for margin descrepancy",
  45.   --Business Action
  46.   CASE
  47.   when reason_code = 10 then 'None'
  48.   when reason_code = 20 then 'None'
  49.   when reason_code = 30 then 'Increase fill to get more impressions for the model to learn'
  50.   when reason_code = 40 then 'Increase fill to get more impressions for the model to learn ' ||
  51.                              'or Decrease target margin gracefully'
  52.   when reason_code = 51 then 'None'
  53.   when reason_code = 52 then 'None'
  54.   when reason_code = 61 then 'None (Model should adjust itself)'
  55.   when reason_code = 62 then 'None (Model should adjust itself)'
  56.   when reason_code = 70 then 'None or Decrease target margin'
  57.   when reason_code = 81 then 'None'
  58.   when reason_code = 82 then 'None or Decrease Promotions'
  59.   when reason_code = 83 then 'None or Decrease target margin'
  60.   when reason_code = 90 then 'None'
  61.   when reason_code = 100 then 'None or Decrease target margin'
  62.   when reason_code = 110 then 'None'
  63.   END                                                         AS "Suggested Action"
  64. FROM
  65.   (
  66.     select
  67.       strategy_name,
  68.       ad_unit,
  69.       application_id,
  70.       instance_id,
  71.       device_country,
  72.       flat_net_value,
  73.       fill_rate_target,
  74.       max(target_margin)                                                        as max_target_margin,
  75.       min(target_margin)                                                        as min_target_margin,
  76.       count(distinct nvl(target_margin, -999))                                  as target_margin_change,
  77.       max(distinct daily_breakdown || ' tm=' || target_margin)                  as target_margin_latest,
  78.       min(daily_breakdown)                                                      as min_request_date,
  79.       max(daily_breakdown)                                                      as max_request_date,
  80.       1 + datediff('day', min_request_date, max_request_date)                   as days_period,
  81.       sum(n_reqs)                                                               as requests,
  82.       sum(ds_fill)                                                              as ds_fill,
  83.       sum(ds_no_eligible_ads)                                                   as ds_no_eligible_ads,
  84.       sum(ds_no_positive_ev)                                                    as ds_no_positive_ev,
  85.       round(100.0 * sum(ds_fill) / nullif(sum(n_reqs), 0), 2)                   as ds_fill_pct,
  86.       sum(impressions)                                                          as impressions,
  87.       sum(impressions_flat_tm_ex_los)                                           as impressions_flat_los,
  88.       sum(impressions_promote)                                                  as impressions_promote,
  89.       round(100.0 * sum(impressions_promote) / nullif(sum(impressions), 0), 2)  as promote_pct,
  90.       sum(expected_revenue)                                                     as expected_revenue,
  91.       sum(revenue)                                                              as revenue,
  92.       sum(profit)                                                               as profit,
  93.       sum(optimal_profit)                                                       as optimal_profit,
  94.       sum(total_prob)                                                           as expected_installs,
  95.       sum(installs)                                                             as installs,
  96.       round(sum(expected_revenue) / nullif(sum(revenue), 0), 4)                 as revenue_calibration,
  97.       round(sum(total_prob) / nullif(sum(installs), 0), 4)                      as install_calibration,
  98.       round(sum(profit) / nullif(sum(revenue), 0), 6)                           as actual_margin,
  99.       round(sum(optimal_profit) / nullif(sum(revenue), 0), 6)                   as optimal_margin,
  100.       sum(impressions_flat_tm_ex)                                               as impressions_processed_by_allocator,
  101.       round(1.0 * sum(impressions_flat_tm_ex) / nullif(sum(impressions), 0), 4) as impressions_flat_tm_ex_pct,
  102.  
  103.       ---------- ABS Margin Gap
  104.       round((sum(optimal_profit) - sum(profit)) / nullif(sum(revenue), 0), 4)   as margin_gap,
  105.       round(abs(optimal_margin - actual_margin), 4)                             as abs_margin_gap,
  106.       --round(abs(target_margin - actual_margin), 4)                           as abs_margin_gap3,
  107.       CASE
  108.       when abs(margin_gap) < 0.05
  109.         then 'gap<5%'
  110.       when abs(margin_gap) < 0.075
  111.         then 'gap<7.5%'
  112.       when abs(margin_gap) < 0.10
  113.         then 'gap<10%'
  114.       when abs(margin_gap) < 0.15
  115.         then 'gap<15%'
  116.       when abs(margin_gap) < 0.20
  117.         then 'gap<20%'
  118.       when optimal_margin notnull
  119.         then 'gap>=20%'
  120.       else
  121.         NULL END                                                                as margin_accuracy,
  122.       ---------- Reasons Code
  123.       CASE
  124.       when optimal_margin isnull
  125.         then 10
  126.       when abs(margin_gap) < 0.05
  127.         then 20
  128.       when sum(impressions) < 2500 * days_period
  129.         then 30
  130.       when (1.0 * sum(impressions_flat_tm_ex) / nullif(sum(impressions), 0)) < 0.8 and optimal_margin < actual_margin
  131.         then 40
  132.       when install_calibration < 0.95 and optimal_margin < actual_margin
  133.         then 51
  134.       when install_calibration > 1.05 and optimal_margin > actual_margin
  135.         then 52
  136.       when revenue_calibration < 0.9 and optimal_margin < actual_margin
  137.         then 61
  138.       when revenue_calibration > 1.1 and optimal_margin > actual_margin
  139.         then 62
  140.       when abs(1 - sum(total_prob) / nullif(sum(installs), 0)) < 0.05
  141.            and abs(actual_margin - (optimal_margin + 0.175)) < 0.05
  142.         then 70
  143.       when ds_fill_pct > 95 and actual_margin > optimal_margin
  144.         then 81
  145.       when actual_margin < optimal_margin and sum(impressions_promote) > sum(impressions_flat_tm_ex_los)
  146.         then 82
  147.       when (1.0 * sum(impressions_flat_tm_ex_los) / sum(impressions)) < 0.02
  148.            and actual_margin < optimal_margin and install_calibration < 1.05
  149.         then 83
  150.       when sum(expected_revenue) < sum(revenue) and optimal_margin > actual_margin
  151.            and (1.0 * sum(impressions_flat_tm_ex_los) / sum(impressions)) > 0.07
  152.         then 90
  153.       when optimal_margin < actual_margin
  154.         then 100
  155.       when optimal_margin > actual_margin
  156.         then 110
  157.       else 999
  158.       end                                                                       as reason_code,
  159.       ---------- Reasons Description
  160.       CASE
  161.       when optimal_margin isnull
  162.         then '[10] Not relevant - target margin is not set'
  163.  
  164.       when abs(optimal_margin - actual_margin) < 0.05
  165.         then '[20] Accurate - margin gap is lower than 5%'
  166.  
  167.       when sum(impressions) < 2500 * days_period
  168.         then '[30] Not relevant - not enough impressions in order to be accurate - need at least 2500 per day'
  169.  
  170.       when (1.0 * sum(impressions_flat_tm_ex) / nullif(sum(impressions), 0)) < 0.8 and optimal_margin < actual_margin
  171.         then '[40] Higher margin than requested - Not enough data across the day, more than 20% of impressions were NLI'
  172.  
  173.       --when (sum(impressions_flat_tm_ex) / nullif(sum(impressions), 0)) < 0.8 and target_margin >  actual_margin
  174.       --then 'Lower margin than expected (gap higher than 5%), more than 20% of impressions were NLI - strange'
  175.  
  176.       when install_calibration < 0.95 and optimal_margin < actual_margin
  177.         then '[51] Higher margin than requested - Model Calibration: recieved more installs than expected'
  178.  
  179.       when install_calibration > 1.05 and optimal_margin > actual_margin
  180.         then '[52] Lower margin than expected - Model Calibration: recieved less installs than expected'
  181.  
  182.       when revenue_calibration < 0.9 and optimal_margin < actual_margin
  183.         then '[61] Higher margin than expected - Allocator prediction: received more revenue than expected (Unexpected high demand quality)'
  184.  
  185.       when revenue_calibration > 1.1 and optimal_margin > actual_margin
  186.         then '[62] Lower margin than expected - Allocator prediction: received less revenue than expected (Unexpected low demand quality)'
  187.  
  188.       --calibration is great, we get higher margin than expected (by offset) - blame offset
  189.       when abs(1 - sum(total_prob) / nullif(sum(installs), 0)) < 0.05
  190.            and abs(actual_margin - (optimal_margin + 0.175)) < 0.05
  191.         then '[70] Higher margin than expected - Allocator offset: Model is alibrated (up to 5%), actual margin gap is ±5% arround margin + offset due to allocator extrenal offset'
  192.  
  193.       --actual margin is higher than expected and we have almost 100% fill nothing todo
  194.       when ds_fill_pct > 95 and actual_margin > optimal_margin
  195.         then '[81] Higher margin than requested - Hit: provided almost maximal fillrate (nothing to do)'
  196.  
  197.       when actual_margin < optimal_margin and sum(impressions_promote) > sum(impressions_flat_tm_ex_los)
  198.         then '[82] Lower margin than requested - Promotes: there were more promotes impressions than impressions inserted by the allocator'
  199.  
  200.       --when already working NLI and actual is lower than optimal - nothing todo
  201.       when (1.0 * sum(impressions_flat_tm_ex_los) / sum(impressions)) < 0.02
  202.            and actual_margin < optimal_margin and install_calibration < 1.05
  203.         then '[83] Lower margin than requested - Hit: Cant hit target margin (worked almost NLI while model is quite calibrated)'
  204.  
  205.       --higher revenue than expected + lower actual margin than requested + move a lot of loosing impressions
  206.       when sum(expected_revenue) < sum(revenue) and optimal_margin > actual_margin
  207.            and (1.0 * sum(impressions_flat_tm_ex_los) / sum(impressions)) > 0.07
  208.         then '[90] Lower margin than requested: Allocator: received more revenue than expected, more than 7% of the impressions were losing impressions'
  209.  
  210.       --higher revenue than expected + lower actual margin than requested + move a lot of loosing impressions
  211.       when optimal_margin < actual_margin
  212.         then '[100] Higher margin than requested - Allocator offset: Actual margin is higher than requested margin (biased to offset)'
  213.  
  214.       when optimal_margin > actual_margin
  215.         then '[110] Lower margin than requested - Actual margin gap is lower than requested margin (unkown)'
  216.  
  217.       when optimal_margin = actual_margin
  218.         then 'Bingo'
  219.       else
  220.         'Unknown'
  221.       end                                                                       as reason_description
  222.     FROM reports_daily_network_margin
  223.     WHERE strategy_name = 'mainstream'
  224.           and daily_breakdown >= dateadd('d', -7, date_trunc('d', current_timestamp :: timestamp))
  225.     GROUP BY 1, 2, 3, 4, 5, 6, 7
  226.     HAVING requests >= 1000 and sum(impressions) > 0 and sum(revenue) >= 5.0
  227.   )
  228.   INNER1;
  229. ------------------------ END Weekly
  230.  
  231. ------------------------ Daily
  232. /* 1) No "Suggested Action"
  233.    2) Use: daily_breakdown >= dateadd('d', -2, date_trunc('d', current_timestamp :: timestamp))  
  234. */
  235. ------------------------ END Daily
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top