How can I track statistics on my site's user population?

Question

I have a site with a fairly large number of users and I am trying to track statistics on them over time.

For example, users can have:

• Any combination of four roles
• One of two values for gender
• One of two values for an additional field

I want to track each combination of role, gender, and additional attribute separately.

This has lead me to use Views to create a huge number of content panes, each with filters set to catch a particular combination. I then added all of these to a panel, where I view them once a week and write the numbers down.

However, I now want to add an additional field with 6 additional values. Creating all those combinations in Views is going to be a mess. Is there a more efficient way to do this rather than clicking through the Views UI, creating a content pane for each possible combination, and then adding that in Panels?

Answer 1

Other than the option you present, I think you have two choices.

One is to wire up a hook_menu(), and create a path that accepts role, gender, and the other field value. You would then pass this into a db_select() or EntityFieldQuery(), and return the results in HTML table form.

The other option, and the one I would recommend, would be to make up a View over your users, add in your roles and fields, and then use Views Data Export to export as XLS or CSV. You can then analyze your data in a third-party tool like Excel.

Why do I recommend the later? Leverage your tools for their strengths. Drupal is good an managing and serving up content (raw data counts). Tools like Excel are great at analyzing data. These three fields are what you need to look at now, but this could change in the future. Having a full export of the raw data will make this much easier down the road.

Answer 2

If you don't mind some SQL work, Forena might be a module for you. Without all that neat and nice features like report skins (a kind of templating) - which might be unneeded in your case - it's much lighter, cleaner and faster.

Forena can be used to query (not update) any MySQL database table (that includes any Drupal table also).

For more details about Forena, 2 types of documentation are available these days:

• Community documentation.

• Documentation that comes with Forena, which you can access right after install and enable of the module. Checkout the demo site for an online example of the current:

  • Forena documentation - use the link 'Reporting documentation' or visit relative link /reports/help.
  • Forena samples - use the link 'Reporting samples' or visit relative link /reports/samples (these samples are fully functional, so make sure to experiment a bit with it, such as the drill downs available on the SVG Graph sample).

The newest 7.x-4.x version also includes an amazing (I think) UI for either creating your reports (the WYSIWYG report editor) and/or for creating your SQL queries (the Query Builder).

Here are some variations of the MySQL database part in my answer:

1. Using a SQLite instead of MySQL database: the Forena samples actually get shipped including a (Tiny) SQLite database. Go check it out in the demo site: the data shown there are data contained in the sampledb, which is in SQLite format.
2. Forena comes with a full suite of Supported database connections ... such as MS SQL, Oracle, Postgress or any PDO compliant variation.

Enough reasons for considering giving Forena a try? While doing so, use it's issue queue for any type of support/docu requests you may have.

Disclaimer: Pierre.Vriens (answer editor) is a co-maintainer of Forena.