Travel Agent Commission Tracking: Match Supplier Payments With AI

Record the expected commission for every booking in one sheet on the day you sell it. When a supplier statement arrives, paste it into an AI assistant and ask it to match each line to a booking, classify every difference and list what is overdue. You approve every match; the AI does the slow cross-checking.

The method only works if the sheet holds the right expected amount first. Commission arrives weeks or months after you sold the trip, in a different layout from every supplier, and often for slightly less than you expected. AI is a good matcher and a poor source of truth, so you supply the rates and it never guesses them.

Follow me on Instagram@sagnikteaches

Why commission goes missing between the sale and the statement

Commission is the one income line a travel agent earns months before it arrives. The client pays, travels and comes home, and only then does a supplier's finance team release your share, usually as one statement covering many bookings. Trade coverage has described a common pattern: hotels pay after the guest has checked out, tour operators after the client has travelled (a few after final payment), and cruise lines vary, with some paying at final payment and taking the money back if the booking is cancelled. Schedules differ by supplier and change over time, so read the table below as a list of things to look up, not as rules.

Connect on LinkedInSagnik Bhattacharya

That delay produces three kinds of loss. A booking can be sold but never expected, because it never reached the sheet. It can be expected but paid short, because the supplier calculated on a different amount or at a different rate. Or it can be paid and not recognised: a line nobody matched, a duplicate, or a recall that quietly cancels an earlier payment. A sheet with an expected figure for every booking turns each of the three into a visible difference, and the AI takes over the dull part, which is comparing hundreds of lines.

Subscribe on YouTube@sagnikteaches

The booking sheet: columns that make matching possible

You need one tab for bookings, one for the statement lines (after the AI has tidied them), and a small tab of supplier terms. The bookings tab is the one that earns its keep, because every difference is measured against its expected column. These are the columns I would start with.

Check what each supplier counts as the commissionable amount before you fill the column. Many exclude taxes, port charges and resort fees, and a gap between your gross sale figure and their base is a frequent reason for a payment that comes in a few dollars light. Put the answer in the supplier terms tab, next to the rate and the number of days after travel that the supplier normally pays.

The formulas below work in both Excel and Google Sheets. They assume these columns, which follow the table above and add variance and days overdue: A our ref, B supplier ref, C supplier, D lead surname, E travel start, F travel end, G commissionable amount, H rate, I expected, J pay-by, K received, L date received, M variance, N status, O days overdue.

Expected commission (I2):  =ROUND(G2*H2,2)
Pay-by date (J2):          =F2+INDEX(Suppliers!$C:$C,MATCH(C2,Suppliers!$A:$A,0))
Received (K2):             =SUMIFS(Statement!$H:$H,Statement!$K:$K,$A2)
Variance (M2):             =K2-I2
Status (N2):               =IF(K2=0,IF(TODAY()<=J2,"Not yet due","Overdue"),IF(ABS(K2-I2)<=0.01,"Matched",IF(K2<I2,"Short","Over")))
Days overdue (O2):         =IF(N2="Overdue",TODAY()-J2,0)

If a supplier pays on final payment rather than after travel, add a trigger-date column and point the pay-by formula at it. The Statement tab used by the SUMIFS formula holds the tidied lines from the next steps: the ten columns the assistant produces (the amount lands in column H) plus an eleventh, column K, where you record the matched booking reference.

A worked month: four statements, 13 lines, 12 bookings

Here is an illustrative month for a small agency. Four suppliers send statements: a hotel group as a CSV file, a tour operator as a PDF, a cruise line as a portal export and a transfers supplier as a table in an email. Together they carry 13 lines. The sheet lists 12 bookings that have finished travelling, and one of them (H-1044, expected $210.00) checked out 12 days ago, so it is not yet due. All figures are invented for the example.

Two statement lines match no booking in this month's list. One is a $45.00 line labelled as an adjustment by the transfers supplier. The other is a negative $288.00 line from the cruise line, a recall for a sailing that was cancelled after an earlier payment. The expected commission for the 11 due bookings is $2,937.90, the amount received against them is $2,489.90, and the apparent shortfall is $448.00. The four statements add up to $2,246.90, which is the $2,489.90 matched to bookings, plus the $45.00 unmatched line, minus the $288.00 recall.

