Marketo Engage - API Endpoint - Get Import Lead Failures Returning Incorrect CSV Data

[trayer]

Hi,

Recently the Get Import Lead Failures endpoint (GET /bulk/v1/leads/batch/{id}/failures.json) started returning data that differs from the original CSV uploaded for Bulk Leads Import.

The process:

1. Bulk Leads Import (creates job from CSV)
2. Get job status (check for failed rows)
3. Get Import Lead Failures (retrieve failed row data)

Previously, Get Import Lead Failures returned the same data structure as the uploaded CSV. Now we're seeing two issues:

CSV Column Example: ID, firstName, lastName, email, phone

Issue 1: Empty columns are removed from response If all failed rows have empty values for certain columns (e.g. email and phone), those columns are completely removed from the Get Import Lead Failures response, even though they were present in the original CSV.

Issue 2: Data shifts to wrong columns when earlier columns are empty If a row contains only firstName and lastName (fails due to missing email), the returned data shows:

• firstName value appears in the ID column
• lastName value appears in the firstName column

The data is shifting left into earlier columns instead of maintaining the original column positions.

These issues started appearing around November 5th.

Could anyone provide any insight with into issue?

[SanfordWhiteman]

If you’re deduping on id (which is implied by including an id column) then empty email and phone won’t cause a failure for that row.

So can you please elaborate on the failure reasons and provide a repro case?

[trayer]

Hi @SanfordWhiteman ,

The failure reason were: "Value for lookup field 'email' not found".

From the tests, ID was not a required column, for example if the original CSV had the following data:

ID,firstName,lastName,email,phone

1001,NameA,SurnameA,emailA@email.com,123456789

1001,,SurnameB,emailB@email.com,

,,SurnameC,,

,NameD,SurnameD,,

1002,,SurnameE,,

1002, NameF, SurnameF,, 

The failed rows will be because email is missing so Row with data for C,D,E and F are affected.

When calling the GET /bulk/v1/leads/batch/{id}/failures.json using the batch id obtained from trying to Bulk Leads Import it returns the following:

ID,firstName,lastName,Import Failure Reason

SurnameC,Value for lookup field 'email' not found

NameD,SurnameD,Value for lookup field 'email' not found

1002,SurnameE,Value for lookup field 'email' not found

1002,NameF,SurnameF,Value for lookup field 'email' not found

The data returned by the endpoint has removed the email column as well as the phone column.

Then shifted the data into earlier columns instead of maintaining the original column positions.

[SanfordWhiteman]

Naturally id is not a required column when using email as the lookup field. id is not writable, and you’re not using it to look up, so it should not be in the CSV at all!

Why are you including empty lookup fields? You know those will fail, and it’s trivial to strip them out of the CSV before sending the request (this would not necessarily be the case with CSVs in general, but with Marketo’s tiny max CSV size, it will always be possible). 

[trayer]

Hi @SanfordWhiteman,

Prior to November 5th, the GET /bulk/v1/leads/batch/{id}/failures.json endpoint consistently preserved all columns from the original CSV, even when fields contained blank values. The endpoint did not remove empty columns or shift data into earlier positions.

Using the same example for CSV:

ID,firstName,lastName,email,phone

1001,NameA,SurnameA,emailA@email.com,123456789

1001,,SurnameB,emailB@email.com,

,,SurnameC,,

,NameD,SurnameD,,

1002,,SurnameE,,

1002, NameF, SurnameF,, 

Before November 5th, a request to GET /bulk/v1/leads/batch/{id}/failures.json returned:

ID,firstName,lastName,email,phone,Import Failure Reason

,,SurnameC,,,Value for lookup field 'email' not found

,NameD,SurnameD,,,Value for lookup field 'email' not found

1002,,SurnameE,,,Value for lookup field 'email' not found

1002, NameF, SurnameF,,,Value for lookup field 'email' not found

After approximately 3am UTC on November 5th, the same endpoint started returning:

ID,firstName,lastName,Import Failure Reason

SurnameC,Value for lookup field 'email' not found

NameD,SurnameD,Value for lookup field 'email' not found

1002,SurnameE,Value for lookup field 'email' not found

1002,NameF,SurnameF,Value for lookup field 'email' not found

Which shows that columns are missing and data is shifting left to incorrect columns.

[SanfordWhiteman]

Including id means it’s a malformed CSV for the Marketo Engage platform. You cannot write to that field; it’s only valid if you’re using it for lookup, which you’re not. So whether there was a change in output behavior or not, the input was always incorrect.

What happens when you do not include id?