> ## Documentation Index
> Fetch the complete documentation index at: https://lago-ftr-wallet-improvements.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Monthly Recurring Revenue (MRR)

<Info>
  **PREMIUM FEATURE** ✨

  This dashboard is only available to users with a premium license. Please
  **[contact us](mailto:hello@getlago.com)** to get access to Lago Cloud and Lago
  Self-Hosted Premium.
</Info>

Monthly Recurring Revenue (MRR) serves as a crucial indicator of your subscription-based revenue stream.
It encapsulates the total monthly revenue derived from subscriptions that is recurring. It's important to highlight that our MRR calculation
method takes into account all types of coupons and taxes. We take a holistic approach to provide you with an accurate representation
of your recurring revenue.

It's worth noting that Lago's MRR calculations solely pertain to `subscription` fees. Usage-based metrics,
whether recurring or metered, are not factored into the MRR calculations. This distinction ensures that your MRR figures
specifically reflect your subscription-based revenue, helping you gain a clear understanding of your core financial
performance.

**Here's our MRR calculation method:**

* Revenue from `monthly` subscription invoices is included in the MRR for the month in which the invoice is issued.
* Revenue from `quarterly` subscription invoices is distributed evenly over three months. This distribution applies to fees paid in advance (allocated to the next remaining months depending on calendar or anniversary billing) as well as to fees paid in arrears (allocated to the preceding months depending on calendar or anniversary billing).
* Revenue from `yearly` subscription invoices is distributed evenly over twelve months. This allocation is applicable for fees paid in advance (spread over the next  remaining months depending on calendar or anniversary billing) and for fees paid in arrears (spread over the previous months depending on calendar or anniversary billing).
* Revenue from `weekly` subscription invoices, the total revenue from all invoices issued within a month is summed up. This total is then divided by the number of invoices issued during that month, and the result is multiplied by 4.33, representing the average number of weeks in a month.

<Info>
  Please note that the UI's MRR total summary displays the MRR from the most recently **completed** billing month.
</Info>

