🌱 EcoInvest Analytics — SQL Portfolio Project

Marketing × Sustainability Finance | A data marketing analyst's deep-dive into a fictional green investment platform using SQL — from acquisition to retention.

---

📌 Project Overview

EcoInvest is a fictional green finance platform connecting retail and institutional investors with ESG-rated sustainable projects (solar, wind, green bonds, carbon credits, etc.).

This project simulates a real marketing analytics workflow:
→ understand the user base → measure campaign performance → segment investors → optimize retention

Table	Rows	Description
investors	800	Demographics, segment, acquisition channel, signup date
green_projects	40	ESG score, sector, target/raised amounts
campaigns	20	Channel, budget, goal (Acquisition / Retention / Reactivation)
investments	~2,400	Transaction-level data, linked to campaigns
email_events	~3,300	Full funnel: sent → opened → clicked → converted

---

🗂️ Schema

investors ──┬── investments ──┬── green_projects
            │                 └── campaigns
            └── email_events ──── campaigns

---

📊 Analysis & Key Insights

1 · Investor Segments — Where Does the Value Come From?

SELECT i.segment,
       COUNT(DISTINCT i.investor_id)  AS num_investors,
       ROUND(SUM(inv.amount), 0)      AS total_invested,
       ROUND(AVG(inv.amount), 0)      AS avg_ticket_size
FROM investors i
JOIN investments inv ON i.investor_id = inv.investor_id
WHERE inv.status = 'Completed'
GROUP BY i.segment
ORDER BY total_invested DESC;

Segment	Investors	Total Invested	Avg Ticket
Institutional	117	€193,321,528	€552,347
HNW	171	€41,989,874	€79,526
Retail	343	€2,918,868	€2,777
Impact	72	€2,121,816	€10,152

💡 Insight: Institutional investors represent only 14.6% of users but generate 80.6% of total revenue. The platform's growth strategy should prioritize institutional acquisition over volume-driven retail growth — a 1% increase in institutional conversion outperforms a 10% increase in retail sign-ups.

---

2 · Campaign ROI — Which Channels Are Worth the Budget?

SELECT c.campaign_name, c.channel,
       ROUND(c.budget, 0)  AS budget,
       COUNT(DISTINCT inv.investor_id) AS investors_acquired,
       ROUND(SUM(inv.amount) / NULLIF(c.budget, 0), 2) AS roi_multiplier
FROM campaigns c
LEFT JOIN investments inv ON c.campaign_id = inv.campaign_id
  AND inv.status = 'Completed'
GROUP BY c.campaign_id
ORDER BY roi_multiplier DESC;

Campaign	Channel	Budget	ROI Multiplier
Spring Reactivation	Email	€6,000	1792×
Green October	Email	€9,000	1747×
Spring Green Launch	Email	€8,000	1606×
Carbon Credits Push	Paid Search	€11,000	1470×
Institutional Year-End	Direct Mail	€50,000	312×

💡 Insight: Email campaigns dominate ROI — not because they reach more people, but because they re-engage investors who already have capital committed. Reactivation email (€6K budget → 1792× return) is the single most efficient marketing action in the dataset. Direct mail to institutional investors has lower ROI but targets a segment with 70× higher average ticket size.

---

3 · Email Funnel — Where Does It Leak?

SELECT c.campaign_name,
  ROUND(opened * 100.0 / NULLIF(sent, 0), 1)    AS open_rate_pct,
  ROUND(clicked * 100.0 / NULLIF(opened, 0), 1) AS ctr_pct,
  ROUND(converted * 100.0 / NULLIF(clicked,0),1) AS click_to_convert_pct
FROM ...  -- see full query in ecoinvest_queries.sql

Campaign	Sent	Open Rate	CTR	Click→Convert
Spring Reactivation	154	48.7%	32.0%	37.5%
ESG Awareness Q2	124	49.2%	41.0%	20.0%
Summer Invest	112	48.2%	44.4%	29.2%
Mid-Year Reactivation	158	43.7%	30.4%	14.3%

💡 Insight: Open rates are consistent across campaigns (~44–49%), meaning subject lines perform uniformly. The real differentiator is click-to-convert rate — reactivation campaigns (37.5%) outperform awareness campaigns (14–20%) by 2×. The funnel doesn't leak at the top; it leaks between click and conversion. Focus optimization on the landing page and call-to-action, not on subject line A/B testing.

---

4 · RFM Investor Segmentation

Using Recency, Frequency, and Monetary value with NTILE(5) window functions:

WITH rfm_scored AS (
  SELECT *,
    NTILE(5) OVER (ORDER BY recency_days ASC)  AS r_score,
    NTILE(5) OVER (ORDER BY frequency DESC)    AS f_score,
    NTILE(5) OVER (ORDER BY monetary DESC)     AS m_score
  FROM rfm_raw
)
SELECT rfm_segment, COUNT(*) num_investors,
       ROUND(AVG(monetary), 0) avg_ltv ...

