6-Funnel Analysis

Company XYZ sells a software for 39. Since revenue has been flat for some time, the VP of Product has decided to run a test increasing the price. She hopes that this would increase revenue. In the experiment, 66% of the users have seen the old price (39), while a random sample of 33% users a higher price (59).

The test has been running for some time and the VP of Product is interested in understanding how it went and whether it would make sense to increase the price for all the users.

Especially he asked you the following questions:

• Should the company sell its software for 39 or 59?
• The VP of Product is interested in having a holistic view into user behavior, especially focusing on actionable insights that might increase conversion rate. What are your main findings looking at the data?
• [ Bonus ] The VP of Product feels that the test has been running for too long and he should have been able to get statistically significant results in a shorter time. Do you agree with her intuition? After how many days you would have stopped the test? Please, explain why.

Index

• Load the data
• Check whether test/control group is randomly splitted?
• Answer question 1
• Answer question 2
  • Chi-Square test
  • How price affects conversion?
  • How OS affects conversion?
  • Model by Decision Tree
  • Actionable-Insights

import numpy as np
import pandas as pd
import scipy.stats as ss
from sklearn.preprocessing import LabelEncoder
from sklearn.feature_selection import chi2,f_classif
from sklearn.tree import DecisionTreeClassifier,export_graphviz

import matplotlib.pyplot as plt
plt.style.use('ggplot')
%matplotlib inline

seed = 999

Load the data

testdata = pd.read_csv("test_results.csv",index_col="user_id")
# users contains [id,city,country,lat,long]
# and all 'country' column is USA
# I don't think users can provide useful information to the problems, so just ignore user_table.csv
# users = pd.read_csv("user_table.csv")

# some time has 60 seconds/minute, which cannot be processed by to_datetime
# if given more time, I will fix this incorrect format
# testdata["timestamp"] = pd.to_datetime(testdata.timestamp)

# rename to short names, make it easier to display
testdata.rename(columns={'operative_system':'OS'},inplace=True)

testdata.head()

	timestamp	source	device	OS	test	price	converted
user_id
604839	2015-05-08 03:38:34	ads_facebook	mobile	iOS	0	39	0
624057	2015-05-10 21:08:46	seo-google	mobile	android	0	39	0
317970	2015-04-04 15:01:23	ads-bing	mobile	android	0	39	0
685636	2015-05-07 07:26:01	direct_traffic	mobile	iOS	1	59	0
820854	2015-05-24 11:04:40	ads_facebook	web	mac	0	39	0

Check whether test and control group is randomly splitted?

X = testdata.copy()
del X['timestamp']

# to use sklearn to check feature importance, we must convert string values to numbers
src_label_encoder = LabelEncoder()
dev_label_encoder = LabelEncoder()
os_label_encoder = LabelEncoder()

X["source"] = src_label_encoder.fit_transform(testdata.source)
X["device"] = dev_label_encoder.fit_transform(testdata.device)
X["OS"] = os_label_encoder.fit_transform(testdata.OS)

X.head()

	source	device	OS	test	price	converted
user_id
604839	3	0	1	0	39	0
624057	8	0	0	0	39	0
317970	0	0	0	0	39	0
685636	5	0	1	1	59	0
820854	3	1	3	0	39	0

run Chi-Square test to see which factor impact the splitting between test/control group

colnames = ["source","device","OS"]
ch2values,pvalues = chi2(X.loc[:,colnames],X["test"])
pd.DataFrame({'chi2_value':ch2values,'pvalue':pvalues},index = colnames).sort_values(by='pvalue')

	chi2_value	pvalue
OS	83.085986	7.856065e-20
device	44.366335	2.723301e-11
source	0.924742	3.362329e-01

from above result, we know that the splitting between test/control groups aren’t random. OS and device has great impact on the split.

def calc_ratios(s):
    d = s.value_counts(normalize=True)
    d['total'] = s.shape[0]
    return d
test_ctrl_by_os = testdata.groupby('OS')['test'].apply(calc_ratios).unstack()
test_ctrl_by_os

	0	1	total
OS
android	0.643358	0.356642	74935.0
iOS	0.647934	0.352066	95465.0
linux	0.533736	0.466264	4135.0
mac	0.652422	0.347578	25085.0
other	0.647865	0.352135	16204.0
windows	0.629764	0.370236	100976.0

test_ctrl_by_os.plot(kind='bar',figsize=(15,5))

<matplotlib.axes._subplots.AxesSubplot at 0x1198e9e10>

os_by_test_ctrl = testdata.groupby('test')['OS'].apply(lambda s: s.value_counts(normalize=True)).unstack()
os_by_test_ctrl 

	windows	iOS	android	mac	other	linux
test
0	0.313678	0.305115	0.237807	0.080729	0.051784	0.010887
1	0.327729	0.294636	0.234280	0.076434	0.050021	0.016901

os_by_test_ctrl.plot(kind='bar',figsize=(15,5))

<matplotlib.axes._subplots.AxesSubplot at 0x119e8f090>

