We have around 3000 templates created since our Business Unit was set up and most of them were of one time use only and never deleted after the use.

How/If we can pull a report in Marketing cloud to list all the Email templates in the Content Builder and add a column to the report tahat when was it last used/send from Marketing cloud and also how many times has this email templates been used after it's creation in Marketing cloud.

  • Do you mean template or email? As in an actual SFMC 'Template' that is used as a base 'skeleton' to build emails on or just the Email object that you use when sending a job? Jan 5 at 14:13
  • @Gortonington , we need the list of templates and how many times they have been used for sending the emails and when was it last used for sending so that we can delete the ones used long time in the past and are not required as of now based on the usage. Jan 6 at 8:53

Templates are not used for sending. I am assuming what you actually mean is Email. These are what you assign data to in order to create a job that sends an email to an attached audience.

Your best way to validate this would be via a SQL Query on the Job Dataview. Now this would only give you a six month look-back, but from the sound of things, that should fit in your needs as you are only looking for most recent sends and most recent activity.

Something like:

Select EmailID,
Max(EmailName) as Name,
Count(*) as TotalSends,
Max(DeliveredTime) as LastSentDate
FROM [_Job]

Should get you there and you can assume that if it is not on there, then it has not been used recently or is a brand new template.

I use EmailID as the key in this query because the name of the email can be altered, but the ID of the email will always stay the same. Which is why I have Max(EmailName) be what defines the name of the email. Any query with GROUP BY in SFMC requires all 'non-key' (not in GROUP BY statement) to be aggregate functions (MAX,MIN,COUNT,SUM,etc) so I set MAX() around EmailName so it can be grabbed but will not need to change the output. As a note, with MAX, it will pull only the name that is 'highest' in the alphabet, so you will not see any of the alternative names.

If you need to look further back, I would look at a series of tracking extracts that pull your required timeline worth of data. The output you would want is 'SendJobs'. This will be very similar to the _Job Dataview.

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.