AMPscript – Count records in data extension, show percentages

I had a project where the client wanted the email recipient to take a simple survey by clicking on a certain link that answers the survey questions. It’s a way to fake a form in a sense.

What is your favorite holiday? (click the appropriate button below)

  • Christmas
  • Thanksgiving
  • Easter

Each button is a link to an ExactTarget hosted landing page. The link passes the users Subscriber Key, email address, and the holiday they chose.

The link would look something like this:


The landing page stores this info into a data extension.

SET @sk = QueryParameter(“sk”)
SET @emailaddr = QueryParameter(“emailaddr”)
SET @occasion = QueryParameter(“occasion”)

SET @SurveyTestDataExt = “Survey DE”
SET @message = “Thanks for your response”

SET @multiRows = LookupRows(@SurveyTestDataExt,”Subscriber Key”,@sk)
SET @CountRows = RowCount(@multiRows)

IF @CountRows > 0 THEN /*checks to make sure the Subscriber Key does not already exist in the data extension. */
SET @message = “You have already submitted a response. Thank You.”
InsertData(@SurveyTestDataExt,”Subscriber Key”,@sk,”Email Address”,@emailaddr,”Occasion”,@occasion,”CreatedDate”,NOW())

The above code matches the users subscriber key with one (if there is one) in the data ext. If one exists, the page shows a message saying they have already taken the survey. If not, the SubKey, email, and holiday they chose are stored in the data ext.

Next, we look up and count how many rows exist in the DE that are for “Christmas”.

SET @ChristmasRows = LookupRows(@SurveyTestDataExt,”Occasion”,”Christmas”) /* Get Christmas records */
SET @CountChristmasRows = RowCount(@ChristmasRows) /* Get a count (number) of Christmas records */

SET @TotalRows = LookupRows(@SurveyTestDataExt,”isRow”,’True’) /* Get all records by getting all records where the field “isRow” is equal to “True”. By default, every record will have this field true. If for some reason we don’t want to count a record, we can have that record have isRow equal to False. */
SET @CountTotalRows = RowCount(@TotalRows) /* Get a count (number) of all records */

SET @Christmas_perCent = Divide(@CountChristmasRows,@CountTotalRows)
SET @Christmas_perCent = Multiply(@Christmas_perCent,100) /* Get the percentage of records that are Christmas. */
SET @Christmas_perCent = FormatNumber(@Christmas_perCent,”F0″) /* Round the percentage */

Show the results:

<h3>Poll Results:</h3>
%%=v(@Christmas_perCent)=%%% picked Christmas


Thank you for reading!

Pulling data from a data extension like a Relational table

Pulling data from a data extension:

Pulling data from a data extension outside of the recipient list DE.

Let’s say I have a DE that is my list of email recipeints and there is another DE with a list of data about these recipients.

In this example, there is a list of animal shelters that will receive a list of animals that came from their shelters with info about each animal.

The Shelters list has a field called SHELTER_ID which is the primary key (unique).

The Anmials list also has a field called SHELTER_ID. The field is not unique, as more than one animal can have the same SHELTER_ID.

I will relate the two lists with this field. I will get records from the Animals list where SHELTER_ID is equal to SHELTER_ID in the Shelters list.

Data Extension Names:

First I need to initialze all the variables I will need. I need variables to hold the results of the row lookup function (@multiRows), the individual row items as I loop through the rows(@row), the loop counter(@i), and the animal name and animal breed fields (@AnimalName, and @AnimalBreed).

VAR @multiRows, @row, @i, @AnimalName, @AnimalBreed

Then, I need to query the DE to get all the rows I’m looking for.

SET @multiRows = LookupRows(“Animals”,”SHELTER_ID”,[SHELTER_ID])

The code above is looking up rows in the Animals DE where “SHELTER_ID” field is equal to the “SHELTER_ID” field in the Shelters DE. And it assigns the results to the @multiRows variable.

Next, we will loop through the rows of data held in the @multiRows varible. During each iteration of the loop we will asign the data in the fields to the variables we created (@AnimalName, and @AnimalBreed).

%%[ for @i = 1 to RowCount(@multiRows) do Set @row = Row(@multiRows,@i) Set @AnimalName = Field(@row,”AnimalName”) Set @AnimalBreed = Field(@row,”AnimalBreed”) ]%%

While the loop is still open, we will render the data to the page (email)

%%[ Output(v(@CLINIC_NAME)) ]%%
%%[ Output(v(@CLINIC_EXT_ID)) ]%%

Then we close the loop.

%%[ next @i ]%%

Thank you for reading!

Import data into a data extension using FTP

FTP data file into “import” folder

In ET, go to “Interactions / Import” and click “Create”

Name: Your choice
File Naming Pattern: This is the name of the file you uploaded using FTP. Make sure to include file extension (.csv).
Destination Type: If you are uploading data to a data extension, make sure and choose “Data Extensions”.
In the box below, select the data extension you want to import data to.

Update Type: Select

Thank you for reading!

Adding possessive apostrophe to names – AMPscript

So we have some personalization in an email where we want to greet the recipient using their first name. In the example the first name was a pet name and the email was alerting the recipient that it was time for their pet’s shots, checkup, etc.

It’s time for Sparky’s checkup.

The works fine, but if the pet name ends in “s”, we want to just add the apostrophe and not the apostrophe and the “s”.

Let’s say your dog’s name is “Spots”.

So, we want to check and see if the pet name ends in “s”.

First I’ll make sure the pet name is in the case I want it in (upper, lower, proper).

Set @PET_NAME = ProperCase(@PET_NAME)

Then I’ll get the length of the name.


Now, let’s check to see if “s” is the last character. If it is, we’ll set a variable to hold just the apostrophe (‘). If the last character is not an “s”, we’ll set the variable to apostrophe + “s” (‘s).

Set @PATIENT_NAME_Apostrophe = "'"
Set @PATIENT_NAME_Apostrophe = "'s"

Substring(S1, I1, I2)
Returns the portion of the specified string starting with the specified character position and no longer than the specified length. If the specified character position is greater than the length of the specified string, an empty string is returned.


S1 The string from which to return a portion
I1 The character position at which to begin the substring
I2 Maximum length of the substring



System returns:bc

Thank you for reading!