Report of the Week - Expected Revenue based on Subscription Data

Report of the Week - Expected Revenue based on Subscription Data

This 'Report of the Week' tip is for those of you who run a (recurring) subscription based business. Zoho Analytics can help you calculate your revenue for upcoming months, based on the current subscriptions. 



We are using Advanced Analytics for Zoho Finance workspace as an example to show how to create this report. You can create this report if you are using Xero and Stripe as well.  


Follow the below steps to do this calculation. 


Step1: Create A Query Table to calculate Amount per Month


You can calculate the per month revenue based on your current active subscriptions using the below sample query. 



/* fetches the customer details and the recurring invoice details from the corresponding tables */

SELECT



"Contacts"."Customer Name" "Customer Name",

"Recurring Invoice"."Start Date" "Start Date",

"Recurring Invoice"."Next Invoice Date" "Next Invoice Date",

"Recurring Invoice"."Total (BCY)" "Total (BCY)",  /* BCY (Basic currency) is a column with all subscribed amount */

"Recurring Invoice"."Recurrence Frequency" "Recurrence Frequency",

"Recurring Invoice"."End Date" "End Date", 

 

/* converts the  invoices with months/years recurrence frequency to Revenue  Per Month */

 

"Recurring Invoice"."When" "When",

if ( "Recurring Invoice"."Recurrence Frequency" = 'Months', "Recurring Invoice"."Total (BCY)" / "Recurring Invoice"."When", "Recurring Invoice"."Total (BCY)" / ("Recurring Invoice"."When" *  12 )) as "Amount Per Month"

FROM "Recurring Invoice"

 


FROM 

/* Fetches the active subscription and joins it with customer details */


"Recurring Invoice"

LEFT JOIN "Contacts" ON "Recurring Invoice"."Customer ID" = "Contacts"."Customer ID"

WHERE "Recurring Invoice"."Status" = 'Active'




Step 2: Create a Query Table to Polulate the Future Month Subscription 


Now you can populate the 'Amount Per Month' calculation for future months. The below sample query will calculate the expected amount of money for the next 6 months. If a customer's subscription period ends in-between, then the amount will be excluded for the subsequent months. 


SELECT  

addmonth( current_date () , "Number_of_Months"."Month Number" ) "Date",

"Subscribed Amount per Month"."Customer Name" "Customer Name",

"Subscribed Amount per Month"."Start Date" "Start Date",

"Subscribed Amount per Month"."Amount Per Month" "Amount Per Month",

"Subscribed Amount per Month"."End Date" "End Date"

 

FROM ( SELECT 0 "Month Number"

UNION

SELECT 1 "Month Number"

UNION

SELECT 2 "Month Number"

UNION

SELECT 3 "Month Number"

UNION

SELECT 4 "Month Number"

UNION

SELECT 5 "Month Number"


) AS "Number_of_Months"

 

CROSS JOIN "Subscribed Amount per Month" 

WHERE ("Subscribed Amount per Month"."End Date"   is null

  OR "Subscribed Amount per Month"."End Date"   >= addmonth( current_date () , "Number_of_Months"."Month Number" ))

  AND start_day( month, "Subscribed Amount per Month"."Start Date")   <= addmonth( current_date(), "Number_of_Months"."Month Number" )




Step 4: Create the Subscribed Amount Reports 


Now you can create reports over the Future Month Subscription by adding Date and Amount Per Month column. 




      • Sticky Posts

      • Announcing Zoho Analytics 6.0 Beta!

        We are delighted to open up the next major version, Zoho Analytics 6.0 Beta! The new version comes packed with a wide range of functionalities for all persona, namely business users, data analysts, data engineers, and data scientists. Zoho Analytics team

      • What's New in Zoho Analytics - August 2024

        Hello Users! We are back with the latest updates and enhancements made to Zoho Analytics. Keep reading to learn more about them. Connect to the data hosted in the cloud without allow-listing the IP addresses Utilize Zoho Databridge to connect to the data

      • We are coming to your city! Zoho Analytics Community Meetup

        Hello, business leaders and data enthusiasts! We are delighted to announce that registrations are now open for the ZUG meetups, and we can't wait for you to be a part of them. Our in-house analytics experts are geared up to lead discussions on constructing

      • Zoho Analytics: 2021 Look Back

        As we start a new year in 2022, here's some of our top moments from 2021. Zoho Analytics in 2021

      • [Customer Talk] PREMO Group's Analyst Interview at Zoho Day 2022

        Premo Group, a 50 year old Spanish Manufacturing Company, has been our long-standing customer with #ZohoAnalytics. They've been using our platform for their end-to-end, unified business analytics solution.  Hear more from Claudio Cabeza, Director at PREMO