Versions Compared

Old Version 17

changes.mady.by.user Alexei Becker

Saved on

compared with

New Version 18

changes.mady.by.user Alexei Becker

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

...

Attribution

Total number of successfully processed conversions for a given advertiser

Code Block
languagesql
  SELECT sum(countcnt) count
  FROM `i19s-insights.insights.insightstrail_conversionuntracked_hourly`cnt` 
  WHERE DATE(performanceHourrequestDate) = current_date()
    AND advertiserId = <advertiserId>

...


    AND requestType = 'Conversion'

...

Total amount untracked conversions on given advertiser

Code Block
languagesql
  SELECT countsum(*cnt) count
  FROM `i19s-insights.insights.insights_conversion`trail_untracked_cnt` 
  WHERE DATE(performedAtrequestDate) = current_date()   
     AND advertiserId = <advertiserId>
    AND requestType =  AND processing.totalWinnerCount > 0

...

'Conversion'

...

Total amount untracked conversions on given advertiser

Code Block
languagesql
  SELECT countsum(*cnt) count
  FROM `i19s-insights.insights.insights_conversion`trail_untracked_cnt` 
  WHERE DATE(performedAtrequestDate) = current_date() 
       AND advertiserId = <advertiserId>
    AND processing.totalWinnerCountrequestType = 0  
    AND array_length(userJourney) = 0

...

'Conversion'

...

Total amount untracked conversions on given advertiser

Code Block
languagesql
with x asSELECT sum(cnt) count
   select
(FROM `i19s-insights.insights.trail_untracked_cnt` 
  WHERE DATE(requestDate) select as struct= current_date()
    AND j.touchpointType,advertiserId = <advertiserId>
  case  AND requestType = 'Conversion'

...

Total amount untracked conversions on given advertiser

Code Block
languagesql
  when j.processing.click is not null then j.processing.click.channelIdSELECT sum(cnt) count
  FROM `i19s-insights.insights.trail_untracked_cnt` 
  WHERE DATE(requestDate) = current_date()
 when j.processing.view is notAND nulladvertiserId then j.processing.view.channelId= <advertiserId>
    endAND asrequestType channelId,=     case'Conversion'

...

Total amount untracked conversions on given advertiser

Code Block
languagesql
  SELECT sum(cnt) count
   when j.processing.click is not null then j.processing.click.requestDeclineReasonAvro
 FROM `i19s-insights.insights.trail_untracked_cnt` 
  WHERE DATE(requestDate) = current_date()
    AND advertiserId when j.processing.view is not null then j.processing.view.requestDeclineReasonAvro
    end as declineReason
) reasons,
ic.processing.trackingId
from= <advertiserId>
    AND requestType = 'Conversion'

...

Total amount untracked conversions on given advertiser

Code Block
languagesql

  SELECT sum(cnt) count
  FROM `i19s-insights.insights.insightstrail_untracked_conversion`cnt` ic,
unnest(userJourney) j whereWHERE dateDATE(ic.performedAtrequestDate) = current_date()
    andAND advertiserId = <advertiserId>
and ic.processing.totalWinnerCount = 0 andAND (requestType = 'Conversion'

...

Total amount untracked conversions on given advertiser

Code Block
languagesql
  SELECT sum(cnt)  j.processing.click.requestDeclineReasonAvro is not null and j.processing.click.requestDeclineReasonAvro != 'UNKNOWN'count
  FROM `i19s-insights.insights.trail_untracked_cnt` 
  WHERE DATE(requestDate) = current_date()
    AND oradvertiserId = <advertiserId>
    AND  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

Number of decline reasons by touchpoint type, channel, real and http referrer host for a given advertiser

Code Block
languagesql

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 descrequestType = 'Conversion'

...

Advanced

...

Access for Free parameters of User Journey Touchpoints.

...