Many to Many assistance or validation of approach

[jnickell]

I'm working with data from Dynamics CRM and for now trying to relate Contacts with the campaign they interacted with, not just their source campaign.  The way our instance is getting populated I believe my best recourse is to use the lead record's 'parentContactID' to relate the Contacts  to the Lead (1 to M).  The Campaigns (one) are related to the (many) Leads by the Lead's CampaignID.  This essentially makes the Lead Table the bridge table between Campaigns and Contacts.

I've concocted a measure that appears to give me the number I need but I'm would like your help in knowing two things. 

• Is this measure the appropriate way to identify number of contacts that interacted with a campaign
• Would it be better to have a separate bridge table (essentially a subset of columns from the Lead table) instead of relying on the Lead table itself.

I've below is a subset of CRM data that I'm using to work with. as well as a screenshot of the relationships using the Leads table as the bridge table. 

Appreciate your assistance!

My Measures:

Campaign Contacts = 
    VAR CampaignExistence = 
        COUNTROWS(
            FILTER(
                'Contacts_v2 Inquiry',
                COUNTROWS(RELATEDTABLE('Leads_v2 Inquiry'))
            )
        )
    
    RETURN
        CampaignExistence

Campaign Leads = 
    CALCULATE(
        COUNTROWS(
            'Leads_v2 Inquiry'
        )
    )

Contacts

contactId	Created By	Created On	Description	originatingLeadId
1	CRM Service	6/9/2024 0:02	Project Name: #1	4
2	CRM Service	6/3/2024 10:00	Project Name: #2
3	Former Employee	2/23/2018 10:44	Inquiry: Hello-  #3	2
4	CRM Service	6/3/2024 10:00	Project Name: #4
5	Former Employee	11/30/2017 9:22

Campaigns

campaignid	Name
1	Campaign 1
2	Campaign 2
3	Campaign 3
4	Campaign 4
5	Campaign 5
6	Campaign 6
7	Campaign 7
8	Campaign 8
9	Campaign 9
10	Campaign 10
11	Campaign 11
12	Campaign 12
13	Campaign 13
14	Campaign 14
15	Campaign 15
16	Campaign 16
17	Campaign 17
18	Campaign 18
19	Campaign 19

campaignId	Description	Full Name	leadId	parentAccountId	parentContactId
	Project Name: #1	Lead 1	1
	Inquiry: Hello- #1	Lead 2	2	1	3
18	Project Name:#2	Lead 3	3		4
18	Project Name: #3	Lead 4	4		1
	# 4	Lead 5	5
18	Project Name: #5	Lead 1	6		2
	#6	Lead 6	7
	#7	Lead 7	8
18	Project Name: #8	Lead 1	9		2
	Hi, #9	Lead 8	10

[Anonymous · Accepted solution]

Hi @jnickell 

1. Is this measure the appropriate way to identify number of contacts that interacted with a campaign

Your measurement looks correct because it tries to count the number of contacts associated with the campaign through the Leads table.

Of course, it is important to ensure that internal functions accurately reflect the relationship between connections and activities through cues.

If the relationships in the model are set up correctly, this metric should work as expected.

2. Would it be better to have a separate bridge table (essentially a subset of columns from the Lead table) instead of relying on the Lead table itself.

Using the lead table as a bridge table is a common approach in cases where there is a many-to-many relationship between two entities.

However, creating a separate bridge table may be helpful in the following cases:

You want to simplify the model by isolating many-to-many relationship logic.

The "Leads" table contains a large amount of data unrelated to the relationship between contacts and marketing activities.

Your goal is to improve the clarity or manageability of your model.

Given these factors, if the Lead table works well in connecting contacts and marketing activities without introducing unnecessary complexity or performance issues, then it makes sense to continue using it.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jnickell]

Thanks for the feedback @Anonymous