Finding Highest Value Combination From Excel Pivot Table Filter

Question

I'm not sure if I am using the correct technique for this so please forgive me if I am way off.

I have a large range of data from previous email campaigns. I have categorised the campaigns with boolean values (Y/N) for the subject line contents e.g: Subject Line Is Personalised, Subject Line Contains Urgency etc...

I am running the columns as Open Rate and the values as the 'Average Open Rate'. The row labels are the campaign names, and the Report Filters are all of the boolean values.

I am trying to automate the process to get the 'ultimate combination' that will result in the highest average open rate. Is there a way to automate this process rather than going through and manually selected Y/N options for the filters until I find the highest average?

As an example; I'm trying to do something that will end up with the filters in set to achieve the highest combination, something like this:

Subjectline - Personalised Y
Subjectline - Urgency Y
Subjectline - Product Groups N
Subjectline - Discount Value Y
Subjectline - Event Reference N
Subjectline - Contains Brands N
Subjectline - LOLS Y

I understand that for this table it isn't that time consuming to do it manually, but I want to run a similar operation on lager data sets and frequently so it would be awesome if I could automate it.

[Note: This is a x-post from Stackoverflow as I didn't get a response]

Answer 1

I would use the Power Query Add-In for this. You can start a Query from an existing Excel table.

I would use the Group By command and group by all your Boolean columns, and Average the Open Rate.

http://office.microsoft.com/en-au/excel-help/group-rows-in-a-table-HA103993875.aspx?CTT=5&origin=HA103993856

Then I would use the sort command to sort by Average Open Rate (descending). I would send the results to a new Excel Table.