The apparent shortfall hides what is really going on. Take the $492.00 unpaid, the $104.00 rate difference and the $8.00 base difference, which together make $604.00, and subtract the $156.00 duplicate that should not be counted as income, and you arrive at the $448.00. Of that money, $596.00 is a candidate for a claim, $8.00 is probably your own sheet being wrong, and $156.00 is a payment you may have to give back. Finding all this by eye might take 90 minutes; with the AI doing the first pass and you reviewing the exceptions, 25 minutes is realistic. Those times are an illustration, not a promise, but the saving comes from the same place every time: the assistant reads every line, and you only read the exceptions.

Step one: turn four statement layouts into one table

Every supplier lays out its statement differently, so the first job is to get all the lines into one set of columns. This is where an assistant saves the most time and also where it makes its quietest mistakes, so the prompt below forbids guessing and asks for a line count and a total at the end. Use it for CSV and spreadsheet statements as well as for text copied from PDFs. If you want a wider grounding in tidying inputs before analysis, the tutorial on cleaning messy data with AI covers the general habits.

You are helping a small travel agency reconcile commission. Below is one supplier statement exactly as received. Convert it to a table with these columns and nothing else:
supplier | statement_date | supplier_ref | our_ref | name3 | travel_start | travel_end | amount | currency | line_type

Rules:
- Do not guess. If a value is missing or unclear, write UNCLEAR in that cell.
- Amounts in brackets or with a minus sign are negative and are recalls.
- This statement uses day/month/year dates. Output every date as YYYY-MM-DD.
- name3 is the first three letters of the lead surname, in capitals.
- line_type is payment, recall or adjustment.
- One output row per statement line. After the table print: lines in, lines out, and the sum of the amount column.

STATEMENT:
[paste the statement text here]

Here is an illustrative fragment of what a cruise line's portal export might look like, and what the assistant should give back. The statement prints the cruise line's own booking numbers, not your references, which is why our_ref comes back UNCLEAR and why the Supplier ref column in your sheet is what makes the match possible.

CRUISE LINE C   COMMISSION STATEMENT   28/10/26
Booking   Guest     Sail from   Sail to    Fare       Rate   Commission
7731420   HAR/M     12/09/26    22/09/26   2,640.00   12%    316.80
SAILING CANCELLED - RECALL
7729815   LIN/P     05/08/26    15/08/26   2,400.00   12%    (288.00)

OUTPUT (illustrative):
supplier | statement_date | supplier_ref | our_ref | name3 | travel_start | travel_end | amount | currency | line_type
Cruise line C | 2026-10-28 | 7731420 | UNCLEAR | HAR | 2026-09-12 | 2026-09-22 | 316.80 | USD | payment
Cruise line C | 2026-10-28 | 7729815 | UNCLEAR | LIN | 2026-08-05 | 2026-08-15 | -288.00 | USD | recall
Lines in: 2. Lines out: 2. Sum of amount: 28.80

Four things in that output deserve a second look every time. The bracketed figure must come back negative; a common failure is a positive 288.00, which turns a recall into income. The dates must follow the order you told it, because an assistant left to guess may read 12/09/26 as 9 December and move a pay-by date by months. The currency should be stated, not assumed, since a few suppliers pay in a different currency from the one you sold in. And the footer must reconcile: add the amount column yourself, or with a spreadsheet SUM, and compare it with the statement's own total. If the two disagree, a line has been dropped, merged or misread.

Step two: match in order of trust, and show the reason

Matching works best when you tell the assistant which evidence to trust first, in the same order a careful clerk would use. A reference that equals a booking is the strongest signal, followed by supplier plus dates plus a near-identical surname, and a bare amount is the weakest. Ask it to label every match with its strength and a short reason, so you can read the doubtful ones and skip the rest.

Match each statement line to a booking in the sheet below. Use these rules in this order:
1. Exact: our_ref or supplier_ref equals a booking's reference.
2. Likely: supplier and travel_start agree, and name3 agrees or differs by one letter.
3. Possible: supplier agrees and the amount equals the expected commission within $0.50, but nothing else agrees.
4. None: anything else.
Never match one booking to two lines unless the statement shows two lines for it. Do not invent or adjust any figure.
For each line return: line number, booking ref (or NONE), match type, reason in under 15 words.
Then print: lines, matched, unmatched, sum of matched amounts, sum of unmatched amounts.

BOOKINGS (our_ref, supplier_ref, supplier, name3, start, end, expected):
[paste]
STATEMENT LINES:
[paste the table from step one]

The sample output below is illustrative and shows only the lines worth reading. Notice that the assistant puts a duplicate and a recall in the answer instead of quietly ignoring them, which is exactly what the "never match one booking to two lines" rule is for.

