One of the privileges of working at Ellipsis is we get to spot common trends and issues across the industry, and we have the scale to fix most of them.

One of these got my attention at the start of 2021: most WordPress businesses have a really difficult time getting accurate financial data. It’s common for businesses doing really significant revenue to struggle to get an accurate handle on how their revenue numbers break down.

We thus set out to fix this. I hired a Google Sheets expert to build us a template sheet that calculates:

  • Revenue year on year
  • New vs recurring revenue
  • Renewal rate
  • Breakdown of revenue across price tiers and products

The sheet works with any raw export of sales. You input your sales, and it’ll go to work doing your calculations. It then visualises the data for you.

The sheet ended up costing over $2k. We use it to help inform decisions in a lot of our strategy work. Today, we’re releasing a copy of the sheet free for anyone to use.

Ellipsis’ role is to lead progress in marketing + WordPress. We could happily keep this to ourselves, but the WordPress industry is missing out on so much insight here — it seems best to release it.

You can make a copy of the sheet here. You’re welcome to make a private copy, so no need to share any data.

What will the sheet tell me?

You’ll need to import your EDD sales data, and then wait for the Sheet to crunch the numbers. You’ll then see key metrics show up:

  1. Key stats broken down year on year, including your renewal rate
  2. Your mix of new sales and recurring revenue over time
  3. How different products contribute to sales and revenue

And more! We’ve built it around the most useful insights WordPress product businesses. Here’s some sample data visualised:

New sales (red) and renewals (blue) over time

Importing your data will obviously give you must more insight, and many more charts!

How do I set up the financial model?

You should expect to spend about half an hour setting this up. The sheet pushes the limits of what Google Sheets can do, and it requires a little patience.

You’ll first need an export of your sales, going back to your first sale. You’ll need 4 data points for each:

  • Customer email
  • Product name
  • Price (excluding tax)
  • Date

In EDD, the standard sales export is best. You want both new sales and renewals: either export “Complete” and “Renewals” individually and combine them, or export everything and delete refunds, failed transactions, and so on.

Once you’ve got the raw data, you’ll need to clean it up. There’s a separate data preparation sheet here you can use. Make a copy of the relevant sheet, and then copy the relevant columns in from your data export.

The product name should be a “nice” name, ie WordPress Product Plugin (Tier) rather than WordPress Product Plugin (Tier) – ID3 $200.97. This will let the sheet identify products as the same thing, even if you’ve changed the price.

We then need the date in US MM_DD_YYYY format. You should have that automatically, but paste a row into the date tester sheet to check.

If you sell in multiple currencies, you need everything in one currency. EDD lets you get just the “base” currency; that works.

You then want to scan through the data and look for any alerts. The sheet tries to make these obvious: any blank product names, emails, or prices will mess with the calculations, so it’s best to check these now. You want to either add in the missing info for these, or delete them.

Check everything looks good. When you’ve prepared your data, you’re ready to paste it into the Raw Data tab. Paste each column, one at a time. The sheet will then start crunching numbers. If you have more than 10k rows, this will take time. The progress bar at the top right will give you an indication. Be prepared to leave it for five minutes.

When done, click the Analysis tab and you’ll get your data!

What does the data mean?

Your data is nothing without insight! A lot of the data will be obvious, but we can highlight common areas of interest:

  • You’ll either find your renewal revenue is vital to the business – or needs to be higher. Anything below 50% needs room for improvement. Above 50%, and you may want to work on ensuring the ongoing value is obvious. We can help with both of those.
  • Higher priced tiers can make an outsized contribution to revenue. Your highest price tier will often make an outsized contribution to revenue. Day to day it can be easy to miss this when each sale shows up as the same Slack notification.
  • You can get a rough idea of how much your business is worth. The breakdown of sales and renewals (and their growth) over time is extremely useful here. This will give you insight into where in the 2.5-5x valuation range you sit.

Here’s our Senior Marketing Strategist, James Baldacchino, on what he sees in the sheet for our clients:

Ellipsis’ financial model sheet is invaluable for getting insight into the performance of any WordPress business. It’s one of the tools we use to base our recommendations on for clients, allowing us to take decisions based on facts gathered from the full picture the sheet presents.

If you need help understanding how you should action your data, we can happily help. Either get in touch here, or have a look at our Marketing Audit and Strategy service here.

Can I get Ellipsis’ help with the sheet, or growing my revenue?

Sure! We’re saving the more powerful version of the sheet, which can also project revenue growth, for Ellipsis clients. We can combine this with done-for-you setup and analysis of your data. If you’d like help, get in touch here.

For many WordPress businesses, the sheet will tell you: you need more revenue growth. If that’s the case for you, our Marketing Audit and Strategy service is an excellent starting point — or get in touch for a chat about how we can help.

FAQs

How is the renewal rate calculated?

The sheet takes your sales data, and then generates a unique ID for each new sale. It does this by combining the email address and product name (which is why you need “nice” product names). It then looks to see if the unique ID has appeared before. If it can’t find it, it marks it as a new sale. If it can, it’s a renewal.

The renewal rate is calculated by looking at those new sale and renewal markers. It’s an eloquently simple system that works really nicely!

Will this work with Freemius, Fastspring, or other platforms?

We’ve built the sheet for EDD, but as long as you get your data into the right format it should be fine.

How do I troubleshoot errors?

The most likely answer to any errors is you’ve missed a blank product name, email, or price. Do a CTRL+F for ERROR on the raw data tab, and fix or delete any missing rows.

Otherwise, the calculation sheet is doing the heavy lifting, so if you see any errors use that to troubleshoot. You can isolate errors to individual months by scrolling across and looking for errors. Once you’ve found the error, look for the corresponding raw sales. There’s usually a stray error there causing the issue.

Which currencies are supported?

You can use any currency. The sheet is built for USD. You just need your data in the same currency. If you want a different currency, just change the $ in the sheet as needed.

I have a complex product setup. Will this work?

The sheet is assuming that each new sale in your data is a new sale. That’s true for most WordPress businesses. If you have a core + addons model where addons are “purchased” at $0, it’s going to think the $0 sales are all new ones. This will mess with your renewal and AOV numbers. It’s likely best to delete those $0 rows.

Is this really free?

Yes! We spent a lot of money building this, but we felt helping the WordPress economy get better data is a public service worth making. It’s also not so bad for Ellipsis to be associated with the cutting-edge in WordPress marketing.

How can I use the sheet?

We’re releasing the financial model as-is, with no warranty. Use it at your own risk! You’re welcome to use it internally, but you may not distribute the sheet.

"Damn, Alex. That was a powerhouse newsletter. Great work!"

Be as happy as David. Get Press Marketing, our hand-curated monthly newsletter with the very finest in marketing and growth tactics for WordPress businesses.

About Alex Denning

Hey, I'm Alex! I'm the Founder of Ellipsis, and I co-curate the really good weekly newsletter MasterWP. I'm @AlexDenning đź‘Ź

Leave a Comment