Verified Visits

-- models/verified_visits.sql
{{ config(materialized='view') }}

select vt.*
     , ( select count(*) from  ahoy_events e
                           where e.visit_id = vt.id
                            and e.name = '$view') as pageviews
     , ( select count(*)
           from ahoy_events e_7
           join ahoy_event_properties ep_7
             on e_7.id = ep_7.event_id
            and e_7.name = 'checkout_funnel'
            and ep_7.name = 'funnel_step'
            and ep_7.value = 'order_confirmation'
          where e_7.visit_id = vt.id ) as orders
  from visits vt
  join visitor_profiles vp
    on vp.visitor_id = vt.visitor_id
   and vp.verified_at is not null
   group by 1, 2, 3, 4, 5, 6, 7, 8

# models/schema.yml
version: 2

models:
  - name: verified_visits
    columns:
      - name: id
      - name: visitor_id
      - name: ip
      - name: user_agent
      - name: referrer
      - name: landing_page
      - name: user_id
      - name: referring_domain
      - name: search_keyword
      - name: browser
      - name: os
      - name: device_type
      - name: screen_height
      - name: screen_width
      - name: country
      - name: region
      - name: city
      - name: utm_source
      - name: utm_medium
      - name: utm_term
      - name: utm_content
      - name: utm_campaign
      - name: started_at
      - name: pageviews
      - name: is_bounce
      - name: orders

Assumptions

• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

select vt.*
     , ( select count(*) from  ahoy_events e
                           where e.visit_id = vt.id
                            and e.name = '$view') as pageviews
     , ( select count(*)
           from ahoy_events e_7
           join ahoy_event_properties ep_7
             on e_7.id = ep_7.event_id
            and e_7.name = 'checkout_funnel'
            and ep_7.name = 'funnel_step'
            and ep_7.value = 'order_confirmation'
          where e_7.visit_id = vt.id ) as orders
  from visits vt
  join visitor_profiles vp
    on vp.visitor_id = vt.visitor_id
   and vp.verified_at is not null
   group by 1, 2, 3, 4, 5, 6, 7, 8

