long time user of civicrm. Have 4.7.6 installed on Drupal 7 in a linux environment and I'd like to look at an old email's metrics. I see I pull up specific URL click-through metrics, but it shows me ALL the clicks since I sent out the email to-date. I'd like to filter those results to only include a specific date range, but am having difficulty in finding how to do this. I would even be comfortable exporting all the results myself and then filtering in excel, but the export doesn't include dates at all. Am I the only one who needs this kind of thing? If I have to go into the DB tables to find what I'm looking for, where can I find it?
1 Answer
There is currently no date filter on the mailing reports.
Adding date filters to the mailing reports and submitting a patch back to core would be one way to solve your problem. https://github.com/civicrm/civicrm-core/blob/master/CRM/Report/Form/Mailing/Clicks.php shows the code that generates the clickthrough report. Adding a date filter to that might not be too difficult to do.
It would end up being similar to the date time filter in the activity report: like the https://github.com/civicrm/civicrm-core/blob/master/CRM/Report/Form/Activity.php#L261 but from the looks of things you'd have to do a bit more playing around with the Report class than just adding it.
The mysql tables you are looking for are civicrm_mailing_event_trackable_url_open (which has a timestap) and civicrm_mailing_trackable_url
