pricing analytics: segmenting customers to maximize revenue
TRANSCRIPT
PRICING ANALYTICS Segmenting Customers to Maximize Revenue
Customer Segmentation • Customers can be segmented into value groups in some
markets • High valuation groups are willing to pay more for a product or service
• Low valuation groups are only willing to spend less for the same product or service
Airline Customer Segmentation • Older method used Saturday night stay to separate leisure and
business passengers
• Modern methods use advance purchase time as a primary discriminator
• The later a passenger purchases a ticket, the more they’re willing to pay
Example: Malaysia Airlines Flight 19 • Maximize revenue on MH Flight 19 (Amsterdam to Kuala
Lumpur)
• Surveyed 2% of market (10 people) on their valuation of flight
$463 $732
$569 $701
$457 $746
$719 $607
$758 $685
Example: Malaysia Airlines Flight 19 • Valuations effectively the same as points on the demand curve
• Calculate demand by finding number of people who would buy tickets at each price point
$463 $732
$569 $701
$457 $746
$719 $607
$758 $685
$463 $732
$569 $701
$457 $746
$719 $607
$758 $685
Enter fare valuations from passenger survey
Enter demand summing formula: =COUNTIF($C$2:$C:$11,“>=”&C2)
Accept formula
Select handle at bottom right of formula cell, then drag down
Enter market adjustment formula: =A2*50
Accept formula
Select handle at bottom right of formula cell, then drag down
Select cells containing demand and price data
Choose “Scatter With Only Markers”
d = a - bp
Right-click one of the data points
Choose “Add Trendline…”
Select “Linear”
Check “Display Equation on Chart”
Click “Close”
a b
d = 838.9 – 0.7p
Enter guess for optimal single price
Enter demand curve formula: =838.9-0.7*E14
Accept formula
Enter total revenue formula: =E15*E14
Accept formula
Launch Excel’s Solver tool
Maximize
Revenue
Ticket Price
Choose GRG Nonlinear solving method
Click “Solve”
Click “OK”
Optimized ticket price: $599
Optimized revenue: $251,340
Yield Management • Goal: charge a set of customers more for the same product to
increase revenue
• Ideal (from the airline’s perspective): charge rich people and desperate people more money
• Doing that’s illegal (at least in US and EU) – discrimination
• Legal method: yield management
Yield Management • We need to find some variable that strongly correlates to how
much a customer is willing to pay
• Airline example: how early a passenger buys a ticket before a flight • Low-valuation customers (tourists, students, bargain hunters) buy tickets
long before a flight
• High-value customers (business travel, emergencies, HBS alumni) buy tickets last-minute
Modeling Yield Management • Incorporate two price points into our existing single-price
model
• High-value customer demand formula (same as single price):
High Value Demand = 838.9 – 0.7 x High Price
• Low-value customer demand formula:
Low Value Demand = (838.9 – 0.7 x Low Price) -
High Value Demand
Enter guess for low price point
Enter guess for high price point
Enter low price demand formula: =(838.9-0.7*B1)-B5
Accept formula
Enter high price demand formula: =838.9-0.7*B2
Accept formula
Enter low price revenue formula: =B4*B1
Accept formula
Enter high price revenue formula: =B5*B2
Accept formula
Enter revenue summing formula: =SUM(B7:B8)
Accept formula
Launch Excel’s Solver tool
Maximize
Total Revenue
Ticket Prices
Choose GRG Nonlinear solving method
Click “Solve”
Click “OK”
Optimized ticket price for low-value passengers: $399
Optimized revenue: $335,121
Optimized ticket price for high-value passengers: $799
33%
Increase
Capacity Constraints • Current model assumes an unlimited number of
seats on airplane
• Need to add a capacity constraint to accurately model pricing
• Aircraft (Boeing 777) holds 341 passengers
Enter total # passengers formula: =B4+B5
Accept formula
Restart Excel’s Solver tool
Click Add button
Total passengers
Less than/equal to
Aircraft capacity: 341
Click OK
Click Solve button
Click OK button
Optimized ticket price for low-value passengers: $711
Optimized ticket price for high-value passengers: $955