1

I have a daily data extract program setup in our Exacttarget parent account. The program basically exports a zipped file to our ftp server. the zipped file is a collection of various csv files i.e. sent, bounces, unsubs etc. the csv files contain all previous day activity. for an example sent.csv would contain list of all the emails sent a day before.

the unsubs.csv which is supposed to contain all the un-subscribes from previous day does not necessarily contain everything. When I called Exacttarget support, they described the situation something like this :

"A subscriber can be un-subscribed from a given list using two methods. one through an API call or the other through subscription center. In Case1 the subscriber used an API call to unsubscribe. Unless this call is using the 'UnsubEvent', the subscriber will be flagged as unsubscribed. This will not be associated with a particular job and therefore will not show in the tracking screen for any job. The Tracking Extract is pulling data for jobs. Since this is not associated with a job, this subscriber will not be found in the Tracking Extract. The UnsubEvent can be used to tie the subcriber's request with a single job. This will then be recorded with the JobID and would be available for the Tracking Extract " In Case2 the subscriber used the Subscription Center to unsubscribe. This method has tracking information that records the job and list that the subscriber is being unsubscribed from. This will show in the tracking screen for the job and will be pulled during the Tracking Extract.

The Problem

The solution seems very logical to include UnSubEvent during API call. But whats going to happen with the ones which were already un-subscribed and were not included in unsubs.csv file.

My Approach to above problem

I have approximately 1500 subaccounts in Exacttarget and every subaccount has approximately 1000 subscribers in "All Subscribers" list. In order to sync the already existing un-subscribe data between exacttarget and our database, I wrote a small program which does following::

For each sub-account::

a. get the list of subscribers from "All Subscribers" list. 

For each Subscriber::

a. get the subscription map for this subscriber. i.e. which publication lists this subscriber has been sub/unsubs-cribed to. 

For each un-subscribe ::

a. check if our database contains this unsusbcribe information for this subcriber. if its not, then insert a record otherwise do nothing. 

If I go with the solution I just mentioned, it takes almost 10 minutes to finish the job for one sub account. which means for 1500 sub accounts , it will take approximately 15000 mins or 11 days to finish the entire job.

Why Each job is taking such a long time

The run time of a job is typically related to the number of SOAP API calls my program is making. so lets say if a sub account has 1000 subscribers in All Subscribers list, my program would make approximately 1001 SOAP API calls (1 to get All Subscribers and 1000 for each subscribers considering All the subscribers are either with Active/Unsubcribe status. All other status are not my concern). I have managed to reduce the database trip time by only doing batch updates. but unfortunately , it did not make my program any faster because most of the time its waiting for SOAP responses.

Questions::

Is my approach correct or are there any other better possibilities which could provide me with the similar set of information in lesser time.

P.S. Right now I am ready to consider anything which is faster than this.

1

Contact your account representative to discuss what an ET Services engagement with the Data Architects would look like. They have direct access to the data, and could write stored procedures to generate a data extract giving you exactly what you need.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.