topic Re: External Sales Rep Lookup by zip code in CMS Development

External Sales Rep Lookup by zip code

ParkerShort — Fri, 30 Jun 2017 16:13:53 GMT

Hey everyone,

Not sure where this question truly belongs, so I'll start here. I'd like to be able to have someone fill out a HubSpot form with their zip code, and then match that to a HubSpot owner. I know a workflow can do this, but in this instance I"m trying to match against 200+ owners, so it's a lot better if I can match against a spreadsheet or database.

So ideally, the flow would be

- fill out HS form (with zip code)

- some sort of lookup to find their HS owner/territory rep

- (optional) Set the HS Owner

- on the next page show the information of who their rep is

Any thoughts on the best way to accomplish this? I've played around with sheetrock before and been able to do similar stuff. Not sure if you can use HubDB to personalize at all or set properties within HubSpot.

Thanks.

Re: External Sales Rep Lookup by zip code

ndwilliams3 — Fri, 30 Jun 2017 16:50:31 GMT

I was looking to do assignment based on zip code as well. Inputting the zip codes in the list or workflow criteria can be super labor intensive, so I ruled that out. Also, the browser kept looking up when there were to many zip codes.

I am looking to use Zapier to do the heavy lifting. While I haven't got it setup just yet, my plan is to use Google sheets to store the zipcodes/owner data and Zapier to set the owner. Zapier would look up the contact's zipcode in the Google sheet, return the Hubspot Owner ID for that zipcode and then set the Owner in Hubspot. Below is a screenshot of the Zapier workflow I'm working on.

The Hubspot list criteria is:

Hubspot Owner is unknown and Zipcode is known.

The Google sheet is:

Zipcode	Hubspot Owner Name	Hubspot Owner ID
12345	John Doe	0123456789

Re: External Sales Rep Lookup by zip code

ParkerShort — Wed, 05 Jul 2017 22:08:54 GMT

Good thinking. I think that will work for just setting the HubSpot owner. But I'm also trying to show (on page) the HubSpot owner's information. I don't think I'll be able to use HubSpot's personalization on the next page (they recommend against it).

Re: External Sales Rep Lookup by zip code

ndwilliams3 — Wed, 05 Jul 2017 22:34:07 GMT

Yep, using smart content wouldn't work as the list processing doesn't happen before the thank you page loads. I've used HUBL with contact properties to personalize thank you pages and that works as it's not dependent on lists. However, it only works for properties that are collected during the form submission or already set prior to the form submission.

Re: External Sales Rep Lookup by zip code

ParkerShort — Thu, 06 Jul 2017 13:55:21 GMT

Interesting. So the tokens should be available for use automatically? So, I might be able to use the zip code to do an external lookup and then submit that information via a query builder url. Then it would be immediately visible on the next page.

Re: External Sales Rep Lookup by zip code

Joe_Finucan — Thu, 05 Oct 2017 18:48:19 GMT

I am looking to do something similar. I want to look up remote data in a table using zip code and send that data back to be assigned to the contacts properties. So for instance a contact submits information including their zip code. I want to look up their associated branch and which region the branch exists in.

Were you able to get Zapier to work with what you were trying? What costs are associated with Zapier for this kind of thing? I was hoping to use HubDB, but perhaps Google Sheets is a better way.

Re: External Sales Rep Lookup by zip code

GregEllis — Thu, 11 Nov 2021 19:46:39 GMT

Just use this HubSpot extension https://zipcodeinfo.org/

For zipcode lookup and address search with autocomplete.

Re: External Sales Rep Lookup by zip code

ScottHemmeter — Wed, 08 Feb 2023 17:51:50 GMT

Hi, I realize this question is really really old, but I wanted to inform you that we have a HubSpot-specific solution for you. We had this same challenge and decided to build a HubSpot app for it. It's called VLOOKUP for Workflow and it just launched on the Marketplace today. It allows you to reference a Google Sheet or upload your own CSV data and use that source as a lookup table in HubSpot. You can then use the result downstream in your workflow. It's simple, scalable, and affordable.

Your Zapier solution will work fine as well, but you may have found its reliability questionable or, if you have large volumes, may have found it to get expensive due to transaction limits with Zapier. If so, check out VLOOKUP.