Paddle SQL reporting: start with the right numbers
Build useful Paddle reports for collections and refunds, with practical SQL and clear rules for dates, currencies and missing data.
Choose a question before choosing a chart
A useful Paddle report starts with a precise question: what did customers pay, which invoices need attention, or how much recurring value do active subscriptions represent? Those questions use different records. Putting all three under a heading called “revenue” makes a dashboard easier to build and harder to trust.
This guide uses PayQuery’s PostgreSQL schema, which stores imported Paddle Billing data in queryable tables. The examples run against that copy; they are not SQL endpoints provided by Paddle. You can inspect the SQL, save a report, and choose a visualization after checking the result.
For a first report, use gross collections by day. It answers a concrete operational question and gives you a small result set to compare with individual transactions. Keep the provider, currency, date basis and refund treatment visible in the report name or notes.
Understand what a row represents
In Paddle, a transaction connects the customer, items, totals and payment attempts. One transaction can contain several attempts, so a transaction count is not a count of every card retry. Paddle explains this distinction in its transaction documentation.
PayQuery’s transactions table gives you one normalized transaction record. The invoices table also represents billed Paddle transactions. Joining these two tables and adding both totals would count the same underlying business activity twice. Start with the table that matches the question.
Refunds need their own treatment. Paddle records refunds and credits as adjustments alongside the original transaction. PayQuery puts adjustments with a refund action in refunds; a credit is not automatically a cash refund. A refund request can also be awaiting approval. Paddle’s adjustment guide describes those states.
Report the last 30 complete UTC days
The query below includes successful collections even if they were later refunded. That is deliberate: the result measures gross collections before refunds. It uses the captured payment time when available, with transaction creation as a disclosed fallback, and excludes today’s partial day.
WITH bounds AS (
SELECT date_trunc('day', now() AT TIME ZONE 'UTC')
AT TIME ZONE 'UTC' AS through
)
SELECT
(COALESCE(t.paid_at, t.created_at) AT TIME ZONE 'UTC')::date AS day,
t.currency,
COUNT(*) AS transactions,
COUNT(*) FILTER (WHERE t.paid_at IS NULL) AS date_fallbacks,
COUNT(*) FILTER (WHERE t.amount_minor IS NULL) AS missing_amounts,
CASE
WHEN t.currency IS NULL OR COUNT(t.amount_minor) <> COUNT(*) THEN NULL
ELSE SUM(t.amount_minor)
END AS gross_collections_minor
FROM transactions t
CROSS JOIN bounds b
WHERE t.provider = 'paddle'
AND t.status IN ('succeeded', 'partially_refunded', 'refunded')
AND COALESCE(t.paid_at, t.created_at) >= b.through - INTERVAL '30 days'
AND COALESCE(t.paid_at, t.created_at) < b.through
GROUP BY 1, 2
ORDER BY day, currency;Amounts remain in the provider’s minor units. Keep different currencies in separate series; a total containing both USD and EUR is not a monetary total. Avoid applying a universal division by 100. Also preserve a null amount: missing money is different from zero money.
The date_fallbacks column tells you how often the report used a less precise date. It matters when reconciling a particular day. An absent day can mean no matching records, but an incomplete import can produce the same appearance. Check connection progress and source freshness before interpreting gaps.
Keep refund requests separate from completed refunds
A useful companion report groups recent refund requests by their current status. This exposes pending work without quietly including it in completed-refund totals.
SELECT
status,
currency,
COUNT(*) AS refund_requests,
CASE
WHEN currency IS NULL OR COUNT(amount_minor) <> COUNT(*) THEN NULL
ELSE SUM(amount_minor)
END AS requested_amount_minor
FROM refunds
WHERE provider = 'paddle'
AND created_at >= now() - INTERVAL '30 days'
AND created_at < now()
GROUP BY status, currency
ORDER BY status, currency;For Paddle, approved is the status PayQuery uses for completed refunds. This query selects requests by creation time and shows their latest imported status. It does not tell you exactly when money returned to a bank account, or prove that approval happened within the same date window.
A “refunds this month” report therefore needs a definition. Requests created this month, currently approved requests, and refunds against this month’s original sales answer different questions. Pick one and name it clearly.
Turn the result into a working report
Check several rows against Paddle before saving. If a headline total differs, compare status filters, timezone, tax and refund treatment first. Paddle’s own revenue chart excludes tax and fees and does not subtract balance adjustments; a gross customer-collection query is not the same metric. See Paddle’s metric definitions.
Use a line or column chart for daily collections in one currency, and a table for refund states or individual invoices. Save descriptive titles such as “Paddle gross collections · daily UTC” rather than “Revenue.” Add the saved visualization to a dashboard if you want to revisit it; dashboard queries refresh on load and can be refreshed manually.
When moving on to MRR, use PayQuery’s configured MRR templates and review your workspace’s discount and subscriber-start settings. Collections measure payments; MRR measures recurring subscription value. One is not a shortcut for the other.
Explore a collections report with fictional Paddle data, or see plans for connecting your own account.