Segmentation? Custom Object?

[Joe_Kneale1]

Hi Marketo Community!

I have a database of around 300,000 records, both prospects and active customers for a multi-location businesses. We are feeding data into marketo through API, in the past we have assigned records to their respective "office location" through a field on the lead object that is passed in through api (done via a segmentation), recently there was a structural change requiring us to now look that data up based on the zip code that is on the lead record.

Should I:

1. Create a segmentation scheme with 75 segments, and within each segment manually type in all 8,832 zip codes.

2. Create a custom object that contains zip code and office name, and link that object to the leads table on zip.

3. Something else?

When I tried to go the custom object route, I noticed:

1. I cannot use the custom object as a filter in the segment (noted here on the community as a requested feature)

2. I cannot write the value of the custom object linked "office" field as a data value change to a field on the lead record as a token as a workaround to "post the value back" to the lead record so it could be used by a segmentation (annoying)

3. When I create a smart list of with the filter "Has Zip2Office" (the custom object name), that smart list only returns 8,832 records (the number of zip code records) NOT all 300,000 lead records which should now be linked by zip to the custom object!

I was hoping there would be a simple way to do a lookup in marketo, but at this point I've spent way longer chasing down dead ends than if I had just done this processing as an intermediate step before pushing to marketo...

Thanks for any input you may have!

[Katja_Keesom]

Hey Joe,

I am not sure how you mapped the CO, but I don't think this would be the way to go here.

I seem to understand the office location is based not on the full zipcode, but only the first 2 positions of the zipcode, correct?

Personally, I would see if you can simply set up an operational program with a smart campaign to populate a lead field with the correct office. You would only need to set up as many change data value flow steps as you have offices and add a choice to each flow step:

Choice 1 - If Zipcode starts with (and then add the list of 2-digit zipcode starts) - Change Office to X

Default - Do nothing

Normally I would actually make it one single flow step with as many choices as you have different values to populate, but I must admit I never tried 75 choices in a single flow step so as a security measure I would break it up per office.

As your first run would be a quite a campaign to run, you could break it up into a smart campaign per office to populate your entire database. Once that's done for maintenance purposes you would only need to run for new records or zipcode changes. Depending on the daily volume you can set that up trigger-based or in a daily batch looking for newly added people or data value changes on the zipcode field.

Hope this helps.

Katja

[SanfordWhiteman]

---

3. When I create a smart list of with the filter "Has Zip2Office" (the custom object name), that smart list only returns 8,832 records (the number of zip code records) NOT all 300,000 lead records which should now be linked by zip to the custom object!

---

This wouldn't happen if you had the relationships built correctly. But I wouldn't go the CO route anyway, so don't bother fixing it.

---

1. Create a segmentation scheme with 75 segments, and within each segment manually type in all 8,832 zip codes.

---

As brutal as this, is it's the way I would go, and it will work. It's less complex than what you'll end up doing if you try to segment on CO.

Another option is to call a webhook-compatible service as a lookup table. For just 8,832 entries you might as well read a text file, like a CSV you store in design studio or elsewhere. Or to be more database-like, store the mapping between zip and location in a Google Sheet and query the Sheet. Or something with a real db.