Did you know you can make an easy, organized Pivot Table using the Supply download?
A Pivot table is an Excel feature that lets you drag and drop the elements of a data set into a logical visual summary that allows you to analyze the components that are relevant to you. The supply download has various interesting elements as it pertains to each individual listing in your market, and here we will show you how to construct a Pivot Table that summarizes the following example: Average ADR 2019 by bedroom count, organized by zip code and layered by property type, with an added filter for the Property Management Company.
1. Go to the Supply tab on your Dashboard and hit the EXPORT CSV to download the Supply. Open in Excel.
2. Select columns that you want to create a Pivot table for; or simply select all by clicking in the top left corner of Column A and Row 1. For this example we will select all.
3. Go to Insert in the header, then the first option on the far left will be Pivot Table. You can create this on the same sheet as the data you are observing, however we recommend using a new sheet when the data file is already this large.
4. Now simply drag and drop the elements you plan to analyze from the Pivot Table Fields section on the right panel, and into one of the four rectangle areas under it. These elements represent the headings of each column from the supply, all the categorical details that define each listing. The table will begin to automatically construct based off of where you drop the element.
The four areas are broken down as such:
4a. Rows- the focus characteristic that you want to organize the data and table by (We will use “zip code”). You can also drag a supplementary element under this selection to create another layer of analysis. For example adding “property type” to narrow down the data within that zip code
4b. Columns- the variable by which you want to examine the data within the chart. We will use “Bedroom Count”
4c. Values- the data quantity to analyze, or the main element to be represented numerically within the organization of the table. We will choose Average ADR 2019 (After dragging and dropping the element in the Values rectangle, you will need to click the drop down arrow next to it and go to “Value Field Settings” in order to change the calculation from the default “Sum” into “Average”, or into whatever setting you prefer)
4d. Filters- Creates a field or multiple fields at the top of the table to quickly find and filter more specific subsets of the overall data. We will go with Property Manager or “pm_domain_name”
There you have it, you can now analyze the Average 2019 ADR data by Zip Code and Property Type, broken down by Bedroom Count and filterable by Property Manager. Click on the values in the table to open up a sheet with the details of the individual listings that make up that grouping.
Do you want another example of a Pivot Table with filters for forward-looking competitor Pricing and Occupancy data? Check out the Ranking tab on the Dashboard, it is set up just like one!
While this is a straightforward example of a Pivot Table, you can actually create increasingly more complex analyses with this feature. Contact us for more thorough assistance with this.