Analyzing Airbnb ratings in Los Angeles

Context

Founded in 2008, Airbnb has quickly gained global popularity among travelers. To elevate this service, identifying the determinants of listing success and their role in drawing tourism is pivotal. The users' property ratings focus on criteria such as accuracy, communication, cleanliness, location, check-in, and value.

This tutorial aim to extract insights into Airbnb users' overall impressions, connecting the overall rating score with distinct variables while taking into account the geographical neighbors behavior through a Geographically Weighted Regression model.

We'll also dive into the regions where location ratings significantly influence the overall score and enrich this analysis with sociodemographic data from CARTO's Data Observatory.

Step-by-Step Guide:

Visualizing Airbnb listings

Access the Maps section from your CARTO Workspace using the navigation menu and create a "New Map".

Add Los Angeles Airbnb data from CARTO Data Warehouse.
- Select the Add source from button at the bottom left on the page.
- Click on the CARTO Data Warehouse connection.
- Select Type your own query.
- Click on the Add Source button.

In the SQL Editor panel, type the following query and click "Run".

SELECT * FROM carto-demo-data.demo_tables.losangeles_airbnb_data

We will inspect how Airbnb listings are distributed across Los Angeles and aggregate the raw data to have a better understanding on how different variables vary geographically within the city.

Rename the map to Map 1 Airbnb initial data exploration. Then click on Layer 1 and apply the next style change:
- Name: Airbnb listings
- Color: Dark yellow
- Radius: 2.5

Now let's add a new data source to visualize the airbnb listings using an H3 grid.

Add a new data source using the below SQL Query that will aggregate the AirBB listings variables for each of the H3 cells. Read more information on Spatial Indexes such as H3 here.

WITH h3_airbnb AS (
  SELECT
    `carto-un`.carto.H3_FROMGEOGPOINT(geom,
      8) AS h3_id,
      *
  FROM
    carto-demo-data.demo_tables.losangeles_airbnb_data),

aggregated_h3 AS (
  SELECT
    h3_id,
    ROUND(AVG(price_num), 2) price,
    ROUND(AVG(review_scores_rating), 2) overall_rating,
    ROUND(AVG(review_scores_value), 2) value_vs_price_rating,
    ROUND(AVG(review_scores_cleanliness), 2) cleanliness_rating,
    ROUND(AVG(review_scores_location), 2) location_rating,
    COUNT(*) AS total_listings
  FROM
    h3_airbnb
  GROUP BY
    h3_id
	HAVING COUNT(*) > 3)
	
SELECT
  * EXCEPT(h3_id),
  `carto-un`.carto.H3_BOUNDARY(h3_id) AS geom
FROM
  aggregated_h3

Style the new layer and enable 3D view.
- Name: H3 Airbnb aggregation
- Order in display: 2
- Fill color: 10 steps blue-red ramp based on column price_num using Quantile color scale.
- No stroke
- Toggle the Height button and style this parameter using:
  - Method: sqrt
  - Value: 20
  - Column: total_listings
- Enable 3D view

Inspect the map results carefully. Notice where most listings are located and where the areas with highest prices are. Optionally, play with different variables and color ramps.

Estimating variables influence on the overall rating score

Next we will apply a Geospatially Weighted Regression (GWR) model using the GWR_GRID function to our Airbnb H3 aggregated data. We’ve already seen where different variables rate higher on our previous map.

This model will allow us to extract insights of what the overall impression of Airbnb users depends on, by relating the overall rating score with different variables (specifically we will use: value, cleanliness and location)

We will also visualize where the location score variable significantly influences the ‘Overall rating’ result.

To save map results and continue working on a separate map, lets duplicate the map, disable the 3D view and rename the map copy to Map 2 GWR Model map
(Optional) We will now proceed to calculate the GWR model leveraging CARTO Analytics Toolbox for BigQuery. You can do so using CARTO Workflows or your Google BigQuery console

First, let's materialize H3 airbnb aggregation from the SQL Query applied previously. For that, use "Create table from query" and save this in the "Shared" dataset available in the CARTO Data Warehouse "Organization" section.

The parameters used in GWR model will be as follows:

Source: The materialized h3_airbnb_aggregation source
Index column: h3
Feature Variables:
- value_vs_price_rating,
- cleanliness_rating
- location_rating
Target variable:
- overall_rating
Kring Size: 3
Kernel function: gaussian
Fit intercept: True

Using CARTO Workflows

Using Google BigQuery console

CALL `carto-un`.carto.GWR_GRID(
    'carto-academy.b02_pub_airbnb_reviews_gwr.02_listings_la_2021_5_reviews_h3_z8_agg',
    ['value_vs_price_rating', 'cleanliness_rating', 'location_rating'], -- [ different ratings features ]
    'overall_rating', -- overall rating (target variable)
    'h3_z8', 'h3', 3, 'gaussian', TRUE,
    NULL
)

