Prune rows in a Data Extension with SSJS

Say you want to delete a million rows from a Data Extension in Salesforce Marketing Cloud (SFMC). Doing that with the SFMC Web Services can be a pain. One alternative is to utilize Server-Side JavaScript (SSJS) and a Landing Page.

I built a landing page that posts to itself and runs this pruneRows() function. I configured a Data Extension that contains the keys to delete. The script retrieves all of the rows with that key, deletes them and updates the DE of keys with the results.

You may have to experiment with the number of Row Keys to retrieve at a time. If the number is too high, the landing page will time-out. In my scenario, I found that processing 50 at a time worked well (line #8).

For further reading:

avatar
4 Comment threads
5 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
6 Comment authors
KumarPedramJoão MaranoJasonAdam Spriggs Recent comment authors
  Subscribe  
newest oldest
Notify of
Joseph
Guest
Joseph

Hey Adam– love your stuff!

I was playing around implementing this but I’m terrible at javascript so wasn’t able to set this up. I started to look around alternative routes.

I was able to set this up using ampscript, and a nice little bonus — it doesn’t seem to require me to loop through 50 at a time.

code in landingpage:

%%[
VAR @timeBefore, @timeAfter, @count
SET @timeBefore = now()
SET @count=DeleteData(“DataExtensionToPrune”,”RowKey”,RowKey)
SET @timeAfter = now()
]%%

total deleted in batch: %%=v(@count)=%%

Started: %%=v(@timeBefore)=%%
Ended: %%=v(@timeAfter)=%%

Jason
Guest
Jason

This AMPscript seems straight forward but this doesn’t work for me. Is the a prerequisite?

João Marano
Guest

Hi, Adam. I made the purchase of your book: ‘The Guide of AMPSCRIPT’ and I would like to say that the book helps me a lot with my experience in Marketing Cloud. But now, I have a problem with SSJS, so I come to your blog to ask for help, kindly. In my context, I have a Data Extension (named: Count w Growth Rate-2) that is populate via integration. I lookup a report that I can accompany the growth rate by daily this Data Extension; In my search the best method is to create a Data Extension and populate this… Read more »

Pedram
Guest
Pedram

Hi Adam, Thank you for your codes and insights. It is very difficult for me to test the code and see what method the objects contain. I’m not used to be coding in the dark and this is taking a lot of my time. How do you usually test your code? Is running codes by creating Landing Pages secure? Anyhow, I have posted a question here on Stack Exchange. I would appreciate if you could help me. I am still yet to find a good source to learn SSJS as the Salesforce documentation does, unfortunately, not suffice. https://salesforce.stackexchange.com/questions/268559/remove-all-rows-retrieved-by-a-filter Thanks for… Read more »

Kumar
Guest
Kumar

Hey Adam, I try to replicate the process and I get the delcount as 0. And I have one more question is this only achieved when there is a subscriber key in the data extension or can we still delete data using a primary key as the rowkey. Thank you.