You're processing hundreds of transactions daily in Stripe, but analyzing that raw event data manually breaks down fast. Sigma is Stripe's built-in SQL analytics tool that lets you query your payment data directly—but connecting those queries to your application requires understanding how to fetch and integrate the results. Here's how to track what matters.
Fetch Event Data and Write Your First Sigma Query
Sigma runs in your Dashboard, but the real power comes from exporting those results into code.
Retrieve event data with the Stripe API
Before querying with Sigma, fetch your raw event stream using the Events endpoint. This shows you exactly what data Sigma has access to.
const stripe = require('stripe')('sk_test_...');
const thirtyDaysAgo = Math.floor(Date.now() / 1000) - 30 * 24 * 60 * 60;
const events = await stripe.events.list({
type: 'charge.succeeded',
created: { gte: thirtyDaysAgo },
limit: 100
});
events.data.forEach(event => {
const charge = event.data.object;
console.log(`${charge.created}: $${(charge.amount / 100).toFixed(2)} from ${charge.customer}`);
});Open Sigma and write a SQL query
Log into your Stripe Dashboard, navigate to Reporting > Sigma (or Analytics > Sigma on newer accounts), and write a SQL query. Example: find your top 10 customers by revenue in the last 30 days.
// SQL you'd write directly in the Sigma editor:
// SELECT
// customer_id,
// COUNT(*) as charge_count,
// SUM(amount) / 100 as total_revenue
// FROM charges
// WHERE created >= datetime('now', '-30 days')
// AND status = 'succeeded'
// GROUP BY customer_id
// ORDER BY total_revenue DESC
// LIMIT 10;
// Then export as CSV from the Dashboard UI
const csv = require('csv-parser');
const fs = require('fs');
const topCustomers = [];
fs.createReadStream('sigma-export.csv')
.pipe(csv())
.on('data', (row) => {
topCustomers.push({
customerId: row.customer_id,
chargeCount: parseInt(row.charge_count),
totalRevenue: parseFloat(row.total_revenue)
});
})
.on('end', () => {
console.log('Top 10 customers:', topCustomers);
});datetime('now', '-30 days') to filter by relative dates. Refer to the Sigma editor's autocomplete to see available tables and functions.Automate Report Execution and Tracking
Manual CSV exports work for one-time analysis, but continuous tracking requires automation.
Fetch scheduled reports via the Stripe API
Stripe's Reports feature (related to Sigma) lets you schedule queries to run automatically. Use the API to retrieve report status and download results.
const stripe = require('stripe')('sk_test_...');
// List all scheduled reports
const reports = await stripe.reporting.reportRuns.list({
limit: 10
});
reports.data.forEach(report => {
console.log(`Type: ${report.report_type}, Status: ${report.status}`);
if (report.status === 'succeeded') {
console.log(`Result available: ${report.result.id}`);
}
});
// Retrieve a specific report run
const reportRun = await stripe.reporting.reportRuns.retrieve('reportrun_...');
if (reportRun.status === 'succeeded') {
const file = await stripe.files.retrieve(reportRun.result.id);
console.log(`Download at: ${file.url}`);
}Set up webhooks to trigger when reports complete
Instead of polling, listen for reporting.report_run.succeeded webhook events. Your application gets notified instantly when a report finishes, and you can process it right away.
const express = require('express');
const app = express();
const stripe = require('stripe')('sk_test_...');
app.post('/stripe-webhook', express.json({type: 'application/json'}), (req, res) => {
const sig = req.headers['stripe-signature'];
let event;
try {
event = stripe.webhooks.constructEvent(req.body, sig, 'whsec_test_...');
} catch (err) {
return res.status(400).send(`Webhook error: ${err.message}`);
}
if (event.type === 'reporting.report_run.succeeded') {
const reportRun = event.data.object;
console.log(`Report ${reportRun.id} completed`);
// Fetch the result file immediately
stripe.files.retrieve(reportRun.result.id).then(file => {
console.log(`Processing: ${file.url}`);
// Download and parse the CSV/JSON
});
}
res.json({received: true});
});
app.listen(3000);status field before trying to access report.result. A status of pending means the report is still running.Process and Store Sigma Results
Once you have the exported data, transform it into your application's format.
Transform CSV exports into structured data
Stripe exports Sigma results as CSV with snake_case column names. Parse and normalize to your database schema.
const csv = require('csv-parser');
const fs = require('fs');
const parseAndStore = async (filePath) => {
const rows = [];
fs.createReadStream(filePath)
.pipe(csv())
.on('data', (row) => {
rows.push({
customerId: row.customer_id || row.cus_xxx,
revenue: parseFloat(row.total_revenue || 0),
chargeCount: parseInt(row.charge_count || 0),
timestamp: new Date(row.period_start),
region: row.customer_country
});
})
.on('end', async () => {
// Upsert into your database or data warehouse
await db.metrics.upsertMany(rows, { uniqueKey: ['customerId', 'timestamp'] });
console.log(`Stored ${rows.length} metrics from Sigma`);
});
};
await parseAndStore('/tmp/sigma-export-2024-03.csv');Common Pitfalls
- Assuming Sigma shows real-time data—Stripe events have a small processing lag, so your Sigma queries won't include charges from the last few seconds. Build in at least a 5-minute buffer.
- Running the same Sigma query repeatedly without caching. Sigma queries consume compute resources; export once per day or per hour and cache the results in your application.
- Forgetting that Sigma is limited to your event retention window. If you've been live for 2 months, you can only query 2 months of data. Plan for this in historical comparisons.
- Exporting as CSV without normalizing column names. Stripe uses snake_case (
customer_id,total_revenue); your database might expect camelCase or different names. Always map fields during import.
Wrapping Up
Sigma SQL gives you direct access to your Stripe data without building a separate analytics pipeline. By combining event fetching, report scheduling, and webhooks, you can automate tracking on your own terms. If you want to track these metrics automatically across tools like Amplitude, PostHog, and Mixpanel without writing custom integrations, Product Analyst can help.