Reconcilation of Data for campaigns to understand how manu cutomer did not open email

[Anonymous]

Hello Friend, 

Request for your help, in Power bi desktop trying to build a reconciliation of information to understand which customer ID did not open an email sent to them. 

Some of the customer are contacted multiple time as there are different campaign delivery labels. what we need to accomplish is if any customer did not open even once an email when sent in different campaigns delivery.

Campaign Name	Campaign Delivery Label	Date	Label	Label-Open	Label-Sent	CustomerID	Status	Reconcilation	Unique_Id_Open	Unique_Id_Sent
Q2 Campaign	Sent-AR Launch Email 1B Winner	AR Launch Email 1B Winner	18e0ab4f80ec4187be6bbddbfc65646b	Failed	No		18e0ab4f80ec4187be6bbddbfc65646b-AR Launch Email 1B Winner
Q3 Campaign	Sent-AR Launch Echo Email 4		AR Launch Echo Email 4	0554f789536943aaa3107e4e5956bf6d	Sent	No		0554f789536943aaa3107e4e5956bf6d-AR Launch Echo Email 4
Q3 Campaign	Sent-AR Launch Echo Email 4		AR Launch Echo Email 4	0921d634c246464287617284286b4c89	Sent	No		0921d634c246464287617284286b4c89-AR Launch Echo Email 4
Q3 Campaign	Sent-AR Launch Echo Email 4		AR Launch Echo Email 4	0ffae51efda04ef4ba2335f168fc24a8	Sent	No		0ffae51efda04ef4ba2335f168fc24a8-AR Launch Echo Email 4
Q3 Campaign	Sent-AR Launch Echo Email 4		AR Launch Echo Email 4	1978498e6aa148f08239a4c406b22000	Sent	No		1978498e6aa148f08239a4c406b22000-AR Launch Echo Email 4
Q3 Campaign	Sent-AR Launch Echo Email 4	31/08/2022 01:31:06	Open	AR Launch Echo Email 4	AR Launch Echo Email 4	03325f09dc414c049d667acf64a7192e	Sent	Yes	03325f09dc414c049d667acf64a7192e-AR Launch Echo Email 4	03325f09dc414c049d667acf64a7192e-AR Launch Echo Email 4
Q3 Campaign	Sent-AR Launch Echo Email 4	31/08/2022 21:15:46	Open	AR Launch Echo Email 4	AR Launch Echo Email 4	0a4941bab97044e2b9c375f340f04d6e	Sent	Yes	0a4941bab97044e2b9c375f340f04d6e-AR Launch Echo Email 4	0a4941bab97044e2b9c375f340f04d6e-AR Launch Echo Email 4
Q3 Campaign	Sent-AR Launch Echo Email 4	########	Open	AR Launch Echo Email 4	AR Launch Echo Email 4	10e8c5f0684742bc8a7ac7290954009e	Sent	Yes	10e8c5f0684742bc8a7ac7290954009e-AR Launch Echo Email 4	10e8c5f0684742bc8a7ac7290954009e-AR Launch Echo Email 4
Q2 Campaign	Sent-AR Launch Email 1B Winner	AR Launch Email 1B Winner	18e0ab4f80ec4187be6bbddbfc65646b	Failed	No		18e0ab4f80ec4187be6bbddbfc65646b-AR Launch Email 1B Winner
Q3 Campaign	Sent-AR Launch Email 1B Winner	AR Launch Email 1B Winner	4615821101d040d6b7d199173804f6aa	Failed	No		4615821101d040d6b7d199173804f6aa-AR Launch Email 1B Winner
Q3 Campaign	Sent-AR Launch Email 1B Winner	AR Launch Email 1B Winner	7430edb85f494abda559c7e25a261290	Failed	No		7430edb85f494abda559c7e25a261290-AR Launch Email 1B Winner
Q3 Campaign	Sent-AR Launch Email 1B Winner	AR Launch Email 1B Winner	1039f3fbde17498b921338be21c71918	Failed	No		1039f3fbde17498b921338be21c71918-AR Launch Email 1B Winner
Q3 Campaign	Sent-AR Launch Email 1B Winner	AR Launch Email 1B Winner	65a16f5d-30f8-4cde-a915-2167e1f78744	Failed	No		65a16f5d-30f8-4cde-a915-2167e1f78744-AR Launch Email 1B Winner
Q2 Campaign	Sent-AR Launch Email 1B Winner	AR Launch Email 1B Winner	3d42bd28e722494dbea70b140b1671f7	Failed	No		3d42bd28e722494dbea70b140b1671f7-AR Launch Email 1B Winner
Q2 Campaign	Sent-AR Launch Email 1B Winner	########	Open	AR Launch Email 1B Winner	AR Launch Email 1B Winner	c788dd4e-18c8-4619-b17b-62ca8c54a124	Failed	Yes	c788dd4e-18c8-4619-b17b-62ca8c54a124-AR Launch Email 1B Winner	c788dd4e-18c8-4619-b17b-62ca8c54a124-AR Launch Email 1B Winner
Q2 Campaign	Sent-AR Launch Email 1B Winner	########	Start Strong Video Promotional	AR Launch Email 1B Winner	AR Launch Email 1B Winner	c788dd4e-18c8-4619-b17b-62ca8c54a124	Failed	Yes	c788dd4e-18c8-4619-b17b-62ca8c54a124-AR Launch Email 1B Winner	c788dd4e-18c8-4619-b17b-62ca8c54a124-AR Launch Email 1B Winner
Q2 Campaign	Sent-AR Launch Email 1B Winner	AR Launch Email 1B Winner	04927b21709340d294e41ae8d696b156	Failed	No		04927b21709340d294e41ae8d696b156-AR Launch Email 1B Winner
Q2 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	04c94419db05438c971f254078864992	Sent	Yes	04c94419db05438c971f254078864992-AR Launch Echo Email 2	04c94419db05438c971f254078864992-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	06c8de61ca4d4bb88ec8294c6f116c54	Sent	Yes	06c8de61ca4d4bb88ec8294c6f116c54-AR Launch Echo Email 2	06c8de61ca4d4bb88ec8294c6f116c54-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	04c94419db05438c971f254078864992	Sent	Yes	04c94419db05438c971f254078864992-AR Launch Echo Email 2	04c94419db05438c971f254078864992-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	017ccb1fc8784ebaaa9be09c9d6a4dae	Sent	Yes	017ccb1fc8784ebaaa9be09c9d6a4dae-AR Launch Echo Email 2	017ccb1fc8784ebaaa9be09c9d6a4dae-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	15/08/2022 16:58:40	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	05e0d5b4ad2149cc95bdea8e06914289	Sent	Yes	05e0d5b4ad2149cc95bdea8e06914289-AR Launch Echo Email 2	05e0d5b4ad2149cc95bdea8e06914289-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	20/05/2022 00:35:37	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	05e0d5b4ad2149cc95bdea8e06914289	Sent	Yes	05e0d5b4ad2149cc95bdea8e06914289-AR Launch Echo Email 2	05e0d5b4ad2149cc95bdea8e06914289-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	0384cbd4-aef8-4ab5-b585-2173fa65bf5b	Sent	Yes	0384cbd4-aef8-4ab5-b585-2173fa65bf5b-AR Launch Echo Email 2	0384cbd4-aef8-4ab5-b585-2173fa65bf5b-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	0384cbd4-aef8-4ab5-b585-2173fa65bf5b	Sent	Yes	0384cbd4-aef8-4ab5-b585-2173fa65bf5b-AR Launch Echo Email 2	0384cbd4-aef8-4ab5-b585-2173fa65bf5b-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	0384cbd4-aef8-4ab5-b585-2173fa65bf5b	Sent	Yes	0384cbd4-aef8-4ab5-b585-2173fa65bf5b-AR Launch Echo Email 2	0384cbd4-aef8-4ab5-b585-2173fa65bf5b-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	0502bcf0a6654db5a191509ce2991431	Sent	Yes	0502bcf0a6654db5a191509ce2991431-AR Launch Echo Email 2	0502bcf0a6654db5a191509ce2991431-AR Launch Echo Email 2
Q2 Campaign	Sent-AR Launch Echo Email 2	13/05/2022 02:07:03	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	0551001a359b4a6a8668fa1ecc176966	Sent	Yes	0551001a359b4a6a8668fa1ecc176966-AR Launch Echo Email 2	0551001a359b4a6a8668fa1ecc176966-AR Launch Echo Email 2
Q3 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	03325f09dc414c049d667acf64a7192e	Sent	Yes	03325f09dc414c049d667acf64a7192e-AR Launch Echo Email 2	03325f09dc414c049d667acf64a7192e-AR Launch Echo Email 2
Q3 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	04c94419db05438c971f254078864992	Sent	Yes	04c94419db05438c971f254078864992-AR Launch Echo Email 2	04c94419db05438c971f254078864992-AR Launch Echo Email 2
Q3 Campaign	Sent-AR Launch Echo Email 2	15/08/2022 16:58:40	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	05e0d5b4ad2149cc95bdea8e06914289	Sent	Yes	05e0d5b4ad2149cc95bdea8e06914289-AR Launch Echo Email 2	05e0d5b4ad2149cc95bdea8e06914289-AR Launch Echo Email 2
Q3 Campaign	Sent-AR Launch Echo Email 2	20/05/2022 00:35:37	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	05e0d5b4ad2149cc95bdea8e06914289	Sent	Yes	05e0d5b4ad2149cc95bdea8e06914289-AR Launch Echo Email 2	05e0d5b4ad2149cc95bdea8e06914289-AR Launch Echo Email 2
Q3 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	04cf5229b6954a04ab9248dbf1e501f7	Sent	Yes	04cf5229b6954a04ab9248dbf1e501f7-AR Launch Echo Email 2	04cf5229b6954a04ab9248dbf1e501f7-AR Launch Echo Email 2
Q3 Campaign	Sent-AR Launch Echo Email 2	########	Open	AR Launch Echo Email 2	AR Launch Echo Email 2	04c94419db05438c971f254078864992	Sent	Yes	04c94419db05438c971f254078864992-AR Launch Echo Email 2	04c94419db05438c971f254078864992-AR Launch Echo Email 2

