1

Our company has Gigabytes of email send data (Sends, Sent, Emails, Opens, Subscribers, etc). We would like to maintain a local relational database of all this Send data. Our current attempt uses the Fuel-SDK and has proven to be slower than we would like due to the amount of data: our multithreaded process spends all day pulling data. In our current approach, we pull data using the following strategy: recent Sends are pulled more frequently than less-recent sends. This way, we are not always polling for changes to the full set of Sends, and are more focused on what's likely to have changed.

Is there perhaps a more efficient, recommended approach to pulling the entire account's data?

Preferably, Python libraries would be available, since the solution will be coded in Python.

  • Have you checked out Simple Salesforce? (pypi.python.org/pypi/simple-salesforce) – Renato Oliveira Jun 1 '16 at 16:12
  • @RenatoOliveira It looks like this is not for us: we have an ExactTarget account and it seems the REST API is not for this, which is for some type of unrelated Salesforce account. – Brian Bien Jun 2 '16 at 18:37
5

We pull all of ours via the API, the initial backfill took a long time, but the daily updates I've got down to about an hour.

I use the FuelSDK, and it commits to the db with each batch of 2500.

There's a snippet of my code included at the bottom here that extracts sends. I have similar scripts for opens, clicks, bounces, unsubs, etc. I also run a bunch of the API jobs concurrently overnight, so it's pulling sends/opens/clicks/etc in parallel.

Another approach you could take is to create queries that populate DE's with exactly the schema you'd like from the hidden tables (e.g. _Sent), then run Data Extracts on that. You can probably get the info you think you're missing via those tables instead of just using Tracking extracts.

Here's the code:

import FuelSDK
import MySQLdb
from datetime import date, timedelta
import hashlib

def commitrecords(results, db, cur, table):
    for x in results.results:

        send_id = str(x['SendID'])
        subscriber_key = str(x['SubscriberKey'])
        event_date = x['EventDate'].strftime('%Y-%m-%d %H:%M:%S')

        if x['TriggeredSendDefinitionObjectID']:
            triggered_object_id = '"' + str(x['TriggeredSendDefinitionObjectID']) + '"'
        else:
            triggered_object_id = 'NULL'

        if x['BatchID']:
            batch_id = str(x['BatchID'])
        else:
            batch_id = 'NULL'

        idstring = str(x['SendID']) + str(x['SubscriberKey']) + event_date
        id = hashlib.md5(idstring).hexdigest()

        query = 'replace into ' + table + ' (id,send_id,subscriber_key,event_date,triggered_object_id,batch_id) values ('

        query += '"' + id + '",' + send_id + ',"' + subscriber_key + '","' + event_date + '",' + \
                 triggered_object_id + ',' + batch_id + ');'

        try:
            cur.execute(query)
        except:
            print 'Query Failed: ' + query

    db.commit()


def getrecords(sends, db, cur, search_filter=None):
    if search_filter:
        sends.search_filter = search_filter

    # Get first results
    results = sends.get()

    if results.code == 200:

        commitrecords(results, db, cur, 'et_email_sends')

        while results.more_results:
            results = sends.getMoreResults()
            commitrecords(results, db, cur, 'et_email_sends')


# Init
db = MySQLdb.connect([YOUR DB CREDENTIALS])
cur = db.cursor()

myClient = FuelSDK.ET_Client()

since = (date.today() - timedelta(2)).strftime('%Y-%m-%d')

sends = FuelSDK.ET_SentEvent()
sends.auth_stub = myClient

props = ['SendID', 'SubscriberKey', 'EventDate', 'TriggeredSendDefinitionObjectID', 'BatchID']

getrecords(sends, db, cur,
           search_filter={'Property': 'EventDate', 'SimpleOperator': 'greaterThan', 'DateValue': str(since)})
| improve this answer | |
  • You mentioned concurrency, which seems essential for speed due to blocking I/O. Have you run into any gotchas? Did you use threading or multiprocessing, and why? – Brian Bien Jun 8 '16 at 17:39
  • It's backgrounding in the bash script that runs all the python jobs. e.g. python script1.py & python script2.py & python script3.py & wait That way it can pull sends/opens/clicks/etc at the same time instead of waiting for each job to finish. This hasn't caused any issues so far. – Joe LeKostaj Jun 8 '16 at 19:10
  • To add to this. We're a SaaS and have relatively low email volume compared to something like eCommerce. If you need to pull more than a few hundred thousands sends per day, the API will probably be too slow, or you'll need to try something with many more threads running, which may or may not be stable. – Joe LeKostaj Jun 8 '16 at 19:17
  • Thanks for the tips. I posted a followup question here for anyone who uses the multithreading / multiprocessing approach: salesforce.stackexchange.com/questions/126235/… – Brian Bien Jun 8 '16 at 19:32
1

Typically, a Data Extract of Tracking data with a rolling date range is the best way to extract the bulk of SFMC activity data.

The resulting output is a .zip file with consistently named files inside. These can be scheduled with an Automation to drop unique files on whatever FTP share fits your process.

While it's certainly possible (as @Joe LeKostaj suggested), I would not recommend using any of the SFMC APIs to pull this data. You can fire the Data Extract activities with the API if you want, but that's the extent of the API use that I'd recommend.

| improve this answer | |
  • Thank you! This looks promising. Do you see any limitations of this appraoch in terms of not being able to retrieve all the data that I'd get via the Fuel SDK or REST API? It appears that if the Data Extract is comprehensive, then it will likely be the best approach. – Brian Bien Jun 1 '16 at 18:00
  • I'd do a daily pull of a rolling 7 day range of data in order to keep the file sizes manageable. – Adam Spriggs Jun 1 '16 at 18:06
  • Apparently, we have tried the data extract in the past, and found that we could not retrieve all the data that we were able to get from the Fuel SDK. – Brian Bien Jun 1 '16 at 19:18
  • Any particular reasons you recommend against the SFMC APIs to pull the data? I'm trying to get the Data Extract working without success :( That's probably a separate post to make. – Brian Bien Jun 2 '16 at 14:51
  • You're limited to retrieving 2500 rows at a time per API call. – Adam Spriggs Jun 2 '16 at 16:17

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.