Gift frequency increase after I contacted donor

[roseyBP]

Hi ,

I am trying to  find second gift rate after I contacted donor  . I want to analyse how my engagement with donor is encouraging donor to make second gift . I am looking at call dates and gift dates to find the gift frequency. I have three tables to get these information . Could you kindly assist . 

Sample data

Table 1 Action look up(I have added action in RE after I make call)

Constituent ID	Action Import ID	Call Type	Call Date	Status
1111	00001-504-0003373693	Welcome Call	14/04/2023	Completed
2222	00001-504-0003373697	Welcome Call	14/04/2023	Completed
3333	00001-504-0003373700	Welcome Call	14/04/2023	Completed
4444	00001-504-0003374645	Welcome Call	18/04/2023	Completed
5555	00001-504-0003417401	Welcome Call	11/5/2023	Completed
6666	00001-504-0003417367	Welcome Call	11/5/2023	Completed
7777	00001-504-0003417368	Welcome Call	11/5/2023	Completed
8888	00001-504-0002911815	Welcome Call	7/10/2022	Completed
9999	00001-504-0002911967	Welcome Call	7/10/2022	Completed

Table 2

Gift Look Up (Gifts made by donor till date)

Constituent ID	Gift Import ID	Gift Date	Gift Amount
1111	00001-545-0000	14/12/2020	$1,000.00
1111	00001-545-0001	3/12/2021	$1,000.00
2222	00001-545-0002	17/05/2022	$1,000.00
2222	00001-545-0003	13/07/2022	$4,000.00
3333	00001-545-0004	29/11/2022	$100.00
3333	00001-545-0005	5/5/2023	$10.00
4444	00001-545-0006	15/06/2023	$10.00
4444	00001-545-0007	15/05/2023	$10.00
4444	00001-545-0008	20/07/2022	$100.00
5555	00001-545-0009	23/05/2016	$30.00
6666	00001-545-0010	25/11/2021	$200.00
6666	00001-545-0011	15/07/2023	$10.00
7777	00001-545-0012	15/08/2023	$10.00
8888	00001-545-0013	19/05/2016	$100.00
9999	00001-545-0014	31/01/2023	$50.00
9999	00001-545-0015	16/05/2016	$20.00

Table 3 Donor Look Up

Constituent ID	First Name	Surname
1111	Andrew	xxxx
2222	Keith	xxxx
3333	John	xxxx
4444	Lorraine	xxxx
5555	Heili	xxxx
6666	Gerry	xxxx
7777	Carmen	xxxx
8888	Melissa	xxxx
9999	Henry	xxxx

[nirali_arora · Accepted solution]

You might require the following dax measure

SecondGiftRate = VAR FirstGiftDate = MIN(Gift[Gift Date]) RETURN DIVIDE( COUNTROWS(FILTER(Gift, Gift[Gift Date] > FirstGiftDate)), COUNTROWS(DISTINCT(Action[Call Date])), 0 )

[DataNinja777 · Accepted solution]

Hi @ roseyBP,

Your calls have increased the frequency of the donations as shown below, even though the amounts are nominal. 

I attach the pbix file below:

Gift Donation.pbix

[roseyBP]

Thank You so much 😄

[nirali_arora · Accepted solution]

You might require the following dax measure

SecondGiftRate = VAR FirstGiftDate = MIN(Gift[Gift Date]) RETURN DIVIDE( COUNTROWS(FILTER(Gift, Gift[Gift Date] > FirstGiftDate)), COUNTROWS(DISTINCT(Action[Call Date])), 0 )

[roseyBP]

Thank You so much 😄