How to audit Drupal 8 to determine the number of active users

When drupal.org reached 1 million registered users, I wondered what was the percentage of really active users.

I’d love to know how many of those 1M http://t.co/uVCABrlDEf accounts are actually active. Say, users who’ve logged in within the past year.

— Aurelien Navarre (@AurelienNavarre) October 11, 2013

Thinking about it more, I figured this would totally be a legit site audit metric to get for business owners. Say you have an e-commerce platform and you want to engage inactive users by giving them a discount or you wish to know if your community is really growing, there are many reasons to get such data and probably you could even create a good contrib module for that.

But if you’re like me and prefer a quick and easy one-liner, then read on.

In Drupal 8, finding when a given user account last accessed the site is a bit different from Drupal 7, as you now need to query the {users_field_data} table.

mysql> SELECT uid,access FROM users_field_data WHERE uid = 1;
+-----+------------+
| uid | access     |
+-----+------------+
|   1 | 1474319299 |
+-----+------------+
1 row in set (0.00 sec)

Problem is we can’t really make anything out of this Unix timestamp. Sure you can use an Epoch converter or use the date command as below:

$ date -d @1474470084
Wed Sep 21 15:01:24 UTC 2016

But what if we’d do it on the fly, within our MySQL query? Enter MySQL’s from_unixtime() function. The value is expressed in the current time zone but we don’t really care here.

mysql> SELECT uid,from_unixtime(access) FROM users_field_data WHERE uid = 1;
+-----+-----------------------+
| uid | from_unixtime(access) |
+-----+-----------------------+
|   1 | 2016-09-19 21:08:19   |
+-----+-----------------------+
1 row in set (0.00 sec)

Okay, this is much better. We no longer have to switch context and have our human-readable date. Now, since we want to get basic usage stats, we can also choose to reformat the output to better GROUP BY later. I went with %Y (year) only but it’s flexible and you totally can get fancy if you will.

mysql> SELECT uid,from_unixtime(access, '%Y') AS last_access FROM users_field_data WHERE uid = 1;
+-----+-------------+
| uid | last_access |
+-----+-------------+
|   1 | 2016        |
+-----+-------------+
1 row in set (0.00 sec)

We’ve now narrowed-down things enough to actually get our final MySQL one-liner. What we’re doing below is simply to group user accounts so that we can calculate the number of ‘active users’ (which year they last logged in) and break it down accordingly.

mysql> SELECT COUNT(uid) AS number_of_users, from_unixtime(access, '%Y') AS last_access FROM users_field_data GROUP BY from_unixtime(access, '%Y') ORDER BY last_access DESC;
+-----------------+-------------+
| number_of_users | last_access |
+-----------------+-------------+
|             210 | 2016        |
|             106 | 2015        |
|               6 | 2014        |
+-----------------+-------------+
3 rows in set (0.00 sec)

If you’re getting the below error, make sure to read the MySQL 5.7 documentation. Using GROUP BY has changed in MySQL 5.7.5+ and its usage is now stricter.

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'database.table.column'; this is incompatible with sql_mode=only_full_group_by

---

---

---

---