0

I'm currently working on a project to automate a weekly email in SF Marketing Cloud. The ideal process involves uploading a csv to a data extension and using it to populate a table within the email. This is working well.

What I'm struggling with is figuring out the best way to allow the team managing the email to use this functionality. The team consists of people who are not familiar with AmpScript and I would like for them to not have to deal with changing any of the code. Ideally they could just upload a file to the FTP and Automation Builder would handle the rest.

Currently, I have a single Data Extension and a single email template which includes the necessary AmpScript to pull the data. My thinking is that the responsible team will upload their data to the same data extension every week, then create a new email using the template and the new data will be pulled.

However, this raises two issues. 1.) If the data is always overwritten there's no way to have a good archive from week to week. Though this could be solved by always keeping the original csvs used to populate the DE each week. And 2.) Tracking data shows the AmpScript instead of the populated information.

I originally used specific dates in the data extensions by using the FormatNow() function so each week would have a unique data extension and tracking seemed to work fine. However, this may require more legwork on the team handling the distribution.

I'm just not sure I'm going about this the right way or maybe I'm trying to do something too crazy. Is there a better way to accomplish this goal?

  • I'd thought about something like this, but it would require the sender to manually change that LookupValue. Which, I could do using a single AmpScript area and that would be the only thing they need to edit. RE: #2, When I send an email using this and go to look at my tracking date (opens, clicks, etc.) I am not seeing the fully populated table. Instead I'm seeing this. I want to be able to actually see the info as it appeared in an inbox. A user on the email slack recommended TreatAsContentArea() which I'm exploring now. – Eczekner Jan 16 '18 at 18:46
  • After the data is sent once, would it be used again? or is it one off every time? – Gortonington Jan 16 '18 at 19:20
  • It's a one off, changes each week. Really all I care about is tracking a link. These are product special emails, so the last column of each row will include a URL for the person to go buy the product. It would be helpful to be able to track those. – Eczekner Jan 16 '18 at 19:50
  • Have you utilized the ampscript function RedirectTo() on the stored URL? – Gortonington Jan 16 '18 at 20:06
  • If it is already being tracked in _Click, you could add an alias on the a tag that is a variable, perhaps have it be the name of the product so when you look at your _Click Dataview, you can lookup by the alias and then differentiate the email sends by the JobID. – Gortonington Jan 16 '18 at 20:10
0

If you want an archive of the data, create a separate DE with all the same fields, except add one more field as a second primary key "JobID'. I would also look at adding "SendDate" into it.

Then inside of the email at the bottom of the for loop add the below AMPscript:

%%[ UpsertDE(@yourArchiveDE, 2, "YourKey", @yourKey, "JobID", jobid, "SendDate", NOW()) ]%%

This will leave your current structure in place, requiring no changes - but will provide you an archive DE of all this information to reference if you need to.


****EDIT****

From your comments, it appears that you are more concerned with tracking the links than in the data. To solve for that:

You could add an alias on the a tag that is a variable, perhaps have it be the name of the product so when you look at your _Click Dataview, you can lookup by the alias and then differentiate the email sends by the JobID.


As far as for tracking image (Issue #2) - you will never be able to get that to display a rendered version. This is pulled from the raw email, prior to any AMPscript or SSJS being processed.

Your best bet is to either:

  1. Store HTML of the send inside of a DE and reference there for display - not recommended as it can get bulky and unweildy quickly.)
  2. Utilize an API call to generate a preview for your team to view (Email preview via api)

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.