Querying

1 Basic
- 1.1 Click
  - 1.1.1 Find clicks by ICLID
- 1.2 Conversion
  - 1.2.1 Conversions of today
  - 1.2.2 Conversions by attribution and rating status
  - 1.2.3 Conversions by list of conversionIds
- 1.3 Touchpoint
  - 1.3.1 Conversions and touchpoint
  - 1.3.2 Conversions and touchpoint object
  - 1.3.3 Conversions and touchpoint fields
  - 1.3.4 Conversions with total amount, commission from touchpoint
- 1.4 Positions
  - 1.4.1 Conversions with position list object
  - 1.4.2 Conversions with total amount, commission and fee
- 1.5 Free parameters
  - 1.5.1 Attributed conversions having pattern 'Madrid' in user value 3
- 1.6 Untracked
  - 1.6.1 Total number of untracked conversions for a given advertiser
  - 1.6.2 Number of untracked conversions by reason for a given advertiser
- 1.7 Attribution
  - 1.7.1 Total number of successfully processed conversions for a given advertiser
  - 1.7.2 Total number of attributed conversions for a given advertiser
- 1.8 Partnerships
  - 1.8.1 Find email addresses of key account managers
- 1.9 Creatives
  - 1.9.1 Get Vouchers
2 Advanced

Basic

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.

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

Single list of todays conversions with additional information about advertiser and target. Descended ordering by performance date.

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

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)

Touchpoint

Conversions and touchpoint

Single list of todays conversions and touchpoint list

Conversions and touchpoint object

Single list of todays conversions and single touchpoint object

Conversions and touchpoint fields

Single list of todays conversions and single touchpoint fields

Conversions with total amount, commission from touchpoint

Single list of todays conversions and totals for amount, commission and fee from touchpoint list

Positions

Conversions with position list object

Single list of todays conversions and position list

Conversions with total amount, commission and fee

Single list of todays conversions and totals for amount, commission and fee from position list

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%

Untracked

Total number of untracked conversions for a given advertiser

Number of untracked conversions by reason for a given advertiser

Attribution

Total number of successfully processed conversions for a given advertiser

Total number of attributed conversions for a given advertiser

Partnerships

Find email addresses of key account managers

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.

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.

Filtering by free parameters

This query filters user journey for touch points having gclid

Partner validation

This query calculates the successfull bounce click redirection rate per advertiser /partner / adspace on advertisers working with Ingenious onpage tag