> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# obt_affiliate_conversions
> Affiliate and creator conversions at order grain — one row per conversion, joinable directly to your orders.
Today this table contains data from **Superfiliate** and **ShopMy**. Support for additional affiliate platforms is on the roadmap. If there is a platform you would like us to integrate with, reach out to your Customer Solutions Engineer.
To calculate a commission rate for any period, recompute it as `SUM(commission_amount) / SUM(order_revenue)`. Never average `conversion_commission_rate`, which is a single order's ratio. On real data the average understated the true daily rate by 8.6%.
`source_currency` is the unit of `order_revenue` and `commission_amount`, and it is **not** the same for every platform. Filter or group by it before summing money across platforms. ShopMy amounts are USD, pre-converted by ShopMy. Superfiliate reports no currency at all, so its amounts are in your store's own currency and carry an unknown-currency marker.
## When to use this table
Use `obt_affiliate_conversions` when you need affiliate or creator activity at the level of individual orders. Each row is one conversion, which for every current platform is one order.
This table replaces `rpt_affiliate_performance_daily`, which summarized to a daily total per affiliate and in doing so dropped the order identity. Because this table carries `sm_order_key`, affiliate activity now joins directly to the rest of your warehouse.
Common questions this table can answer:
* Which specific orders did a creator or affiliate drive?
* What did those customers actually buy, by joining through to order lines?
* Which affiliates bring in first-time customers rather than repeat buyers?
* What is the true commission rate for a period, affiliate, or campaign?
* How long after a creator link click did the order happen? (ShopMy)
## Rebuilding the daily affiliate report
Everything the retired `rpt_affiliate_performance_daily` showed is one aggregation over this table:
```sql theme={null}
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
```
Note the commission rate is recomputed from the summed amounts, per the warning above.
## Common joins
* Join to [`obt_orders`](/docs/data-activation/data-tables/sm_transformed_v2/obt_orders) on `sm_order_key` for full order detail, channel, and customer attributes.
* Join to [`obt_order_lines`](/docs/data-activation/data-tables/sm_transformed_v2/obt_order_lines) on `sm_order_key` to see what products an affiliate order contained.
* Compare against [`rpt_ad_performance_daily`](/docs/data-activation/data-tables/sm_transformed_v2/rpt_ad_performance_daily) on `(date, source_system)` for cross-channel spend comparison. Affiliate commission appears there as spend.
`sm_order_key` is NULL when a conversion could not be matched to an order. Read it together with `is_new_customer`: on an unmatched row, `is_new_customer` falls back to TRUE rather than being derived, so a NULL key means "assumed new", not "confirmed new". Filter on `sm_order_key IS NOT NULL` when you need only confirmed matches.
```yaml theme={null}
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.
```