Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

percent calculation

michaelr8873103
Level 2
Level 2

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?

Thanks

1 Accepted Solution
Jyoti_Yadav
Correct answer by
Community Advisor
Community Advisor

Hi,

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

Thanks.

View solution in original post

0 Replies
Jyoti_Yadav
Correct answer by
Community Advisor
Community Advisor

Hi,

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

Thanks.

View solution in original post