Building vs Buying Software

At Automattic and now at Help Scout I’ve been involved in several discussions about the merits of building something internally vs paying for a third party SaaS tool or plugin. Examples include the internal analytics platform Automattic (which we wound up building ourselves) and a business intelligence tool at Help Scout (which we wound up buying).

If you’re lucky it will be an easy decision, but more often than not there will be pros and cons to both approaches that you’ll have to weigh. Some of the factors include:

Do you have the engineering resources and know-how to build it? If not, you’ll obviously have to buy.

How much time will it take to build? As anyone who has built software knows, estimating how much time it will take to “complete” is often incredibly difficult. A one month project could easily turn into four after all is said and done. The more complex the project and the longer it will take to build, the more you should lean towards buying.

How much does it cost to buy? The cheaper it is the more it makes sense to buy.

How much will it cost to build? At first this might seem like $0, but engineers are expensive. A developer making $120K/year might be $180K/year fully loaded (with benefits, taxes, etc) or $15K/month. Several developers spending a month on a project can easily cost tens of thousands of dollars. And that’s not even counting PMs, designers, and anyone else involved in the project. It’s tempting to think “we have to pay them anyway so it doesn’t really cost us that”, but it’s not so simple once you consider what other revenue-generating projects they could be working on instead (and you don’t have to employ them anyway). There’s a real opportunity cost to building something internally.

Does the thing you’ll buy meet all of your requirements? If it’s critical that the software do something and the SaaS you’re considering doesn’t do it, you’ll probably need to build. However, you might be better off paying for a tool that is 90% of what you think you need vs spending weeks or months building something that might be 100%.

What other capabilities does the tool you’ll buy provide? While you might be able to build something that meets your minumum requirements, a tool you buy is likely going to do a lot more than the minimum. You may not think you need those extra features now, but there’s a good chance that you will later. At that point you might have little choice but to keep iterating on what you’ve built which can easily extend the duration of a project.

Is it important to own the data? Third party tools will likely collect user data which may be a concern both from privacy and analysis perspectives.

How likely is it that the third party solution will exist in 5 years? And are there alternatives you could switch to if that hot-new-startup you pay for shuts their doors in six months?

Are you building this as a long term investment? When we were considering whether to buy or build Automattic’s internal analytics platform one of the factors was that we planned on Automattic existing in 10, 20+ years so it made sense to invest time building something that we could use for the long haul. Be skeptical with this one though: while we might hope that whatever we’re working on now will exist in 20 years, the reality is that it probably won’t.

I usually default to buy unless there is a compelling reason to build. In my experience software usually takes much longer to build than initially expected so it winds up making more sense to pay for a 90% solution that you can have immediately and devote those engineering resources to the core business. But again, it’s often not so simple in practice and there can be reasons to build instead (full control over the features and design, ownership of the data, cost, etc).

I’d love to hear about your experience buying vs building. Was there ever a time you built something that you later regretted or vice versa?

Using Data to Forecast the Impact of a Pricing Change

Back in April of this year Help Scout announced we would be raising prices for customers on some of our old legacy plans in six months time (in October). I recently helped with an analysis to estimate what the impact would be on our monthly recurring revenue (MRR). We performed a similar analysis prior to the announcement, but severals months had passed so it was time for fresh forecast.

At a high level, we performed the analysis as follows:

1. Identify the variables that impact the forecast

For us, this meant variables such as:

  • Of of the customers who we announced the price increase for in April, how many have churned between then and now? And how many can we expect to churn between now and October? And how many can we expect to churn after the price increase?
  • How many can we expect to upgrade or downgrade after the price increase?
  • How many can we expect to change from monthly payments to discounted annual payments?
  • Because customers pay per user, what % of users can we expect to lose after the price increase?
  • And so on.

2. Create a spreadsheet that lets you adjust the variables to see what impact they have on the forecast

