Sales by Product

-- staging.shopify.order
-- Thin wrapper over raw `order`. Casts, renames, drops test orders. No other logic.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
    'store_1' as shopify_store
  , cast(id as int64) as order_id
  , cast(name as string) as order_name
  , cast(customer_id as int64) as customer_id
  , cast(location_id as int64) as location_id
  , lower(cast(source_name as string)) as source_name
  , cast(processed_at as timestamp) as processed_at
  , cast(created_at as timestamp) as created_at
  , cast(cancelled_at as timestamp) as cancelled_at
  , cast(closed_at as timestamp) as closed_at
  , -- COALESCE to '' so downstream NOT IN (...) filters keep NULL-status orders
    -- rather than silently dropping them.
    lower(coalesce(cast(financial_status as string), '')) as financial_status
  , lower(cast(fulfillment_status as string)) as fulfillment_status
  , -- The order's own currency, preferred over the shop's CURRENT currency.
    -- Stores that changed base currency, or imported history from another
    -- platform, hold orders denominated in something else; defaulting to today's
    -- shop currency would mislabel them by the full FX factor.
    upper(
        nullif(
            trim(
                cast(
                    current_total_price_set_shop_money_currency_code as string
                )
            )
          , ''
        )
    ) as order_shop_currency
  , upper(nullif(trim(cast(currency as string)), '')) as currency
  , upper(
        nullif(
            trim(
                cast(
                    current_total_price_set_presentment_money_currency_code as string
                )
            )
          , ''
        )
    ) as order_presentment_currency
  , upper(
        nullif(trim(cast(presentment_currency as string)), '')
    ) as presentment_currency
  , upper(
        nullif(
            trim(cast(shipping_address_country_code as string))
          , ''
        )
    ) as shipping_country_code
  , upper(
        nullif(
            trim(cast(billing_address_country_code as string))
          , ''
        )
    ) as billing_country_code
from
    {{raw.shopify.order}}
where
    coalesce(test, false) = false

-- staging.shopify.order_line
-- Product identity and line money. Gift cards are NOT filtered here - staging
-- stays neutral and consumers decide. `is_gift_card` and `requires_shipping` are
-- exposed because the sales report needs them to classify orders.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
    'store_1' as shopify_store
  , cast(id as int64) as line_id
  , cast(order_id as int64) as order_id
  , cast(product_id as int64) as product_id
  , cast(variant_id as int64) as variant_id
  , cast(sku as string) as sku
  , nullif(trim(cast(title as string)), '') as product_title
  , cast(variant_title as string) as variant_title
  , cast(vendor as string) as vendor
  , cast(quantity as int64) as quantity
  , coalesce(cast(gift_card as bool), false) as is_gift_card
  , coalesce(cast(requires_shipping as bool), true) as requires_shipping
  , coalesce(
        cast(price_set_shop_money_amount as numeric)
      , cast(price as numeric)
    ) as unit_price
  , coalesce(
        cast(price_set_shop_money_amount as numeric)
      , cast(price as numeric)
    ) * cast(quantity as int64) as gross_sales
  , coalesce(
        cast(total_discount_set_shop_money_amount as numeric)
      , cast(total_discount as numeric)
      , 0
    ) as discounts
from
    {{raw.shopify.order_line}}

-- staging.shopify.product
-- Canonical product attributes. order_line carries a snapshot taken at purchase
-- time; prefer these when a product was later renamed or recategorised.
--
-- Single store. To add another, UNION ALL a second block below pointing at that
-- store's connector with a different shopify_store label. Keep it in staging so
-- the core models never have to know how many stores there are.
select
    'store_1' as shopify_store
  , cast(id as int64) as product_id
  , cast(title as string) as product_title
  , cast(product_type as string) as product_type
  , cast(vendor as string) as vendor
  , lower(cast(status as string)) as status
from
    {{raw.shopify.product}}

-- analytics.shopify.sales_by_product
-- BI-facing contract over the core model. See analytics/sales_over_time.sql for
-- why this layer exists even when it is a passthrough.
select
    *
from
    {{core.shopify.sales_by_product}}