In order to take full advantage of Marketo analytics it is crucial to build out correct and robust associations between your lead, company and opportunity records. When you are not leveraging a native CRM-sync, building these relationships can pose some difficulties, so today we’ll walk through building them.
Object Relationships
In Marketo, there are a few vital relationships to fully establish opportunity reporting:
- Leads and Opportunities have a many to many relationship via the OpportunityRole object.
- OpportunityRole has both a leadId and externalopportunityid field to create the relationship from lead to opportunity.
- In order to qualify for a Has Opportunity smart list filter, a lead must have an OpportunityRole related to an opportunity.
- Opportunities have a many-to-one relationship to the Company object via the externalCompanyId field.
- Leads have a one-to-many relationship to Companies via the externalCompanyId field.
- Opportunities are attributed to a program based on a lead’s Acquisition Program or their membership and success in a program (See Understanding Attribution).
Building out these relationships across your lead database will allow you to fully leverage Marketo analytics and see the influence that your programs have on opportunity creation and win rates.
Companies
The simplest way to build out these relationships is by starting with company creation. This ensures that we can pass externalCompanyId to our opportunities during creation, instead of having to perform additional API calls to update opportunities after they’ve been created. Depending on existing configuration, this may or may not be a necessary step, but net new leads and contacts with associated companies will need to have these records added to your Marketo instance in order for the relationships to be built, so let’s take a look at some code to create company records.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
package dev.marketo.opportunities; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.OutputStreamWriter; import java.io.Reader; import java.net.MalformedURLException; import java.net.URL; import java.util.ArrayList; import java.util.List; import javax.net.ssl.HttpsURLConnection; import com.eclipsesource.json.JsonArray; import com.eclipsesource.json.JsonObject; public class UpsertCompanies { public List<JsonObject> input; //a list of Companies to use for input. Each must have a member "externalCompanyId". public String action; //specify the action to be undertaken, createOnly, updateOnly, createOrUpdate public String dedupeBy; //select mode of Deduplication, dedupeFields for all dedupe parameters(externalCompanyId), idField for marketoId private String endpoint; //endpoint URL created with Constructor private Auth auth; //Marketo Auth Object //Constructs an UpsertOpportunities with Auth, but with no input set public UpsertCompanies(Auth auth){ this.auth = auth; this.endpoint = this.auth.marketoInstance + "/rest/v1/companies.json"; } //Constructs and UpsertOpportunities with Auth and input set public UpsertCompanies(Auth auth, List<JsonObject> input) { this(auth); this.input = input; } //adds input to existing list, creates arraylist if it was built without a list public UpsertCompanies addCompanies(JsonObject... companies){ if (this.input == null){ this.input = new ArrayList(); } for (JsonObject jo : companies) { System.out.println(jo); this.input.add(jo); } return this; } public JsonObject postData(){ JsonObject result = null; try { JsonObject requestBody = buildRequest(); //builds the Json Request Body String s = endpoint + "?access_token=" + auth.getToken(); //takes the endpoint URL and appends the access_token parameter to authenticate URL url = new URL(s); HttpsURLConnection urlConn = (HttpsURLConnection) url.openConnection(); //Return a URL connection and cast to HttpsURLConnection urlConn.setRequestMethod("POST"); urlConn.setRequestProperty("Content-type", "application/json"); urlConn.setRequestProperty("accept", "text/json"); urlConn.setDoOutput(true); OutputStreamWriter wr = new OutputStreamWriter(urlConn.getOutputStream()); wr.write(requestBody.toString()); wr.flush(); InputStream inStream = urlConn.getInputStream(); //get the inputStream from the URL connection Reader reader = new InputStreamReader(inStream); result = JsonObject.readFrom(reader); //Read from the stream into a JsonObject urlConn.disconnect(); } catch (MalformedURLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return result; } private JsonObject buildRequest(){ JsonObject requestBody = new JsonObject(); //Create a new JsonObject for the Request Body JsonArray in = new JsonArray(); //Create a JsonArray for the "input" member to hold Opp records for (JsonObject jo : input) { in.add(jo); //add our company records to the input array } requestBody.add("input", in); if (this.action != null){ requestBody.add("action", action); //add the action member if available } if (this.dedupeBy != null){ requestBody.add("dedupeBy", dedupeBy); //add the dedupeBy member if available } return requestBody; } //Getters and Setters //Setters return the UpsertCompanies instance to allow simple formatting: public List<JsonObject> getInput() { return input; } //sets or replaces existing input with list public UpsertCompanies setInput(List input) { this.input = input; return this; } public String getAction() { return action; } public UpsertCompanies setAction(String action) { this.action = action; return this; } public String getDedupeBy() { return dedupeBy; } public UpsertCompanies setDedupeBy(String dedupeBy) { this.dedupeBy = dedupeBy; return this; } } |
The companies API provides two deduplication options, set by the dedupeBy parameter in the request, “dedupeFields” and “idField.” These can be explicitly retrieved by calling Describe Companies. If dedupeBy is unset, it defaults to dedupeFields. In the case of company records, dedupeFields always corresponds to “externalCompanyId,” which is an arbitrary string set by an external source, and idField, corresponding to the “marketoId” field, which is an integer generated and returned by Marketo after creation. Depending on the selection for dedupeBy, one of externalCompanyId or marketoId must be included in any upsert call for a company record. These same requirements apply to the Opportunity and Opportunity Role object APIs.
Our code exposes two constructors: one accepting a single argument of an Auth object, and another which accepts Auth and a list of JsonObject company records. If constructed without an input List, then company records must be added through the addCompanies method, which will check create a new ArrayList if the input is null, and then add all JsonObject arguments to the input List. Here’s an example usage:
|
1 2 3 4 5 |
//Create a new company to associate to JsonObject myCompany = new JsonObject().add("externalCompanyId", "myCompany"); UpsertCompanies upsertCompanies = new UpsertCompanies(auth).addCompanies(myCompany); JsonObject companiesResult = upsertCompanies.postData(); System.out.println(companiesResult); |
We’re creating a single company JsonObject with just one field, externalCompanyId, then constructing an instance of UpsertCompanies, and adding our company to the input list with addCompanies.
Opportunities
Similar to the company objects, the opportunity API has a dedupeBy parameter, accepting “dedupeFields” or “idField,” corresponding to “externalopportunityid” and “marketoGUID” respectively. So here’s our code, which looks quite similar to the UpsertCompanies class:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
package dev.marketo.opportunities; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.OutputStreamWriter; import java.io.Reader; import java.net.MalformedURLException; import java.net.URL; import java.util.ArrayList; import java.util.List; import javax.net.ssl.HttpsURLConnection; import com.eclipsesource.json.JsonArray; import com.eclipsesource.json.JsonObject; public class UpsertOpportunities { public List<JsonObject> input; //a list of Opportunities to use for input. Each must have a member "externalopportunityid". Each can optionally include "externalCompanyId" for company association public String action; //specify the action to be undertaken, createOnly, updateOnly, createOrUpdate public String dedupeBy; //select mode of Deduplication, dedupeFields for all dedupe parameters, idField for marketoId private String endpoint; //endpoint URL created with Constructor private Auth auth; //Marketo Auth Object //Constructs an UpsertOpportunities with Auth, but with no input set public UpsertOpportunities(Auth auth){ this.auth = auth; this.endpoint = this.auth.marketoInstance + "/rest/v1/opportunities.json"; } //Constructs and UpsertOpportunities with Auth and input set public UpsertOpportunities(Auth auth, List<JsonObject> input) { this(auth); this.input = input; } public UpsertOpportunities addOpportunities(JsonObject... opp){ if (this.input == null){ this.input = new ArrayList(); } for (JsonObject jo : opp) { System.out.println(jo); this.input.add(jo); } return this; } public JsonObject postData(){ JsonObject result = null; try { JsonObject requestBody = buildRequest(); //builds the Json Request Body String s = endpoint + "?access_token=" + auth.getToken(); //takes the endpoint URL and appends the access_token parameter to authenticate URL url = new URL(s); HttpsURLConnection urlConn = (HttpsURLConnection) url.openConnection(); //Return a URL connection and cast to HttpsURLConnection urlConn.setRequestMethod("POST"); urlConn.setRequestProperty("Content-type", "application/json"); urlConn.setRequestProperty("accept", "text/json"); urlConn.setDoOutput(true); OutputStreamWriter wr = new OutputStreamWriter(urlConn.getOutputStream()); wr.write(requestBody.toString()); wr.flush(); InputStream inStream = urlConn.getInputStream(); //get the inputStream from the URL connection Reader reader = new InputStreamReader(inStream); result = JsonObject.readFrom(reader); //Read from the stream into a JsonObject urlConn.disconnect(); } catch (MalformedURLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return result; } private JsonObject buildRequest(){ JsonObject requestBody = new JsonObject(); //Create a new JsonObject for the Request Body JsonArray in = new JsonArray(); //Create a JsonArray for the "input" member to hold Opp records for (JsonObject jo : input) { in.add(jo); //add our Opportunity records to the input array } requestBody.add("input", in); if (this.action != null){ requestBody.add("action", action); //add the action member if available } if (this.dedupeBy != null){ requestBody.add("dedupeBy", dedupeBy); //add the dedupeBy member if available } return requestBody; } //Getters and Setters //Setters return the UpsertOpportunites instance to allow simple formatting: public List<JsonObject> getInput() { return input; } public UpsertOpportunities setInput(List<JsonObject> input) { this.input = input; return this; } public String getAction() { return action; } public UpsertOpportunities setAction(String action) { this.action = action; return this; } public String getDedupeBy() { return dedupeBy; } public UpsertOpportunities setDedupeBy(String dedupeBy) { this.dedupeBy = dedupeBy; return this; } } |
The same constructor options are provided, taking an Auth or Auth+List<JsonObject>, and an addOpportunities method to input JsonObject opportunities. Here’s a usage example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//Create some JsonObjects for Opportunity Data JsonObject opp1 = new JsonObject().add("name", "opportunity1") .add("externalopportunityid", "Opportunity1Test") .add("externalCompanyId", "myCompany") .add("externalCreatedDate", "2015-01-01T00:00:00z"); JsonObject opp2 = new JsonObject().add("name", "opportunity2") .add("externalopportunityid", "Opportunity2Test") .add("externalCompanyId", "myCompany") .add("externalCreatedDate", "2015-01-01T00:00:00z"); //Create an Instance of UpsertOpportunities and POST it UpsertOpportunities upsertOpps = new UpsertOpportunities(auth) .setAction("createOnly") .addOpportunities(opp1, opp2); JsonObject oppsResult = upsertOpps.postData(); System.out.println(oppsResult); |
Here, we’re creating two example opportunities and then giving them values for the name, externalopportunityid, externalCompanyId, and externalCreatedDate fields. We haven’t discussed externalCreatedDate yet, but it is important to utilize since it is treated as the master field in RCE for when an opportunity was created, making it important for correct attribution. You can use your organization’s business logic to determine what you input in this field, based on whether you’re backfilling existing opportunity data, or creating new ones on the fly. We’ll create our instance of UpsertOpportunities and then add our JsonObjects via addOpportunities. Now that the instance is configured you can push this to Marketo with postData and print out your result
Roles
Roles are quite similar to the preceding two objects, except that they have a slightly different requirement when setting dedupeBy to dedupeFields. Roles require that three fields be included when creating or updating a record via this method, “leadId,” “role,” and”externalopportunityid.” “role” may be any string value, but the other two must refer to a valid Id of a lead, and a valid Id of an opportunity respectively.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
package dev.marketo.opportunities; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.OutputStreamWriter; import java.io.Reader; import java.net.MalformedURLException; import java.net.URL; import java.util.ArrayList; import java.util.List; import javax.net.ssl.HttpsURLConnection; import com.eclipsesource.json.JsonArray; import com.eclipsesource.json.JsonObject; public class UpsertOpportunityRoles { public List<JsonObject> input; //Array of Opportunity Roles as JsonObjects, must have "leadId", "role" and "externalopprtunityid" public String action; //specify the action to be undertaken, createOnly, updateOnly, createOrUpdate, defaults to createOrUpdate if unset public String dedupeBy;//select mode of Deduplication, dedupeFields for all dedupe parameters, idField for marketoId private String endpoint; //endpoint URL created with Constructor private Auth auth; //Marketo Auth Object //Constructs an UpsertOpportunityRoles with Auth, but with no input set public UpsertOpportunityRoles(Auth auth) { this.auth = auth; this.endpoint = this.auth.marketoInstance + "/rest/v1/opportunities/roles.json"; } //Constructs and UpsertOpportunities with Auth and input set public UpsertOpportunityRoles(Auth auth, List<JsonObject> input) { this(auth); this.input = input; } public UpsertOpportunityRoles addRoles(JsonObject... role){ if (this.input == null){ this.input = new ArrayList(); } for (JsonObject jo : role) { System.out.println(jo); this.input.add(jo); } return this; } //executes the request to Marketo, body will be empty if input is not set public JsonObject postData(){ JsonObject result = null; try { JsonObject requestBody = buildRequest(); //builds the Json Request Body String s = endpoint + "?access_token=" + auth.getToken(); //takes the endpoint URL and appends the access_token parameter to authenticate URL url = new URL(s); HttpsURLConnection urlConn = (HttpsURLConnection) url.openConnection(); //Return a URL connection and cast to HttpsURLConnection urlConn.setRequestMethod("POST"); urlConn.setRequestProperty("Content-type", "application/json");//"application/json" content-type is required. urlConn.setRequestProperty("accept", "text/json"); urlConn.setDoOutput(true); OutputStreamWriter wr = new OutputStreamWriter(urlConn.getOutputStream()); wr.write(requestBody.toString()); wr.flush(); InputStream inStream = urlConn.getInputStream(); //get the inputStream from the URL connection Reader reader = new InputStreamReader(inStream); result = JsonObject.readFrom(reader); //Read from the stream into a JsonObject urlConn.disconnect(); } catch (MalformedURLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return result; } public JsonObject buildRequest(){ JsonObject requestBody = new JsonObject(); JsonArray in = new JsonArray(); for (JsonObject jo : input) { in.add(jo); } requestBody.add("input", in); if (this.action != null){ requestBody.add("action", action); } if (this.dedupeBy != null){ requestBody.add("dedupeBy", dedupeBy); } return requestBody; } //Getters and Setters //Setters return the UpsertOpportunites instance to allow simple formatting: public List<JsonObject> getInput() { return input; } public UpsertOpportunityRoles setInput(List<JsonObject> input) { this.input = input; return this; } public String getAction() { return action; } public UpsertOpportunityRoles setAction(String action) { this.action = action; return this; } public String getDedupeBy() { return dedupeBy; } public UpsertOpportunityRoles setDedupeBy(String dedupeBy) { this.dedupeBy = dedupeBy; return this; } } |
We’re following a similar pattern for the constructors and addRoles method as the previous examples. Here’s an example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//Create Some opp roles now JsonObject opp1Role = new JsonObject() .add("role", "Captain") .add("externalopportunityid", opp1.get("externalopportunityid").asString()) .add("leadId", 318794); JsonObject opp2Role = new JsonObject() .add("role", "Commander") .add("externalopportunityid", opp2.get("externalopportunityid").asString()) .add("leadId", 318795); //Create an Instance of UpsertOpportunityRoles and POST it UpsertOpportunityRoles upsertRoles = new UpsertOpportunityRoles(auth) .setAction("createOnly") .addRoles(opp1Role, opp2Role); JsonObject rolesResult = upsertRoles.postData(); System.out.println(rolesResult); |
Here we’re creating the new JsonObjects for our 2 example roles, and adding their required dedupeFields, pulling the externalopportunityid from the opportunities we already created, then pushing them down to Marketo.
Putting It All Together
Here is the complete example of our main method:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
package dev.marketo.opportunities; import com.eclipsesource.json.JsonObject; public class App { public static void main( String[] args ) { //create an Instance of Auth Auth auth = new Auth("CLIENT_ID_CHANGE_ME", "CLIENT_SECRET_CHANGE_ME", "MARKETO_HOST_CHANGE_ME"); //Create a new company to associate to JsonObject myCompany = new JsonObject().add("externalCompanyId", "myCompany"); UpsertCompanies upsertCompanies = new UpsertCompanies(auth).addCompanies(myCompany); JsonObject companiesResult = upsertCompanies.postData(); System.out.println(companiesResult); //Create some JsonObjects for Opportunity Data JsonObject opp1 = new JsonObject().add("name", "opportunity1") .add("externalopportunityid", "Opportunity1Test") .add("externalCompanyId", "myCompany") .add("externalCreatedDate", "2015-01-01T00:00:00z"); JsonObject opp2 = new JsonObject().add("name", "opportunity2") .add("externalopportunityid", "Opportunity2Test") .add("externalCompanyId", "myCompany") .add("externalCreatedDate", "2015-01-01T00:00:00z"); //Create an Instance of UpsertOpportunities and POST it UpsertOpportunities upsertOpps = new UpsertOpportunities(auth) .setAction("createOnly") .addOpportunities(opp1, opp2); JsonObject oppsResult = upsertOpps.postData(); System.out.println(oppsResult); //Create Some opp roles now JsonObject opp1Role = new JsonObject() .add("role", "Captain") .add("externalopportunityid", opp1.get("externalopportunityid").asString()) .add("leadId", 318794); JsonObject opp2Role = new JsonObject() .add("role", "Commander") .add("externalopportunityid", opp2.get("externalopportunityid").asString()) .add("leadId", 318795); //Create an Instance of UpsertOpportunityRoles and POST it UpsertOpportunityRoles upsertRoles = new UpsertOpportunityRoles(auth) .setAction("createOnly") .addRoles(opp1Role, opp2Role); JsonObject rolesResult = upsertRoles.postData(); System.out.println(rolesResult); } } |
You can see the sequence of creating companies, opportunities, and roles. Now you’re all set to sync your Company and Opportunity data to Marketo.