Digging down to users - possible?

[tommygunner8]

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

[maschoff]

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

[tommygunner8]

Ok, thank you.

[tommygunner8]

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

[maschoff]

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

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

[tommygunner8]

Cool, thank you.

[lord_alan]

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

[maschoff]

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

[lord_alan]

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

That makes sense.

Thanks

[paull]

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;

[lord_alan]

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

[ptandler]

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.

[ptandler]

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;

[lord_alan]

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

[ptandler]

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.