Leads

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

Table Specific Information

Select

All columns must be specified using the '=' operator. If a column is filterable, you specify multiple values by use of the IN operator or the OR logical operator.

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)

You will get the best performance from this query if you can confine your query to a list of known Leads within Marketo. To do this, create a static list of Leads within Marketo, and then specify the ListId to retrieve them.

If no filter is specified, the Activities_NewLead table is queried to retrieve a list of Lead Ids. After the Lead Ids are compiled, they are used to query the Leads table. Since one Activities_Newlead request must be made for each Leads request made, when no filter is specified it effectively doubles the number of API calls made per lead. This affects query performance.

For example:

To limit the returned Leads to those created during a specific time period, filter on the 'CreatedAt' column. When you use the '>' or '>=' operator, the datetime value is included in the Activities_NewLead table request. Since the maximum batch size per request for the REST API is 300, you can get a rough estimate of the number of API calls this query will require using the formula:
```
(Total Number of Leads / 300) * 2
```
To limit the returned Leads to those updated at a particular datetime, filter on the 'UpdatedAt' column. When you use the '>' or '>=' operator, the datetime value is included in the Activities_LeadChanges table request. Since the maximum batch size per request for the REST API is 300, you can get a rough estimate of the number of API calls this query will require using the formula:
```
(Total Number of Leads / 300) * 2
```

You can also use the SOAP API to retrieve a list of Lead Ids by setting "UseSOAPForLeadIds=True" in the 'Other' property. When UseSOAPForLeadIDs is True and the SOAP connection details are specified, the SOAP API compiles a list of Lead Ids which will then be used as a filter for the REST API.

This hybrid approach is faster than using the SOAP API by itself, because the SOAP API is significantly slower than the REST API. Since the maximum batch size per request for the SOAP API is 1000, you can get a rough estimate of the number of API calls this query will require using the formula:

(Total Number of Leads / 1000) + (Total Number of Leads / 300)

Insert

To create a new Lead record, specify the Lead's first name, last name, email address, and company name for entry into the database.

For example, to insert a new lead for someone named John Mangel with an email address of [email protected], enter:

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

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

To import multiple leads from a CSV file, ensure UseBulkAPI is set to True, then enter a command similar to the following:

INSERT INTO Leads (CSVFile) VALUES ('C:\\\\Upload\\\\ImportLeads.csv')

Update

You can update any field in the Leads table that is not read-only. Updates are performed using any 'Filterable' column, such as Email, as a lookup field (external key). (To identify all the ReadOnly and Filterable columns in the Leads table, see "Columns", below.)

For example:

To update the Leads table to assign 111-222-3333 as the MobilePhone entry to all rows where Id=1 (Id is the lookup field):
```
UPDATE Leads SET MobilePhone = '111-222-3333' WHERE Id = 1
```
To update the Leads table to assign 111-222-3333 as the MobilePhone entry to all rows where [email protected] (Email is the lookup field):
```
UPDATE Leads SET MobilePhone = '111-222-3333' WHERE Email = '[email protected]'
```

You can also use a custom field as the lookup field. To do this, you must clearly identify that the custom field is being used in this manner, by first defining the LookupField as the custom field. For example, to assign 111-222-3333 as the MobilePhone entry to all rows where MyCustomField=my value (MyCustomField is the lookup field):

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

Delete

To remove a lead from the Leads table, you must identify the lead by its Marketo Id. For example, to delete the lead whose Marketo Id=1, enter:

DELETE FROM Leads WHERE Id = 1

GetDeleted

To retrieve a list of all leads that have been deleted from the Leads Table in the past 14 days, use the GetDeleted query.

GetDeleted FROM Leads

To retrieve a list of all leads that have been deleted since the table's last update, use the GetDeleted query with the UpdatedAt filter.

