comparing data from different sources

Created by Doug Stoddart, Modified on Mon, 20 Mar, 2023 at 9:52 AM by Doug Stoddart

We often get asked about comparisons of data made against different sources.

For example;

graphs
data exports
within BRM itself (on the Reservation List Panel)

The problem is the data often doesn't correlate (at first glance). Here we examine why that is:

Month-overlap - when reservations start in one month and end in another.
What to include - for example, cancelled bookings or not.
Technical constraints - BRM has a limit on how many reservations it can return within BRM (300).
Changes - if you change the dates of a reservation after you've taken a statistical snapshot.
Occasionally a bug - e.g. 30 day months not behaving consistently.

Let's take a real-world example

(taken from an actual BRM store, but anonymized)

Reservations for the month of June.

First of all, let's look at the actual data;

	BRM - June	BRM - June (+cancelled)	BRM - custom 1-30th June	Graphs	Export
no. rentals	153*	167*	145 / 158	144	158
total rental income	7,065.00*	7,129.00*	6,475.00 / 6539.00	6,397.00	6,539.00

* It seems that in BRM when you search for June(30 days) it includes reservations starting on 1st July. (bug)

[issue ID BRM-4982 we are working on a fix]

Then let's dig in to understand the various sources of data;

	BRM	BRM (+cancelled)	Graphs	Export
include cancelled?**	optional (default no)	yes	no	yes
dates	reservation STARTS on June 1st > 30th

** When a reservation is cancelled it has its price set to zero.

Reservations for the month of March

(smaller data set is easier to compare)

	BRM - March	BRM - March (+cancelled)	Graphs	Export
no. rentals	43	56	43	56
total rental income	2,369.00	2,422.00***	2,369.00	2,422.00

Reservations for the month of July

(now a bigger data set)

	BRM - July	BRM - July (+cancelled)	Graphs	Export
no. rentals	too many ****	too many	271	309 (30 cancelled)
total rental income	too many	too many	19,729.00	22,117.00

*** It appears that when refunds occur the price (which has been refunded) is re-instated post refund. (even though the reservation's price is set to zero on cancellation.

There are two further dimensions to consider, which add to the complexity.

When is the data 'frozen'? - it is not. If you modified a reservation 2 months later (price or date) - then it would be altered.
**** Physical limits on pulling back Reservation Data into BRM - 300 reservations.

Transactions

Then there is another complication. What about comparing reservation data with transactional data?

This is, of course, a very different thing!

Transactions may occur at different dates - even after the reservation has 'ended'.

Reservations may also be paid for through multiple transactions.

Reservations may not be fully paid - or may be overpaid.

All of these things mean you may get a very different answer when comparing or reconciling payments against

See Also

month overlap issue