view: verified_visits {
  derived_table: {
    sql: select vt.*
     , ( select count(*) from  ahoy_events e
                           where e.visit_id = vt.id
                            and e.name = '$view') as pageviews
     , ( select count(*)
           from ahoy_events e_7
           join ahoy_event_properties ep_7
             on e_7.id = ep_7.event_id
            and e_7.name = 'checkout_funnel'
            and ep_7.name = 'funnel_step'
            and ep_7.value = 'order_confirmation'
          where e_7.visit_id = vt.id ) as orders
  from visits vt
  join visitor_profiles vp
    on vp.visitor_id = vt.visitor_id
   and vp.verified_at is not null
   group by 1, 2, 3, 4, 5, 6, 7, 8 ;;
  }

  dimension: id {
    sql: ${TABLE}.id ;;
  }

  dimension: visitor_id {
    sql: ${TABLE}.visitor_id ;;
  }

  dimension: ip {
    sql: ${TABLE}.ip ;;
  }

  dimension: user_agent {
    sql: ${TABLE}.user_agent ;;
  }

  dimension: referrer {
    sql: ${TABLE}.referrer ;;
  }

  dimension: landing_page {
    sql: ${TABLE}.landing_page ;;
  }

  dimension: user_id {
    sql: ${TABLE}.user_id ;;
  }

  dimension: referring_domain {
    sql: ${TABLE}.referring_domain ;;
  }

  dimension: search_keyword {
    sql: ${TABLE}.search_keyword ;;
  }

  dimension: browser {
    sql: ${TABLE}.browser ;;
  }

  dimension: os {
    sql: ${TABLE}.os ;;
  }

  dimension: device_type {
    sql: ${TABLE}.device_type ;;
  }

  dimension: screen_height {
    sql: ${TABLE}.screen_height ;;
  }

  dimension: screen_width {
    sql: ${TABLE}.screen_width ;;
  }

  dimension: country {
    sql: ${TABLE}.country ;;
  }

  dimension: region {
    sql: ${TABLE}.region ;;
  }

  dimension: city {
    sql: ${TABLE}.city ;;
  }

  dimension: utm_source {
    sql: ${TABLE}.utm_source ;;
  }

  dimension: utm_medium {
    sql: ${TABLE}.utm_medium ;;
  }

  dimension: utm_term {
    sql: ${TABLE}.utm_term ;;
  }

  dimension: utm_content {
    sql: ${TABLE}.utm_content ;;
  }

  dimension: utm_campaign {
    sql: ${TABLE}.utm_campaign ;;
  }

  dimension: started_at {
    sql: ${TABLE}.started_at ;;
  }

  dimension: pageviews {
    type: number
    sql: ${TABLE}.pageviews ;;
  }

  dimension: is_bounce {
    type: yesno
    sql: ${pageviews} < 2 ;;
  }

  dimension: orders {
    type: number
    sql: ${TABLE}.orders ;;
  }

  dimension_group: created_at {
    type: time
    timeframes: [time, date, week, month, day_of_week, hour_of_day]
    sql: ${TABLE}.created_at ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: count_bounces {
    type: count
    filters: [is_bounce: "Yes"]
  }

  measure: bounce_rate {
    type: number
    value_format: "#.0%"
    sql: ${count_bounces} / nullif(${count},0) ;;
  }
}

- view: verified_visits
  derived_table:
    sql: select vt.*
     , ( select count(*) from  ahoy_events e
                           where e.visit_id = vt.id
                            and e.name = '$view') as pageviews
     , ( select count(*)
           from ahoy_events e_7
           join ahoy_event_properties ep_7
             on e_7.id = ep_7.event_id
            and e_7.name = 'checkout_funnel'
            and ep_7.name = 'funnel_step'
            and ep_7.value = 'order_confirmation'
          where e_7.visit_id = vt.id ) as orders
  from visits vt
  join visitor_profiles vp
    on vp.visitor_id = vt.visitor_id
   and vp.verified_at is not null
   group by 1, 2, 3, 4, 5, 6, 7, 8
  fields:
  - dimension: id
    sql: ${TABLE}.id
  - dimension: visitor_id
    sql: ${TABLE}.visitor_id
  - dimension: ip
    sql: ${TABLE}.ip
  - dimension: user_agent
    sql: ${TABLE}.user_agent
  - dimension: referrer
    sql: ${TABLE}.referrer
  - dimension: landing_page
    sql: ${TABLE}.landing_page
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: referring_domain
    sql: ${TABLE}.referring_domain
  - dimension: search_keyword
    sql: ${TABLE}.search_keyword
  - dimension: browser
    sql: ${TABLE}.browser
  - dimension: os
    sql: ${TABLE}.os
  - dimension: device_type
    sql: ${TABLE}.device_type
  - dimension: screen_height
    sql: ${TABLE}.screen_height
  - dimension: screen_width
    sql: ${TABLE}.screen_width
  - dimension: country
    sql: ${TABLE}.country
  - dimension: region
    sql: ${TABLE}.region
  - dimension: city
    sql: ${TABLE}.city
  - dimension: utm_source
    sql: ${TABLE}.utm_source
  - dimension: utm_medium
    sql: ${TABLE}.utm_medium
  - dimension: utm_term
    sql: ${TABLE}.utm_term
  - dimension: utm_content
    sql: ${TABLE}.utm_content
  - dimension: utm_campaign
    sql: ${TABLE}.utm_campaign
  - dimension: started_at
    sql: ${TABLE}.started_at
  - dimension: pageviews
    type: number
    sql: ${TABLE}.pageviews
  - dimension: is_bounce
    type: yesno
    sql: ${pageviews} < 2
  - dimension: orders
    type: number
    sql: ${TABLE}.orders
  - dimension_group: created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - day_of_week
    - hour_of_day
    sql: ${TABLE}.created_at
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: count_bounces
    type: count
    filters:
    - is_bounce: Yes
  - measure: bounce_rate
    type: number
    value_format: '#.0%'
    sql: ${count_bounces} / nullif(${count},0)