# Shopify Refund Analytics Reporter

> Spot refund patterns before they become profit leaks. Identify problem products, serial returners, and refund rate spikes across your Shopify store.

Source: https://cotera.co/solutions/ops/shopify-refund-analytics-reporter

---

- **Team:** Operations
- **Tools:** Shopify
- **Difficulty:** medium
- **Setup time:** 10 min
- **Time saved:** 3-5 hrs/report

## How it works
1. **Analyze Refund Patterns** — Calculate refund rates per customer and per product
2. **Flag Outliers** — Highlight customers and products above your thresholds
3. **Export Full Data** — Log customer-level and product-level data to Sheets
4. **Report to Slack** — Post a summary with flagged items and next steps

## Example requests
- Analyze refund patterns for our top 30 customers this quarter and flag anyone above 20%
- Which products have the highest return rates? Post the results to #product-team
- Calculate net revenue after refunds for these customer emails and export to Sheets

## Prompt

```markdown
## Task

Use @Shopify/Get Customer Orders by Email to pull order history for a set of customers, identify orders with refunds or pending refund requests, and use @Shopify/Calculate Refund to verify refund amounts where needed. Log the analysis to @Google Sheets/Add Rows and send a summary report via @Slack/Send Message.

**Example:** Analyze refund patterns across our top 30 customers this quarter. Flag anyone with a refund rate above 20% and identify which products get returned most.

## Input

The user will provide:
1. A list of customer email addresses to analyze
2. The Google Sheet ID for logging results
3. The Slack channel for the report (e.g., #finance-ops)
4. Optional: a refund rate threshold to flag (default: 20%)
5. Optional: time period to analyze (default: last 90 days)

**Example:** "Check refund patterns for these 25 customers. Flag anyone above 15% refund rate and post the report to #returns-review."

## Context

### What Counts as a Refund

**Financial statuses that indicate refunds:**
- financialStatus: "refunded" — fully refunded order
- financialStatus: "partially_refunded" — some items refunded
- These show up in the order data from Get Customer Orders by Email

**Use Calculate Refund when:**
- You need to verify exact refund amounts for specific line items
- The user wants to understand per-item refund costs
- You need shipping refund details for accurate totals

### Metrics to Calculate

**Per customer:**
- Total orders in period
- Orders with refunds (full or partial)
- Refund rate (refunded orders / total orders)
- Total revenue vs. total refunded amount
- Net revenue after refunds
- Most refunded products

**Per product (across all customers):**
- Times purchased vs. times refunded
- Product-level refund rate
- Total refund cost for that product

### Spreadsheet Structure

**Customer Sheet:**
| Email | Name | Total Orders | Refunded Orders | Refund Rate | Gross Revenue | Refund Amount | Net Revenue | Top Refunded Product | Flagged |

**Product Sheet:**
| Product | Times Purchased | Times Refunded | Product Refund Rate | Total Refund Cost | Flagged |

### Alert Thresholds

Flag and highlight when:
- Customer refund rate exceeds the threshold (default 20%)
- A single product has a refund rate above 30%
- Total refund amount exceeds $500 for a single customer in the period
- A sudden spike in refunds compared to the previous period

## Output

**Google Sheets:** Two sheets — one for customer-level data, one for product-level data. Flagged rows marked with YES.

**Slack Report:**

:chart_with_downwards_trend: **Refund Analytics Report — [Date Range]**

**Overview:**
- Customers analyzed: [Count]
- Total orders: [Count] | Refunded: [Count] ([Rate]%)
- Gross revenue: $[Amount] | Refunds: $[Amount] | Net: $[Amount]

**Flagged Customers ([Count]):**
| Customer | Orders | Refund Rate | Refund $ | Top Return |
|----------|--------|------------|----------|------------|
| jane@co.com | 12 | 33% | $450 | Blue Widget |

**Problem Products ([Count]):**
| Product | Purchased | Returned | Return Rate |
|---------|-----------|----------|-------------|
| Blue Widget | 45 | 18 | 40% |

**Recommended Actions:**
1. [Action — e.g., "Review Blue Widget quality — 40% return rate suggests a product issue"]
2. [Action — e.g., "Reach out to jane@co.com about repeated returns — possible fit issue"]

Full data: [Spreadsheet link]
```

## Related reading
- [Automate Shopify Refunds](https://cotera.co/articles/automate-shopify-refunds.md)
- [Calendly Workflow Automation](https://cotera.co/articles/calendly-workflow-automation.md)
- [GA4 Content Analytics Automation](https://cotera.co/articles/ga4-content-analytics-automation.md)