Tutorial - Housing Data Analytics & Business Intelligence

Discover how to analyze rental property data using Tacnode's powerful analytics tools. Learn to extract actionable business insights and make data-driven decisions for your rental platform.

Introduction

Data is the lifeblood of modern business. In today’s competitive rental marketplace, the ability to extract meaningful insights from property data, user behavior, and market trends can make the difference between success and failure. This tutorial builds upon our previous work in Information Enrichment & Omni Search to create a comprehensive analytics framework for rental platforms.

Using Tacnode’s powerful analytical capabilities combined with JSONB flexibility, we’ll transform raw data into actionable business intelligence that drives growth, improves user experience, and optimizes operations.

The Value of Data Analytics in Rental Platforms

Strategic Business Benefits

Market Intelligence: Understanding property performance, pricing trends, and user preferences enables strategic decision-making.

Operational Optimization: Identifying top-performing hosts, popular amenities, and booking patterns helps optimize platform operations.

User Experience Enhancement: Analyzing user behavior and feedback improves search algorithms and recommendation systems.

Revenue Growth: Data-driven insights enable better pricing strategies, marketing campaigns, and feature development.

Tacnode’s Analytical Advantages

Tacnode provides several key advantages for rental platform analytics:

• JSONB Flexibility: Efficiently analyze semi-structured property and user data
• SQL Analytics: Leverage familiar SQL for complex analytical queries
• Performance: Handle large datasets with excellent query performance
• Scalability: Grow analytics capabilities alongside your platform

Core Analytics Scenarios

Let’s explore essential analytics scenarios that every rental platform needs to understand their business performance and make informed decisions.

1. Business Growth & Trend Analysis

Recent Listing Growth by Amenities

Business Question: How many new properties with premium amenities (like “Free WiFi” and “Elevator”) were added in the last month?

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;

This analysis helps identify growth trends in premium property segments and informs marketing strategies.

Host Verification Method Effectiveness

Business Question: Which verification methods correlate with the most property listings?

WITH VerificationCounts AS (
    SELECT 
        host_id,
        jsonb_array_elements_text(host_verifications_jsonb) AS verification_method
    FROM listings
    WHERE host_verifications_jsonb IS NOT NULL
)
SELECT 
    verification_method, 
    COUNT(DISTINCT host_id) AS host_count,
    ROUND(COUNT(DISTINCT host_id) * 100.0 / 
          (SELECT COUNT(DISTINCT host_id) FROM listings), 2) AS percentage
FROM VerificationCounts
GROUP BY verification_method
ORDER BY host_count DESC;

Expected Results:

verification_method | host_count | percentage
-------------------+-----------+-----------
email              |       2,847|      68.4
phone              |       2,156|      51.8
work_email         |         892|      21.4
government_id      |         634|      15.2

This reveals which verification methods are most popular and effective for building host trust.

2. Host Performance Analysis

High-Quality Host Identification

Business Question: Which hosts maintain excellent response rates and verified identities?

SELECT 
    id,
    host_name,
    host_response_rate,
    host_about,
    number_of_reviews,
    review_scores_rating
FROM listings
WHERE 
    host_response_rate > '90%'
    AND host_identity_verified = 't'
    AND review_scores_rating >= 4.5
ORDER BY review_scores_rating DESC, number_of_reviews DESC;

This identifies top-performing hosts who could be featured in marketing campaigns or invited to premium host programs.

Verification Method Impact on Ratings

Business Question: Do certain verification methods lead to higher property ratings?

WITH HostAverageRating AS (
    SELECT 
        host_id,
        AVG(review_scores_rating) AS avg_rating,
        COUNT(*) as property_count
    FROM listings
    WHERE review_scores_rating IS NOT NULL
    GROUP BY host_id
    HAVING COUNT(*) >= 2  -- Hosts with multiple properties
),
VerificationRatings AS (
    SELECT 
        jsonb_array_elements_text(l.host_verifications_jsonb) AS verification_method,
        h.avg_rating,
        h.property_count
    FROM listings l
    JOIN HostAverageRating h ON l.host_id = h.host_id
    WHERE l.host_verifications_jsonb IS NOT NULL
)
SELECT 
    verification_method,
    ROUND(AVG(avg_rating), 2) as average_rating,
    COUNT(*) as host_count,
    ROUND(AVG(property_count), 1) as avg_properties_per_host
