Vacation Rental Analytics: Occupancy and Rate Reports With AI

A vacation rental host builds both reports from a booking export: occupancy is nights booked divided by nights available, and ADR is room revenue divided by nights sold. Calculate them in spreadsheet formulas, then give the results to ChatGPT, Claude or Gemini to explain what changed and suggest decisions. Never let the chat do the arithmetic unchecked.

Two things spoil most first reports: stays that cross a month end, and cleaning fees mixed into the nightly rate. Both are fixed in the sheet before any AI sees a number. A third trap is subtler. Hosts compare this month with last month, when the fair comparison is the same month a year ago, or at least a similar run of weeks.

Follow me on Instagram@sagnikteaches

Occupancy, ADR and RevPAR: three numbers and their traps

These come from hotel reporting and fit short-term rentals well, because a rental sells the same thing a hotel does: a night that cannot be resold once it has passed. Each answers a different question, and together they stop you celebrating a higher price that quietly emptied the calendar.

Connect on LinkedInSagnik Bhattacharya

Decide your definitions once and write them at the top of the sheet. "Nights available" should exclude nights you blocked for owner stays and repairs, because you could not have sold them. "Room revenue" should be the nightly charge only: leave out the cleaning fee, guest-paid taxes and the platform's guest service fee. Cancelled bookings drop out of both occupancy and ADR, though you may want a separate cancellation count.

Subscribe on YouTube@sagnikteaches

The platforms use their own definitions, which is a reason to build your own report. Airbnb's help pages, which describe the Earnings dashboard figures shown to hosts using its professional hosting tools, define occupancy rate as nights booked divided by total available nights, but its "average nightly revenue" includes fees you charge, before Airbnb's service fee and taxes. That is not room-only ADR. If you charge a cleaning fee, the dashboard figure will read higher than yours.

Export the bookings and tidy the columns

Start with whatever record lists every reservation, one row per stay: your property-management system, your channel manager, or each platform's export. On Airbnb, the Earnings dashboard lets you go to Upcoming or Paid, filter by listing and date range, choose Get report, pick the data fields and download a CSV. Airbnb's help page says that file includes earnings, host service fees, the cleaning fee if you charge one, gross earnings and any taxes withheld. Fields and menu names change, so tick every date, night-count and listing field you are offered, and check that the file really holds check-in and check-out dates. If an export repeats a booking across several payment rows, collapse it with a pivot table on the confirmation code so that each stay is one row. Other channels and your direct bookings have their own downloads, usually under reservations or reports.

Build one sheet with these columns:

• Listing and Channel (Airbnb, Booking.com, direct), so you can split results later.
• Confirmation code, the key you will use instead of guest names.
• Booked on, if the export has it, for lead-time analysis.
• Check-in and Check-out, as real dates, not text.
• Status: Confirmed or Cancelled.
• Room revenue and Cleaning fee as separate numbers, plus the platform fee if you want net figures.

Delete guest names, emails and phone numbers before the file goes near an AI tool. A report does not need them.

Raw exports are messy, and a chat assistant is a decent tidy-up tool if you stop it guessing. This prompt separates what it is sure of from what it is not:

Below is a raw booking export (guest details already removed). Return a table with these columns only: Listing, Code, Check-in (YYYY-MM-DD), Check-out (YYYY-MM-DD), Nights, Status (Confirmed or Cancelled), Room revenue (a number, no currency symbol, excluding cleaning fee and taxes), Cleaning fee (a number), Channel.

Rules: do not guess. If a date could be read two ways, or a revenue figure might include a fee you cannot separate, leave the row out of the table and list it under CHECK with the reason. Do not change any number.

[paste rows]

BEFORE (two raw lines)
Garden flat,Confirmed,HMABC12345,09/28/2026,10/03/2026,5 nights,"$725.00 (incl. $85 cleaning)",Airbnb
Studio,Cancelled by guest,HMDEF67890,05/06/2026,05/09/2026,3,$360,Direct

AFTER (illustrative output)
Garden flat | HMABC12345 | 2026-09-28 | 2026-10-03 | 5 | Confirmed | 640.00 | 85.00 | Airbnb

CHECK
HMDEF67890: 05/06/2026 could be 5 June or 6 May. Other rows use month-first dates, which would make it 6 May, but please confirm. Status also reads "Cancelled by guest".

