rpt_affiliate_performance_daily

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.

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, fraud/declined-cancelled, 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.

Top affiliates by commission and affiliate ROAS, last 30 days

What you’ll learn: Which affiliates are driving the most revenue and what the platform-reported return on commission spend looks like per affiliate.

-- 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.

-- 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;

Affiliate vs paid channels — ROAS comparison, last 30 days

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.

-- 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;

Daily affiliate revenue, commission, and effective rate

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.

-- 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;

rpt_ad_performance_daily

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.

Superfiliate integration

Connection setup, data coverage, and the questions this integration is designed to answer.

Getting Started

Quick Reference

Table Documentation

Schema Standards

Platform Raw Data

rpt_affiliate_performance_daily

Example queries

rpt_ad_performance_daily

Superfiliate integration

​Example queries

​Related tables

rpt_ad_performance_daily

Superfiliate integration

Example queries

Related tables