Move all activities data to vendor custom object

[AnthonyRollins]

Background

We have upgraded Hubspot to Sales Enterprise to access the custom object feature in Hubspot. This allows us to create objects different from the standard ones, contacts, companies, tickets, deals, etc. Vendors are the first custom object we want to implement for Hubspot.

The CS team has begun determining the required properties for the new vendor custom object. Because Hubspot already has data on vendors associated with the companies, we need to pull out all existing activity data from the companies with company_type = vendors and add them to the new vendor custom object.

As a second step of this process, we want to archive all the old Hubspot companies with company_type = vendors

We are using a sandbox in Hubpsot to test what properties should be used for the new custom object. We can test different things, but no data can be transferred to the live Cal-ITP Hubspot.

Example

Using the Engagement API detail below, we need all activity data (calls, emails, notes, etc.)Company Object Example A moved to Custom object: Vendor Example B. This is an example of a vendor that we need activity data moved over, but we will need to do this with all vendors in our Company object database.

Hubspot Documentation

• Associations API
• Companies API
• Custom Objects API
• Engagement API
• Engagement Notes
• Engagement Calls
• Engagement Tasks
• Engagement Meetings
• Engagement Email

Acceptance criteria

• [ ] Forward associations for each engagement type are created for every vendor
• [ ] Reverse associations for each engagement type are created for every vendor
• [ ] Every company with company_type == vendor is archived

[AnthonyRollins]

@thekaveman Vendor data has been added to the new CRM property vendors. The new object uses the domain property as the unique identifier. We can also use the Record ID associated with vendor objects and the company object. More property and association data is still needed, but the records are ready for activity data.

CRM Vendor object data

[thekaveman]

@AnthonyRollins I'm getting into the data and noticed that a few vendors (the custom object kind) are missing their domain property (see list below).

