Amazon Seller Central Models

select
    TRIM(amazon_order_id::varchar) as amazon_order_id --pk
  , TRIM(seller_order_id::varchar) as seller_order_id
  , purchase_date::timestamp as purchase_date
  , last_update_date::timestamp as last_update_date
  , LOWER(TRIM(order_status::varchar)) as order_status
  , LOWER(TRIM(fulfillment_channel::varchar)) as fulfillment_channel
  , LOWER(TRIM(sales_channel::varchar)) as sales_channel
  , LOWER(TRIM(order_type::varchar)) as order_type
  , LOWER(TRIM(ship_service_level::varchar)) as ship_service_level
  , TRIM(order_total_currency_code::varchar) as order_total_currency_code
  , NULLIF(TRIM(order_total_amount), '')::float as order_total_amount
  , number_of_items_shipped::integer as n_items_shipped
  , number_of_items_unshipped::integer as n_items_unshipped
  , TRIM(marketplace_id::varchar) as marketplace_id
  , is_business_order
  , is_prime
  , is_replacement_order
  , TRIM(shipping_address_city::varchar) as shipping_city
  , TRIM(shipping_address_state_or_region::varchar) as shipping_state
  , TRIM(shipping_address_country_code::varchar) as shipping_country_code
  , TRIM(shipping_address_postal_code::varchar) as shipping_postal_code
  , _fivetran_synced as fivetran_synced_at
from {{sources.amazon_seller_central.orders}}

select
    TRIM(order_item_id::varchar) as order_item_id --pk
  , TRIM(amazon_order_id::varchar) as amazon_order_id
  , TRIM(asin::varchar) as asin
  , TRIM(seller_sku::varchar) as seller_sku
  , title::varchar as title
  , quantity_ordered::integer as quantity_ordered
  , quantity_shipped::integer as quantity_shipped
  , TRIM(item_price_currency_code::varchar) as item_price_currency_code
  , NULLIF(TRIM(item_price_amount), '')::float as item_price_amount
  , NULLIF(TRIM(item_tax_amount), '')::float as item_tax_amount
  , NULLIF(TRIM(shipping_price_amount), '')::float as shipping_price_amount
  , NULLIF(TRIM(shipping_discount_amount), '')::float as shipping_discount_amount
  , NULLIF(TRIM(promotion_discount_amount), '')::float as promotion_discount_amount
  , is_gift
  , _fivetran_synced as fivetran_synced_at
from {{sources.amazon_seller_central.order_item}}

select
    TRIM(order_item_id::varchar) || '-' || TRIM(promotion_id::varchar) as id --pk (composite order_item_id + promotion_id)
  , TRIM(amazon_order_id::varchar) as amazon_order_id
  , TRIM(order_item_id::varchar) as order_item_id
  , TRIM(promotion_id::varchar) as promotion_id
  , _fivetran_synced as fivetran_synced_at
from {{sources.amazon_seller_central.order_item_promotion_id}}

select
    TRIM(shipment_item_id::varchar) as shipment_item_id --pk
  , TRIM(amazon_order_id::varchar) as amazon_order_id
  , TRIM(amazon_order_item_id::varchar) as amazon_order_item_id
  , TRIM(shipment_id::varchar) as shipment_id
  , TRIM(marketplace_id::varchar) as marketplace_id
  , TRIM(sku::varchar) as sku
  -- only buyer identifier on the whole connector — anonymized Amazon alias
  , LOWER(TRIM(buyer_email::varchar)) as buyer_email
  , product_name::varchar as product_name
  , NULLIF(TRIM(quantity_shipped), '')::integer as quantity_shipped
  , TRIM(currency::varchar) as currency
  , item_price
  , item_tax
  , shipping_price
  , NULLIF(TRIM(item_promotion_discount), '')::float as item_promotion_discount
  , NULLIF(TRIM(ship_promotion_discount), '')::float as ship_promotion_discount
  , LOWER(TRIM(carrier::varchar)) as carrier
  , TRIM(fulfillment_center_id::varchar) as fulfillment_center_id
  , LOWER(TRIM(fulfillment_channel::varchar)) as fulfillment_channel
  , TRIM(tracking_number::varchar) as tracking_number
  , purchase_date::timestamp as purchase_date
  , shipment_date::timestamp as shipment_date
  , payments_date::timestamp as payments_date
  , reporting_date::timestamp as reporting_date
  , estimated_arrival_date::timestamp as estimated_arrival_date
  , _fivetran_synced as fivetran_synced_at
from {{sources.amazon_seller_central.fulfilled_shipments_data_general_report}}