FROM VerificationRatings
GROUP BY verification_method
HAVING COUNT(*) >= 50  -- Statistically significant sample
ORDER BY average_rating DESC;

Expected Results:

verification_method | average_rating | host_count | avg_properties_per_host
-------------------+---------------+-----------+------------------------
government_id      |           4.73|        234|                     2.3
work_email         |           4.68|        445|                     2.1
phone              |           4.52|        892|                     1.8
email              |           4.48|      1,234|                     1.7

This analysis reveals that government ID verification correlates with higher ratings, suggesting enhanced trust.

3. Property Feature & Market Analysis

Most Desired Amenities Analysis

Business Question: Which amenities do guests value most?

WITH AmenityPopularity AS (
    SELECT 
        jsonb_array_elements_text(amenities) AS amenity,
        COUNT(id) AS listings_count,
        AVG(review_scores_rating) as avg_rating,
        AVG(price::numeric) as avg_price
    FROM listings
    WHERE amenities IS NOT NULL
    GROUP BY amenity
    HAVING COUNT(id) >= 100  -- Statistically significant
)
SELECT 
    amenity,
    listings_count,
    ROUND(avg_rating, 2) as avg_rating,
    ROUND(avg_price, 0) as avg_price_hkd,
    ROUND(listings_count * 100.0 / (SELECT COUNT(*) FROM listings), 1) as market_penetration_pct
FROM AmenityPopularity
ORDER BY listings_count DESC
LIMIT 15;

Expected Results:

amenity           | listings_count | avg_rating | avg_price_hkd | market_penetration_pct
-----------------+--------------+----------+--------------+----------------------
Wifi             |         5,234|      4.52|          892|                   77.8
Kitchen          |         4,567|      4.48|          1,045|                  67.8
Air conditioning |         3,892|      4.56|          978|                   57.8
Washer           |         3,445|      4.51|          1,123|                  51.2
Elevator         |         2,789|      4.59|          1,234|                  41.4

This reveals that WiFi and Kitchen are essential amenities, while Elevator commands premium pricing.

Location-Based Amenity Search

Business Question: Find properties in specific areas with desired amenities for targeted marketing.

SELECT 
    id, 
    name, 
    neighbourhood,
    price,
    review_scores_rating
FROM listings
WHERE 
    amenities @> '["Wifi", "Pets allowed"]' 
    AND neighbourhood = 'Hong Kong, Hong Kong Island, Hong Kong'
    AND has_availability = true
ORDER BY review_scores_rating DESC, price ASC;

This enables location-specific marketing campaigns and inventory management.

4. Guest Experience & Feedback Analysis

Top-Rated Properties by Location

Business Question: Which properties should we feature in each neighborhood?

WITH RankedProperties AS (
    SELECT 
        id,
        name,
        neighbourhood_cleansed,
        review_scores_rating,
        number_of_reviews,
        price,
        RANK() OVER (
            PARTITION BY neighbourhood_cleansed 
            ORDER BY review_scores_rating DESC, number_of_reviews DESC
        ) as ranking
    FROM listings
    WHERE 
        review_scores_rating IS NOT NULL
        AND number_of_reviews >= 10  -- Ensure reliable ratings
        AND has_availability = true
)
SELECT 
    neighbourhood_cleansed,
    name,
    review_scores_rating,
    number_of_reviews,
    price
FROM RankedProperties
WHERE ranking <= 3  -- Top 3 per neighborhood
ORDER BY neighbourhood_cleansed, ranking;

This identifies standout properties in each area for featuring in search results and marketing materials.

Review Sentiment Analysis

Business Question: What specific amenities do guests mention positively in reviews?

SELECT 
    r.listing_id,
    l.name,
    r.comments,
    l.amenities
FROM reviews r
JOIN listings l ON r.listing_id = l.id
WHERE 
    l.amenities @> '["Wifi"]'
    AND (
        r.comments ILIKE '%wifi%' 
        OR r.comments ILIKE '%internet%'
        OR r.comments ILIKE '%connection%'
    )
    AND (
        r.comments ILIKE '%excellent%'
        OR r.comments ILIKE '%fast%'
        OR r.comments ILIKE '%reliable%'
    )
ORDER BY l.review_scores_rating DESC
LIMIT 10;

This helps identify which amenities generate positive guest feedback and should be highlighted in marketing.

5. Advanced Business Intelligence Queries

Revenue Potential Analysis

