79. SALES FUNNEL ANALYSIS

OBJECTIVE

Improve the conversion of prospective customers buy optimizing the sales funnel.

DESCRIPTION

Models that estimate demand usually focus on a specific aspect of the sales channel. For example, a pricing model usually defines the choice of customers in front of a set of possible products or services; however, it does not take into consideration the awareness of a product in the market (it usually assumes full awareness to simplify the analysis).

Ideally, these pricing models (see PRICING AND DEMAND models) have to be included in a broader framework called the sales funnel. A sales funnel consists of four main elements:

• -          Market: the total number of potential customers;
• -          Awareness: the people in the market who know the product;
• -      Consideration: the people who know the product and take it into consideration for purchasing;
• -          Conversion: of the people who consider the product, those who actually buy it.

These elements are related to each other, for example:

• -          70% of people in the market are aware of the product (market awareness);
• -          80% of people who are aware of the product consider purchasing it;
• -          10% of people considering it actually purchase it.

Sales Funnel

We can calculate that 56% of all potential customers know the product (70% * 80%) and that 5.6% buy the product (70% * 80% * 10%). We also can calculate that 8% of people who know the product will buy it (5.6% / 70%).

An increase in the number of people in the market will result in an increase in purchases, since (usually) we assume that the relationships or ratios remain the same: 70%, 80%, and 10%. Data on market changes can be derived from public population data. For example, if our potential clients are males with an annual income between €40,000 and €80,000 and this sector of the population increases, then our “market” increases.

Besides the market, we can act on the relationships between market awareness, awareness consideration, and consideration conversion to increase the sales.

Market Awareness

In our example 70% of people know our product, but we can increase this ratio by investing in advertising, public relations, special offers, and so on. The effect on sales of an increase in people’s awareness can be calculated using the other ratios, which we assume do not change (80% awareness consideration and 10% consideration conversion). However, we have to consider that this assumption may not be completely true and either use corrective measures or just be cautious in interpreting the results. In fact, the remaining 30% of the people in the market who do not know the product may have lower consideration and conversion ratios, for example because they are more loyal to a specific brand and they do not know us or because they are not interested in looking for an alternative product. In this case, even if we make them aware of our product, it is probable that the awareness consideration ratio of these people will be far lower than our 80%.

Awareness Consideration

Out of the people who know our product, some of them will not consider it for a purchase either because they are loyal to a brand or because they think that our quality and/or price levels are not acceptable. There are two main ways to improve consideration:

• -    If the price or quality are good but the low consideration is due to people having a worse perception than the reality, we should invest in communication;
• -   If people have a correct perception of the price and quality, we can improve them (higher quality and lower price).

Investing in communication will improve the number of aware people who consider our product, and we can calculate the increase in sales using the consideration conversion ratio of 10%. We assume that the conversion ratio does not change, but, as in the abovementioned example, this assumption may not be completely accurate.

If we improve the quality or lower the price, it is probable that the consideration conversion rate will also change, since the product is more attractive to those people who have already considered it.

Consideration Conversion

In this case price and quality are the main drivers, but here they are compared with other alternatives (this is a similar concept to the reference price vs maximum price explained in the introduction to PRICING AND DEMAND models). The effect of a price modification can be derived from several pricing and elasticity models.

Optimization

With the proposed model, we calculate the impact on volume, more specifically the number of additional customers, but we should include other elements to decide which strategy to pursue:

• -      Revenues: revenues depend on the average number of purchases and on the average price. Take into consideration that if we lower the price we may also have a positive effect on the number of purchases (depending on the product) but there will also be a negative effect since we are selling cheaper;
• -      Costs: the costs of advertising, communications, and quality improvements have to be included in the model.

Once we have defined the structure of the costs and revenues associated with the model, we can use Excel Solver to find the optimal investment plan (see 80. OPTIMIZATION).

TEMPLATE

Download the Sales Funnel Analysis Template

74. SCENARIO ANALYSIS

OBJECTIVE

Identify possible future scenarios.

BEST–WORST–AVERAGE CASE SCENARIO ANALYSIS

Simple scenario analysis can be managed in a spreadsheet into which we can insert data and functions that represent our business model. In the proposed example, the spreadsheet contains the figures for an investment in a business intelligence system for a hotel chain and the costs and returns associated with this investment. In the first sheet, the most likely scenario is presented, and with a simple what-if analysis we add a worst-case scenario and a best-case scenario.

First of all we define the main input variables that affect the outcome:

• -     Revenues: we project the expected additional revenues in future years thanks to the new BI system. The additional revenues are expected due to the better service personalization, better client service, and more effective marketing campaigns.
• -       Costs: we will incur additional costs associated with the BI system management.
• -       Cost of capital: to calculate the net present value, we discount the cash flows with the cost of capital for our company.

The second step is to define the number of scenarios. In our case we have three, but we can also opt for a multiple-scenario analysis including more intermediate scenarios (bear in mind that, if we increase the number of scenarios, on one side we will have more realistic results but on the other side the analysis will become more complicated).

