Google BigQuery Event Tracking

Google BigQuery Links

⁠

https://console.cloud.google.com/bigquery?project=nyt-tbrandstudio-prd⁠

⁠

https://console.cloud.google.com/bigquery?project=nyt-bigquery-beta-workspace⁠

⁠

https://console.cloud.google.com/bigquery?_ga=2.104524800.1005372420.1655817889-692732542.1655139422&_gac=1.190258649.1655992306.Cj0KCQjwntCVBhDdARIsAMEwAClKcxwBWbwXdFrRVWhWenSC6gslxABPCQL5X42AP7QiEyN52acJQ9MaAsa6EALw_wcB&project=nyt-bigquery-beta-workspace&ws=!1m5!1m4!4m3!1snyt-eventtracker-prd!2set_view!3sdfp_ad_events⁠

⁠

Events Query

SELECT * FROM `nyt-eventtracker-prd.et_view.dfp_ad_events` WHERE partition_date = "2022-12-19" AND `creativeid` = "138417021821" ORDER BY `dt_nyct` DESC LIMIT 10

Sample Query (Video Event Tracking)

WITH vid_events AS (

SELECT

safe_cast(orderid AS INT64) AS order_id,

safe_cast(lineitemid AS INT64) AS lineitem_id,

safe_cast(creativeid AS INT64) AS creative_id,

prop,

SUM(IF(event_type LIKE 'video%' AND event_location = '0',1,0)) AS et_vid_starts,

SUM(IF(event_type LIKE 'video%' AND event_location = '25',1,0)) AS et_vid_25_percent,

SUM(IF(event_type LIKE 'video%' AND event_location = '50',1,0)) AS et_vid_50_percent,

SUM(IF(event_type LIKE 'video%' AND event_location = '75',1,0)) AS et_vid_75_percent,

SUM(IF(event_type LIKE 'video%' AND event_location = '100',1,0)) AS et_vid_completes,

FROM `nyt-eventtracker-prd.et_view.dfp_ad_events`

WHERE partition_date = "2022-10-21"

GROUP BY 1,2,3,4

)

SELECT *

FROM vid_events

WHERE (et_vid_completes > et_vid_starts) OR (vid_events.et_vid_75_percent > et_vid_starts)

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.