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. 


    Access your files securely from anywhere

        Zoho Developer Community




                                  Zoho Desk Resources

                                  • Desk Community Learning Series


                                  • Digest


                                  • Functions


                                  • Meetups


                                  • Kbase


                                  • Resources


                                  • Glossary


                                  • Desk Marketplace


                                  • MVP Corner


                                  • Word of the Day



                                      Zoho Marketing Automation
                                              • 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


                                              Manage your brands on social media



                                                    Zoho TeamInbox Resources

                                                      Zoho DataPrep Resources



                                                        Zoho CRM Plus Resources

                                                          Zoho Books Resources


                                                            Zoho Subscriptions Resources

                                                              Zoho Projects Resources


                                                                Zoho Sprints Resources


                                                                  Qntrl Resources


                                                                    Zoho Creator Resources



                                                                        Zoho Campaigns Resources


                                                                          Zoho CRM Resources

                                                                          • CRM Community Learning Series

                                                                            CRM Community Learning Series


                                                                          • Kaizen

                                                                            Kaizen

                                                                          • Functions

                                                                            Functions

                                                                          • Meetups

                                                                            Meetups

                                                                          • Kbase

                                                                            Kbase

                                                                          • Resources

                                                                            Resources

                                                                          • Digest

                                                                            Digest

                                                                          • CRM Marketplace

                                                                            CRM Marketplace

                                                                          • MVP Corner

                                                                            MVP Corner





                                                                              Design. Discuss. Deliver.

                                                                              Create visually engaging stories with Zoho Show.

                                                                              Get Started Now