Question on Measures and Data Filters in Marketing Cloud

Question

I have an email which is sent as triggered send in Journey Builder. A few days into this being active, I noticed that one of the links was wrong (too many http:// notations) and so I changed the template and re-published the email template. The link is now correct and works perfectly.

However, I've been asked to do an email send to all the people who had the wrong link.

The first part I did manually - I exported the list of clicks from the tracking report, cut it down to only those who had the wrong link, then re-imported it to Marketing Cloud into a Data Extension.

The problem is, the email has some dynamic content and personalisation such as %%firstname%% and %%surveylink%%, which are not present in the new Data Extension.

I tried making a measure and data filter but got a bit lost and kept getting 0 subscribers.

What measure/data filter combination would I need to do something like:

Find all subscribers who received JobId 12345 and clicked a link that begins with "http://http://"?

Edit: So I've managed to pull through the firstname, but not a neighbouring column I need with the survey link in it. I get this:

In that first 'Here:' is supposed to be %%=v(@linkurl)=%% and the red image should link to it, and doesn't. Interestingly, when you do a send preview, the 'text' version of the email shows, but the html version doesn't. So I think my ampscript is ok.

Answer 1

If the personalization info you need is in an all subscribers attribute, you dont need it to be in the data extension, just use the personalization strings you indicated.

Personalization strings always check for a match on all subscribers profile attributes as well as the DE audience.

If they are not, you can use ampscript functions in the body of the email to pull from wherever the data is located.

Supposing that firstname is a column in a DE called 'subscribers', and that this DE has an 'emailaddress' column you could do this:

%%[
set @email = [email address] 
set @firstname = Lookup('subscribers','firstname','emailaddress',@email) 
]%%

This lookup function basically says return the value of firstname from the DE 'subscribers' where emailaddress equals this specific subscribers email address, then assign that value to @firstname.

To have it write to the html, instead of %%firstname%%, write:

%%=v(@firstname)=%%

Regarding your data filter question: I dont believe you can filter on tracking data with a data view without a solution like Audience builder.

You could, however, build a query activity and join in whatever columns were needed. This would return all the subscribers who meet your criteria:

select subscriberkey from _click 
where jobid = 12345
where linkcontent like 'http://http://%'

From that point, you'd want to add an inner join, likely on subscriber key, to include whatever other columns you'd like in your sendable DE.