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

# Refund Logic

> Refund fields, components, and best-practice refund analysis patterns.

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)

```sql theme={null}
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 >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 90 DAY)
GROUP BY 1
ORDER BY week_start DESC;
```

### Identify “over-refunded” orders

```sql theme={null}
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;
```

## Related resources

* [Revenue Fields](/help-center/core-concepts/data-definitions/revenue-fields)
* [`obt_orders` column docs](/data-activation/data-tables/sm_transformed_v2/obt_orders)