Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 17 Next »

Basic

Basic queries for conversions. In this section, the result is containing single conversions with different fields, filtered by performedAt

Conversion

Conversions of today

Single list of todays conversions with additional information about advertiser and target. Descended ordering by performance date.

SELECT  performedAt, updatedAt, conversionId, adv as advertiser, ctg as target
FROM `i19s-insights.insights.insights_conversion` 
WHERE DATE(performedAt) = current_date()
ORDER BY performedAt DESC

Conversions by attribution and rating status

Single list of todays conversions, filtered by attribution and rating status

SELECT  performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus
FROM `i19s-insights.insights.insights_conversion` 
WHERE DATE(performedAt) = current_date()
  AND attributionStatus='ATTRIBUTED' and ratingStatus in ('CONFIRMED','REJECTED')

Conversions by list of conversionIds

Single list of todays conversions, filtered by a list of conversionIds (separated by comma)

SELECT  performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus
FROM `i19s-insights.insights.insights_conversion` 
WHERE DATE(performedAt) = current_date()
      AND conversionId in ('abc123','def456','ghi789')

Touchpoint

Conversions and touchpoint

Single list of todays conversions and touchpoint list

SELECT  performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, userJourney
FROM `i19s-insights.insights.insights_conversion` 
WHERE DATE(performedAt) = current_date()

Conversions and touchpoint object

Single list of todays conversions and single touchpoint object

SELECT  performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, userJourney[OFFSET(0)] as touchpoint
FROM `i19s-insights.insights.insights_conversion` 
WHERE DATE(performedAt) = current_date()
 AND attributionStatus='ATTRIBUTED'

Conversions and touchpoint fields

Single list of todays conversions and single touchpoint fields

SELECT  performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, userJourney[OFFSET(0)].admediaCode as touchpointAdmediaCode
FROM `i19s-insights.insights.insights_conversion` 
WHERE DATE(performedAt) = current_date()
 AND attributionStatus='ATTRIBUTED'

Conversions with total revenue, commission from touchpoint

Single list of todays conversions and totals for revenue, commission and fee from touchpoint list

SELECT  performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, currencyCode,
  (SELECT AS STRUCT
    SUM(rating.revenue) AS revenue,
    SUM(rating.revenue) - SUM(rating.discount) AS revenueDiscounted,
    SUM(rating.commission) AS commission,
    SUM(rating.fee) as fee
    FROM UNNEST(userJourney)
  ) AS totals
FROM `i19s-insights.insights.insights_conversion` 
WHERE DATE(performedAt) = current_date()
AND attributionStatus='ATTRIBUTED'

Positions

Conversions with position list object

Single list of todays conversions and position list

SELECT  performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, positions
FROM `i19s-insights.insights.insights_conversion` 
WHERE DATE(performedAt) = current_date()

Conversions with total revenue, commission and fee

Single list of todays conversions and totals for revenue, commission and fee from position list

SELECT  performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, currencyCode,
        (
          SELECT AS STRUCT
            SUM(price) AS price,
            SUM(quantity) AS quantity,
            SUM(revenue) AS revenue,
          FROM UNNEST(positions)
        WHERE status IN( 'OPEN', 'CONFIRMED','REJECTED')
      ) as totals,
FROM `i19s-insights.insights.insights_conversion` 
WHERE DATE(performedAt) = current_date()
  AND attributionStatus='ATTRIBUTED'

Free parameters

Attributed conversions having pattern 'Madrid' in user value 3

Single list of todays conversions

  • Selecting conversionUuiD from meta object

  • Selecting customerId from processing object

  • Selecting specific parameters from free parameters

  • Filtering for specific free parameter %Madrid%

SELECT
  performedAt, eventMeta.id AS conversionUuid, conversionId,
  processing.customerId AS customerId,
  (select value from unnest(freeParameters) where key ='uv1') AS userValue1,
  (select value from unnest(freeParameters) where key ='uv2') AS userValue2,
  (select value from unnest(freeParameters) where key ='uv3') AS userValue3,
 FROM `i19s-insights.insights.insights_conversion`
 WHERE DATE(performedAt) = current_date()
     AND processing.totalWinnerCount > 0
     AND (select value from unnest(freeParameters) where key ='uv3') LIKE '%Madrid%'

Untracked

Total number of untracked conversions for a given advertiser

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

Number of untracked conversions by reason for a given advertiser

  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

Attribution

Total number of successfully processed conversions for a given advertiser

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

Total number of attributed conversions for a given advertiser

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

Total number of unattributed conversions having an empty user journey for a given advertiser

  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

Number of decline reasons by touchpoint type and channel for a given advertiser

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

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

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

Advanced

Access for Free parameters of User Journey Touchpoints.

Fields on touchpoint level that can be submitted in the tracking tag on advertiser side can now be found in the free parameter list.

 SELECT
    ic.processing.trackingId requestId,
    uj.touchpointid as requestIdOfTheTouchpoint,
    uj.touchpointType,
    (SELECT value FROM UNNEST(uj.freeParameters) WHERE key = 'gclid'  ) AS gclid,
    (select value from unnest(uj.freeParameters) where key ='uv1') as userValue1,
    (select value from unnest(uj.freeParameters) where key ='uv2') as userValue2,
    (select value from unnest(uj.freeParameters) where key ='uv3') as userValue3,
    (select value from unnest(uj.freeParameters) where key ='uv4') as userValue4,
    (select value from unnest(uj.freeParameters) where key ='smc1') as clickin_subId1,
    (select value from unnest(uj.freeParameters) where key ='smc2') as clickin_subId2,
    (select value from unnest(uj.freeParameters) where key ='smc3') as clickin_subId3,
    (select value from unnest(uj.freeParameters) where key ='smc4') as clickin_subId4,
    (select value from unnest(uj.freeParameters) where key ='smc5') as clickin_subId5,
    (select value from unnest(uj.freeParameters) where key ='smc6') as clickin_subId6,
    (select value from unnest(uj.freeParameters) where key ='sid') as clickin_siteId,
    -- check your platform configuration, this key 'pid' is default and can be changed by customer
    (select value from unnest(ic.freeParameters) where key ='pid') as clickin_productId,
  FROM `i19s-insights.insights.insights_conversion` ic
  LEFT JOIN UNNEST(userJourney) uj
  WHERE 
    DATE(ic.performedAt) = current_date()
    AND ic.processing.totalWinnerCount > 0 -- if you want to analyse conversions with winner
    AND uj.rating is not null -- if you want to take winner touchpoints only

Filtering by free parameters

This query filters user journey for touch points having gclid

SELECT
    ic.processing.trackingId requestId,
    uj.touchpointid as requestIdOfTheTouchpoint,
    uj.touchpointType,
    (
      SELECT value FROM 
      UNNEST(uj.freeParameters) 
      WHERE key = 'gclid'
    ) AS gclid
  FROM `i19s-insights.insights.insights_conversion` ic
  LEFT JOIN UNNEST(userJourney) uj
  WHERE 
    DATE(ic.performedAt) = current_date()
    AND EXISTS(
      SELECT value FROM 
      UNNEST(uj.freeParameters) 
      WHERE key = 'gclid'
    );
  • No labels