For the API calls, I need to use the object IDs (I can't use domain)... BUT I still need domain to be able to match the old company vendor to the new custom object vendor.

{
  "id": "12242424647",
  "properties.domain": null,
  "properties.hs_object_id": "12242424647",
  "properties.vendor_name": "Kuba"
},
{
  "id": "12242424661",
  "properties.domain": null,
  "properties.hs_object_id": "12242424661",
  "properties.vendor_name": "FiServ"
},
{
  "id": "12242424662",
  "properties.domain": null,
  "properties.hs_object_id": "12242424662",
  "properties.vendor_name": "SC Soft"
},
{
  "id": "12242424663",
  "properties.domain": null,
  "properties.hs_object_id": "12242424663",
  "properties.vendor_name": "EngHouse"
},
{
  "id": "12242455355",
  "properties.domain": null,
  "properties.hs_object_id": "12242455355",
  "properties.vendor_name": "Elavon"
},

[AnthonyRollins]

@thekaveman I believe the vendors without a domain name were there for testing purposes. We can delete all of the files without a domain name, as the import created duplicates for those vendors. For example, here's the record id: 12820776744 for the real Enghouse.

Did you want me to delete those in the CRM, or do you want to remove them from your import? I confirmed that all vendors without a domain name can be deleted.

[thekaveman]

Thanks @AnthonyRollins -- I can handle on the import side with this understanding!

[thekaveman]

@AnthonyRollins more questions for you about the data...

We have a few company/vendors that mismatch on their domain:

Hayden AI

Company

{
  "id":"8566977114",
  "properties.company_type":"Vendor",
  "properties.domain":"hayden.ai",
  "properties.hs_object_id":"8566977114",
  "properties.name":"Hayden AI",
}

Vendor

{
  "id":"12867126054",
  "properties.domain":"http://hayden.ai",
  "properties.hs_object_id":"12867126054",
  "properties.vendor_name":"Hayden AI"
}

Kontron

Company

{
  "id":"8566857380",
  "properties.company_type":"Vendor",
  "properties.domain":"kontron.com",
  "properties.hs_object_id":"8566857380",
  "properties.name":"Kontron"
}

Vendor

{
  "id":"12867580998",
  "properties.domain":"https://kontron.com",
  "properties.hs_object_id":"12867580998",
  "properties.vendor_name":"Kontron"
}

RideCo

Company

{
  "id":"16836454529",
  "properties.company_type":"Vendor",
  "properties.domain":"rideco.com",
  "properties.hs_object_id":"16836454529",
  "properties.name":"RideCo"
}

Vendor

{
  "id":"12867588923",
  "properties.domain":"https://rideco.com",
  "properties.hs_object_id":"12867588923",
  "properties.vendor_name":"RideCo"
}

Peak Transit

Company

{
  "id":"17824709269",
  "properties.company_type":"Vendor",
  "properties.domain":"peaktransit.com",
  "properties.hs_object_id":"17824709269",
  "properties.name":"Peak Transit"
}

Vendor

{
  "id":"12878445744",
  "properties.domain":"https://peaktransit.com",
  "properties.hs_object_id":"12878445744",
  "properties.vendor_name":"Peak Transit"
}

[thekaveman]

@AnthonyRollins We also have quite a few (66 :flushed:) company vendor objects with activities that do not have any corresponding custom vendor object. I tried matching with a custom vendor object on domain and company/vendor name and these ones failed on one or the other. Not sure how you want to handle this larger list? Or are these ones to ignore as well?

We can meet about this if you like, or wait until @laneymangan is back.

properties.domain	properties.name	properties.hs_object_id	id	properties.company_type
a-zbus.com	A-Z Bus Sales	17878597284	17878597284	Vendor
a1altfuels.com	A-1 Alternative Fuel Systems	17878794595	17878794595	Vendor
alexander-dennis.com	NFI Group (New Flyer, MCI, ARBOC, Alexander Dennis)	18099214355	18099214355	Vendor
allisontransmission.com	Allison Transmission, Inc.	18099180093	18099180093	Vendor
b2gnow.com	B2Gnow	17877080456	17877080456	Vendor
bofa.com	Bank of America	18740345404	18740345404	Vendor
braunability.com	BraunAbility	17878671699	17878671699	Vendor
cdce.com	CDCE, Inc.	18099061357	18099061357	Vendor
cleverdevices.com	Clever Devices	8499830651	8499830651	Vendor
connectedsignals.com	Connect Signals, Inc.	18099168817	18099168817	Vendor
constantcontact.com	Constant Contact	8619542104	8619542104	Vendor
cts-software.com	TripMaster by CTS Software	17877083437	17877083437	Vendor
daveycoach.com	Davey Coach Sales	17878757146	17878757146	Vendor
driverge.com	Driverge Vehicle Innovations	17877123158	17877123158	Vendor
ecolane.com	Ecolane USA	17825017379	17825017379	Vendor
ehi.com	Commute with Enterprise	18099027293	18099027293	Vendor
eldorado-ca.com	El Dorado National - California, Inc.	17877181321	17877181321	Vendor
etatransit.com	ETA Transit Systems	8499871339	8499871339	Vendor
fentonmobility.com	Fenton Mobility / AbiliTrax	17877053274	17877053274	Vendor
fleetwatch.com	S & A Systems, Inc.	18099090361	18099090361	Vendor
forvia.com	Forvia	16793581336	16793581336	Vendor
gillig.com	GILLIG	17877185886	17877185886	Vendor
gobiz.ca.gov	GoBiz	17878630751	17878630751	Vendor
hanoverdisplays.com	Hanover Displays	17878800164	17878800164	Vendor
hansecom.com	INIT Innovations in Transportation, Inc.	18098932120	18098932120	Vendor
hitachirail.com	Hitachi Rail	8567092396	8567092396	Vendor
hntb.com	HNTB	8566924639	8566924639	Vendor
initusa.com	INIT Innovations in Transportation, Inc.	8566961422	8566961422	Vendor
interclean.com	Interclean	18062734482	18062734482	Vendor
intermotive.net	Intermotive Vehicle Controls	17824819055	17824819055	Vendor
kentkart.com	Kent Kart	19056892683	19056892683	Vendor
keolisna.com	Keolis	17878800163	17878800163	Vendor
masabi.com	Masabi LLC	8566942261	8566942261	Vendor
maymobility.com	May Mobility	18099180090	18099180090	Vendor
morryde.com	MORryde International, Inc.	17875986487	17875986487	Vendor
multipass.co.uk	MV Transportation, Inc	8566977118	8566977118	Vendor
nanovdisplay.com	Nanov Display Inc.	18336106098	18336106098	Vendor
newflyer.com	New Flyer | MCI	17877115504	17877115504	Vendor
nfi.parts	NFI Group (New Flyer, MCI, ARBOC, Alexander Dennis)	18098973301	18098973301	Vendor
oxinst.com	Oxford Instruments	19073737221	19073737221	Vendor
papercast.com	PaperCast	18100882252	18100882252	Vendor
proterra.com	Proterra, Inc.	17876970796	17876970796	Vendor
qstraint.com	Q'Straint/Sure-Lok	18099130252	18099130252	Vendor
quarticsolutions.com	Quartic	19026212098	19026212098	Vendor
radioeng.com	REI	17878752290	17878752290	Vendor
ridereport.com	Ride Report	15598393245	15598393245	Vendor
rideshark.com	Rideshark	8566873972	8566873972	Vendor
ridewithvia.com	Via Transportation	10194901066	10194901066	Vendor
robussales.com	Sunset Vans	17841413985	17841413985	Vendor
safetyvision.com	Safety Vision, LLC	18099134324	18099134324	Vendor
scheidt-bachmann-usa.com	Scheidt & Bachmann USA, Inc.	8567010738	8567010738	Vendor
seifertgraphics.com	Seifert Graphics, Inc.	18098951351	18098951351	Vendor
sure-lok.com	Q'Straint/Sure-Lok	17877050450	17877050450	Vendor
taiamerica.com	Toyota Tsusho America, Inc.	17878814851	17878814851	Vendor
tolarmfg.com	Tolar Manufacturing Company	17825080886	17825080886	Vendor
transairmfg.com	Trans/Air Manufacturing	17878620565	17878620565	Vendor
transdev.com	Transdev	9535452419	9535452419	Vendor
transitmarketinggroup.com	Transit Marketing Group	17877181322	17877181322	Vendor
transportationpublishing.com	Transportation Publishing	19059584270	19059584270	Vendor
transtrack.net	TransTrack Systems Group	17587610208	17587610208	Vendor
turbo-images.com	Turbo Images	18099191878	18099191878	Vendor
urbansolarcorp.com	Urban Solar	17876881788	17876881788	Vendor
us-energy.com	U.S. Energy	17878789984	17878789984	Vendor
velocia.io	Velocia Inc.	18082328913	18082328913	Vendor
ventek-intl.com	VenTek International	18335970688	18335970688	Vendor
vontas.com	Vontas	18096889722	18096889722	Vendor

[laneymangan]

Weird! One thing I'm noticing off the bat is that quite a few of these have odd characters in the name--I did a quick search for the first 5 on the list and I'm seeing a mismatch in the non-alphanumeric characters in the vendor names, and reordered words. For example:

A-Z Bus Sales is A-Z Bus Sales; Inc. A-1 Alternative Fuel Systems is Alternative A1 Fuel Systems NFI Group (New Flyer, MCI, ARBOC, Alexander Dennis) is NFI Group (New Flyer; MCI; ARBOC; Alexander Dennis)

I may have to just go through and manually update these mismatches, wouldn't take me too long.

[laneymangan]

@thekaveman ok they should be fixed now. Many of them just had slight variations in the name. Some were simply not created (my assumption is they were added after I originally exported the data or I somehow missed them) Let me know how it's looking now and if there are any lingering issues!

[thekaveman]

@laneymangan the data looks much better! We are down to 8 vendor company objects that I couldn't match to vendor custom objects:

properties.domain	properties.name	properties.hs_object_id	id	properties.company_type
airandlube.com	Interclean	18098977159	18098977159	Vendor
constantcontact.com	Constant Contact	8619542104	8619542104	Vendor
cts-software.com	TripMaster by CTS Software	17877083437	17877083437	Vendor
fehrandpeers.com	Fenton Mobility/AbiliTrax	8566757227	8566757227	Vendor
firstgroup.com	Transdev	8566757229	8566757229	Vendor
lgs-group.com	InterMotive Vehicle Controls	17877030239	17877030239	Vendor
nfi.parts	NFI Group (New Flyer, MCI, ARBOC, Alexander Dennis)	18098973301	18098973301	Vendor
transitmarketinggroup.com	Transit Marketing Group	17877181322	17877181322	Vendor

[laneymangan]

Did some more clean up! Let me know if you find any more. Thanks @thekaveman!

[thekaveman]

@laneymangan it's weird, there are now a few more but they are different from the most recent ones above!

properties.domain	properties.name	properties.hs_object_id	id	properties.company_type
ehi.com	Commute with Enterprise	18099027293	18099027293	Vendor
enderacorp.com	Endera	17877036826	17877036826	Vendor
fentonmobility.com	Fenton Mobility / AbiliTrax	17877053274	17877053274	Vendor

[laneymangan]

@thekaveman Fenton Mobility should be good to go now

Is it possible to skip the import for the other two? ehi.com Commute with Enterprise 18099027293 18099027293 Vendor enderacorp.com Endera 17877036826 17877036826 Vendor

[thekaveman]

@laneymangan do we want to skip them because they aren't real vendors, or something else? I can hardcode them in to skip, but it would probably be better to e.g. mark their company records as archived if we don't want to work with them as vendor objects.

[laneymangan]

@thekaveman It looks like they were both duplicates, their records show Adam merged them last week (which must be why they were new errors). Both company records seem to have no or very minimal activity history so I was thinking it would be easiest to skip and I can manually log any activity.

But I just realized that for some reason the two domains didn't get merged (I'll walk Adam through the merging process) so I added the domains (ehi.com and enderacorp.com) to their corresponding Vendor records...so maybe it will work now?