That CHECK list is the value of the prompt. A careless first version of this output (illustrative) guessed 5 June and kept the row; the "do not guess" rule is what moved it to CHECK. In the "before" sample, the first row hid an $85 cleaning fee inside a text field, so the room revenue is $640, not $725. If the cleaning fee stays in, ADR for that stay reads $145 instead of $128, and a whole month of that drift looks like a price rise you never made.

Formulas that split a stay across two months

A guest who checks in on 28 September and leaves on 3 October stays five nights: three of them belong to September and two to October. A report that books the whole stay to the check-in month overstates September and understates October, and it does so differently every month. The fix is a small formula on every row of the booking sheet. Put the first day of the report month in one cell (L1 below), then add two helper columns.

Nights in the month   (column H):
=MAX(0, MIN(D2, EOMONTH($L$1,0)+1) - MAX(C2, $L$1))

Room revenue in the month   (column I):
=IF(E2=0, 0, G2/E2*H2)

C = check-in, D = check-out, E = nights (=D2-C2), G = room revenue for the whole stay.

Take the $640 garden-flat stay from the tidy-up example ($640 / 5 nights = $128 a night). With L1 set to 1 September, its helper columns give 3 nights and $384 of room revenue; set L1 to 1 October and they give 2 nights and $256. The summary sheet then needs only four lines per listing: nights available (days in the month minus blocked nights), nights booked, room revenue and the three measures. Use SUMIFS on the helper columns with the Status column set to "Confirmed".

Nights booked:   =SUMIFS(Bookings!H:H, Bookings!A:A, A2, Bookings!F:F, "Confirmed")
Room revenue:    =SUMIFS(Bookings!I:I, Bookings!A:A, A2, Bookings!F:F, "Confirmed")
Occupancy:       =IFERROR(booked/available, 0)
ADR:             =IFERROR(revenue/booked, 0)
RevPAR:          =IFERROR(revenue/available, 0)

If spreadsheets are not your strength, ask your chat assistant to write these formulas for your exact column letters, then test them on three rows you can work out by hand. The same habit appears in Google Sheets AI prompts: the assistant proposes, you verify on a sample.

A September for three listings, and what the numbers say

All figures here are invented to show the method. A host runs three listings: a garden flat, a studio and a three-bedroom house. For September the sheet produces this.

The portfolio row is calculated from the totals, not by averaging the three rows above it. The simple average of the three ADRs is $130.33, but only 15 of the 58 booked nights were in the expensive house and 43 were in the two cheaper listings, so the weighted figure is $126.53. Averaging the averages is a classic error in hand-built reports, and AI tools make it too, as we will see in a moment.

Now put two comparisons beside it. In August the same three listings had 93 nights available, 66 booked and $7,986 of room revenue, which is 71.0% occupancy, a $121.00 ADR and an $85.87 RevPAR. Last September, the fairer yardstick, they had 90 nights available, 63 booked and $7,434 of room revenue: 70.0% occupancy, a $118.00 ADR and an $82.60 RevPAR.

This is the picture that a single headline number hides. ADR rose, which feels like good news, but occupancy fell by more than the higher price repaid, so each available night earned less than in August and the month brought in 8.1% less. Against last September the portfolio looks steadier: RevPAR is up 80 cents, and revenue is down only 1.3%, partly because the studio had two fewer nights to sell. That suggests much of the fall since August is the season. The three-bedroom house is where to look first: half empty at the highest rate. Last September it booked 19 nights at a $172 ADR ($3,268); this September it booked 15 at $185 ($2,775). The extra $13 a night cost four booked nights and $493 of room revenue. Whether that is a pricing problem, a calendar problem (for example, a minimum stay that is too long for midweek) or a change in local demand is a question for the next sections, and for a human who knows the area and the property.

Prompts that turn the table into a one-page report

Give the AI the finished table, not the raw bookings. Tell it what it may and may not do, and ask it for questions as well as conclusions, because the sheet cannot see events, roadworks, a closed attraction or a new competitor next door.

You are helping me review one month of a three-listing short-term rental business. Below is a summary table that I calculated in a spreadsheet, for this month, the previous month and the same month last year. Do not recalculate anything. Use only these figures.

1. Write a 100-word summary for each listing: what happened and how it compares with last month and with the same month last year.
2. List the three biggest changes with the numbers behind each.
3. Give five questions I should check in my booking calendar before I change any price.
4. Suggest two or three decisions for next month. For each, name the figure that would show it worked.
Label anything that is a guess as a hypothesis. If a conclusion needs data I have not supplied, say so.

