Clicks Reporting Help!

Hi all,

I am trying my hardest to hack my way to getting daily click report. I have managed to generate nice reports through mysql for sent, opened, unsubscribed but for the life of me cannot find how I can extract this from mysql.

Any ideas what tables/fields would hold this data ?

Thanks in advance.


You could start with some thing like this maybe, filtered to one day?

In terms of the database here are the queries for that report, found by using dev mode:

Amazing Ruth!! Thank you.

For anyone reading this, to start with you must choose Email Stats as data source.

@rcheesley - how do I get to debug mode ?

1 Like

If you install locally by cloning from Github you will have the index_dev.php file - you just append that to the site - you can check on also.

1 Like

To give a bit more light on this thread after Ruth showed me some magic to see queries reports are generating I played a bit with the DB, I am not 100% sure that my query is correct but I have checked it with a number of mailers and I seem to be getting correlating number of clicks and unique clicks.

My queries were as follows:

clicks: select count(tracking_id) from page_hits where date_hit like “2021-02-08%” and email_id is not NULL;

uniq_clicks: select count(distinct tracking_id) from page_hits where date_hit like “2021-02-08%” and email_id is not NULL;