[thekaveman]

@laneymangan

...their records show Adam merged them last week (which must be why they were new errors)

But I just realized that for some reason the two domains didn't get merged (I'll walk Adam through the merging process) so I added the domains (ehi.com and enderacorp.com) to their corresponding Vendor records

I think this is along the lines of what I was asking about at last week's check-in i.e. are there still changes happening to the vendor company objects, and do we have all the corresponding vendor custom objects created.

The easiest thing from this import perspective is to process all vendor company objects that have activity, and treat them all the same. It is actually more complicated to exclude certain vendor company objects, and I'd rather not miss any activities and have to do this again (but then, only including certain vendor company objects -- a further complication).

So this is why I'd like to nail down when we think there will be stability in the vendor company objects (i.e. no more merges, additions) and ensure we have a matching vendor custom object for each. I think we're almost there once these 2 sets of duplicates have their domains fixed.

Fenton Mobility should be good to go now

Confirmed :+1:

[laneymangan]

@thekaveman Ah yes, I should have checked with Adam if he would be making any changes in there. From my end, there won't be any changes (other than to fix this issue) so I'll just ask him to hold off until everything's complete.

As it stands, Commute with Enterprise and Endera both have two domains listed (Commute with Enterprise: ehi.com and erac.com and Endera: emsyscon.com, enderacorp.com) Will this allow the transfer to match up? If not, I could merge them from the Company side to match the current Vendor records OR create new Vendor records for each with only the domains you listed above (that we can later merge AFTER the transfer)?

