AnsweredAssumed Answered

Lookup tables

Question asked by Michael Oslin on Nov 12, 2018
Latest reply on Jun 13, 2019 by Sanford Whiteman

Like • Show 0 Likes0
Comment • 10

I understand Marketo doesn't have lookup tables, but we can create a smart campaign to do a replace value.The problem is that currently I have ~300 values, and that list may grow. Also if we can figure out a way to do lookups, there are cases where we would use this for much larger data sets.

Sanford Whiteman had posted a blog post related to a similar case I had, but being that I'm not a developer, I am not sure if this can be used to replace an existing value field or if no match in column A, simply ignore.

The value I want to replace is a UTM Campaign that is brought in from our web visits, the problem is that in the past those UTM Campaign values were not standardized, so we have all sorts of values coming in that we would like standardized.

I've created the translator values in an excel table, but wanted to avoid creating 300+ rules in Marketo smart campaigns if this could be done another way.

I see ideas for lookups in the community for this, but apparently they didn't get enough votes. We did functions like this with our previous Automation tool and I could benefit tremendously if we could run data transformation tables in Marketo.

Here is an example of the data:

Campaign Name received from the form [value we get from the web visit]	Value we would like stored on the record
17janc_fr_sc_dm_better_together_wms_supply_chain_mag	17-01-emea-fr-supplychain-dm-better-together-wms-supply-chain-mag
17-dec_emea_tms_logisticsmagazine_ads	17-01-emea-uk-transportation-logisticsmagazine-ads
17jan_us_sc_cm_mcleod_lme_carrier_perspectives_whitepaper	17-01-noam-us-supplychain-cm-mcleod-lme-carrier-perspectives-whitepaper

Any thoughts or help would be great.

Thank you-

1 person has this question

Outcomes

Sanford Whiteman Nov 12, 2018 7:53 AM

If you feel like trying a new method, I'm working on a blog post about a new take and you can help me QA if you want. Send me a message.

P.S. Don't know which of my older posts you meant because it looks like you linked to the blog homepage.

Actions

Jay Jiang Nov 12, 2018 6:19 PM

A simple php webhook solution

<?php
$utm_campaign = $_POST['utm_campaign'];
$data=[
    "utm_campaign1"=>"utm_campaign_actual1",
    "utm_campaign2"=>"utm_campaign_actual2",
    "utm_campaign3"=>"utm_campaign_actual3",
    "utm_campaign4"=>"utm_campaign_actual4",
    "utm_campaign5"=>"utm_campaign_actual5"
];
if(isset($data[$utm_campaign])){
$response = ["utm_campaign"=>$data[$utm_campaign]];
echo json_encode($response);
}
?>

Actions

Sanford Whiteman @ Jay Jiang on Nov 12, 2018 7:23 PM

Wait a few days and you'll be able to do this without maintaining a whole separate server...

1 person found this helpful

Actions

Matt De Palm @ Sanford Whiteman on Jun 4, 2019 1:08 PM

Sanford Whiteman - Sorry to resurrect an old post, did you end up finishing your blog post related to this topic? Interested to see what you came up with.

Actions

Grant Booth Nov 13, 2018 12:50 PM

We use a webhook to Workato (where lookup tables are supported) to accomplish this. We send the value in the webhook, it matches it in a lookup table, and then sends a value back to Marketo via REST API. The lookup table can be updated via a simple CSV import. It is a custom process but I'd be happy to explain how we do it if you're interested in that route. We were already using Workato for other custom services and integrations, so it was 0 extra cost to do.

Actions

Sanford Whiteman @ Grant Booth on Nov 13, 2018 1:12 PM

Via REST API? That sounds crazy. DoS waiting to happen for any batch that's larger than your number of daily API calls (not even touching the other integrations).

Shouldn't ever need to loop back over REST when using a webhook. The Webhook API is designed to write data back to the lead directly.

Actions

Grant Booth @ Sanford Whiteman on Nov 13, 2018 1:19 PM

Sanford we only use it for triggers, not batch processes, so the API limit isn't a problem. The same webhook actually runs a variety of logic at the same time, so it needs a little time to process before writing into the Marketo DB. For example it also does some simple math-related tasks that can't be done in Marketo, like marking as junk if First Name = Last Name, etc.

Actions

Sanford Whiteman @ Grant Booth on Nov 13, 2018 1:26 PM

we only use it for triggers, not batch processes, so the API limit isn't a problem.

But are the triggers themselves forcibly capped at a certain number of calls per day?

Either way, it's not the way one should use webhooks unless there's truly no alternative. The goal w/webhooks is that data updates on the host result from a single request/response. There are so many easier ways to do what you're doing that don't require any loopback connections.

For example it also does some simple math-related tasks that can't be done in Marketo, like marking as junk if First Name = Last Name, etc.

Surely that takes < 1μs to process on the server.

1 person found this helpful

Actions

Willem Labuschagne @ Grant Booth on Jun 13, 2019 5:53 AM

Hi Grant
This is interesting. So basically I can have a webhook to Redis (key &value list) to update a field in Marketo.