[paste the portfolio and listing tables]

A useful first response to the tables above reads something like this (illustrative, shortened):

Biggest changes: (1) Portfolio occupancy fell from 71.0% to 65.9% while ADR rose from $121.00 to $126.53, so RevPAR slipped from $85.87 to $83.40. Against last September RevPAR is up from $82.60 to $83.40, so part of the fall since August may be seasonal (hypothesis). (2) The three-bed house booked 15 of 30 nights at $185.00, against 19 nights at $172.00 last September. (3) The studio lost 2 available nights to repairs and booked 19 of 28.

Questions to check: Were the house's 15 nights mostly Friday and Saturday? Did a price rise apply to midweek nights too? How many 1-2 night gaps sit between bookings? Did the minimum stay change? Were there cancellations that were never rebooked?

Decisions: (a) Test a lower midweek rate on the house for four weeks; success is occupancy above 54% with ADR above $170. (b) Allow one-night stays on the house's gap dates only, and count the extra cleaning cost.

That is the output once the prompt has been tightened. Earlier drafts of a prompt like this tend to slip in three ways: saying "revenue improved because ADR increased" when room revenue fell 8.1%; stating "the end of summer" as the cause of the whole drop, when the year-on-year row only hints at it and the house is down on last September too; and reporting a portfolio ADR of $130.33 by averaging the three listings. The wording "do not recalculate" and "label guesses as hypotheses" is there to stop those, and the portfolio row is always supplied. Read the output as a briefing from an analyst who has not seen the property, and check every number in it against your table before it goes anywhere else.

Which pattern points to which decision

Once the table is right, the useful work is looking beneath it. The patterns below need one or two extra columns or pivot tables, and each one connects to a decision you can make. The thresholds are starting points to test, not benchmarks. Your own history matters more than any figure quoted here.

The gap pattern makes a good example. The host of the house in our example finds four single empty nights, each sitting between two bookings, and the minimum stay is two nights, so those nights cannot be sold at all. Allowing one-night stays on those gap dates only could fill some of them, though each extra stay adds a turnover and its cleaning cost, which the host should count first. This is exactly what an AI assistant can help you list: give it the booking dates (without guest names) and ask it to find every gap shorter than your minimum stay.

A quick sum: is each listing above its break-even nights?

Occupancy only means something against your costs. Break-even nights are monthly fixed costs divided by what each booked night adds after its own costs. For the three-bedroom house, suppose fixed costs of $2,400 a month (rent or mortgage, insurance, subscriptions) and $38 of variable cost per booked night (cleaning, laundry, supplies, utilities used). At the September ADR of $185, each night contributes $185 - $38 = $147. Break-even is $2,400 / $147 = 16.3 nights, or about 54% of a 30-night month. The house booked 15 nights, so it made 15 x $147 = $2,205 against $2,400 of fixed costs and lost $195 on the month, whatever its revenue column looked like.

Add that break-even line to each listing's row. A listing above its line has room to experiment with higher prices. A listing below it is a candidate for the gap and midweek tests above. Do these sums yourself, or have the AI write the formula, but fill in the cost figures from your own books. They are also where an accounting tool earns its place, which Airbnb hosts' accounting software and AI receipts covers.

A 20-minute monthly meeting and the decision log

A report nobody acts on is a hobby. Book twenty minutes on the same day each month, for example the third working day, and use this order:

1. Refresh the export and check the totals against the payout statements (five minutes).
2. Read the portfolio table and the AI's summary. Mark anything that surprises you (five minutes).
3. Check the AI's five questions against your calendar. Decide what is real (five minutes).
4. Choose no more than three changes, and write each in the decision log (five minutes).

The log is what makes the report improve over time. A filled-in line looks like this:

When you check the result, compare like with like. Put this October beside the same weeks last October, or compare Monday-to-Thursday nights with Monday-to-Thursday nights, because comparing a midweek discount month with a school-holiday month tells you nothing. If you changed three things at once, you will not know which one worked, so stagger them where the calendar allows.

Spreadsheet and AI, platform dashboards, or paid software

