How I Built a Real Estate ROI Calculator Using Supabase & Looker Studio

When investing in real estate, raw property prices only tell half the story. To identify the most profitable investment opportunities in Poprad (Slovakia), I built an end-to-end data pipeline and interactive BI dashboard.

By combining Supabase (PostgreSQL) for data storage and transformations with Google Looker Studio for visualization, this project provides property investors with real-time ROI calculations and geographic price distribution.

Key Finding: While the Veľká district commands the highest average price per m², older 1-bedroom apartments near the city center offer the highest annual ROI (~5.8% to 10% depending on specific market conditions).

---

🛠️ Tech Stack & Architecture

The architecture was kept lean, robust, and scalable:

1. Database Layer: Supabase (PostgreSQL) — data storage, cleaning, and aggregation.
2. Analytical Layer: Advanced SQL — window functions (LAG), date truncations, and anomaly filtering.
3. BI & Visualization: Google Looker Studio — custom calculated fields (ROI%, Estimated Rent) and interactive UI.
4. Documentation & Version Control: GitHub Markdown & Public Repository.

---

🔍 Data Cleaning & SQL Transformations

Raw data often contains outliers (e.g., mispriced listings or inaccurate square footage). Before building visualizations, I filtered out non-representative listings using custom SQL constraints.

1. Removing Price Anomalies

sql
SELECT * 
FROM poprad_reality
WHERE (cena_eur / rozloha_m2) BETWEEN 800 AND 6000;

2. Year-over-Year Growth Trend (Window Functions)
To analyze how prices evolved over time, I implemented the LAG window function to calculate percentage growth year-over-year:

SQL
WITH yearly_averages AS (
    SELECT 
        EXTRACT(YEAR FROM datum_inzercie::date) AS year,
        AVG(cena_eur / rozloha_m2) AS price_per_m2
    FROM poprad_reality
    WHERE typ_nehnutelnosti LIKE '%byt%'
    GROUP BY EXTRACT(YEAR FROM datum_inzercie::date)
)
SELECT 
    year,
    ROUND(price_per_m2::numeric, 2) AS avg_price_m2,
    ROUND((LAG(price_per_m2) OVER (ORDER BY year))::numeric, 2) AS previous_year_price,
    ROUND((((price_per_m2 - LAG(price_per_m2) OVER (ORDER BY year)) / LAG(price_per_m2) OVER (ORDER BY year)) * 100)::numeric, 2) AS yoy_growth_percent
FROM yearly_averages;
📊 BI Dashboard & Interactive ROI Calculator
In Google Looker Studio, I designed custom metrics to evaluate financial viability directly on the dashboard:

Estimated Annual Rent (€): rozloha_m2 * 10 * 12 (based on a conservative average market rent of 10 €/m²/month)

ROI (%): (rozloha_m2 * 10 * 12) / cena_eur

Key UI Features:
Interactive Slicers: Filter instantly by location (lokalita) and property type (typ_nehnutelnosti).

KPI Scorecards: Overview of global average ROI and pricing metrics.

Granular Breakdown Table: Location-based aggregation showing average area (m²), calculated ROI (%), and total price (€).

Comparative Bar Chart: Side-by-side ROI distribution across property categories.

💡 Business Recommendations for Investors
High-Yield Target: Focus on 1-room and 2-room apartments in high-density residential areas (Poprad - Juh, Centrum). They provide the best balance between purchase price and steady rental yield.

Capital Appreciation vs. Yield: Properties in Veľká offer great long-term value preservation due to proximity to the High Tatras, but yield lower immediate rental returns due to higher acquisition costs.

🔗 Live Links & Repository
📊 Interactive Looker Studio Dashboard

📁 GitHub Repository