Skip to content

Instantly share code, notes, and snippets.

@csokol

csokol/breakdown.txt

Created November 11, 2016 13:58
Show Gist options
  • Save csokol/a6e4373637090e927867de74b7be429d to your computer and use it in GitHub Desktop.
Save csokol/a6e4373637090e927867de74b7be429d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
breakdown.txt
I, [2016-11-11T13:41:55.291931 #68] INFO -- : [ed5629db-8705-4168-846d-aa0746612aca] Started GET "/api/v1/rtbx/campaigns/100187/app_placements?end_date=2016-11-10&page=1&per_page=10&sort_direction=desc&sort_field=impressions&start_date=2016-11-10" for 52.45.220.125 at 2016-11-11 13:41:55 +0000
I, [2016-11-11T13:41:55.293004 #68] INFO -- : [ed5629db-8705-4168-846d-aa0746612aca] Processing by RTBX::AppPlacementsController#index as HTML
I, [2016-11-11T13:41:55.293217 #68] INFO -- : [ed5629db-8705-4168-846d-aa0746612aca] Parameters: {"end_date"=>"2016-11-10", "page"=>"1", "per_page"=>"10", "sort_direction"=>"desc", "sort_field"=>"impressions", "start_date"=>"2016-11-10", "campaign_id"=>"100187"}
D, [2016-11-11T13:41:55.297027 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] User Load (2.6ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 48 ORDER BY `users`.`id` ASC LIMIT 1
D, [2016-11-11T13:41:55.300616 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] (2.3ms) SELECT `enterprises`.`id` FROM `enterprises` WHERE `enterprises`.`deleted_at` IS NULL AND `enterprises`.`name` = 'AppLift'
D, [2016-11-11T13:41:55.304307 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] Campaign Load (2.6ms) SELECT `campaigns`.* FROM `campaigns` WHERE `campaigns`.`deleted_at` IS NULL AND `campaigns`.`id` = 100187 LIMIT 1
D, [2016-11-11T13:41:55.315845 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] LineItem Load (9.9ms) SELECT `line_items`.* FROM `line_items` WHERE `line_items`.`deleted_at` IS NULL AND `line_items`.`id` = 100168 LIMIT 1
D, [2016-11-11T13:41:55.319626 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] Enterprise Load (2.2ms) SELECT `enterprises`.* FROM `enterprises` WHERE `enterprises`.`deleted_at` IS NULL AND `enterprises`.`id` = 10001 LIMIT 1
D, [2016-11-11T13:41:55.322939 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] Enterprise Load (2.4ms) SELECT `enterprises`.* FROM `enterprises` WHERE `enterprises`.`deleted_at` IS NULL ORDER BY `enterprises`.`id` ASC LIMIT 1
D, [2016-11-11T13:41:55.324335 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] CACHE (0.0ms) SELECT `campaigns`.* FROM `campaigns` WHERE `campaigns`.`deleted_at` IS NULL AND `campaigns`.`id` = 100187 LIMIT 1 [["id", 100187], ["LIMIT", 1]]
D, [2016-11-11T13:41:55.325845 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] CACHE (0.0ms) SELECT `line_items`.* FROM `line_items` WHERE `line_items`.`deleted_at` IS NULL AND `line_items`.`id` = 100168 LIMIT 1 [["id", 100168], ["LIMIT", 1]]
D, [2016-11-11T13:41:55.329370 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] Advertiser Load (2.4ms) SELECT `advertisers`.* FROM `advertisers` INNER JOIN `users_advertisers` ON `users_advertisers`.`advertiser_id` = `advertisers`.`id` AND `users_advertisers`.`deleted_at` IS NULL WHERE `advertisers`.`deleted_at` IS NULL AND `users_advertisers`.`user_id` = 48 AND `advertisers`.`id` = 10002 LIMIT 1
D, [2016-11-11T13:41:55.330345 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] CACHE (0.0ms) SELECT `enterprises`.* FROM `enterprises` WHERE `enterprises`.`deleted_at` IS NULL ORDER BY `enterprises`.`id` ASC LIMIT 1 [["LIMIT", 1]]
D, [2016-11-11T13:41:55.335333 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] UsersAdvertiser Load (2.4ms) SELECT `users_advertisers`.* FROM `users_advertisers` WHERE `users_advertisers`.`deleted_at` IS NULL AND `users_advertisers`.`user_id` = 48 AND `users_advertisers`.`active` = 1 AND `users_advertisers`.`advertiser_id` = 10002 ORDER BY `users_advertisers`.`id` ASC LIMIT 1
D, [2016-11-11T13:41:55.339135 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] Advertiser Load (2.2ms) SELECT `advertisers`.* FROM `advertisers` WHERE `advertisers`.`deleted_at` IS NULL AND `advertisers`.`id` = 10002 LIMIT 1
D, [2016-11-11T13:41:55.344051 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] RtbCampaign Load (2.6ms) SELECT `campaigns`.* FROM `campaigns` INNER JOIN `line_items` ON `line_items`.`id` = `campaigns`.`line_item_id` AND `line_items`.`deleted_at` IS NULL LEFT OUTER JOIN `advertisers` ON `advertisers`.`id` = `line_items`.`advertiser_id` AND `advertisers`.`deleted_at` IS NULL WHERE `campaigns`.`type` IN ('RtbCampaign') AND `campaigns`.`deleted_at` IS NULL AND `line_items`.`deleted_at` IS NULL AND `advertisers`.`enterprise_id` = 10001 AND `line_items`.`advertiser_id` = 10002 AND `campaigns`.`id` = 100187 LIMIT 1
D, [2016-11-11T13:41:55.347602 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] RTBCampaignDetail Load (2.4ms) SELECT `rtb_campaigns`.* FROM `rtb_campaigns` WHERE `rtb_campaigns`.`campaign_ref_id` = 100187 LIMIT 1
D, [2016-11-11T13:41:59.546881 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] (4193.7ms) SELECT `combined`.`campaign_id`, `combined`.`app_id`, `combined`.`exchange_id`, `exchange_info`.`name` AS exchange_name, ROUND(SUM(wins)/SUM(bids), 4) AS win_rate, SUM(`combined`.`impressions`) AS impressions, ROUND(SUM(spent_micros)/SUM(impressions)/1000, 4) AS ecpm, ROUND(SUM(clicks)/SUM(impressions), 4) AS ctr, SUM(`combined`.`clicks`) AS clicks, ROUND(SUM(spent_micros)/1000000/SUM(clicks), 4) AS ecpc, ROUND(SUM(installs)/SUM(clicks), 4) AS conversion_rate, SUM(`combined`.`installs`) AS installs, ROUND(SUM(spent_micros)/1000000/SUM(installs), 4) AS ecpi, ROUND(SUM(exchange_cost_micros)/1000000, 2) AS media_cost FROM ( SELECT campaign_id AS campaign_id, app_id AS app_id, exchange_id AS exchange_id, SUM(`AppsHourlyIM`.`unique_impressions`) AS impressions, SUM(`AppsHourlyIM`.`unique_clicks`) AS clicks, SUM(`AppsHourlyIM`.`unique_installs`) AS installs, SUM(`AppsHourlyIM`.`bids`) AS bids, SUM(`AppsHourlyIM`.`unique_wins`) AS wins, SUM(`AppsHourlyIM`.`spent_micros`) AS spent_micros, SUM(`AppsHourlyIM`.`exchange_cost_micros`) AS exchange_cost_micros FROM `AppsHourlyIM` WHERE campaign_id IN (100187) AND enterprise_id IN (10001) AND (`AppsHourlyIM`.`day` > '2016-11-09' OR `AppsHourlyIM`.`day` = '2016-11-09' AND `AppsHourlyIM`.`hour` >= 23) AND (`AppsHourlyIM`.`day` < '2016-11-10' OR `AppsHourlyIM`.`day` = '2016-11-10' AND `AppsHourlyIM`.`hour` <= 22) GROUP BY campaign_id, app_id, exchange_id ) AS combined LEFT OUTER JOIN `exchange_info` ON `exchange_info`.`id` = `combined`.`exchange_id` GROUP BY 1, 2, 3, 4 ORDER BY impressions DESC LIMIT 10 OFFSET 0
D, [2016-11-11T13:41:59.551718 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] RTBCampaignBlacklist Load (2.5ms) SELECT `rtb_campaign_blacklists`.* FROM `rtb_campaign_blacklists` WHERE `rtb_campaign_blacklists`.`state` IN ('active', 'pending') AND `rtb_campaign_blacklists`.`campaign_id` = 100187
D, [2016-11-11T13:41:59.562399 #68] DEBUG -- : [ed5629db-8705-4168-846d-aa0746612aca] (9.2ms) SELECT COUNT(DISTINCT app_id, exchange_id) FROM `AppsHourlyIM` WHERE campaign_id IN (100187) AND enterprise_id IN (10001) AND (`AppsHourlyIM`.`day` > '2016-11-09' OR `AppsHourlyIM`.`day` = '2016-11-09' AND `AppsHourlyIM`.`hour` >= 23) AND (`AppsHourlyIM`.`day` < '2016-11-10' OR `AppsHourlyIM`.`day` = '2016-11-10' AND `AppsHourlyIM`.`hour` <= 22)
I, [2016-11-11T13:41:59.564334 #68] INFO -- : [ed5629db-8705-4168-846d-aa0746612aca] [active_model_serializers] Rendered ActiveModel::Serializer::Null with Hash (1.33ms)
I, [2016-11-11T13:41:59.564768 #68] INFO -- : [ed5629db-8705-4168-846d-aa0746612aca] Completed 200 OK in 4271ms (Views: 1.9ms | ActiveRecord: 4239.3ms)
Raw
slowquery.sql
SELECT
`combined`.`campaign_id`,
`combined`.`app_id`,
`combined`.`exchange_id`,
`exchange_info`.`name` AS exchange_name,
ROUND(SUM(wins) / SUM(bids), 4) AS win_rate,
SUM(`combined`.`impressions`) AS impressions,
ROUND(SUM(spent_micros) / SUM(impressions) / 1000, 4) AS ecpm,
ROUND(SUM(clicks) / SUM(impressions), 4) AS ctr,
SUM(`combined`.`clicks`) AS clicks,
ROUND(SUM(spent_micros) / 1000000 / SUM(clicks), 4) AS ecpc,
ROUND(SUM(installs) / SUM(clicks), 4) AS conversion_rate,
SUM(`combined`.`installs`) AS installs,
ROUND(SUM(spent_micros) / 1000000 / SUM(installs), 4) AS ecpi,
ROUND(SUM(exchange_cost_micros) / 1000000, 2) AS media_cost
FROM (SELECT
campaign_id AS campaign_id,
app_id AS app_id,
exchange_id AS exchange_id,
SUM(`AppsHourlyIM`.`unique_impressions`) AS impressions,
SUM(`AppsHourlyIM`.`unique_clicks`) AS clicks,
SUM(`AppsHourlyIM`.`unique_installs`) AS installs,
SUM(`AppsHourlyIM`.`bids`) AS bids,
SUM(`AppsHourlyIM`.`unique_wins`) AS wins,
SUM(`AppsHourlyIM`.`spent_micros`) AS spent_micros,
SUM(`AppsHourlyIM`.`exchange_cost_micros`) AS exchange_cost_micros
FROM `AppsHourlyIM`
WHERE campaign_id IN (100187) AND enterprise_id IN (10001) AND
(`AppsHourlyIM`.`day` > '2016-11-09' OR `AppsHourlyIM`.`day` = '2016-11-09' AND `AppsHourlyIM`.`hour` >= 23)
AND
(`AppsHourlyIM`.`day` < '2016-11-10' OR `AppsHourlyIM`.`day` = '2016-11-10' AND `AppsHourlyIM`.`hour` <= 22)
GROUP BY campaign_id, app_id, exchange_id) AS combined LEFT OUTER JOIN `exchange_info`
ON `exchange_info`.`id` = `combined`.`exchange_id`
GROUP BY 1, 2, 3, 4
ORDER BY impressions DESC
LIMIT 10 OFFSET 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment