Shopify Models

select 
    split_part(id, '/', 5) AS order_id --PK
  , created_at 
  , billing_address AS billing_address_json 
  , cancelled_at
  , case when cancelled_at is null then false else true end as is_cancelled
  , custom_attributes AS customer_attributes_json
  , discount_codes
  , split_part(trim(BOTH '"' FROM json_extract_path_text(customer::json, 'id')), '/', 5) AS customer_id
  , display_financial_status AS financial_status
  , display_fulfillment_status AS fulfillment_status 
  , email
  , name AS order_number
  , net_payment_set AS net_payment_set_json 
  , (net_payment_set->'shopMoney'->>'amount')::FLOAT AS net_payment
  , (total_tax_set->'shopMoney'->>'amount')::FLOAT AS total_tax
  , subtotal_price_set AS sub_total_price_set_json
  , (subtotal_price_set->'shopMoney'->>'amount')::FLOAT AS sub_total_price
  , payment_gateway_names AS payment_gateway_json
  , shipping_address AS shipping_address_json
  , source_name AS order_source
  , tags AS tags_json 
  , total_discounts_set AS total_discounts_set_json
  , (total_discounts_set->'shopMoney'->>'amount')::FLOAT AS total_discount
  , total_price_set AS total_price_set_json
  , (total_price_set->'shopMoney'->>'amount')::FLOAT AS total_price
  , total_shipping_price_set AS total_shipping_price_set_json
  , (total_shipping_price_set->'shopMoney'->>'amount')::FLOAT as shipping_price
  , total_tax_set AS total_tax_set_json
  , line_items AS line_items_json
  , fulfillments as fulfillments_json

  , (net_payment_set->'shopMoney'->>'amount')::FLOAT - (total_tax_set->'shopMoney'->>'amount')::FLOAT as net_sales_including_shipping


 
from {{sources.shopify.orders}}

select 
  split_part(id,'/',5) as customer_id --PK
  , created_at 
  , email 
  , first_name 
  , last_name
  , phone 
  , state as customer_state
  , note 
  , tags as tags_json 
  , addresses as addresses_json 
  , default_address 
  
from {{sources.shopify.customers}}

with 
    base as (
      select 
        o.order_id 
        , split_part(li->'node'->>'id','/',5) AS line_item_id --PK
        , li->'node'->>'title' AS line_item_title
        , split_part(li->'node'->'product'->>'id','/',5) AS product_id
        , li->'node'->'product'->>'title' AS product_title
        , li->'node' as node
        , split_part(li->'node'->'variant'->>'id','/',5) AS variant_id 
        , li->'node'->'variant'->>'sku' AS variant_sku  
        , li->'node'->'variant'->>'price' AS variant_price
        , (li->'node'->>'quantity') :: integer AS quantity
        , li->'node'->>'fulfillmentStatus' AS fulfillment_status
        , (li->'node'->'discountAllocations'->0->'allocatedAmountSet'->'shopMoney'->>'amount') :: float as discount_allocation_amount
        , (li->'node'->'discountedTotalSet'->'shopMoney'->>'amount') :: float AS post_discount_revenue
        , (li->'node'->'originalUnitPriceSet'->'shopMoney'->>'amount') :: float AS pre_discount_revenue
      from {{models.shopify_orders_base}} o, 
      LATERAL jsonb_array_elements(o.line_items_json->'edges') AS li
  )

SELECT 
  *
  , dense_rank() over (partition by order_id order by post_discount_revenue desc) as line_item_order_price_rank

FROM BASE

SELECT
    o.order_id, 
    split_part(f.value->>'id','/',5)             AS fulfillment_id, --PK
    f.value->>'name'                             AS fulfillment_name,
    f.value->>'status'                           AS status,
    (f.value->>'createdAt')::timestamptz         AS created_at,
    (f.value->>'updatedAt')::timestamptz         AS updated_at,
    (f.value->>'deliveredAt')::timestamptz       AS delivered_at,
    (f.value->>'inTransitAt')::timestamptz       AS in_transit_at,
    f.value->>'displayStatus'                    AS display_status,
    (f.value->>'totalQuantity')::int             AS total_quantity,
    (f.value->>'requiresShipping')::boolean      AS requires_shipping,
    (f.value->>'estimatedDeliveryAt')::timestamptz AS estimated_delivery_at
