Querying

 

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.

1 2 3 4 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

1 2 3 4 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)

1 2 3 4 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

1 2 3 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

1 2 3 4 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

1 2 3 4 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

1 2 3 4 5 6 7 8 9 10 11 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

1 2 3 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

1 2 3 4 5 6 7 8 9 10 11 12 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 object

  • Selecting customerId from processing object

  • Selecting specific parameters from free parameters

  • Filtering for specific free parameter %Madrid%

1 2 3 4 5 6 7 8 9 10 11 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%'

 

Advanced


Access for https://ingenioustechnologies.atlassian.net/wiki/spaces/KB/pages/3194126345 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.

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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' );