Bulk Lead Extract API questions

[Kurt_Koller]

Working with the Bulk API, have a few questions about it.

1. What's the easiest way to just get all leads for an initial sync via the bulk API? You need to have a filter. Say I have an instance with 150k leads created over 3 years. Do I need to do 30 day bulk API requests starting at some arbitrary old date to get all leads? This needs to be done through the API. It seems that in this scenario using the non-bulk Rest API with a paging token would be faster (see #3) even though it would result in more calls. This is what we are currently doing but we'd hoped to replace it with the Bulk API. (If this is how this must be would LOVE to see the 30 limit upped to 31 days so we can logically pull by calendar month)

2. (minor) Why does the Bulk API return the word "null" in a missing filed in a CSV? In a CSV a missing/null value is just missing, not the word "null". Here's an example:

id,email,company,sfdctype,postalcode,inferredpostalcode,inferredmetropolitanarea,country,inferredcountry,persontype

408,me@example.com,MyCompany,Contact,null,null,null,United States,null,contact

In every other CSV scenario I've worked in, this would be represented as:

id,email,company,sfdctype,postalcode,inferredpostalcode,inferredmetropolitanarea,country,inferredcountry,persontype

408,me@example.com,MyCompany,Contact,,,,United States,,contact

3. Why does the startedAt date change/why the time weirdness here? I created a lead export of 1 month of leads filtered by createdAt. Here are the stages:

Create:

{

   "fields": ["id", "email", "company", "createdAt", "updatedAt","unsubscribed", "emailInvalid", "originalSourceType", "sfdcType","postalCode", "inferredPostalCode",

"inferredMetropolitanArea","country", "inferredCountry", "personType"],

   "format": "CSV",  "filter": {  "createdAt": {

         "startAt": "2017-06-01T00:00:00Z",  "endAt": "2017-07-01T00:00:00Z"  }   }

}

Response:

{

    "requestId": "bcfe#15d53739e6b",

    "result": [

        {

            "exportId": "b70642a3-8a89-4bf1-a441-24b214096b78",

            "format": "CSV",

            "status": "Created",

            "createdAt": "2017-07-18T02:07:52Z"

        }

    ],

    "success": true

}

Status after enqueue:

{

    "requestId": "1796#15d53747d57",

    "result": [

        {

            "exportId": "b70642a3-8a89-4bf1-a441-24b214096b78",

            "format": "CSV",

            "status": "Queued",

            "createdAt": "2017-07-18T02:07:52Z",

            "queuedAt": "2017-07-18T02:08:49Z"

        }

    ],

    "success": true

}

Status after change from "Queued" to "Processing":

{

    "requestId": "16721#15d5374f1fe",

    "result": [

        {

            "exportId": "b70642a3-8a89-4bf1-a441-24b214096b78",

            "format": "CSV",

            "status": "Processing",

            "createdAt": "2017-07-18T02:07:52Z",

            "queuedAt": "2017-07-18T02:08:49Z",

            "startedAt": "2017-07-18T02:09:00Z"

        }

    ],

    "success": true

}

Status after complete:

{

    "requestId": "17c12#15d53bbac87",

    "result": [

        {

            "exportId": "b70642a3-8a89-4bf1-a441-24b214096b78",

            "format": "CSV",

            "status": "Completed",

            "createdAt": "2017-07-18T02:07:52Z",

            "queuedAt": "2017-07-18T02:08:49Z",

            "startedAt": "2017-07-18T03:26:09Z",

            "finishedAt": "2017-07-18T03:26:25Z",

            "numberOfRecords": 43,

            "fileSize": 7389

        }

    ],

    "success": true

}

Why does the startedAt date change? I polled status.json every minute or so and it was the same as the status above until it finished at which time the startedAt jumped forward.

3a. Also under what circumstances would an export of 1 month of lead records out of an instance that has 1600 leads take more than 1 hour 15 minutes? The final result looks like it took 16 seconds but it entered processing way before that. There is no other API usage, no other Bulk API jobs are queued, etc. I've repeated this 3 times in a row.

4. There's a note in the API that the filter type updatedAt*
* Filter type is unavailable for some subscriptions.  Marketo Support can provide you with this information

How can we find out via the API whether this is available or not? Just call it and process a filter error? Or will it just return an empty set? I need to know what to do when we run against an instance and we have no idea whether they have this feature or not, how would we find/extract leads that are updated since a date through the bulk API? So far I haven't seen this on instances I've tested, but I'd like to know what to do.

Any insight is appreciated.

[David_Everly]

1. Correct, using bulk you would have to pull leads in 30 day increments.  I have created an enhancment request to increase the date range to 31 days.

2. This design choice is to maintain consistency with bulk lead export from within the Marketo UI.  i.e. a precedent had been set

3. I am unable to reproduce this behavior, I recommend filing a support case with Marketo.

3a. There are many variables that impact performance in a multi-tenant system.  You might try enqueuing the job during off hours.

4. You can determine if the instance supports updatedAt by calling Create Export Lead Job endpoint.  If not supported, tou will receive an error 1035, "Unsupported filter type for target subscription".

[Anonymous]

David Everly wrote:

1. Correct, using bulk you would have to pull leads in 30 day increments. I have created an enhancment request to increase the date range to 31 days.

