Question

5) Consider the following fictional scenario: A mid-sized firm has hired Viget to redesign its website. The site lets users view sample cards, design a custom card and view a pricing page before ordering. In the client kick-off meeting, Viget learns that the key success metric for the project is an increase in monthly revenue. Viget's analytics team reviews the client's revenue and website data to determine what the focus of the website redesign should be. Using the data in the table below, deduce which actions have the most impact on monthly revenue. Please explain how you arrived at your conclusion. Month Sessions View Example Cards Design Custom Card View Pricing Page Revenue March 1,695,292 April 1,670,136 15,052 15,008 18,408 May 1,868,864 25,964 19,231 9,704 13,877 7,136 14,721 June 17,236 13,523 14,135 1,727,159 1,781,272 1,956,408 1,930,809 2,367,562 2,568,950 2,741,061 17,758 20,146 22,857 17,394 17,926 24,360 20,046 26,298 30,039 28,246 27,426 24,113 27,960 July August September October November December January February 13,016 17,226 $11,443,801 $11,473,438 $14,037,828 $13,235,195 $10,465,325 $10,641,739 $10,093,305 $10,933,293 $11,872,900 $10,713,635 $11,719,280 $10,022,618 $10,355,119 14,223 15,517 14,284 15,846 13,149 13,950 19,816 19,671 7,613 12,973 9,586 14,277 2,495,498 2,202,975 2,593,533 March

Answer 1

Let us start with a few background of the objective.

There are 3 actions that a user can perform on the website namely - View Example Cards, Design Custom Card and View Pricing Page. The objective is to identify which action among these 3 most affects the Revenue (since revenue is the most important metric identified by the analytics team).

The following would be the methodology to deduce the action which most impacts revenue :-

1. Feed data on Excel and then plot the 3 vs. revenue to see the generic trend

2. Find mathematical correlation between each of the actions and the revenue; Correlation shows the degree in which the two variables are linearly related/dependent [it is a value from -1(most negatively correlated) to +1(most positively correlated)]. Find the action that is most 'correlated' to revenue by the magnitude of the correlation value (can be +ve or negative).

The steps mentioned below in detail:

Step 1

A. Copy data in excel with all columns in the above table as separate columns in cells B3:G16

Sessions Revenue Month March April May Jun July August September October November December January February March 1695292 1670136 1868864 1727159 1781272 1956408 1930809 2367562 2568950 2741061 2485498 2202975 2593533 View Example Cards 17,758 20,146 22,857 17,394 17.926 24,360 20,046 26,298 30,039 28,246 27,426 24,113 27,960 Design Custom Card 15,052 15,008 18,408 17,236 13,523 14,135 13,016 14,223 15,517 14,284 15,846 13,149 13,950 View Pricing Page 25,964 $ 19,231 $ 9,704 $ 13,877 $ 7,136 $ 14,721 $ 17,226 $ 19,816 $ 19,671 $ 7,613$ 12,973 $ 9,586 $ 14,277$ 1,14,43,801 1,14,73,438 1,40,37,828 1,32,35,195 1,04,65,325 1,06,41,739 1,00,93,305 1,09,33,293 1,18,72,900 1,07,13,635 1,17,19,280 1,00,22,618 1,03,55,119

The table looks like below -

Month	Sessions	View Example Cards	Design Custom Card	View Pricing Page	Revenue
March	1695292	17,758	15,052	25,964	$         1,14,43,801
April	1670136	20,146	15,008	19,231	$         1,14,73,438
May	1868864	22,857	18,408	9,704	$         1,40,37,828
Jun	1727159	17,394	17,236	13,877	$         1,32,35,195
July	1781272	17,926	13,523	7,136	$         1,04,65,325
August	1956408	24,360	14,135	14,721	$         1,06,41,739
September	1930809	20,046	13,016	17,226	$         1,00,93,305
October	2367562	26,298	14,223	19,816	$         1,09,33,293
November	2568950	30,039	15,517	19,671	$         1,18,72,900
December	2741061	28,246	14,284	7,613	$         1,07,13,635
January	2485498	27,426	15,846	12,973	$         1,17,19,280
February	2202975	24,113	13,149	9,586	$         1,00,22,618
March	2593533	27,960	13,950	14,277	$         1,03,55,119

B. Then we start plotting the actions with revenue by following - select the column say view example cards (D) and then press control and then select the column Revenue (G). Then go to Insert and under the chart select X-Y scatter to plot the below-

$1,60,00,000 $1,40,00,000 $1,20,00,000 $1,00,00,000 Revenue $80,00,000 $60,00,000 $40,00,000 $20,00,000 . 5,000 30,000 35,000 10,000 15,000 20,000 25,000 View Example Cards

Repeat for other two actions.1. Design Custom card

$1,60,00,000 $1,40,00,000 $1,20,00,000 $1,00,00,000 Revenue $80,00,000 $60,00,000 $40,00,000 $20,00,000 2,000 4,000 6,000 14,000 16,000 18,000 20,000 8,000 10,000 12,000 Design Custom Card

2. View Pricing Page

$1,60,00,000 $1,40,00,000 $1,20,00,000 $1,00,00,000 Revenue $80,00,000 $60,00,000 $40,00,000 $20,00,000 5,000 10,000 20,000 25,000 30,000 15,000 View pricing Page

Inference: It seems that view example cards and view pricing page are not related / weakly related to revenue and Design Custom Card is strongly,positively and linearly related to Revenue (as the Design Custom Card value increases the revenue increases accordingly).

Step 2 : Now we calculate the correlation of the 3 actions with the revenue by using the formulas below and typing them in cells D19, E19 and F19.

		View Example Cards	Design Custom Card	View Pricing Page
Correlation with Revenue		=CORREL(D4:D16,$G$4:$G$16)	=CORREL(E4:E16,$G$4:$G$16)	=CORREL(F4:F16,$G$4:$G$16)

		View Example Cards	Design Custom Card	View Pricing Page
Correlation with Revenue		-0.158795	0.990788	0.021484

On formatting, The results look like below where green shows highest correlation. (values are formatted as %)

		View Example Cards	Design Custom Card	View Pricing Page
Correlation with Revenue		-15.87948%	99.07878%	2.14841%

The above correlation table shows that Design Custom card influences the Revenue to the maximum extent in a positive manner.

Thus it can be concluded that the focus of redesign should be The Design Custom Card page which would most positively impact the revenue.