Starting Airbnb And Airbnb Cleaning Business In Copenhagen, Denmark

Context

Airbnb, as in “Air Bed and Breakfast,” is a service that lets property owners rent out their spaces to travellers looking for a place to stay. Travellers can rent a space for multiple people to share, a shared space with private rooms, or the entire property for themselves. Today, many people whose houses or properties qualify to be enlisted on AirBnB are making money from it.

Why the Analytics?

An imaginary company acquiring properties for AirBnB purposes is trying to expand to Copenhagen. But before expanding to the city, they need to understand how AirBnB is performing in the CPH concerning Hosts’ earnings. 

Questions

1. How much is the top AirBnB Earner Making in CPH, the average monthly revenue of a host and which neighbourhood of Copenhagen has the highest earnings? 
2. To understand the property type to acquire, what types of properties have the highest earnings?
3. Who are the potential customers for the AirBnB cleaning service in my area

The Process

Data Acquisition

The datasets used for this analysis were acquired from http://insideairbnb.com/get-the-data/

AirBnB listings and reviews datasets we acquired.

Question 1

To answer the first question, there’s a need to understand the simple mathematics behind total earnings on AirBnB. 

Projected Monthly Revenue = Nightly Price x Nights Booked Per month

The above formula was used to determine the earnings and the calculations were done using BigQuery

SQL CODE – BigQuery

SELECT * FROM Airbnb.listings; SELECT CAST(id AS INT64) AS listing_id, /*The type was change from STRING to INTEGER so the listing_id*/ listing_url, name, room_type, property_type, neighbourhood_cleansed, latitude, longitude, price, 30 - availability_30 AS monthly_booked_out, (price *  (30 - availability_30)) AS monthly_proj_revenue FROM Airbnb.listings ORDER BY room_type, monthly_proj_revenue DESC; /* Results of query above is exported as table (newlistings)*/

The queries above helped see the whole table with the calculation added to it, but the question is yet to be answered. To answer the question, the queries below were used.

SELECT * FROM Airbnb.newlistings; /*To find out the Highest and Average monthly projected revenue*/ SELECT MAX(monthly_proj_revenue) AS highest_revenue, ROUND(AVG(monthly_proj_revenue), 0) AS average_revenue FROM Airbnb.newlistings;

I found that the highest earner in Copenhagen is making a monthly projected revenue of $1947000 and an average host earns $28259.0

Visualisation Using Tableau

To see how people earn by their neighbourhoods in Copenhagen, I used a Treemap visualisation for it. I found out that Indre By is the neighbourhood where AirBnB hosts in Copenhagen make more money.

The implication is that the company now have an idea of where in Copenhagen to acquire properties.

QUESTION 2

To answer question 2, which is: what types of properties has the highest earnings? Tableau public was also used for the visualisation. The property types on Airbnb vary from Entire condo, Entire home, Entire townhouse etc. I found out that the Entire Rental unit is the property type hosts are using to make the highest earnings

QUESTION 3

Who are the potential customers for the AirBnB cleaning service in my area?

To know the potential customers for the proposed cleaning service, we used SQL queries to filter reviews that contain the word ‘dirty’. This is based on the assumption that any review containing means there’s a cleaning service issue with such a listing.

SQL QUERIES – BigQuery

SELECT DISTINCT listing_id FROM Airbnb.reviews; SELECT * FROM Airbnb.reviews WHERE comments LIKE "%dirty%"; /*save results into a table called cleaning_leads*/ /*change the listing_id to INTEGER to prepare it for joining with the newlistings table*/ SELECT CAST(listing_id AS INT64) AS new_listing_id, * FROM Airbnb.cleaning_leads; /*Query is returning error because of a wrong value*/ /*Rewrite the inital query again and exlude the wrong value*/ SELECT * FROM Airbnb.reviews WHERE comments LIKE "%dirty%" AND listing_id NOT LIKE '%E+%'; /*´values containing 'E+' are the error value being returned by the query*/ SELECT CAST(listing_id AS INT64) AS new_listing_id, comments FROM (SELECT * FROM Airbnb.reviews WHERE comments LIKE "%dirty%" AND listing_id NOT LIKE '%E+%');

The result of this query showed 374 potential leads for the cleaning service. In reality, it is not concluded that all the 374 potential leads will need another cleaning service, but it shows opportunities.

To get other information from the listing table to the review table, I wrote an INNER JOIN query to join the two tables, and the result was exported as a new table.

/*Join newlisting table and new reviews to get the information of listings with dirty as part of the reviews*/ SELECT DISTINCT * except(comments) FROM Airbnb.newreviews INNER JOIN Airbnb.newlistings ON new_listing_id = listing_id /*Export results to table cleaning_service_leads*/

Lessons Learned

Working on this project helped me understand data analytics beyond just a nice-to-have skillset. I began seeing it as a process of solving problems for businesses. The data analytics process helps to confirm assumptions and simultaneously cancel out wrong assumptions. Much more, I found out that answering a business question using data analytics helps to reveal answers to questions that were never thought of at the beginning of the analytics process. Ultimately, I can say that the opportunities presented by data analytics are endless.