Revenues of Charities

#datafriday

Every year the Canada Revenue Agency (CRA) gathers data from every charity in Canada. Charities complete a T3010 form. This form captures data like the total revenue, amounts and types of fundraising, and what the charity spends on a particular kind of compensation. In a previous post, I demonstrated how we could use KNIME to compare data from a couple of CRA Open Data sets. This week I’ll quickly show how we can put data into a database, and how we can extract a table of the highest gross revenue charity sectors. 

Import Data To Database

For our exercise today, we’ll use CRA T3010 data from 2016, the most recent year that is posted. The first step that we want to take is to put the data into our database. To get the data into our database, we need to have a dynamic way to create tables. Fortunately, KNIME allows me to create tables based on the layout of the file. In the diagram below, we can see the workflow that operates as follows:

  1. A file of data from CRA is read
  2. I connect to the PostgreSQL database that I am putting the data into. 
  3. Based on the row headings in the file, a table is created. KNIME creates a column for each row heading. (I do need to enter the table name each time manually, but that isn’t a problem. I’m just glad it is saving me the time of not having to name each of the rows.) 
  4. Once my table and columns are created, the data is imported. 

Extract Useful Information

Once the data is in the database, all I have to do is look at my CRA Data Dictionary, and Code Lists and create helpful queries. Below is the results of a query that shows the total revenue by the organization type. 

CategoryTypeGroupcountTotal Revenue
20Teaching Institutions or Institutions of LearningEducation3,04382,727,359,055
10HospitalsHealth43056,723,090,977
19Health Organizations (not else classified)Health76239,036,920,650
11Services other than HospitalsHealth3,20517,253,987,125
9Welfare Organizations (not else classified)Health10,26810,779,435,909
1Organizations providing care other than treatmentWelfare3,2439,229,263,819
3(Welfare) Charitable CorporationsHealth4,4497,948,477,325
21Support of Schools and EducationEducation3,9125,481,277,495
59Community Organizations (not else classified)Benefits to the Community & Other4,4023,653,912,576
13(Health) Charitable CorporationsHealth1,0873,327,785,624
22Cultural Activities and Promotion of the ArtsEducation4,3513,178,470,900
29Education Organizations (not else classified)Education1,0552,551,936,121
39Other Denominations’ Congregations or Parishes (not else classified)Religion – Churches and Other Places of Worship9,6212,512,917,000
50Libraries, Museums and Other Repositories Benefits to the Community & Other2,3512,301,463,358
49Religious Organizations, (not else classified) Religion – Churches and Other Places of Worship1,8052,089,954,706
38Roman Catholic Parishes and ChapelsReligion – Churches and Other Places of Worship3,9151,539,222,258
23(Education) Charitable CorporationsEducation1,0481,308,594,263
56Recreation, Playgrounds and Vacation Camps Benefits to the Community & Other2,8931,236,364,590
47Missionary Organizations and Propagation of GospelReligion – Churches and Other Places of Worship1,8211,077,482,956
52Preservation of Sites, Beauty and Historical Benefits to the Community & Other1,308807,624,292
5(Welfare) Charitable TrustsHealth905686,617,391
31Baptist CongregationsReligion – Churches and Other Places of Worship2,065547,737,050
54Protection of AnimalsBenefits to the Community & Other915503,435,339
53(Community) Charitable CorporationsBenefits to the Community & Other561494,243,309
44United Church CongregationsReligion – Churches and Other Places of Worship2,513491,524,097
36Pentecostal Assemblies (Pentecostal Assemblies) of Canada onlyReligion – Churches and Other Places of Worship1,351472,462,095
30Anglican ParishesReligion – Churches and Other Places of Worship1,827409,914,180
40Salvation Army TemplesReligion – Churches and Other Places of Worship302409,030,070
46Convents and MonasteriesReligion – Churches and Other Places of Worship471366,016,539
57Temperance AssociationsBenefits to the Community & Other403331,781,572
34Mennonite CongregationsReligion – Churches and Other Places of Worship656319,712,454
43(Religion) Charitable OrganizationsReligion – Churches and Other Places of Worship396289,385,219
41Seventh Day Adventist CongregationsReligion – Churches and Other Places of Worship422288,044,143
37Presbyterian CongregationsReligion – Churches and Other Places of Worship1,040239,944,616
99Miscellaneous Charitable Organizations (not else classified)Benefits to the Community & Other88232,226,197
42SynagoguesReligion – Churches and Other Places of Worship331222,854,670
60Islamic Religious GroupsReligion – Churches and Other Places of Worship317217,781,872
32Lutheran CongregationsReligion – Churches and Other Places of Worship946156,240,241
15(Health) Charitable TrustsHealth204151,468,001
25(Education) Charitable TrustsEducation351136,406,914
61Jehovah’ Witnesses CongregationsReligion – Churches and Other Places of Worship1,604126,836,995
35Buddhist Religious GroupsReligion – Churches and Other Places of Worship343121,966,996
45(Religion) Charitable TrustsReligion – Churches and Other Places of Worship21276,698,825
2Disaster FundsHealth7564,895,556
55(Community) Charitable Trusts (Other than Service Clubs and FraternalBenefits to the Community & Other17460,472,760
62Sikh Religious GroupsReligion – Churches and Other Places of Worship15747,366,825
48Hindu Religions GroupsReligion – Churches and Other Places of Worship18645,027,520
65Service Clubs and Fraternal Societies’ ProjectsBenefits to the Community & Other49139,307,954
75Employees’ Charity TrustsBenefits to the Community & Other15129,300,383
63Service Clubs and Fraternal Societies’ Charitable CorporationsBenefits to the Community & Other18727,594,604
33Baha’ is Religious GroupsReligion – Churches and Other Places of Worship23623,028,622
81Registered National Arts Services Organization (RNASO)Benefits to the Community & Other258,911,123
51Military UnitsBenefits to the Community & Other634,245,851