AMPScript for creating a dynamic URL in SFMC?

Question

This is my first post so I apologize for length, noob-ness and lack of understanding. I do really well writing IF/ELSE AMPScript for dynamic swapping of content within the body of an email. I have no problem having these statements look in to the linked DE and populate content based on that.

What I fail to understand is how to write an AMPScript that can create a dynamic URL based on DE columns/rows. Hopefully I can make sense of this for anyone willing to help with an answer.

My company has URLs in many of our marketing emails that are used for tracking purposes. They contain:

• The base URL (http://www.gosomeplace.com)
• The user ID from the SF DE applied to the send (?sfid=%%Account_ID%%)
• The source code. This changes per campaign but as an example: (19727)
• The list number. Again, this changes but an example would be: (11)
• Whether it's a front-end, back-end or launch type send. This is represented by a two digit code. For this, let's use (23)
• The IP being used for the send. Third time's the charm, this changes. Ex: (173)
• A variable used internally. Also changes per campaign (&client=FALSE-UTP)

So one URL in its entirety would look similar to this:

http://www.gosomeplace.com?sfid=%%Account_ID%%&source=19727-11-23-173&client=TRUE

Is there a way to hold all of this data for source, list, type, IP, etc. in a DE so that it can be populated through AMPScript? How can I pull that information in to my links from a separate data extension than the one being used to hold the subscriber lists? Please keep in mind that the %%Account_ID%% is kept with the subscriber data in a data extension I would not be using for the remainder of the URL behind that part.

The closest thing I found was this resource by Adam Spriggs: https://sprignaturemoves.com/ampscript-lookup-examples/

The simplest of his examples looks like this:

%%[
var @DEColumn1, @lookupValue
set @lookupValue = "whee"
set @DEColumn1 = Lookup("DataExtensionName", "ReturnColumn", "LookupColumn", @lookupValue)
]%%
DEColumn1 is %%=v(@DEColumn1)=%%

Unfortunately, I don't understand the overall breakdown of how this all works.

• var @DEColumn1, @lookupValue -- I get this. We're declaring var names. Fine.
• set @lookupValue = "whee" -- this is where I get lost. What is the purpose of this line? I see it referenced in the line below but I don't understand the function of it.
• set @DEColumn1 = Lookup("DataExtensionName", "ReturnColumn", "LookupColumn", @lookupValue) -- This starts off making sense but do I really only have to type the name of the DE in the first set of quotes, Column header name in the second set of quotes? I'm not sure what LookupColumn does and I am totally lost again with what @lookupValue does in this last position inside the parentheses.
• DEColumn1 is %%=v(@DEColumn1)=%% -- My understanding here is that we're now pulling in the variable @DEColumn1 right behind some body copy.

So there you have the best breakdown I can offer of my feeble attempt to have my brain understand this method of AMPScripting.

Can anyone shed some more light or point me in the direction of good learning resources online for this that don't come at an astronomical cost?

Answer 1

Your solution is relatively simple. Combine Lookups/AttributeValue functions with a CONCAT and RedirectTo.

example:

%%[
   SET @SFID = AttributeValue("Account_ID")
   SET @BaseURL = Lookup("yourSeperateDE", "BaseURL", "SFID", @SFID)
   SET @SourceCode = Lookup("yourSeperateDE", "SourceCode", "SFID", @SFID)
   SET @ListNumber = Lookup("yourSeperateDE", "ListNumber", "SFID", @SFID)
   SET @SendType = Lookup("yourSeperateDE", "SendType", "SFID", @SFID)
   SET @IP = Lookup("yourSeperateDE", "IP", "SFID", @SFID)
   SET @Attribute = Lookup("yourSeperateDE", "Attribute", "SFID", @SFID)

   SET @URL = CONCAT('http://', @BaseURL, '?sfid=', @SFID, '&source=', @SourceCode, '-', @ListNumber, '-', @SendType, '-', @IP, '&client=', @Attribute)

]%%

OUTPUT in href:

<a href=%%=RedirectTo(@URL)=%%" >your link</a>

Now if you are looking to get listid from SFMC and not a seperate list number you created, you would need to change it to: SET @ListNumber = AttributeValue('listid')

The sending IP is not something you can collect at send time. You would need to store this in DE.

If you are going to use something other than the Account ID to house this, you would just need to define that in the AMPScript and replace the SFID part of the lookups with your unique ID name/values.