Accept the exact matches in bulk, read every "Likely" and "Possible" line, and write the matched booking reference into column K of the Statement tab yourself, or paste the assistant's answer there. The SUMIFS formula then fills the Received column. The same discipline of tolerance, tie-out and exception review sits behind matching supplier invoices to purchase orders, and the habit of checking a bank deposit against the sum of lines comes from bank reconciliation with AI. Run one last test before you move on: the lines matched, the lines unmatched and any recalls must add up to the deposit that reached the bank. In the worked month that is $2,489.90 + $45.00 - $288.00 = $2,246.90.

Step three: sort every difference into one of six causes

A flagged difference is only useful once you know what to do about it. The six causes below cover almost everything a statement throws up, and each one points to a different person or a different fix. Ask the assistant to label every exception with one of them, then check the labels yourself, because the decision in two of the six rows depends on a contract the assistant has not read.

Cancellations are where recalls come from, so the same prompts that handle refunds and cancelled bookings in ChatGPT prompts for delays, cancellations and refunds should end with one more step: check whether the supplier will take commission back, and mark the booking in the sheet. A recall for a booking you paid commission on months ago is a surprise only if the sheet never recorded that the commission could be recalled.

The overdue chase list and the email that goes with it

Overdue items are the part of the sheet that turns into money, so give them rules before you give them emails. The ageing bands below are a starting point; adjust them to each supplier's terms and your own relationship with their commission team.

Your chase list is then a filtered view of the bookings tab: every row whose status is Overdue, with days overdue, the expected amount, the date and wording of your last contact and the next action. An illustrative list for the worked month might hold C-3311 at 22 days overdue and $492.00 expected, plus an older line carried over from a previous month: a $64.00 hotel commission, 41 days overdue, that has already had one query. The first sits in the second band and the older one in the third.

Let the assistant draft each email from a list row, with one rule: it copies figures and never calculates or rounds them. The wording below is a filled-in example for C-3311 with the usual placeholders for names and references.

Subject: Commission query: booking C-3311 / [supplier ref]

Hello [first name],

I'm writing about commission that doesn't appear on your latest statement.

Booking: C-3311 (supplier ref [supplier ref])
Travel: sailing ended [date]
Commissionable amount: $4,100.00
Rate in our agreement: 12%
Commission expected: $492.00
Payable by: [date], 30 days after sailing ends

Could you confirm whether this has been processed and, if so, the payment date and remittance reference? If it is on hold, please tell me why and what you need from me.

Thanks,
[your name]
[agency name]

Keep the tone factual, as in the example. For a wider set of wordings that sound like a person and not a template, chasing late payments with AI reminders that sound human has the same approach for customer invoices. Whatever the draft says, read the numbers against the sheet before sending, and log the send date in a Last chased column (P) so the second query goes out on time and can quote the first.

Five checks to run before you accept an AI match

Most matching errors come from a short list of causes, and each has a quick check. Run the five checks below before you accept any output; they take about two minutes and catch the errors that cost money.

1. Line counts agree. Lines in must equal lines out. Long statements are where assistants quietly drop a row, so split anything beyond a few dozen lines into chunks and check each chunk.
2. Totals agree. The sum of the amount column must equal the statement's own total, or the deposit in the bank, to the cent.
3. No booking is matched twice unless the statement itself has two lines for it. A family with two bookings on the same dates and a common surname is where an assistant picks the wrong one.
4. No rate or amount appears that you did not supply. When a statement omits the rate, some assistants helpfully fill in a typical one. Ask for UNCLEAR and fill the gap from your agreement.
5. Subtotals and headings are not treated as bookings. A "Total for hotel group" line read as a payment doubles that supplier's income on paper.

Two further habits help. Keep the original statement file untouched in a folder named by supplier and month, so you can always return to the source. And keep a short error log of each mismatch the assistant produced, with the fix. After a few months the log tells you which supplier layouts need an extra instruction in the prompt.

What to paste into the assistant and what to keep out

Commission statements are less sensitive than passenger records, but they still carry personal data and commercial terms. Cut the name column down to the first three letters of the surname before pasting, or delete it if your matching relies on references. Leave out card numbers, passport details, dates of birth and any medical or accessibility notes. Use a business plan, or the model-training switch in a personal plan's privacy settings, so the chats are not used to train models, and check your supplier agreements in case they treat commission rates as confidential. If a supplier statement arrives as a scanned image rather than text, the assistant's reading of it is least reliable; check the line count and total even more carefully.

A month-end summary the AI can draft in one pass

