Webhook Using CSV as Lookup to Write to a Field

[Catzilla]

Hi all,

I haven't worked with webhooks very much, but I'm investigating if this is a possible use case, and if so, how to create it. 

Some background: my organization uses email domain to label what company or agency a contact belongs to. Currently we have a smart campaign with dozens of flow steps and a choice for each domain. We say if their Domain is agencyA.org, then their Agency Name is Agency A. 

We are planning to make sure the values are updated, as it's been a few years, and checking and changing the many choices will be cumbersome to say the least. I think a webhook via FlowBoost could work, but I've only set up one webhook before and I essentially copied the FlowBoost user guide. I haven't been able to find a similar use case here on the forum, and I'm way out of my depth with Javascript. 

So, my question is this: if I upload a CSV file with relevant fields to Design Studio and reference it in the webhook, how can I write use column A (domain) to write column B (company/agency) to a different field?

[SanfordWhiteman · Accepted solution]

You can’t send a webhook directly to a static file (your CSV in Design Studio) and perform a lookup.

Requesting the file URL just downloads the entire file, it doesn’t search the file. Imagine opening the static CSV in your browser, it’ll do the same thing.

So you need to use a service like FlowBoost to fetch, parse, and search the file and return results to Marketo. (You can also cache the file within FlowBoost so it doesn’t need to be fetched every time, only when you make an update.)

If you upload a domain_agency_names.csv like this:

domain,name
example.com,ExampleCo
example.net,NetEx LLC

Then the FlowBoost JS to fetch + search is:

var emailParts = FBUtil.string.partsFromEmail({{lead.Email Address}});

FBHttp
.fetch( "https://pages.example.com/rs/123-ABC-456/images/domain_agency_names.csv" ) 
.then( FBText.CSV.autoParse )
.then( rows => 
  rows.find( row => row.domain === emailParts.domain )  
)
.then( foundDomain => 
  foundDomain ? foundDomain.name : undefined
)
.then( success )

The code in brief:

• parses the current lead’s Email into its parts (mailbox & domain)
• gets the CSV file
  
• parses the CSV contents into an array
  
• finds the first object in the array whose domain matches the lead’s domain
• if an object was found, returns its name
  

[Catzilla]

Thanks for such a thorough response! To check my understanding:

This line requests the CSV file (aka fetches it)

FBHttp
.fetch( "https://pages.example.com/rs/123-ABC-456/images/domain_agency_names.csv" ) 

 This line parses the contents of the CSV into a usable format. In this case, because we have multiple items, it's an array.

.then( FBText.CSV.autoParse )

If the domain it finds in the contact's email is equal to the domain in the array, it will return the agency name. Otherwise, it will return "undefined".

.then( rows => 
  rows.find( row => row.domain === emailParts.domain )  
)
.then( foundDomain => 
  foundDomain ? foundDomain.name : undefined

 Is that about right?

Thank you again!

[SanfordWhiteman]

Pretty close!

 This line parses the contents of the CSV into a usable format. In this case, because we have multiple items, it's an array.

.then( FBText.CSV.autoParse )

---

Well, whether there’s one row or (as we expect) many rows, you still want an array so you have something to loop over. At This will create an array of objects even if there’s only one row. Or actually even if there’s zero rows (in which case the array is empty).

---

If the domain it finds in the contact's email is equal to the domain in the array, it will return the agency name. Otherwise, it will return "undefined".

.then( rows => 
  rows.find( row => row.domain === emailParts.domain )  
)
.then( foundDomain => 
  foundDomain ? foundDomain.name : undefined

 Is that about right?

Thank you again!

---

Array#find is effectively shorthand for a loop that stops when it finds a match. So this would be equivalent:

.then( rows => {
  let foundDomain;
  for( const row of rows ) {
    if( row.domain === emailParts.domain ) {
      foundDomain = row;
      break;
    }
  }
  return found;  
})
.then( foundDomain => 
  foundDomain ? foundDomain.name : undefined

[Catzilla]

Got it! Thanks so much for the code and thorough explanation!