redshift_simulate_split_test.sql

-- Python function template to simulate the effects of a split test 
-- Returns string with expected uplift and probability of Alt being better than Orig
-- as these are the values used to support decision making for UIUX testing
create function f_simulate_split_test(completions_orig INTEGER, sessions_orig INTEGER,
                                      completions_alt INTEGER, sessions_alt INTEGER) 
;

create function f_simulate_split_test(completions_orig INTEGER, sessions_orig INTEGER,
                                      completions_alt INTEGER, sessions_alt INTEGER) 
returns varchar(2000)
STABLE
AS $$

    # Based on sessions and completions, similute the effects of a split test
    # and write a descriptive string 
    import numpy as np
    from scipy.stats import scoreatpercentile 
    
    TRIALS = 10000
    conv_orig = np.random.beta(completions_orig + 1, sessions_orig - completions_orig + 1, size=TRIALS)
    conv_alt = np.random.beta(completions_alt + 1, sessions_alt - completions_alt + 1, size=TRIALS)

    conv_delta = conv_alt - conv_orig

    # improvement range 
    mean_delta = conv_delta.mean() 
    lower_delta = scoreatpercentile(conv_delta, 2.5)
    upper_delta = scoreatpercentile(conv_delta, 97.5)

    # probability alt better 
    p_alt_better = (conv_alt > conv_orig).mean()

    return ("Conv increase: " + str(round(mean_delta * 100, 2)) + 
            " (" + str(round(lower_delta * 100, 2)) + " to " + 
            str(round(upper_delta * 100, 2)) + ") " +
            "Prob Alt Better: " + str(round(p_alt_better * 100, 2)) + "%")

$$ LANGUAGE plpythonu
;

grant execute on function f_simulate_split_test(completions_orig INTEGER, sessions_orig INTEGER,
                                      completions_alt INTEGER, sessions_alt INTEGER) to group analysts
;