Visualising activity

Question

I work for an organisation that provides free activities that benefit people's health. We have a well maintained database that records participation and I am using it in order to research the active population, and their participation levels and trends.

I am trying to show how people use the service over time.

I have been able to show this for a small number of people, by using an XY scatter graph, giving each person a unique number that is plotted on the Y axis, then their activity day is plotted on the X axis (number of days since sign up). What this shows is that activity tends to be clumped (i.e. people do the activity for a short period of time then give up again).

This is interesting, but I would like to be able to show it for a much larger group of people. However, visualising it in this way quickly gets messy for a much larger dataset. I was wondering if you would be able to suggest some alternative ways I could show it.

I am already using a few graphs to show average activity level over time, but what is really interesting here is the 'clumping' of activity which I can't show by aggregating data.

I have shown a few images below that should help with my above explanation.

I will only be able to use free tools or MS Excel to visualise unfortunately.

Answer 1

You can try using a horizontal stacked bar in Excel to show people's activity days in colour blocks with gaps painted white, like so:

It will mean doing some transformations to your data, though, as you'd need two rows for each day with 1 and 0 depending on whether a customer engaged in an activity that day. If you're familiar with VBA it should be possible to do it programmatically.