Re: Intermediary Object Question

michaelstancil
Level 3

Hey all,

 

 

 

I'm trying to figure out a way to keep a dataset that I need to access, but not all the values may be linked to anyone in my database...yet. The data set I have is keyed/linked off a zip code. There are then five interesting fields about that zip code I'd like to store. However, I'm trying to keep as many zip codes as possible to reduce the work in the future, which means some zip codes won't be tied to anyone. When I've tried to upload to my current custom object, only the zips that match a zip are updated.

 

From my research, I think an intermediary/bridge/edge relationship will solve this. It seems as though I should create a new edge object that has all of the zips + five interesting fields, and then a bridge object that links to the lead zip code, and then the edge object zip code. However, the part that is confusing me is now I don't know how to access those five interesting fields?

 

Is the answer simply that I need to create two edge custom objects that are zip + two fields and zip + three fields (given the three link max) and then link/dedude on all of those?

Tags (1)
14 REPLIES 14
Darshil_Shah1
Level 10 - Community Advisor + Adobe Champion

I am wondering if you need a link field in true sense here? Would it not be possible to create a custom object without a link field, store all the Zip code and interesting data about it in it - you could query the CO data based off a matching zip code search in the velocity (assuming you're not gonna trigger any flows as records are added to this CO since its a standalone zip + info database)? Zips could easily be duplicated/repeated across multiple leads in your DB, in that case the corresponding CO record would not be linked to all the people with the same zip code in case you're using the zip as the link field, if you change the link field, to say email/lead-id you would have to create separate zip CO records for each person with same zip.

Katja_Keesom
Level 10 - Community Advisor + Adobe Champion

It would not really be possible to use the zipcode as a link field anyway, as that would mean the CO record is only going to matched with one record in your lead database with that zipcode (usually the record that was last updated).

Depending on the set up of the integration, I would actually opt to add these interesting fields directly to the lead / company object.

Darshil_Shah1
Level 10 - Community Advisor + Adobe Champion

Agreed, that's what I mentioned about using zip as the link field!

However, creating the custom fields (which is definitely one of the possible solution to manage and setup this)  would require the same interesting data to be updated on all the leads having same zip codes and this would have to repeated for all the zip values - and this exercise would in-turn have to be repeated each time interesting data about any of the zip changes. Having a standalone CO would comparatively be easier to manage and update data corresponding to the zip. But if the use-case requires this data being updated from the Marketo flows too along with integration/API calls it would make more sense to create the fields and do the heavy lifting of updating/managing person records with the data. Again, all down to the actual use-case! 🙂

michaelstancil
Level 3

@Jo_Pitts1 CRM is Salesforce, but given limitations and speed restrictions on that team it might as well be CRM agnostic. The use case is that I want to send these interesting fields within the email content to users who have this zip code on their record. 

 

@Katja_Keesom My concern with setting it up on a per lead record is that many records will have the same zip code, so I want to cut down on the duplication.

 

@Darshil_Shah1 So I create a master CO that isn't linked to anything right now that is currently setup this way. Nothing is triggered from the CO, I just want to look for the zip code and then pull the five fields. I think what you're saying makes sense, I just don't know how to access it given that there's no link on it, it doesn't show up in the side field within my email scripts.

Darshil_Shah1
Level 10 - Community Advisor + Adobe Champion

Is your CO approved? AFAIK, You cannot not see the CO in the email script tree until it's approved.

michaelstancil
Level 3

It is approved, but I'm assuming that it's not showing because there are no linked fields?

SanfordWhiteman
Level 10 - Community Moderator

It is approved, but I'm assuming that it's not showing because there are no linked fields?

Correct. You can’t access unlinked objects from Velocity.

 

Moreover, you couldn’t access the details of the edge objects from Velocity, even if you did have the junction objects in place.

 

Now here’s a question: how many bytes of data do you anticipate storing across all the ZIP codes? And can you give an example of the 5 data points so we can see how the data might be most efficiently stored?

michaelstancil
Level 3

For the sake of simplicity, let's take it to four items:

1. Zip (five digits)

2. Cost One: (six digits)

3. Cost Two: (six digits)

4. Count: (two digits)

The fifth item was a percent change between #2 and #3, but I can do that calculation elsewhere.

 

As for total size, a CSV with ~6,000 records (of the four above) is 200KB, so each row of four items is ~33KB. 

SanfordWhiteman
Level 10 - Community Moderator

33 bytes, I think you mean.

 

Unfortunately that’s going to go over the limit of what I was envisioning, which was using a Velocity token as a lookup table. Unless — do the costs vary widely, or are there a fixed number of possible costs?

michaelstancil
Level 3

They are unique to that zip code, and update monthly, so they vary quite a bit.

SanfordWhiteman
Level 10 - Community Moderator

Hmm. That’s gonna stay over the Velocity limit of 100K, then.

 

I know it’s annoying to do, but if this is a 1-to-1 mapping from (current) Zip to data, you might as well use flat Lead fields. You can bring them in using FlowBoost (knowing you’re already a user).

michaelstancil
Level 3

Got it.

 

The other way around it would be uploading just the zips that match back to current records and using that as a link to then carry over the interesting fields, yes? My concern is relying too heavily on webhooks for information that would be updated monthly (the interesting items + new zips & subsequent items) en masse, in addition to being sent to large numbers more frequently.

SanfordWhiteman
Level 10 - Community Moderator

Yeah, you probably don’t want to run an end-of-month reconciliation for more than ~100,000 people via webhook.

 

So on the one hand it seems like you should use a Custom Object, where you at least you have a bulk upsert option. Still, the need to duplicate + flatten the CO instead of using 2 dimensions (junction + edge) is suboptimal.  You might as well do a bulk upsert into lead fields IMO.

Jo_Pitts1
Level 10 - Community Advisor

@michaelstancil ,

what's the use case for this.

Also, what CRM system are you syncing with?

Cheers