Are you able to confirm if this has come into effect yet? I recently did an export that seemed to allow 31 day date range.

[David_Everly · Accepted solution]

Yes, 31 day date range is now supported.

[Kurt_Koller]

Hi there David Everly​, regarding:

4. You can determine if the instance supports updatedAt by calling Create Export Lead Job endpoint.  If not supported, tou will receive an error 1035, "Unsupported filter type for target subscription".

We do this, it's fine, but do you know, will the servers/APIs that currently do not support the updatedAt filter be getting an update to support that in the future?

[David_Everly]

Yes, in the future all subscriptions will support this feature.

[Kurt_Koller]

Do you know if it's near-future or far-future?

Also what are the types of subscriptions that don't have this feature? We want to have something in our documentation about how they know and what the ramifications are.

We would also love an approximate date for our own planning, if that kind of thing is available. Even things like "first half 2018, maybe" is a good answer as is "it's not currently on our internal roadmap but we will be planning this out over the next few months so feel free to ask again in 3 months."

Very many thank yous in advance

Thank you David Everly​

[Jeremy_Gilbrea1]

Hi Kurt Koller,

Were you able to get enough info to build out the "UpdatedAt" documentation? If so would you by chance be willing to share that part of your docs? I am in the process of crafting guidance around this exact thing as our integration is filtered by it as well. Would love to have it as a guide if you would be willing.

Best

[David_Everly]

Sorry, but there is no date currently available.  I recommend that you check back in 3 months.

[Jeremy_Gilbrea1]

Is there an ETA for when all subscriptions will support updatedAt attribute?

[Anonymous]

Hi David,

Is upgrading all subscriptions to support updatedAt-based filtering on the roadmap yet?

[Kurt_Koller]

My other response didn't post for some reason. Thanks for #1 request, and thanks for #4. Appreciate all your answers.

[Kurt_Koller]

David Everly​ thanks for the response. With #2, I just pulled an export of leads from the UI with the same fields, and none of them contain the word null in them. Sample line from the CSV for my account in our system, note the ,,, and not ,null,null,null:

1013663,kk@digitalpi.com,Digital Pi,2016-04-26 15:13:15,2017-07-27 23:44:17,,,1,Omega,List import

[David_Everly]

Slight correction to #2, design decision to be consistent with REST API.  For example, a call to Get Multiple Leads by Filter Type returns null for non-existent lead fields.  Here is an example with non-existent field "leadRole" for a given lead id "318581":
GET /rest/v1/leads.json?filterType=id&filterValues=318581&fields=leadRole

{

    "requestId":"15426#15dc7cc2251",

    "result":[

        {

            "id":318581,

            "leadRole":null

        }

    ],

    "success":true

}

[Kurt_Koller]

I will add logic to our parsers to look for a null string and replace it with an actual null in our database. One thing that gets lost is that sometimes people end up with the text "null" in a field in Marketo because of import issues etc, and encoding nulls the way they are currently encoded in bulk export means we can't detect if it's a real null or a text "null"

[SanfordWhiteman]

The string "NULL" is already a special value in Marketo -- it's a reserved word used to empty fields (though not, confusingly, to set them to the valueless null/NULL).

Also, I don't agree that CSVs don't represent NULL values as strings. A SQL dump to CSV must use ,NULL, to set off valueless NULL from 'NuLl' fwiw....

[Kurt_Koller]

Sanford,

The CSV export from the Marketo UI does exactly what I suggest.

[SanfordWhiteman]

The point is that double-quoted or unquoted

     NULL

(or any case variation) is very common in the wider world of CSVs, where it explicitly represents a SQL NULL.

And since Marketo uses SQL on the back end, what's missing here is a distinction between single-quoted values and SQL special values. Basically, the CSV isn't accurately representing the source, in favor of making it look simpler.

And when you take into account that the string 'NULL', which has no special meaning in SQL, has special meaning in Marketo, it's clear that the rules for clear import/export are out the door.

[Kurt_Koller]

I won't belabor this, but CSV != SQL dump, and again, the Marketo UI CSV dump of the lead data from the UI (same data) does the (correct in my opinion) ,, behaviour and doesn't include the word null for missing data.

[SanfordWhiteman]

Sure, CSV isn't only for SQL exports, but it's not not a SQL export, either, since the values have no inherent meaning outside of some data domain. Like you said, CSV is a generic format.  I just don't see either variant as correct, because honoring null as a separate value is appropriate (it can be a separate value on the back end) and that's lost in the UI version, while separating empty values from null is also important, and that's lost in the API version. If this were not oversimplified and gave single-quoted strings or unquoted null, everyone would be happy!

[Kurt_Koller]

Sanford,

This is the last post I'll make on this, I'm not sure what your motive is for arguing, but to me this is pretty clear.

The CSV export of the _same data_ from the UI is, in my opinion, correct. The reason given for including ,null, in my opinion, is not a good one, beccause CSV != JSON. There is indeed a precedent in Marketo, and that precedent for CSV is that ,null, is not used, and that ,, is used. I believe they made a mistake here, and the mistake is that they applied the JSON null representation into CSV when they had a perfectly reasonable CSV implementation for the same data already. Thanks for your counter opinion, but I personally believe it's incorrect, since the existing CSV export that they have represents it the way I'm expecting it here.