Business Question: Which property characteristics correlate with higher pricing?

WITH PropertyMetrics AS (
    SELECT 
        id,
        price::numeric as price_numeric,
        beds,
        accommodates,
        review_scores_rating,
        neighbourhood_cleansed,
        room_type,
        jsonb_array_length(amenities) as amenity_count,
        CASE 
            WHEN amenities @> '["Pool"]' THEN 1 ELSE 0 
        END as has_pool,
        CASE 
            WHEN amenities @> '["Hot tub"]' THEN 1 ELSE 0 
        END as has_hot_tub,
        CASE 
            WHEN amenities @> '["Gym"]' THEN 1 ELSE 0 
        END as has_gym
    FROM listings
    WHERE price IS NOT NULL AND amenities IS NOT NULL
)
SELECT 
    room_type,
    ROUND(AVG(price_numeric), 0) as avg_price,
    ROUND(AVG(amenity_count), 1) as avg_amenity_count,
    ROUND(AVG(has_pool) * 100, 1) as pool_percentage,
    ROUND(AVG(has_hot_tub) * 100, 1) as hot_tub_percentage,
    ROUND(AVG(has_gym) * 100, 1) as gym_percentage,
    COUNT(*) as property_count
FROM PropertyMetrics
GROUP BY room_type
ORDER BY avg_price DESC;

Expected Results:

room_type        | avg_price | avg_amenity_count | pool_percentage | hot_tub_percentage | gym_percentage | property_count
----------------+----------+------------------+----------------+-------------------+---------------+---------------
Entire home/apt |     1,245|              23.4|             8.2|               12.4|            5.7|          3,456
Private room    |       567|              18.9|             2.1|                3.8|            2.1|          2,123
Shared room     |       234|              14.2|             0.8|                1.2|            1.2|            234

This reveals that entire homes command premium prices and luxury amenities significantly impact pricing.

Market Saturation Analysis

Business Question: Which neighborhoods have the best opportunity for new listings?

WITH NeighborhoodMetrics AS (
    SELECT 
        neighbourhood_cleansed,
        COUNT(*) as total_listings,
        AVG(review_scores_rating) as avg_rating,
        AVG(price::numeric) as avg_price,
        AVG(number_of_reviews) as avg_review_count,
        SUM(CASE WHEN has_availability = true THEN 1 ELSE 0 END) as available_listings
    FROM listings
    WHERE neighbourhood_cleansed IS NOT NULL
    GROUP BY neighbourhood_cleansed
    HAVING COUNT(*) >= 20  -- Focus on established markets
)
SELECT 
    neighbourhood_cleansed,
    total_listings,
    ROUND(avg_rating, 2) as avg_rating,
    ROUND(avg_price, 0) as avg_price_hkd,
    ROUND(avg_review_count, 1) as avg_review_count,
    ROUND(available_listings * 100.0 / total_listings, 1) as availability_rate,
    CASE 
        WHEN avg_price > 1000 AND avg_rating > 4.5 THEN 'Premium Market'
        WHEN avg_price > 800 AND total_listings < 100 THEN 'Growth Opportunity'
        WHEN availability_rate > 80 THEN 'High Supply'
        ELSE 'Balanced Market'
    END as market_category
FROM NeighborhoodMetrics
ORDER BY avg_price DESC, avg_rating DESC;

This analysis identifies market opportunities and helps guide expansion strategies.

6. Operational Analytics

Host Performance Monitoring

Business Question: Which hosts need support to improve their performance?

WITH HostPerformance AS (
    SELECT 
        host_id,
        host_name,
        COUNT(*) as property_count,
        AVG(review_scores_rating) as avg_rating,
        AVG(number_of_reviews::numeric) as avg_review_count,
        host_response_rate,
        host_is_superhost,
        MAX(last_scraped) as last_activity
    FROM listings
    WHERE host_id IS NOT NULL
    GROUP BY host_id, host_name, host_response_rate, host_is_superhost
    HAVING COUNT(*) >= 2  -- Multi-property hosts
)
SELECT 
    host_name,
    property_count,
    ROUND(avg_rating, 2) as avg_rating,
    ROUND(avg_review_count, 1) as avg_review_count,
    host_response_rate,
    host_is_superhost,
    last_activity,
    CASE 
        WHEN avg_rating < 4.0 THEN 'Needs Rating Improvement'
        WHEN host_response_rate < '80%' THEN 'Needs Response Improvement'
        WHEN avg_review_count < 5 THEN 'Needs More Bookings'
        WHEN avg_rating >= 4.5 AND host_response_rate >= '90%' THEN 'Top Performer'
        ELSE 'Good Performance'
    END as performance_category
