Free to Paid Conversion Rates

Summary

Around 30% of Subscription Started events come from users that either haven’t been on a trial or started one more than 30 days prior to the conversion event.

The free-to-paid conversion rate was estimated by looking at the number of Subscription Started events that came from users without an associated trial and dividing it by the total number of users that signed up that month less the number that converted with an associated trial.

Even though there is a set of assumptions inherent in that calculation, we can estimate the free-to-paid conversion rate to be somewhere around 1.25%.

# connect to bigquery
con <- dbConnect(
  bigrquery::bigquery(),
  project = "buffer-data"
)

# define sql query
sql <- "
  select distinct
    u.id as user_id
    , u.account_id
    , u.created_at as signup_at
    , t.id as trial_id
    , t.trial_start_at
    , t.trial_end_at
    , t.converted
    , s.id as sub_start_id
    , s.original_timestamp as converted_at
    , s.plan_id
    , s.subscription_id
    , s.trial_start_date as sub_trial_start
    , s.trial_end_date as sub_trial_end
  from dbt_buffer.publish_users u
  left join dbt_buffer.stripe_trials t
    on u.stripe_customer_id = t.customer_id
  left join segment_publish_server.subscription_started s
    on s.user_id = u.account_id
  where u.created_at between '2019-07-01' and '2020-04-15'
"
  
# query BQ
users <- dbGetQuery(con, sql)

# save data
saveRDS(users, "free_to_paid_conversion.rds")

Great, there are 731 thousand users that signed up since July 2019.

Diagnostic Plots

Let’s plot the number of events that occur over time to spot check the data.

We started putting all new signups on Trial around August 2019.

This plot seems to indicate that most Subscription Started events have a trial associated with them. Still, there seem to be a significant number of conversions that come from users that aren’t on a trial or have never had a trial.

Around 30% of conversion events come from users that haven’t had a trial or started one more than 30 days prior to the conversion event.

Conversion Rates

Next we will come up with a conversion rate for Free -> Paid. If the conversion event occurrs more than 30 days after the trial started (~16 days after the trial ends), we’ll assume that the user was on the Free plan for a sufficient amount of time.

What should the denominator be? We want the total number of users that were on the Free plan.

The conversion rate is lowest in the most recent months because those users have had the least amount of time to convert, and the COVID-19 pandemic. We can estimate that, under more “normal” circumstances, the free-to-paid conversion rate is somewhere around 1.25%.