It’s On – Aiimi’s collaboration project with Southwark Schools to provide business intelligence around activities/clubs for young people

After meeting with Southwark Council and a number of local schools, it became clear that a number of young people in Southwark were not only unclear on what activities/clubs were available to them but the young people would be more likely to attend activities/clubs if they knew who else was attending. Therefore For almost a year now, Aiimi has been working in collaboration with 12 students from 4 schools from Southwark to create an online portal, called It’s On, which will provide intelligence and analysis regarding the local activities young people are involved in. The outcome of It’s On will act as a proof of concept and will feed into a further project that the Council will be starting later in the year to create a social media site for young people in Southwark. One of the key elements of the project is to bring increased awareness and an understanding of the IT and more specifically business intelligence to young people. One of the many functions of the portal is to present statistical reports (information) regarding the demographic breakdown of young people who attend specific activities and activity types. To generate these reports, Aiimi has decided to use the SSRS tool.

As a first step, a sample of data (just over a thousand rows) was placed into a SQL server to serve as the underlying data source for all of our reports. There are three tables in total; one of Activities, one of Students, and one of Students mapped to Activities (survey responses). Using this data, my task is to generate a number of reports to show the following:

• Average Ratings, Average age, and male to female ratio of Activities
• Average Ratings by Schools split by Gender and Age Range
• Average Ratings by Area split by Gender and Age Range
• Heat Map to display concentration of activity locations
• Survey statistics to show the number of respondents and type of responses

In many cases the reports use simple visualisations such as pie charts, bar charts, and tables to display information and can be drilled to alternative reports of greater detail (Top 3 activities by average rating etc).

The purpose of such reports is to show users – in particular young people around Southwark – which and where the most popular activities are. As our research showed young people often found the idea of joining a club alone quite daunting and therefore may have decided against doing so due to this fact. With this kind of information at hand they will be able to see if a particular club has many members and also if any of those are from the same school, of the same gender, and of a similar age. A great benefit of this would be the extra encouragement for kids to get involved with more activities and become much more active in general.

Furthermore the reports can be extremely beneficial to club committees and supporting funders (i.e. the council). Currently the Council has no intelligence on which young people are doing what! This is due to the lack of information on each club; they do not know which are the most active, which are most popular, and which are growing the quickest. The It’s On portal and SSRS reports will aim to provide such insight into club activity and hopes to give the Council intelligence to make informed decisions regarding activities and clubs.

Currently the reporting element of the project is still in the early stages (we are less than a week in) and still needs some major development. However I have generated a few first draft reports which I can display here to show just where things will be headed and what can be expected in the near future.

Activity Report

The activity report breaks the data down by different categories and displays levels of attendance (by school) and the age vs. gender split. In this example report I have selected the Gymnastics activity – Unsurprisingly we can see that there are a larger number of girls attending the activity in comparison to boys. We can also see that the majority of members come from the Sacred Heart School.

Area Report

The area report display data aligned by the area in which activities are hosted. For this report the user can filter by Gender, Age range and School. This then results in a series of visualisations showing the average rating given, the number of activities per area, and also how likely the member is to go again or recommend the activity to a friend.

Respondent Report

The respondent report shows the general response towards the survey itself.  From this report we can discover the ratio of male to female respondents and the spread in terms of age, school, and area.

School Report

The school report displays the average ratings given by respondents and also how likely they are to recommend or go again to an activity. The report can be filtered by gender, age range, and school.

Whilst the reports are only first drafts and are likely to be changed in the near future they illustrate the direction of this project and the value it can bring to the potential end users. In the future when the portal nears the final stages I will revisit this topic and create a new blog to demonstrate the final product. Hopefully I can get some guest bloggers (student users and co-developers) to discuss the project from their perspective also. Stay tuned and watch this space!

(Many thanks to Ben Sprague for his assistance and input in writing this blog!)

« IIS 7, SSL, and Two Factor Authentication – Installing Client Side Certificate for SSL Authentication

Aiimi’s BI Team learn MicroStrategy Web Design tips from Sylvain Le Moel »