Basic queries for conversions. In this section, the result is containing single conversions with different fields, filtered by performedAt
Given a list of ICLIDs, how to find all according clicks.
with iclids as ( SELECT -- Conversion to requestIds cast('0x' || SUBSTR(id, 3,8) || SUBSTR(id, 12,4) || SUBSTR(id, 27,2) || SUBSTR(id, 19,2) as int64) as requestId from unnest(ARRAY[ -- List of ICLIDs '1-200f169d-998e-3cbd-80bd-9c8e999d160f-a77370', '1-200f169d-99dc-35ad-80ad-95dc999d160f-a77370' ]) id ) SELECT * -- c.requestId as cli, adspaceId FROM i19s-insights.insights.trail_click c JOIN iclids i on c.requestId = i.requestId WHERE DATE(requestTime) = '2023-10-09' -- and c.networkId = 'XXX' -- and advertiserId = 'XXX' |
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 |
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') |
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') |
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() |
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' |
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' |
Single list of todays conversions and totals for amount, commission and fee from touchpoint list
SELECT performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, currencyCode, (SELECT AS STRUCT SUM(rating.revenue) AS amount, SUM(rating.revenue) - SUM(rating.discount) AS amountDiscounted, 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' |
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() |
Single list of todays conversions and totals for amount, 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 amount, 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' |
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%' |
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(*) count FROM `i19s-insights.insights.insights_conversion` WHERE DATE(performedAt) = current_date() AND advertiserId = <advertiserId> AND processing.totalWinnerCount > 0 |
WITH partners AS ( SELECT partnerId, label, CASE keyAccountManagerId WHEN 'ENTITY_DEFAULT' THEN entity.defaultPartnerKeyaccount ELSE keyAccountManagerId END AS effKeyAccountManagerId FROM `i19s-insights.insights.partnerships_partner` ) SELECT p.*, kam.userId as kamUserId, kam.email as kamEmail FROM partners p LEFT JOIN `i19s-insights.insights.access_user` kam ON p.effKeyAccountManagerId = kam.userId |
Vouchers table contains the full history of all voucher settings, so that if we need only the current values, a filter is required.
SELECT * FROM `i19s-insights.insights.creatives_voucher` WHERE entryType = 'CURRENT_VALUES' |
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 |
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' ); |
This query calculates the successfull bounce click redirection rate per advertiser /partner / adspace on advertisers working with Ingenious onpage tag
with adv as ( select distinct advertiserId from i19s-insights.insights.trail_click_cnt where requestDate = current_date() and networkId = <networkId> and ( billingCat != 'trailClickPartnerBounce' or billingCat is null) and cnt > 0 ), x as ( select DISTINCT a.requestId, a.advertiserId, a.partnerId, a.adspaceId, b.redirectingId as followClick, FROM i19s-insights.insights.trail_click a inner join adv c on a.advertiserId = c.advertiserId left join i19s-insights.insights.trail_click b on b.redirectingId = a.requestId and date(b.requestTime) = current_date() and b.networkId=<networkId> WHERE date(a.requestTime) = current_date() and a.networkId = <networkId> and a.mediaTarget is not null ) select advertiserId, partnerId, adspaceId, count(*) total, count(followClick) as redirected, round(count(followClick)*100/count(*),2) as share from x -- where partnerId = <partnerId> group by 1,2,3 order by total desc |