How to Build a Real Estate Tableau Dashboard | Part 3
In this video, I'm going to show you how to take free public market analysis data from Redfin and put it into a Tableau dashboard, so you'd be able to analyze your own market.
Ariel Herrera 0:00
Having market data at your fingertips is so important for housing. It allows you to understand the pulse of your market to see if things are trending upwards or downwards. In this video, I'm going to show you how to take free public market analysis data from Redfin and put it into a Tableau dashboard, so you'd be able to analyze your own market. My name is Ariel Herrera, the analytics aerial channel where we bridge the gap between real estate and technology. I love data driven solutions. And if you do to them, please subscribe as well like this video if you want to see more market analysis and visualization so I know to make more of it. Alright, let's get started. In this video, we're going to be able to develop this dashboard here that will give us an analysis of how our market is performing all the way down to the zip code level. In previous videos, we went over what this dashboard is about, and also how to get the data. The data is on Redfin site data center, and they have region data from national level, Metro State and all the way down to the zip code. It allows us to dive deeper into the codes that can be problematic or on the uptrend, the data that Redfin has is in a very hard to consume format, which is why in video two, I showed you how to actually extract this data using Python, if you'd like to skip the Python piece and just start working with this data, but you're not able to do so in this format than please reach out to me directly, we could work something out. Once you have your data set, it looks something like this. This is an Excel, and we have information on different periods going all the way back to 2012. We have information on the zip code, the property type, and then stats like median sale price, year over year changes price per square foot inventory, average days on market and more. I also brought in some other information within that Python notebook where we got the city county and this latest period field which is going to allow us to plot this within Tableau. So next step is to open Tableau. And if you don't already have a tableau account, then you need to create one Tableau Public is free to use make sure that you are on Tableau Public and that Tableau online is that is not free to use. Once you have an account, you'll be able to go to your own profile. Here you can see previous visualizations that you've created. You can also favorite ones that you discover, as well as follow other pages and retain followers to here on the right hand side, we're going to create a this. This now opens up the tableau workbook. This is where we're going to create the visualizations that will create this dashboard here is only a few easy steps. The first step here is dragging our file that we had from realtor and putting it into the system.
The file will take several seconds to upload based on the dataset that you sampled. So in my case, I only looked at four counties across Florida. The counties that I looked at included Hillsborough County, which is where Tampa the city exist, Pinellas County, where's Clearwater and St. P er, and then northern counties, Pasco Hernando and citrus. The reason why I downsampled and looked at these counties, and not all of Florida is I usually sample when I'm trying to create a visualization or do an analysis and then later on, I'll expand this out to a larger dataset. When you make your data set smaller, you can work with it a lot more quickly. And it really helps in development time. Once your data has loaded, you'll see it on the left hand side of the file. And you can actually update your data within this part. So you could see some sample information. On the left hand side we can see period begin and all those fields that I had mentioned previously when I pulled up the Excel file. Now if you go to sheet one on the bottom, we can start creating our first visualization. What we want to do is to create six different charts. Each of these charts is going to show us how one of our housing metrics has changed over time. So we have here inventory home sold by man, days on market, sold above list price, median list per square foot and median list price. I have some definitions up top but I do highly suggest for you to watch the first video where I went into detail of what these each mean. So the first one we're going to create is total inventory. We go back into our sheet In order to create a line graph within Tableau, we need to have our date or x axis within columns. So here we're going to look for our period and drag it to columns and change this to month. Next, the value that we want to actually measure is going to be an inventory. Inventory is a number, so we could find it down here. And we can drag this up top. And now we see a line chart. This line chart represents all of our data summing up the inventory by month. Let's break this out a bit. So we could see how this differentiates per property type. So if we've locate property type and the left hand side, we can add it into color. And now we could see that the inventory has split into five different lines. And these represent the property types. The blue line sums all the different types and those consist of condo. Multifamily is a two to four unit, single family residence, which is the most popular and townhouse. Now within our dashboard, we want to be able to filter on different date periods, as well as some other features here. So we're going to add those filters. Now onto our visualization. Let's select city, we could select all the cities and select county as well. We also want to filter on zip code. Property Type, period begin. And let's change this to relative date and select range of dates. Great, now we have our chart and we can label it at the bottom. Let's name this total inventory by month really right click, click Rename, and then enter in the new name. Now we could see that our sheet name has changed in the bottom. And we also have it at the top. Let's color coordinate this. So as we start to create more visuals, we can keep track of the type of visuals per color. So I'm going to right click and label this as green. The next charts that we want to create are all pretty much the same, because they're all line charts. And they have month period going across, the only thing that changes is the value that we're plotting, which is on our y axis as well as the titles. So we're going to do is actually duplicate each of these charts and just change those two fields. So let's start going to our sheet, right click and hit duplicate the next value that we want to look at our total home sold. So we're going to take home sold on the left hand side and replace that with some inventory. Now we have total home sold by month, but we need to change our sheet name. So right click, rename and change it to home sold.
Click OK. Now when we go over to the next one median days on market, it's going to be a little bit different. So for home sold, we want to see the total value of how many properties sold have in this time period. But for median days on market, we wouldn't want to sum that up. In this case, we want to look at the average. So when we go back we're going to duplicate this chart once more. But we're going to change this part here from sum to average. So let's duplicate this. Take median days on market and replace the in the rows. And if you click the drop down, you'll see under measure that we can select average. You can also select median too, if you want to get rid of any outliers here I'm just going to use average for simplicity Next, change this name to reflect median days on market. I'm going to repeat this process for the next three graphics. The only thing that's going to be changing is the variable that we're actually calculating so sold above list, median list per square foot median list price, as well as the names of the sheets.
Once you've completed the process of replicating each of those sheets, you will now have six different sheets below. So the ones that were added were median list price per square foot, median list price and sold above list price. All you have to do was duplicate and then replace the value over here. Before we get into our dashboard, we need to create three more different fields. So we have up here some summary stats. And these include average inventory, average, median, and average sold above list price all month over month. So in the case that our latest value was for May of 2022, and last month was April of 2022. These numbers basically get the difference between the values of each month to see if we're currently going up or down. And based on that, it could indicate a buyer's or seller's market, going back into our dashboard, we're going to create another sheet down at the bottom. And in this sheet, we want to first replicate average inventory month over month, which is going to be a single digit here. So we're going to take average inventory from our left hand side for month over a month. And we're going to add this as text. Now we see our value, even though this is summarizing everything, so we want to change this by clicking the drop down. And where it says measure click average instead. Right now this average is representing all the time periods, we only want to look at the last month over a month change. So we're going to take the field that we created within video to latest period. And we're going to drag that into filters. Here, we're going to select true, so we're only getting the latest month over month change. We should see now this number has changed a little bit, but it's still very small. And we also don't want to see sheet number seven. So let's click the drop down here and hide title. And let's also amend the size of this. So if we go to text and edit text, we can select this to be a larger size. So if we highlight everything, we can select, say 14, click OK. And our value is now larger. But this doesn't really indicate a percentage change. So let's change this here. And format our number so that it's a percentage. And this represents when we average inventory month over month, there has been a 21.5% increase. So let's add in some more filters. On the left hand side, I'm going to select Property Type. And here only select single family residences, which is what is of my interest. next four months, I'm going to drag in period end and select month. Next, we may also want to filter on zip code. Select All, City Select All, and county. Now we can re label this and set it as inventory month over month we can change the color of our sheet and make this red. This helps us to differentiate that all of the items in green are line charts. And in red, they're going to be single values. Now Let's replicate this duplicate it so that we can create it for median days on market, which is going to be our second value right over here. Right click, click duplicate and for median days on market month over month, drag that value and replace it with the inventory and modify this so that we have the average. We can also change this into a percentage by going to format number and selecting percentage. Now let's rename our sheet right click Rename. And we can call this median days on market month over month. Our last one is going to follow same process we're going to duplicate this sheet and now we're going to get sold above list month over
month. Now we can start creating our dashboard since we have all the different sheets and elements that we want to put in here. So if we go over to edit, we can go on the right hand side and select instead of new worksheet new dashboard. Here in the dashboard, we're going to see a similar type of view. But on the left hand side instead of seeing all of our fields. We now see all of our sheets, and we could start bringing in these sheets. So up top we do have a header which I think makes our dashboard look a little bit cleaner. So let's add that in regarding To take the text object at the bottom, drag it in. And here you can name this anything you'd like. I'm just going to put market analysis for Tampa. Next, I'm going to center this, make it a bit larger, and also make it white format. Here, we can't see anything. That's because our text is white. So let's go to layout. And let's change our background. And instead, we can make this a darker color. Now we can see that our header pops out a bit more. Our next steps is to create each of these visuals here. So this is average inventory, average median average sold above list price. So let's go back to dashboard. And we can take these values and add them back in. So let's put this at the bottom. We'll be able to hide this later on. But let's just drag in all of our sheets here. And let's remove the titles. So we can just delete this here. Click OK. And delete this. And you might be thinking, why are we deleting the sheet name isn't that going to help us know what these values are? Yes, but the way the formatting is, is that it's not going to look very clean. So in this case, for our dashboard to look nice, we're going to remove this and add titles in later. So let's make sure this is pretty centered. And now we can add some titles. So if we take text just like we did for our header, we could put this on top. And for our texts, we can match what we have here. So this is average inventory month over month.
Perfect now we have everything in here. Our next steps is we want to bring in over our line charts. There are six in total. So let's go back to edit. And let's start bringing these in by dragging them.
Great, now we have all of our sheets in but it looks all over the place. It's because this is really small right now our view and we want to increase our views so that we can fit each of our charts in a more friendly way. So let's adjust our size by going to the left hand side. And let's change these each to 1400.
We can now see that our dashboard has expanded yet we still don't see some of our charts here, we want all of our filters to be on the left hand side and then to be grouped with our charts. So follow the steps that I take care. Awesome, so now our chart looks a lot better. We now have everything pretty aligned within three different columns. And we could start formatting this a bit better as well. So we want to have these values centered. So we could do is add some blank fields around the numbers. And this helps to center. So let's adjust this a little bit and do the same steps for each of the other numbers.
Cool, so now our headers are pretty much aligned. But we want to have filters we want that our dashboard to dynamically update. So for example, we should be able to click different counties. We should be able to all So update for different zip codes. And we should be able to quickly toggle if we want to look at multiple property types, and for multiple periods, as well. So now let's go back to edit. So what we're going to do is go to one of the charts that we want to have filters for. And we're going to click the down arrow, go to Filters, and select the first filter that we want. In this case, it's going to be city city is going to pop up on the right hand side, we can drag this over to be underneath property type. We can modify this so that property type isn't as long so let's drag this upwards. And for our city, we have a ton of cities. But sometimes this isn't that friendly of a view. So let's click inside our box drop down arrow. And let's keep it that we can select multiple values, but make it that it's a drop down that way has a lot less space here. So let's add the rest of the filters that we'd like. So let's go back to our chart, click it downward arrow filters, county, take this and drag this down, we can change this again in order to be multiple values drop down. Great, now that we have all the filters that we care about, our next step is to make sure that they're going to apply for all the different charts. So in this example, if I were to select a single zip code that I want to focus on, let's just go with the first one here, it's only going to apply to that first chart. So how do we actually get it to apply for all charts? Well, we have to do is click the drop down, go to apply to worksheets, and click selected worksheets. This is where it makes it really easy to have things color coordinated, so that you could quickly see which tabs should all relate. So in this case, everything that is green should all update together. Once we click OK, we can now see that it's only geared for the specifics of code. So I'm going to set this back and repeat this process for each of our filters.
Now that all of our filters are updated, if we wanted to select, say only residential for all of our zip codes, we can now see that each of these charts reflects the change, we can also make the period smaller, and it will adjust. We can focus on only a handful of zip codes if we like. And we could further filter on city as well as county. Now remember, we also created filters for those previous single values. So you can do the same exact thing. You can have it that they update either with those other charts, or they update by some other cadence. Awesome. So now we have our dashboard, and we can actually publish it, it is now ready to be used where we can view our data on a routine basis, we could even set it up that we'd be able to automatically extract the data from Red fin once it's updated, it gets pulled into this dashboard. And a dashboard automatically updates. Once we exit out of our dashboard. And we go back to our profile. We're going to see our dashboard available for your dashboard. You may be thinking, okay, great, I'm able to click on different values. But how do I actually see some of this data in a spreadsheet? Well, once you click onto your chart, will you be able to do is go to the right hand side, click Table. And you could see the values there. So same values. In this case it was for total home sold. And we saw a month of period property type and home sold the How did that data actually get aggregated. We could go to the full dataset here and see what cities recovered and how that value became about. So it's really awesome because we could download this as well and do further analysis outside of Tableau if we'd like. As a summary, what we've been able to do in three videos, is first understand how we can tell if a market crash is occurring. If it's becoming a buyer's or seller's market, based on our particular market. It's important for us to look granularly at the zip code level if possible, but also see a macro Due to determine what changes and trends are happening around our area, second video, we were able to get all this data from Redfin. Redfin is data source is massive, it has millions of rows. Therefore, we use Python to process this data, sample it so that we can upload it into Tableau. Now in this video, we are able to take that data and bring it to life with a dashboard. Now based on your particular market, you'd be able to use this dashboard that you've created to analyze it on a routine basis. I hope this three part tutorial has been super useful for you in order to see how you can take a problem such as is my Market crashing, how to find a data and ultimately how to visualize it so you can make inferences and decide how you want to adjust your investment strategy. If you like to see these in depth tutorials in the future, then please let me know in the comments below as well as like this videos. It's been useful, so that I know to make more of these tutorials in the future. Thanks so much. And if you haven't already, please subscribe.