I have a scenario,
we send multiple emails to users and in return we get leads generated due to the emails we send. Now, there are chances that a leads submitted by a user might have received more than 1 email and we want to give credit to all the email sent instead of earliest or latest email.
Now my issue is based on the email sent date, the number of email a per received will change and the credit given to emails will also change.
For ex: For a date range, I have 1 lead and 3 emails were sent to that person, than each will get the lead credit as 1/3 each. If I change the date range there might be chances that instead of 3 emails I might have only 2 and 3rd is out of the daterange, in such case the email lead credit should be 1/2 and not 1/3.
Sample Data:
| Lead_id | Email_sent_id | Email_Type | Email Sent date |
| L1 | E1 | Campaign | 1/1/2022 |
| L1 | E2 | Lead Autoresponder | 1/2/2022 |
| L2 | E3 | Lead Nurture | 1/3/2022 |
| L3 | E1 | Campaign | 1/1/2022 |
| L3 | E2 | Lead Autoresponder | 1/2/2022 |
| L3 | E3 | Lead Nurture | 1/3/2022 |
Required output:
| Email Type | Lead Credit |
| Campaign | |
| Lead Autoresponder | |
| Lead Nurture | |
I am trying to use summarize/rollup or a table function to filter the whole data on the fly basis date range selected, and than calculating the for each lead how many email were sent, but when I put that measure in the above table, the number of email sent is getting filtered basis email type which I don't wont. for 1 lead I have 2 emails . one in campaign and another in lead nurture email type, the denominator should be 2 irrespective or email type, but now its getting filtered to 1 each and lead credit is 1 & 1 instead of 0.5 each.
Lead Credit=
VAR GRPBY =
GROUPBY(view_emailperformance,
view_emailperformance[Lead_id],
"cnt",COUNTX(CURRENTGROUP(),view_emailperformance[Email_sent_id]))
VAR GRPBY2 = ADDCOLUMNS(GRPBY,"cnt2",1/[cnt])
Return SUMX(GRPBY2,[cnt2]*[cnt])