FROM {{models.shopify_orders_base}} o
CROSS JOIN LATERAL jsonb_array_elements(o.fulfillments_json::jsonb) AS f(value)

select 
  order_id || '-'|| json_array_elements_text(tags_json::json) as id --PK 
  , order_id
  , json_array_elements_text(tags_json::json) :: varchar as tag 

  from {{models.shopify_orders_base}}

with base as (
select  
order_id  
, json_array_elements_text(discount_codes::json) as discount_code
from {{models.shopify_orders_base}}
) 

select 
  order_id || '-' || discount_code as id --PK
  , order_id 
  , discount_code 
from base 

    SELECT 
        o.order_id,
        split_part(li_edge->'node'->>'id','/',5) AS line_item_id, --PK
        (da_alloc->'allocatedAmountSet'->'shopMoney'->>'amount')::numeric AS discount_amount,
        da_alloc->'allocatedAmountSet'->'shopMoney'->>'currencyCode' AS currency_code,
        li_index,
        alloc_index,
        md5(
            li_index::text || ':' ||
            alloc_index::text || ':' ||
            li_edge::text || da_alloc::text
        ) AS id
    FROM {{models.shopify_orders_base}} o
    
    -- line_items_json->'edges'
    CROSS JOIN LATERAL jsonb_array_elements(o.line_items_json->'edges')
        WITH ORDINALITY AS li(li_edge, li_index)
        
    -- li_edge->'node'->'discountAllocations'
    CROSS JOIN LATERAL jsonb_array_elements(li_edge->'node'->'discountAllocations')
        WITH ORDINALITY AS da(da_alloc, alloc_index)

select 
  order_id 
  , refunds

  , split_part((refunds::jsonb -> 0 ->>'id'), '/', 5) :: varchar AS refund_id --PK
  , refunds::jsonb -> 0 ->>'createdAt' as created_at

  , (refunds::jsonb -> 0 -> 'refundLineItems' -> 'nodes') as refund_lines_json
  
from {{sources.shopify.refunds}}
where split_part((refunds::jsonb -> 0 ->> 'id'), '/', 5) is not null 

select
    r.order_id
    , r.refund_id --PK
    , rli.value
    , split_part(rli.value ->> 'id', '/',5) as id
    , split_part(rli.value -> 'lineItem' ->> 'id', '/',5) :: varchar as line_item_id
    , (rli.value ->> 'quantity') :: float as refund_quantity
    , (rli.value -> 'subtotalSet' -> 'shopMoney' ->> 'amount') :: float as refund_subtotal
  
from {{models.shopify_refunds_base}} r
cross join lateral jsonb_array_elements(r.refunds::jsonb) refund
cross join lateral jsonb_array_elements(refund.value->'refundLineItems'->'nodes') rli

SELECT
    o.order_id --PK
  , o.customer_id
  , o.created_at

  -- All order sequence
  , ROW_NUMBER() OVER (
      PARTITION BY o.customer_id 
      ORDER BY o.created_at
    ) AS order_sequence

FROM {{models.shopify_orders_base}} o

SELECT
	line_item_id --PK
	, sum(discount_amount) as discount_amount

FROM {{models.shopify_order_line_item_discount_allocations_base}}

group by 1 

select 
  c.customer_id--PK
  , min(case when order_seq.order_sequence = 1 then order_seq.created_at else null end) as first_order_created_at
  , max(case when order_seq.order_sequence = 1 then order_seq.created_at else null end) as last_order_created_at

 
from {{models.shopify_customers_base}} c 
left join {{models.shopify_order_sequence_facts}} order_seq on c.customer_id = order_seq.customer_id 

group by 1 

with fulfillments as (
select 
order_id 
, min(created_at) as first_successful_fulfillment_created_at
, max(created_at) as last_successful_fulfillment_created_at
from  {{models.shopify_order_fulfillments_base}}
where status = 'SUCCESS'
group by 1 
)

select 
  b.order_id --PK
  , f.first_successful_fulfillment_created_at
  , last_successful_fulfillment_created_at
from {{models.shopify_orders_base}} b 
left join fulfillments f on b.order_id = f.order_id 

