excel-Funnel Chart Analysis (variance analysis)

The funnel chart is suitable for the business process comparison specification, the cycle is long, the link multi-process analysis, through the funnel each link business data comparison, can intuitively discover and explain the problem lies. In site analysis, typically used for conversion rate comparisons, it not only shows the final conversion rate of the user from entering the website to achieving the purchase, but also shows the conversion rate of each step, asshown in 9-91.

Figure 9-91 website Customer conversion rate using a funnel chart

Funnel charts not only provide users with conversions and churn rates in the business, but also reveal how popular the various businesses are in the site. Although a single funnel can not evaluate the conversion rate of each step in a critical process of a website, it is possible to discover problems in the site by comparing or comparing the funnel charts of different businesses and different customer groups.

drawing a funnel chart in Excel is accomplished with a stacked bar chart. Figure 9-92 is the customer conversion rate statistics for a site,column C is the number of customer visits,column B is the number of participants based on the number of visitors, the purpose is to center the number of bars display, the number of positions is calculated as "(first link visitors -current link visitors) /2 ".

Figure 9-92 conversion rate data for each link

• With the a1:c6 area selected, Click Bar in the diagram module of the Insert Ribbon, Select Stacked Bar, and then delete the grid line, as shown in effect 9-93.

Figure 9-93 Stacked bar chart inserted

• You can see that the bar for the number of data series is centered, but the funnel shape is reversed. Right-click the Vertical Axis tab, select the format Axis command, and in the Open dialog box, tick the Reverse category check box to see the funnel shape as a positive display. Also, the major tick mark type is selected as none to remove the scale display of the ordinate axis. as shown in effect 9-94.

Figure 9-94 Adjusting the funnel shape orientation and removing the vertical axis scale display

• Right-click the bar of the placeholder data series, select the Format data Series command, set the fill color to no fill in the Open dialog box, and hide the bar display for that data series, as shown in9-95.

Figure 9-95 hiding the bar display for the "placeholder data" data series

• In the layout ribbon of the Chart tool, select Axis? Primary horizontal axis "? "None", remove the horizontal axis,shown in 9-96.

Figure 9-96 Removing the horizontal axis

• Delete the legend portion of the chart. Right-click the bar of the "People" data series, select the "Format data series" command, set the fill color of the bar to "solid fill", to make the chart beautify,9-97.

Fig . 9-97 the bar fill color of landscaping funnel chart

• Click the Shape button in the Insert Ribbon, select the line shape, and draw the border of the funnel until you are finished. In addition, you can insert the DOWN arrow shape in the vertical axis label area and enter the link conversion rate in the arrows. as shown in effect 9-98.

Figure 9-98 drawing a funnel border

• Finally, right-click the bar of the "People" data series, select the "Add Data Label" command, and modify the label to the overall conversion rate, resulting in a funnel chart of Figure 9-91.

Transferred from: http://blog.csdn.net/zhanghongju/article/details/18901857

excel-Funnel Chart Analysis (variance analysis)