Klaviyo Models

select
  id as profile_id --PK
  , (attributes ->> 'created') :: timestamp as created_at
  , attributes ->> 'first_name' as first_name
  , attributes ->> 'last_name' as last_name
  , attributes ->> 'email' as email
  , attributes ->> 'phone_number' as phone_number
  , attributes ->> 'organization' as organization

  -- ADD: most-used consent + geo, extracted from the JSON you already carry
  , (attributes -> 'subscriptions' -> 'email' -> 'marketing' ->> 'consent') as email_marketing_consent
  , (attributes -> 'subscriptions' -> 'sms' -> 'marketing' ->> 'consent') as sms_marketing_consent
  , (attributes -> 'location' ->> 'country') as country
  , (attributes -> 'location' ->> 'region') as region

  , attributes as attributes_json
  , relationships as relationships_json
  , links as links_json
from {{sources.klaviyo.profiles}}

select
  id as campaign_id --PK
  , attributes ->> 'name' as campaign_name
  , attributes ->> 'status' as campaign_status
  , (attributes ->> 'archived') :: boolean as is_archived
  , (attributes ->> 'created_at') :: timestamp as created_at
  , (attributes ->> 'scheduled_at') :: timestamp as scheduled_at
  , (attributes ->> 'send_time') :: timestamp as send_time  -- ADD: actual send timestamp

  , attributes as attributes_json
  , relationships as relationships_json
  , links as links_json
from {{sources.klaviyo.campaigns}}

select
  id as flow_id --PK
  , attributes ->> 'name' as flow_name
  , attributes ->> 'status' as flow_status
  , (attributes ->> 'archived') :: boolean as is_archived
  , (attributes ->> 'created') :: timestamp as created_at    -- FIX: key is 'created', not 'created_at'
  , (attributes ->> 'updated') :: timestamp as updated_at    -- FIX: flows have 'updated', not 'scheduled_at'
  , attributes ->> 'trigger_type' as trigger_type            -- ADD: flows are triggered, not scheduled

  , attributes as attributes_json
  , relationships as relationships_json
  , links as links_json
from {{sources.klaviyo.flows}}

select
  concat(date, '-', flow_message_id) as id --PK
  , (date::timestamptz at time zone '{{ reporting_timezone }}')::date as report_date  -- REPLACE with your account's reporting timezone
  , flow_id
  , flow_message_id
  , send_channel
  , recipients
  , delivered
  , failed
  , bounced
  , opens
  , opens_unique
  , clicks
  , clicks_unique
  , spam_complaints
  , unsubscribes
  , unsubscribe_uniques
  , conversion_uniques
  , conversion_value
  , conversions
  , average_order_value
  , bounce_rate
  , bounced_or_failed
  , bounced_or_failed_rate
  , click_rate
  , click_to_open_rate
  , conversion_rate
  , delivery_rate
  , failed_rate
  , open_rate
  , revenue_per_recipient
  , spam_complaint_rate
  , unsubscribe_rate
from {{sources.klaviyo.reports__flow_performance}}

select
  concat(date, '-', campaign_id) as id --PK
  , (date::timestamptz at time zone '{{ reporting_timezone }}')::date as report_date  -- REPLACE with your account's reporting timezone
  , campaign_id
  , send_channel
  , recipients
  , delivered
  , failed
  , bounced
  , opens
  , opens_unique
  , clicks
  , clicks_unique
  , spam_complaints
  , unsubscribes
  , unsubscribe_uniques
  , conversion_uniques
  , conversion_value
  , conversions
  , average_order_value
  , bounce_rate
  , bounced_or_failed
  , bounced_or_failed_rate
  , click_rate
  , click_to_open_rate
  , conversion_rate
  , delivery_rate
  , failed_rate
  , open_rate
  , revenue_per_recipient
  , spam_complaint_rate
  , unsubscribe_rate
from {{sources.klaviyo.reports__campaign_performance}}

