Real Estate Analytics: Price Prediction & Market Insights

# Lead conversion funnel
monthly_leads = 10000000  # 1 crore leads/month
serious_buyer_rate = 0.10  # 10% serious
conversion_rate = 0.25  # 25% of serious buyers transact
avg_property_value = 8000000  # ₹80 lakh
platform_commission = 0.015  # 1.5%

successful_transactions = monthly_leads * serious_buyer_rate * conversion_rate
# 250,000 transactions/month

revenue = successful_transactions * avg_property_value * platform_commission
# ₹3,000 crore/month

# Problem: Sales team can't follow up with 1 crore leads
# Need to identify 10% serious buyers (10 lakh leads) for targeted outreach

# Property listing data
{
  "property_id": "P12345",
  "location": "Koramangala, Bangalore",
  "lat": 12.9279,
  "lon": 77.6271,
  "bedrooms": 2,
  "bathrooms": 2,
  "sqft": 1050,
  "floor": 5,
  "total_floors": 12,
  "age_years": 3,
  "amenities": ["gym", "pool", "parking", "security", "clubhouse"],
  "facing": "East",
  "furnishing": "Semi-furnished",
  "listed_price": 9500000  # ₹95 lakh
}

# Historical transaction data (sold properties)
{
  "property_id": "P98765",
  "sale_price": 8200000,  # Actual sale price (vs listed price)
  "sale_date": "2026-01-15",
  "days_to_sell": 45,
  "price_per_sqft": 7810  # ₹7,810/sqft
}

import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor
from sklearn.metrics import mean_absolute_error, r2_score
import matplotlib.pyplot as plt

# Load historical sales data
data = pd.read_csv('bangalore_properties.csv')

# Feature engineering
data['age_years'] = 2026 - data['year_built']
data['price_per_sqft'] = data['sale_price'] / data['sqft']
data['has_gym'] = data['amenities'].str.contains('gym').astype(int)
data['has_pool'] = data['amenities'].str.contains('pool').astype(int)
data['has_parking'] = data['amenities'].str.contains('parking').astype(int)

# Location encoding (one-hot encode top localities)
top_localities = data['locality'].value_counts().head(20).index
data['locality_encoded'] = data['locality'].apply(
    lambda x: x if x in top_localities else 'Other'
)
data = pd.get_dummies(data, columns=['locality_encoded'], drop_first=True)

# Select features
features = [
    'sqft', 'bedrooms', 'bathrooms', 'floor', 'total_floors',
    'age_years', 'has_gym', 'has_pool', 'has_parking'
] + [col for col in data.columns if col.startswith('locality_encoded_')]

X = data[features]
y = data['sale_price']

# Train-test split
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

# Train model (Gradient Boosting performs best for real estate)
model = GradientBoostingRegressor(
    n_estimators=200,
    max_depth=5,
    learning_rate=0.1,
    random_state=42
)
model.fit(X_train, y_train)

# Evaluate
y_pred = model.predict(X_test)
mae = mean_absolute_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)

print(f"Mean Absolute Error: ₹{mae:,.0f}")
print(f"R² Score: {r2:.3f}")

# Output:
# Mean Absolute Error: ₹6,50,000
# R² Score: 0.85

# Interpretation:
# - Model explains 85% of price variance
# - Average prediction error: ₹6.5 lakh (on ₹80 lakh avg property = ±8% error)

# Feature importance
feature_importance = pd.DataFrame({
    'feature': features,
    'importance': model.feature_importances_
}).sort_values('importance', ascending=False)

print("\nTop 10 Most Important Features:")
print(feature_importance.head(10))

# Output:
#              feature  importance
# 0               sqft       0.35
# 8  locality_encoded_Koramangala  0.18
# 3              floor       0.12
# 5          age_years       0.09
# 1           bedrooms       0.08
# ...

# Predict price for new property
new_property = pd.DataFrame({
    'sqft': [1050],
    'bedrooms': [2],
    'bathrooms': [2],
    'floor': [5],
    'total_floors': [12],
    'age_years': [3],
    'has_gym': [1],
    'has_pool': [1],
    'has_parking': [1],
    'locality_encoded_Koramangala': [1],
    'locality_encoded_Whitefield': [0],
    # ... (all other locality columns = 0)
})

predicted_price = model.predict(new_property)
print(f"\nPredicted Price: ₹{predicted_price[0]:,.0f}")
# Output: Predicted Price: ₹92,50,000

-- Monthly price trends by locality (last 24 months)
WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', sale_date) AS sale_month,
    locality,
    AVG(sale_price / sqft) AS avg_price_per_sqft,
    COUNT(*) AS transactions,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sale_price / sqft) AS median_price_per_sqft
  FROM properties
  WHERE sale_date >= CURRENT_DATE - INTERVAL '24 months'
    AND sale_price IS NOT NULL
  GROUP BY DATE_TRUNC('month', sale_date), locality
)

