3 Replies Latest reply on Feb 6, 2015 9:10 AM by Alex Cook

Avoiding average of averages. Calculating open rate.

I'm trying to calculate open rates for email over time. When I create a calculated field in Tableau, and then chart that against date, it seems to be averaging the averages, giving equal weight to small sends with 100% opens as giant sends with 20% opens. I've tried searching google and this forum, but haven't found the answer (though I got close).

Each row of the data has one send with:

- date of send

- number of sends (DeliveriesImplicit)

- number of opens (Unique Opens)

I created a calculated field called "myOpenRate" as

iif([DeliveriesImplicit]=0,0,[UniqueOpens]/[DeliveriesImplicit],0)

I did this to avoid division by 0, which caused problems in Excel. (DeliveriesImplicit is same as sends)

I tried the table calc total(avg([profit])) approach that I found on this forum, but it just gave me a flat line.

I know this should be easy since it's so easy in Excel pivot tables, and I've seen more complicated solutions for other types of problems in these forums. I'm just stuck. Thanks for your help.

• 1. Re: Avoiding average of averages. Calculating open rate.

Hi Jay,

Would you be able to post the packaged workbook (twbx file)?

Hard to see what's going wrong. One reason you may be getting something other than what you suspect would be if your measures aren't aggregated. You may try:

iif(sum([DeliveriesImplicit])=0,0,sum([UniqueOpens])/sum([DeliveriesImplicit]),0)

-Tracy

• 2. Re: Avoiding average of averages. Calculating open rate.

That worked perfectly. I just copied and pasted your formula in there. Thanks!!

• 3. Re: Avoiding average of averages. Calculating open rate.

Hey gang, I'm having a related problem with getting the correct totals for unique opens. The results I want are simply opens, unique opens, and the relevant rates, by email recipient+email name. It seems that I can get either the absolute number or the rate correct, but not both. When I use the two-pass aggregation (Analysis->Totals->Total All Using->Sum), I get the correct total for unique opens, but then the percentage is incorrect.

The correct totals should be 13 opens, for an overall open rate of 118% (13/11), and 9 unique opens with an overall unique open rate of 69% (9/13). I have read through many of the posts on customizing totals (esp here: Customizing Grand Totals – Part 2 | Drawing with Numbers and Not getting grand totals for complex table calculations) but am still having trouble. If anyone can assist it would be much appreciated. Or if you can direct me to a post that has the solution (so far I've found none) then that would be helpful too.

Thanks,

Alex