sample excel Questions

1. What is the total number of players that were placed into the test? How many players are
in the Control group? How many players are in the Alt group?

            Using the = count(A3:A10002) give the total number of nonempty cells in the column in this case, the total number of players enrolled in the test are 10,000. The total number of players enrolled in the control group can be obtained using the =COUNTIFS(B3:B10002,"Control") function. The function counts the number of cells containing string characters in the column.  There are 4823 players in the control group, and 5177 in the Alt group.

2. How many spenders are there by Day 1, Day 3, Day 7, Day 15 and Day 30 of the test
within each group? What is the average revenue per user by Day 7, Day 30? What is the
average revenue per spender by Day 7, Day 30?

The number of spenders in each day can be obtained by getting a sum of the value. Because 1 represents presence and 0 absence, running the sum() function on the column would give the total spenders in each day. For Day 1: =SUM(C3:C10002) = 3004. For Day 3, Day 7, Day 15 and Day 30, the number of spenders available were 2095, 1714, 924 and 595 respectively. To get the average revenue spent by Day 7 and Day 30, the =AVERAGEIF(J3:J10002,">0") function is used. The function eliminates cells with zeros. Thus, the valid cells are those with values greater than zero. The average spent by day 7 is $ 8.086, and the average spent y Day 30 is $12.302.

3. On average within each group, for players who purchased coins, what percent of the
purchased coins were spent on playing matches or purchasing each powerup?

=AVERAGEIFS(Y3:Y10002,Y3:Y10002,">0",B3:B10002,"Alt") function is used to calculate the average purchased coins spent on matches by the Alt group. The function’s parameters are the average range, and the set of criteria applicable. First, we specify that the average of only the cells with none zeros are the valid cells, then we select the group of interest. The function was applied to generate the other relevant statistics, and  the results are presented in the table below.

The percent value was obtained by applying the =(B10+B11)/B12 * 100 formula. That’s by finding the sum of the two averages and dividing by the total coins purchased within the group. 68.76% and 65.90 % of the purchased coins were spent on matches and powerup extra coins by the Alt and the Control group respectively.

4. Based on the data set provided, what is different about the Extra Cards Powerup
between the Control group and Alt group? What effect did this change have on other
metrics within the game?

Using =AVERAGEIFS(W3:W10002,W3:W10002,">0",$B$3:$B$10002,"Alt") gives the average Extra Cards Powerup purchased by the control group. Replacing “Alt” with “Control” gives the average spent by the control group. Function calculate the mean for the data that meet the specified criteria. Replacing AVERAGEIFS with SUMIFS gives the total extra cards Power up purchased.  The table below presents the sum and the averages for each group.

The Alt group purchased more power up extra cards than the Control group. This affected the number of coins purchased by each group – as it is evidence from 4 that the group had to purchase coins.

5. Which A/B test group would you declare to be the winner of the test and why? Can you
provide a reasonable explanation why the A/B Test change correlates to this outcome?
a. Note: declaring a test winner means all players will receive the winning groups
experience

The winner of the test would be the group associated with the least matches lost on average. First, matches won are subtracted from matches plaid to obtain the number of matches by each team. The function =AVERAGEIF(B3:B10002,"Alt", AC3:AC10002) gives the average matches lost by the Alt group. The same function is applied for the Control group. The Alt group lost 36.54 matches while the Control group lost 34.17 matches of average. The Control group won the test because it lost the least on average.

6. If you had access to any dataset, what additional data would you look for that could
influence your final decision on the winning group of the test?

The time taken in each test could be variable that can be factored to determine the winning group.