For example (and simplifying a lot), if we had W customers originally and X have churned between the announcement and now, and we expect another Y to churn between now and the price increase, and we expect Z% to churn after the price increase, paying $N/month on average, we’ll wind up with a future MRR of (W – X – Y) * (1 – Z) * N.

Our actual spreadsheet wound up become quite complex to take into account all of the variables, but in the end we had something that took into account all of the key factors that impact will the outcome.

3. Come up with a range of estimates for each of variable

Using historic trends and educated guesses, we estimated the range for each of the key variables we identified. With those in hand, we create several forecasts (pessmistic, realistic, optimistic) showing what the outcome looks like for each of those situations:

Screen Shot 2017-08-04 at 3.40.52 PM.png

My original instinct was to come back with a single number: “The forecast is $X” but my lead wisely suggested calculating several outcomes to account for the range of possibilities.

This was a fascinating exercise because it forced us understand on a deep level what the inputs are (churn rate, etc) and what impact they have on our bottom line (MRR).

If you’re interested in trying this for your business, try to create a simple spreadsheet model that takes into account various metrics (number of unique visitors, trial sign up rate, trial to paid rate, etc) that comes close to predicting your historic results, then see how it well it does going forward. You’ll likely learn a lot in the process about which metrics you need to change and by how much to achieve your growth goals.

What I’ve Been Up To

This morning I was catching up with my friend and coworker Dave Martin and we got to talking about blogging and how we both miss casual blogging: things like writing about what we’ve working on, what issues we’re running into, what we’re learning etc.

The problem for me boils down to this really high bar I have set in my head for what’s worthy of a blog post. I like writing long technical posts and it’s difficult getting used to the idea that short nontechnical posts are just fine too.

So, in an effort to get back into blogging, here’s a quick update on what I’ve been up to:

I’ve been at Help Scout now for about 4½ months and am really enjoying it. My coworkers are awesome, I love the product, and I’m really getting to level up my data science and analytics skills which I plan to write more about in the future.

My only side project these days is my 7-year old timeline maker service, Preceden. Besides about an hour of customer support each week (via Help Scout, of course), it’s almost entirely passive, though I try to put a few hours into product development and marketing each month to keep improving it.

On the home front, my two year old son and one year old daughter are doing great. I feel incredibly lucky to work remotely which lets me spend more time with them and my wife each day.

I’ve been trying to focus more on my health lately, not because of any major issues, just in an effort to feel more energetic and less stressed each day. Things like sleeping and exercising more, avoiding coffee, meditating, not checking the news so often, etc. I’ve had mixed success maintaining these efforts long term though… it’s a work in progress :).

If we haven’t chatted in a while, I’d love to catch up. Drop me a note anytime by email at matthew.h.mazur@gmail.com or on Twitter/Telegram @mhmazur. Cheers!

How to Schedule Cloning your Heroku Postgres Database Locally

In my last post, I wrote about how to set up a Bash alias to pull your Heroku data into a local Postgres database. This post takes it a step further by showing how to automate it so that the database is automatically updated on a regular basis.

Background

We left off the last post with this Bash alias:

alias prdb="dropdb preceden_production_copy; PGUSER=postgres PGPASSWORD=password heroku pg:pullHEROKU_POSTGRESQL_MAGENTA preceden_production_copy --app sushi"

This lets me run prdb which first deletes my local database copy, then pulls the production data into a new one.

When attempting to schedule this as a cronjob, I ran into several issues.

First, dropdb won’t work if the database is being used by any applications. You’ll get an error message like:

dropdb: database removal failed: ERROR:  database "preceden_production_copy" is being accessed by other users

This is a problem because I usually leave Metabase and SQLPro for Postgres open which causes dropdb to throw and error which causes pg:pull not to work because the database already exists. I then have to shut down the apps and try again, and even then there’s often some hidden connection that requires a complete reboot.

Second, it usually takes about half an hour for pg:pull to load all of the data into a database. If I’m eager to dive into the latest data, it’s frustrating to have to wait that long to do it.

