如何将 Stripe 连接到 Excel - 使用 SQL 查询付款数据

SELECT
    DATE_TRUNC('month', created) AS 月份,
    COUNT(*) AS 交易次数,
    SUM(amount) / 100.0 AS 总收入,
    SUM(CASE WHEN 退款状态 = 'refunded' THEN 退款金额 ELSE 0 END) / 100.0 AS 退款金额,
    (SUM(金额) - SUM(CASE WHEN 退款状态 = '已退款' THEN 退款金额 ELSE 0 END)) / 100.0 AS 净收入
FROM stripe.charges
WHERE 状态 = '成功'
  AND 创建时间 >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', 创建时间)
ORDER BY 月份 DESC;

SELECT
    p.name AS 产品名称,
    pr.nickname AS 套餐名称,
    pr.interval AS 计费周期,
    pr.unit_amount / 100.0 AS 单价,
    COUNT(s.id) AS 活跃订阅用户数,
    COUNT(s.id) * pr.unit_amount / 100.0 AS 经常性收入贡献额
FROM stripe.subscriptions s
JOIN stripe.subscription_items si ON si.subscription = s.id
JOIN stripe.prices pr ON pr.id = si.price
JOIN stripe.products p ON p.id = pr.product
WHERE s.status = 'active'
GROUP BY p.name, pr.nickname, pr.interval, pr.unit_amount
ORDER BY mrr_contribution DESC;

SELECT
    DATE(created) AS date,
    failure_code,
    failure_message,
    COUNT(*) AS failure_count,
    SUM(amount) / 100.0 AS lost_revenue
FROM stripe.charges
WHERE status = 'failed'
  AND created >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created), failure_code, failure_message
ORDER BY date DESC, failure_count DESC;