Self-Join relationship

[NickMontagu]

Hey All,

For an Ecommerce client with a LOT of products we need to be able to easily manage the upsell/cross-sell related products/items within HubSpot.

I believe the answer to this question will be "You can't" but I am hoping for someone to offer a good work-around.

Q: Is it possible to create a 'self-join' relationship in product table?
Purpose: To replicate the upsell/cross-sell products from the ecommerce site.

Otherwise, could this relationship be achieved with HubDB for integration into Emails?

@Walter_Anderson or @Sean_Duane, you might have some ideas here.

[mcoley]

Hey @NickMontagu,
Sorry about that I was out on vacation. I just deployed the changes needed so you can reference rows in your own table now.

[NickMontagu]

@Matthew_Coley Welcome back from leave.

I can't find anything in the product API documentation about using the foreign ID column.

I did note this article about using HubDB to set this up.

designers.hubspot.com

How to join multiple HubDB tables

How to join multiple HubDB tables

For our use case we wanted to do this off the native HubSpot product table. Can you help point me in the right direction to the documentation for this?

Nick

[mcoley]

Nick,

I can expand the functionality of the foreign ID column to also be able to link to rows in the same table.

Would that satisfy your use case?

Thanks,
Matt

[NickMontagu]

Definitely worth testing, yes please! (And thank you!)

[NickMontagu]

@Matthew_Coley I will start testing this shortly, were you able to expand the functionality, or how will you let me know it's ready for testing?

Nick

[NickMontagu]

So this is when we relate a table (in this case products) to itself.

If we want to recommend items that go well together (upsell/cross sell) then how we really want to structure in data is:

If customers purchased ‘Product A’ we could recommend ‘Product B and Product C’ as related items they might be interested in because they bought Product A.

All the details for Product B and Product C are already listed in the Product table. We don’t need to create an additional table to store information about these. In fact we absolutely shouldn’t as this would be a duplication/redundancy in our data.

Instead what we can do is add a field to our Products table which stores the ID’s of ‘related Products’; so in this was a relationship (self-join) is created to say that [Related Product ID] - [Product ID].

Or the more ideal world is one join for [Cross sell ID] and another for [upsell ID]. These both live on the Product table and refer to Product ID’s.

I am happy to jump on a call and discuss this if I still haven’t explained it well.

[sduane]

Nick,

I'm not quite sure what self-join means here, sorry :x
Maybe you can provide an example of what functionality you're looking for?