Lovette Duke

My SQL Data Analysis Portfolio Table of Contents Table of Contents 0 Professional Background 1 Introduction 1 Root Cause Analysis Process 1 Insights from the Analysis 2 Findings and Recommendations 8 Conclusion 8 Professional Background I have a background in Electrical / Electronic Engineering & Data analysis. I'm currently working as a Data Specialist at 4Lines Concept. I have 2years experience working as a data entry/administrative officer and 2years experience as and IT/ frontend Developer which helped strengthen my capabilities in critical thinking, teamwork and keen attention to detail. I have a well-rounded skill set in data analysis and honed talents in tools like SQL, Python, R programming, Tableau (public), Excel and Power BI (desktop). Introduction There is an upcoming fundraising strategy meeting in a hypothetical charity organisation called “Education for all’. The head of fundraising intends to use data to guide the fundraising strategy for the coming year. I as the data analyst has been tasked to present insights on “donors” and “donation rates” data with the objective to increase the number of donors, increase the donation frequency and increase the value of donation. I came up with questions which can extract useful insights from the donation data, used SQL to query the database and used tableau to visualise my findings. I discovered that California, Texas and Florida had the highest number of donors and thereby donation and Human Resource, Research and Development and Project management where the professions with the highest amount in donation. Root Cause Analysis Process There are donors in many different states, in varying professions donating at different frequencies that is, weekly, monthly, yearly and even one-offs. From the data analysis, it was observed that the difference between the total yearly donation and the total weekly donations is relatively small. It is important to note that the weekly donations were not cumulative hence, this goes to show that increasing the frequency of donations can increase the donation value significantly. It was also observed that the states with the highest number of donors also had the height donation value. This goes to prove that increasing the number of donors and increasing the frequency of donations can increase the donation value. 1 Insights from the Analysis 1. Looking at the professions with the top 5 and bottom 5 donation totals SELECT job_field, SUM(donation) AS job_field_total FROM Donation_Data GROUP BY job_field ORDER BY SUM(donation) DESC LIMIT 5; SELECT job_field, SUM(donation) AS job_field_total FROM Donation_Data GROUP BY job_field ORDER BY SUM(donation) LIMIT 5; 2 2. Then, looking at the states with the top 5 and bottom 5 donation totals SELECT state, SUM(donation) AS state_total FROM Donation_Data GROUP BY state ORDER BY SUM(donation) DESC LIMIT 5; SELECT state, SUM(donation) AS state_total FROM Donation_Data GROUP BY state ORDER BY SUM(donation) LIMIT 5; 3 3. Now, inspecting the donation totals with regard to donation frequencies SELECT donation_frequency, SUM(donation) AS total_by_frequency, COUNT(*) AS donation_frequency_count FROM Donation_Data JOIN Donor_Data2 ON Donation_Data.id = Donor_Data2.id GROUP BY donation_frequency ORDER BY SUM(donation) DESC; 4 4. Inspecting the number of donors per donation frequency in the top 5 states SELECT state, donation_frequency, COUNT(*) AS num_of_donors FROM Donation_Data JOIN Donor_Data2 ON Donation_Data.id = Donor_Data2.id WHERE state = 'California' OR state = 'Texas' OR state = 'Florida' OR state = 'New York' OR state = 'Virginia' GROUP BY donation_frequency, state ORDER BY state, COUNT(*) DESC; 5 5. Top 5 donors in the different donation frequencies SELECT first_name, last_name, email, job_field, state, donation, donation_frequency FROM Donation_Data JOIN Donor_Data2 ON Donation_Data.id = Donor_Data2.id WHERE donation_frequency = 'Weekly' ORDER BY donation DESC LIMIT 5; SELECT first_name, last_name, email, job_field, state, donation, donation_frequency FROM Donation_Data JOIN Donor_Data2 ON Donation_Data.id = Donor_Data2.id WHERE donation_frequency = 'Monthly' ORDER BY donation DESC LIMIT 5; SELECT first_name, last_name, email, job_field, state, donation, donation_frequency FROM Donation_Data JOIN Donor_Data2 ON Donation_Data.id = Donor_Data2.id WHERE donation_frequency = 'Yearly' ORDER BY donation DESC LIMIT 5; SELECT first_name, last_name, email, job_field, state, donation, donation_frequency FROM Donation_Data JOIN Donor_Data2 ON Donation_Data.id = Donor_Data2.id WHERE donation_frequency = 'Once' 6 7 Findings and Recommendations The following insights can be drawn from the analysis and visualizations above: 1. The top 5 states with the highest amount of donations made also had the highest number of donors. 2. The top 5 weekly donors are all located in the top 3 states with the highest donations. 3. While certain professions made more donations the difference between the top 5 professions and the bottom 5 donations are not very significant. The 2 major ways to increase the donation value would be to: 1. Encourage the current donors to increase the frequency of their donations and especially top monthly, yearly and one-off donors and 2. To encourage more people to become donors preferably in the top 5 states where they are more likely to donate and become donors. Conclusion We can conclude that focusing fundraising efforts in the top 5 state with the highest donations could potentially give us the best result in terms of increasing the donation value. 8