select
    _fivetran_id::varchar as settlement_line_id --pk (no natural key; sync-tool row id)
  , TRIM(settlement_id)::varchar as settlement_id
  , NULLIF(TRIM(order_id), '')::varchar as order_id
  , NULLIF(TRIM(order_item_code), '')::varchar as order_item_id
  , NULLIF(TRIM(shipment_id), '')::varchar as shipment_id
  , NULLIF(TRIM(sku), '')::varchar as sku
  , NULLIF(TRIM(promotion_id), '')::varchar as promotion_id
  , LOWER(TRIM(transaction_type))::varchar as transaction_type
  , TRIM(amount_type)::varchar as amount_type
  , TRIM(amount_description)::varchar as amount_description
  , NULLIF(TRIM(amount), '')::float as amount
  , quantity_purchased::integer as quantity_purchased
  , TRIM(marketplace_name)::varchar as marketplace_name
  , posted_date_time as posted_date_time
  , _fivetran_synced as fivetran_synced_at
from {{sources.amazon_seller_central.settlement_report}}

select
    o.amazon_order_id as amazon_order_id --pk
  , o.seller_order_id
  , o.purchase_date
  , o.last_update_date
  , o.order_status
  , o.fulfillment_channel
  , o.sales_channel
  , o.order_type
  , o.ship_service_level
  , o.order_total_currency_code
  , o.order_total_amount
  , o.n_items_shipped
  , o.n_items_unshipped
  , o.marketplace_id
  , o.is_business_order
  , o.is_prime
  , o.is_replacement_order
  , o.shipping_city
  , o.shipping_state
  , o.shipping_country_code
  , o.shipping_postal_code
  -- item-grain rollups joined in as enrichment
  , ir.n_line_items
  , ir.total_quantity_ordered
  , ir.total_quantity_shipped
  , ir.total_gross_revenue
  , ir.total_promotion_discount
  , ir.total_net_revenue
  , ir.total_principal
  , ir.total_item_fees
  , ir.total_net_settlement
  , ir.order_asin_list
  , ir.order_sku_list
from {{models.amazon_seller_central_orders_base}} o
left join item_rollup ir on o.amazon_order_id = ir.amazon_order_id

with
  item_promotions as (
    --collapse the many-to-one promo bridge (up to 2 promos/item) to one row per item
    --so the join cannot fan out and double-count
    select
        order_item_id
      , STRING_AGG(promotion_id, ', ' ORDER BY promotion_id) as list_promotion_ids
      , count(*) as n_promotions
    from {{models.amazon_seller_central_order_item_promotions_base}}
    group by 1
) ,

  line_settlement as (
    --net proceeds / fees per order item from the settlement report
    select
        order_item_id
      , sum(amount) filter (where amount_description = 'Principal') as principal_amount
      , sum(amount) filter (where amount_type = 'ItemFees') as item_fees_amount
      , sum(amount) as net_settlement_amount
    from {{models.amazon_seller_central_settlement_report_base}}
    where order_item_id is not null
    group by 1
)

select
    oli.order_item_id as order_item_id --pk
  , oli.amazon_order_id
  , oli.asin
  , oli.seller_sku
  , oli.quantity_ordered
  , oli.quantity_shipped
  , oli.item_price_amount
  , coalesce(oli.promotion_discount_amount, 0) as promotion_discount_amount
  , (oli.item_price_amount - coalesce(oli.promotion_discount_amount, 0)) as net_revenue

  -- economics from settlement
  , ls.principal_amount
  , ls.item_fees_amount
  , ls.net_settlement_amount

  -- promotion identity, joined from the pre-aggregated bridge CTE
  , ip.list_promotion_ids
  , coalesce(ip.n_promotions, 0) as n_promotions
  , (ip.n_promotions > 0) as has_promotion

from {{models.amazon_seller_central_order_items_base}} oli
left join item_promotions ip on oli.order_item_id = ip.order_item_id
left join line_settlement ls on oli.order_item_id = ls.order_item_id

with deduplicated_shipments as (
  --buyer_email lives only on shipments (line-item grain); dedupe to one row per (order, buyer)
  select distinct
      amazon_order_id
    , buyer_email
  from {{models.amazon_seller_central_fulfilled_shipments_base}}
  where buyer_email is not null and TRIM(buyer_email) != ''
)

select
    o.amazon_order_id as amazon_order_id --pk
  , s.buyer_email
  , o.purchase_date
  , o.order_total_amount
  -- 1 = that buyer's first order; use to isolate first/Nth order downstream
  , row_number() over (
      partition by s.buyer_email
      order by o.purchase_date asc
    ) as order_sequence
from deduplicated_shipments s
inner join {{models.amazon_seller_central_orders_base}} o on s.amazon_order_id = o.amazon_order_id

