I would be grateful for some assitance with the following data model design query.......
I have the following structure
1) Fact Table 1 Sales 1 row per sale) : Contains details of sales with links to date , customer and product dimensions (amongst others in tradditional star schema model)
2) Fact Table 2 Campaigns: (1 row per customer per campaign sent) Contains details of marketing campaigns received by a customer with links to the customer dimension and a campaigns dimension sumarising the campaigns details (each campaign being split into diffferent treatments)
I would like to get the sales results (How many people were mailed, how many responded etc) for specific campaigns but am struggling to work out how I do this and what changes are needed to the design to allow me to achieve this. Having read this it seems to be called the chasm trap
For reference I can calculate the answer in SQL by pulling out who was mailed in each campaign and searching for the relevant sales in the sales table. I could import this into PowerBI and report of that but it would mean sales data existing in both Fact Tables and have a feeling that there is a better way?
Does anyone have any suggestions as to the best way to approach this?
Could you please share your sample data to me? You can upload your file to one drive and share the link here.