Real Estate Data Warehouse
Imagine not having to run a report in your property management system each time you have a question
You are watching a football game on TV and a team scores a touchdown. Within seconds, the commentator says something like, “This is the receiver’s fifth catch this season in the fourth quarter that was longer than 20 yards … and two of those catches were over 40 yards!”
How are they able to do that? They can come up such factoids because they have lots of lots stats stored in such a way that frequently asked questions can be easily answered. The storage method they use is called a “Data Warehouse.”
What Can You Do With a Real Estate Data Warehouse?
If questions like these frequently come up, they can be quickly answered if the information is in a real estate data warehouse:
- How many times has Tenant X been late by more than 10 days on rent?
- How many work orders are currently open longer than 20 days?
- Have we ever had a work order open longer than 30 days?
- How much business have we done with Vendor X, and has annual business been increasing?
- How does our vacancy for 2BR-2BA units compare with vacancy for 2BR-1BA units? Is the gap increasing?
- What’s our NOI to OpEx ratio for the entire portfolio? And which properties are below the portfolio average?
- How many leads get converted to leases? Is the conversion ratio better for leads from Craigslist vs leads from apartments.com?
… and so on.
If you think being able to quickly answer such questions will help manage better, below is more information about real estate data warehouses.
How We Build Your Data Warehouse
When the sports broadcasting people started building their data warehouse (decades ago!) they started loading all sorts of statistics. Initially they collected data on touchdowns by quarter, touchdowns by quarterback, touchdowns by receiver and so on. As technology became cheaper and faster, just about any information got added, such as whether the touchdown was thrown by a right-handed quarterback, and if the receiver made the catch over his left shoulder or right shoulder.
When we build your data warehouse, we start by pulling the most commonly used reports, and storing all the rows and columns from those reports into the data warehouse. For example, every P&L run at each month-end since the property opened. Or, every General Ledger transaction (invoice, payment etc. by property, charge type, date etc.), every lease, every work order and so on. See below diagram.
Organizing the Data
Loading the data is the easy part. Once it has been loaded, it has to be organized so that questions can easily be answered.
For example, if you were building your data warehouse entirely in Excel, you would need to create a lot of lookup tables. Usually there is a field (or column) needed to look up information, such as Property ID, GL Account ID, Transaction Date, Vendor ID etc. We do all of this work not in Excel but in technology called the “SQL database.”
What the Data Warehouse Looks Like
The data we pull out of the property management systems, CRM systems and accounting systems etc. is very cryptic. It was written by programmers to be understandable by machines. So, we have to translate it into readable, usable formats. For example, the Transaction Date might be stored as t_date and the Property Name might be stored as p_name_long. We clean all this up so that the reports you see are understandable and the charts show titles and labels that make sense.
The diagram below shows the real estate data warehouse organized in topics and subtopics.
Ready to utilize our experience and knowhow for building your company's data analytics competency?