Airline customer segmentation from booking data

Airline customer segmentation groups travelers by observed bookings so commercial teams can compare frequency, route mix, and contribution. Start with completed travel tied to stable customer identifiers, then reconcile revenue before interpreting the groups. A frequent traveler label describes recorded activity; it does not establish whether someone travels for business or leisure.

Define the traveler and observation window

Use a fixed travel period, such as January 1 through June 30, 2026, for an illustrative US carrier. Count independent bookings associated with completed journeys in that window. Keep canceled purchases, refunds, exchanges, and unfinished travel identifiable so they do not create duplicate activity.

Decide whether the commercial unit is the traveler or the purchasing organization. Here it is the traveler. A corporate account comparison requires a separate relationship identifier, because the company buying travel and the passenger taking the flight are not interchangeable.

The general work of choosing customer attributes belongs in customer profiling and segmentation. This example focuses on reconstructing measures from booking and flown-travel records. Use stable anonymous identifiers in the working table rather than names or contact details.

Keep travelers without reliable identifiers in a separate unmatched category. Their revenue still belongs in the reconciliation, but assigning anonymous purchases to invented individual customers would distort frequency.

Prepare an account-level comparison table

Join each completed booking to its traveler and sum the associated net revenue once. Revenue includes the agreed fare and ancillary amounts after applicable refunds, excluding taxes and charges collected for others. Use that same boundary in the finance total.

Bring attributable costs from the carrier's approved cost file. This example assumes finance has assigned those values consistently. Contribution is revenue less these costs, before unallocated corporate overhead; it is not net profit.

The route classification is also supplied: short-route legs are those meeting the team's declared distance threshold. Keep the definition fixed. Do not infer distance from fare or use connecting flight legs as separate purchases.

Assign groups before comparing outcomes

Place the table in A1:F7. These illustrative groups use booking frequency: frequent means at least four bookings, occasional repeat means two or three, and single booking means one. In G2:

=IF(B2>=4,"Frequent",IF(B2>=2,"Occasional repeat","Single booking"))

Contribution in H2 is:

=E2-F2

Fill both formulas through row 7. This is behavioral segmentation: the labels come from recorded purchases. For a broader ranking across recency and spend, use RFM analysis for B2B customers, while preserving airline-specific booking and leg definitions.

Calculate the segment comparison

Enter group names in J2:J4. In K2 count travelers, L2 sum revenue, M2 sum contribution, N2 sum legs, and O2 sum short-route legs:

=COUNTIF($G$2:$G$7,J2)
=SUMIFS($E$2:$E$7,$G$2:$G$7,J2)
=SUMIFS($H$2:$H$7,$G$2:$G$7,J2)
=SUMIFS($C$2:$C$7,$G$2:$G$7,J2)
=SUMIFS($D$2:$D$7,$G$2:$G$7,J2)

The formulas use the conditional summation documented for Excel's SUMIFS function and Google Sheets SUMIFS. Covirage is not affiliated with Microsoft or Google.

Calculate contribution margin with =IF(L2=0,"No revenue",M2/L2) and short-route share with =IF(N2=0,"No legs",O2/N2).

Percentages are rounded to one decimal place. The total contribution margin is 5,120 / 13,800, using summed dollars rather than averaging the group percentages.

Reconcile the result and choose an action

The group revenue adds to $13,800. Costs add to $8,680, so contribution must equal 13,800 - 8,680 = $5,120. Traveler counts sum to six, and route legs reconcile to 34, including 20 short-route legs. Resolve any unmatched travelers before treating this as a company-wide comparison.

Four of the six travelers made more than one booking, giving observed repeat-booker share of 66.7%:

=COUNTIF(B2:B7,">=2")/COUNTA(A2:A7)

That is activity within the observation window, not a forward retention forecast. Compare equal exposure periods before using it for retention decisions. Customer journey segmentation addresses where travelers are in a relationship rather than how often they purchased.

Frequent travelers produce more contribution dollars here, but their short-route share matches occasional repeat travelers. Route mix alone does not explain the revenue difference. Commercial teams can examine purchase history and offer relevance; finance should approve any contribution interpretation before a service change.

Where it goes wrong

• Legs treated as bookings. Connections inflate purchase frequency without another buying decision.
• Advance sales mixed with flown revenue. A payment-date comparison and a travel-date comparison cover different activity.
• Unmatched customers discarded. Removing their revenue makes the segmented export appear complete when it is not.
• Revenue labeled profitability. Fare and ancillary receipts require attributable costs before a contribution comparison.
• Travel purpose guessed from behavior. A weekday departure or higher fare is not a declared business trip.

Compare your booking groups in Covirage

Bring a booking export, traveler identifiers, and your approved revenue and attributable-cost fields. Covirage maps the columns, and its tools compute and check segment comparisons; the external AI model explains them with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See sales insights for the supporting commercial context.