Prune subscribers who haven't opened an email in X time

[ajoslin]

Hi, first of all thanks love your project. Way more polished and simple than similar OSS alternatives, and am making extensive use of the multiple SMTP provider support.

Feature question: I’d like to delete subscribers who haven't opened any email in last X weeks.

Is there a way to do this or to query for this?

[MaximilianKohler]

Being able to resend to unopens would be nice too, but as far as I can tell, there is no tracking of who opened or didn't open, despite the "Individual subscriber tracking" setting.

Also, open tracking is very inaccurate anyway #1025, so I don't think deleting unopens is a good idea.

[ajoslin]

I guess will have to dig into the source code and see if it's possible with a cron job

[knadh]

You can set a psql cron with something like this.

DELETE FROM subscribers WHERE id = (
     SELECT subscribers.id FROM subscribers LEFT JOIN campaign_views
     ON (subscribers.id = campaign_views.subscriber_id)
     WHERE campaign_views.created_at < NOW() - INTERVAL '14 days'
);

[ajoslin]

Coming back to say - did this to prune all >60 day old subscribers who haven't opened in 60 days. Thanks @knadh

DELETE FROM subscribers where id in (
  select DISTINCT(subscribers.id) FROM subscribers 
  LEFT JOIN (
    select * from campaign_views where created_at > NOW() - interval '60 days'
  ) views ON views.subscriber_id=subscribers.id 
  WHERE views is null and subscribers.created_at < NOW() - interval '60 days' and status='enabled'
);