[Anonymous]

thank you Polly (Community Support Team _ Polly),your input is helpful. did give me an idea. 

I saw the data got jumbled in the above table. hence pasting a sample. 

I have different Campaigns and in each campaigns have different delivery lables. 

To know number of sent email, unique email sent id will have the details 

To know number of open email, unique email open id will have the details. 

some of the sent id would have been sent email in different campaigns delivery lables. so we are trying to know if any of the sent email id did not open an email in all the delivery campaigns. 

Campaign Name	Campaign Delivery Label	Date	Label	Label-Open	Label-Sent	CustomerID	Status	Reconcilation	Unique_Id_Open	Unique_Id_Sent
Q3 Campaign	Winner1	1/9/2022 17:48			Winner1	18e0ab4f80ec4187be6bbddbfc65646b	Failed	No		18e0ab4f80ec4187be6bbddbfc65646b-AR Launch Email 1B Winner
Q3 Campaign	Winner1	1/9/2022 22:03	Click	Winner1	Winner1	4615821101d040d6b7d199173804f6aa	Sent	Yes	4615821101d040d6b7d199173804f6aa-AR Launch Email 1B Winner	4615821101d040d6b7d199173804f6aa-AR Launch Email 1B Winner
Q3 Campaign	Echo1	1/9/2022 22:03			Echo1	7430edb85f494abda559c7e25a261290	Failed		7430edb85f494abda559c7e25a261290-AR Launch Email 1B Winner
Q3 Campaign	Echo1	1/9/2022 22:03			Echo1	1039f3fbde17498b921338be21c71918	Failed		1039f3fbde17498b921338be21c71918-AR Launch Email 1B Winner
Q3 Campaign	Email1	1/9/2022 22:05			Email1	65a16f5d-30f8-4cde-a915-2167e1f78744	Sent		65a16f5d-30f8-4cde-a915-2167e1f78744-AR Launch Email 1B Winner
Q3 Campaign	Email2	1/9/2022 22:10			Email2	00b71e705a7a46f3b10086ae8f89db19	Sent		00b71e705a7a46f3b10086ae8f89db19-AR Launch Echo Email 1
Q3 Campaign	Email1	1/9/2022 22:11	Click	Email1	Email1	0c416a21a0114d19856e9038b61b52f5	Sent	Yes	0c416a21a0114d19856e9038b61b52f5-AR Launch Echo Email 1	0c416a21a0114d19856e9038b61b52f5-AR Launch Echo Email 1
Q3 Campaign	Echo2	1/9/2022 22:11	Open	Echo2	Echo2	13aae02b10a54b46babdf6c10f8469a7	Sent	Yes	13aae02b10a54b46babdf6c10f8469a7-AR Launch Echo Email 1	13aae02b10a54b46babdf6c10f8469a7-AR Launch Echo Email 1
Q3 Campaign	Email1	1/9/2022 22:17	Open	Email1	Email1	29740e7214ff427ba2bfd74f5bc6cf16	Sent	Yes	29740e7214ff427ba2bfd74f5bc6cf16-AR Launch Echo Email 1	29740e7214ff427ba2bfd74f5bc6cf16-AR Launch Echo Email 1
Q2 Campaign	Winner1	1/6/2022 17:48			Winner1	2cbbdc063632491b8557af286b962907	Sent		2cbbdc063632491b8557af286b962907-AR Launch Echo Email 1
Q2 Campaign	Winner1	10/5/2022 22:03			Winner1	2ef16fd407694fd29dd0a5b6db83c62e	Sent		2ef16fd407694fd29dd0a5b6db83c62e-AR Launch Echo Email 1
Q2 Campaign	Echo1	10/5/2022 22:03	Open	Echo1	Echo1	35281e94519c42349f93ebeb25c6a873	Sent	35281e94519c42349f93ebeb25c6a873-AR Launch Echo Email 1	35281e94519c42349f93ebeb25c6a873-AR Launch Echo Email 1
Q2 Campaign	Echo1	10/5/2022 22:03			Echo1	35fdc4904a5e4569a89b76c0c93a4e78	Sent		35fdc4904a5e4569a89b76c0c93a4e78-AR Launch Echo Email 1
Q2 Campaign	Email1	10/5/2022 22:05			Email1	440e1a7da0d1491a854940cc4c7636c6	Sent		440e1a7da0d1491a854940cc4c7636c6-AR Launch Echo Email 1
Q2 Campaign	Email2	10/5/2022 22:10	Open	Email2	Email2	4765a18855ca46eeb2235f419c8aa181	Sent	4765a18855ca46eeb2235f419c8aa181-AR Launch Echo Email 1	4765a18855ca46eeb2235f419c8aa181-AR Launch Echo Email 1
Q2 Campaign	Email1	10/5/2022 22:11	Open	Email1	Email1	52f65c17ebbf442b9d7d4e6c7cac134b	Sent	52f65c17ebbf442b9d7d4e6c7cac134b-AR Launch Echo Email 1	52f65c17ebbf442b9d7d4e6c7cac134b-AR Launch Echo Email 1
Q2 Campaign	Echo2	10/5/2022 22:11	Open	Echo2	Echo2	54ee1cfded1c466992f103e477541a14	Failed	54ee1cfded1c466992f103e477541a14-AR Launch Echo Email 1	54ee1cfded1c466992f103e477541a14-AR Launch Echo Email 1
Q2 Campaign	Email1	10/5/2022 22:17			Email1	561796a4e88745239ec9669882745353	Sent		561796a4e88745239ec9669882745353-AR Launch Echo Email 1
Q2 Campaign	Email2	10/5/2022 22:18	Click	Email2	Email2	573173b8e5bc40cfbabe32553de04f96	Sent	573173b8e5bc40cfbabe32553de04f96-AR Launch Echo Email 1	573173b8e5bc40cfbabe32553de04f96-AR Launch Echo Email 1
Q2 Campaign	Email2	10/5/2022 22:18			Email2	5818c05627954f40864e48bd572ce6c6	Sent		5818c05627954f40864e48bd572ce6c6-AR Launch Echo Email 1

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please have a try.

