Hi,
I have the following entities in my source system (simplified for the forum):
Leads
| lead_id | created | updated | age | gender | sum_assured | lead_status |
| 12345 | 2021-01-01 01:00:00 | 2021-01-04 14:23:12 | 45 | M | 100,000 | 4 - Sold |
Note: lead_status gets updated during the sales process from;
1- New > 2 - Contacted
2 - Contacted > 3 - Quoted
3- Quoted > 4 - Sold
Calls
| call_id | lead_id | created | duration_seconds |
| 00001 | 12345 | 2021-01-01 09:32:09 | 0 |
| 00002 | 12345 | 2021-01-01 10:49:12 | 0 |
| 00003 | 12345 | 2021-01-02 14:21:56 | 0 |
| 00004 | 12345 | 2021-01-03 16:45:56 | 45 |
| 00005 | 12345 | 2021-01-04 14:00:03 | 967 |
Tasks
| task_id | lead_id | created | task_type |
| 00001 | 12345 | 2021-01-03 16:47:09 | Call Back Scheduled |
The current plan for the data model looks like this, in order to report on effort and time taken to get a sale
DimDate - Standard
DimTime - Standard
DimAgeGender - Agebands by Gender
DimSumAssured - Sum Assured Bands
FactCalls - Transaction Fact Table
FactTasks - Transaction Fact Table
FactLeadSalesProcess - Accumulating Fact Table
- To contain a date column for each lead status date (new_date, contacted_date, etc)
- To contain columns timediff in days/hours between status dates
- To contain dax calculated aggregates from (FactCalls & FactTasks) for this like calls/duration to contact, calls/duration to quote, calls/duration to sale, number of task etc
Is this a sound approach or should i be looking at Calculate Table to aggregate data from the transactional fact tables, i'm a bit confused any help is appreciated.
Best,
yokunade
