Help with semantic model structure

[klev28]

Hi

I'm not a new user but I'm having trouble getting a specific model right. I'm doing marketing campaign analysis. I want to show the campaign results by campaign and segment, split by product and income type: subscriptions vs. one-off. We also have direct and indirect campaign results where indirect results are linked to the campaign but not an activity (the customer was not mailed) and direct results have both campaign and activity ids.

For the mailings data I have a campaign lookup and a table of combined activity. Results are from two sources: transactions (product income) and new product subscriptions. Note, some subscriptions have not [yet] got any associated transactions, and not all transactions are subscription income.

My basic model set up so far looks like this:

Relationships are colour coded: green line is relationship on Campaign ID, purple is on Activity ID. I've not added a direct relationship between Transactions and Subscriptions but they could be linked on Subscription ID in an alternative schema.

The data heirarchy / distribution is roughly:

Initially I tested out a simple schema with 3 direct relationships between Campaign and Activity / Transactions / Subscriptions but it was impossibe to split results down by activity segment.

The current schema works well for the direct campaign results but not the indirect (right outer of the centre venn diagrams) if including campaign info.

What can I do to improve the model to allow me to show all campaign results by segment, where indirect results have a blank (or default override) segment name? I considered splitting the 2 results data sources into direct and indirect tables each with a direct relationship to the campaign table. I'd then need to duplicate my results measures for direct and indirect and also create combined measures. But I think there would still be issues when trying to drill down to activity segment level..

I hope this makes sense. The diagrams here are a simplification. There are quite a number of extra activity and results fields that I want to be able to visualise the data split or filtered by.

Thanks for any help.

[v-tejrama · Accepted solution]

Hi @klev28 ,

The main challenge here isn't with the relationships themselves, but rather that the current model requires the Subscriptions table to serve as both a fact and a dimension, which creates conflicts. For income calculations, Subscription data needs to function as a fact table, while for scenarios where a subscription is linked to a campaign without activity, it needs to serve as a dimension. Using a single physical table for both roles leads to issues, such as indirect results disappearing when Activity-level filters are applied.

A clear solution is to separate these roles. Retain your existing fact table for subscription income, and create a separate copy of the Subscriptions table to use as a dimension. This dimension table should include only the necessary keys and attributes for segmentation and campaign mapping.

The Campaign table would then relate to this Subscription Dimension, while Activity and Transaction tables maintain their appropriate fact-level relationships.This structure ensures indirect results are retained, as they no longer rely on Activity. Segmentation can be applied consistently, and direct results continue to flow as before. There is no need to duplicate or maintain separate measures, as filter propagation is handled more predictably. This approach also supports all your reporting requirements and offers scalability for future enhancements by clearly separating descriptive and transactional logic.

Thank you.

[klev28]

Thank you for taking time to understand my requirements and outline what I need to re-work. It's not yet clear to me how to implement your solution, but I do now feel more confident that I know the general direction to head in.

Meanwhile I did manage to get some insights out of the data by adding the segment to the two results table (using lookup values). This at least allowed me to quantify the direct vs. indirect results by creating a transaction results visual split by segment where populated. And the same for subscriptions. 

[amitchandak]

@klev28 , My initial thought,  we need Subscriptions as both dimension and fact, so we need two copies. 

[klev28]

Thank you. It's reassuring to have 2 responses agreeing on this. Really appreciate your reply 🙂