0

I would like to use data view _CLICK to extract all a list of all clicked URLs along with the count (how many unique subscribers clicked on the link) for specific 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:

JobID URL LinkName Total_Count
1 www.google.com CTA_1 10
1 www.yahoo.com CTA_2 4
2 www.hotmail.com link_top 50
2 www.spacex.com footer_link 10

It would be great to include email name in the output (if possible).

1

Update: I ended up creating the query

SELECT TOP 1000 c.JobID, MAX(j.EmailName) AS EmailName, c.LinkName, c.URL, c.IsUnique, count(distinct c.SubscriberID) as Total_Count
FROM _Click c WITH (NOLOCK)
INNER JOIN _Job j WITH (NOLOCK) ON j.JobID = c.JobID
where c.JobID IN ('123456','111111')
and c.IsUnique = 'true'
GROUP BY c.jobid, c.URL, c.LinkName, c.IsUnique
ORDER BY c.jobid, count(distinct c.SubscriberID) desc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.