I am using a database table _CLICK and joining with another table _JOB to extract a list of all clicked URLs from an email along with the count (how many unique subscribers clicked on the link) for specific emails (aka job ids).
Can anyone help me with the approach? This is what I got so far SELECT c.JobID, j.EmailName, c.LinkName, c.LinkContent, c.URL, c.IsUnique, count(*) as Total_Count FROM _Click c INNER JOIN _Job j ON j.JobID = c.JobID where c.JobID IN ('123456','111111') and c.IsUnique = 'true' GROUP BY c.jobid, c.URL
Basically I am looking for an output like this:
It would be great to include email name in the output (if possible).