Create, update, delete, and query Leads for a Marketo organization.

Table Specific Information

Select

All columns must be specified using the '=' operator. All filterable columns allow multiple values to be specified by using the IN operator or the OR logical operator.

Response time from the server can be improved by identifying only the rows and columns you want to retrieve.

SELECT Id, FirstName, LastName FROM Leads WHERE Id IN (1, 2, 5, 10)

If a filter is not specified, the Activities_NewLead table will be queried to retrieve a list of Lead Ids. Once the Lead Ids have been compiled, the Leads table will be queried using the compiled list of Lead Ids. The 'CreatedAt' column can be used as a filter to specify the created datetime range of Leads to retrieve. When the '>' or '>=' operator is specified, the datetime value will be submitted in the Activities_NewLead table request. Note that this requires additional API calls and will at least double the amount of API calls made as one Activities_NewLead request must be made for each Leads request made. The maximum batch size per request for the REST API is 300, so to get a rough estimate of the number of API calls required, the following formula can be used: (Total Number of Leads / 300) * 2

The 'UpdatedAt' column can also be used as a filter to specify the updated datetime range of Leads to retrieve. When specified, the Activities_LeadChanges table will be queried to retrieve a list of Lead Ids. Once the Lead Ids have been compiled, the Leads table will be queried using the compiled list of Lead Ids. When the '>' or '>=' operator is specified, the datetime value will be submitted in the Activities_LeadChanges table request. Note that this requires additional API calls and will at least double the amount of API calls made as one Activities_LeadChanges request must be made for each Leads request made. In most cases, the total API calls will more than double as individual values for a Lead are returned as a record). Thus to get a full list of Lead Ids, multiple Activities_LeadChanges requests may need to be made. The maximum batch size per request for the REST API is 300, so to get a minimum estimate of the number of API calls required, the following formula can be used: (Total Number of Leads / 300) * 2

Alternatively, the SOAP API can be used to to retrieve a list of Lead Ids by setting "UseSOAPForLeadIds=True" in the 'Other' property. When specified the SOAP API will be used (provided the SOAP connection details are specified) to compile a list of Lead Ids which will then be used as a filter for the REST API. Note that the SOAP API is significantly slower than the REST API, although this hybrid approach is faster than using just the SOAP API by itself. The maximum batch size per request for the SOAP API is 1000, so to get a minimum estimate of the number of API calls required, the following formula can be used: (Total Number of Leads / 1000) + (Total Number of Leads / 300)

To bypass utilizing multiple API calls and for the best performance, create a static list of Leads within Marketo and then specify the ListId to retrieve them.

Insert

To create a new Lead record, specify the information about the Lead to be entered into the database.

The following example demonstrates how to insert a new Lead:

INSERT INTO Leads (Email, FirstName, LastName) VALUES ('[email protected]', 'John', 'Mark')

Update

Any field that is not read-only can be updated. Updates can be performed using an external key which is any 'Filterable' column, such as Email.

The following example demonstrates how to update using the 'Id' column as the lookup field.

UPDATE Leads SET MobilePhone='111-222-3333' WHERE Id = 1

The following example demonstrates how to update using the 'Email' column as the lookup field.

UPDATE Leads SET MobilePhone='111-222-3333' WHERE Email = '[email protected]'

Additionally you can use a custom field as the lookup field. In such a case, you will need to set the 'LookupField' column as well to the name of the custom field you are using. The following example demonstrates how to update use a custom field called 'MyCustomField' as the lookup field.

UPDATE Leads SET MobilePhone='111-222-3333' WHERE LookupField = 'MyCustomField' AND MyCustomField = 'my_value'

Delete

Delete is used to remove leads from Marketo. To perform a delete, the lead Id field is required.

DELETE FROM Leads WHERE Id = 1

GetDeleted

GetDeleted is used to get deleted leads from Marketo. To get deleted leads, you need to specify the UpdatedAt filter. If not specified, it will fetch data for last 14 days.

GetDeleted FROM Leads

