Google Sheets Query Extract Value by Max Spend

Question

I'm running a Google Sheets report on our Facebook marketing campaigns and I want to use a query to return the best performing ads in each campaign by spend. The data is set up as follows:

Campaign Name          Ad Name          Link          Spend
Campaign 1             Ad 1             www.a.com/1   250
Campaign 1             Ad 2             www.a.com/2   100
Campaign 1             Ad 3             www.a.com/3   150
Campaign 2             Ad 1             www.a.com/4   75
Campaign 2             Ad 2             www.a.com/5   180
Campaign 3             Ad 1             www.a.com/6   300
Campaign 3             Ad 2             www.a.com/7   50

So what I would like is to pull out the top performing ad (the one with the largest spend) in each campaign with its corresponding link. Essentially a list that looks like:

Campaign Name          Ad Name          Link          Spend
Campaign 1             Ad 1             www.a.com/1   250
Campaign 2             Ad 2             www.a.com/5   180
Campaign 3             Ad 1             www.a.com/6   300

I am sure that I can do this using a query but just haven't been able to make the arguments work.

I can provide an example spreadsheet if needed.

Thanks a lot Alex

Answer 1

Query can't handle max value by selective fields only.

Please try:

=ArrayFormula(vlookup(UNIQUE(FILTER(A2:A,A2:A<>"")),SORT(A2:D,4,0),{1,2,3,4},))