Data Modeling - Customers

ANM_97 · ‎09-16-2024

Hi,

Hello,

I have the DIM_CUSTOMERS table, which is populated based on the CURRNO column that represents the customer's version. When an attribute in the source application changes, a new CURRNO appears, and a new DIM_CUSTOMER_ID key is inserted with the date_from (modification date) and date_to as null.

The current request requires bringing attributes to the customer level that may change more rapidly than the customer's version. For example, whether or not they have marketing consent.

How can I model the data so that I can see the complete picture of the customer on a specific day?

Example: Customer 200142025 has 59 versions, the last version is valid from 31.05.2024 to the present. However, during this period, they changed their marketing consent from YES to NO on 15.08.2024.

How can I model the data so that I can see the complete and correct picture of the customer on any given date?

DIM_CUSTOMERS:

DIM_CUSTOMER_ID	CURRNO	CUSTOMER_ID	DATE_FROM	DATE_TO
13879786	1	200142025	3/7/17 17:29	3/8/17 0:10
13879797	2	200142025	3/8/17 0:10	4/7/17 3:33
13879808	3	200142025	4/7/17 3:33	4/27/17 13:00
13879812	4	200142025	4/27/17 13:00	9/21/17 12:53
13879813	5	200142025	9/21/17 12:53	10/4/17 10:26
13879814	6	200142025	10/4/17 10:26	10/4/17 10:26
13879815	7	200142025	10/4/17 10:26	2/27/18 10:52
13879816	8	200142025	2/27/18 10:52	2/28/18 8:18
13879817	9	200142025	2/28/18 8:18	3/7/18 18:27
13879787	10	200142025	3/7/18 18:27	5/26/18 12:45
13879788	11	200142025	5/26/18 12:45	7/26/18 16:23
13879789	12	200142025	7/26/18 16:23	7/26/18 16:24
13879790	13	200142025	7/26/18 16:24	7/26/18 17:05
13879791	14	200142025	7/26/18 17:05	7/27/18 17:19
13879792	15	200142025	7/27/18 17:19	8/2/18 18:34
13879793	16	200142025	8/2/18 18:34	8/6/18 10:18
13879794	17	200142025	8/6/18 10:18	1/11/19 16:05
13879795	18	200142025	1/11/19 16:05	1/15/19 17:48
13879796	19	200142025	1/15/19 17:48	3/2/19 12:41
13879798	20	200142025	3/2/19 12:41	4/12/19 7:28
13879799	21	200142025	4/12/19 7:28	10/1/19 17:38
13879800	22	200142025	10/1/19 17:38	10/2/19 10:48
13879801	23	200142025	10/2/19 10:48	11/6/19 9:37
13879802	24	200142025	11/6/19 9:37	2/28/20 1:58
13879803	25	200142025	2/28/20 1:58	3/31/20 1:50
13879804	26	200142025	3/31/20 1:50	5/29/20 1:39
13879805	27	200142025	5/29/20 1:39	6/2/20 15:22
13879806	28	200142025	6/2/20 15:22	7/31/20 1:36
13879807	29	200142025	7/31/20 1:36	8/8/20 15:02
13879809	30	200142025	8/8/20 15:02	9/30/20 2:11
13879810	31	200142025	9/30/20 2:11	10/3/20 17:05
13879811	32	200142025	10/3/20 17:05	12/18/20 8:46
184578	33	200142025	12/18/20 8:46	4/29/21 18:19
15945479	34	200142025	4/29/21 18:19	4/29/21 18:19
15945480	35	200142025	4/29/21 18:19	5/4/21 8:54
15952414	36	200142025	5/4/21 8:54	5/18/21 10:56
16013087	37	200142025	5/18/21 10:56	7/30/21 2:10
16368237	38	200142025	7/30/21 2:10	8/31/21 2:25
16517819	39	200142025	8/31/21 2:25	9/30/21 2:48
16784554	40	200142025	9/30/21 2:48	4/4/22 12:41
18108380	41	200142025	4/4/22 12:41	5/2/22 17:31
18366546	42	200142025	5/2/22 17:31	5/2/22 17:32
18366547	43	200142025	5/2/22 17:32	5/31/22 5:09
18539691	44	200142025	5/31/22 5:09	6/29/22 19:02
18693112	45	200142025	6/29/22 19:02	6/30/22 3:39
18693113	46	200142025	6/30/22 3:39	7/29/22 2:35
18933767	47	200142025	7/29/22 2:35	8/20/22 11:18
19088490	48	200142025	8/20/22 11:18	9/1/22 18:19
19264568	49	200142025	9/1/22 18:19	11/29/22 2:38
19836221	50	200142025	11/29/22 2:38	3/31/23 3:11
20509038	51	200142025	3/31/23 3:11	4/28/23 4:15
20647689	52	200142025	4/28/23 4:15	6/30/23 3:27
20952134	53	200142025	6/30/23 3:27	8/31/23 4:06
21249742	54	200142025	8/31/23 4:06	10/10/23 14:07
21455908	55	200142025	10/10/23 14:07	10/31/23 4:54
21561608	56	200142025	10/31/23 4:54	2/29/24 5:46
22197406	57	200142025	2/29/24 5:46	3/29/24 4:39
22338088	58	200142025	3/29/24 4:39	5/31/24 4:45
22674290	59	200142025	5/31/24 4:45	NULL

Marketing agreement "YES" until 15.08.2024 and "NO" after.

Thank you! ~~

Anonymous · ‎09-16-2024

Hi,@ANM_97 .I am glad to help you.
According to your description, you are having some problems designing your data model.
Although I'm not very good at data model design, I'd like to help you out
I think you can create a separate table specifically to record dynamic attribute changes for customers.
like this:

ATTRIBUTE_ID	CUSTOMER_ID	ATTRIBUTE_NAME	ATTRIBUTE_VALUE	DATE_FROM	DATE_TO
1	200142025	Marketing Consent	YES	2024-05-31	2024-08-15
2	200142025	Marketing Consent	NO	2024-08-15	NULL

The table is associated with your existing table through the CUSTOMER_ID field to your existing table.
You can use join query (left outer join/right outer join) to fetch the user's consolidated data
Note that this is only my suggestion to you, you need to build the appropriate data model according to your actual situation.

I hope the following information can help you.

URL:

Dimensional Modeling Techniques - Kimball Group
A Guide to Data Modeling & The Different Types of Models | Twilio Segment

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ANM_97 · ‎09-17-2024

Hello! Thank you for the suggestion. I think I will also add a FCT table that will connect the date dimension to the customers dimension and mini dimension.

Like this: