Gross revenue represents the total income generated by your organization in a single month, giving you a complete overview of your financial performance.
We ensure a comprehensive approach, encompassing all types of discounts, such as credit notes, coupons, and credits, to provide you with a precise picture of your gross revenue.
In essence, it’s the amount you should expect to receive every month.
Our gross revenue calculation considers the following factors:
- All
finalized invoices, whether related to subscriptions or usage;
- Pay in advance fees, whether they are invoiceable or not;
- Invoices related to prepaid credits from wallets;
- One-off invoices; and
- Deducting the credit note amounts
refunded to customers.
To access the Gross Revenue analytics dashboard:
- Navigate to the “Analytics” section; and
- Access the “Gross Revenue” Dashboard.
To access the Gross Revenue analytics dashboard:
- Navigate to the “Analytics” section; and
- Access the “Gross Revenue” Dashboard.
LAGO_URL="https://api.getlago.com"
API_KEY="__YOUR_API_KEY__"
curl --location --request GET "$LAGO_URL/api/v1/analytics/gross_revenue?external_customer_id=hooli_1234¤cy=USD" \
--header "Authorization: Bearer $API_KEY" \
--header 'Content-Type: application/json' \
The generated amounts are in cents, making it compatible with different currencies.
--- Start date of the query is the organization creation date
WITH organization_creation_date AS (
SELECT
DATE_TRUNC('month', o.created_at) AS start_month
FROM organizations o
WHERE o.id = '__YOUR_ORGANIZATION_ID__'
),
--- Generate a number of date series in the future
all_months AS (
SELECT
generate_series(
(SELECT start_month FROM organization_creation_date),
DATE_TRUNC('month', CURRENT_DATE + INTERVAL '10 years'),
interval '1 month'
) AS month
),
--- Get value for all issued invoices
issued_invoices AS (
SELECT
i.id,
i.issuing_date,
i.total_amount_cents::float AS amount_cents,
i.currency,
COALESCE(SUM(refund_amount_cents::float),0) AS total_refund_amount_cents
FROM invoices i
LEFT JOIN customers c ON i.customer_id = c.id
LEFT JOIN credit_notes cn ON cn.invoice_id = i.id
WHERE i.organization_id = '__YOUR_ORGANIZATION_ID__'
AND i.status = 1
---AND c.external_id = 'hooli_1234' --- FILTER BY CUSTOMER
GROUP BY i.id, i.issuing_date, i.total_amount_cents, i.currency
ORDER BY i.issuing_date ASC
),
--- Get value for all instant charges (paid in advance but not invoiceable)
instant_charges AS (
SELECT
f.id,
f.created_at AS issuing_date,
f.amount_cents AS amount_cents,
f.amount_currency AS currency,
0 AS total_refund_amount_cents
FROM fees f
LEFT JOIN subscriptions s ON f.subscription_id = s.id
LEFT JOIN customers c ON c.id = s.customer_id
WHERE c.organization_id = '__YOUR_ORGANIZATION_ID__'
AND f.invoice_id IS NULL
AND f.pay_in_advance IS TRUE
---AND c.external_id = 'hooli_1234' --- FILTER BY CUSTOMER
),
--- Combine data to get total of gross revenue
combined_data AS (
SELECT
DATE_TRUNC('month', issuing_date) AS month,
currency,
COALESCE(SUM(amount_cents), 0) AS amount_cents,
COALESCE(SUM(total_refund_amount_cents), 0) AS total_refund_amount_cents
FROM (
SELECT * FROM issued_invoices
UNION ALL
SELECT * FROM instant_charges
) AS gross_revenue
GROUP BY month, currency, total_refund_amount_cents
)
--- Get gross revenue month over month
SELECT
am.month,
cd.currency,
SUM(cd.amount_cents - cd.total_refund_amount_cents) AS amount_cents
FROM all_months am
LEFT JOIN combined_data cd ON am.month = cd.month
WHERE am.month <= DATE_TRUNC('month', CURRENT_DATE)
---AND am.month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11 months') --- LAST 12 MONTHS
AND cd.amount_cents IS NOT NULL
--- AND cd.currency = 'EUR'
GROUP BY am.month, cd.currency
ORDER BY am.month
