Timestamp for subscribers [#948200]

Hey,
Recently a client of mine requested to view newsletter subscribers by date they joined the newsletter. I have created a simple module, that attaches to current simplenews using Trigger and Actions to attach the timestamp. It also alters the `simplenews_subscribers` table to add the timestamp(int) column.
Also it adjusts the simplenews subscribers list admin table. This features can be easily patched to the core module, if it's a general requirement, or it can be easily used as an addon module for those who want this subscribers date feature.

Please let me know what you think,
Gabriel

Comment	File	Size	Author
#8	simplenews_timestamp_admin-948200-8.patch	2.27 KB	sin
#8
#6	simplenews_948200_6_timestamp_apply.patch	2.51 KB	miro_dietiker
#6
#6	simplenews_948200_6_timestamp_admin.patch	2.02 KB	miro_dietiker
#6
#2	simplenews-[948200].patch	4.39 KB	gabrielu
#2
	simplenews_timestamp.tar_.gz	2.5 KB	gabrielu

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

miro_dietiker

Switzerland

CreditAttribution: miro_dietiker commented 21 October 2010 at 18:37

Did you check the snid_tid table?
We have there information about the subscription/unsubscription timestamp to the individual newsletters.

Since there can also happen unsubscriptions that update the entry i think we also should add a column to the subscriber (creation time) also.
At least we should also check D7 for current changes of this functionality...

See code below pointing to locations where timestamp changes
@file simplenews.module

function simplenews_unsubscribe_user
...
function simplenews_subscribe_user
...

So again -- i would also like to see when the original first occurrence of a mail address happened.

Could you add a patch against 6--2 to add this column?
BTW: we should also add an update routine that copies the data from the oldest subscription of each snid after adding the field to the table to reconstruct data where available.

Comment #2

gabrielu CreditAttribution: gabrielu commented 22 October 2010 at 08:44

File	Size
simplenews-[948200].patch	4.39 KB

Right, I found the timestamp in snid_tid.
Actually I was working on a live site, with latest stable, wich is 1.x
But I easily ported changes to the 2.x-DEV branch. Please see my patch.

I have added the schema alter and update routine to update timestamp from the latest active subscription timestamp in snid_tid in .install
I have changed query in .module
function simplenews_unsubscribe_user
And also added timestamp to the admin table view at admin/content/simplenews/users

Comment #3

miro_dietiker

Switzerland

CreditAttribution: miro_dietiker commented 23 October 2010 at 01:10

Status:

Active

» Needs work

Looks fine with two caveats:

- Did you run the UPDATE SELECT? I'm not perfectly fluent to all those variants but it looks a little weird.

- Additionally: adding a further column makes simplenews wider again.
I think we're much over the size we should stay in - so i'm against adding this as an additional full column in the table display.
Ideas appreciated on how to remain compact or improve compactness (regarding the date to be displayed).

thx for more input.

Comment #4

gabrielu CreditAttribution: gabrielu commented 23 October 2010 at 10:58

As for the select, yes it's tested, It's a basic query in query and I couldn't find a better way of updating timestamp. Actually if I look better it should be:

UPDATE {simplenews_subscriptions} s
    SET s.timestamp = (SELECT MIN(timestamp)
                  FROM {simplenews_snid_tid}
                  WHERE snid = s.snid AND status = 1)

instead of

UPDATE {simplenews_subscriptions} s
    SET s.timestamp = (SELECT MAX(timestamp)
                  FROM {simplenews_snid_tid}
                  WHERE snid = s.snid AND status = 1)

as we need the oldest timestamp, for a newsletter user has subscribed to.
As for timestamp column in table, I had client request for this, as he wanted to view subscriptions ordered by date. I don't know what would be world needs in this area. Maybe it would be better to have timestamp only added to export. (did you see my addon module http://drupal.org/node/948206) It exports subscribers in a CSV as: Email,Timestamp.

Comment #5

gabrielu CreditAttribution: gabrielu commented 2 November 2010 at 13:39

Let me know what you think. I could merge those two functionalities into one patch if so.

Comment #6

miro_dietiker

Switzerland

CreditAttribution: miro_dietiker commented 3 November 2010 at 10:24

File	Size
simplenews_948200_6_timestamp_admin.patch	2.02 KB

simplenews_948200_6_timestamp_apply.patch	2.51 KB

I compared to the standard admin/content/node table whereas no timestamp is displayed by default - but used as order.
It is very important not to output too many columns. It would then be possible to display the timestamp with a custom module if needed.
However i've tried adding a hidden default sort (as on node table) to display the most recent changes on top. Here i hit a limitation of drupal table header / sort / order implementation: this is not possible.
If we want to sort it by date (by default), we need to output it. For more details check:
http://api.drupal.org/api/drupal/includes--theme.inc/function/theme_table
http://api.drupal.org/api/drupal/includes--theme.inc/function/_theme_tab...
http://api.drupal.org/api/drupal/includes--tablesort.inc/function/tables...
Including related issue:
#295430: Default sort using tablesort_get_order() is broken.

In addition i've compared to the users list:
admin/user/user
and the comment list:
admin/content/comment

Here both SHOW the time.
However we have here a 1:n relationship. If a user is subscribed for a long time and changes a subscriptions, he won't be displayed on top. Even if we filter for this newsletter only. So the display is somewhat misleading.

ATM i've added the schema update and the data update part - corrected with MIN.
Also i've removed "where status = 1" to have data. It's better to have the unsubscription date there than to have no date.

I'm open to add timestamp data to lists and exports. Also i think we should display it on the subscriber edit page. However we should have a clear understanding of its usage and make sure users will understand it. Attached the patch applied and the modified patch for the admin which is remaining currently.

Inputs appreciated. :-)

Comment #7

gabrielu CreditAttribution: gabrielu commented 4 November 2010 at 08:43

I believe the best approach would be that, timestamp is users age in the newsletter system. Member of our newsletters from the 12'th of nov 2009.
This is how I see it.

Comment #8

sin CreditAttribution: sin commented 31 January 2014 at 09:56

Assigned:	Unassigned	» sin
Issue summary:	View changes
Status:	Needs work	» Needs review

File	Size
simplenews_timestamp_admin-948200-8.patch	2.27 KB

I also got request to order subscribes by date latest first :)

I see schema patches and a code to store timestamp in simplenews_subscriptions table is already comitted and working in 6.x-dev. Only display and sort parts are missing.

Here is the patch against 6.x-dev adding timestamp display and default timestamp desc sort to /admin/content/simplenews/users

P.S. I also found simplenews_realname module needs update to support this feature as it alters the same form and overrides form theming. I already got it working, will submit the patch to simplenews_realname if this will be commited.