Columns

Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	The unique, Marketo-assigned identifier of the account.
Email	String	False	True	The lead's email address.
Salutation	String	False		The lead's salutation.
FirstName	String	False		The lead's first name.
MiddleName	String	False		The lead's middle name.
LastName	String	False		The lead's last name.
DateOfBirth	Date	False		The lead's date of birth.
Title	String	False		The lead's job title.
Address	String	False		The lead's street address.
City	String	False		The lead's city.
State	String	False		The lead's state.
PostalCode	String	False		The lead's ZIP/postal code.
Country	String	False		The lead's country.
Website	String	False		The lead's website.
Phone	String	False		The lead's phone number.
MobilePhone	String	False		The lead's mobile phone number.
Fax	String	False		The lead's fax number.
Company	String	False		The name of the lead's company.
MainPhone	String	False		The phone number of the lead's company.
SICCode	String	False		The SIC (Standard Industrial Classification) code of the lead's company.
Site	String	False		The site of the lead's company.
BillingStreet	String	False		The billing street address of the lead's company.
BillingCity	String	False		The billing city of the lead's company.
BillingState	String	False		The billing state of the lead's company.
BillingPostalCode	String	False		The billing ZIP/postal code of the lead's company.
BillingCountry	String	False		The billing country of the lead's company.
NumberOfEmployees	Integer	False		The number of employees at the lead's company.
Industry	String	False		The industry of the lead's company.
Department	String	False		The lead's deparment.
AnnualRevenue	Double	False		The annual revenue generated at the lead's company.
AnonymousIP	String	False		The IP address of the lead if it is anonymous.
Unsubscribed	Boolean	False		Determines whether the lead is unsubscribed.
UnsubscribedReason	String	False		The reason why the lead has unsubscribed.
EmailInvalid	Boolean	False		Identifies whether the lead's email address is invalid.
EmailInvalidCause	String	False		The reason why the lead's email address is invalid.
DoNotCall	Boolean	False		Identifies whether the lead is on the 'Do Not Call' list.
DoNotCallReason	String	False		The reason why the lead is on the 'Do Not Call' list.
PersonType	String	False		The type of person the current record is, such as a contact.
IsAnonymous	Boolean	True		Identifies whether the lead is anonymous or not.
IsLead	Boolean	False		Identifies whether the person is a lead or not.
LeadRole	String	False		The lead's role.
LeadSource	String	False		The lead's source.
LeadStatus	String	False		The lead's current status.
LeadScore	Integer	False		The lead's score.
Rating	String	False		The lead's rating.
Urgency	Double	True		The lead's urgency.
Priority	Integer	True		The lead's priority.
RelativeScore	Integer	True		The lead's relative score.
OriginalSourceType	String	True		The original source type where the lead originated from.
OriginalSourceInfo	String	True		Information about the original source of the lead.
RegistrationSourceType	String	False		The original source type where the lead originated from.
RegistrationSourceInfo	String	False		Information about the original source of the lead.
CreatedAt	Datetime	True	True	The date the lead was created.
UpdatedAt	Datetime	True	True	The date the lead was last updated.
Cookies	String	False	True	The cookies associated with the lead.
AcquisitionProgramId	String	False		The Id of the program in which the lead was acquired.
Gender	String	False		The lead's social gender.
TotalReferredVisits	Integer	True		The lead's total social referred visits.
TotalReferredEnrollments	Integer	True		The lead's total social referred enrollments.
LastReferredEnrollment	Datetime	False		The lead's last social referred enrollment.
LastReferredVisit	Datetime	False		The lead's last social referred visit.
SyndicationId	String	False		The lead's social syndication Id.
FacebookDisplayName	String	False		The lead's Facebook display name.
FacebookId	String	False	True	The lead's Facebook Id.
FacebookPhotoURL	String	False		The lead's Facebook photo URL.
FacebookProfileURL	String	False		The lead's Facebook profile URL.
FacebookReach	Integer	False		The lead's Facebook reach.
FacebookReferredEnrollments	Integer	False		The lead's Facebook referred enrollments.
FacebookReferredVisits	Integer	False		The lead's Facebook referred visits.
LinkedInDisplayName	String	False	True	The lead's LinkedIn display name.
LinkedInId	String	False		The lead's LinkedIn Id.
LinkedInPhotoURL	String	False		The lead's LinkedIn photo URL.
LinkedInProfileURL	String	False		The lead's LinkedIn profile URL.
LinkedInReach	Integer	False		The lead's LinkedIn reach.
LinkedInReferredEnrollments	Integer	False		The lead's LinkedIn referred enrollments.
LinkedInReferredVisits	Integer	False		The lead's LinkedIn referred visits.
TwitterDisplayName	String	False		The lead's Twitter display name.
TwitterId	String	False		The lead's Twitter Id.
TwitterPhotoURL	String	False		The lead's Twitter photo URL.
TwitterProfileURL	String	False		The lead's Twitter profile URL.
TwitterReach	Integer	False		The lead's Twitter reach.
TwitterReferredEnrollments	Integer	False		The lead's Twitter referred enrollments.
TwitterReferredVisits	Integer	False		The lead's Twitter referred visits.
ListId	Integer	True	True	A List Id used to retrieve all leads contained within the specified list. This is a filter only field made available to enhance filtering capabilities.
ProgramId	Integer	True	True	A Program Id used to retrieve all leads associated with the specified program. This is a filter only field made available to enhance filtering capabilities.
LookupField	String	True	True	Used to specify the field used to find duplicate leads. Only used when performing an INSERT or UPDATE. Available values are: id (default), cookie, email, twitterId, facebookId, linkedInId, sfdcAccountId, sfdcContactId, sfdcLeadId, sfdcLeadOwnerId, and custom fields. This is a filter only field made available to enhance filtering capabilities.