Create a column.

Column =
VAR _1 =
    CALCULATE (
        COUNT ( 'Table'[Campaign Name] ),
        FILTER ( 'Table', 'Table'[Campaign Name] = EARLIER ( 'Table'[Campaign Name] ) )
    )
VAR _2 =
    CALCULATE (
        COUNT ( 'Table'[Campaign Name] ),
        FILTER (
            'Table',
            'Table'[Campaign Name] = EARLIER ( 'Table'[Campaign Name] )
                && 'Table'[Unique_Id_Sent] = BLANK ()
        )
    )
VAR _3 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Campaign Name] ),
        'Table'[Campaign Name] = EARLIER ( 'Table'[Campaign Name] )
            && 'Table'[Unique_Id_Sent] = BLANK ()
    )
RETURN
    IF ( _1 = _2, _3, 0 )

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hi @Anonymous ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _1 =
    CALCULATE (
        COUNT ( 'Table'[Campaign Name] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Campaign Name] = SELECTEDVALUE ( 'Table'[Campaign Name] )
                && 'Table'[Label-Open] = "Failed"
        )
    )
VAR _2 =
    CALCULATE (
        COUNT ( 'Table'[Campaign Name] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Campaign Name] = SELECTEDVALUE ( 'Table'[Campaign Name] )
        )
    )
RETURN
    IF ( _1 = _2, _1, 0 )

If I have misunderstood your meaning , please provide more details with your desired output based on the aboved data.

Best Regards

Community Support Team _ Polly

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