SELECT
  sale_month,
  locality,
  avg_price_per_sqft,
  median_price_per_sqft,
  transactions,
  -- Month-over-month growth
  (avg_price_per_sqft - LAG(avg_price_per_sqft) OVER (
    PARTITION BY locality ORDER BY sale_month
  )) * 100.0 / LAG(avg_price_per_sqft) OVER (
    PARTITION BY locality ORDER BY sale_month
  ) AS mom_growth_pct,
  -- Year-over-year growth
  (avg_price_per_sqft - LAG(avg_price_per_sqft, 12) OVER (
    PARTITION BY locality ORDER BY sale_month
  )) * 100.0 / LAG(avg_price_per_sqft, 12) OVER (
    PARTITION BY locality ORDER BY sale_month
  ) AS yoy_growth_pct
FROM monthly_sales
WHERE locality IN ('Koramangala', 'Whitefield', 'Indiranagar', 'HSR Layout')
ORDER BY locality, sale_month DESC;

from sklearn.linear_model import LogisticRegression
from sklearn.metrics import classification_report, roc_auc_score

# Load lead behavior data
leads_data = pd.DataFrame({
    'user_id': range(10000),
    'searches_count': np.random.poisson(3, 10000),
    'properties_viewed': np.random.poisson(5, 10000),
    'contact_clicks': np.random.poisson(0.5, 10000),
    'site_visits_count': np.random.poisson(2, 10000),
    'days_since_first_visit': np.random.randint(1, 90, 10000),
    'avg_property_budget': np.random.normal(8000000, 2000000, 10000),
    'has_home_loan_search': np.random.choice([0, 1], 10000, p=[0.7, 0.3]),
    'converted': np.random.choice([0, 1], 10000, p=[0.9, 0.1])  # 10% conversion
})

# Feature engineering
leads_data['engagement_score'] = (
    leads_data['searches_count'] * 0.2 +
    leads_data['properties_viewed'] * 0.3 +
    leads_data['contact_clicks'] * 1.5 +
    leads_data['site_visits_count'] * 0.1
)

# Features for model
X_leads = leads_data[[
    'searches_count', 'properties_viewed', 'contact_clicks',
    'site_visits_count', 'days_since_first_visit',
    'has_home_loan_search', 'engagement_score'
]]
y_leads = leads_data['converted']

X_train, X_test, y_train, y_test = train_test_split(
    X_leads, y_leads, test_size=0.3, random_state=42
)

# Train logistic regression
lead_model = LogisticRegression(max_iter=1000)
lead_model.fit(X_train, y_train)

# Predict conversion probability
y_pred_proba = lead_model.predict_proba(X_test)[:, 1]

# Classify leads into tiers
def classify_lead(probability):
    if probability >= 0.30:
        return 'HOT'  # Top 5-10% (high conversion probability)
    elif probability >= 0.15:
        return 'WARM'  # Next 15-20%
    else:
        return 'COLD'  # Bottom 70%

leads_data['conversion_probability'] = lead_model.predict_proba(X_leads)[:, 1]
leads_data['lead_tier'] = leads_data['conversion_probability'].apply(classify_lead)

print(leads_data['lead_tier'].value_counts())

# Output:
# COLD     7,200 (72%)
# WARM     2,000 (20%)
# HOT        800 (8%)

# ROI: Sales team focuses on 800 HOT leads (instead of 10,000 total)
# Conversion rate: HOT leads 40%, WARM 15%, COLD 3%

# Before lead scoring
monthly_leads = 100000
sales_reps = 50
leads_per_rep_per_month = monthly_leads / sales_reps  # 2,000 leads
conversion_rate = 0.10
avg_commission = 120000  # ₹1.2 lakh per transaction
revenue_per_rep = leads_per_rep_per_month * conversion_rate * avg_commission
# ₹24 lakh/rep/month

total_revenue = revenue_per_rep * sales_reps
# ₹12 crore/month

# After lead scoring (focus on HOT/WARM = 28% of leads)
high_quality_leads = monthly_leads * 0.28  # 28,000 leads
leads_per_rep_per_month_new = high_quality_leads / 20  # 1,400 leads/rep (20 reps)
conversion_rate_new = 0.28  # Higher (targeted leads)
revenue_per_rep_new = leads_per_rep_per_month_new * conversion_rate_new * avg_commission
# ₹47 lakh/rep/month

total_revenue_new = revenue_per_rep_new * 20
# ₹9.4 crore/month

# Note: Revenue down (₹12 crore → ₹9.4 crore) but COST down even more
# Sales team cost: 50 reps × ₹50K salary = ₹25 lakh → 20 reps = ₹10 lakh
# Net profit margin improves (fewer reps, similar revenue)

# Example: Should buyer wait 6 months or buy now?
current_price_per_sqft = 8500
forecasted_6m_price = 8800  # +3.5% in 6 months
home_loan_rate = 0.09  # 9% annual (0.75% monthly)

# Cost of waiting
appreciation_cost = (forecasted_6m_price - current_price_per_sqft) * 1000  # For 1000 sqft
# ₹3,00,000 (property becomes more expensive)

# Savings from waiting (if rent < loan EMI)
monthly_rent = 25000
monthly_emi_on_80L_loan = 72000  # EMI on ₹80L loan @ 9% for 20 years
net_savings_per_month = monthly_emi_on_80L_loan - monthly_rent
# ₹47,000/month × 6 months = ₹2,82,000

# Decision: Appreciation cost (₹3L) > Rent savings (₹2.8L) → BUY NOW
# If forecasted appreciation was <3%, waiting would be better