Created
November 11, 2016 13:58
-
-
Save csokol/a6e4373637090e927867de74b7be429d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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