How to automatically calculate averages from ID in Google Sheet?

Question

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

Answer 1

Here's a Query that can do the trick :

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

---

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