Dashboard week has started.

Dashboard Week, The Data Schools’ challenge to consultants nearing the end of their intense fourth month training before starting placement. The challenge for each day this week – create a dashboard and tell a story using a newly provided dataset each day. Each day will present a new challenge, requiring us to recall parts of our training to access data and create our dashboards. For example, we will at some point need to use an API to access the data. And to top it all off, we will then present our story dashboards to data school colleagues and staff.

Day 1 – Australian Charities

The first dataset, downloaded from the ACNC website contained information on registered charities across Australia. Inspecting the dataset, the data provided a general overview of registered charities looking at registered charities over the last 9 years. Looking closer, it contained information on:

  • charity contact information,
  • charities by where they operate in Australia,
  • establishment and registration date,
  • charities grouped by categories and sub-categories.
Approach/Story

My approach to the challenge was to clean the data and then explore different aspects of the data. This took a bit of time but I was able to find my story. The focus being on the number of new charities registering and revenue reported.

One aspect of my approach that helped understand my story was in finding a user guide. The user guide from the ACNC website provided a detailed breakdown of the data contained in each column and some additional details. For example, I was able to identify the values for categories and sub-categories as well as make sense of any acronyms. Finding the user guide was a big help!

Data preparation

Inputting the data into Alteryx, overall it was fairly clean. However there were a few values to fix and the format was not ideal to start create my dashboard. For example, the categories and sub-categories for each charity was broken into individual columns which meant most of the rows were empty. My approach was to transpose the data so that I had one column each for categories and subcategories.

I was also able to add some additional information from the user guide into the dataset – revenue groups. This was done by matching the size of the charity with the revenue descriptions.

For simplicity and to get into Tableau, I outputted for tables from the original dataset with the intension of joining the data back up using relationships. I took this approach as some charities were grouped by multiple categories and subcategories which was not ideal to combine.

Dashboard creation

The hardest part about making this dashboard was thinking of the story. After playing around with the data and a few different charts, I was able to identify two trends:

1 – The number of charities reporting revenue has drastically declined since 2013

This could be due to charities not receiving any revenue or that they are simply not reporting. I took the view that charities weren’t receiving revenue as I would have thought charities would need to report their revenue to keep their status with the Australian authority of charities (ACNC).

2 – The number of new charities registering across Australia has been declining over recent years

Adding to the first trend, I thought that looking at the decline of new charities registering could be linked to revenue. Drawing a conclusion that less organisations are becoming charities because charities are receiving less revenue.

New Australian charities are in decline and revenue has stagnated dashboard (Link to dashboard)

Functionality included in the dashboard allows the user to drill down by year, state, revenue and sub-type (charity category) and compare multiple sub-types over these variables. I have also included help buttons to help with navigation and interactivity.

 

Scott Johnston
Author: Scott Johnston