Blog · Board and management reporting
Build an airline customer dashboard from booking records, refund entries, and customer history. This worked example separates repeat customers from booking frequency and reconciles customer revenue to its source rows.
An airline customer dashboard shows who booked, who had booked before, how much fare remained after refunds, and which customer groups contributed that revenue. Build those measures from booking and customer-history exports using a fixed period and a stable customer identifier. The useful dashboard keeps customers, bookings, passengers, and dollars in separate measures.
Consider a fictional US regional airline reviewing corporate travel accounts for September 2026. Its reporting population is bookings with completed travel in September, extracted on October 23, 2026. A booking is one commercial booking record, even when it includes several passengers or flight segments.
The account identifier represents the purchasing company. It does not identify an individual traveler. This matters because a travel manager buying for several employees should not appear as several corporate customers, while unrelated companies using the same agency should not automatically become one account.
Use a booking export containing booking ID, customer ID, travel completion date, fare after discounts, and refund amount attributed to that booking. Exclude separately collected taxes from both fare and refunds. Consolidate ticket and refund lines to one booking row before counting anything. A customer-history export supplies qualifying completed bookings before September 1, 2026.
For this example, a qualifying booking has completed travel and positive retained fare. Every booking below qualifies. These are management customer-revenue figures; finance must reconcile their scope to the ticket revenue ledger, including any accounting adjustments outside this table.
A general customer dashboard can connect this view to wider account measures. Keep airline definitions visible alongside the figures so readers know exactly what the population includes.
Put the following table in a sheet named Bookings, with headers in row 1 and data in rows 2 through 10. Fare and refund are positive USD amounts; retained fare subtracts the refund once.
| Booking ID | Customer ID | Fare (USD) | Refund (USD) | Retained fare (USD) |
|---|---|---|---|---|
| B01 | A | 9,000 | 1,000 | 8,000 |
| B02 | A | 9,000 | 1,000 | 8,000 |
| B03 | B | 12,000 | 0 | 12,000 |
| B04 | C | 7,000 | 1,000 | 6,000 |
| B05 | C | 8,000 | 2,000 | 6,000 |
| B06 | D | 9,000 | 0 | 9,000 |
| B07 | E | 11,000 | 2,000 | 9,000 |
| B08 | E | 13,000 | 2,000 | 11,000 |
| B09 | F | 12,000 | 0 | 12,000 |
| Total | 90,000 | 9,000 | 81,000 |
In Google Sheets, retained fare in E2 is:
=C2-D2
Fill down. The first proof is an identity: 90,000 - 9,000 = 81,000. The second is a source check: each booking ID must appear once in the consolidated table, and the refund register must explain the 9,000 assigned to these bookings at the extraction cutoff.
Do not interpret the refund amount as a cancellation count. A partial refund and a fully canceled booking answer different questions. This dashboard measures refunded dollars as a share of the fare base.
Create a second sheet, Customers, with one row per current-period customer. Columns A through F are customer ID, prior qualifying bookings, current bookings, fare, refunds, and retained fare. Prior bookings come from the supplied history file; the other amounts come from the booking rows above.
| Customer ID | Prior bookings | Current bookings | Fare (USD) | Refunds (USD) | Retained fare (USD) |
|---|---|---|---|---|---|
| A | 2 | 2 | 18,000 | 2,000 | 16,000 |
| B | 0 | 1 | 12,000 | 0 | 12,000 |
| C | 1 | 2 | 15,000 | 3,000 | 12,000 |
| D | 0 | 1 | 9,000 | 0 | 9,000 |
| E | 3 | 2 | 24,000 | 4,000 | 20,000 |
| F | 1 | 1 | 12,000 | 0 | 12,000 |
| Total | 7 | 9 | 90,000 | 9,000 | 81,000 |
For customer A in row 2, calculate current bookings in C2 and retained fare in F2:
=COUNTIF(Bookings!$B$2:$B$10,A2)
=SUMIFS(Bookings!$E$2:$E$10,Bookings!$B$2:$B$10,A2)
Google documents COUNTIF as a conditional count and SUMIFS as a sum subject to criteria. Apply the same sum approach to fare and refunds, then fill all customer formulas down. Covirage is not affiliated with Google.
The dashboard measures now follow from the customer table:
| Measure | Calculation | Result |
|---|---|---|
| Active purchasing accounts | Count customer rows | 6 |
| Repeat purchasing accounts | A, C, E, F have prior bookings | 4 |
| Repeat-customer share | 4 / 6 | 66.7% |
| Accounts with multiple current bookings | A, C, E | 3 |
| Multiple-booking account share | 3 / 6 | 50.0% |
| Refunded fare share | 9,000 / 90,000 | 10.0% |
| Revenue from repeat accounts (USD) | 16,000 + 12,000 + 20,000 + 12,000 | 60,000 |
| Repeat-account revenue share | 60,000 / 81,000 | 74.1% |
Percentages are rounded to one decimal place after division. Use the unrounded amounts in later calculations. Repeat-customer share describes the composition of current business; it is not the return rate of a starting cohort. For that different denominator, see the worked retention example.
Suppose B04's attributed refund changes from $1,000 to $4,000 after a correction. Keep every other input, including customer history and booking qualification, unchanged.
| Measure | Original | Revised calculation | Revised |
|---|---|---|---|
| Total refunds (USD) | 9,000 | 9,000 + 3,000 | 12,000 |
| Retained fare (USD) | 81,000 | 90,000 - 12,000 | 78,000 |
| Refunded fare share | 10.0% | 12,000 / 90,000 | 13.3% |
| Repeat-account revenue (USD) | 60,000 | 60,000 - 3,000 | 57,000 |
| Repeat-account revenue share | 74.1% | 57,000 / 78,000 | 73.1% |
| Repeat-customer share | 66.7% | 4 / 6 | 66.7% |
The correction changes dollars, not customer status. Commercial operations can investigate the refund records while account management reviews repeat business. Any airline customer segmentation should preserve these definitions when splitting accounts into groups. A separate customer experience dashboard can compare supplied feedback and service outcomes without treating refunds alone as a satisfaction score.
Bring a booking export, attributed refund records, and the customer history used to define repeat business. Covirage maps the columns, and its tools compute and check the supplied booking, refund, and customer 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 data; account creation takes about a minute, followed by setting up your business.
See self-service analytics for the supporting business context.
Only if that matches your written definition. Two tickets on one booking can represent one purchase. For an established-customer measure, require a qualifying booking before the reporting period and a qualifying booking within it. Report within-period purchase frequency separately.
Use the date basis that answers the question. A travel-cohort view assigns refunds to the original booking cohort, with an extraction cutoff. A cash refund report uses payment dates. Keep both views labeled rather than combining their totals.
It can when a starting customer cohort and a later observation window are supplied. The share of current customers who booked previously is a repeat-customer mix measure. It does not show how many customers from a starting cohort returned.