Query Leads of a Marketo organization.

Select

Note: Filters provided with one of the supported operators listed in the "Operators" column are processed server-side, all the other filters are processed client-side.

Tip: This table uses server-side projection. Performance can be improved by minimizing the number of columns in projection.

To optimize response time from the server, identify only the rows and columns you want to retrieve.

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

To achieve the best performance from this query, confine it to a list of known Leads within Marketo. Create a static list of Leads within Marketo, and then specify the ListId to retrieve them.

SELECT * FROM Leads WHERE ListId=1014

If you do not specify a filter, the driver retrieves all Leads, which might take a long time depending on the number of leads in your Marketo instance. Specify the UpdatedAt filter to improve query performance by limiting the number of records requested.

	SELECT * FROM Leads WHERE UpdatedAt >= '2024-03-20T15:49:22.000Z'
	SELECT Id, UpdatedAt FROM Leads WHERE UpdatedAt >= '2024-01-17T14:32:37.000-05:00'

Insert

This table supports BATCH INSERT when UseBulkAPI is set to false.

	INSERT INTO Leads (FirstName, LastName, Email, DoNotCall, DoNotCallReason, DateOfBirth) VALUES ('test', 'test', '[email protected]', true, 'Testing', '01/01/2000')
	INSERT INTO Leads (FirstName, LastName, Email, DoNotCall, DoNotCallReason, DateOfBirth) VALUES ('test1', 'test1', '[email protected]', true, 'Testing', '01/01/2000'), ('test3', 'test3', '[email protected]', true, 'Testing', '01/01/2000')
	INSERT INTO Leads (FirstName, LastName, Email, PartitionName) VALUES ('test', 'test', '[email protected]', 'testPartition')

To insert multiple leads at once via a #TEMP table, first create the #TEMP table, and then insert that table into your Leads table.

The following example creates a #TEMP table with three new Leads, and then inserts that #TEMP table into the Leads table:

	INSERT INTO Leads#TEMP (FirstName, LastName, Email, Company) VALUES ('John', 'Mangel', '[email protected]', 'ABC')
	INSERT INTO Leads#TEMP (FirstName, LastName, Email, Company) VALUES ('Steve', 'Puth', '[email protected]', 'ABC')
	INSERT INTO Leads#TEMP (FirstName, LastName, Email, Company) VALUES ('Andrew', 'Stack', 'andy@abc', 'ABC')

	INSERT INTO Leads (FirstName, LastName, Email, Company) SELECT FirstName, LastName, Email, Company FROM Leads#TEMP

Update

This table supports BATCH UPDATE when UseBulkAPI is set to false.

You can update any field in the Leads table that is not read-only. All critria fields are used as deduplication fields. Use a field for deduplication if it supports server side filtering, otherwise the Id column is used for deduplication.

	UPDATE Leads SET Company='TestCompany', NumberOfEmployees=100 WHERE Id='2521004'
	UPDATE Leads SET Title='My Job', Address='123 Main St' WHERE Email='[email protected]'
	UPDATE LEADS SET Email='[email protected]' WHERE Id=2523020
	UPDATE LEADS SET TestKpQA='test' WHERE TestCustomfieldEmail='[email protected]'

Upsert

This table supports BATCH UPSERT when UseBulkAPI is set to false.

This table supports BULK UPSERT when UseBulkAPI is set to true.

	UPSERT INTO Leads (Email, FirstName, LastName) VALUES ('[email protected]', 'Lead', 'Upsert_InsertNewRecord')

Delete

This table supports BATCH DELETE when UseBulkAPI is set to false.

	DELETE FROM Leads WHERE Id=508
	DELETE FROM Leads WHERE Id IN (505, 506, 501, 507)
	DELETE FROM Leads WHERE Email='[email protected]'

GetDeleted

Note: The API returns only leads which have been deleted in the past 14 days.

	GetDeleted FROM Leads

GetDeleted supports filtering by UpdatedAt.

	GETDELETED FROM Leads WHERE UpdatedAt > '2024-01-01T01:00:00'
	GETDELETED FROM Leads WHERE UpdatedAt > '2024-01-01T01:00:00' AND UpdatedAt <= '2024-02-01T01:00:00' 

Bulk

When UseBulkAPI is set to true the CreatedAt filter can be processed server-side, in addition to that the following extra filters are available:

Name	Type	ReadOnly	Operators	Description
StaticListName	String	True	=	Mirror column that can be used only as a filter. Will retrieve custom objects related to the provided static list name.
SmartListId	Int	True	=	Mirror column that can be used only as a filter. Will retrieve custom objects related to the provided smart list Id.
SmartListName	String	True	=	Mirror column that can be used only as a filter. Will retrieve custom objects related to the provided smart list name.

Sample queries:

	SELECT Id, UpdatedAt FROM LEADS WHERE UpdatedAt>'2024-01-17T14:28:57.000-05:00' AND UpdatedAt<'2024-01-29T04:29:55.000-05:00'
	SELECT Id, CreatedAt FROM LEADS WHERE CreatedAt>='2024-01-17T14:27:20.000-05:00' AND CreatedAt<'2024-01-29T04:29:30.000-05:00'
	SELECT * FROM LEADS WHERE ListId=1062
	SELECT * FROM LEADS WHERE StaticListName='test0614'
	SELECT Id FROM LEADS WHERE SmartListId=1095
	SELECT Id FROM LEADS WHERE SmartListName='LeadSmartListTest'

