Project 1 : Airbnb New York City Market Analysis

Business Case

Head of Airbnb in New York City needs BI Analyst help to analyze the rent room data in Airbnb listing.

The problem : data is not ready to use, so that BI Analyst have to transform the data first in order to do analysis.

A number of questions that BI Analyst needs to answer:

1. How much average price to stay in Airbnb for a night?

2. How about average price in Airbnb listing per month compared with the private rental market?

3. How many listing for personal room?

4. How about the price comparison in 5 region in New York City?

BI Analyst can add another information to get more insight about Airbnb in New York City.

This challenge done with BigQuery and Google Data Studio.

Data Preview & Data Transformation

Data used in this challenge consists of 3 tables that is: ‘airbnb_prices’ , ‘airbnb_reviews’, ‘airbnb_room_types’

From the picture, we can see that data type on price column is string, so that I have to transform the data type into integer and deleted “dollars” behind the number.

In the column "nbhood_full" there are contain borough name and residential neighborhood name. So I want devide into just borough name. So, I want to create a new column named “borough” that taken from nbhood_full column.

From the 'airbnb_room_types' table above, value in 'room_type' column still messy. So, I want to fix the value from 'room_type' column into all lowercase text.

From 'airbnb-reviews' table there is no data to be fixed.

According to the statement above I want to transform data and combine three tables into one for easier analysis. The query used is:

The result :

Data that has been transformed is stored under the name ‘airbnb_data’

Descriptive Analysis Price Column

In order to get more information about price column, I want to find out percentile value, average value, minimum & maximum value in column price. The query used is :

The result :

From the result above, we can see that average price of Airbnb in NYC is 141.78 dollar. The maximum price is 7500 dollar and the minimum price is 0, it means there are Airbnb which is rented for free. But, the value of Q3 is only 175 dollar. It might be there are outlier in the maximum price.

Average Price To Stay In Airbnb NYC For A Night

According to descriptive analysis, there are Airbnb with 0 price (free). So, I want to calculate the average price without including Airbnb with 0 price.

The average price to stay in Airbnb NYC for a night is 141.82 dollar. There is no significant difference when calculating average price with the zero value or without zero value.

Average Price In Airbnb Listing per Month Compared With The Private Rental Market

According to the previous section, the average price of Airbnb in NYC is $141.87 for a night. On the other hand, based on one of the apartement research platform in NYC (Renthop) said that the average price of private rental market in NYC is $3.100 for a month. So, I have to know Airbnb average price for a month with the query below :

From the table we can see that average price of Airbnb in NYC per month is higher ($4313.61) than average price per month of other private rental market in NYC ($3.100).

Number of Personal Room in Airbnb New York City

Based on the data, there are several type of room in Airbnb NYC, I want to know how many listing each type of room. The query is:

We can see that room type ‘entire home/apt’ are higher demand in the market, I assumed that people in NYC who rent Airbnb are family, So that many Airbnb type ‘entire home’ has high demand.

Price Comparison in 5 Region in New York City

From the result above, we can see that the average price of Airbnb in Manhattan is highest than other region in NYC. Brooklyn is the second with high average price.

Average Price Airbnb in New York City Based on Room Type

From the result above, we can see that room type ‘entire home/apt’ is more expensive than type ‘private room’ and ‘shared room’. It might be because type ‘entire home / apt’ room is wider than type ‘private room’ and ‘shared room’.

Price Range By Region

I want to categorizing price to certain price range that is “Budget”, “Average”, “Expensive”, and “Extravagant” and grouping by the region.

The result :

From the chart above, we can see that the number of room with average price in Brooklyn and Manhattan is almost have same amount. The most number of room with expensive price are in Manhattan. So that’s why Manhattan being region with the highest Airbnb average price in NYC.

Conclusion

1. Average price to stay in Airbnb NYC for a night is 141.82 dollar.

2. Average price of Airbnb in NYC per month is 4.313 dollar. The average price is higher than average price for other private rental market in NYC.

3. There are 3 type of room in Airbnb NYC. The highest demand room in market is type ‘home / apartment’.

4. Comparing with 5 region in NYC, Average price of Airbnb in Manhattan is the highest than other region in NYC, that is 184 dollar.

5. Comparing with 3 type of Airbnb room in NYC, room type ‘home /apartment’ is the most expensive than other, that is 197.17 for a night.