SELECT 
  order_id --PK
  , STRING_AGG(tag, ', ' ORDER BY tag) AS list_tags

FROM {{models.shopify_order_tags_base}}

GROUP BY 1

SELECT 
	order_id --PK
  , STRING_AGG(discount_code, ', ' ORDER BY discount_code) AS list_discount_code
 
FROM {{models.shopify_order_discounts_base}}

GROUP BY 1

with
  order_total_shipping as (
    select
      order_id 
      , shipping_price
    
    from {{models.shopify_orders_base}}
) , 


order_line_items as (
  select 
    order_id 
    , count(distinct line_item_id) as n_line_items

  from {{models.shopify_order_line_items_base}}

  group by 1
)

select 
  o.order_id --PK
  , o.shipping_price / nullif(n_line_items, 0) as prorated_line_item_shipping
  
from order_total_shipping o
left join order_line_items oli on oli.order_id = o.order_id 

with
  line_refund_totals as (
    select
        line_item_id --PK
      , sum(refund_quantity) as refunded_quantity
      , sum(refund_subtotal) as refunded_subtotal

    from {{models.shopify_order_line_item_refunds_base}}

    group by 1
) ,

  base as (
    select
        oli.order_id
      , oli.line_item_id
      , oli.variant_sku
      , oli.pre_discount_revenue
      , ((oli.pre_discount_revenue * oli.quantity) - coalesce(oli.discount_allocation_amount, 0)) as post_discount_revenue
      , osff.prorated_line_item_shipping
      , case when gcl.product_unit_cost is null then true else false end as is_missing_cogs_input
      , coalesce(
            gcl.product_unit_cost
            -- EXAMPLE ONLY — manual override COGS for specific products that don't drive revenue
            , case
                when oli.product_title = 'SOMEVALUE' then 1.50
                else null end
            , (oli.pre_discount_revenue * 0.41)
        ) * oli.quantity as total_product_cost
      , (gcl.shipping_cost * oli.quantity) as total_shipping_cost
      , (
          (case
               when oli.product_title = 'SOMEVALUE' then 1.50
               else gcl.other_cost end)
          * oli.quantity) as total_other_cost

      , lrff.refunded_quantity
      , lrff.refunded_subtotal

    from {{models.shopify_order_line_items_base}} oli
    left join {{models.shopify_orders_base}} ob on ob.order_id = oli.order_id
    -- EXAMPLE ONLY — replace with your own SKU-level COGS ledger model.
    -- Required columns: sku, product_unit_cost, shipping_cost, other_cost,
    -- start_date, end_date, index (row_number partitioned by sku, for dedup
    -- when a SKU has overlapping cost periods — take index = 1 as the active row).
    left join {{models.your_sku_cogs_ledger}} gcl on UPPER(gcl.sku) = UPPER(oli.variant_sku)
       and ob.created_at::date between gcl.start_date and gcl.end_date
       and gcl.index = 1
    left join {{models.shopify_order_line_item_prorated_shipping_facts}} osff on osff.order_id = oli.order_id
    left join line_refund_totals lrff on lrff.line_item_id = oli.line_item_id
)

select
    *
  , ( coalesce(post_discount_revenue, 0)
      + coalesce(prorated_line_item_shipping, 0)
     ) - coalesce(refunded_subtotal, 0)
    as net_revenue
  , (total_product_cost + coalesce(total_shipping_cost, 0) + coalesce(total_other_cost, 0)) as total_costs
  , (
      ( coalesce(post_discount_revenue, 0)
      + coalesce(prorated_line_item_shipping, 0) )
      - coalesce(refunded_subtotal, 0)
    ) - (total_product_cost + coalesce(total_shipping_cost, 0) + coalesce(total_other_cost, 0))
    as gross_profit
from base

