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

SELECT
    min_temperature_air_2m_f,
    avg_temperature_air_2m_f,
    max_temperature_air_2m_f
FROM 
    TABLE
    (
        onpoint_id.get_history_day(
            '01094',
            'US', 

lfy79001

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

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

lfy79001

with stats as (

    select *
    from {{ var('campaign_stats') }}
), 

accounts as (

    select *
    from {{ var('account_history') }}

lfy79001

with prep as (
select
  concat(
    user_pseudo_id,
    (
      select value.int_value from unnest(event_params) 
      where key = 'ga_session_id'
    )
  ) as session_id,
  countif(event_name = 'page_view') as views

lfy79001

SELECT
  COUNTIF(event_name = 'page_view') / COUNT(DISTINCT user_pseudo_id) AS event_count_per_user,
  COUNT(
    DISTINCT 
    CASE
      WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1' 
      THEN
        CONCAT(
          user_pseudo_id, 
          CAST(