Excel-funnel chart analysis (Difference Analysis)

From the author's website data analysis: data-driven website management, optimization and operation: http://item.jd.com/11295690.html

The funnel chart is applicable to the Process Analysis with standardized business processes, long cycles, and multiple links. By comparing the business data in each link of the funnel chart, you can intuitively discover and describe the problem. In website analysis, it is usually used to compare the conversion rate. It not only shows the user's final conversion rate from entering the website to purchasing, but also shows the conversion rate of each step, as shown in 9-91.

Figure 9-91 use a funnel chart to show the customer's conversion rate

A funnel Chart not only provides the user's conversion rate and turnover rate in the business, but also reveals the popularity of various businesses on the website. Although a single funnel chart cannot evaluate the conversion rate of each step in a key process of a website, it compares the conversion rate between different business and customer groups, you can still find problems on your website.

Creating a funnel chart in Excel requires Stacked bar chart. Figure 9-92 shows the customer conversion rate statistics of a website. Column C shows the number of visitors, and column B shows the number of visitors based on the number of visitors, the calculation method for the number of digits is "(number of accessors in the first step-Number of accessors in the current step)/2 ".

Figure 9-92 conversion rate data of each link

• Select the A1: C6 area, click bar chart in the "chart" module of the "insert" functional area, select "stacked bar chart", and delete the grid lines, as shown in Figure 9-93.

Figure 9-93 Stacked bar chart inserted

• We can see that the bars of the number of people data series are displayed in the center, but the funnel shape is reversed. Right-click the vertical axis label and select the "set axis format" command. In the displayed dialog box, select the "Backward category" check box. The funnel is displayed in a positive direction. At the same time, select "NONE" as "Main dial type" to remove the scale display of the ordinate axis. The results are as follows.

Figure 9-94 adjust the funnel shape direction and remove the vertical axis scale display

• Right-click the "placeholder data" data series bar and select the "set data series format" command. In the displayed dialog box, set the fill color to "no fill ", hide the bar display of the data series, as shown in 9-95.

Figure 9-95 hide the bar display of the placeholder data series

• In the "layout" function area of the Chart Tool, select "axis"> "main horizontal axis"> "NONE" to remove the horizontal axis, as shown in 9-96.

Figure 9-96 remove the x-axis

• Delete the legend section of the chart. Right-click the "Number of people" data series bar and select the "set data series format" command. Set the fill color of the bar to "solid color fill" to beautify the chart, as shown in 9-97.

Figure 9-97 beautify the bar fill color of the funnel chart

• Click the shape button in the insert function area, select the line shape, and draw the funnel border until all is complete. In addition, you can insert an arrow in the vertical axis label area and input the conversion rate in the arrow. The result is 9-98.

Figure 9-98 draw a funnel border

• Finally, right-click the "Number of people" data series bar, select the "add data label" command, and change the label to the overall conversion rate. The funnel chart shown in Figure 9-91 is obtained.

Zookeeper