...
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
...