1

I'm looking to create a report based on a column in my sendable Data Extension used in a deployment in Salesforce Marketing Cloud.

Ideally, I would like the report to look similar to "Email Performance by Attribute" which is based on a list model.

Is there a sample query I can reference to target a specific column in my data extension to retrieve tracking details of a deployment that was sent out?

2
  • Can you provide more info on exactly what you mean? Like do you mean you want to get sent/open/click count based on unique values in a column? e.g. column Group has values 'A,B,C' in it and you want to find out the tracking info based on those in Group A, Group B and Group C. e.g. Group A | 34 | 10 | 2 Feb 13, 2020 at 13:49
  • Thanks for the reply. Like do you mean you want to get sent/open/click count based on unique values in a column? Exactly! I would like a breakdown based on the standard metrics of the send "sent/open/clicks etc"
    – Michael
    Feb 13, 2020 at 14:03

1 Answer 1

1

This is far from optimized, but its something that I have used in the past for a similar need:

Select a.myGroupField,
MAX(sen.Sent) as Sent,
MAX(ope.Opens) as Opens,
MAX(uope.UniqueOpens) as UniqueOpens,
MAX(cli.Clicks) as Clicks,
MAX(ucli.UniqueClicks) as UniqueClicks
FROM [myDE] a
LEFT JOIN (
    SELECT b.myGroupField,
    COUNT(se.SubscriberKey) as Sent
    FROM [myDE] b
    LEFT JOIN (
        SELECT s.SubscriberKey
        FROM [_Sent] s
        WHERE s.JobID = 123456
    ) se
    ON b.SubscriberKey = se.SubscriberKey
    GROUP BY b.myGroupField
) sen
ON a.myGroupField = sen.myGroupField
LEFT JOIN (
    SELECT b.myGroupField,
    COUNT(op.SubscriberKey) as Opens
    FROM [myDE] b
    LEFT JOIN (
        SELECT o.SubscriberKey
        FROM [_Open] o
        WHERE o.JobID = 123456
    ) op
    ON b.SubscriberKey = op.SubscriberKey
    GROUP BY b.myGroupField
) ope
ON a.myGroupField = ope.myGroupField
LEFT JOIN (
    SELECT b.myGroupField,
    COUNT(uop.SubscriberKey) as UniqueOpens
    FROM [myDE] b
    LEFT JOIN (
        SELECT uo.SubscriberKey
        FROM [_Open] uo
        WHERE uo.JobID = 123456
        AND uo.IsUnique = 1
    ) uop
    ON b.SubscriberKey = uop.SubscriberKey
    GROUP BY b.myGroupField
) uope
ON a.myGroupField = uope.myGroupField
LEFT JOIN (
    SELECT b.myGroupField,
    COUNT(cl.SubscriberKey) as Clicks
    FROM [myDE] b
    LEFT JOIN (
        SELECT c.SubscriberKey
        FROM [_Click] c
        WHERE c.JobID = 123456
    ) cl
    ON b.SubscriberKey = cl.SubscriberKey
    GROUP BY b.myGroupField
) cli
ON a.myGroupField = cli.myGroupField
LEFT JOIN (
    SELECT b.myGroupField,
    COUNT(ucl.SubscriberKey) as UniqueClicks
    FROM [myDE] b
    LEFT JOIN (
        SELECT uc.SubscriberKey
        FROM [_Click] uc
        WHERE uc.JobID = 123456
        AND uc.IsUnique = 1
    ) ucl
    ON b.SubscriberKey = ucl.SubscriberKey
    GROUP BY b.myGroupField
) ucli
ON a.myGroupField = ucli.myGroupField
GROUP BY a.myGroupField

Do note that this query is fairly heavy and if you are looking at a significant volume in your sending audience, it may time out. If so, you can break the query into multiple parts and run each individually to fill in the target DE.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .