Wednesday, October 17, 2012

Time only goes forward, or does it? A time turner for civimail bad links!

Recently a client sent out an email with a bad link in it.

Normally, I'd sympathise with them but tell them that the horse has bolted and there's no point in closing the barn doors (because I like to use old fasioned expressions).  (And yes - if the link was on my server, I could give them an apache redirect.)

But, if the message went out via civimail and you're tracking click-throughs of links (actually this trick also works with simplenews if you're using simplenews_stats), then there's still hope.

This is because the actual link embedded in the mail isn't the final destination, but a civicrm url with a magic trackable url id, which then redirects to the final url, which is stored in a civicrm table. The rest you can probably do as a simple homework exercise, but here's the details since I've got more room for this post.

Start with loading up a mysql client and use your civicrm database, then find out the right id using this:

select * from civicrm_mailing_trackable_url order by mailing_id desc limit 30;

The first thing you'll want to do is fix that with something like

update civicrm_mailing_trackable_url set url = 'your-corrected-url' where id = the-id-of-the-bad-url;

If you caught it early enough, then you're done, but you might want to try this query as well:

select distinct contact_id from civicrm_mailing_event_trackable_url_open o inner join civicrm_mailing_event_queue q on o.event_queue_id = where trackable_url_id = the-id-of-the-bad-url;

Which will give you a list of the contact ids of people who have already clicked through your bad url - in which case you can resend them the corrected version and some humble pie.