Query Data View to find email Subject Line

Question

I am querying the job data view to retrieve the email details, which are part of a specific set of journey. The emails have a dynamic subject line populated through AMPScript. The query works fine except that the rendered subject line is not returned, it returns the AMPScript that was used, for example it shows the subject line as %%condition%% than 'XYZ- Your offer is here'. Would appreciate, if anyone can share any insight.

select
j.JourneyName as journey_name,
J.VersionNumber as version,
ja.ActivityName as email_name,
jb.TriggeredSendCustomerKey as TriggeredSend_ID,
jb.EmailSubject as Email_Subject_Line
from  _JourneyActivity ja 
join _Journey j
on ja.VersionID = j.VersionID
join _job jb
on ja.JourneyActivityObjectID=jb.TriggererSendDefinitionObjectID
where ja.ActivityType in ('EMAIL','EMAILV2')
and (j.journeyname like '%jjjj%' or j.journeyname like '%pppp%')

Answer 1

There's no way of doing this with Query Activities as the personalised content is not stored anywhere in Marketing Cloud. Only the email definition and the sendable data persist and these can be used to rebuild the personalisation in View as Web Pages. The actual personalised content sent is ephemeral, though.

Goronington's point is a useful one. Use an AMPScript variable to store your subject in, "@Subject", and add a field to your Send Log Data Extension that matches this variable's name - Subject(Text(254)):

In the body of your message:

%%[SET @Subject = 'Foo']%%

In the Subject field of your email:

%%=v(@Subject)=%%

Answer 2

This sounds like a duplicate of: Capture subject line in send log

If you are setting the SL dynamically, then you should write to a field in your sendlog with that parameter's value. That is the cleanest and easiest path.

Adam Spriggs' pointed out in the ticket listed above that the subject is available in the System Data Views and is accessible via Query Activity.

Per @AdamSpriggs:

For a particular send, you can query the EmailSubject and DynamicEmailSubject columns. I'd recommend building your query starting with _Job and then inner joining to _Sent based on the JobID (aka the SendID from the Tracking tab). I typically use _Job.PickupTime to narrow it down by date. Something like this:

select
j.emailName
, j.EmailSubject
, j.DynamicEmailSubject
, s.SubscriberKey
, s.EventDate
from _job as j with (nolock)
inner join _sent as s with (nolock) on (s.jobid = j.jobid)
where j.pickuptime >= dateadd(day,-7,getdate())

You can also join this back to your Send Log if you'd like (by JobID).