We're excited to introduce the first part of our annual #SANTALYTICS challenge — an all-inclusive, non-denominational, secular, festive excuse for some friendly competition using Alteryx. If you are just checking out Santalytics for the first time, please read the announcement blog for the inside scoop: #SANTALYTICS 2018 SANTALYTICS 2018 - Part 1 In the spirit of the season, the Community team would like to donate to a local Colorado charity and we need your help! In today's challenge, you'll help us find a worthy charity, and in next week's part II, you'll help us determine exactly how to spend the money. We need you to analyze publicly available IRS data. There are three key datasets: * Exempt Organization Returns Filed in Calendar Year 2017: download the Form 990 Extract here. * Download the corresponding data dictionary here. * There's also an IRS lookup list that will allow you associate Employer Identification Number (EIN) to the charity's name. Download it here. Blend and analyze the data to determine which charity we will donate to given: * It needs to be in Colorado (see State field) * It should benefit people (see Grants to individuals? field) * They should actively be raising funds (see Fundraising activities? field) * We should be allowed to donate items, not just cash (see Deductible non-cash contributions? field) * The charity should be small and flying under the radar (Number of employees $5,000,000) Once you've narrowed down the list based on the above criteria, rank the remaining charities based on fundraising efficiency and select the top charity! (see Fundraising expenses and Total contributions fields) Extra credit: Come up with your own ranking criteria to identify a worthy charity in your neck of the woods! 2018-12-17 update: our particular solution here.

Here's my solution! challenge_145.yxmd

Completed! Challenge_145 JEP Complete.yxmd

My solution Spoiler desafio_145.yxmd

Challenge #145: SANTALYTICS 2018 - Part 1

We're excited to introduce the first part of our annual #SANTALYTICS challenge — an all-inclusive, non-denominational, secular, festive excuse for some friendly competition using Alteryx.

If you are just checking out Santalytics for the first time, please read the announcement blog for the inside scoop: #SANTALYTICS 2018

SANTALYTICS 2018 - Part 1

In the spirit of the season, the Community team would like to donate to a local Colorado charity and we need your help! In today's challenge, you'll help us find a worthy charity, and in next week's part II, you'll help us determine exactly how to spend the money.

We need you to analyze publicly available IRS data. There are three key datasets:

Exempt Organization Returns Filed in Calendar Year 2017: download the Form 990 Extract here.
Download the corresponding data dictionary here.
There's also an IRS lookup list that will allow you associate Employer Identification Number (EIN) to the charity's name. Download it here.

Blend and analyze the data to determine which charity we will donate to given:

It needs to be in Colorado (see State field)
It should benefit people (see Grants to individuals? field)
They should actively be raising funds (see Fundraising activities? field)
We should be allowed to donate items, not just cash (see Deductible non-cash contributions? field)
The charity should be small and flying under the radar (Number of employees < 300; Advertising and promotion < $1,000)
But making a big impact (Total contributions > $5,000,000)

Once you've narrowed down the list based on the above criteria, rank the remaining charities based on fundraising efficiency and select the top charity! (see Fundraising expenses and Total contributions fields)

Extra credit: Come up with your own ranking criteria to identify a worthy charity in your neck of the woods!

2018-12-17 update: our particular solution here.

Developer

Macros

Preparation

Advanced

Data Analysis

Data Preparation

Transform

Basic

Accepted answers

All comments

patrick_digan

I've selected...

Spoiler

A Precious Child Inc.

challenge_145.yxmd

Kenda

Spoiler

Part I = Complete! Here are the top charities that I found using the given criteria.

santalytics part i.yxmd

JoBen

Cheers!

Spoiler

Here's my solution. Also, I had to download a different dataset from the IRS website. I think the one that links is the "Form 990 EZ Extract" and the one I had to use was "Form 990 Extract".

NicoleJohnson

'Tis the season for my favorite analytics challenges!!! My solution for Part 1...

Spoiler

NOTE: I had to connect to the Form 990 file directly from the IRS website since the one linked as the "download Form 990 Extract here" link appears to be the EZ version, which doesn't have the fields we need...

Cheers!

NeilR

Thanks @JoBen & @NicoleJohnson I've fixed the link!

Ladarthure

My solution!

santanalitycs.yxmd

pjdit

Here are my top 3

Spoiler

challenge_145_PD_Solution.yxmd

John_K

A good exercise.

Santalytics_145_JohnK.yxmd

wjohndenham

So here it is. I got a little hung up on the calculation for Average Fundraising Efficiency since its across 3 years and from what I could see in the data only 2 non-profits had that much data. I pulled in 3 years worth of data from the IRS site and bucketed the averages from there. I'm still learning Alteryx so I'm sure there may be some errors in my tool use but I wanted to get this out there.

Based on my data I would select Denver Scholarship foundation.

Also, I did have issues with the IRS dataset not showing the correct fields for the challenge so I downloaded the correct one directly from the site.

Spoiler

Santa Part 1 Colorado.yxmd

wjohndenham

I would pick the second non profit (Denver Scholarship Foundation) since they have a longer history. I pulled three years worth of data (per the Efficiency calculation).

Spoiler

Santa Part 1 Colorado.yxmd

