OpenEMM Technical Support Forum

Posted: **Wed Nov 26, 2008 9:31 pm**

Is it possible to find out what specific users have viewed an email?

Posted: **Thu Nov 27, 2008 3:53 pm**

Not via the frontend, only by digging into the database.

Posted: **Mon Dec 01, 2008 6:45 pm**

Ok, thank you.

Posted: **Tue Dec 02, 2008 1:11 am**

Would you happen to know what table I need to look at in order to see who read or clicked on a link?

Posted: **Tue Dec 02, 2008 8:25 am**

Please see here for a description of the OpenEMM database tables:

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

Posted: **Tue Dec 02, 2008 7:39 pm**

Cool, thank you.

Posted: **Mon Jan 26, 2009 3:11 pm**

Is there a "good" reason why the user detail information is not displayed in the admin system?

I think that in Germany there are some quite heavy restrictions on personal information such as this so maybe it is a legal rather than a technical issue?

Just curious...

Alan

Posted: **Mon Jan 26, 2009 3:13 pm**

Our commercial customers (marketing people) would be overwhelmed ...

Posted: **Mon Jan 26, 2009 3:26 pm**

Ahhh - you mean like they'd have thousands of pages of information to view?

That makes sense.

Thanks

Posted: **Fri Feb 27, 2009 4:26 pm**

select c.email, c.firstname, c.lastname,
l.ip_adr, l.change_date,
u.shortname
from rdir_log_tbl l,
rdir_url_tbl u,
customer_1_tbl c
where l.url_id = u.url_id
and l.customer_id = c.customer_id;

Posted: **Fri Feb 27, 2009 5:09 pm**

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;

And to get all users who just opened the mail I use:

Code: Select all

SELECT
  customer_1_tbl.firstname,
  customer_1_tbl.lastname,
  customer_1_tbl.email,
  onepixel_log_tbl.change_date
FROM onepixel_log_tbl
LEFT OUTER JOIN customer_1_tbl on onepixel_log_tbl.customer_id = customer_1_tbl.customer_id
WHERE onepixel_log_tbl.mailing_id = 'MAILING_ID'
ORDER BY onepixel_log_tbl.change_date ASC;

Just replace the 'MAILING_ID' with the id number of the mailing you want to investigate.

HTH someone.

Alan

Posted: **Sat Mar 14, 2009 10:48 pm**

ma wrote:Our commercial customers (marketing people) would be overwhelmed ...

What about adding the recipient's click count (total and/or last mailing) in the overview list of recipients as a new column? I think this would not be confusing. And you could use it to sort by click count, which I expect to be helpful also for marketing people.

Posted: **Sat Mar 14, 2009 11:34 pm**

Here are some more variations.

Get the number of URLs clicked per recipient:

Code: Select all

select c.email, c.firstname, c.lastname, count(l.customer_id) from rdir_log_tbl l, customer_1_tbl c where l.customer_id = c.customer_id group by c.email order by count(l.customer_id) desc;

Get the number of URLs clicked per recipient in a given mailing (replace 'MAILING_ID' with the ID of the mailing):

Code: Select all

select c.email, c.firstname, c.lastname, count(l.customer_id) from rdir_log_tbl l, customer_1_tbl c where mailing_id='MAILING_ID' and l.customer_id = c.customer_id group by c.email order by count(l.customer_id) desc;

Get the count, how often a mailing was opened per recipient (replace 'MAILING_ID' with the ID of the mailing):

Code: Select all

select c.email, c.firstname, c.lastname, l.open_count from onepixel_log_tbl l, customer_1_tbl c where mailing_id='MAILING_ID' and l.customer_id = c.customer_id group by c.email order by count(l.customer_id) desc;

Get the count, how often all mailings were opened per recipient:

Code: Select all

select c.email, c.firstname, c.lastname, sum(l.open_count) from onepixel_log_tbl l, customer_1_tbl c where l.customer_id = c.customer_id group by c.email order by count(l.customer_id) desc;

Posted: **Sun Mar 15, 2009 12:16 pm**

Some nice ideas there Peter,

If I get time I might have a go at building a few new statistics pages for OpenEMM.

Cheers

Al

Posted: **Sun Mar 15, 2009 8:44 pm**

Would be nice, Al, I'm looking forward

I was thinking about this myself, but in the next month I won't have time for sure ...

Instead I had another idea:

In addition to the overview list, the recipient's detail view could also get some statistics information, e.g. no of clicks total, no of mailings recieved / open / open count, list of mailings recieved, list of URLs clicked.

OpenEMM Technical Support Forum

Digging down to users - possible?

Digging down to users - possible?

SQL to show who Clicked Through

Adding the click count in recipient list?

Get click counts per recipient