Lastly, you can’t use aliases in a cronjob, meaning you’d have to duplicate the content of the alias in the cronjob.

As we’ll see, we can work through each of these issues.

Solution

There’s a way with Postgres to drop existing connections. By excuting this query through terminal before we attempt to drop the database, we ensure the command will work. Here’s what it looks like:

echo "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='preceden_production_copy';" | psql -U postgres

If all goes well, you’ll see something like:

pg_terminate_backend 
----------------------
 t
 t
(2 rows)

Next, in order to avoid duplicating the alias command and the cronjob command, we’ll combine all of the commands into a Bash script which we’ll execute from both places:

#!/bin/bash

current_date_time="`date +%Y\-%m\-%d\ %H\:%M\:%S`";
echo "Running at:" $current_date_time;

echo "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='preceden_production_copy';" | psql -U postgres
dropdb preceden_production_copy
PGUSER=postgres PGPASSWORD=password heroku pg:pullHEROKU_POSTGRESQL_MAGENTA preceden_production_copy --app sushi

This will output the date and time when the script is being run, then drop any open database connections, remove the database, then pull the production Postgres data into a new database.

Next, we can update the alias to point to this script:

alias prdb="/Users/matt/Projects/Preceden/prdb.sh"

And we can point to it from a cronjob:

0 12 * * * /Users/matt/Projects/Preceden/prdb.sh >> /Users/matt/Projects/Preceden/prdb.log 2>&1

This says “At noon every day, run prdb.sh and append the output to prdb.log”.

And voila! The data should get updated automatically once a day thanks to the cronjob and you still have the ability to run it manually using the alias.

Exploring your Heroku Rails app’s database using SQLPro for Postgres

In the past when I’ve wanted to explore production data for a Heroku-hosted Ruby on Rails app, I’ve primarily used heroku console and rake tasks. Each method has limitations though: heroku console makes easy to answer simple questions about your data, but makes it difficult to perform complicated analyses that take more than a few lines of code. Rake tasks let you perform complex analyses, but make it difficult to explore data because each time you tweak your task to do something new, you need to commit, push to production, run the task, and wait for it to execute. Neither option makes it easy to quickly explore the data.

Wouldn’t it be nice if you could quickly query your database and explore the results?

Fortunately there is a way using a combination of Heroku’s pg:pull feature and a Mac app called SQLPro for Postgres. Here’s how it works:

Step 1: Pull your production data into a local Postgres database

Heroku makes this fairly easy using the pg:pull command:

$ heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

Where mylocaldb is the name of a local Postgres database, sushi is the name of your Heroku app, and HEROKU_POSTGRESQL_MAGENT is the name of your database which you can obtain by running:

$ heroku pg:info -a sushi

If your local Postgres instance requires a user name and password, you can provide them via the command line as well:

$ PGUSER=postgres PGPASSWORD=password heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

In order for this command to work, mylocaldb can’t exist when you run this command. To delete it beforehand, you can run:

$ dropdb mylocaldb

For my own workflow combine them and use a Bash alias to make it easier to run:

alias prdb="dropdb preceden_production_copy; PGUSER=postgres PGPASSWORD=password heroku pg:pullHEROKU_POSTGRESQL_MAGENTA preceden_production_copy --app sushi"

Then I can just run prdb (my short hand for “Preceden Database”) from the command line to drop the old copy and grab the latest production data:

$ prdb
heroku-cli: Pulling postgresql-infinite-32999 ---> preceden_production_copy
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
...

Step 2: Explore the data using SQLPro for Postgres

SQLPro for Postgres is a fantastic Mac app for exploring Postgres databases. You can also query the data other ways but for quickly exploring, querying, and exporting the data, SQLPro for Postgres is hard to beat.

Here’s what the UI looks like along with an example query to display the first 10 people to sign up:

sqlpro-for-postgres.jpg

