SQLProject_4

Impact Analysis of GoodThought NGO Initiatives

Project Overview

ngo_project_image

This project focuses on supporting GoodThought NGO, an organization dedicated to driving positive change through initiatives in education, healthcare, and sustainable development. The goal is to leverage data-driven insights to help GoodThought optimize its impact and resource allocation.

Using the GoodThought PostgreSQL database, which contains detailed records of assignments, funding, impacts, and donor activities from 2010 to 2023, the project aims to analyze trends, evaluate program effectiveness, and uncover opportunities for improvement. This hands-on data analysis empowers the NGO to make more informed decisions, maximize its reach, and strengthen its humanitarian efforts worldwide.

Data Sources

GoodThought has provided comprehensive dataset includes:

Refer to the below ERD diagram for a visual representation of the relationships between these data tables:

erd

Exploratory Data Analysis (EDA)

EDA involved exploring the datasets to answer key questions, such as:

Data Analysis

Including some interesting code/features worked with

-- highest_donation_assignments
WITH donation_details AS
(
SELECT d.assignment_id,
       ROUND(SUM(d.amount),2) AS rounded_total_donation_amount,
       dn.donor_type
FROM donations AS d
JOIN donors AS dn
  ON d.donor_id = dn.donor_id
GROUP BY d.assignment_id, dn.donor_type
)

SELECT a.assignment_name, 
       a.region, 
       dd.rounded_total_donation_amount, 
       dd.donor_type
FROM assignments AS a
JOIN donation_details AS dd
  ON a.assignment_id = dd.assignment_id
ORDER BY rounded_total_donation_amount DESC
LIMIT 5;

-- top_regional_impact_assignments
WITH donation_counts AS
(
SELECT assignment_id,
       COUNT(donation_id) AS num_total_donations
FROM donations
GROUP BY assignment_id	
),

ranked_assignments AS
(
SELECT a.assignment_name,
       a.region,
       a.impact_score,
       dc.num_total_donations,
       ROW_NUMBER() OVER (PARTITION BY a.region ORDER BY a.impact_score DESC) AS rank_in_region
FROM assignments AS a
JOIN donation_counts AS dc
  ON a.assignment_id = dc.assignment_id
WHERE dc.num_total_donations > 0
)

SELECT assignment_name,
       region,
       impact_score,
       num_total_donations
FROM ranked_assignments
WHERE rank_in_region = 1
ORDER BY region ASC;

Results/Findings

The analysis results are summarized as follows:

Recommendations

Based on the analysis, we recommend the following actions: