lfy79001

-- pulling user page views from GA4 events
WITH base_table AS (
-- pulls relevant columns from relevant dates to decrease the size of data scanned
  SELECT
    event_name,
    event_date,
    event_timestamp,
    user_pseudo_id,
    user_id,
    device,

lfy79001

-- pulling user page views from GA4 events
WITH base_table AS (
-- pulls relevant columns from relevant dates to decrease the size of data scanned
  SELECT
    event_name,
    event_date,
    event_timestamp,
    user_pseudo_id,
    user_id,
    device,

lfy79001

SELECT ANY_VALUE(board_type) as board, AVG(score) as average_score
FROM  (
  SELECT MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
    MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_type
  FROM (
    SELECT user_pseudo_id, event_timestamp, param
    FROM `firebase-public-project.analytics_153293282.events_20180915`,
    UNNEST(event_params) AS param
    WHERE event_name = "level_complete_quickplay"
    AND (param.key = "value" OR param.key = "board")

lfy79001

  SELECT
      city, AVG(value) as avg_aqi, latitude, longitude
  FROM
      `bigquery-public-data.openaq.global_air_quality`
  WHERE
      pollutant = 'pm25'
      AND (city = 'Beijing' OR city = 'Shanghai')
      AND EXTRACT(YEAR FROM timestamp) BETWEEN 2017 AND 2021
  GROUP BY
      city, latitude, longitude

lfy79001

SELECT
contact.properties:firstname::string AS contact_firstname,
contact.properties:lastname::string AS contact_lastname,
contact.properties:email::string AS contact_email,
TO_TIMESTAMP_NTZ(contact.properties:notes_last_updated::number, 3) AS contact_last_contacted,
deal.properties:amount::number AS deal_amount,
deal.properties:name::string AS deal_name
FROM v2_live.object_with_object_properties deal
LEFT JOIN v2_live.associations deal_to_contact ON deal.objectId = deal_to_contact.fromObjectId
LEFT JOIN v2_live.object_with_object_properties contact ON contact.objectId = deal_to_contact.toObjectId

lfy79001

-- Write queries as a union of all 3 tables.

WITH
  CombinedEvents AS (
    SELECT
      viewability_metrics,
      campaign_id
    FROM adh.google_ads_impressions
    UNION ALL
    SELECT

lfy79001

WITH
  CombinedEvents AS (
    SELECT
      impression_id,
      campaign_id,
      viewability_metrics,
      TRUE AS is_impression
    FROM adh.google_ads_impressions
    UNION ALL
    SELECT

lfy79001

WITH
  VideoImpressions AS (
    SELECT impression_id
    FROM adh.google_ads_impressions
    WHERE format_category = 'VIDEO'
  ),
  CombinedEvents AS (
    SELECT
      impression_id,
      campaign_id,

lfy79001

WITH filtered_uniques AS (
  SELECT
    user_id,
    COUNT(event.placement_id) AS frequency
  FROM adh.cm_dt_impressions
  WHERE user_id != '0'
    AND event.advertiser_id IN UNNEST(@advertiser_ids)
    AND event.campaign_id IN UNNEST(@campaign_ids)
    AND event.country_domain_name = 'US'

lfy79001

WITH impression_stats AS (
  SELECT
    event.country_domain_name AS country,
    CONCAT(event.country_domain_name, '-', event.state) AS state,
    COUNT(DISTINCT user_id) AS users,
    COUNT(*) AS impressions
  FROM adh.cm_dt_impressions
  WHERE event.country_domain_name = 'US'
    OR event.country_domain_name = 'CA'
  GROUP BY 1, 2