Modeling customer activity

Data Vault 2.0
1

Hi,
I am trying to understand what would be your recommendation for modeling the following object. There is a Salesforce custom object Customer_Activity. It has relationship to Campaign, Contact or Lead (current design keeps Lead and Contact as separate hubs), Opportunity. My first idea was to model this as a link between all the above entities. There are multiple challenges though:

  • There are about 10% of records that are not connected to any of the above objects. Typical cases are activities like: "visited web page’, ‘download white paper’
  • There are records linking to Lead, Contact or both.
  • Other objects are referencing Customer Activity - there is an object Campaign Member that links Lead or Contact to Campaign. In about 50% records it also links to Customer Activity.
  • To make it even worse, there are other tables that reference Campaign Member

I can see these 2 options for modeling Customer Activity:

  1. Customer Activity becomes a Hub - obviously wrong from DV perspective, as Customer Activity is not a business entity. However, it allows to create links to all the related entities and also allows to model Campaign Member as a link between Campaign, Contact/Lead and Customer Activity
  2. Customer Activity is a link (multiple links) - not sure what links would be needed to capture all the combinations of Customer Activity and Campaign Member. Not sure also how to model those Customer Activities that have no relation to any entity.
  3. Other?

Here is the ERD of the source:

image
image603×315 5.21 KB

2

Everything needs a business key, otherwise what are we tracking here?

Sounds like you need to consider Zero keys if the intended UoW is to include both

Customer activity does not sound like a business object, correct? It is a business event, activity sounds like it may be a look up to an activity table and therefore a reference table

What is a campaign member?

Never create hubs just so that you have something to join to

Look into how to use Zero keys effectively, I do provide some guidance in:

3

Thanks @patrickcuba, the Zero keys concept is definitely helpful and solves a lot.

Regarding the use case:
Customer Activity is a log of customer actions that happen on our external web, Marketo and some other customer facing applications. There is a Detail column that contains additional info that can be later parsed (business vault?). It is a sort of multi purpose / overloaded table and in some cases the FKs to Contact/Lead/Opportunity are not provided. Is there a design pattern in DV to model such object?

[quote="patrickcuba, post:2, topic:1581]

What is a campaign member?
[/quote]

Campaign member is a standard Salesforce object that represents the relationship between a Campaign and either a Lead or a Contact. In our instance of SF there is a custom column that references Customer activity as well.

[quote="patrickcuba, post:2, topic:1581]

Never create hubs just so that you have something to join to
[/quote]

How should I model the links then? There should be one link just for Customer Activity with a satellite. For Campaign member I would create another link and add a satellite for its attributes. What about the attributes coming from Customer Activity? Should they be another satellite over the same link? Something like below?

image
image578×649 8.05 KB

4

Likely a BV… fks are not keys for hub tables, almost always never are … you may need pre-staging to solve this with business keys.

Reference then?

You’re in the best position to decide the model of course, the desired DV model should dictate how you load the DV model and not the other way around.
RV - links should look close to what the business process is depicted, hubs should be business objects only and yes you could have more than one link-sat over a link (why not)

5

Yes, reference table makes sense here. I didn’t think of it as it is not a typical use case used in the Linstedt/Olschimke Data Vault book. Thanks!

6

That book is nearly 10 years old :wink: