Data collection

We can write a SQL query that will return the customer and plan IDs for every charge made in the 12 months surrounding the price change. We will exclude refunded charges.

# connect to redshift
con <- redshift_connect()

select
  c.created as charge_date
  , c.customer as customer_id
  , i.subscription_plan_id as plan_id
  , p.simplified_plan_id
from stripe._charges as c
inner join stripe._invoices as i
  on c.invoice = i.id
left join simplified_billing_plan as p
  on i.subscription_plan_id = p.plan_id
where c.captured
  and c.refunded = FALSE
  and c.created >= '2015-09-13'
  and c.created <= '2016-09-07'

Great, we have lots of charges to sift through now. We have to identify upgrades.

Identifying upgrades

To do this, we’ll need to group the charges by customer and find the first charge date of the awesome and business subscriptions, if they exist.

# replace enterprise with business
charges <- charges %>%
  mutate(simplified_plan_id = gsub('enterprise', 'business', simplified_plan_id))

# count number of plan types users have charges for
plan_counts <- charges %>%
  filter(simplified_plan_id == 'awesome' | simplified_plan_id == 'business' ) %>%
  group_by(customer_id) %>%
  summarise(plan_types = n_distinct(simplified_plan_id))

# group by customer
by_customer <- charges %>%
  group_by(customer_id, simplified_plan_id) %>%
  summarise(first_charge_date = min(charge_date)) %>%
  inner_join(plan_counts, by = 'customer_id')

When a user has charges for awesome and business charges, we can assume that an upgrade occurred if the business charge comes at a later date.

# filter to only show customers with > 1 plan
customers <- by_customer %>%
  filter(plan_types > 1) %>%
  spread(simplified_plan_id, first_charge_date)

# remove NULL plan ID
customers <- customers[, -5]

# rename columns
colnames(customers) <- c('customer_id', 'plan_types', 'first_awesome_charge', 'first_business_charge')

# identify upgrades
customers <- customers %>%
  mutate(upgraded = first_business_charge > first_awesome_charge)

Now let’s plot the number of upgrades that occurred each week.

We can see a slight decrease after the price change went into effect. Let’s try to determine the size of this effect, and whether or not it is statistically significant.

Estimating causal impact

We can use the CausalImpact package to estiamte the effect that the price change had on upgrades to Business plans from customers that were previously on Awesome subscriptions.

To perform inference, we run the analysis using the CausalImpact command.

# run analysis
impact <- CausalImpact(upgrades_ts, pre.period, post.period, model.args = list(niter = 5000))

Let’s plot the outcome.

# plot results
plot(impact)

Let’s summarise the effect.

summary(impact)

## Posterior inference {CausalImpact}
## 
##                          Average       Cumulative     
## Actual                   29            723            
## Prediction (s.d.)        31 (4)        769 (100)      
## 95% CI                   [23, 38]      [574, 960]     
##                                                       
## Absolute effect (s.d.)   -1.8 (4)      -45.9 (100)    
## 95% CI                   [-9.5, 5.9]   [-237.2, 148.6]
##                                                       
## Relative effect (s.d.)   -6% (13%)     -6% (13%)      
## 95% CI                   [-31%, 19%]   [-31%, 19%]    
## 
## Posterior tail-area probability p:   0.3056
## Posterior prob. of a causal effect:  69%
## 
## For more details, type: summary(impact, "report")

We can’t quite say that there was a statistically significant effect on the number of upgrades.