> ## 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. # rpt_affiliate_performance_daily > Daily affiliate program performance — conversions, commission, and new vs. repeat customer breakdown per affiliate, campaign, and promo code. Today, this report contains data from **Superfiliate**. Support for additional affiliate platforms — Impact, ShareASale, and others — is on the roadmap. If there's a platform you'd like us to integrate with, reach out to your Customer Solutions Engineer. ```yaml theme={null} version: 2 models: - name: rpt_affiliate_performance_daily description: > Daily affiliate program performance — conversions, commission, order revenue, and new-vs-repeat customer breakdown. Grain: One row per (sm_store_id, date, source_system, affiliate_id, campaign_id, promo_code). Date field: date. Critical filters: source_system for platform (currently 'superfiliate'); affiliate_id, campaign_id, promo_code for drilldown. Key joins: blend with rpt_ad_performance_daily via (date, source_system) for cross-channel comparison; affiliate_id is platform-native. A customer is "new" on their first valid order under this sm_store_id. Conversions whose attributed order cannot be matched, or matches an invalid order (refunded, cancelled, voided, excluded by the configured $0 net-revenue rule, or off-channel), are counted as new — a conservative default. columns: - name: sm_store_id description: > SourceMedium's unique store identifier. - name: date description: > UTC date of the conversions in this row, derived from the affiliate platform's conversion timestamp. - name: source_system description: > Affiliate platform the conversions originated from. Currently 'superfiliate'. - name: affiliate_id description: > Platform-native affiliate identifier (referral_code for Superfiliate). - name: campaign_id description: > Platform-native affiliate campaign identifier. - name: promo_code description: > Referral or voucher code used in the conversions. - name: affiliate_name description: > Affiliate display name, resolved from the platform's customer/publisher record. Stable for a given affiliate_id. - name: campaign_name description: > Platform-native campaign name. Stable for a given campaign_id. - name: is_order_joinable description: > Whether conversions in this row support order-level new/repeat classification. Always true for Superfiliate. - name: conversions description: > Total conversion count from the affiliate platform. - name: new_customer_conversions description: > Conversions attributed to first-time customers (first valid order under this sm_store_id). Conversions whose attributed order cannot be matched or is invalid are also counted here as the conservative default. - name: repeat_customer_conversions description: > Conversions confirmed as repeat customers (a prior valid order exists under this sm_store_id). - name: order_revenue description: > Order revenue as reported by the affiliate platform (Superfiliate's total_value). Note this is platform-reported revenue, not SourceMedium-canonical order revenue from obt_orders. - name: commission_amount description: > Total commission paid to affiliates for the conversions in this row. - name: new_customer_order_revenue description: > Platform-reported order revenue from new-customer conversions only. Repeat-customer conversions contribute 0. - name: new_customer_commission_amount description: > Commission paid for new-customer conversions only. Repeat-customer conversions contribute 0. - name: commission_rate description: > commission_amount divided by order_revenue. NULL when order_revenue is zero. ``` ## Example queries Use the same standards as the rest of the SQL Query Library: `your_project` and `your-sm_store_id` placeholders, `SAFE_DIVIDE` for safe division, and `DATE()` wrapping when comparing timestamps. Replace the `source_system` filter as additional affiliate platforms come online. **What you'll learn:** Which affiliates are driving the most revenue and what the platform-reported return on commission spend looks like per affiliate. ```sql theme={null} -- Assumptions: timeframe=30d | metric=commission_and_roas | grain=affiliate | scope=sm_transformed_v2 SELECT affiliate_id, ANY_VALUE(affiliate_name) AS affiliate_name, SUM(conversions) AS conversions, SUM(order_revenue) AS order_revenue, SUM(commission_amount) AS commission_amount, SAFE_DIVIDE(SUM(order_revenue), SUM(commission_amount)) AS affiliate_roas, SAFE_DIVIDE(SUM(commission_amount), SUM(order_revenue)) AS commission_rate FROM `your_project.sm_transformed_v2.rpt_affiliate_performance_daily` WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND source_system = 'superfiliate' GROUP BY affiliate_id ORDER BY commission_amount DESC LIMIT 25; ``` **What you'll learn:** Which affiliate campaigns are bringing in net-new customers vs driving repeat purchases. Useful for evaluating campaign mix and acquisition efficiency. ```sql theme={null} -- Assumptions: timeframe=90d | metric=new_vs_repeat_share | grain=campaign | scope=sm_transformed_v2 SELECT campaign_id, ANY_VALUE(campaign_name) AS campaign_name, SUM(conversions) AS conversions, SUM(new_customer_conversions) AS new_customer_conversions, SUM(repeat_customer_conversions) AS repeat_customer_conversions, SAFE_DIVIDE(SUM(new_customer_conversions), SUM(conversions)) AS new_customer_share, SUM(new_customer_order_revenue) AS new_customer_order_revenue, SUM(new_customer_commission_amount) AS new_customer_commission_amount FROM `your_project.sm_transformed_v2.rpt_affiliate_performance_daily` WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND source_system = 'superfiliate' GROUP BY campaign_id ORDER BY conversions DESC LIMIT 25; ``` **What you'll learn:** How affiliate ROAS stacks up against your paid ad platforms on a like-for-like basis. Commission is treated as affiliate spend; platform-reported revenue is the numerator on both sides. ```sql theme={null} -- Assumptions: timeframe=30d | metric=roas | grain=source_system | scope=sm_transformed_v2 WITH ads AS ( SELECT source_system, SUM(CAST(ad_spend AS FLOAT64)) AS spend, SUM(CAST(ad_platform_reported_revenue AS FLOAT64)) AS revenue FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily` WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY source_system ), affiliates AS ( SELECT source_system, SUM(CAST(commission_amount AS FLOAT64)) AS spend, SUM(CAST(order_revenue AS FLOAT64)) AS revenue FROM `your_project.sm_transformed_v2.rpt_affiliate_performance_daily` WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY source_system ) SELECT source_system, spend, revenue, SAFE_DIVIDE(revenue, spend) AS roas FROM (SELECT * FROM ads UNION ALL SELECT * FROM affiliates) ORDER BY roas DESC; ``` **What you'll learn:** Trended affiliate performance — useful for spotting weekend-vs-weekday patterns, promo-driven spikes, and whether the effective commission rate is drifting over time. ```sql theme={null} -- Assumptions: timeframe=90d | metric=daily_revenue_and_commission | grain=date | scope=sm_transformed_v2 SELECT date, SUM(conversions) AS conversions, SUM(order_revenue) AS order_revenue, SUM(commission_amount) AS commission_amount, SAFE_DIVIDE(SUM(commission_amount), SUM(order_revenue)) AS daily_commission_rate FROM `your_project.sm_transformed_v2.rpt_affiliate_performance_daily` WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND source_system = 'superfiliate' GROUP BY date ORDER BY date DESC; ``` ## Related tables Cross-platform daily advertising performance. Superfiliate appears here too via `source_system`, alongside Meta, Google, and other paid platforms — useful for direct channel-level comparison. Connection setup, data coverage, and the questions this integration is designed to answer.