Data Studio
1.7K members online now
1.7K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Aggregated AdWords/Analytics Accounts as Datasource

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi,

 

I'm working with Data Studio the first time.

My task is to build a Reporting containing data from lot's of Adwords and Analytics Accounts (seperate country websites).

Is there a possibility to show for example the Impressions for all accounts (e.g. Germany, UK, France...) in one chart?

Otherwise the Reporting will not work.

 

KR and thanks in advance

Denise

Aggregated AdWords/Analytics Accounts as Datasource

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Hi @Denise P,

 

I'm afraid that while you can show multiple data sources on the same report, you can't merge them in a single chart (yet!). If you require them in a single chart you would have to merge the information at data level, in Google Sheets for example.

 

Hope that helps,

Ben

Re: Aggregated AdWords/Analytics Accounts as Datasource

[ Edited ]
Follower ✭ ✭ ☆
# 3
Follower ✭ ✭ ☆

As @Ben P points out, you need to do the aggregation in Sheets, but this is another case where "ARRAYFORMULA" is your friend -- although it's not even mentioned at all in the official documentation, you can actually use the syntax " =ARRAYFORMULA( { Range01 ; Range02 ; Range03 } ) " to append multiple ranges into a single table (as long as they all have the exact same number of columns).

 

In your case, then, what you want to do...

  • Pull the exact same report (column-wise) from each regional AdWords account.
  • Put each regional report on its own tab in a Google Sheets doc, without column labels across the top. (It's also a good idea to delete any empty rows at the bottom of each tab -- see following "Technical Point" below for why.)
  • On a new "Combined" tab, put the correct column labels you want ("Date", "Campaign", "Ad Group", "Clicks", etc.) across row A. (And then I'd recommend freezing the top row, as well.)
  • In cell A2 of your "Combined" tab, put your correct version of the following formula: " =ARRAYFORMULA( { 'AW Germany'!A:K ; 'AW UK'!A:K 'AW France'!A:K } ) ". (Obviously, using the actual names of all your tabs, with the span of columns you're actually using in your report.)

At that point, you should have a single table that consolidates all of your regional accounts in one dataset, which will allow you to create any kind of integrated chart or table in Datastudio that you need.

 

TECHNICAL POINT: Why you only want to put column headers across the top of the "combined" tab

Spoiler
You really want the arrays in your ARRAYFORMULA to be column ranges (e.g., "A:K"), not "cell ranges" (e.g., "A1:K3496"). If they're column ranges, then it doesn't matter how many rows there actually are in each tab, or if the number of rows change every time you update your reports -- you'll always grab them all. (Incidentally, that's also why it's a good habit to delete any empty rows at the bottom of each account tab, so you don't have big stretches of empty rows in your combined table when you glue them all together. It's actually OK if that happens, but it's annoying and inelegant.)

If you have column headers across each account tab, though, then you can't use column ranges in your ARRAYFORMULA. If you did try to use column ranges, the headers for each tab would be included as data rows in the final table, which can't work. It means that there are going to be rows with alphanumeric metric labels like "Clicks", "Impressions", etc. appearing in columns that are supposed to only be metric numbers. Datastudio will give you an error as soon as you try to connect to your combined table.

OTOH, if you try to keep column headers across the top of each tab, and use cell ranges instead (like "A2:K1357"), then you're just asking for a world of hurt. You'll need to manually update the specific cell ranges for each of your tables, which is not only tedious, but creates huge opportunities for error.

The most important argument for using column ranges, though, is to allow for automation. There are a number of ways you can pull in AdWords data automatically into a Google Sheet (my favorite is "Supermetrics", which requires a subscription, but is very powerful, and works really well). If your ARRAYFORMULA is defined using column ranges, then it doesn't matter if each of your source tables gets longer and longer with each automated pull -- they just all keep getting appended, in full.

 


 

When it comes to merging and shaping your data into a single table using Sheets, you can obviously get a lot more complicated than that through combinations of ARRAYFORMULA, QUERY, IMPORTRANGE, VLOOKUP, MATCH/INDEX, etc., but the approach above should at least get you what you need right now. There are, however, some pretty straightforward ways that you can extend and even automate this process...

 

Cross-Platform Reporting

If you can do it with AdWords data, you can do it with Bing Ads data. Just create another source tab for each Bing account, and make sure to have the exact same corresponding columns in the exact same position. (This is another benefit of leaving out the column headers on all the source tabs -- it doesn't matter that AdWords and Bing sometime use different names for the exact same metric or dimension.) If you're pulling in data from one platform that doesn't exist on the other (like "Calls" from AdWords), then just make sure to leave blank columns in the corresponding tables from the other platform. (It's usually easiest to try and leave those columns all the way to the right.)

 

Custom Columns

  • Adding Custom Columns: If you need, you can have also create additional columns running alongside your core source tables. For example, if you're pulling in data from both AdWords and Bing, you can have an extra column to the left or right of your source data, that just has "Bing Ads" or "AdWords" all the way down, in every row. As long as you've got the same column, in the same place, on every source table, then you can include it in your final column range, and it will pulled in as a dimension that you can use in Datastudio. (Same thing with your regional accounts, @Denise P  -- just add a column next to each of your source tables that repeats "France", or "Germany", or "UK" all the way down, and then you can easily filter and compare your data in Datastudio by region.)
  • Automating Custom Columns: You can absolutely populate any custom column by just copying and pasting a fixed value all the way down, but -- as in the "Fine Point" above -- that's also a manual process that's both tedious, and hugely error-prone. This is another place where "ARRAYFORMULA" can help, though. In the top cell of any column you want to populate, just add your appropriate version of this formula: " =ARRAYFORMULA( IF(ROW($A:$A), "MY LABEL" )) ". That will ensure that every row on that tab has the text "MY LABEL" in that column, no matter how many rows there are.

TECHNICAL POINT: How this "ARRAYFORMULA" works

Spoiler
This is the most straightforward application of ARRAYFORMULA, where you're asking the Sheet to take a built-in function that would normally be applied to a single cell, and apply it over a range, instead. Since we're really not trying to apply any built-in function -- just assign a text value -- we need to use a plain-vanilla function (in this case, 'ROW()'), use that to "fool" ARRAYFORMULA into applying itself over a range, and then basically just say "Nah, jk...I just want to assign this text, instead".

"Why ROW?"
I usually use ROW as the plain-vanilla function because it's robust (it's _always_ going to return a legal value), it's short, and it's intrinsically useful. Imagine if you pasted the simple formula "=ROW(A1)" in the top cell of your row, and then copy-pasted that all the way down your column, each row of that column would obviously just display its row number. If you put "=ARRAYFORMULA(ROW($A:$A))" in that top cell, though, then you don't have do the copy-paste. You'll immediately see the entire column populate with the correct row numbers, all the way to the bottom of your sheet, and it will continue to work correctly, no matter how many rows you have in your sheet.

Why "ROW($A:$A)"?
It doesn't actually matter which column of the table you use as the "reference" array, since ARRAYFORMULA basically applies your function to a range that's the same "shape" as your reference range, but anchored at the cell it actually "lives" in. Since "$A:$A" is a single column stretching from the top to the bottom of the sheet, then no matter which column contains your ARRAYFORMULA, the output values are going to be applied from the top to the bottom of that column in the sheet. (NOTE: This is only true while you're referencing columns in the same tab. You have to be careful when referencing ranges in other tabs, since there's no intrinsic reason that they've got the same number of rows.)
As for why "$A:$A", specifically? Just because there is _always_ a column "A" in a sheet, so it's a convenient default column to use. As far as using the absolute "$A:$A" reference, as opposed to "A:A", that's just a matter of careful habit. It prevents any weird errors from cropping up because you copy and paste one of these ARRAYFORMULAS, and all of a sudden you're trying to reference a relative column that doesn't exist, or something like that.

Why "IF(ROW($A:$A), ...)"?
This is where we do the "bait-and-switch". "=ARRAYFORMULA(ROW($A:$A))" will give us an 'index' column (which can be really useful), but we don't want a column of explicit row numbers, we want a column of text values. Using "IF" takes advantage of the fact that IF automatically translates integer values as TRUE/FALSE -- basically, "0" evaluates to "FALSE", and any other number evaluates to "TRUE". Since there is no "Row 0" on a sheet, every row in our column evaluates to TRUE, so all we need to do is provide our text value as the value_if_true option, and we've filled the entire column with our text.

 


Sample Google Sheet: PPC Data Shaping

Sample Data Connection: PPC Data Shaping

Sample Datastudio Report: PPC Data Shaping