GoodThought NGO has been a catalyst for positive change, focusing its efforts on education, healthcare, and sustainable development to make a significant difference in communities worldwide. With this mission, GoodThought has orchestrated an array of assignments aimed at uplifting underprivileged populations and fostering long-term growth.
This project offers a hands-on opportunity to explore how data-driven insights can direct and enhance these humanitarian efforts. In this project, I engage with the GoodThought PostgreSQL database, which encapsulates detailed records of assignments, funding, impacts, and donor activities from 2010 to 2023. This comprehensive dataset includes:
Assignments: Details about each project, including its name, duration (start and end dates), budget, geographical region, and the impact score.Donations: Records of financial contributions, linked to specific donors and assignments, highlighting how financial support is allocated and utilized.Donors: Information on individuals and organizations that fund GoodThought’s projects, including donor types.
Refer to the below ERD diagram for a visual representation of the relationships between these data tables:
I will execute SQL queries to answer two questions.
-- highest_donation_assignments
--List the top five assignments based on total value of donations, categorized by donor type.
SELECT
a.assignment_name,
a.region,
ROUND(SUM(d.amount),2) AS rounded_total_donation_amount,
donors.donor_type
FROM donations AS d
LEFT JOIN assignments AS a
ON a.assignment_id = d.assignment_id
LEFT JOIN donors
ON d.donor_id = donors.donor_id
GROUP BY a.assignment_name, a.region, donors.donor_type
ORDER BY rounded_total_donation_amount DESC
LIMIT 5;By analyzing the patterns in this data, GoodThought can tailor its outreach efforts to different donor types and regions, optimizing its fundraising strategy for maximum impact.
East Region: Assignments in the East region, such as Assignment_3033 and Assignment_268, show strong support from individual donors. This suggests that GoodThought’s outreach in this region may resonate more with personal contributors, who tend to fund projects related to specific causes or local initiatives. West Region: In contrast, the West region, with assignments like Assignment_300 and Assignment_1765, primarily receives funding from organizations, indicating a stronger alignment with corporate giving. GoodThought may consider expanding its partnerships with corporations in this region to secure more large-scale donations.
While individual donations feature prominently, organization donations tend to be slightly larger on average. For instance, Assignment_300 and Assignment_4114 received over
The presence of both individual and organizational donors in the top five highlights the importance of maintaining a balanced approach in donor engagement.
Analysis: Highest Impact Assignments by Region
The goal of this analysis is to identify the assignment with the highest impact score in each region, while ensuring that each assignment has received at least one donation. The results include the assignment name, region, impact score, and the total donation amount, categorized by region.
-- top_regional_impact_assignments
--Identify the assignment with the highest impact score in each region, ensuring that each listed assignment has received at least one donation. Include only the highest-scoring assignment per region, avoiding duplicates within the same region.
WITH ranked_assignments AS (
SELECT
a.assignment_name,
a.region,
a.impact_score,
COUNT(d.donation_id) AS num_total_donations,
ROW_NUMBER() OVER (PARTITION BY a.region ORDER BY a.impact_score DESC) AS rank
FROM assignments AS a
LEFT JOIN donations AS d
ON d.assignment_id = a.assignment_id
GROUP BY a.assignment_name, a.region, a.impact_score
HAVING COUNT(d.donation_id) >0
)
SELECT
assignment_name,
region,
impact_score,
num_total_donations
FROM ranked_assignments
WHERE rank = 1
ORDER BY region ASC;The results show that across all regions, GoodThought is running highly impactful projects. While there is some variation in the number of donations, the impact scores are consistently at or near the maximum of 10, indicating a strong alignment between the organization's efforts and its mission.
The North and South regions have highly impactful assignments but lower donation totals. GoodThought could enhance its outreach in these regions to attract more donors, highlighting the significant impact these projects are having.