Merge two(or multiple) Google Ads accounts to blend with another data source

Question

How to merge two(or multiple) Google Ads accounts to blend with another data source?

If I create the blended source with a third-party analytics system and two Google Ads sources I get doubled metrics in a blended source such as two CTRs, two Avg.CPC etc(Obviously, cause I add both of them to the blended source).

Therefore if I add this blended source as data to the dashboard, it shows statistics with only results from the one Ads account, depends on which for example CTR field I add.

There is no sense in merging two Google Ads, cause its impossible to blend this blended source with another data source.

I cant create a calculated field as well cause somehow it proposes to me only one field(for instance one CTR field) to add despite the fact there are two of them in a blended source(from both Google Ads accounts)

I cant use these fields as join keys cause obviously third dataset hasn't such metrics

Answer 1

The best way is to load in the impressions, clicks and costs of both google ads accounts. Name them differently in the blended source, i just went with 'click1' etc.

Then make a calculated field and calculate the ctr:

(sum(clicks1) + sum(clicks2))/(sum(impressions1)+sum(impressions2)) 

Using 'sum' makes sure it is an aggregated field so when using total rows, it will also be correctly calculated.

Do the same with average cpc:

(sum(cost1)+sum(cost2))/(sum(clicks1)+sum(clicks2))