we can see that OS distribution in test/control groups are very different.

the experiment claim that ‘66% of the users have seen the old price (39), while a random sample of 33% users a higher price (59).’, but users from Linux don’t obey such rule, but 53% go to control group, 47% go to test group.

Answer question 1

Should the company sell its software for 39 or 59?

revenues = testdata.groupby(by="test").apply(lambda df: df.price * df.converted)
ctrl_revenues = revenues[0]
test_revenues = revenues[1]

def group_statistics(df):
    return pd.Series({'n_users': df.shape[0],
                      'convert_rate': df.converted.mean(), 
                      'mean_revenue': (df.price * df.converted).mean()})
testdata.groupby('test').apply(group_statistics)

	convert_rate	mean_revenue	n_users
test
0	0.019904	0.776734	202727.0
1	0.015543	0.916843	114073.0

we are going to perform a t-test to test whether the test group’s average revenue is higher than control group’s average revenue

• H0: test group’s average revenue equal to control group’s average revenue
• HA: test group’s average revenue is higher than control group’s average revenue

ttest_result = ss.ttest_ind(test_revenues,ctrl_revenues,equal_var=False)
# ttest_ind is a two tailed
# since our HA is test_mean > ctrl_mean, so we need to divide by 2
ttest_result.pvalue/2 

7.7037493023391909e-09

since the p-value is much smaller than 0.05 threshold, so we reject H0, and accept HA, that is, test group’s average revenue is significantly higher than control group’s average revenue.

but due to OS distribution aren’t the same between test/control groups, price isn’t the only difference between test/control groups, so we cannot contribute the increase in average revenue to price.

the experiment design fails, I cannot answer decide whether we can sell software to all users in 59 dollars. I suggest

1. find the reason why linux users have different representation ratio than other OS.
2. then run the experiment again, make sure price is the only difference between test and control group
3. then run t-test again, to see whether the average revenue is significantly improved.
4. then I can draw the conclusion.

Answer question 2

The VP of Product is interested in having a holistic view into user behavior, especially focusing on actionable insights that might increase conversion rate. What are your main findings looking at the data?

# X is the data after being transformed by LabelEncoder
X.tail()# glance the data

	source	device	OS	test	price	converted
user_id
17427	3	1	5	0	39	0
687787	5	1	5	0	39	0
618863	1	1	3	0	39	0
154636	6	0	0	0	39	0
832372	3	0	0	1	59	0

Chi-Square test

I first run Chi-Square test to see which feature greatly impact converted or not

colnames = ["source","device","OS",'price']
ch2values,pvalues = chi2(X.loc[:,colnames],X["converted"])
pd.DataFrame({'chi2_value':ch2values,'pvalue':pvalues},index = colnames).sort_values(by='pvalue')

	chi2_value	pvalue
price	150.992849	1.051844e-34
OS	7.642955	5.699447e-03
source	2.373391	1.234187e-01
device	0.729490	3.930485e-01

Chi-Square test tells us,

• Price and OS are two main factor which impact converted or not.
• Source and Device don’t impact conversion rate that much.

How price affects conversion?

converted_by_price= testdata.groupby("price")['converted'].apply(lambda s: s.value_counts(normalize=True)).unstack()
converted_by_price

	0	1
price
39	0.980111	0.019889
59	0.984430	0.015570

Users see 59 dollar has lower conversion rate than users see 39 dollars.

How OS affects conversion?

converted_by_os = testdata.groupby("OS")['converted'].apply(lambda s: s.value_counts(normalize=True)).unstack()
converted_by_os.sort_values(by=1,ascending=False)

	0	1
OS
mac	0.976002	0.023998
iOS	0.977678	0.022322
windows	0.983045	0.016955
android	0.985067	0.014933
other	0.987040	0.012960
linux	0.991778	0.008222

os_by_converted = testdata.groupby("converted")['OS'].apply(lambda s: s.value_counts(normalize=True)).unstack()
os_by_converted

	android	iOS	linux	mac	other	windows
converted
0	0.237357	0.300117	0.013187	0.078725	0.051429	0.319185
1	0.192665	0.366908	0.005854	0.103650	0.036157	0.294766

os_by_converted.plot(kind='bar',figsize=(10,5))

<matplotlib.axes._subplots.AxesSubplot at 0x11b253f10>

os_by_converted.transpose().plot(kind='bar')

<matplotlib.axes._subplots.AxesSubplot at 0x1250d40d0>

from above result, we can see

• Mac and iOS users have higher conversion rate than users from other OS.
• Linux users have lower conversion rate than users from other OS.

Model by Decision Tree

I will build a Decision Tree to get feature importance. Since the question is asking about “actionable insight” other than a model which precisely predict converted or not, so I will just fit a shallow Decision Tree on all the data, without giving out model’s performance on test set.

testdata.sample(10)# glance the data

	timestamp	source	device	OS	test	price	converted
