obt_affiliate_conversions

SELECT
  sm_store_id,
  conversion_date AS date,
  source_system,
  affiliate_id,
  campaign_id,
  promo_code,
  COUNT(*)                                  AS conversions,
  COUNTIF(is_new_customer)                  AS new_customer_conversions,
  COUNTIF(NOT is_new_customer)              AS repeat_customer_conversions,
  SUM(order_revenue)                        AS order_revenue,
  SUM(commission_amount)                    AS commission_amount,
  SAFE_DIVIDE(SUM(commission_amount), NULLIF(SUM(order_revenue), 0)) AS commission_rate
FROM sm_transformed_v2.obt_affiliate_conversions
GROUP BY 1, 2, 3, 4, 5, 6

version: 2

models:
  - name: obt_affiliate_conversions
    description: >
      Affiliate and creator conversions at order grain. Grain: one row per (sm_store_id, source_system, conversion_id), which for every current platform is one row per order. Date field: conversion_date. Critical filters: source_system for platform; source_currency before summing money across platforms; sm_order_key IS NOT NULL for confirmed order matches. Key joins: obt_orders and obt_order_lines on sm_order_key. A customer is "new" on their first valid order under this sm_store_id; conversions that could not be matched to a valid order default to new, a conservative default.
    columns:
      - name: sm_store_id
        description: >
          SourceMedium's unique store identifier.

      - name: affiliate_conversion_key
        description: >
          Stable unique identifier for the conversion. Use this as the primary key of this table.

      - name: conversion_date
        description: >
          UTC date of the conversion, derived from the affiliate platform's conversion timestamp.

      - name: conversion_created_at
        description: >
          UTC timestamp the affiliate platform recorded the conversion.

      - name: source_system
        description: >
          Affiliate platform the conversion originated from. Currently 'superfiliate' or 'ShopMy'. This is the same column name and vocabulary used by rpt_ad_performance_daily.

      - name: platform_order_id
        description: >
          The order identifier exactly as the affiliate platform reports it. This is not always your Shopify order id: ShopMy reports an order name or number instead. Use sm_order_key to join to orders rather than this column.

      - name: sm_order_key
        description: >
          SourceMedium order key. Joins directly to obt_orders.sm_order_key and obt_order_lines.sm_order_key. NULL when the conversion could not be matched to an order.

      - name: matched_order_source_system
        description: >
          Source system of the matched order, for example Shopify. NULL when the conversion is unmatched.

      - name: valid_order_index
        description: >
          The matched order's position in that customer's sequence of valid orders, where 1 is their first. NULL when the conversion is unmatched.

      - name: affiliate_id
        description: >
          The platform's own affiliate identifier: the referral code for Superfiliate, the creator handle for ShopMy. Directly comparable to the identifier in that platform's own export.

      - name: affiliate_name
        description: >
          Affiliate or creator display name.

      - name: campaign_id
        description: >
          Platform-native campaign identifier. NULL for platforms with no campaign concept, including ShopMy.

      - name: campaign_name
        description: >
          Platform-native campaign name. NULL for platforms with no campaign concept.

      - name: promo_code
        description: >
          Discount or referral code used. NULL for conversions attributed by link rather than by code.

      - name: order_revenue
        description: >
          Order revenue as the affiliate platform reports it, in source_currency. This is the platform's reported figure, not SourceMedium's canonical order revenue from obt_orders, and the two can differ.

      - name: commission_amount
        description: >
          Commission owed to the affiliate for this conversion, in source_currency.

      - name: source_currency
        description: >
          The unit of order_revenue and commission_amount, as declared by the source platform. 'USD' for ShopMy, whose amounts are pre-converted by the vendor. An unknown-currency marker for Superfiliate, which reports no currency, meaning those amounts are in your store's own currency. Do not sum money across rows with different values here.

      - name: conversion_commission_rate
        description: >
          This single order's commission divided by its revenue, NULL when revenue is zero. To get a rate for any wider period, recompute SUM(commission_amount) / SUM(order_revenue) rather than averaging this column.

      - name: is_new_customer
        description: >
          TRUE when the matched order was that customer's first valid order. Defaults to TRUE when the conversion matched no order, so read it together with sm_order_key.

      - name: is_order_joinable
        description: >
          TRUE when the platform reports an order identifier capable of matching your orders. FALSE marks platforms with no order-level feed, whose is_new_customer is always the default.

      - name: affiliate_network_metadata
        description: >
          Platform-specific detail for the conversion, as a nested record: network, affiliate_profile_url, pin_link_url, order_status, order_native_currency, click_date, click_to_order_hours. ShopMy populates all seven; Superfiliate reports only network. Note that order_native_currency describes the currency the order was placed in and does not describe this row's money columns, whose unit is the top-level source_currency.