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 objectSelecting
customerId
from processing objectSelecting 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' );