OpenEMM Technical Support Forum

lord_alan wrote:I did it a bit differently. I didn't want every click event.

To get link tracked customers for a particular mailing I use this query:

Code: Select all

SELECT
  customer_1_tbl.firstname,
  customer_1_tbl.lastname,
  customer_1_tbl.email,
  rdir_log_tbl.change_date,
  rdir_log_tbl.url_id,
  rdir_url_tbl.full_url
FROM rdir_log_tbl
LEFT OUTER JOIN customer_1_tbl ON rdir_log_tbl.customer_id = customer_1_tbl.customer_id
LEFT OUTER JOIN rdir_url_tbl ON rdir_url_tbl.url_id = rdir_log_tbl.url_id
WHERE rdir_log_tbl.mailing_id = 'MAILING_ID'
ORDER BY rdir_log_tbl.change_date ASC;

Alan

Hey, but where should I add this query to see results?

Hey, but where should I add this query to see results?

I just run it in phpmyadmin for the time being. When I get some time, I'd like to create a couple of new pages for the backend but time is very scarce right now.

What would be the My SQL query to pull all users in the database that opened mail?

What would the MySql query look like to extract all the users that click links inside the open mail?

I would like to clean my databases of the dead emails that never get opened.

Cyberfab007

You can find a doc of the OpenEMM database schema here:

http://www.openemm.org/fileadmin/docs/O ... le-Doc.txt