Then we modify our input variables (revenues, costs, and cost of capital) by reasoning on the underlying factors that can change them. For example, a worse global economic situation negatively affects revenues, and some unexpected situations can increase the costs associated with the project or just our estimations of the positive effects of the BI system. After defining the main factors and the underlying assumptions, we modify the input variables with the maximum negative effect for the worst-case scenario and with the maximum positive effect for the best-case scenario.

We can use the Excel What-If Analysis tool to save different scenarios and to create a summary of them by clicking on “Data,” “What-If Analysis,” and “Scenario Manager.” We will see the three scenarios that I saved; if we click on one scenario and then “Show,” we will see the scenario. We can also add a new one by clicking “Add” (I suggest first changing the changing cells in the spreadsheet – green cells – and then adding a new scenario; otherwise, we will have to change the values in a less intuitive way in the scenario manager window). Once we have added a new scenario, we can click on “Summary” and choose the report type and result cells (by default we will see the cells of NPV of the scenarios that I have saved).

Scenario Summary

The figure above presents the result cells of the summary scenario (changing cells have been deleted). The current values are the same as the most likely scenario, since they are the value at the moment of running the summary. With the cumulated net present values in the next five years, we can now evaluate the risks and opportunities of this project.

OTHER TYPES OF SCENARIO ANALYSIS

We can also opt for a multivariate scenario analysis, by adding more intermediate scenarios. In this case a common practice is to assign a probability to each scenario to evaluate the risks and opportunities according to both the magnitude of the impact and the associated probability.

Another common technique is decision tree analysis, which tries to represent all the possible events with associated probabilities and impacts (see 75. SIMPLE DECISION TREES). A more precise but more complex technique is to perform simulations, which rely on input variable probability distributions to create a distribution of possible outcomes (or scenarios). The advantage of simulations is a more accurate analysis, since we can calculate statistical parameters with accuracy, such as the standard error, confidence intervals, and so on (see 77.MONTE CARLO SIMULATIONS).

More information concerning probabilistic scenario analysis is available in the following document:

http://people.stern.nyu.edu/adamodar/pdfiles/papers/probabilistic.pdf

TEMPLATE

Download the Scenario Analysis Template

59. BINARY CLASSIFICATION

OBJECTIVE

Classify the elements of a data set into two groups.

DESCRIPTION

In binary classification the goal is to classify the elements of a data set into two groups according to a more or less complex classification rule. The example proposed in the template concerns a company that wants to promote a very exclusive perfume by giving a free sample to some of its customers. The cost of providing a free sample is €50, but in the case of reaching the right customer, the expected return is worth €950 (€1000 minus the cost of the sample). To classify the clients, the company decides to use a score that is calculated from the number of purchases and the average amount spent by each customer (see 33.SCORING MODELS for more information about the creation of score indicators). Our classification rule is “the higher the score, the more likely it is that the free sample will work.”

The first step is to check whether the chosen classification rule is valid, that is to say how efficiently it allows us to classify the elements (customers). The ROC curve measures the efficiency of the classification, and it is the combination of:

• -          Sensitivity: TRUE POSITIVE RATIO = TP / (TP+FN)[1]
• -          1-specificity: FALSE POSITIVE RATIO = 1 - TN / (TN + FP)

The curve is obtained by ordering our sample of 20 clients from the highest to the lowest score and reporting the results of the experiment (1 = the client bought the product, 0 = the client did not buy the product; see Figure below).

Table for the Calculation of the ROC Curve

The ROC curve is the continuous line in the following graph (Figure below), while the dashed line is the theoretical ROC curve in a model in which the classification is random. Graphically, we understand that our model is more efficient than a random classification method, since the continuous line is above the dashed line. The area under the curve (AUC) is the measure of classification efficiency and represents the probability of a positive event being classified as positive. A random model (red line) has an AUC of 0.5, while a good model has an AUC higher than 0.7. Our model has an AUC of approximately 0.82, so we can conclude that our classification rule classifies customers efficiently.

ROC Curve

The next step is to find the optimal threshold, which is the minimum score that a client should have to receive a free sample. For that we have to assign the costs and gains of the four possible results of the classification:

• -        True positive: if we give a free sample to the right customer, we have a cost of €50 for the sample and a return of €1000, so we assign to the TP a revenue of €950;
• -         False positive: if we give the sample to the wrong customer, we have a cost of €50;
• -        True negative: we correctly establish that this customer will not buy in spite of the free sample, so we have neither costs nor revenues;
• -        False negative: we fail to identify a customer who would have bought the product, but still we have neither costs nor revenues.

When we define this matrix, we do not have to include either opportunity costs (the potential revenues lost in a false negative) or opportunity gains (the money saved from not sending a free sample to the wrong customer in a true negative). If we included them, we would double the costs and revenues. In our example the optimal threshold is a score of 65, which means that the company has to send a free sample to customers with at least this score.

This cost/revenue matrix can be used to establish the probability threshold in a logistic regression (see 60. LOGISTIC REGRESSION). Using the same example, we can perform a logistic regression with several predictor variables (number of purchases, amount spent, location, marital status, etc.) and calculate the probability of individual customers buying a product. On the basis of the cost of incentives and the gains from reaching the right customers, the probability threshold may be higher or lower than 0.5.

TEMPLATE

Download the Binary Classification Template

---

[1] T = true, F = false, P = positive, and N = negative.