Promotional
Intelligence
Engine
Client
Wollongong City Council
Platform
Excel (Macro-Enabled Workbook)
Scope
Tourist Parks — Bulli, Corrimal, Windang
Wollongong City Council operates three tourist parks — Bulli, Corrimal, and Windang — each tracking occupancy data separately in Newbook, with no shared system for deciding when to run promotions or how to compare performance across parks. Promotional decisions were made from memory and intuition. The brief was to replace that with a tool built from actual occupancy data.
Five sheets, each feeding the next. Newbook exports quarterly occupancy data into the Occ Summary sheet. Park Breakdown pulls from there, showing month-by-month performance by accommodation type and park, each cell colour-coded by distance from the occupancy threshold.
The Control Panel sets thresholds: cabins at 60%, powered and unpowered sites at 50%. Sunday occupancy is tracked separately because weekday and weekend patterns drive different decisions. Change a threshold once and the colour coding updates across every park and month automatically.
The Promo Calendar reads those signals and applies conditional logic to determine which promotion type is appropriate each month. Recommendations is the output — a month-by-month plan showing what to run, at what discount level, and for which parks.
The master input sheet — occupancy thresholds, park names, date ranges, and offer parameters are set once here and feed every downstream calculation. Changing the cabin threshold from 60% to 55% in one cell updates every month's Switch-On Status across all three parks simultaneously. Everything else in the workbook is output, not input.
Per-park occupancy across Bulli, Corrimal, and Windang, broken down by accommodation type and month. Cabins use a 60% threshold; powered and unpowered sites use 50%. Sunday is tracked separately from the rest of the week. Each cell is colour-coded by distance from threshold — green when well above, yellow when borderline, orange when below but recoverable, red when critically low.
An Excel macro-enabled workbook (.xlsm) with strict input/output separation. The Control Panel is the only place where values are entered manually — everything else is formula-driven and updates automatically, preventing the data drift that happens when people edit outputs directly.
The threshold system uses four colour bands: green (70%+), yellow (approaching threshold), orange (below threshold but above 30%), red (critically low). At-a-glance status without reading individual numbers.
Built-in guardrails block invalid recommendations: January, April, October, and December are locked as no-promotion months per Council policy. Discounts are capped at 30% per procurement rules. A T-28 trigger flags promotions for review at least 28 days before the target period.
Four promotion types, each matched to a specific occupancy pattern. Late Checkout Value-Add: a non-discount incentive for borderline occupancy — adds value without cutting the rate. Midweek Reset: 10–20% off midweek when midweek lags but weekends are full. Fills the gap without cannibalising peak periods.
Stay & Save: 4-for-3 or 7-for-5 stays, used when occupancy is low across all days. Sunday Slow-Down: 50% off Sundays when Friday and Saturday are booked — targets the one-night drop-off at the end of peak weekends.
July is the worst month across all three parks — cabin midweek occupancy drops to around 4%, sites and camping below 1%. The Stay & Save offer is the recommended response for June through August, with Sunday Slow-Down running in parallel to capture weekend extensions.
The logic layer that translates occupancy signals into promotional recommendations. Conditional formulas read each month's Switch-On Status for each accommodation type and assign the appropriate promotion — or flag the month as a no-promotion period. January, April, October, and December are locked out of promotional activity by Council policy regardless of occupancy.
The 2026 annual promo plan was generated directly from the Recommendations sheet using 24 monthsof historical Newbook occupancy data — the first time Wollongong City Council's Tourist Parks had a full-year promotional calendar built from actual park performance rather than estimates.
All three parks moved from separate spreadsheets and ad hoc decisions to one shared tool with a single input layer. The operations team can run quarterly updates themselves — import new Newbook exports, review the colour coding, use the Recommendations output.
I documented the full workbook at handover: what each sheet does, how the threshold logic works, how to update the Newbook imports, and what the output values mean. The tool was designed to be maintained without the person who built it.
The output sheet — a month-by-month plan showing which promotion applies, at what discount level, and for which accommodation types across each park. The 2026 annual promo plan was generated from this sheet using 24 months of historical Newbook data. Discounts are capped at 30% in line with Wollongong City Council promotional policy.