Import Google Ads Data Into Power BI For Free

Learn how to create an automated report in Google Ads (AdWords) that gets imported into Power Bi using Ads Script and Google Sheets.

Follow these steps

1. Create your Ads Script & Create a Google Sheet
2. Create a Google Sheet
3. Run the code to populate the Google Sheet
4. Change the share properties of the Google Sheet
5. Download the Google sheet as Microsoft Excel
6. Get the download URL
7.  Connect to Google Sheets
8. Clean the data

It’s based on an Excel Power Query report done by Dan Zrust – so a big shout out to him.

As many of my clients use Google Ads conversion tracking code I can’t rely on the Google Analytics add on in Google Sheets. Google Ads gives me KPI’s such as All Conversion Value, Historic Quality Score and Search Impression Share.

My solution was to create a script in Google Ads which then exports the data directly into Google Sheets.

1. Create your Ads Script & Create a Google Sheet

Below is my simple script. Because I wanted to schedule my report my date range  looks back over the last 60 days. This date range also allows me to avoid the limits of Google Ads Script and Google Sheets.

For those familiar with Google Ads you’re probably aware of these values “<10%”, “<90%” and a null value “–” which get returned in the data. We need to remove these text strings from our Google Sheet to prevent errors in our Power Bi report. To get rid of these we add the conditions below.

‘AND SearchImpressionShare > 0.10 ‘ + ‘AND SearchExactMatchImpressionShare > 0.10 ‘ + ‘AND SearchRankLostImpressionShare < 0.90 ‘ +

 

function main() {

// The start and end date of the date range
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var now = new Date();
var from = new Date(now.getTime() - 60 * MILLIS_PER_DAY);
var to = new Date(now.getTime() - 1 * MILLIS_PER_DAY);
var timeZone = AdWordsApp.currentAccount().getTimeZone();

// Add you spreadsheet here
var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxx/edit#gid=0');

// Select Query - note the and conditions to remove strings such as ">90%", "<10%" and "--"
var query = 'SELECT Date, CampaignName, Impressions, Clicks, AveragePosition, ' +
'SearchImpressionShare, SearchExactMatchImpressionShare, SearchBudgetLostImpressionShare, SearchRankLostImpressionShare ' +
'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
'WHERE AdNetworkType2 = SEARCH ' +
'AND SearchImpressionShare > 0.10 ' +
'AND SearchExactMatchImpressionShare > 0.10 ' +
'AND SearchRankLostImpressionShare < 0.90 ' +
'DURING ' + Utilities.formatDate(from, timeZone, 'yyyyMMdd') + ','

+ Utilities.formatDate(to, timeZone, 'yyyyMMdd');

// Check timezone. If you don't want to use account timezone you can change it
// Logger.log(Utilities.formatDate(date, 'Etc/GMT', 'yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\''));
Logger.log(Utilities.formatDate(to, timeZone, 'yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\''));

Logger.log(query);
var report = AdWordsApp.report(query, { 'includeZeroImpressions': false });

report.exportToSheet(spreadsheet.getActiveSheet());
Logger.log("Your Spreadsheet is ready " + spreadsheet.getUrl());
}

 

2. Create a Google Sheet

Once the code is in place you will need to add a new Google Sheet to your drive. Copy the URL and paste it into the code.

3. Run the code to populate the Google Sheet

Now run the script to populate your Google Sheet. If you want to automate the report you will need to setup a schedule in Google Ads.

4. Change the share properties of the Google Sheet

You need to share the Google Sheet URL as “Anyone with link can edit”.

5. Download the Google sheet as Microsoft Excel

Go to File > Download As > Microsoft Excel (xlsx)

6. Get the download URL

To get the download URL you need to open up the download window in the browser and then copy the link. In Chrome this is Chrome//downloads or the shortcut “Ctrl + J”.

The reason why we need this URL rather than the actual Google Sheet URL is that the download URL contains details about the Excel file. This is critical in order to bring the data into Power Bi. You can see the difference between the URLs below

Download URL:

https://docs.google.com/spreadsheets/d/xxxxxxxxxxx/export?format=xlsx&id=xxxxxxxxxxxxxxxxxxxxxxx

Google Sheet URL:

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxx/edit#gid=0

7.  Connect to Google Sheets

Open up Power Bi and click on Get Data > Web and then paste in your URL

 

8. Clean the data

Once your data is in Power Bi you should check that your data is clean. Check that the data types look correct, i.e. the data column has the data type and numbers are decimals rather than text.

If you downloaded quality scores you will need to check for these “–“.  If there is no quality score this “–” will be in the columns. You can quickly check this by using the dropdown selector on the column, as shown below.

Click on the “replace values” button to remove any spurious data, as shown below.

Additional Options

Clear Cache in Power Bi

If you pull data into Power Bi regularly it’s a good idea to clear the cache so that the correct data shows. To do this go to File > Options & Settings > Options. In the Data tab press the button that says “Clear cache”.

Delete Empty Cells in Google Sheets

When you run a long date range in your script many rows are created in Google Sheets. If you then run a shorter date range less rows will be filled with data but the blank rows will still remain. Unfortunately if you then refresh Power Bi these blank rows will also be imported into the data model. To prevent this delete all the blank rows in Google Sheets and then refresh the

Add A Date Table

Whilst you may have a date column what you really need is a new date table which will allow you to create a relationship between your current data table and any possible new tables you wish to create. For example, perhaps you would like to import geographical data into Power Bi. This would require a new script that would specifically query how your campaigns have performed by location.

Leave a Reply

Your email address will not be published. Required fields are marked *