After executing the GWR model, feel free to save it or use our materialized table cartobq.docs.airbnb_la_h3_gwr

Now, let's add a new layer as a ‘SQL query’ using the following query:

SELECT
  h3_z8 as h3,
  value_vs_price_rating_coef_estimate,
  cleanliness_rating_coef_estimate,
  location_rating_coef_estimate,
  intercept
FROM `cartobq.docs.airbnb_la_h3_gwr`

Style the layer.
- Name: Location relevance (Model)
- Order: 3
- Fill Color: 10 steps blue-red ramp based on location_rating_coef_estimateusing Quantile color scale.
- No stroke

Change the basemap to Google Maps Roadmap basemap.

Click on the Dual map view button to toggle the split map option.

Left map: disable the Location relevance (Model) and Airbnb listings layers
Right map: disable the H3 AirBnB aggregation and Airbnb listings layers

Inspect the model results in detail to understand where the location matters the most for users' overall rating score and how the location rating values are distributed.

Style the map layers depending on other variables to have a better understanding on how different variables influence model results.

Enrich your visualization using CARTO Spatial Features

So far we have seen how the Airbnb listings locations and its main variables are distributed across the city of Los Angeles. Next, we will enrich our visualization by adding CARTO Spatial Features H3 at resolution 8 dataset from CARTO Data Observatory.

This dataset holds information that can be useful to explore the influence of different factors, including variables such as the total population, the urbanity level or the presence of certain type of points of interests in different areas.

To save map results and continue working on a separate map, let's duplicate the previous map once again, and disable the dual map view (close the left panel), then rename the map copy to Map 3 Airbnb Spatial Features
From the main menu, click on ‘Data Observatory’ to browse the Spatial Data Catalog and apply these filters:
- Countries: United States of America
- Licenses: Public data
- Sources: CARTO
Select the Spatial Features - United States of America (H3 Resolution 8) dataset and click on Subscribe for free. This action will redirect us to the subscription level at the Data Explorer menu.

From Data Explorer, use "Access In" functionality for BigQuery and copy the SQL code. We will use it to add this dataset as a new SQL Query source.

Go back to your Builder map, close and paste the copied SQL Query as a new source, using "Add Source from..." button. Once a layer has been added, zoom to Los Angeles.

Style the layer to have color opacity 0 in order to keep it hidden while displaying the information in the pop-up and the widgets that we will add next.

Tip: Optionally, style the layer as desired to visualize how different variables behave across the territory.

Rename your layer to Spatial Features and add the following widgets to the map:

Total listings
- Layer: Airbnb listings
- Type: Formula
- Operation: COUNT
- Note: Total nº of Airbnb listings in the map extent.

Population Spatial Features
- Layer: Spatial Features
- Type: Formula
- Operation: SUM
- Aggregation column: population

Urbanity level
- Layer: Spatial Features
- Type: Pie
- Operation: COUNT
- Column: urbanity

Add a hoover interaction so users can review attributes while navigating the map.

Navigate the map and observe how widget values vary depending on the viewport area. Check out specific areas by hovering over them and review pop-up attributes.

Now let's add a rich description using Markdown syntax. You can use the below example or create your own.

### Airbnb Ratings and Location Impact 🌟

![Image: Global Populated Places](https://app-gallery.cartocdn.com/builder/LosAngeles.jpg)

Explore the intricate relationship between Airbnb ratings and the geographical distribution of listings in Los Angeles with our dynamic map. This map provides valuable insights into what influences user ratings and offers a comprehensive view of the city's Airbnb landscape.

**Discover User Ratings** 📊
- Analyze how Airbnb users rate listings based on key factors such as accuracy, communication, cleanliness, location, check-in, and value.
- Visualize the distribution of ratings to uncover patterns that affect overall user impressions.

**Geographic Insights** 🗺️
- Dive into Los Angeles neighborhoods and observe how specific areas impact user ratings.
- Identify regions where location ratings significantly influence the overall score, and explore what makes these neighborhoods stand out.

**Sociodemographic Data Enrichment**
- Enhance your understanding of each neighborhood with sociodemographic insights from the CARTO Data Observatory.
- Access data on total population, urbanity level, tourism presence, and more to gain a holistic view of the city's dynamics.

The output map will be similar to this:

Finally we can make the map public and share the link to anybody in the organization. For that you should go to “Share” on the top right corner and set the map as Public. For more details, see Publishing and sharing maps.

Now we are ready to visualize to share and visualize the results!

Last updated 3 months ago