Creating Proper Data Model

[Maverick999]

Hello,

I would like to know the right way to work with this data. I loaded 4 different tables, which all have a client ID field in common. What is the best data model to aim for and what types of data transformation should I do to achieve it?

Also, I am familiar with the concept of Master Calendar but how can I know if I should create a master calendar PER each date field ?

Thanks a lot.

[Maverick999]

Hello,

To confirm this data model is correct, I need to understand how to use it then.

I am not sure how to use it properly since I am bit confused about which ID to use (KeyLink, Client_ID etc.) depending on the requirement.

For example, if I want to make a graph to show which are the campaigns that led to the greatest number of clients making an order less than 4 days after being exposed to the campaign.

The date when a client has been exposed to a campaign being the column "date" in CRM table and the date when a client made an order being the column "date_cmd" in the "achat" table (in the excel).

Thanks a lot for your help in advance.

[Arthur_Fong · Accepted solution]

There you go:

Do take a look on how the logic is defined in the script.

Once the flag is created, you can sum it up and get the counts of successful campaigns.

This screenshot is took at the last tab called "Sheet".

Thanks and regards,

Arthur Fong

[Maverick999]

Thanks a lot Arthur! It works greatly, I marked it as solution.

I am not sure of the necessity of KeyLinkCLIENT, KeyLinkOrder and KeyLinkWEB but I will investigate deeper the script.

I would have much more questions regarding the proper data model to aim (like for example I thought we should always aim for a star schema model but here it does not seem the case), but I should save these for another post probably.

Thanks again, have a great day.