...
Basic queries for conversions. In this section, the result is containing single conversions with different fields, filtered by performedAt
Click
Find clicks by ICLID
Given a list of ICLIDs, how to find all according clicks.
Code Block |
---|
|
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' |
Conversion
Conversions of today
...
Code Block |
---|
|
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
...
amount, commission from touchpoint
Single list of todays conversions and totals for revenueamount, commission and fee from touchpoint list
Code Block |
---|
|
SELECT performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, currencyCode,
(SELECT AS STRUCT
SUM(rating.revenue) AS revenueamount,
SUM(rating.revenue) - SUM(rating.discount) AS revenueDiscountedamountDiscounted,
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' |
...
Code Block |
---|
|
SELECT performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, positions
FROM `i19s-insights.insights.insights_conversion`
WHERE DATE(performedAt) = current_date() |
...
Conversions with total
...
amount, commission and fee
Single list of todays conversions and totals for revenueamount, commission and fee from position list
Code Block |
---|
|
SELECT performedAt, updatedAt, conversionId, advertiserId, conversionTargetId, ratingStatus, currencyCode,
(
SELECT AS STRUCT
SUM(price) AS price,
SUM(quantity) AS quantity,
SUM(revenue) AS revenueamount,
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' |
...
Code Block |
---|
|
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
Code Block |
---|
|
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
Code Block |
---|
|
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
Code Block |
---|
|
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
Code Block |
---|
|
SELECT count(*) count
FROM `i19s-insights.insights.insights_conversion`
WHERE DATE(performedAt) = current_date()
AND advertiserId = <advertiserId>
AND processing.totalWinnerCount > 0 |
...
Partnerships
Find email addresses of key account managers
Code Block |
---|
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 |
...
Creatives
Get Vouchers
Vouchers table contains the full history of all voucher settings, so that if we need only the current values, a filter is required.
Code Block |
---|
SELECT *
FROM `i19s-insights.insights.creatives_voucher`
WHERE entryType = 'CURRENT_VALUES' |
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.
Code Block |
---|
breakoutMode | wide |
---|
language | sql |
---|
|
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
Code Block |
---|
|
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'
); |
Partner validation
This query calculates the successfull bounce click redirection rate per advertiser /partner / adspace on advertisers working with Ingenious onpage tag
Code Block |
---|
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 |