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
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))