1
SELECT 
Distinct D.URL AS [Link],a.jobid AS [JobID],F.Emailsubject AS [Email Subject],a.Country as Country,a.consumerlanguage as consumerlanguage,
CAST((SUM([Unique Clicks]) SUM([Sent]) * 100) AS DECIMAL(5,2)) AS CTR,

(
  SELECT COUNT(d.EventDate) 
  FROM _Click d
  WHERE a.JobId = d.JobId 
  GROUP BY d.JobId
) AS [Total Clicks],


(
  SELECT COUNT(DISTINCT CASE WHEN d.EventDate <> '' THEN Subscriberkey END) 
  FROM _Click d
  WHERE a.JobId = d.JobId
  GROUP BY d.JobId
) AS [Unique Clicks],

(
  SELECT COUNT(DISTINCT CASE WHEN b.EventDate <>'' THEN SubscriberKey END) 
  FROM _sent b
  WHERE a.JobId = b.JobId 
  GROUP BY b.JobId
) AS [Sent]


FROM [Click Activity - Sweden] a
Left JOIN _Sent B  ON A.JobID = B.JobID
left JOIN _Open C ON A.JobID = C.JobID
left JOIN _Click D ON A.JobID = D.JobID
left JOIN _Unsubscribe E ON A.JobID = E.JobID
left JOIN _Job F ON A.JObId = F.Jobid
left JOIN _Bounce g ON A.JobId = g.JobId
GROUP BY a.JobID,F.Emailsubject,ConsumerLanguage,Country,D.URL

I'm trying to re-create this set up from marketing cloud but some problems occurred. I have problem with replicate the "NET CTRs" so the highest value is on the top of the links. Trying to re-create this

Another problem that arrived recently is that if a only one email is sent out on a job id and the receiver don't open the email it breaks the CTR reporting on every job id before that occurrence. enter image description here

Any help would be appreciated!

Thank you!

Your Answer

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

Browse other questions tagged or ask your own question.