Shopify Refund Analytics Reporter

Task

Use @Shopify/Get Customer Orders by EmailName it "Shopify/Get Customer Orders by Email" and call it with @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 RefundName it "Shopify/Calculate Refund" and call it with @Shopify/Calculate Refund to verify refund amounts where needed. Log the analysis to @Google Sheets/Add RowsName it "Google Sheets/Add Rows" and call it with @Google Sheets/Add Rows and send a summary report via @Slack/Send MessageName it "Slack/Send Message" and call it with @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@coName it "co" and call it with @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@coName it "co" and call it with @co.com about repeated returns — possible fit issue"]

Full data: [Spreadsheet link]