Querying related objects in Smart lists or Velocity

[Victor_Herrero]

Hi, 

Is it possible to create a smart list and segment it by attributes from a related object? 

Is it possible to do the same thing with Velocity at least? 

Alternatively, could a report in salesforce automatically and periodically (or perhaps at runtime) update a list in Marketo?

The whole problem is not being able to tune a communication to a target audience based on information that is stored in a related object.

Here is the scenario we are struggling with:

Our business operates with two types of customers (Hosts and Guests) and our business model is about matching them for a cultural exchange. 

We want to send a communication to Hosts in a specific opportunity stage (we call it Finalled), but we want to segment this communication depending on an attribute from their Guest: their type (which can be say "A" or "B"). 

In Salesforce this could be solved with a query, but in Marketo I haven't found a way to Create a smart list like this: 

Finalled Hosts whose Guest is of type A. 

This would mean querying a related object in Salesforce and I haven't figured out how to do this in Marketo. 

I was exploring the possibility to use SOQL in Velocity (but haven't found an answer yet). If this were possible, maybe I could just create one email with a script token that outputs different text depending on the guest type. 

Thanks for your help in advance!

[SanfordWhiteman · Accepted solution]

---

Is it possible to create a smart list and segment it by attributes from a related object? 

---

Maybe... depends on the exact relationship.

---

Is it possible to do the same thing with Velocity at least? 

---

Any value you can access/output from Velocity can also be used in a Smart List. (Though not vice versa.) So while VTL can do all sorts of amazing things with data, it doesn't have deeper reach than the outer layer of SL qualification.

---

I was exploring the possibility to use SOQL in Velocity (but haven't found an answer yet). If this were possible, maybe I could just create one email with a script token that outputs different text depending on the guest type. 

---

There's no SOQL in Marketo (period) and no SQL-type access possible from Velocity.

Velocity gets OO copies of first-level and related objects (can be 2nd- and 3rd-level related objects, again it depends on how the rels are set up in CRM). It can't reach further than what it's given.

[Victor_Herrero]

Thanks! 

OK, SOQL not an option and VTL and Smart Lists have the same reach. 

I'll try to be a bit more specific then: 

Guests and Hosts are both opportunities at this point, but they have a different record type. We have another object now (the Relationship) for which two rows are generated, to denote the relationship of the Guest with the Host and the Host with the Guest. Both these rows contain the Account ID of both the Guest and the Host. Perhaps a diagram will be necessary. 

Now what would need to be done is: In each host list should contain only the hosts with a Guest of type A or type B, which is determined by a value on a field on the opportunity (but not the record type). 

How I think one would go about this in Salesforce is: get the Guest account ID from the relationship, search for that account, search for the corresponding opportunity, then see the type. If type = A, then add to list, otherwise don't. Same approach for a list of Hosts with Guests of type B.