2

I've been looking around for an answer but couldn't find what I was looking for.

The problem is that I want to use an arrayformula, but can't figure out a dynamic formula that:

  1. Looks at the unique IDs in one column, and gets corresponding data in the other columns
  2. Averages the corresponding columns
  3. Automatically does this when a new ID is present

The case: I'm building a dashboard for all marketing channels, combining Google Analytics (GA) and Facebook Ads (FBA).

I wish to see how each campaign (ID) is performing, using averages as the indicators. (CPC, Clicks, Impressions...)

I've managed to pull data from GA and FBA to Google Sheets, and wants to calculate the average cost per click (CPC) and clicks for each ad campaign.

I've used:

  • Filter
  • Query
  • VLOOKUP

Without the arrayformula I get the correct result for the filtered campaigns. But I don't want to manually add the formula to each row every day.

Here is a sheet to see example data, and expected results from an array. https://docs.google.com/spreadsheets/d/1pTEMbJGh-Gf5RwU8bR-JZCv2rWIHsKLBLTz-BxD9Uw4/edit?usp=sharing

1

Here's a Query that can do the trick :

=QUERY(Data!B1:D,"SELECT B,AVG(C),AVG(D) GROUP BY B ")

enter image description here


You can use label to rename the column's header.

  • Thank you! It worked beautifully. – JWaern Nov 5 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.