-- Grain: one row per Placed Order event.
with placed_orders as (
  select
    id
    , attributes
    , relationships
    , attributions
  from {{sources.klaviyo.events}}
  where relationships -> 'metric' -> 'data' ->> 'id' = '{{ placed_order_metric_id }}'  -- REPLACE with your account's Placed Order metric ID
)
, attr as (
  select
    id
    , case when jsonb_typeof(attributions) = 'array' and jsonb_array_length(attributions) > 0
           then attributions -> 0 end as first_attr  -- first attribution element only
  from placed_orders
)
select
  po.id as event_id --PK
  , (po.attributes ->> 'datetime')::timestamptz as occurred_at
  , (po.attributes ->> 'timestamp')::bigint as event_timestamp
  , po.relationships -> 'profile' -> 'data' ->> 'id' as profile_id
  , po.relationships -> 'metric' -> 'data' ->> 'id' as metric_id
  , po.attributes -> 'event_properties' ->> '$event_id' as shopify_order_id
  , (po.attributes -> 'event_properties' ->> '$value')::float as order_value
  , po.attributes -> 'event_properties' ->> '$currency_code' as currency_code
  , (po.attributes -> 'event_properties' ->> 'Item Count')::float as item_count
  , po.attributes -> 'event_properties' -> 'Collections' as collections_json
  , po.attributes -> 'event_properties' ->> 'Source Name' as source_name
  , po.attributes -> 'event_properties' ->> 'ShippingRate' as shipping_rate
  , po.attributes -> 'event_properties' -> 'Discount Codes' as discount_codes_json
  , (po.attributes -> 'event_properties' ->> 'Total Discounts')::float as total_discounts
  , po.attributes -> 'event_properties' -> '$extra' as extra_json
  , a.first_attr -> 'relationships' -> 'flow' -> 'data' ->> 'id' as flow_id
  , a.first_attr -> 'relationships' -> 'flow-message' -> 'data' ->> 'id' as flow_message_id
  , a.first_attr -> 'relationships' -> 'campaign' -> 'data' ->> 'id' as campaign_id
  , a.first_attr -> 'relationships' -> 'campaign-message' -> 'data' ->> 'id' as campaign_message_id
  , a.first_attr -> 'relationships' -> 'attributed-event' -> 'data' ->> 'id' as attributed_event_id
  , (a.first_attr is not null) as is_attributed
  , po.attributions as attributions_json
from placed_orders po
left join attr a on a.id = po.id

-- Grain: one row per report_date x send (campaign or flow message).
select
  concat(report_date, '-', campaign_id) as id --PK
  , report_date
  , 'campaign'::text as send_type
  , campaign_id
  , null::text as flow_id
  , null::text as flow_message_id
  , send_channel
  , recipients
  , delivered
  , failed
  , bounced
  , opens_unique as opens
  , clicks_unique as clicks
  , spam_complaints
  , unsubscribe_uniques as unsubscribes
  , conversion_uniques
  , conversion_value as conversion_revenue
  , average_order_value
  , bounce_rate
  , click_rate
  , click_to_open_rate
  , conversion_rate
  , delivery_rate
  , open_rate
  , revenue_per_recipient
  , spam_complaint_rate
  , unsubscribe_rate
from {{models.klaviyo_campaign_performance_base}}
union all
select
  concat(report_date, '-', flow_message_id) as id --PK
  , report_date
  , 'flow'::text as send_type
  , null::text as campaign_id
  , flow_id
  , flow_message_id
  , send_channel
  , recipients
  , delivered
  , failed
  , bounced
  , opens_unique as opens
  , clicks_unique as clicks
  , spam_complaints
  , unsubscribe_uniques as unsubscribes
  , conversion_uniques
  , conversion_value as conversion_revenue
  , average_order_value
  , bounce_rate
  , click_rate
  , click_to_open_rate
  , conversion_rate
  , delivery_rate
  , open_rate
  , revenue_per_recipient
  , spam_complaint_rate
  , unsubscribe_rate
from {{models.klaviyo_flow_performance_base}}

