Skip to main content

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.

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;