Versions Compared

Old Version 16

changes.mady.by.user Walter Meng

Saved on

compared with

New Version Current

changes.mady.by.user Walter Meng

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

  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
languagesql

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

...

Code Block
languagesql
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