Oops, all right? Lauro here again bringing a content to simplify your work routines! 😉
After several positive feedbacks from the article Integrating Google Analytics with Google Sheets in 5 minutes, I decided to present a way to fetch the data of Google Ads (formerly Google Adwords).
In this example I will show you how to get basic information from paid campaigns. If you want, you can configure your report in other ways, according to your needs. Let’s start?
1) Create a new spreadsheet
First, log in with your Google account to the Google Sheets homepage. Create a new “Blank” document and give it a name of your choice.
Tip: What is the difference between Google Ads and Google AdWords?
I called my spreadsheet “Google Ads – Reports“. This time I was more creative during the process! 😀
2) Install the add-on
Google Ads has been around for quite some time now. allows you to create scripts within the platform itself that can send data to Google Sheets. But recently came the official add-on that allows you to fetch the data from there. The result may be the same, but the path is different.
Tip: Learn how to generate more leads using Google to your advantage
For personal reasons, I prefer to use an add-on that fetches the information. So let’s install it!
Click here to install the add-on. You will see a page like the image below:
Some security permissions will be requested. Follow the step by step and the add-on will be available in your account.
3) Create your first report
It goes without saying that to follow here you need an activated Google Ads account, right?
Well, in your spreadsheet click on “Add-ons > Google Ads > Create new report”. Select the account in the popup that opens and, later, reselect the view in the field “Select account”.
Configure your report as follows:
- Where is it Last 7 days select all time;
- In Select a report typeselect campaigns;
- Choose a suggestive name in Name your report;
- In Choose columnsmark the fields that you want to bring;
- Apply some filter if you wish;
- Mark schedule reports and leave the option selected daily. This will make your report update automatically, alone, every day. Wonderful, right?
After that, your report will run automatically and a new tab will be created.
4) Final Settings
on the tab Report Configuration you will find the settings defined for this report. You can move directly there, or edit through the visual interface.
Tip: How important is analytics for websites?
To edit, go to “Add-ons > Google Ads > Edit a report”. The settings side box will appear.
In my report I I inserted some columns that make sense for the type of reports I want to interpret. If you want to leave it the same way I did, add the following in Columns: Campaign status, Campaign, Account, Budget, Status, Bid strategy type, Month, Clicks, CTR, Avg. CPC, Impr., Cost.
BONUS: Converting the date format
The best is always for the end, isn’t it? In that report the Google Ads brings the date format as “2018 M11”, p. e.g., representing the month of November 2018. For purposes of calculation and cross-checkyou will normally want the date format to be more “elegant”.
Tip: How is data marketing capable of increasing sales for a business?
I will show here how to adjust it. Follow the steps below:
A) Create a new tab and call it campaigns (organized), p. ex.;
B) In cell B1, paste the following formula: ={Campaigns! A13:P}
B.1) In my case I I called my report “Campaigns”. If you chose another name, change it in the formula;
B.2) Maybe you will need work the data range which, in my case, is from A13 to column P.
C) After that, you you will see that the report data will be synchronized with the new tab;
D) In ​​column A2, paste the following formula: =ARRAYFORMULA(IF(NOT(ISBLANK(B2:B));DATEVALUE(CONCAT(SUBSTITUTE(B2:B;”M”;”-“);”-01″));))
D.1) This formula will take every month in “weird” format and turn it into a date.
E) Select the entire column A, click “Format > Number > Date” and choose “Month/Year” format. Or any other format you want.
Ready! Every day your report will be updated and you can use the information to cross-reference.
Want to learn how we unite different channels to acquire customers here at Digital? Click on the banner!
So what do you think of this one? How to fetch Google Ads data?
Comment below if you want me to continue with the series of posts on Growth Hacking applied to Digital marketing! 😎
Enjoy it. 😉
