TDV Adapter for Marketo

Build 22.0.8462

Leads

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 ('john@mark.com', '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 = 'john@mark.com'

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.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462