select 
  oc.customer_id --PK
  , sum(case when (ob.created_at::date - oc.first_order_created_at::date) between 0 and 90 then lrf.net_revenue else null end) as "_90d_net_revenue" 
  , sum(case when (ob.created_at::date - oc.first_order_created_at::date) between 0 and  90 then lrf.gross_profit else null end) as "_90d_gross_profit"

  , sum(case when (ob.created_at::date - oc.first_order_created_at::date) between 0 and  180 then lrf.net_revenue else null end) as "_180d_net_revenue" 
  , sum(case when (ob.created_at::date - oc.first_order_created_at::date) between 0 and  180 then lrf.gross_profit else null end) as "_180d_gross_profit"

    , sum(case when (ob.created_at::date - oc.first_order_created_at::date) between 0 and  365 then lrf.net_revenue else null end) as "_365d_net_revenue" 
    , sum(case when (ob.created_at::date - oc.first_order_created_at::date) between 0 and  365 then lrf.gross_profit else null end) as "_365d_gross_profit"

from {{models.shopify_customer_order_facts}} oc
left join {{models.shopify_orders_base}} ob on ob.customer_id = oc.customer_id
left join {{models.shopify_order_sequence_facts}} osq on osq.order_id = ob.order_id
left join {{models.shopify_line_item_net_revenue_facts}} lrf on lrf.order_id = ob.order_id

where osq.order_sequence >= 1

group by 1 

select
    o.order_id --PK
  , o.created_at
  , o.customer_id
  , o.order_number
  , o.email
  , o.financial_status
  , o.fulfillment_status
  , o.order_source
  , o.is_cancelled
  , o.cancelled_at
  , o.total_price
  , o.net_payment
  , o.total_tax
  , o.total_discount
  , o.shipping_price
  , o.net_sales_including_shipping

  -- tag info
  , otf.list_tags

  -- discount info
  , odf.list_discount_code

  , seq.order_sequence

  -- fulfillment facts
  , off.first_successful_fulfillment_created_at
  , off.last_successful_fulfillment_created_at

  -- order-level revenue/profit rolled up from line items
  , lrf.order_net_revenue
  , lrf.order_gross_profit

from {{models.shopify_orders_base}} o
left join {{models.shopify_order_tag_facts}} otf on o.order_id = otf.order_id
left join {{models.shopify_order_discount_facts}} odf on o.order_id = odf.order_id
left join {{models.shopify_order_sequence_facts}} seq on o.order_id = seq.order_id
left join {{models.shopify_order_fulfillment_facts}} off on o.order_id = off.order_id
left join (
  select
      order_id
    , sum(net_revenue) as order_net_revenue
    , sum(gross_profit) as order_gross_profit
  from {{models.shopify_line_item_net_revenue_facts}}
  group by 1
) lrf on o.order_id = lrf.order_id

sselect
    li.order_id
  , li.line_item_id --PK
  , li.line_item_title
  , li.product_title
  , li.variant_sku
  , li.quantity
  , li.fulfillment_status
  , li.pre_discount_revenue
  , disc.discount_amount
  , li.pre_discount_revenue - coalesce(disc.discount_amount, 0) as revenue_net_of_discounts

  -- net revenue / margin facts
  , nrf.post_discount_revenue
  , nrf.prorated_line_item_shipping
  , nrf.total_product_cost
  , nrf.total_shipping_cost
  , nrf.total_other_cost
  , nrf.total_costs
  , nrf.refunded_quantity
  , nrf.refunded_subtotal
  , nrf.net_revenue
  , nrf.gross_profit
  , nrf.is_missing_cogs_input

from {{models.shopify_order_line_items_base}} li
left join {{models.shopify_order_line_item_discount_facts}} disc on li.line_item_id = disc.line_item_id
left join {{models.shopify_line_item_net_revenue_facts}} nrf on li.line_item_id = nrf.line_item_id

select
    c.customer_id --PK
  , c.created_at
  , c.email
  , c.first_name
  , c.last_name
  , c.phone
  , c.note
  , c.default_address
  , cof.first_order_created_at
  , cof.last_order_created_at

  -- LTV facts
  , ltv."_90d_net_revenue"
  , ltv."_90d_gross_profit"
  , ltv."_180d_net_revenue"
  , ltv."_180d_gross_profit"
  , ltv."_365d_net_revenue"
  , ltv."_365d_gross_profit"

from {{models.shopify_customers_base}} c
left join {{models.shopify_customer_order_facts}} cof on c.customer_id = cof.customer_id
left join {{models.shopify_customer_nth_month_ltv_facts}} ltv on c.customer_id = ltv.customer_id