Hi i want to build a query on people who have opened more than 30% of deliveries sent.
I tried this expression:
Percent(Countdistinct([trackingLog/@id]), Countdistinct([broadLog/@id])) greater than 0.3
But it's not working, can anyone help?
You need to design a workflow which will fetch data from BroadLogRcp and TrackingLogRcp table.
1) Design query on broadLogRcp with delivery id greater than one and delivery status equal to sent. Then perform Group by on recipient Id and have one column to get count of those recipient id.
2) Design query on trackingLogRcp with delivery id greater than one and email open equal to yes. Then perform Group by on recipient Id and have one column to get count of those recipient id.
3) Perform intersection on both the queries using recipient id as identification parameter. You will get both recipients who has received mail and their open rate.
4) Now use your formula: Percent(Count of tracking log recipient Id/Count of broad log recipient id) greater than 0.3
That works, thank you!