Plugins: Tracker - Add a date/timestamp to options for updating profile fields in the preferences

[justingit]

Here's the current list, this would add a date/timestamp. I guess the question is, which one? My vote is just Unix time

[dickmo]

Format should be at least yyyy-mm-dd. so it can be sorted in the proper order. Max would be yyyy-mm-dd hh:mm:ss which is the mysql current_timestamp()

[justingit]

If there's no time zone, I don't see a reason to make the value being saved be all that complicated. Also the sql column will still just be, "TEXT". Saving say, a timestamp value in a text field is going to confuse people .

[dickmo]

Then just a date would be great. Know when the receiver opened up the letter. I am assuming that the server knows the time zone. Viewing the member list one can see if that member opened up any email today of 4 weeks ago or never. In a text field it still can be sorted does not have to be defined as date in the DB text is fine.

[justingit]

Email opens are already timestamped, and that data can be queried. I guess it's useful to see it in the membership: view screen, but it would be doubling up of data. If it's not very obvious, tracking users in this way isn't a direction I'm very aggressive in pursuing.

[dickmo]

The profile fields are fields that I can create for my use. With the data being updated in (last time a list member opened a newsletter) I can run a sql report on what our members have seen. It is just useful for me. You allow other data to be updated by the system Ip number zip code country etc All I am asking is the add in the last time the person opened any newsletter.

here is a sql that I use to see who is opening the newsletter.

select list,dada_profile_fields.last_name, dada_profile_fields.first_name, dada_profile_fields.who, dada_profile_fields.lastupd, dada_mass_mailing_event_log.timestamp, dada_mass_mailing_event_log.event, dada_mass_mailing_event_log.email from (dada_mass_mailing_event_log dada_mass_mailing_event_log INNER JOIN dada_profile_fields dada_profile_fields ON dada_mass_mailing_event_log.email = dada_profile_fields.email) where lastupd = timestamp order by list

I can remove the where clause and get every newsletter that was opened in the past but that will generate way too much. Or I can limit it to a timestamp > than some date.

If you dont want to do this then I will have my IP provider create a trigger that updates this profile field every time you re create or modify it in your program.

My trigger code is this and it does work

CREATE DEFINER = 'sbccsail'@'localhost' TRIGGER dada_profile_add BEFORE INSERT ON dada_profile_fields FOR EACH ROW set new.lastupd = current_timestamp();

Richard Mourino

------ Original Message ------ From: "Justin J" @.> To: "justingit/dada-mail" @.> Cc: "dickmo" @.>; "Comment" @.> Sent: 6/26/2021 4:39:23 PM Subject: Re: [justingit/dada-mail] Plugins: Tracker - Add a date/timestamp to options for updating profile fields in the preferences (#1032)

Email opens are already timestamped, and that data can be queried. I guess it's useful to see it in the membership: view screen, but it would be doubling up of data. If it's not very obvious, tracking users in this way isn't a direction I'm very aggressive in pursuing.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/justingit/dada-mail/issues/1032#issuecomment-869059079, or unsubscribe https://github.com/notifications/unsubscribe-auth/AMIAZYJN5ZXHXIYGOEES2LTTUY3HXANCNFSM47LR2QGQ.

[justingit]

I understand it's useful for you - that's a great example. IP addresses tied to email addresses is PII, so it's something I have think very long and hard if I want to add as a feature. It makes it a little more niche, as you have to enable ip logging, create the profile field, set up the Tracker plugin, etc.

[dickmo]

I really don't need the IP number. My sql matches on email address in profile_field to email address on email in mass mailing event log. I can restrict the output using timestamp >= curdate() or timestamp >= date_sub(curdate(), interval 2 day) 2 days

Our discussion has lead me to find other ways to report on who is opening and who is not. At this point I dont need you do do anything as the lastupd field I may not even need. I need to play around with my sql to see if It can produce the info That I need .

If I get there I will share it with you.

Richard Mourino

------ Original Message ------ From: "Justin J" @.> To: "justingit/dada-mail" @.> Cc: "dickmo" @.>; "Comment" @.> Sent: 6/27/2021 10:43:18 AM Subject: Re: [justingit/dada-mail] Plugins: Tracker - Add a date/timestamp to options for updating profile fields in the preferences (#1032)

I understand it's useful for you - that's a great example. IP addresses tied to email addresses is PII, so it's something I have think very long and hard if I want to add as a feature. It makes it a little more niche, as you have to enable ip logging, create the profile field, set up the Tracker plugin, etc.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/justingit/dada-mail/issues/1032#issuecomment-869174814, or unsubscribe https://github.com/notifications/unsubscribe-auth/AMIAZYIWCMVF3NON2DV74ELTU42INANCNFSM47LR2QGQ.

[justingit]

I've added support for unix timestamp in https://github.com/justingit/dada-mail/commit/81cbfe2f33ee33709e4716af98cc3ccde66298e3 - I'll keep this issue open until it merges with master and gets released.