review_interval

import pandas as pd, numpy as np
from statsmodels.stats.proportion import proportion_confint

df = (pd.read_csv('../exports/categories_deliveries.csv', dtype={'is_single_category':"boolean"})
        .query("order_status == 'delivered'")
        .dropna(subset=['delay_days', 'review_score'])
        [['order_id', 'delay_days', 'review_score', 'is_single_category']]
        .drop_duplicates('order_id'))


bins   = [-np.inf, -1, 0, 3, 7, 14, np.inf]
labels = ['early', 'on time', '1-3', '4-7', '8-14', '15+']
df['delay_bin'] = pd.cut(df.delay_days, bins=bins, labels=labels)

ci_full = (df.assign(negative=df.review_score <= 2)
       .groupby('delay_bin', observed=True)
       .agg(n_orders=('order_id', 'count'), n_negatives=('negative', 'sum'))
       .reset_index())

ci_full['rate'] = ci_full.n_negatives / ci_full.n_orders

ci_full[['wald_lo', 'wald_hi']] = pd.DataFrame(
    [proportion_confint(k, n, method='normal') for k, n in zip(ci_full.n_negatives, ci_full.n_orders)],
    index=ci_full.index)

ci_full[['wilson_lo', 'wilson_hi']] = pd.DataFrame(
    [proportion_confint(k, n, method='wilson') for k, n in zip(ci_full.n_negatives, ci_full.n_orders)],
    index=ci_full.index)

ci_full['single_category_orders'] = False
ci_full

	delay_bin	n_orders	n_negatives	rate	wald_lo	wald_hi	wilson_lo	wilson_hi	single_category_orders
0	early	88163	8130	0.092216	0.090306	0.094125	0.090323	0.094143	False
1	on time	1280	159	0.124219	0.106150	0.142288	0.107266	0.143420	False
2	1-3	1852	595	0.321274	0.300007	0.342542	0.300396	0.342893	False
3	4-7	1748	1183	0.676773	0.654848	0.698699	0.654481	0.698291	False
4	8-14	1446	1159	0.801521	0.780964	0.822079	0.780176	0.821269	False
5	15+	1335	1046	0.783521	0.761428	0.805613	0.760632	0.804783	False

While there are barely any difference between the confidence interval from Wald’s and Wilson’s method, in order to have a guardrail against a small sample with a small rate, we will be choosing Wilson’s as Wald’s would produce a misleading result under those conditions.

Now, there are orders that have multiple products with multiple categories. A bulkier item may affect the delivery time of the smaller items. Hence, we will see whether the interval change if we exclude the orders that carries multiple categories.

ci_single = (df[df['is_single_category'] == True].assign(negative=df.review_score <= 2)
       .groupby('delay_bin', observed=True)
       .agg(n_orders=('order_id', 'count'), n_negatives=('negative', 'sum'))
       .reset_index())

ci_single['rate'] = ci_single.n_negatives / ci_single.n_orders

ci_single[['wald_lo', 'wald_hi']] = pd.DataFrame(
    [proportion_confint(k, n, method='normal') for k, n in zip(ci_single.n_negatives, ci_single.n_orders)],
    index=ci_single.index)

ci_single[['wilson_lo', 'wilson_hi']] = pd.DataFrame(
    [proportion_confint(k, n, method='wilson') for k, n in zip(ci_single.n_negatives, ci_single.n_orders)],
    index=ci_single.index)


ci_single['single_category_orders'] = True
ci_single

	delay_bin	n_orders	n_negatives	rate	wald_lo	wald_hi	wilson_lo	wilson_hi	single_category_orders
0	early	87413	7819	0.089449	0.087557	0.091341	0.087575	0.091359	True
1	on time	1273	156	0.122545	0.104532	0.140559	0.105659	0.141703	True
2	1-3	1846	593	0.321235	0.299934	0.342536	0.300324	0.342889	True
3	4-7	1742	1179	0.676808	0.654846	0.698771	0.654477	0.698361	True
4	8-14	1443	1156	0.801109	0.780513	0.821704	0.779726	0.820893	True
5	15+	1333	1044	0.783196	0.761075	0.805317	0.760278	0.804486	True

There are barely any changes, probably due to the fact that orders with multiple categories are not common in this dataset. However, it is good to still have the option to look both for the future.