[laneymangan]

(just messaged the team to ensure no more changes are made until we give the go-ahead)

[thekaveman]

@laneymangan

I could merge them from the Company side to match the current Vendor records OR create new Vendor records for each with only the domains you listed above

I think this is totally up to you and how you want it to work! Either we can have 2 company + 2 vendor records with different domains, and I'll transfer all activities to the respective vendors; or if you merged the companies and had only 1 vendor record with a matching domain (whichever one it was), it would get all the activities (assuming the company merge also merges activities?)

[laneymangan]

@thekaveman Ok thanks! I went with the option of having 2 company + 2 vendor records. (This felt safer to me for some reason. I'll look closer and merge these records after the transfer is done) Let me know how things are looking now...fingers crossed!

[thekaveman]

@laneymangan confirmed! I can now match all vendor companies and vendor custom objects on domain!!

[thekaveman]

@AnthonyRollins @laneymangan OK I think I have this all working to run the full process.

I did a few test runs, against Littlepay's vendor object.

Here are 2 emails that the process associated:

(I'm not sure where the first one in the list came from, it was already there?)

You can see the reverse association on each email as well:

And here's an example of a note that the process associated (and the reverse):

So I think this is ready to run all the way, and just wanted to let you know / get your approval.

And just for fun, here's the total number of associations it will create:

vendors --> emails: 868
vendors --> meetings: 138
vendors --> notes: 27
vendors --> tasks: 8
emails --> vendors: 868
meetings --> vendors: 138
notes --> vendors: 27
tasks --> vendors: 8
total: 2082

[AnthonyRollins]

@thekaveman Looks good to me.

[laneymangan]

@thekaveman amazing, thank you!!

[thekaveman]

Just confirming @AnthonyRollins @laneymangan you also want me to archive all of the old vendor company objects after the activity transfer, correct?

[thekaveman]

ping @AnthonyRollins @laneymangan