OpenEMM Technical Support Forum

Sometimes I use a custom field "product_xyz_counter" for the recipient and create a custom action:

- profile modification
- product_xyz_counter + 1

With this information I could create a new target group with all recipients with product_xyz_counter > 1

However, now I need to create a new target group based on all recipients which clicked a specific link in the last newsletter for which I didn't hat this action.
If I look at the statistic I can see how much clicks this link has, but I can't see from whom.
Is this information (from which recipients are this clicks) stored somewhere in the database or in the logs?

If yes, I could write a little script to update my "customer_1_tbl"

OpenEMM version is 2011

thanks in advance

You can find this stuff in the database. Have a look at the extension architecture guide which provides a complete documentation of the OpenEMM database schema.

Thanks, that looks promising.

I searched the database mysel, but as I only have ssh-access and the mysql cli this is not an easy task.

I found this link in this forum: http://www.openemm.org/fileadmin/docs/O ... le-Doc.txt

1. Step: find the unique id of the link from rdir_url_tbl
SELECT url_id FROM rdir_url_tbl WHERE full_url = 'http://somewere.over/the/rainbow.html'

2. Step: Figure out who clicked the link
SELECT customer_id FROM rdir_log_tbl WHERE url_id = 12345

3. Step: Update customers
UPDATE customer_1_tbl SET product_xyz_counter = product_xyz_counter +1 WHERE customer_id IN (...)

4. Step: ???

5. Step: Profit!!!

This can be achived in one query (haven't testet it yet).

UPDATE customer_1_tbl c
SET product_xyz_counter = product_xyz_counter +1
INNER JOIN rdir_log_tbl l ON c.customer_id = l.customer_id
INNER JOIN rdir_url_tbl u ON l.url_id = u.url_id
WHERE u.full_url = 'http://somewere.over/the/rainbow.html';