Q: How can I keep statistics and do reporting on my LISTSERV list usage?

Answer by Liam Kelly
Senior Consulting Analyst, L-Soft

List owners commonly would like details on how their list usage has changed over time. Do I have more subscribers than last year, or fewer? When was the last posting for my list? What is my busiest month for list traffic? Did the flame war on my discussion list last month cause people to unsubscribe? Did adding a link on my main web page cause an increase in new subscribers? Did new spam blocking result in mass auto-deletions over the last six months?

LISTSERV's change-log feature is designed to answer exactly these sorts of questions. Change-logs keep track of all activities on a LISTSERV list, whether they are message postings, subcribes, unsubscribes, address changes or auto-deletions. They allow the list owner to track such activities in great detail without having to do manual bookkeeping and without having to ask the site administrator for the main LISTSERV logs.

Adding a Change-log to your LISTSERV list can be as simple as adding a single list header keyword:

* Change-log= Yes

Adding the Change-log keyword as above will cause LISTSERV to create a listname.CHANGELOG file in the main LISTSERV directory (\LISTSERV\MAIN on Windows, ~listserv/home on Unix). From the time that you enable the change-logs, LISTSERV will begin recording all list activities. Change-logs are not retroactive; they collect new data but cannot provide information from before the feature was enabled.

The easiest way to access the reporting data contained in the change-logs is through the LISTSERV web interface. Under the "List Management" menu, select "List Reports > List Activity Reports". Then select the list for reporting from the "Select List" drop-down. There are two types of reports: history reports and statistics reports. The history report shows the full history for the selected time period. For example, we could request a history of all auto-deletions for the month of January, and LISTSERV would return a line for each Auto-Delete, showing the address that was deleted, the date and time of the deletion and the reason for the deletion:

The statistics report returns summary information. For example, we might request a monthly interval report of all auto-deletions for 2008. In that case, LISTSERV would return one line per month, showing the number of deletions for each month:

These reports can also be automatically downloaded in CSV (comma-separated values) format for easy import into a spreadsheet program. To enable CSV downloads, we need to switch our LISTSERV web interface settings from "Basic Mode" to "Expert Mode". In the upper-right corner of the browser, next to the link for "Edit Page", click the link that says "Basic Mode". Alternatively, you can also click the "Preferences" link in the main navigation bar.

This will open the Preferences screen. Change the "Mode" drop-down to "Expert Mode" and click "Submit". Then return to "List Management > List Reports > List Activity Report". Under the "Report Format" drop-down, we can now select either "Web Site", "History in CSV Format", or "Stats in CSV Format". Running one of the CSV reports will prompt the browser to download the CSV file and either save it or open it in the spreadsheet program of our choice.

It is also relatively easy to write our own program to parse the change-log files directly. The *.changelog files are in plain text and have an easy-to-parse format:

20090213121245 SUBSCRIBE user@EXAMPLE.ORG Sample User
20090213121248 POST user@EXAMPLE.ORG First Post!
20090213121250 AUTODEL user2@EXAMPLE.ORG 5.1.1 550 5.1.1 <user2@EXAMPLE.ORG>... User unknown
20090213121304 ADD user3@example.org Third User

The file is space-delimited. The first field is a timestamp, the second shows the action type, the third shows the subscriber email address for the action, and the remainder of the line depends on the action type. For POST events, the line shows the Subject line of the message. For SUBSCRIBE/ADD commands, it shows the Name field. For AUTODEL events, it contains the Status message from the non-delivery notification. Using a fairly simple script, we could easily build our own list activity reports by working directly with the change-log files.

It is also possible to tell LISTSERV to automatically rotate the change-log file for a list by specifying an interval. For example, to rotate the change-log monthly:

* Change-log= Yes,Monthly

Instead of a single listname.changelog file, that will produce a listname.changelog-YYYYMM file for each month. Change-logs may be rotated yearly, monthly, weekly or daily. Rotating change-logs makes no difference to the web interface reporting but allows for smaller change-log files for high-volume lists for which a single change-log would be cumbersome.

If LISTSERV is configured for DBMS access, it is also possible to write the change-log data directly to a database table. This is done by setting the CHANGELOG_DBMS, CHANGELOG_DBMS_CONNECTION and CHANGELOG_DBMS_TABLE site configuration parameters.

For further information about these site keywords, see the LISTSERV 15.5 Site Configuration Keyword Reference. For further information about configuring LISTSERV for DBMS access, see the LISTSERV 15.5 Advanced Topics Manual.