Tutorial - Housing Data Analytics

As data volumes grow, extracting meaningful insights becomes crucial. Previous articles explored using Tacnode to establish a rental system and perform omni queries on properties. In this piece, we will delve deeper into data analysis, showcasing how to utilize Tacnode to uncover business insights that inform decisions for the rental platform.

Background

The value of data

In today's information age, data has become the new currency. Every interaction with online rental platforms—from user behavior to housing details—produces valuable data. Analyzing this data effectively can uncover user needs, forecast market trends, enhance service experiences, and propel business growth.

Strong Analytical Capability

Tacnode is a cloud-native database solution offering efficient data storage, querying, and robust data analysis tools. Its jsonb features simplify the handling of semi-structured data while maintaining SQL's strong analytical capabilities.

Analysis Scenarios

We will examine various data analysis scenarios. Studying this chapter will enable you to utilize Tacnode for comprehensive data analysis, turning data into actionable strategies and decisions. Together, we will delve into the significance of data analysis and implement it in real business situations.

Business Growth and Trend Analysis

a. Assess the recent month’s new listings with particular amenities

Scenario Description: Management seeks to understand the number of new listings offering both "Free Wifi" and "Elevator" in the last month to evaluate trends in business growth.

SELECT COUNT(id) as newly_listed_properties
FROM listings
WHERE amenities @> '["Wifi", "Elevator"]'
    AND last_scraped BETWEEN CURRENT_DATE - INTERVAL '30 days' AND CURRENT_DATE;

b. Analyzing the correlation between the number of listings a landlord has and their verification method

Scenario description: To identify the most effective verification method, we aim to tally the listings owned by hosts according to each verification method.

WITH VerificationCounts AS (
   SELECT host_id,
          JSONB_ARRAY_ELEMENTS_TEXT(host_verifications_jsonb) AS verification_method
   FROM listings
)
SELECT verification_method, COUNT(DISTINCT host_id) AS host_count
FROM VerificationCounts
GROUP BY verification_method
ORDER BY host_count DESC;

Host (Landlord) Profile Assessment

a. Evaluate hosts with high response rates and verified identities

Scenario description: For an optimal user experience, a homestay platform should consider screening hosts who frequently respond and have confirmed their identities.

SELECT id, host_name, host_response_rate, host_about
FROM listings
WHERE host_response_rate > '90%'
AND host_identity_verified = 't';

b. Average rating of host listings based on specific verification methods

Scenario description: To determine which verification methods lead to higher ratings for hosts, we can compute the average listing ratings according to their verification method.

WITH HostAverageRating AS (
    SELECT host_id,
           AVG(review_scores_rating) AS avg_rating
    FROM listings
    GROUP BY host_id
)
SELECT JSONB_ARRAY_ELEMENTS_TEXT(l.host_verifications_jsonb) AS verification_method,
       AVG(h.avg_rating) as average_rating
FROM listings l
JOIN HostAverageRating h ON l.host_id = h.host_id
GROUP BY verification_method
ORDER BY average_rating DESC;

Analysis of Property Features and Preferences

a. Identify the Most Desired Amenities

Scenario description: A homestay platform aims to identify the amenities that tenants prefer most to enhance its listing suggestions.

SELECT JSONB_ARRAY_ELEMENTS_TEXT(amenities) AS amenity,
       COUNT(id) AS listings_count
FROM listings
GROUP BY amenity
ORDER BY listings_count DESC;

b. Narrow down listings by location and available amenities

Scenario description: A tenant seeks to reserve a property in a particular borough that provides free WiFi and permits pets.

SELECT id, name, neighbourhood
FROM listings
WHERE amenities @> '["Wifi", "Pets allowed"]' AND neighbourhood = 'Hong Kong, Hong Kong Island, Hong Kong';

Analysis of Comments and Feedback

a. Identify the top-rated properties in a given borough

Scenario description: A tenant aims to reserve the highest-rated listing within a particular borough.

SELECT id, name, review_scores_rating
FROM listings
WHERE neighbourhood = 'Hong Kong, Hong Kong Island, Hong Kong' ORDER BY review_scores_rating DESC
LIMIT 5;

b. Examine particular amenities noted in reviews

Scenario description: A homestay platform may be interested in whether guests reference specific amenities, like "Wifi", in their feedback.

SELECT r.listing_id, r.comments
FROM reviews r
JOIN listings l ON r.listing_id = l.id
WHERE l.amenities @> '["Wifi"]' AND r.comments ILIKE '%wifi%';

Conclusion

Our evolution from constructing the rental system's foundational architecture to incorporating dynamic house search and evaluation functionalities has positioned us at the cutting edge of data analysis. This article delves into how to leverage Tacnode's jsonb functions and SQL analysis tools to extract valuable insights from our data. Using real-world data and scenarios, we can uncover patterns in user behavior, assess house performance, and anticipate market trends.

As our data grows, so will the sophistication and depth of our analysis. Tacnode equips us to process and analyze extensive data sets, showing us how to enhance business processes and boost customer satisfaction through informed, data-driven decisions.

We aim to refine our analytical skills further, investigate more complex data analysis techniques, and implement them within our rental platform. Stay tuned for our upcoming article, where we'll provide detailed tutorials on data analysis, empowering you to leverage these insights as a competitive edge for your business.