Once matches are in the sheet, a last prompt can summarise the month by supplier. Paste the bookings tab (without names) and ask for a table of expected, received and difference for each supplier, a list of the exceptions with their causes, and the three actions that matter most. Check every figure against the sheet's own totals, because a summary is where an assistant is most tempted to round. For the worked month the supplier table should read like this.

The summary should end with actions in order of value: chase C-3311 for $492.00, read the tour operator agreement for the $104.00, set the hotel group's base rule in the sheet, hold the duplicate $156.00 aside, trace the $45.00 line and record the recall. A short routine keeps this manageable: match each statement on arrival, refresh the chase list weekly and run the supplier summary at month end. Brokers in other industries run the same loop; reconciling insurer commission statements follows almost the same steps.

When a commission platform earns its fee instead

Dedicated software does this job natively, and the ranking pages for this topic are mostly its vendors. Travel CRMs such as Travefy record commissions alongside client records and invoices. Specialist tools go further: Sion's site describes commission reconciliation that auto-matches incoming payments and leaves only the ones needing attention for you to review, and its announcements describe a commission-chasing service run with a partner firm. Check the current features and prices in a demo before you decide, because both change.

My own rule of thumb is to stay with the sheet and the assistant while reconciliation takes under a working day a month and one person does it. Look at a platform when more than one adviser needs a commission split calculated, when the same supplier layouts repeat so often that rules can be automated, or when chasing takes more time than matching. Whatever you choose, ask how to export your bookings and commission history, and what happens to your data if the product changes hands. And run the new tool alongside your sheet for one month: where the platform and your AI-assisted sheet disagree, one of them has found something worth knowing.

Questions travel agents ask about matching commission

How often should a travel agent reconcile commission?

Match each statement on the day it arrives, then review the whole sheet once a month. Statement-by-statement matching keeps differences small enough to explain, and the monthly review catches bookings that never appeared on any statement. Some agreements set a deadline for commission queries, so read yours and diarise it.

What if my host agency pays me instead of the supplier?

Treat the host agency's commission report as the statement. Your sheet still holds the expected amount, which is the supplier rate less your host's split, and the matching steps are identical. Ask the host for line-level reports with booking references, because a summary total cannot be matched to anything, and note when the host pays out.

Can AI send the chase emails to suppliers for me?

It can draft them, but a person should press send. A chase email makes a claim about money, and the draft will quote a wrong amount or reference if the sheet is wrong. A safer first automation is a reminder to yourself that lists the chase emails due today, with the drafts waiting for your review.

Is it safe to paste supplier statements into an AI assistant?

Paste the least you can. Statements usually carry traveller names, so cut the name column down to the first three letters of the surname, and leave out card details and passport data entirely. Use a business plan or the privacy switch that stops chats being used for model training, and check your supplier agreements for confidentiality terms on commission rates.

What counts as an overdue commission?

Overdue means past the date in the supplier's own terms plus any grace you choose, not past the date you hoped for. Keep each supplier's payment trigger and number of days in a small table and calculate the pay-by date from the travel end date. If the terms are not written down, ask the supplier or host to confirm them in writing.

Further reads

• Travel Proposal Template: Build a Client-Ready Trip PDF With AI — Build the client-ready proposal that starts the booking this sheet tracks
• Travel Agent Follow-Up Emails: Chase Silent Quotes With AI — Chase clients who go quiet after a quote, not suppliers
• Travel Agent Checklist: Pre-Departure Document Packs With AI — Reuse the same booking data for pre-departure packs
• What AI Costs a Small Travel Agency and What It Saves — See what AI costs a small agency against the time it saves
• Best AI Tools for Travel Agents: Itineraries, Emails and Research — Compare assistants before choosing one for the matching
• Travel Agency CRM: What a Small Agency Needs and Where AI Helps — The small-agency minimum for a travel agency CRM: lead stages, trip records and reminders, with AI used for notes and email drafts.
• AI for Travel Companies: Track Supplier Payment and Release Deadlines — Turn supplier contracts into a deadline sheet that flags deposits, release dates and attrition early, with every date checked by a person before it counts.
• ChatGPT Prompts for Travel Agents: 10 for Quotes and Follow-Ups — Ten prompts for briefs, quotes and follow-ups, each built around your supplier figures, with worked output and the fact-checks to run first.
• ChatGPT for Travel Agents: Group Trip Info Packs and Departure Emails — An agency owner's pipeline: supplier documents to a facts table, a sourced group pack, a liability check and eight pre-departure emails, with no visa advice.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Sion's commission reconciliation page and announcements, and Travefy's product pages for its CRM features (checked October 2026); trade press coverage of supplier commission payment timing, which varies by supplier and must be confirmed in each agreement.