...
| Code Block | ||
|---|---|---|
| ||
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 | ||
|---|---|---|
| ||
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 | ||
|---|---|---|
| ||
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 | ||
|---|---|---|
| ||
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' |
...