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.
Writer is a powerful online word processor, designed for collaborative work.