<Tabs>
  <Tab title="Dashboard">
    To access the MRR analytics dashboard:

    1. Navigate to the "Analytics" section; and
    2. Access the "Monthly Recurring Revenue" Dashboard.

    <Frame caption="MRR dashboard">
      <img src="https://mintlify.s3-us-west-1.amazonaws.com/lago-ftr-wallet-improvements/guide/analytics/images/mrr.png" />
    </Frame>
  </Tab>

  <Tab title="API">
    <CodeGroup>
      ```bash Get MRR
      LAGO_URL="https://api.getlago.com"
      API_KEY="__YOUR_API_KEY__"

      curl --location --request GET "$LAGO_URL/api/v1/analytics/mrr?currency=USD" \
      --header "Authorization: Bearer $API_KEY" \
      --header 'Content-Type: application/json' \
      ```
    </CodeGroup>
  </Tab>

  <Tab title="SQL Query">
    <Info>
      The generated amounts are in cents, making it compatible with different currencies.
    </Info>

    <CodeGroup>
      ```sql SQL Query used by Lago
        --- Organization creation date is the start of this report
          WITH organization_creation_date AS (
              SELECT
                  DATE_TRUNC('month', o.created_at) AS month
              FROM organizations o
              WHERE o.id = '__YOUR_ORGANIZATION_ID__'
          ),

          --- Generate series of month
          all_months AS (
              SELECT
                  *
              FROM generate_series(
                  (SELECT min(month) FROM organization_creation_date),
                  date_trunc('month', now()) + interval '10 years',
                  interval '1 month'
              ) AS month
          ),

          --- Get invoice details for recurring subscriptions with finalized invoices and number of months billed
          invoice_details AS (
              SELECT
                  f.subscription_id,
                  f.invoice_id,
                  c.name,
                  ((f.amount_cents - f.precise_coupons_amount_cents) + f.taxes_amount_cents) AS amount_cents,
                  f.amount_currency AS currency,
                  i.issuing_date,
                  (EXTRACT(DAY FROM CAST(properties ->> 'to_datetime' AS timestamp) - CAST(properties ->> 'from_datetime' AS timestamp)) 
                  + EXTRACT(HOUR FROM CAST(properties ->> 'to_datetime' AS timestamp) - CAST(properties ->> 'from_datetime' AS timestamp)) / 24 
                  + EXTRACT(MINUTE FROM CAST(properties ->> 'to_datetime' AS timestamp) - CAST(properties ->> 'from_datetime' AS timestamp)) / 1440) / 30.44 AS billed_months,
                  p.pay_in_advance,
                  CASE
                      WHEN p.interval = 0 THEN 'weekly'
                      WHEN p.interval = 1 THEN 'monthly'
                      WHEN p.interval = 2 THEN 'yearly'
                      WHEN p.interval = 3 THEN 'quarterly'
                  END AS plan_interval
              FROM fees f
              LEFT JOIN invoices i ON f.invoice_id = i.id
              LEFT JOIN customers c ON c.id = i.customer_id
              LEFT JOIN organizations o ON o.id = c.organization_id
              LEFT JOIN subscriptions s ON f.subscription_id = s.id
              LEFT JOIN plans p ON p.id = s.plan_id
              WHERE fee_type = 2
                  AND c.organization_id = '__YOUR_ORGANIZATION_ID__'
                  AND i.status = 1
              ORDER BY issuing_date ASC
          )

          --- Distribute quarterly revenue prorated for the next months
          ,quarterly_advance AS (
              SELECT
                  DATE_TRUNC('month', issuing_date) + INTERVAL '1 month' * gs.month_index AS month,
                  CASE 
                      WHEN gs.month_index = 0 THEN (amount_cents / billed_months) * (DATE_PART('day', DATE_TRUNC('month', issuing_date + INTERVAL '1 month') - issuing_date) / DATE_PART('day', DATE_TRUNC('month', issuing_date + INTERVAL '1 month') - DATE_TRUNC('month', issuing_date)))
                      WHEN gs.month_index = CEIL(billed_months) - 1 THEN (amount_cents - (amount_cents / billed_months) * (FLOOR(billed_months) - 1 + (DATE_PART('day', DATE_TRUNC('month', issuing_date + INTERVAL '1 month') - issuing_date) / DATE_PART('day', DATE_TRUNC('month', issuing_date + INTERVAL '1 month') - DATE_TRUNC('month', issuing_date)))))
                      ELSE amount_cents / billed_months
                  END AS amount_cents,
                  currency,
                  name
              FROM invoice_details,
              LATERAL GENERATE_SERIES(0, CEIL(billed_months) - 1) AS gs(month_index)
              WHERE pay_in_advance = TRUE
                  AND plan_interval = 'quarterly'
          )

          --- Distribute quarterly revenue prorated for the past months
          ,quarterly_arrears AS (
              SELECT
                  DATE_TRUNC('month', issuing_date) - INTERVAL '1 month' * gs.month_index AS month,
                  CASE 
                      WHEN gs.month_index < CEIL(billed_months::numeric) - 1 THEN 
                          amount_cents::numeric / billed_months::numeric
                      ELSE 
                          amount_cents::numeric - (amount_cents::numeric / billed_months::numeric) * (CEIL(billed_months::numeric) - 1)
                  END AS amount_cents,
                  currency,
                  name
              FROM invoice_details,
              LATERAL GENERATE_SERIES(0, CEIL(billed_months::numeric) - 1) AS gs(month_index)
              WHERE pay_in_advance = FALSE
                  AND plan_interval = 'quarterly'
          )

          --- Distribute yearly revenue prorated for the next months
          ,yearly_advance AS (
              SELECT
                  DATE_TRUNC('month', issuing_date) + INTERVAL '1 month' * gs.month_index AS month,
                  CASE 
                      WHEN gs.month_index = 0 THEN (amount_cents / billed_months) * (DATE_PART('day', DATE_TRUNC('month', issuing_date + INTERVAL '1 month') - issuing_date) / DATE_PART('day', DATE_TRUNC('month', issuing_date + INTERVAL '1 month') - DATE_TRUNC('month', issuing_date)))
                      WHEN gs.month_index = CEIL(billed_months) - 1 THEN (amount_cents - (amount_cents / billed_months) * (FLOOR(billed_months) - 1 + (DATE_PART('day', DATE_TRUNC('month', issuing_date + INTERVAL '1 month') - issuing_date) / DATE_PART('day', DATE_TRUNC('month', issuing_date + INTERVAL '1 month') - DATE_TRUNC('month', issuing_date)))))
                      ELSE amount_cents / billed_months
                  END AS amount_cents,
                  currency,
                  name
              FROM invoice_details,
              LATERAL GENERATE_SERIES(0, CEIL(billed_months) - 1) AS gs(month_index)
              WHERE pay_in_advance = TRUE
                  AND plan_interval = 'yearly'
          )

          --- Distribute yearly revenue prorated for the past months
          ,yearly_arrears AS (
              SELECT
                  DATE_TRUNC('month', issuing_date) - INTERVAL '1 month' * gs.month_index AS month,
                  CASE 
                      WHEN gs.month_index < CEIL(billed_months::numeric) - 1 THEN 
                          amount_cents::numeric / billed_months::numeric
                      ELSE 
                          amount_cents::numeric - (amount_cents::numeric / billed_months::numeric) * (CEIL(billed_months::numeric) - 1)
                  END AS amount_cents,
                  currency,
                  name
              FROM invoice_details,
              LATERAL GENERATE_SERIES(0, CEIL(billed_months::numeric) - 1) AS gs(month_index)
              WHERE pay_in_advance = FALSE
                  AND plan_interval = 'yearly'
          )

          --- Distribute monthly revenue for the current month of issuing
          ,monthly AS (
              SELECT
                  DATE_TRUNC('month', issuing_date) - interval '1 month' * generate_series(0, 0, -1) AS month,
                  amount_cents,
                  currency
              FROM invoice_details
              WHERE plan_interval = 'monthly'
          )

          --- Distribute weekly revenue by dividing number of invoices in a month by 4.33 (average number of weeks in a month)
          ,weekly AS (
              SELECT
                  DATE_TRUNC('month', issuing_date) - interval '1 month' * generate_series(0, 0, -1) AS month,
                  currency,
                  (SUM(amount_cents) / COUNT(*)) * 4.33 AS amount_cents
              FROM invoice_details
              WHERE plan_interval = 'weekly'
              GROUP BY month, currency
          )

          ,consolidated_mrr AS (
              SELECT month, amount_cents::numeric, currency
              FROM quarterly_arrears
              UNION ALL
              SELECT month, amount_cents::numeric, currency
              FROM quarterly_advance
              UNION ALL
              SELECT month, amount_cents::numeric, currency
              FROM yearly_arrears
              UNION ALL
              SELECT month, amount_cents::numeric, currency
              FROM yearly_advance
              UNION ALL
              SELECT month, amount_cents::numeric, currency
              FROM monthly
              UNION ALL
              SELECT month, amount_cents::numeric, currency
              FROM weekly
          )

          SELECT
              am.month,
              cm.currency,
              COALESCE(SUM(cm.amount_cents),0) AS amount_cents
          FROM all_months am
          LEFT JOIN consolidated_mrr cm ON cm.month = am.month
          WHERE am.month <= DATE_TRUNC('month', CURRENT_DATE)
              AND am.month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11 months') --- LAST 12 MONTHS
              ---AND cm.currency = ''
          GROUP BY am.month, cm.currency
          ORDER BY am.month ASC
      ```
    </CodeGroup>
  </Tab>
</Tabs>