with klaviyo_numbered as (
  select
    k.event_id
    , k.occurred_at
    , k.shopify_order_id
    , k.flow_id
    , k.flow_message_id
    , k.campaign_id
    -- earliest event per order; duplicates are identical except event_id
    , row_number() over (partition by k.shopify_order_id order by k.occurred_at asc, k.event_id asc) as order_event_index
  from {{models.klaviyo_placed_order_events_base}} k
)
, klaviyo_attributed as (
  select
    shopify_order_id
    , occurred_at
    , flow_id
    , flow_message_id
    , campaign_id
  from klaviyo_numbered
  where order_event_index = 1
  and (flow_id is not null or campaign_id is not null)
)
select
  k.shopify_order_id as order_id --PK
  , k.occurred_at
  , o.first_time_or_returning
  , case 
			  when k.flow_id is not null 
				  then 'flow' 
				  else 'campaign'
		end as attribution_channel
  , k.flow_id
  , k.flow_message_id
  , k.campaign_id
  , o.order_total_line_item_net_revenue as net_revenue
from klaviyo_attributed k
left join {{models.shopify_orders_output}} o on o.order_id = k.shopify_order_id

-- order_rollup: Klaviyo-attributed orders rolled up to send key (flow_message_id
-- or campaign_id) x order conversion date. Joined below on BOTH key and date
-- (full outer join) so late-converting orders (no matching send report_date)
-- still land in the output on their own conversion date.
with order_rollup as (
  select
    flow_id
    , flow_message_id
    , campaign_id
    , (occurred_at at time zone '{{ reporting_timezone }}')::date as order_date
    , count(*) as n_attributed_orders
    , count(*) filter (where first_time_or_returning = 'First-time') as n_first_time_orders
    , count(*) filter (where first_time_or_returning = 'Returning') as n_returning_orders
    , count(*) filter (where first_time_or_returning = 'Zero dollar orders') as n_zero_dollar_orders
    , count(*) filter (where first_time_or_returning is null) as n_unmatched_orders
    , sum(net_revenue) as attributed_revenue
  from {{models.shopify_order_klaviyo_attribuition_facts}}
  group by
    flow_id
    , flow_message_id
    , campaign_id
    , (occurred_at at time zone '{{ reporting_timezone }}')::date
)
select
  coalesce(s.id, concat(r.order_date, '-', coalesce(r.flow_message_id, r.campaign_id))) as id --PK
  , coalesce(s.report_date, r.order_date) as report_date
  , coalesce(s.send_type, case when r.flow_id is not null then 'flow' else 'campaign' end) as send_type
  , coalesce(s.campaign_id, r.campaign_id) as campaign_id
  , coalesce(s.flow_id, r.flow_id) as flow_id
  , coalesce(s.flow_message_id, r.flow_message_id) as flow_message_id
  , s.send_channel
  , s.recipients
  , s.delivered
  , s.failed
  , s.bounced
  , s.opens
  , s.clicks
  , s.spam_complaints
  , s.unsubscribes
  , s.conversion_uniques
  , s.conversion_revenue
  , s.average_order_value
  , s.bounce_rate
  , s.click_rate
  , s.click_to_open_rate
  , s.conversion_rate
  , s.delivery_rate
  , s.open_rate
  , s.revenue_per_recipient
  , s.spam_complaint_rate
  , s.unsubscribe_rate
  , r.n_attributed_orders
  , r.n_first_time_orders
  , r.n_returning_orders
  , r.n_zero_dollar_orders
  , r.n_unmatched_orders
  , r.attributed_revenue
from {{models.klaviyo_daily_send_performance_facts}} s
full outer join order_rollup r
  on coalesce(r.flow_message_id, r.campaign_id) = coalesce(s.flow_message_id, s.campaign_id)
  and r.order_date = s.report_date

select
  flow_id --PK
  , flow_name
  , flow_status
  , is_archived
  , created_at
  , updated_at
from {{models.klaviyo_flows_base}}

SELECT
  campaign_id --pk
  , campaign_name
  , campaign_status
  , is_archived
  , created_at
  , scheduled_at
FROM {{models.klaviyo_campaigns_base}}