Need better visualizations to analyze and report top KPIs? Use the KPI Charts spreadsheet template to input up to a million rows of raw data. Then filter a subset to view and the board automatically generate premade charts to visualize the data for reports and analysis. This could be sales data, bug or issues data, a project to-do list, features to implement, or even stock market analysis. Rename and customize up to two date ranges, two numeric values, and five drop-down values to filter and compare against each other. Then create beautiful KPI charts for analysis and reports.
Ask follow up
To provide an example of how you could use this KPI Charts spreadsheet, we'll first explain the formula Spotify and similar companies use to calculate the return on ad spend (ROAS) for a subscription service. Then, we'll show how you could use a key performance indicator template like ours to visualize your top KPIs like ROAS or customer lifetime value (LTV) and calculate which ad platform gave a higher ROAS over time. But this is just one example of how you could use the KPI Charts spreadsheet model. If you want to learn more about how the KPI Charts spreadsheet template works, scroll below to the explainer section.
Ask follow up
As of January 2022, Spotify controlled 31% of the subscription music streaming market, with double the share of Apple Music. This dominance comes with large customer acquisition costs. As a growth stock, Spotify is assessed by new subscriber growth. But for a subscription company with a freemium business model, each new subscriber takes an upfront cost to acquire, while the revenue brought in comes over a longer time horizon.
Ask follow up
To calculate return on ad spend, companies need to know how much they spent to acquire each customer, and the customer's lifetime value, which is how much the customer will spend on average over their lifetime as a customer. So Spotify has to calculate a customer's lifetime value a little differently. Here's how the tech strategist Anuj Shah broke it down: the LTV of a Spotify subscriber depends on the average revenue per user, or how much of its subscription fee Spotify charges on average per subscriber. This is subtracted by the cost of revenue, or how much Spotify spends on average per subscriber.
Ask follow up
These numbers are divided by the churn rate. Back in 2019, Spotify's average revenue per user was $5.4 dollars, while the cost of revenue per user according to Shah's calculations came out to $3.9 dollars. Divided by an average 4.5% monthly churn rate, this came out to an average LTV of $31.1 dollars. Now we have to determine Spotify's subscriber acquisition costs. To determine the true cost per subscriber acquisition, the cost to support the free product also needs to be considered.
Ask follow up
Shah calculated Spotify's total sales and marketing cost per new gross subscriber and the cost of revenue to support free users divided by the total free users to find Spotify's CAC: $16.2 dollars per user. According to Shah's calculations, Spotify made $14.9 dollars from every subscriber in 2019, which made Spotify's LTV to CAC ratio approximately 2.2. For reference, 3 and above is what you want. Back then Spotify had 116 million free users and 94 million paid users. As of Q1 2022, Spotify has basically doubled those numbers.
Ask follow up
But in 2021, the company spent over a billion on sales and marketing, largely due to advertising costs for its marketing campaigns increasing. According to its Q1 filings, the company's already spent nearly $300 million on sales and marketing to gain 16 million new free users in 2022.
So what if Spotify wanted to cut back on its ad spend in Q2 of 2022, and optimize its acquisition efforts? It could figure out how many of those new users came from which ad platform and optimize accordingly.
Below, we show you how to do exactly that with the fully customizable KPI template we created. We'll use it to compare our customer acquisition data to determine which ad platform gave us the highest return on ad spend.
So the first thing is to update the Inputs tab to match your report. As with any You Exec spreadsheet, the text in blue is a user input for you to change. Since we'll assess our ad spend, we'll change the inputs to track when a user joined as a free member and the date they paid as a paid subscriber.
Ask follow up
Then we'll update our numerics to track how much the user spent as a subscriber and how much it cost to acquire them. And last we'll update our dropdown fields to cover our most important metrics about the user.
In this case, the metric we care about the most is the ad platform. Don't forget to check off each box as it's edited to validate to the rest of the team that it's been updated to match your report specifications.
Next up, enter the dropdown values to be assessed. For example, the five dropdown fields to be assessed could be location data from all the regions we acquired subscribers, the type of ad that captured them, the platform they were acquired from, the device they use, and the gender of the user.
Once these values are updated, the filter function and charts will know which values to search for. If a value is not defined here, it won't appear in either the Filter or Dashboard tabs.
After the dropdown values are defined, enter the data in the Raw Data tab. To determine the return on ad spend of a particular platform, these two numbers are most important: the "Total Spent" on your product or service by the customer, or their lifetime value; and the "Acquisition Cost" that was spent by you to acquire that user. The goal is to compare each user's acquisition cost versus their lifetime value across each platform.
Ask follow up
To accomplish this, go to the Filter tab, where the blue fields can be edited to define the search query. Edit the date fields to match the period of time you want to analyze, then set the numeric fields to match your ideal range. For example, if you want to set a minimum LTV of the ideal customer, you could filter the range to only show customers that have spent over $40. But the real filter to set is the acquisition costs, which should be capped at your breakeven point. The dropdown filters can also be edited and updated with the dropdown menus below, but aren't applied until the box next to each field is checked. Below the filters, all the data that matches the filter query is shown.
Ask follow up
What we really want is to visualize our filter data so it's easy to analyze and report. On the Dashboard tab, the dashboard provides a high-level overview of the data in the search filter, separated by graphs dedicated to the "Date1" field, "Date2" field, dropdown fields, and both numeric fields. These graphs reveal the customer acquisition costs and total lifetime value per platform to analyze which platform provided the highest value at the lowest cost of acquisition.
Ask follow up
To further refine your search, just go back to the Filter tab, edit the values you want to change, then recheck the dashboard and the dashboards will updated accordingly. Once you've defined your ideal return on ad spend, you can analyze which platforms gave you the closest to your goal to prioritize your efforts where they are best rewarded.
Ask follow up
Remember - the KPI Charts model is meant to be an abstract KPI template spreadsheet that can be updated to match any inputs and dropdown values you define with a broad dashboard of charts. Sales data, issue tracker data, stock price data - you name it.
For more tools like this to help improve and report on your customer acquisition efforts, download the Customer Acquisition Toolbox presentation template. For more specific analysis, check out the Marketing Dashboard spreadsheet, which provides a similar generic and fully-customizable dashboard that defines "budget versus actual" and "actual spent versus revenues." And for a similar spreadsheet tool specific to sales data, you can download our other KPI Sales Dashboard to streamline KPI reports for sales teams and track your top sales metrics all in one place.
Ask follow up