Actions

Sanford Whiteman @ Willem Labuschagne on Jun 13, 2019 12:40 PM

Yes, you can (and should) use a webhook's own response mapping to update Marketo. (Not another API connection.)

We use several different back ends for this kind of thing across clients:

etcd (KV store)
influxdb (timeseries db)
Google Sheet (query, not update in this case)

1 person found this helpful

Actions

10 Replies

Sanford Whiteman Nov 12, 2018 7:53 AM
Mark Correct
Correct Answer
If you feel like trying a new method, I'm working on a blog post about a new take and you can help me QA if you want. Send me a message.

P.S. Don't know which of my older posts you meant because it looks like you linked to the blog homepage.
Like • Show 0 Likes0
Actions
Jay Jiang Nov 12, 2018 6:19 PM
Mark Correct
Correct Answer
A simple php webhook solution
```
<?php
$utm_campaign = $_POST['utm_campaign'];
$data=[
    "utm_campaign1"=>"utm_campaign_actual1",
    "utm_campaign2"=>"utm_campaign_actual2",
    "utm_campaign3"=>"utm_campaign_actual3",
    "utm_campaign4"=>"utm_campaign_actual4",
    "utm_campaign5"=>"utm_campaign_actual5"
];
if(isset($data[$utm_campaign])){
$response = ["utm_campaign"=>$data[$utm_campaign]];
echo json_encode($response);
}
?>
```
Like • Show 0 Likes0
Actions
- Sanford Whiteman @ Jay Jiang on Nov 12, 2018 7:23 PM
  Mark Correct
  Correct Answer
  Wait a few days and you'll be able to do this without maintaining a whole separate server...
  1 person found this helpful
  Like • Show 0 Likes0
  Actions
  - Matt De Palm @ Sanford Whiteman on Jun 4, 2019 1:08 PM
    Mark Correct
    Correct Answer
    Sanford Whiteman - Sorry to resurrect an old post, did you end up finishing your blog post related to this topic? Interested to see what you came up with.
    Like • Show 0 Likes0
    Actions
Grant Booth Nov 13, 2018 12:50 PM
Mark Correct
Correct Answer
We use a webhook to Workato (where lookup tables are supported) to accomplish this. We send the value in the webhook, it matches it in a lookup table, and then sends a value back to Marketo via REST API. The lookup table can be updated via a simple CSV import. It is a custom process but I'd be happy to explain how we do it if you're interested in that route. We were already using Workato for other custom services and integrations, so it was 0 extra cost to do.
Like • Show 1 Like1
Actions
- Sanford Whiteman @ Grant Booth on Nov 13, 2018 1:12 PM
  Mark Correct
  Correct Answer
  Via REST API? That sounds crazy. DoS waiting to happen for any batch that's larger than your number of daily API calls (not even touching the other integrations).
  
  Shouldn't ever need to loop back over REST when using a webhook. The Webhook API is designed to write data back to the lead directly.
  Like • Show 0 Likes0
  Actions
  - Grant Booth @ Sanford Whiteman on Nov 13, 2018 1:19 PM
    Mark Correct
    Correct Answer
    Sanford we only use it for triggers, not batch processes, so the API limit isn't a problem. The same webhook actually runs a variety of logic at the same time, so it needs a little time to process before writing into the Marketo DB. For example it also does some simple math-related tasks that can't be done in Marketo, like marking as junk if First Name = Last Name, etc.
    Like • Show 0 Likes0
    Actions
    - Sanford Whiteman @ Grant Booth on Nov 13, 2018 1:26 PM
      Mark Correct
      Correct Answer
      we only use it for triggers, not batch processes, so the API limit isn't a problem.
      But are the triggers themselves forcibly capped at a certain number of calls per day?
      
      Either way, it's not the way one should use webhooks unless there's truly no alternative. The goal w/webhooks is that data updates on the host result from a single request/response. There are so many easier ways to do what you're doing that don't require any loopback connections.
      
      For example it also does some simple math-related tasks that can't be done in Marketo, like marking as junk if First Name = Last Name, etc.
      Surely that takes < 1μs to process on the server.
      1 person found this helpful
      Like • Show 0 Likes0
      Actions
- Willem Labuschagne @ Grant Booth on Jun 13, 2019 5:53 AM
  Mark Correct
  Correct Answer
  Hi Grant
  This is interesting. So basically I can have a webhook to Redis (key &value list) to update a field in Marketo.
  Like • Show 0 Likes0
  Actions
  - Sanford Whiteman @ Willem Labuschagne on Jun 13, 2019 12:40 PM
    Mark Correct
    Correct Answer
    Yes, you can (and should) use a webhook's own response mapping to update Marketo. (Not another API connection.)
    
    We use several different back ends for this kind of thing across clients:
    
    etcd (KV store)
    influxdb (timeseries db)
    Google Sheet (query, not update in this case)
    1 person found this helpful
    Like • Show 1 Like1
    Actions

Lookup tables

Outcomes

Related Content

Recommended Content

Incoming Links