How the Interealty Hotsheet Works

a whitepaper

by Steve Clarke

This whitepaper describes the high-level design and implementation of the Hotsheet and Property History functionality on Interealty servers. This is not necessarily a description of how these functions are implemented by all server vendors, however it should be a reasonable discussion point. The following diagram describes how data is processed and stored related to these two functions. A description of each element in the diagram follows.

Processes:

Generate Raw Transaction Data – Regardless of how listing records are added/modified/deleted on the system, we will generate a complete audit trail of all changes. This audit trail consists of “Raw Transaction Data” and “Listing Snapshots”.

Generate PAR Archive Data – This process is run as a nightly job. It basically generates the “Property History Data” that will be reported to the end-user via the PAR report. The rules that govern what types of changes will constitute a recorded “Property History” event are site configurable. Also, the fields that will be recorded are site configurable. Typically, Status changes and Price changes are considered relevant for the “Property History”. It is, however not guaranteed (and not even likely) that every listing change that is relevant in a Hotsheet will be recorded in the “property History”. The “Property History” data is maintained for archival purposes (indefinitely or for many years). Because this table will contain MANY, MANY rows, we typically configure the “Property History” to only record very relevant information from the listing record.

Pre-Process Hotsheet Sections – This process runs continually in order to assure that Hotsheets are always current. It basically prepares the data for the specific sections of the Hotsheet report that will be presented to the end-user. The “Hotsheet Data” is prepared according to the “Hotsheet Section Configurations” which are site-configurable. The “Hotsheet Section Configurations” define precisely what types of changes will be recorded in each of Hotsheet sections.

Produce Hotsheet Report – This process is invoked by the end-user whenever they run a Hotsheet report. Basically, we just present the preprocessed “Hotsheet Data“ according to the filters requested by the end-user. One thing that is noteworthy of the Hotsheet report is that the listing data for the report is retrieved from the “Listing Snapshots” data. That means that the data reported in the hotsheet will not necessarily be the current data from the listing record. For example: If a listing was reclassified from ‘Pending’ to ‘Active’ and then later had a price reduction from $100,000 to $99,000 it may show up in the “Back on Market” section as well as in the “Price Change” section. In the “Back on Market” section, the ListPrice would be shown as $100,000. In the “ListPrice Change” section the price would be shown as $99,000. Another example would be the “Deleted Listings” section. Obviously, the deleted listing would no longer be available, therefore it must come from the “Listing Snapshots”.

Produce Property History Report – This process is invoked by the end-user whenever they run a PAR report. Basically, we just present the preprocessed “Property History Data“ according to the filters requested by the end-user.

Data Stores:

Hotsheet Section Configurations – This data describes the Hotsheet sections as defined by a particular site. This includes what PropertyTypes apply to a particular section, what types of listing changes will be included, as well as what user classes have access to each section.

PAR Configurations – This data describes what types of listing changes will be recorded in the long-term “Property History”, as well as specifically what subset of data elements from the listing will be recorded.

Raw Transaction Data – This data records ALL listing change activity regardless of the source. For MODIFY transactions, we also record every individual field level change as well as the old and new values for the changed fields. As this is quite a lot of data to record, we keep this only for 30-60 days. Various downstream processes to generate more refined “Property History and Hotsheet” data use this data. This data is also used for ongoing Prospect matching activity.

Listing Snapshots – For each listing change transaction we record a complete copy of the listing record as a snapshot. This snapshot data is only maintained for 30-60 days. The data is used for Hotsheet reports exclusively.

Property History Data (PAR) – This is a very small subset of the listing data that is recorded at particular snapshot points that are relevant in a property’s history. The data is compiled from the “Raw Transaction data” by a nightly job. The rules for when to record the snapshot and exactly which subset of fields will be recorded are site configurable.

Hotsheet Data – This data is compiled continuously for the Hotsheet report. Based on the site-configuration for the hotsheet sections, relevant events are identified in the “Raw Transaction data” and then assigned to the appropriate Hotsheet sections. The Hotsheet report then uses this information of compile the end-user report according to their specific filters.

Relevant Notes related to RETS Specification

“Property History” and “Hotsheet” data are not necessarily the same thing. Under the Interealty implementation of these functions it is clear that the events that constitute a Hotsheet event are not necessarily the same as the events that constitute a Property History event. Furthermore, the Hotsheet data is only available for a short time, while the Property History data is available beyond the life of the listing.
The relationship between Listings and Property History is many-to-many. A Listing record can certainly have many Property History records. Conversely, a single Property History record may apply to many different listings. The reason for this is that for a property that has been listed several times, under several different ML#s, the Property History chain will show all of those ML#s. In other words, the foreign key relationship between Listing and Property History is NOT ML#. It is TaxID + CountyID.
The data that is displayed in a Hotsheet report section is NOT necessarily the current data of the corresponding listing. Certain Hotsheet sections are specifically designed to show a relevant change that happened in the past. The data of the listing at the time of that change is what is desired in the report.
Hotsheet data is very transient. We limit the maximum number of “Days Back” for obvious efficiency reasons.