Versions Compared

Old Version 15

changes.mady.by.user Martin Heymann

Saved on

compared with

New Version 16

changes.mady.by.user Walter Meng

Saved on

Key

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

...

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%'

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
breakoutModewide
languagesql
 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
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'
    );