Panoptez is now Pez.AI

Panoptez is now Pez.AI. Why the change? We’ve added a conversational AI interface on top of our data analysis platform to improve the experience for non-technical people. That means you can talk to Pez.AI in Slack like you would talk to a business analyst or data scientist. Based on the conversation, Pez.AI identifies what you want and executes code to get your answers. Under the hood, the same Pez language you’ve grown to love powers the analysis and platform, giving you the best of both worlds.

Our first conversational application targets Google Analytics. You’ll be able to ask Pez.AI questions about your web traffic stats and trends in plain English. Pez.AI does all the hard work of constructing GA queries and summarizing the results right in Slack. And since Pez.AI is always there for you, you can get insights whenever you need them.

Everyone on the beta list will have early access to the GA application. We’ll be rolling out instances within the next few weeks. We’ve streamlined the installation to under 30 seconds, so you’ll be able to get insights quickly.

In other news, we also have a few partnerships in the works for more enterprise applications in the finance and customer service verticals. If you are interested in a partnership opportunity or would like custom AI development, give us a shout.

Intro to data structures for Excel users

In this series of posts, we teach programming concepts from the perspective of spreadsheets using pez, Zato Novo’s data analysis language. If you know Excel, then you already have the foundation to start coding!

Data structures form the backbone of any programming language (and software system), and for computer science students it can send a shiver down their spine. But data structures don’t have to be intimidating. By the end of this post, you’ll be able to work with them confidently and efficiently.

So what is a data structure? Simply put they are containers that hold data. A spreadsheet is actually a massive data structure that represents data as a grid. Spreadsheets are good for displaying all the gory details of a (tabular) dataset but are cumbersome when moving data around or creating custom functions to modify data. Programming languages, on the other hand, provide compact notation for working with data structures but it can be cumbersome to see all of the data.

Most programming languages come with “batteries included”, meaning once it’s installed you have everything you need to immediately play with it. What’s implied is that all sorts of data structures are provided out of the box, which is great for variety but difficult to pick up and remember. Pez likes to err on the side of simplicity, so there are two primary data structures: lists and data frames. We’ll explore both of these structures using an example of creating financial projections for a startup.

Forecasting MRR

To make the lessons concrete, we’ll use a business forecasting example. In a previous article I showed how to use Panoptez to calculate the MRR of Slack using a basic set of assumptions. For this article, we’ll forecast the MRR of my startup, Zato Novo, based on an even simpler set of assumptions. As with the previous article, we establish a baseline approach using a Google Sheets document. This spreadsheet has a handful of columns, starting with the forecast date, followed by a projected number of paying customers. For pedagogical purposes, I’m assuming a fixed subscriber growth rate of 5% per month, which annualizes to 80%. Then I take that user number and multiply it by the base monthly price of $25/user to get a monthly recurring revenue number. To keep things simple, I’m ignoring tiers, annual prepay, and churn. This spreadsheet will be examined throughout the article as we walk through various concepts.

zn_ss_mrr

Working with lists

Okay, now let’s see how to construct the same thing in pez. Lists are an ordered collection of items and can contain any type of data. In a spreadsheet, a range of cells is analogous to a list. When we say an ordered list, items in the list are guaranteed to be in the same order as you entered them. This is like a spreadsheet where the value in A4 always follows the one in A3. In our revenue forecast example, each column is a list. It’s fine to treat each row as a list as well, although later we’ll see why it’s more convenient to think of lists as columns.

Let’s look at the first column that contains dates. In a spreadsheet we create this column by starting with an initial date. Next we define a formula that adds one month to create the next date (using EDATE in Google Sheets). We then copy and paste this formula for each successive cell to create the whole range. Our final date range lives in the cells A2:A25.

zn_ss_edate

Notice that for each successive date, we are adding one month to the previous date. Hence, the second date adds 1 to the initial date, while the fourth date adds 3, and so on. In pez, we take advantage of this observation to create the dates more compactly. First, we create the initial date, which is simply the literal text 2016-01-01. If you enter dates with this specific format, pez knows that it’s a date, just like in a spreadsheet. (The same is true of timestamps.)

Now let’s create an integer range that represents how many months the initial date needs to be added to create the complete date range. For this we use the range operator, ... For example, 0..23 creates 24 integers, from 0 to 23. The final step is to create the dates, which simply requires adding this date to the list of numbers.

See how much simpler this is than copy and pasting a formula into a number of cells? In the spreadsheet, there is one other detail, which is that the column has a header. In pez, we just assign this expression to a variable, which we’ll call month. Here is what it looks like in our Panoptez-enabled Slack.

zn_ss_month

Literal list creation

