How to calculate Listing Level Pickup

Analyze the specific properties that are or are not experiencing changes in availability

If you’re familiar with our Pickup graph on your Rate and Demand Dashboard, then you know the importance of being able to check for peaks or dips in demand each week. This allows you to visualize when the bookings are happening overall in the market, and especially when applying your competitive set filters.

To take it a step further, we can show you how to refine your pickup analysis down to the individual booking tendency of each listing within your compset. With this level of reporting, you can sort and group together properties that have significant measures of unavailability in a given time period. This will offer insight into which specific types of properties are performing and to what extent, beyond the categorical limits of the main pickup graph. Having the listing level pickup analysis will provide masterful insight into the property-characteristic trends of your market and will also allow you to create tighter, differentiated custom groups even more granularly than before.

1) First take note that you will need to download two sets of data from the Listing Level View tab, from sequential updates. Therefore you’ll want to plan ahead by saving an export for a certain week’s delivery; then, using the same exact filters on the following week’s update, export the Listing Level View for the same calendar period.


2) When you have both files ready, start with the older export. Delete the columns with dates that do not correspond with the newer file export. Depending on the time period you’re analyzing, you may have downloaded and saved the initial export with older dates that will not appear on the newer export, and we want the dates to line up.

3) Next, use the function COUNTA in the first empty column at the end of all the dates in the header and in the second row (do not use COUNTA as the header).

4) Select the range starting from the cell under the first date that corresponds to the newer file export and drag to the cell at the end of the row (right before the COUNTA function cell). Enter and you will receive a value that reflects how many days that property is available for in that selected calendar period. We will refer to this as the availability value (feel free to title the column).

5) Double click the bottom right corner of the cell to apply this function to each row all the way down the column, giving us availability values for each listing. Create a new column next to the unified IDs column. Now copy the entire column of the availability values and paste as values in the column next to the unified IDs. Delete the last column with the COUNTA function.

6) Repeat steps 2-5 for the newer file.


7) In the older file, create four more new columns next to the availability value column.

8) Use the function VLOOKUP in the first cell of the first blank column. Click the column with the unified IDs in this older file, and select the Table Array range from the newer file that includes the unified IDs and the availability values (Click and Drag from column A to B). Enter 2 in the next line and then 0 in the last line; then enter. Copy this whole column and paste as values into the blank column next to it (you can now delete the original VLOOKUP column).

9) In the next blank column, you will perform a subtraction equation by using the formula bar. Simply enter in the cell numbers to subtract in the bar, in this case B2-C2, and hit enter to produce the difference. Next click the bottom right corner of this cell to apply the formula to the rest of the column. Now copy the entire column and paste as values in the next and last blank column. You can delete the column with the subtraction formulas as well as the two columns with the availability values.


You now have the Pickup for each individual listing in this comp set, and you can title the column as such. The numbers in this column will represent how many dates the property became unavailable in the past week for the selected calendar period, with higher values representing significant traction for such listings, and negative numbers representing recent cancellations or added inventory. Apply a filter to this column and sort accordingly to analyze the individual properties in terms of their weekly demand.