user_id
840498	2015-03-23 19:22:17	seo-google	mobile	other	0	39	0
839879	2015-05-05 09:59:57	direct_traffic	mobile	iOS	1	59	0
468013	2015-03-29 23:52:11	direct_traffic	mobile	iOS	1	59	0
377372	2015-04-05 07:12:46	seo-google	mobile	android	1	59	0
713838	2015-03-10 13:53:46	seo-other	web	windows	0	39	0
140135	2015-03-08 10:12:03	ads_other	mobile	iOS	1	59	0
198679	2015-04-11 07:05:34	seo-google	web	windows	0	39	0
547268	2015-03-14 14:04:22	ads-google	mobile	iOS	0	39	0
334905	2015-05-28 10:14:47	ads-bing	web	mac	0	39	0
813274	2015-03-09 19:43:28	friend_referral	web	windows	0	39	0

X = testdata.copy()
del X['timestamp']
del X['test']

X.source.value_counts()

direct_traffic     60357
ads-google         59379
ads_facebook       53396
ads_other          29876
seo-google         23175
ads-bing           22873
seo_facebook       21205
friend_referral    20695
seo-other           9260
ads-yahoo           7583
seo-yahoo           6848
seo-bing            2153
Name: source, dtype: int64

X.device.value_counts()

mobile    186471
web       130329
Name: device, dtype: int64

X.OS.value_counts()

windows    100976
iOS         95465
android     74935
mac         25085
other       16204
linux        4135
Name: OS, dtype: int64

# One-Hot-Encoding on categorical features
X = pd.get_dummies(X)

# a categorical feature of K unique values, only need K-1 vectors
# I don't use 'drop_first' parameter in get_dummies, since it cannot specify which level to drop
del X['source_ads_other']
del X['device_web']
del X['OS_other']

X.tail()# glance the data

	price	converted	source_ads-bing	source_ads-google	source_ads-yahoo	source_ads_facebook	source_direct_traffic	source_friend_referral	source_seo-bing	source_seo-google	source_seo-other	source_seo-yahoo	source_seo_facebook	device_mobile	OS_android	OS_iOS	OS_linux	OS_mac	OS_windows
user_id
17427	39	0	0.0	0.0	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0
687787	39	0	0.0	0.0	0.0	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0
618863	39	0	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0	0.0
154636	39	0	0.0	0.0	0.0	0.0	0.0	1.0	0.0	0.0	0.0	0.0	0.0	1.0	1.0	0.0	0.0	0.0	0.0
832372	59	0	0.0	0.0	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0	1.0	0.0	0.0	0.0	0.0

Xtrain = X.loc[:,X.columns != 'converted']
ytrain = X.loc[:,'converted']

Xtrain.head()# glance the data

	price	source_ads-bing	source_ads-google	source_ads-yahoo	source_ads_facebook	source_direct_traffic	source_friend_referral	source_seo-bing	source_seo-google	source_seo-other	source_seo-yahoo	source_seo_facebook	device_mobile	OS_android	OS_iOS	OS_linux	OS_mac	OS_windows
user_id
604839	39	0.0	0.0	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0	0.0	1.0	0.0	0.0	0.0
624057	39	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0	0.0	0.0	0.0	1.0	1.0	0.0	0.0	0.0	0.0
317970	39	1.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0	1.0	0.0	0.0	0.0	0.0
685636	59	0.0	0.0	0.0	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0	0.0	1.0	0.0	0.0	0.0
820854	39	0.0	0.0	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0	0.0

dt = DecisionTreeClassifier(max_depth=4)
dt.fit(Xtrain,ytrain)
export_graphviz(dt,feature_names=Xtrain.columns,proportion=True,leaves_parallel=True)

from above tree plot, we can see that, to reach the leaf node with highest conversion rate (the third leaf from right with conversion rate=0.05), the path is:

1. source ‘friend_referral’ = true
2. price < 49, i.e., price = 39
3. OS ‘iOS’= true

pd.Series(dt.feature_importances_,index = Xtrain.columns).sort_values(ascending=False)

source_friend_referral    0.663549
OS_iOS                    0.143231
OS_mac                    0.107014
source_ads-google         0.043922
price                     0.022918
OS_linux                  0.019366
source_ads-bing           0.000000
source_ads-yahoo          0.000000
source_ads_facebook       0.000000
source_direct_traffic     0.000000
OS_windows                0.000000
source_seo-bing           0.000000
source_seo-other          0.000000
source_seo-yahoo          0.000000
source_seo_facebook       0.000000
device_mobile             0.000000
OS_android                0.000000
source_seo-google         0.000000
dtype: float64

Actionable Insights

• friend_referral, Apple user (MAC or iOS), low price are three great positive factors which improve the conversion rate
• if we want to increase the price, which impacts negatively on conversion rate, we must compensate on the other two factors
  • lauch special marketing program targeted to Apple users (MAC or iOS users)
  • run some program which rewards user which can invite his/her friend to use our software
• Linux users don’t like our software as much as users on other OS. Development team should find out the reason. For example, is there any incompatibility issue on Linux?