Deleting and exporting subscribers

Question

I need to clean up the all subscribers table in exacttarget. It currently has 6 million subscribers.

My idea is to delete everyone and then re-import all the held and anyone we mailed to in the past 6 months.

Is there a way that I could export Subscriber Key and Last sent Date?

Answer 1

You can do this without deleting/importing those you mailed to in the last 6 months.

You need to:

Find all subscriber records that have existed for more than 6 months Add these to a list in your admin account Delete that list and when you do so elect to also delete the subscriber records

to obtain these records you'd want to make use of a query:

Here is your query

SELECT
a.SubscriberKey as 'SubscriberKey',
a.EmailAddress as 'EmailAddress'
FROM _Subscribers a
WHERE a.DateJoined  < DATEADD(day,-180,GETDATE())

Create a data extension and direct the query to overwrite to this.

Export the data extension and import it into a list

then you just need to delete the list and when doing so also elect to delete the subscriber records

You need to do this in your admin I.E highest level account

Furthermore it's possible to automate this whole process bar the actual deletion of the list if desired.

This can be done by adding a data extract, file transfer and import file activity to the automation with your initial query.

Allowing you to automate the extract of the data extension and import into your chosen list.

It's also worth noting that deleting subscribers can take time backend and you will need to delete lists of no more than 500k in size.

A easily way around this is to split your list into several groups and delete these individually.

Answer 2

Thank you Luke, I've added to your query:

Contacts that are more than 2 years old, their status is active and haven't been emailed in the past 6 months

SELECT
a.SubscriberKey,
a.EmailAddress,
a.DateJoined,
a.Status
FROM _Subscribers a
WHERE a.DateJoined  < DATEADD(day,-730,GETDATE())
and a.Status = 'Active'
and a.SubscriberKey not in
    (
      select
        SubscriberKey
      from
        _sent s
    )

1. In the Parent account run the bulk deletion query via an automation
2. Split this into Data Extensions of 499,000. I believe there is a backend limit on the number of records that can be deleted in one time, it's potentially a safety net for customers, of up to 499K per List.
3. Export all of the the data extensions via FTP.
4. Open each CSV, sense check data and remove unnecessary columns of data and retain Subscriber key and Email address.
5. Save a back up the sheets for future reference.
6. First check the All Subscribers count and take note of it.
7. Create a new List, import the file with the Add & Update import option. The UI import limit is 20MB, however it allowed me to import files of 29MB.
8. After the List has completed processing, give it a few minutes and delete the list and tick the box to delete the contacts from the entire application.
9. Check to see that the All Subscribers count has dropped.
10. The Global Contacts Report, refreshes every Saturday.

Based on a page I read online it suggested that the list size limit for deletion is 275K, I reduced the list sizes to 275K, however re-running the query after deletion, it still did not delete all of those contacts. 499K records may be the most efficient list size to go with.