CharlieS

I'm glad I challenged myself to do this with only the URLs provided as the input and not download any files.

Spoiler

The Excel file was the only tricky part. I used the Download tool to write the file as a temporary file and changed the file name back to the '17eofinextractdoc.xls' provided by the URL so the Dynamic Input would be happy with the file extension. I didn't think of a way to handle for the '990' sheet name easily, so that was just hard coded

Challenge_0145-CharlieS_Solution.yxmd

AGoyal

Solved. However, I am getting different results (on the sorted output) that what others did - not sure why.

Also, added my state to the mix.

Spoiler

challenge_145.yxmd

CamodCPA

Took me a few hours to figure out the excel sheet issue! I definitely used the help of some more experienced alateryx'rs. This one took me for a loop!

By seeing another alteryx user use the dynamic rename tool, I took it on. it was fun to see that in action!

Also, I don't have the greatest computer in the world, but, I now understand why making your workflow efficient is important..... by the end it was taking me 12-16 seconds to run this puppy! I believe mainly because of the downloading aspect, I will be practicing downloading the file loacally, it would have helped!

Spoiler

Challenge_145_Cod.yxmd

NeilR

@AGoyal I think yours sorts differently because you created your AFE column as a string instead of a number.

estherb47

My solve.... Revamped for downloading data directly from the websites. Thanks to @CharlieS for figuring out how to do all of that.

Santalytics2018_01_EHB.yxmd

ddiesel

My Solution:

Spoiler

A Precious Child Inc.

And extra credit...

Spoiler

... still having fun with this data and working on it!

ggruccio

Love Santalytics every year! I looked up Ohio to see what ranked tops there as well.

The website wouldn't accept the yxzp for exceeding the maximum size. Here's my workflow.

challenge_145_gg_finish.yxmd

sprodger

my solution:

Spoiler

Challenge_145_SP.yxmd

George_Walker

My solution

Spoiler

A Precious Child Inc.

santalytics.yxmd

lminors

Merry alteryxmas!

Spoiler

Solution below, using union keeping common subset to narrow data set down:

Workflow

I used the 'Total functional expenses' as it refers to the 'Average of Part IX line 25D' which seems to imply that field rather than just the fundraising expenses - this seems to indicate they are much less efficient than you'd expect.
Results

Results

OliverW

Hey all,

I gave this some sort and thought I do it the way that I only create the average for the Efficiency for the last available 3 Fiscal Periods. I dont think its fair for an Uptodate report to build the average for longer periods back, why should an orginzation benefit in their Efficiency Rating in the end from an average that includes data from a really long time ago? So I limited the average calculation for the efficiency to max. 3 last available 3 years.

Additionally I build in a check to make sure that the criterias that were defined are looked at only for the last available fiscal period, did not check if that even occurs but generally I guess it could happen that an Organization for example grants to individuals in data from 2016 but not in 2017, or maybe even they had 98 employees in 2016 and 102 in 2017. I think its not an issue in this data set, but as I usually build stuff as much general (so to be used with new datasets later) as possible, I built that check in as well.

Lastly I have made this all dynamic, so no static, manual downloads at all, all is running dynamically and pulling the data at run time. If I were to design that for a production use case, I would probably design this as an Analytical App where the user could choose if they want to download the files newly or if they want to use the existing dataset that has been downloaded in the past.

Spoiler

So in the end, my result, looks the same I think as for many others, I just built in some extra logics if someone would run it for other states for example.

SANTALYTICS2018_part1.yxzp

Joe_Lipski

Here's my selection and workflow:

Spoiler

Santalytics Part 1.yxmd

WilliamR

Using an apps to choose the number of organizations to donate to, equally distributed according to a range of 4 the organization size.

Spoiler

145-WilliamR.yxzp

hollyhinson

My solution

Santalytics Part 1.yxmd

jssandom

My top 3!

Spoiler

challenge_145_solution_JSS.yxmd

dillon_bok

Spoiler

Here is CO's top charity based on the requirements given: Santalytics-Pt 1.PNG

Spoiler

And here is the result from OH using the same criteria:
Santalytics-Pt 1(OH).PNG

Done :-)

My solution.

Midway between the easy road and the hard. I scrape the 2 .txt files from the web sites and downloaded the excel.

Also, It's good to see that the exceedingly under-recognized city of Broomfiled, CO. is finally receiving its due!!

Spoiler

A Precious Child Inc. seems to be the overwhelming choice.

Dan

P.S. I finally finished all the challenges. I finally get to say Hi to @estherb47, @ggruccio and Mr. Speed Demon @patrick_digan. And I'm sure that it's just a matter of days before @JoBen, @kat, @NicoleJohnson, and @SeanAdams regain the top step. It's been a long, sometimes difficult journey(I'm looking at you, Find Ned). Thanks to all the people who helped along the way.

Challenge_145_solution_DL.yxzp

mmenth

Spoiler

My top:

santalytics round 1.yxmd

egranberry

My solution!

Santalytics Pt 1.yxmd

CynthiaE

Spoiler

I chose "A Precious Child". It seems like they spent the least in fundraising expenses to get more contributions.

2018-12-11_16-59-31 Santanalytics p1.jpg