Columns

Name	Type	ReadOnly	Operators	Description
Id [KEY]	Int	True	=,IN	The id of the lead.
CreatedAt	Datetime	True		The datetime when the lead was created.
UpdatedAt	Datetime	True	=,>,>=,<,<=	The datetime when the lead was last updated.
ProgramId	Int	True	=	Mirror column that can be used only as a filter. Will retrieve Leads related to the given email ProgramId. Not available when UseBulkAPI=true.
ListId	Int	True	=	Mirror column that can be used only as a filter. Will retrieve Leads related to the provided static list Id.
PartitionName	String	False		Mirror column that can be used only during inserts to specify the partition of the lead.
ItemURL	String	True		The URL of the lead in the Marketo UI.
AcmeAccessCode	String	False	=,IN
AcquisitionProgramId	Int	False
Address	String	False
AnnualRevenue	Decimal	False
AnonymousIP	String	False
BillingCity	String	False
BillingCountry	String	False
BillingPostalCode	String	False
BillingState	String	False
BillingStreet	String	False
BlackListed	Bool	False
BlackListedCause	String	False
City	String	False
Company	String	False
ContactCompany	Int	True
Cookies	String	False	=,IN
Country	String	False
Cstmfdtest1	String	False	=,IN
Cstmfdtest2	String	False	=,IN
DateOfBirth	Date	False
Department	String	False	=,IN
DoNotCall	Bool	False
DoNotCallReason	String	False
Ecids	String	True
Email	String	False	=,IN
EmailInvalid	Bool	False
EmailInvalidCause	String	False
EmailSuspended	Bool	False
EmailSuspendedAt	Datetime	False
EmailSuspendedCause	String	False	=,IN
ExternalCompanyId	String	False	=,IN
ExternalSalesPersonId	String	False	=,IN
Fax	String	False
FirstName	String	False
FirstUTMCampaign	String	False
FirstUTMContent	String	False
FirstUTMMedium	String	False
FirstUTMSource	String	False
FirstUTMTerm	String	False
GdprMailableStatus	String	False
Industry	String	False
InferredCity	String	True
InferredCompany	String	True
InferredCountry	String	True
InferredMetropolitanArea	String	True
InferredPhoneAreaCode	String	True
InferredPostalCode	String	True
InferredStateRegion	String	True
IsAnonymous	Bool	False
IsLead	Bool	False
LastName	String	False
LatestUTMCampaign	String	False
LatestUTMContent	String	False
LatestUTMMedium	String	False
LatestUTMSource	String	False
LatestUTMterm	String	False
LeadPartitionId	Int	False	=,IN
LeadPerson	Int	True
LeadRevenueCycleModelId	Int	False
LeadRevenueStageId	Int	False
LeadRole	String	False
LeadScore	Int	False
LeadSource	String	False
LeadStatus	String	False
MainPhone	String	False
MarketingSuspended	Bool	False
MarketingSuspendedCause	String	False
MiddleName	String	False
MktoAcquisitionDate	Datetime	False
MktoCompanyNotes	String	False
MktoDoNotCallCause	String	False
MktoIsCustomer	Bool	False
MktoIsPartner	Bool	False
MktoName	String	True	=,IN
MktoPersonNotes	String	False
MobilePhone	String	False
NumberOfEmployees	Int	False
NumberofEmployeesProspect	String	False
OriginalReferrer	String	True
OriginalSearchEngine	String	True
OriginalSearchPhrase	String	True
OriginalSourceInfo	String	True
OriginalSourceType	String	True
PersonLevelEngagementScore	Int	False	=,IN
PersonPrimaryLeadInterest	Int	True
PersonTimeZone	String	True
PersonType	String	False
Phone	String	False
PostalCode	String	False
Priority	Int	False
Rating	String	False
RegistrationSourceInfo	String	False
RegistrationSourceType	String	False
RelativeScore	Int	False
RelativeUrgency	Int	False
Salutation	String	False
SicCode	String	False
Site	String	False
State	String	False
Test	String	False	=,IN
Test1	Bool	False
Test98	String	False	=,IN
TestBoolean	Bool	False
TestCustomfieldEmail	String	False	=,IN
TestFieldText1	String	False	=,IN
TestInteger	Bool	False
TestInteger_cf	Int	False	=,IN
TestKpQA	String	False	=,IN
Title	String	False
Unsubscribed	Bool	False
UnsubscribeDateFoFS	String	False
UnsubscribeDateUnleashed	String	False
UnsubscribedReason	String	False
UnsubscribeFoFS	String	False
UnsubscribeMarketing	String	False
UnsubscribeSales	String	False
UnsubscribeUnleashed	String	False
Urgency	Double	False
UTMTerm	String	False	=,IN
Website	String	False