We saw how easy integer ranges can be created in the previous section. What if you want to create a list that is not an integer range? In this case, a literal list can be created using bracket notation: [x1, x2, x3, ..., xn]. With this syntax, each element is specified explicitly within square brackets. Using the date range above, the first four elements can be created as [2016-01-01, 2016-02-01, 2016-03-01, 2016-04-01]. This approach is perfectly legal, but for efficiency, it’s often easier to think about using an expression to generate the appropriate range for you.

Learn more about lists

Element selection

So what can we do with this list? In a spreadsheet we can pull specific elements from a range and reference them in a separate cell using its coordinates. For example, January of 2017 is located at A14. This approach is convenient, but what happens if we move this column somewhere else? Let’s say we add one column to the left of A. Most of the time the spreadsheet automatically updates the cell references to reflect its new location. However, that means if we need to reference it anew, we need to know where it is in the spreadsheet! For complicated spreadsheets it can start to feel like a perverse Where’s Waldo exercise. Wouldn’t be nice if we could always reference the range using the same locations? In pez, our date range is called month, so any time we access month[13] we get the first day of 2017. That means no more missing references!

The operation using the name of the variable followed by brackets, x[y], is called indexing or subsetting. The number inside the brackets is called the index. In pez, the first element starts at an index of 1, while the last element is at length(x). There are other ways to index a list, but for now we’ll stick to the basics.

Compounding growth

Let’s move on to the second column, which contains a hypothetical user growth rate. Starting with an initial value of 100 users (hey, you gotta start somewhere), we assume a monthly growth rate of 5%. So growth is compounding monthly, meaning that each month is 1.05 times greater than the prior month. To model this in a spreadsheet, we again turn to a formula. This time the formula multiplies 1.05 to the previous value instead of adding a value.

zn_ss_customers

In pez, there are a few ways to tackle this. One approach is to use the cumprod function, which takes a list of numbers and computes the cumulative product of all the numbers in the list from the first element to the current element. For example, cumprod 1..4 yields [1, 2, 6, 24], which is equivalent to [1, 1*2, 1*2*3, 1*2*3*4]. For the growth rate, we create a repeated list of 1.05 and apply cumprod to it.

zn_ss_growth_1

Calling functions is similar to calling functions in a spreadsheet, where the name of the function is followed by its arguments wrapped in parentheses. Pez supports a simpler syntax as well, which will be discussed in a future post.

You may have noticed that there’s one problem with this approach. While the spreadsheet starts at 100, our pez list starts at 105. We need to modify the list to do this. However, an even simpler approach takes advantage of how compounding works. Since the compounding rate is constant, each compounding term raises the power of the compounding. Month one is just 1, while month two is 1.05, month three is 1.05^2, and so on. Using what we’ve already learned, we can raise 1.05 to the sequence 0..23, which produces all the powers for us!

zn_ss_growth_2

Calculating the MRR

The last column to create is the monthly recurring revenue. The current assumption is $25/user/month, so we multiply each value in C2:C25 by 25.

zn_ss_mrr_g

In pez, the range C2:C25 corresponds to the variable customers, so we multiply that by 25 and assign its result to a new variable mrr.

zn_ss_mrr_pz

Again, notice how simple it is to describe this operation.

Creating the data frame

The final step is to bring all these variables together into a single table. Data frames are organized by column, which is why we claimed that it’s best to think of lists as columns. Each variable we defined is simply a column in the table.

The output table is just like the spreadsheet. To make the table easier to work with, it’s actually better to assign our dates to the index of the table. This reduces the number of columns and sets the index to the dates. We use a special @index key at the end of the table definition to specify the index.

This looks pretty good. However, notice that we had to create a whole bunch of variables to create this table. This pollutes your workspace, which makes it harder to find useful stuff in the future. It’s better to use a let expression to define temporary variables instead.

Now only the variable you care about is created in your workspace. All the others are deleted once the let expression is evaluated.

zn_ss_rev_forecast

As a final goodie, here is a plot of the MRR based on the data we created.

zn_ss_rev_plot

Conclusion

Data structures are an important part of programming. In this article, we took your existing knowledge of Excel and showed how cell ranges are lists and tables are data frames. You also got a taste of let expressions and vectorization, which are two powerful features of pez.

Panoptez is a collaborative data analysis and visualization platform accessible via chat systems, like Slack. Request an invite to the beta or contact us for preferred access.

How to calculate monthly recurring revenue (MRR) in Slack instead of Excel

FastCompany wrote an article about Slack, which cited some subscriber numbers. This got me wondering what their monthly recurring revenue (MRR) is based on these figures. The MRR is a key metric that helps determine if your company is cashflow positive or not. Knowing the MRR also gives you insight into a SaaS company’s P/E ratio. Since we don’t know if Slack is profitable, we can’t compute the P/E. We can, however, use price-to-revenue as a naive proxy. In this article, I show how to use Panoptez within Slack to calculate the MRR and P/R instead of Excel (or other spreadsheet program).