GetDeleted FROM Leads UpdatedAt='date'

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Int	False	True	This is a generated column, no description is available.
Company	String	False		This is a generated column, no description is available.
Site	String	False		This is a generated column, no description is available.
BillingStreet	String	False		This is a generated column, no description is available.
BillingCity	String	False		This is a generated column, no description is available.
BillingState	String	False		This is a generated column, no description is available.
BillingCountry	String	False		This is a generated column, no description is available.
BillingPostalCode	String	False		This is a generated column, no description is available.
Website	String	False		This is a generated column, no description is available.
MainPhone	String	False		This is a generated column, no description is available.
AnnualRevenue	Decimal	False		This is a generated column, no description is available.
NumberOfEmployees	Int	False		This is a generated column, no description is available.
Industry	String	False		This is a generated column, no description is available.
SicCode	String	False		This is a generated column, no description is available.
MktoCompanyNotes	String	False		This is a generated column, no description is available.
ExternalCompanyId	String	False		This is a generated column, no description is available.
MktoName	String	True		This is a generated column, no description is available.
PersonType	String	False		This is a generated column, no description is available.
MktoIsPartner	Bool	False		This is a generated column, no description is available.
IsLead	Bool	False		This is a generated column, no description is available.
MktoIsCustomer	Bool	False		This is a generated column, no description is available.
IsAnonymous	Bool	False		This is a generated column, no description is available.
Salutation	String	False		This is a generated column, no description is available.
FirstName	String	False		This is a generated column, no description is available.
MiddleName	String	False		This is a generated column, no description is available.
LastName	String	False		This is a generated column, no description is available.
Email	String	False	True	This is a generated column, no description is available.
Phone	String	False		This is a generated column, no description is available.
MobilePhone	String	False		This is a generated column, no description is available.
Fax	String	False		This is a generated column, no description is available.
Title	String	False		This is a generated column, no description is available.
ContactCompany	Int	True		This is a generated column, no description is available.
DateOfBirth	Date	False		This is a generated column, no description is available.
Address	String	False		This is a generated column, no description is available.
City	String	False		This is a generated column, no description is available.
State	String	False		This is a generated column, no description is available.
Country	String	False		This is a generated column, no description is available.
PostalCode	String	False		This is a generated column, no description is available.
PersonTimeZone	String	True		This is a generated column, no description is available.
OriginalSourceType	String	True		This is a generated column, no description is available.
OriginalSourceInfo	String	True		This is a generated column, no description is available.
RegistrationSourceType	String	False		This is a generated column, no description is available.
RegistrationSourceInfo	String	False		This is a generated column, no description is available.
OriginalSearchEngine	String	True		This is a generated column, no description is available.
OriginalSearchPhrase	String	True		This is a generated column, no description is available.
OriginalReferrer	String	True		This is a generated column, no description is available.
EmailInvalid	Bool	False		This is a generated column, no description is available.
EmailInvalidCause	String	False		This is a generated column, no description is available.
Unsubscribed	Bool	False		This is a generated column, no description is available.
UnsubscribedReason	String	False		This is a generated column, no description is available.
DoNotCall	Bool	False		This is a generated column, no description is available.
MktoDoNotCallCause	String	False		This is a generated column, no description is available.
DoNotCallReason	String	False		This is a generated column, no description is available.
MarketingSuspended	Bool	False		This is a generated column, no description is available.
MarketingSuspendedCause	String	False		This is a generated column, no description is available.
BlackListed	Bool	False		This is a generated column, no description is available.
BlackListedCause	String	False		This is a generated column, no description is available.
MktoPersonNotes	String	False		This is a generated column, no description is available.
AnonymousIP	String	False		This is a generated column, no description is available.
InferredCompany	String	True		This is a generated column, no description is available.
InferredCountry	String	True		This is a generated column, no description is available.
InferredCity	String	True		This is a generated column, no description is available.
InferredStateRegion	String	True		This is a generated column, no description is available.
InferredPostalCode	String	True		This is a generated column, no description is available.
InferredMetropolitanArea	String	True		This is a generated column, no description is available.
InferredPhoneAreaCode	String	True		This is a generated column, no description is available.
EmailSuspended	Bool	False		This is a generated column, no description is available.
EmailSuspendedCause	String	False		This is a generated column, no description is available.
EmailSuspendedAt	Datetime	False		This is a generated column, no description is available.
Department	String	False		This is a generated column, no description is available.
CreatedAt	Datetime	True	True	This is a generated column, no description is available.
UpdatedAt	Datetime	True	True	This is a generated column, no description is available.
Cookies	String	False	True	This is a generated column, no description is available.
ExternalSalesPersonId	String	False		This is a generated column, no description is available.
LeadPerson	Int	True		This is a generated column, no description is available.
LeadRole	String	False		This is a generated column, no description is available.
LeadSource	String	False		This is a generated column, no description is available.
LeadStatus	String	False		This is a generated column, no description is available.
LeadScore	Int	False		This is a generated column, no description is available.
Urgency	Double	False		This is a generated column, no description is available.
Priority	Int	False		This is a generated column, no description is available.
RelativeScore	Int	False		This is a generated column, no description is available.
RelativeUrgency	Int	False		This is a generated column, no description is available.
Rating	String	False		This is a generated column, no description is available.
PersonPrimaryLeadInterest	Int	True		This is a generated column, no description is available.
LeadPartitionId	Int	False		This is a generated column, no description is available.
LeadRevenueCycleModelId	Int	False		This is a generated column, no description is available.
LeadRevenueStageId	Int	False		This is a generated column, no description is available.
AcquisitionProgramId	Int	False		This is a generated column, no description is available.
MktoAcquisitionDate	Datetime	False		This is a generated column, no description is available.
TestKpQA	String	False		This is a generated column, no description is available.
TestCustomfieldEmail	String	False		This is a generated column, no description is available.
Ecids	String	True		This is a generated column, no description is available.
TestFieldText1	String	False		This is a generated column, no description is available.
Test1	Bool	False		This is a generated column, no description is available.
Cstmfdtest1	String	False		This is a generated column, no description is available.
Cstmfdtest2	String	False		This is a generated column, no description is available.
Test	String	False		This is a generated column, no description is available.
Test98	String	False		This is a generated column, no description is available.
LookupField	String	False	True	This is filter only column that will not contain data.
ListId	Int	False	True	This is filter only column that will not contain data.
ProgramId	Int	False	True	This is filter only column that will not contain data.
PartitionName	String	False	True	This is filter only column that will not contain data.
MembershipAcquiredBy	String	True	False	This is a generated column, that is only available when filtering by ProgramId.
MembershipIsExhausted	Bool	True	False	This is a generated column, that is only available when filtering by ProgramId.
MembershipMembershipDate	Datetime	True	False	This is a generated column, that is only available when filtering by ProgramId.
MembershipNurtureCadence	String	True	False	This is a generated column, that is only available when filtering by ProgramId.
MembershipProgressionStatus	String	True	False	This is a generated column, that is only available when filtering by ProgramId.
MembershipReachedSuccess	Bool	True	False	This is a generated column, that is only available when filtering by ProgramId.
MembershipReachedSuccessDate	Datetime	True	False	This is a generated column, that is only available when filtering by ProgramId.
MembershipStream	String	True	False	This is a generated column, that is only available when filtering by ProgramId.
MembershipUpdatedAt	Datetime	True	False	This is a generated column, that is only available when filtering by ProgramId.

SSIS Components for Marketo