How to download a csv file that is returned by Marketo REST API call
Hi- SnapLogic community has been of immense help for our organization. As part of our initiative, we are required to make a Marketo REST API call and ingest the data into target system. Marketo documentation: Lead Database - Marketo Developers Marketo REST API ( [/bulk/v1/leads/export/{exportId}/file.json) returns a csv file. We are using the snap “REST Get” to make the Marketo REST API call. However, we are trying to figure out the best way to download the csv file that is returned by the REST API. Please note that the content returned by the REST API will be around 300 MB. Appreciate any help on this. Thanks SumanConnecting to Marketo with the REST Snap Pack
While SnapLogic’s REST OAuth account supports only OAuth 2.0, it does not work with Marketo’s OAuth implementation. To work with Marketo, you must authenticate manually using the REST Get Snap. In this pipeline, we pass the credentials in as pipeline parameters. Note: This method does expose your credentials in the pipeline. Authorization To simplify the process, define the following pipeline parameters url: the REST API URL for your Marketo instance, like: https://xxx-xxx-xxx.mktorest.com clientID: The clientID for API access. clientKey: The client secret for API access. Add a REST Get Snap (labeled Marketo Login here) and configure as follows: For Service URL, toggle on the Expression button ( = ) and set the field to: _url + '/identity/oauth/token?grant_type=client_credentials&client_id=' + _clientID + '&client_secret=' +_clientKey Remove the input view. Validate the Snap and it will a return a response that contains an access_token and scope. In this example, we follow the REST Get with a Mapper Snap to map the token outside of the array. Using the Access Token In subsequent Snaps, we pass this token as a header, rather than a query parameter because it simplifies paged operations such as Get Lead Changes. Here’s an example of a simple call which does this. For Service URL, toggle on the Expression button ( = ) and set the field to: _url + '/rest/v1/activities/types.json' Under HTTP Header, set Key to Authorization and Value with the Expression button ( = ) toggled on to ‘Bearer ‘ + $accessToken Paged Operations When you get to more complex operations, such as getting lead changes, you need to make two API calls: the first creates a paging token, and the second uses the paging token typically with the paging mechanism enabled in our REST GET Snap. Get Paging Token In this REST Get Snap (renamed Get Paging Token for clarity) is where you specify the query parameters. For instance, if you want to get lead changes since a particular date, you’d pass that in via “sinceDateTime”. The example provided uses a literal string, but could be a pipeline parameter or ideally one of a Date objects formatted to match what Marketo expects. _url + '/rest/v1/activities/pagingtoken.json' Configure Paging Mechanism When calling Get Leads (via a REST GET Snap), a few things to bear in mind: You need to pass “nextPageToken” as a query parameter, along with the fields you want back. Ideally, the list of fields should be in a pipeline parameter because they appear twice in this configuration. The leads will be returned in $entity.result, which is an array. This field will not exist if there are no results, so you need to enable “Null safe” on a Splitter Snap after this REST Get. Paging expressions for the REST Get Snap are: Has next: $entity.moreResult == true Next URL: '%s/rest/v1/activities/leadchanges.json?nextPageToken=%s&fields=firstName,lastName'.sprintf( _url, $entity.nextPageToken ) API Throttling Marketo throttles API calls. Their documentation says “100 API calls in a 20 second window”. Since our REST Snap paging now includes an option to wait for X seconds or milliseconds between requests, use it whenever you are retrieving paginated results. Downloads Marketo REST.slp (14.7 KB)
Get Marketo Access Token
Created by @pdas This pipeline is used as a child pipeline to provide the access token in other Marketo-based patterns. Configuration For the REST Get, you will need to specify the following Marketo information: Set Service URL to get the OAuth token, in the form of: https://XXX-XXX-XXX.mktorest.com/identity/oauth/token Define the following Query parameters: client_id client_secret grant type, set to client_credentials Sources: Marketo API Targets: Marketo API Snaps used: REST Get, Mapper, Union Downloads Marketo-Access Token.slp (5.4 KB)Sync Excel to Marketo
Created by @mthammareddy For a row in an Excel spreadsheet, search for the lead in Marketo based on the email address supplied. If the lead does not exist in Marketo, add it. The Excel file should contain matching field names, such as: FirstName, LastName, Email, Title, Company, Country Configuration In the Pipeline Parameters, update the endpoint parameter with the url to your Marketo instance. In the File Reader, update the Excel file to read. In the REST Post Snap labelled Create/Update Leads in Marketo, under HTTP Header, update the Authorization value. Sources: Excel file Targets: Marketo Lead Snaps used: File Reader, Excel Parser, JSON Generator, REST Post Downloads Sync_Excel_to_Marketo.slp (7.9 KB)
Batch sync new leads from Marketo to Amazon Redshift
Created by @mthammareddy When there is a new lead activity batch in Marketo, synch the batch into Amazon Redshift. Configuration This pattern also requires the Get Marketo Access Token pattern. Sources: Marketo Targets: Redshift Snaps used: Pipeline Execute, REST Get, JSON Splitter, Mapper, Redshift - Insert Downloads MarketoBatchLeads_To_RedShift.slp (9.3 KB)Sync new customer from NetSuite to Marketo
Created by @mthammareddy For a new customer created in NetSuite, SnapLogic searches for a matching lead in Marketo by email address. If no matching lead is found in Marketo, SnapLogic creates a new lead in Marketo. Configuration Update the following pipeline parameters to match your Marketo environment: authorization: the token in the form of “Bearer XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX:XX” endpoint: your Marketo URL in the form of “https://XXX-XXX-XXX.mktorest.com” Sources: NetSuite customer Targets: Marketo lead Snaps used: REST Post, NetSuite Get, Mapper, JSON Generator Downloads NetSuite_Customer_Created_Record_Marketo.slp (11.7 KB)Sync new/updated lead from Microsoft Dynamics CRM to Marketo
Created by @mthammareddy For a lead created or updated in Microsoft Dynamics CRM, SnapLogic searches for a matching lead in Marketo by email address. If no matching lead is found in Marketo, SnapLogic creates a new lead. If a matching lead is found in Marketo, SnapLogic updates that lead. Configuration You will need to define the following pipeline parameters: authorization, in the form of Bearer xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:xx endpoint, in the form of https://xxx-xxx-xxx.mktorest.com Sources: Microsoft Dynamics CRM Lead Targets: Marketo Lead Snaps used: Dynamic CRM Read, Filter, Mapper, JSON Generator, REST Post Downloads Microsoft_CRM_Lead_Record_Marketo.slp (9.9 KB)Add lead in Marketo and record in Dropbox
Created by @mthammareddy This pattern creates a lead in Marketo, then SnapLogic adds the lead into Dropbox as a new file. Configuration You will need to define the following pipeline parameters: authorization, in the form of Bearer xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:xx endpoint, in the form of https://xxx-xxx-xxx.mktorest.com Then you need to pass the user details with the parameters first, last, email, title, company, and country. Sources: Mapper Targets: Marketo Lead, Dropbox Snaps used: REST Post, Mapper, Worksheet Writer, JSON Generator, Copy, JSON Splitter, Sequence, Join Downloads Marketo_Lead_Created_Record_Dropbox.slp (19.9 KB)Lead added/updated in Marketo creates record in Google Sheets
Created by @mthammareddy This pattern creates a lead in Marketo, then SnapLogic adds the lead into Google Sheets as a new row. Configuration You will need to define the following pipeline parameters: authorization, in the form of Bearer xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:xx endpoint, in the form of https://xxx-xxx-xxx.mktorest.com GoogleSheetsName, the name of the Google Sheet to write to sheetName, the name of the individual worksheet to write to within that Google Sheet Then you need to pass the user details with the parameters first, last, email, title, company, and country. The initial Mapper contains data to create a user. You can either replace these values every time or define pipeline parameters to pass the values in. Sources: Mapper Targets: Marketo Lead, Google Sheet Snaps used: REST Post, Mapper, Worksheet Writer, JSON Generator, Copy, JSON Splitter, Sequence, Join Downloads Marketo_Lead_Created_Updated_GoogleSheets.slp (19.7 KB)Sync leads from Salesforce to Marketo
Created by @mthammareddy For a new lead created in Salesforce, SnapLogic searches for a matching lead in Marketo by email address. If a matching lead is not found in Marketo, SnapLogic creates a new lead. Configuration Specify your access token and endpoint URL in the Pipeline Parameters. The initial Mapper contains and expression to lookup a particular user. You can either replace the ID value every time or define pipeline parameters to pass the value in and change the expression to: "Id='" +_SFDC_ID + "'" where SFDC_ID is the name of the pipeline parameter. Sources: Salesforce Lead Targets: Marketo Lead Snaps used: REST Post, Mapper, Salesforce Read, JSON Generator, Join, Pipeline Execute Downloads Sync_Leads_from_Salesforce_to_Marketo.slp (11.5 KB) Note: This pattern also uses the pattern to get the Marketo access token found here: Get Marketo Access Token