A spreadsheet (e.g. Excel, Google Sheets) is often the go-to tool when you want to make a quick back-of-the-envelope calculation. In isolation this is sufficient, but when sharing your calculation with others, it becomes more involved. Within a team, it’s also likely that you want to share your methodology or the function you wrote to your colleagues. In a spreadsheet this becomes a bit more challenging since usually it means writing a function in Visual Basic or something comparable and then figure out how to distribute that among your colleagues. For this article, we’ll ignore the sharing aspect and focus on only the calculations. Our baseline will be using Google Sheets to implement these values.

The Data

First, we need the raw data. In this case, it comes from FastCompany, which says Slack has 370,000 paid subscribers. Slack has two tiers of pricing, but FastCompany doesn’t break this out for us. The pricing itself comes from Slack, where they list the price of the standard and plus plans.

slack_pricing

To get a single value for the MRR, we need to know how many people pay for the standard versus the plus tier. We also need to know how many pay month-to-month versus annually. Since these numbers aren’t available, we have to make assumptions for the proportion of subscribers in each plan as well as the ratio of subscribers paying month-to-month versus annually. My hand-waving guess is 70% pay for the standard tier and 30% pay for plus. I also assume that 70% of the standard tier pay month-to-month and 30% pay annually. For the plus tier I assume the opposite. If you have better assumptions, please let me know in the comments!

Spreadsheet Calculation

In a spreadsheet, the normal procedure is to populate cells with these values and add some labels for the rows and columns. Next we create a formula to hold some intermediate results. In our case this is the weighted monthly value of a user in the standard and plus tiers. The formula bar shows the computed value for the standard tier.

slack_mrr_1

To get the MRR we tally those up and multiply by the number of paid subscribers. This gives us $3.44 million per month, or $41.3 million per year.

slack_mrr_2

That means with a private valuation of $2.8 billion, the P/R is about 68. Remember, this doesn’t equate to the P/E, since we aren’t accounting for expenses, so the P/E will likely be much higher. This is a detail overlooked in the Business Insider article that you shouldn’t ignore.

Using Panoptez

Now let’s see how to do the same thing in Panoptez. First, we create a nearly identical table. Remember that since this is in Panoptez, once this table is created, any colleague on Slack can access this same table to use as they wish. We’ll create a data frame using { } notation and assign it to the variable slack_stats. In case you’re wondering, a “data frame” is a fancy way of saying “table”.

slack_stats

Here’s a text version so you can copy and paste into your Panoptez-enabled Slack.

Each list within the data frame represents a column of the table. In our spreadsheet, the first column of data represented the standard pricing tier. To reference it, we would create a range from B2:B6. Our data frame holds the same data, except we reference it as slack_stats$standard. The @index at the end of the data frame sets the row names for the table. If we don’t specify this, the rows will simply be numbered numerically.

To calculate the weighted value of each tier, we’ll create a temporary function. Since Panoptez tracks all variables created in your workspace, it can fill up with a bunch of garbage quickly. To reduce clutter, you can use what’s known as a “let expression” to create temporary variables that will disappear after the expression has been evaluated. The basic structure of a let expression is let x in y. In this example, we create a temporary function f and then apply it to slack_stats$standard. The function itself is doing the same thing as in the spreadsheet formula =B2 * (B3*B5 + B4*B6), except we use the dot product (the ** operator) instead of explicitly summing the two products. The value at x[1] corresponds to B2 in the spreadsheet, since that is the range we are passing to the function. If we had used slack_stats$plus instead, then x[1] would correspond to C2.

slack_wv_fn

Putting it all together, we can take our let expression and use it inside a function! That means we can create a temporary function to simplify the overall calculation. This last step creates a function that accepts the number of paying users and calculates the MRR. Notice that the expression following the in is essentially the same as in the spreadsheet, which was =F2*(B8+C8). The difference is that instead of cell positions, we are using variables and functions. The variable u is equivalent to F2, while f(slack_stats$standard) evaluates to the same value as B8.

slack_mrr_fn

This is the code to try in your Panoptez-enabled Slack session.

To get the final result, we simply call this function like !pez slack_mrr 370000. The nice thing about having a function is that as Slack’s user base changes, we can call this function again to get the latest MRR.

slack_mrr_calc

Conclusion

In this post, I’ve shown how to use Panoptez to calculate an estimate of Slack’s MRR. I’ll leave it to reader to write an expression that calculates the P/R ratio from this. In a subsequent post, we’ll look at changing the assumptions used in this example.

Panoptez is a collaborative data analysis and visualization platform accessible via chat systems, like Slack. Request an invite to the beta or contact us for preferred access.