FROM HostPerformance
ORDER BY avg_rating ASC, host_response_rate ASC;

This identifies hosts who need support and those who deserve recognition or premium features.

Performance Optimization for Analytics

Analytical Indexes

-- Index for amenity analysis
CREATE INDEX idx_analytics_amenities ON listings USING GIN(amenities);

-- Index for host analysis
CREATE INDEX idx_analytics_host ON listings(host_id, review_scores_rating, last_scraped);

-- Index for neighborhood analysis
CREATE INDEX idx_analytics_neighborhood ON listings(neighbourhood_cleansed, price, review_scores_rating);

-- Composite index for complex analytics
CREATE INDEX idx_analytics_composite ON listings(has_availability, room_type, price) 
INCLUDE (review_scores_rating, number_of_reviews);

Materialized Views for Heavy Analytics

For frequently-run analytics queries, consider materialized views:

-- Create materialized view for amenity popularity
CREATE MATERIALIZED VIEW mv_amenity_analytics AS
SELECT 
    jsonb_array_elements_text(amenities) AS amenity,
    COUNT(*) AS listings_count,
    AVG(review_scores_rating) as avg_rating,
    AVG(price::numeric) as avg_price,
    COUNT(CASE WHEN has_availability THEN 1 END) as available_count
FROM listings
WHERE amenities IS NOT NULL
GROUP BY amenity;

-- Refresh periodically
REFRESH MATERIALIZED VIEW mv_amenity_analytics;

Real-Time Analytics Dashboard Queries

Key Performance Indicators (KPIs)

-- Platform overview dashboard
SELECT 
    'Total Properties' as metric,
    COUNT(*)::text as value
FROM listings
UNION ALL
SELECT 
    'Available Properties',
    COUNT(*)::text
FROM listings WHERE has_availability = true
UNION ALL
SELECT 
    'Average Rating',
    ROUND(AVG(review_scores_rating), 2)::text
FROM listings WHERE review_scores_rating IS NOT NULL
UNION ALL
SELECT 
    'Total Reviews',
    COUNT(*)::text
FROM reviews
UNION ALL
SELECT 
    'Active Hosts',
    COUNT(DISTINCT host_id)::text
FROM listings;

Revenue Metrics

-- Revenue analysis dashboard
SELECT 
    DATE_TRUNC('month', last_scraped) as month,
    COUNT(*) as new_listings,
    AVG(price::numeric) as avg_price,
    SUM(price::numeric) as total_potential_revenue
FROM listings
WHERE last_scraped >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', last_scraped)
ORDER BY month DESC;

Conclusion

Through this comprehensive analytics tutorial, we’ve demonstrated how Tacnode enables sophisticated business intelligence for rental platforms:

Key Analytics Capabilities Achieved

1. Growth Analysis: Track new listings, market expansion, and trend identification 2. Host Performance: Monitor host quality, identify top performers, and provide targeted support 3. Market Intelligence: Understand amenity demand, pricing trends, and competitive positioning 4. User Experience Optimization: Analyze guest feedback and improve search/recommendation systems 5. Operational Efficiency: Identify operational bottlenecks and optimization opportunities

Business Value Delivered

• Data-Driven Decision Making: Replace gut feelings with concrete analytics
• Revenue Optimization: Identify pricing opportunities and high-value market segments
• User Experience Enhancement: Improve platform features based on real user behavior
• Competitive Advantage: Leverage unique insights unavailable to competitors
• Scalable Analytics: Foundation that grows with your platform

Technical Excellence

• Performance: Optimized queries handle large datasets efficiently
• Flexibility: JSONB enables analysis of complex, evolving data structures
• Scalability: Architecture supports growing data volumes and analytical complexity
• Integration: SQL-based analytics integrate seamlessly with existing applications

Our rental platform has evolved from basic property management to a sophisticated, data-driven marketplace. By combining Tacnode’s powerful database capabilities with thoughtful analytics design, we’ve created a foundation for continued growth and innovation in the competitive rental industry.

The insights generated from these analytics enable informed strategic decisions, improved user experiences, and sustainable business growth - transforming data from a byproduct into a core business asset.