Background

I reached out to the director of a non-profit in my city to see if they would be interested in having an analysis done on their organization. We met for a brainstorming session in which the client listed out what they were interested in learning about their org, and I asked clarifying questions around what data was accessible, and what metrics they wanted analyzed. By the end of the meeting, I had a list of data requirements and objectives to meet for the client.

The questions the client had were:

• What is our customer base made of?

• How much does each customer type contribute to our org?

• What are the donation totals by year?

• Are the housing programs growing?

• How much food did we serve?

Data Collection

I began to collect data by working with the non-profits accountant, who was able to give me access to their Quickbook data, and Excel spreadsheets. Bringing these two sources together, I was able to collect the data I needed to begin analyzing. I pulled all the data into Excel. It contained 4 tables: Donors by Month, Grace House, Mission House, and Customer List

Data Cleaning

I performed some data cleaning exercises, checking for duplicates, inconsistencies, or NULL values on all tables. While the data types appeared to be consistent, some duplicates and NULL values were found in the Customer List table. After getting clarification from the accountant, I was able to correct the NULL values, and remove the duplicates.

I realized most of the client objectives could be met through the use of the "donor's by month" table. I also knew I was going to need to create labels for "customer types". I used the following formula to create customer type labels in the "donors by month" table.

=IF(ISNUMBER(SEARCH(",",A2)), "Individual", IF(LEN(A2)>0, "Business or Foundation", ""))

Then, using conditional formatting, I highlighted cells containing the word " Church" or "Ministry" to quickly identify the final customer type bucket and labeled it accordingly.

Lastly, I used the SUM function to roll up the monthly donations into yearly totals. The "donors by month" table contained donations from 2013-2022. After speaking with the client, I added up yearly totals from 2018 to 2022, to show a more recent timeline.

The Analysis and Results

After the data was cleaned, I used pivot tables to analyze the data and create visualizations.

The clients first questions was What is our customer base made of? To answer this I used pivot tables to count the totals of each customer type, then I made text boxes containing the information to include in the final report.

Total donation customers: 2,853 Individuals: 2,526 Businesses or Foundations: 281 Churches: 46

The second chart I made was "Percent of Donations by Customer" which answers the clients next question of What percentage does each customer type contribute to our org? This chart shows the percentage of contributions by customer type in the years 2018-2022.

The next chart addresses the clients third question. What are the donation totals by year? I decided to make this a line chart to show trending. After creating the chart, I noticed that donation totals were trending down. This prompted me to create an extra visual to show Year over Year changes so the client would know how much money was being lost or gained throughout the timeline.

I created two bar charts using pivot tables to address the clients next question about Grace House and Mission House Growth. I decided to show growth from each location individually because I thought this would provide more insight to the client.

Lastly, I included food serving totals for the client to answer their final question. These totals reflect years 2021-2022. The reason this timespan is different from the other charts is because the organization did not start tracking food servings until 2021.

Delivery the Report

I met in-person with my client to present the report. I began with reviewing the questions the client had at the beginning of the project, and explained each chart. The "Year over Year" changes chart prompted a conversation around why donations are trending down. Using the "Percent of Donations by Customer" chart, I speculated that the decline in donations from businesses in 2021-2022 could be a contributing factor to the decline in overall donations. The client also had some ideas for the declining donations which prompted them to further investigate. The client was satisfied with the report and gave me positive feedback.