Airline customer analytics: retention and contribution

Airline customer analytics compares booking cohorts by completed travel, repeat journeys and contribution after defined costs. The useful question is whether customers with comparable time to travel return and contribute more, after cancellations and refunds have been handled consistently.

Start with prepared booking and finance exports. A commercial review needs settled customer measures, a documented period and a reconciliation to finance.

Set the cohort before counting travel

Choose an entry event, such as a traveler's first completed journey, and an observation window. This example counts the initial journey plus journeys completed within the following 90 days. Include only travelers whose full window has elapsed by the report cutoff; keep newer travelers in a separate pending cohort.

Use the customer key already approved in the prepared export. Do not count a booking contact as every passenger, or count separate flight segments as separate journeys. A connecting itinerary remains one journey under this example's definition. Document how rebookings and duplicate booking references were resolved before summarizing.

Assign the booking channel from the initial journey and keep that assignment fixed for the comparison. Otherwise, a traveler who later changes channels can appear in both groups. Broader customer journey analytics examples address the sequence of interactions; this analysis measures what happened within a fixed travel window.

Prepare the customer rows

Ask for a prepared traveler summary containing the cohort channel, completed journeys, canceled bookings, recognized net passenger revenue and included costs. Revenue here is already adjusted for the refunds and credits applicable to the analysis. It is not the gross cash value of reservations.

Finance should define the included costs: for example, consistently allocated travel costs plus attributable distribution and service costs. Include unrecovered cancellation costs where the policy requires them. Do not subtract a refunded fare again if the revenue field is already net of that refund.

The fictional US carrier below has one row per eligible traveler. Columns A through F are the exported inputs; G and H are calculated. All monetary columns are USD.

Keep the underlying booking, refund and cost references with the prepared export so finance can investigate a difference. The customer table is the review surface, not a replacement for those supporting records.

Calculate repeat travel and contribution

In the worksheet, put the first traveler on row 2. Enter these formulas in G2 and H2, then fill down through row 7:

=IF(C2>=2,1,0)
=E2-F2

A traveler repeats only after completing another journey. A canceled second booking does not make A03 a repeat traveler.

For a group summary, put Direct in J2 and Agency in J3. Count travelers in K2, repeat travelers in L2, repeat rate in M2 and contribution in N2:

=COUNTIFS($B$2:$B$7,J2)
=SUMIFS($G$2:$G$7,$B$2:$B$7,J2)
=IF(K2=0,"",L2/K2)
=SUMIFS($H$2:$H$7,$B$2:$B$7,J2)

Copy the summary formulas down. COUNTIFS counts rows meeting specified criteria, while SUMIFS sums a selected range for matching rows. Keep every criteria range aligned with its corresponding value range. Covirage is not affiliated with Google.

Percentages are rounded to one decimal place. The overall rate is 3 / 6 = 50.0%. Calculate it from total repeat travelers divided by total eligible travelers, even when group sizes happen to match as they do here.

Prove the totals before choosing an action

Contribution must reconcile in both directions: $2,750 minus $2,020 equals $730, and the channel contributions of $600 and $130 sum to $730. Check the revenue and cost totals against the same eligible booking population in the prepared finance export, not the carrier's entire period revenue.

The direct cohort returns more often and contributes more in this example. That supports a closer commercial review, not a conclusion that the booking channel caused the difference. Route mix, fare class and trip purpose can differ between cohorts. Define comparable customer groups before changing acquisition spending.

Use the customer-base denominator method when deciding which travelers belong in a comparison. For a recurring presentation of these measures, see the airline customer dashboard.

Where it goes wrong

• Unequal follow-up. Recent travelers have less opportunity to repeat. Exclude incomplete windows from the mature-cohort rate.
• Bookings substituted for journeys. Cancellations, rebookings and connecting segments can inflate travel counts unless the unit is explicit.
• Refunds deducted twice. A net revenue field must not be reduced by the same refund again in contribution.
• Costs changing by channel. Different allocation policies create a contribution gap even when underlying economics are unchanged.
• A small cohort treated as proof. The example demonstrates the arithmetic. Larger, comparable cohorts and operational context are needed before a spending decision.

Compare your own booking cohorts in Covirage

Bring a prepared booking summary with eligible traveler keys, completed journeys, cancellations, net revenue and included costs. Covirage maps the columns, and its tools compute and check the cohort comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See customer profitability for the supporting commercial analysis.