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:

http://landingpageURL?sk=%%=v(@sk)=%%&emailaddr=%%=v(@emailaddr)=%%&occasion=Christmas

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.”
ELSE
InsertData(@SurveyTestDataExt,”Subscriber Key”,@sk,”Email Address”,@emailaddr,”Occasion”,@occasion,”CreatedDate”,NOW())
ENDIF

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

 

It's only fair to share...Tweet about this on TwitterShare on FacebookEmail this to someoneShare on Google+
This entry was posted in AmpScript, ExactTarget and tagged , , , , , . Bookmark the permalink.