SQLize | PHPize | SQLtest

with campaignKPI as ( select c.PartnerId , c.objectiveId , (select PartnerName from provisioning2.Partner where PartnerId = c.PartnerId) as PartnerName , c.AdvertiserId , (select AdvertiserName from provisioning2.Advertiser where AdvertiserId = c.AdvertiserId) as AdvertiserName , c.CampaignId , c.CampaignName , (select ROIGoalTypeId from provisioning2.CampaignROIGoal where campaignid = c.CampaignId and Priority = '1') as KPI1 , (select ROIGoalTypeId from provisioning2.CampaignROIGoal where campaignid = c.CampaignId and Priority = '2') as KPI2 , (select ROIGoalTypeId from provisioning2.CampaignROIGoal where campaignid = c.CampaignId and Priority = '3') as KPI3 , (select ROIGoalValue from provisioning2.CampaignROIGoal where campaignid = c.CampaignId and Priority = '1') as "Primary Goal" , (select ROIGoalValue from provisioning2.CampaignROIGoal where campaignid = c.CampaignId and Priority = '2') as "Secondary Goal" , (select ROIGoalValue from provisioning2.CampaignROIGoal where campaignid = c.CampaignId and Priority = '3') as "Tertiary Goal" from provisioning2.Campaign c where c.partnerid in :PartnerID ) , kpis as ( select rtb.campaignid , rtb.PartnerId , zeroifnull(sum(bidcount)) as bids , zeroifnull(sum(impressioncount)) as impressions , zeroifnull(sum(advertisercostinusd)) as AdvertiserCostinUSD , zeroifnull(sum(creativewasviewablecount) / nullif(sum(creativeistrackablecount), 0)) as IVR , isnull(((zeroifnull(sum(advertisercostinusd)) / nullif(sum(impressioncount), 0)) *1000) / ((sum(creativewasviewablecount)/sum(creativeistrackablecount))), 0) as "evCPM" , zeroifnull(sum(advertisercostinusd)/nullif(sum(clickcount), 0)) as eCPC , zeroifnull(sum(clickcount) / nullif(sum(impressioncount), 0)) as CTR , zeroifnull(sum(advertisercostinusd) / nullif(zeroifnull(sum(lastclick1count)) + zeroifnull(sum(lastview1count)), 0)) as CPA , zeroifnull(sum(videoeventcompletecount) / nullif(sum(videoeventstartcount), 0)) as VCR , zeroifnull(sum(advertisercostinusd) / nullif(sum(videoeventcompletecount), 0)) as CPVC from reports.rtbplatformreport rtb inner join campaignKPI kpi on kpi.campaignid = rtb.campaignid where date(reporthourutc) >= :AfterDate and date(reporthourutc) < current_date() and ImpressionCount > 1 group by 1, rtb.PartnerId, rtb.campaignid, rtb.campaignid ) select k.AdvertiserID , k.PartnerId , k.AdvertiserName , c.CampaignID , k.CampaignName , case k.ObjectiveId when 0 then 'Programmatic Guaranteed' when 1 then 'Awareness' when 2 then 'Consideration' when 3 then 'Conversion' end as "Objective" , case KPI1 when 9 then 'Viewability' when 6 then 'Conversion Revenue' when 10 then 'Estimated CPM' when 3 then 'CPC' when 4 then 'CTR' when 5 then 'CPA' when 14 then 'CPCV' when 7 then 'VCR' when 2 then 'Reach' end as "Primary KPI" , k."Primary Goal" , case KPI1 when 9 then IVR when 6 then null when 10 then evCPM when 3 then eCPC when 4 then CTR when 5 then CPA when 14 then CPVC when 7 then VCR when 2 then null end as "Actual Primary KPI" , case KPI2 when 9 then 'Viewability' when 6 then 'Conversion Revenue' when 10 then 'Estimated CPM' when 3 then 'CPC' when 4 then 'CTR' when 5 then 'CPA' when 14 then 'CPCV' when 7 then 'VCR' when 2 then 'Reach' end as "Secondary KPI" , k."Secondary Goal" , case KPI2 when 9 then IVR when 6 then null when 10 then evCPM when 3 then eCPC when 4 then CTR when 5 then CPA when 14 then CPVC when 7 then VCR when 2 then null end as "Actual Secondary KPI" , case KPI3 when 9 then 'Viewability' when 6 then 'Conversion Revenue' when 10 then 'Estimated CPM' when 3 then 'CPC' when 4 then 'CTR' when 5 then 'CPA' when 14 then 'CPCV' when 7 then 'VCR' when 2 then 'Reach' end as "Tertiary KPI" , k."Tertiary Goal" , case KPI3 when 9 then IVR when 6 then null when 10 then evCPM when 3 then eCPC when 4 then CTR when 5 then CPA when 14 then CPVC when 7 then VCR when 2 then null end as "Actual Tertiary KPI" from kpis c inner join campaignKPI k on k.campaignid = c.campaignid Order by 2, 4, 9 DESC