RFM Segment	Investors	Avg Days Since Last	Avg LTV	Total Value
New & Promising	84	409 days	€836,602	€70.2M
Needs Attention	122	216 days	€460,218	€56.1M
Lost	76	97 days	€645,944	€49.0M
Loyal	195	381 days	€216,242	€42.1M
At Risk	144	133 days	€155,008	€22.3M
Champion	82	501 days	€4,278	€0.35M

💡 Insight: The "Lost" segment (76 investors, €49M) is a high-priority reactivation opportunity — they have high LTV but haven't invested recently. A targeted win-back campaign for this segment could recover significant revenue at low cost. Paradoxically, "Champions" have very low avg LTV (€4K) suggesting the model may need recalibration — likely because these are frequent small-ticket retail investors.

---

5 · ESG Score vs. Funding Success

SELECT esg_tier,
       ROUND(AVG(funding_pct), 1)    AS avg_funding_pct,
       ROUND(AVG(num_investors), 1)  AS avg_investors_per_project
FROM project_stats
GROUP BY esg_tier ORDER BY avg_esg DESC;

ESG Tier	Projects	Avg ESG Score	Avg Funding %	Avg Investors
High ESG (85+)	14	92.8	74.1%	48.9
Mid ESG (70–84)	9	78.5	74.0%	53.7
Low ESG (<70)	17	60.1	62.9%	57.1

💡 Insight: High-ESG projects raise 11.2 percentage points more of their target vs. low-ESG projects — but attract fewer individual investors. This suggests that ESG-conscious investors commit larger amounts to fewer, carefully-selected projects. Lower ESG projects compensate with volume. For platform growth, a dual strategy is needed: optimize ESG discoverability for high-conviction investors, while keeping lower-ESG options accessible for diversification-seeking retail investors.

---

6 · Monthly Growth + Rolling Average (Window Functions)

SELECT month, total_amount,
  SUM(total_amount) OVER (ORDER BY month)  AS cumulative_amount,
  ROUND((total_amount - LAG(total_amount) OVER (ORDER BY month))
        * 100.0 / LAG(total_amount) OVER (ORDER BY month), 1) AS mom_growth_pct,
  AVG(total_amount) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    AS rolling_3m_avg
FROM monthly
ORDER BY month;

💡 Insight: Using LAG() and SUM() OVER() reveals month-over-month acceleration. The rolling 3-month average smooths seasonal spikes (e.g. year-end campaigns) and provides a cleaner growth signal for executive reporting. Full output in ecoinvest_queries.sql.

---

7 · First-Touch vs. Last-Touch Attribution

-- Comparing which channel gets "credit" under different attribution models
WITH first_touch AS (...), last_touch AS (...)
SELECT channel,
       first_touch_revenue,
       last_touch_revenue
FROM first_touch
FULL OUTER JOIN last_touch USING (channel);

💡 Insight: Social Media channels generate strong first-touch revenue (top-of-funnel awareness) but rarely appear in last-touch attribution — confirming their role as a discovery channel rather than a closing channel. Email, meanwhile, closes deals. Attribution model choice dramatically changes how budget is allocated — a single-touch model would defund Social Media, which is a strategic error.

---

🧠 SQL Skills Demonstrated

Concept	Queries
SELECT, WHERE, GROUP BY, ORDER BY	Q1, Q2, Q4
JOIN (INNER, LEFT, FULL OUTER)	Q3, Q5, Q6, Q13
CASE WHEN conditional aggregation	Q7, Q8, Q12
Subqueries & WITH (CTEs)	Q9, Q12, Q13
Window functions: NTILE, LAG, SUM OVER, ROW_NUMBER	Q9, Q11, Q13
Cohort analysis	Q10
Funnel analysis	Q7
RFM segmentation	Q9
Multi-touch attribution	Q13
Date arithmetic (JULIANDAY, STRFTIME)	Q9, Q10, Q11

---

🛠️ How to Run

# 1. Clone the repo
git clone https://github.com/yourusername/ecoinvest-sql-analytics

# 2. Generate the database (requires Python 3.8+)
pip install faker pandas numpy
python generate_db.py

# 3. Open with any SQLite client
sqlite3 ecoinvest.db
# or use DB Browser for SQLite, DBeaver, etc.

# 4. Run queries
.read ecoinvest_queries.sql

---

📁 Repository Structure

ecoinvest-sql-analytics/
├── generate_db.py           # Fake but realistic data generator (800 investors, 3K+ events)
├── ecoinvest_queries.sql    # All 13 SQL queries, annotated
├── README.md                # This file
└── schema.png               # (optional) ER diagram

---

👤 About

Built as a portfolio project to demonstrate SQL skills in a marketing analytics / sustainable finance context.
Questions? Find me on LinkedIn.

---

Data is fully synthetic and generated for portfolio purposes only.