Skip to main content
Refunds can hit multiple components of an order (items, shipping, taxes, duties). SourceMedium tracks these separately so you can build accurate refund-rate and profitability analysis.

​
Core refund fields (order-level)

In your_project.sm_transformed_v2.obt_orders you’ll commonly see:
  • order_refunds: item-level refunds
  • order_shipping_refunds: shipping refunds
  • order_tax_refunds: tax refunds (not shipping tax)
  • order_shipping_tax_refunds: shipping tax refunds
  • order_duty_refunds: duty refunds (when applicable)
  • order_total_refunds: order refunds + shipping refunds
Refund timing fields include:
  • earliest_order_refund_date
  • latest_order_refund_date
  • order_to_refund_days_earliest / _latest (clamped to 0 for rare timing edge cases)

​
Sign convention

Refund fields are intended to be negative (or zero) in most reporting tables. That makes netting behavior consistent:
  • Add refunds to subtract revenue
  • Sum refunds to get “refunds as a negative number”

​
Common patterns

​
Refund rate (revenue-weighted)

SELECT
  DATE_TRUNC(DATE(order_processed_at_local_datetime), WEEK(MONDAY)) AS week_start,
  SUM(order_gross_revenue) AS gross_revenue,
  SUM(order_refunds) AS refunds,
  SAFE_DIVIDE(ABS(SUM(order_refunds)), NULLIF(SUM(order_gross_revenue), 0)) AS refund_rate
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_processed_at_local_datetime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY 1
ORDER BY week_start DESC;

​
Identify “over-refunded” orders

SELECT
  sm_order_key,
  order_id,
  order_gross_revenue,
  order_refunds
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND ABS(order_refunds) > order_gross_revenue
ORDER BY ABS(order_refunds) DESC
LIMIT 200;