select
    seq.buyer_email as buyer_email --pk
  , min(case when seq.order_sequence = 1 then seq.purchase_date end) as first_order_created_at
  , max(seq.purchase_date) as last_order_created_at
  , count(distinct seq.amazon_order_id) as order_count
  , sum(seq.order_total_amount) as total_revenue
  , min(case when seq.order_sequence = 1 then seq.order_total_amount end) as first_order_revenue
  , (count(distinct seq.amazon_order_id) > 1) as is_repeat_customer
  , max(case when seq.order_sequence = 2 then seq.purchase_date end) as second_order_created_at
  , max(case when seq.order_sequence = 3 then seq.purchase_date end) as third_order_created_at
  , max(case when seq.order_sequence = 4 then seq.purchase_date end) as fourth_order_created_at
  , max(case when seq.order_sequence = 5 then seq.purchase_date end) as fifth_order_created_at
  , max(case when seq.order_sequence = 6 then seq.purchase_date end) as sixth_order_created_at
from {{models.amazon_seller_central_order_sequence_facts}} seq
group by 1

--windowed net revenue per buyer measured from their first order (LTR / nth-day LTV)
select
    cof.buyer_email as buyer_email --pk
  , sum(case when (seq.purchase_date::date - cof.first_order_created_at::date) between 0 and 90  then seq.order_total_amount end) as "_90d_revenue"
  , sum(case when (seq.purchase_date::date - cof.first_order_created_at::date) between 0 and 180 then seq.order_total_amount end) as "_180d_revenue"
  , sum(case when (seq.purchase_date::date - cof.first_order_created_at::date) between 0 and 365 then seq.order_total_amount end) as "_365d_revenue"
  , sum(case when (seq.purchase_date::date - cof.first_order_created_at::date) between 0 and 730 then seq.order_total_amount end) as "_730d_revenue"
from {{models.amazon_sc_customer_order_facts}} cof
left join {{models.amazon_seller_central_order_sequence_facts}} seq on cof.buyer_email = seq.buyer_email
group by 1

select
    f.amazon_order_id as amazon_order_id --pk
  , s.buyer_email
  , f.seller_order_id
  , f.purchase_date
  , f.last_update_date
  , f.order_status
  , f.fulfillment_channel
  , f.sales_channel
  , f.order_type
  , f.ship_service_level
  , f.order_total_currency_code
  , f.order_total_amount
  , f.n_items_shipped
  , f.n_of_items_unshipped
  , f.marketplace_id
  , f.is_business_order
  , f.is_prime
  , f.shipping_city
  , f.shipping_state
  , f.shipping_country_code
  , f.shipping_postal_code
from {{models.amazon_seller_central_order_facts}} f
left join {{models.amazon_seller_central_order_sequence_facts}} s on f.amazon_order_id = s.amazon_order_id

select
    oif.order_item_id as order_item_id --pk
  , oif.amazon_order_id
  , o.purchase_date
  , o.order_status
  , o.marketplace_id
  , o.sales_channel
  , oif.asin
  , oif.seller_sku
  , oif.quantity_ordered
  , oif.quantity_shipped
  , oif.item_price_amount
  , oif.promotion_discount_amount
  , oif.net_revenue
  , oif.principal_amount
  , oif.item_fees_amount
  , oif.net_settlement_amount
  -- promotion attribution surfaced at line-item grain
  , oif.list_promotion_ids
  , oif.n_promotions
  , oif.has_promotion
from {{models.amazon_seller_central_order_items_facts}} oif
left join {{models.amazon_seller_central_orders_base}} o on oif.amazon_order_id = o.amazon_order_id

select
    cof.buyer_email as buyer_email --pk
  -- shipping location is the best proxy for "who" the buyer is (no real identity in SP-API)
  , o.shipping_city
  , o.shipping_state
  , o.shipping_country_code
  , o.shipping_postal_code
  , cof.first_order_created_at
  , cof.last_order_created_at
  , cof.order_count
  , cof.total_revenue
  , cof.first_order_revenue
  , cof.is_repeat_customer
  , cof.second_order_created_at
  , cof.third_order_created_at
  , cof.fourth_order_created_at
  , cof.fifth_order_created_at
  , cof.sixth_order_created_at
  , ltv."_90d_revenue"
  , ltv."_180d_revenue"
  , ltv."_365d_revenue"
  , ltv."_730d_revenue"
from {{models.amazon_seller_central_customer_order_facts}} cof
left join {{models.amazon_seller_central_customer_ltv_facts}} ltv on cof.buyer_email = ltv.buyer_email
left join {{models.amazon_seller_central_order_sequence_facts}} seq on cof.buyer_email = seq.buyer_email and seq.order_sequence = 1
left join {{models.amazon_seller_central_order_facts}} o on seq.amazon_order_id = o.amazon_order_id