A spreadsheet plus an AI assistant is enough for one to five listings, which is also the range where Airbnb's own AI summary applies. Its help pages describe a "Top takeaway" that reads your earnings, occupancy and search performance, and say it is available only to hosts with five listings or fewer. Beyond that, or when you want automatic pricing, dedicated software can earn its fee.

Those tools overlap with reporting, but they answer a different question: what to charge tomorrow, rather than what happened last month. How AI sets nightly rates in vacation rental pricing software covers that side, and the comparison of AI tools for Airbnb hosts covers the property-management side. Hotels run a close cousin of this routine, described in weekly revenue-management reports for small hotels. For everyday host tasks beyond reporting, see ten host jobs for ChatGPT.

Checks to run before you trust the report

Three checks take ten minutes and catch most errors:

1. Reconcile revenue. Room revenue plus cleaning fees for the month should roughly match the payout statements for stays in that month. Differences of timing are normal, because platforms often pay after check-in. Differences you cannot explain are not.
2. Test three bookings by hand. Pick one that crosses a month end, one cancelled and one with a cleaning fee. Recompute nights and revenue and compare with the sheet.
3. Confirm the ceiling. Nights booked should never exceed nights available for any listing, and occupancy should never pass 100%.

Then keep the usual failure patterns in mind. Counting the cleaning fee in ADR flatters every number. Leaving cancelled bookings in inflates occupancy. Treating owner stays as available nights makes a busy listing look half empty. Comparing a 28-day February with a 31-day August on totals misleads, so compare per-night figures. A sample of three bookings in a month can swing a listing's ADR by tens of dollars, so with very few bookings, read the numbers as hints and look at a longer window. And any figure the AI reports that is not in your table should be treated as unverified until you have checked it.

Questions hosts ask about AI-built rental reports

Can ChatGPT do the calculations itself?

It can, using its code tool on an uploaded spreadsheet, but the safer pattern is to calculate in your own sheet and let the AI read the results. Every figure then traces back to a formula you can audit, and the AI's job is limited to explaining and questioning. Ask it to show its working for any number that is not in your table.

Should I remove guest names before uploading?

Yes. A report needs dates, nights, revenue, listing and channel, not names, emails or phone numbers. Delete those columns first and use the confirmation code as the key. On consumer plans, switch off the model-training setting; business plans do not train on your content by default, but the rule of sending only what the report needs still applies.

What counts as a good occupancy rate?

There is no universal figure. Compare each listing with its own history for the same month, and with its break-even occupancy: monthly fixed costs divided by the profit on each booked night. A listing at 65% that breaks even at 50% is healthier than one at 80% that breaks even at 78%.

How often should I run the report?

Monthly for decisions, plus a ten-minute look at the next 60 days of bookings if you change prices often. A full weekly report mostly adds noise for one to three listings, because a single booking can swing the weekly ADR by tens of dollars.

Further reads

• Airbnb Cleaning Schedule: Coordinate Cleaners and Changeovers — Short stays raise cleaning cost per night; plan changeovers properly.
• Holiday Let Website: Get Direct Bookings and Repeat Guests With AI — Direct bookings improve net ADR once channel fees are counted.
• How to Respond to Airbnb Reviews With AI: Glowing, Mixed, Bad — Ratings feed occupancy; answer reviews well with AI help.
• Hospitable Airbnb Software: What Each Plan's AI Actually Does — See what each Hospitable plan's AI does for guest messages.
• AI Agents for Short-Term Rentals: What a Host Can Do Today — Beyond reports: which host tasks an AI agent can take on today.
• Airbnb Listing Description Written With AI: Title, Summary, Rules — Better listing copy can lift the occupancy you are measuring.
• Airbnb Co-Host Business: Run Several Owners' Properties With AI — A multi-owner workflow for an Airbnb co-host: permissions, fee models, a monthly owner statement with a check cell, inbox triage and cleaner coordination.
• Holiday Cottage Marketing: Past-Guest Emails and Seasonal Offers — A past-guest email sequence and seasonal offer calendar for self-catering owners, with AI prompts, sample emails and the sums behind one rebooking.
• Pet Boarding Software: Can AI Forecast Holiday Demand and Staffing? — Turn three years of boarding bookings into a holiday forecast, a staffing sum and waitlist rules, with the checks that keep AI honest.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Airbnb help pages on downloading and finding earnings, on accessing earnings data (definitions of occupancy rate and average nightly revenue) and on the AI earnings summary, checked October 2026.