Your refund rate tells you what percentage of revenue you're giving back. A high refund rate can signal product issues, unclear expectations, or fraud. Stripe doesn't display refund rate as a metric directly, so you need to query the API, use Sigma SQL, or export data to see the trend.
Query Refunds via the Stripe API
The most straightforward way is to list all refunds in a date range and compare them to your charge volume.
Step 1: Authenticate and initialize the Stripe client
Install the Stripe Node.js SDK and authenticate with your secret key. This key has full access to your account, so keep it secure in environment variables.
const stripe = require('stripe')(process.env.STRIPE_SECRET_KEY);
// Verify the key works
stripe.accounts.retrieve().then(account => {
console.log('Connected to:', account.email);
});Step 2: Fetch all refunds and charges in a date range
Use stripe.refunds.list() and stripe.charges.list() with Unix timestamps to get data. Calculate total refunded amount and total charged amount.
const startDate = Math.floor(new Date('2024-01-01').getTime() / 1000);
const endDate = Math.floor(new Date('2024-12-31').getTime() / 1000);
const refunds = await stripe.refunds.list({
limit: 100,
created: {gte: startDate, lte: endDate}
});
const charges = await stripe.charges.list({
limit: 100,
created: {gte: startDate, lte: endDate}
});
const totalRefunded = refunds.data.reduce((sum, r) => sum + r.amount, 0);
const totalCharged = charges.data.reduce((sum, c) => sum + c.amount, 0);
const refundRate = ((totalRefunded / totalCharged) * 100).toFixed(2);
console.log(`Refund Rate: ${refundRate}%`);Step 3: Handle pagination for high-volume accounts
If you have thousands of transactions, the API returns max 100 per request. Use autoPagingEach() to iterate through all pages automatically.
let totalRefunded = 0;
let chargeCount = 0;
await stripe.charges.list({created: {gte: startDate, lte: endDate}}).autoPagingEach(async (charge) => {
chargeCount++;
totalRefunded += charge.amount_refunded || 0;
});
console.log(`Processed ${chargeCount} charges, ${totalRefunded / 100} total refunded`);amount_refunded gives you the actual refunded portion, not just a boolean. A charge can be partially refunded, so this is more accurate than counting refunded charges.Use Stripe Sigma for SQL Queries
For recurring reports, Stripe Sigma lets you write SQL directly in the dashboard without API calls or code changes.
Step 1: Open Sigma in your Stripe Dashboard
Go to Developers > Sigma and click Create Report. This opens a SQL editor where you can query your Stripe data.
-- Monthly refund rate trend
SELECT
DATE_TRUNC('month', charges.created) AS month,
COUNT(*) AS total_charges,
ROUND(100.0 * SUM(CASE WHEN charges.amount_refunded > 0 THEN charges.amount_refunded ELSE 0 END) / SUM(charges.amount), 2) AS refund_rate_pct,
SUM(charges.amount) / 100 AS total_charged,
SUM(CASE WHEN charges.amount_refunded > 0 THEN charges.amount_refunded ELSE 0 END) / 100 AS total_refunded
FROM charges
WHERE charges.created >= '2024-01-01' AND charges.status = 'succeeded'
GROUP BY DATE_TRUNC('month', charges.created)
ORDER BY month DESC;Step 2: Run the query and export results
Click Run Query. Once complete, click Download and select CSV to export. You can save this as a scheduled report to run daily or weekly.
-- Refund rate by reason
SELECT
refunds.reason,
COUNT(*) AS count,
SUM(refunds.amount) / 100 AS total_refunded,
ROUND(100.0 * SUM(refunds.amount) / (SELECT SUM(charges.amount) FROM charges WHERE charges.status = 'succeeded'), 2) AS pct_of_revenue
FROM refunds
WHERE refunds.created >= '2024-01-01'
GROUP BY refunds.reason
ORDER BY total_refunded DESC;Import and Visualize the Data
Export your refund rate data to a BI tool or spreadsheet to create charts and track trends over time.
Step 1: Save the CSV locally or upload to a BI tool
Use the CSV from Sigma or format API data as CSV. Import into Google Sheets, Metabase, Tableau, or Looker.
const fs = require('fs');
const {stringify} = require('csv-stringify/sync');
const data = [
{month: '2024-01', refund_rate: 7.2, total_refunded: 850.00, total_charged: 11800.00},
{month: '2024-02', refund_rate: 8.1, total_refunded: 920.00, total_charged: 11350.00},
{month: '2024-03', refund_rate: 6.8, total_refunded: 780.00, total_charged: 11500.00}
];
const csv = stringify(data, {header: true});
fs.writeFileSync('refund_rates.csv', csv);
console.log('Exported to refund_rates.csv');Step 2: Create a line chart to spot trends
In your chosen tool, plot month on the X-axis and refund_rate on the Y-axis. This makes seasonal spikes and improvement trends visible.
// Example: append data to Google Sheets
// (Run this in Google Sheets > Extensions > Apps Script)
function appendRefundData() {
const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
const newRow = ['2024-04', 7.5, 875.00, 11600.00];
sheet.appendRow(newRow);
console.log('Added refund data for 2024-04');
}
appendRefundData();Common Pitfalls
- Forgetting to paginate: API calls return max 100 items. If you have high transaction volume, you'll miss data unless you use
autoPagingEach()or loop through all pages. - Comparing refunded charges to total charges: A charge can be partially refunded. Use
amount_refundedfield, not just a count of refunded vs. total charges. - Not filtering by charge status: Failed charges don't generate revenue, so including them skews your refund rate. Always filter with
WHERE charges.status = 'succeeded'in Sigma. - Treating refund rate as revenue churn: Refunds are not the same as churn. A customer might refund one purchase but keep using your product. Track both separately.
Wrapping Up
Calculating and visualizing refund rate in Stripe is straightforward using the API or Sigma SQL. Track this metric monthly to spot product issues and customer friction early. If you want to track this automatically across tools, Product Analyst can help.