Page Comparison

  SELECT sum(cnt) count
  FROM `i19s-insights.insights.trail_untracked_cnt` 
  WHERE DATE(requestDate) = current_date()
    AND advertiserId = <advertiserId>
    AND requestType = 'Conversion' 

  SELECT reason, sum(cnt) count
  FROM `i19s-insights.insights.trail_untracked_cnt` 
  WHERE DATE(requestDate) = current_date()
    AND advertiserId = <advertiserId>
    AND requestType = 'Conversion' 
  GROUP BY 1   

  SELECT sum(count) count
  FROM `i19s-insights.insights.insights_conversion_hourly` 
  WHERE DATE(performanceHour) = current_date()
    AND advertiserId = <advertiserId>

  SELECT count(*)
  FROM `i19s-insights.insights.insights_conversion`
  WHERE DATE(performedAt) = current_date()    
    AND advertiserId = <advertiserId>    
    AND processing.totalWinnerCount > 0

  SELECT count(*)
  FROM `i19s-insights.insights.insights_conversion`
  WHERE DATE(performedAt) = current_date()    
    AND advertiserId = <advertiserId>
    AND processing.totalWinnerCount = 0  
    AND array_length(userJourney) = 0

with x as (
    select
(
    select as struct
    j.touchpointType,
    case
        when j.processing.click is not null then j.processing.click.channelId
        when j.processing.view is not null then j.processing.view.channelId
    end as channelId,
    case
        when j.processing.click is not null then j.processing.click.requestDeclineReasonAvro
        when j.processing.view is not null then j.processing.view.requestDeclineReasonAvro
    end as declineReason
) reasons,
ic.processing.trackingId
from `i19s-insights.insights.insights_conversion` ic, unnest(userJourney) j
where date(ic.performedAt) = current_date()
and advertiserId = <advertiserId>
and ic.processing.totalWinnerCount = 0
and (
        j.processing.click.requestDeclineReasonAvro is not null and j.processing.click.requestDeclineReasonAvro != 'UNKNOWN' 
    or
        j.processing.view.requestDeclineReasonAvro is not null and j.processing.view.requestDeclineReasonAvro != 'UNKNOWN'
    )
)

,r as (
    select reasons.touchpointType, tc.alias as channelName, reasons.declineReason, count(distinct trackingId) count from x
    left outer join `i19s-insights.insights.trail_channel` tc on reasons.channelId = tc.channelId
    where reasons is not null
    group by 1,2,3
)

select array_agg(struct(touchpointType, channelName, declineReason, count) order by count desc) reason, sum(count) as countTotal
from r
order by countTotal desc

with x as (
    select
(
    select as struct
    j.touchpointType,
    case
        when j.processing.click is not null then j.processing.click.channelId
        when j.processing.view is not null then j.processing.view.channelId
    end as channelId,
    case
        when j.processing.click is not null then j.processing.click.requestDeclineReasonAvro
        when j.processing.view is not null then j.processing.view.requestDeclineReasonAvro
    end as declineReason,
    case
        when j.processing.click is not null then j.processing.click.realReferer.host
        when j.processing.view is not null then j.processing.httpReferer.host
    end as realReferrerHost,    
    j.processing.httpReferer.host as httpReferrerHost
) reasons,
ic.processing.trackingId
from `i19s-insights.insights.insights_conversion` ic, unnest(userJourney) j
where date(ic.performedAt) = current_date()
and advertiserId = <advertiserId>
and ic.processing.totalWinnerCount = 0
and (
        j.processing.click.requestDeclineReasonAvro is not null and j.processing.click.requestDeclineReasonAvro != 'UNKNOWN' 
    or
        j.processing.view.requestDeclineReasonAvro is not null and j.processing.view.requestDeclineReasonAvro != 'UNKNOWN'
    )
)

,r as (
    select reasons.touchpointType, tc.alias as channelName, reasons.realReferrerHost, reasons.httpReferrerHost, reasons.declineReason, count(distinct trackingId) count from x
    left outer join `i19s-insights.insights.trail_channel` tc on reasons.channelId = tc.channelId
    where reasons is not null
    group by 1,2,3,4,5
)

select array_agg(struct(touchpointType, channelName, realReferrerHost, httpReferrerHost, declineReason, count) order by count desc) reason, sum(count) as countTotal
from r
order by countTotal desc