In future posts we’ll see how to query Postgres with R to analyze the data and gain insights about how people use our products.

If you’re interested in learning more, sign up for my new Data Science for Product Analytics newsletter to get notified when there are new posts.

Update: check out the follow up post, How to Schedule Cloning your Heroku Postgres Database Locally.

A Simple CROSS JOIN Example

99% of the queries I write to join tables wind up using JOIN (aka INNER JOIN) or LEFT JOIN so whenever there’s an opportunity to use one the other types, I get pretty excited 🙂. Today, that wound up being a CROSS JOIN.

Consider the following table containing charges:

How would you add add a column showing how much each charge represents as a percentage of the total charges?

Option 1: Using a subquery

One way to solve this is to use a subquery:

For each record, we divide the amount by the sum of all the amounts to get the percentage.

Option 2: Using a variable

Similar to the solution above, except here we save the sum of the amounts in a variable and then use that variable in the query:

Option 3: Using CROSS JOIN

A cross join takes every row from the first table and joins it on every row in the second table. From w3resource.com:

cross-join-round.png

In this solution, we create a result set with one value (the sum of the amounts) and then cross join the charges table on it. That will add the total to each record, which we can then divide the amount by to get the percentage:

If we didn’t want the total column in the result, we could simply exclude it:

In this case there shouldn’t be any performance gains using the CROSS JOIN vs one of the other methods, but I find it more elegant than the subquery or variable solutions.

CROSS JOIN vs INNER JOIN

Note that CROSS JOIN and INNER JOIN do the same thing, it’s just that because we’re not joining on a specific column, the convention is to use CROSS JOIN. For example, this produces the same result as the last CROSS JOIN example:

And so does this:

So why use CROSS JOIN at all? Per a Stack Overflow thread:

Using CROSS JOIN vs (INNER) JOIN vs comma

The common convention is:

* Use CROSS JOIN when and only when you don’t compare columns between tables. That suggests that the lack of comparisons was intentional.
* Use (INNER) JOIN with ON when and only when you have comparisons between tables (plus possibly other comparisons).
* Don’t use comma.

Props this Stack Overflow question for the tip about using CROSS JOIN to solve this type of problem.

Removing Gaps from Stacked Area Charts in R

Creating a stacked area chart in R is fairly painless, unless your data has gaps. For example, consider the following CSV data showing the number of plan signups per week:

Plotting this highlights the problem:

chart.png

The reason the gaps exist is that not all plans have data points every week. Consider Gold, for example: during the first four weeks there are 55, 37, 42, and 26 signups, but during the last week there isn’t a data point at all. That’s why the chart shows the gap: it’s not that the data indicates Gold went to zero signups the final week; it indicates no data at all.

To remedy this, we need to ensure that every week contains a data point for every plan. That means for weeks where there isn’t a data point for a plan, we need to fill it in with 0 so that R knows that the signups are in fact 0 for that week.

I asked Charles Bordet, an R expert who I hired through Upwork to help me level up my R skills, how he would go about filling in the data.

He provided two solutions:

1. Using expand.grid and full_join

Here’s how it works:

expand.grid creates “a data frame from all combinations of the supplied vectors or factors”. By passing it in the weeks and plans, it generates the following data frame called combinations:

The full_join then takes all of the rows from data and combines them with combinations based on week and plan. When there aren’t any matches (which will happen when a week doesn’t have a value for a plan), signups gets set to NA:

Then we just use dplyr’s mutate to replace all of the NA values with zero, and voila:

2. Using spread and gather

The second method Charles provided uses the tidyr package’s spread and gather functions:

The spread function takes the key-value pairs (week and plan in this case) and spreads it across multiple columns, making the “long” data “wider”, and filling in the missing values with 0:

Then we take the wide data and convert it back to long data using gather The - week means to exclude the week column when gathering the data that spread produced:

Using either methods, we get a stacked area chart without the gaps ⚡️:

chart.png