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.

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.

Step 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());
}

 

Step 1: 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.

Step 2: 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.

Step 3: Change the share properties of the Google Sheet

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

Step 4: Download the Google sheet as Microsoft Excel.

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

Step 5: 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

Step 6: Connect to Google Sheets

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

 

Step 7: 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.

Scroll Tracking Using Google Tag Manager’s New Scroll Depth Trigger

Scroll depth tracking is not new but now you can set it up in a jiffy.

Log into Google Tag Manager (GTM) and then click on “Triggers” in the left navigation.

GTM Triggers

Select new and then click on the pencil icon in the top right of the box.

Select “Scroll Depth” in the right hand column.

Depending on what you want you can either select vertical or horizontal scroll depth tracking.

Add the percentages for when the trigger will fire. In this case 10%, 25%, 50%, 75% and finally 100%, which is at the bottom of the page.

If you only want this trigger to fire on certain pages you can specify “some pages” and then add the path, as shown below.

Save the trigger and press preview (top right) to test the trigger is firing correctly.

In the same browser that you have GTM open open the site.  You will see GTM’s debugger frame open at the bottom.

As you scroll you will see the data object being pushed.  So success.

 

Set up Google Analytics Event Tag

In GTM click on Tag (left column) then new. Click on the pencil to then select Universal Google Analytics as shown below.

Configure the new tag as follows

 

 

 

Connect to Multiple Data Sets Using Excel Power Query

The purpose of this tutorial is to merge data from two data sets. To do this we need the products table in excel format, which you can get here: Products – Master 

In a new excel spreadsheet we will first import the Products table from excel spreadsheet.

Next we will connect to the Northwind OData Feed and then import the columns we are interested in from the Order_Details table.

Import Excel products table

On the Data tab select

Select “Get data” in the left of the tab. From the  drop down select “From File” and then select excel

When the Navigator appear select the Products worksheet. Press Edit at the bottom.

You should see something like this below in the Query Editor

You should always check the top row of the columns just to make sure the columns are correct.  If they are not you may have to change these by using “Use first row as headers”.

Make top row as headers

If the first row has headers like Column 1, Column 2, etc then you will need to make the first row as headers. If the headers are okay then you can skip this section.

There are two places where you can select “Use first row as headers”.  You can do this from either the top bar or you can select the table icon at the top of the column.

Remove unwanted columns

Now we need to remove all the rows that are not of interest to us. We only want ProductID, ProductName, CategoryID, and QuantityPerUnit.

To select the columns to include click on the individual columns whilst holding down the Ctrl button.

Then select “Remove other columns”

Select Close & Load to finish the import

A new sheet is created in the Excel spreadsheet.

Connect to a OData Feed

Connect to data feed

On the Data tab select

Get Data drop down > From Other Sources > From OData Feed

Add your data source URL, in this case Northwind, and click OK

http://services.odata.org/Northwind/Northwind.svc

Select the  Order_Details table and then press Edit.

The Power Query Editor now opens.

Make top row as headers

 

In Power Query, a column containing a link to a related table has an Entry link or Table link. An Entry link navigates to a single related record. A Table link navigates to a related table. A link represents navigation properties in a data source within a relational model. For an OData feed, navigation properties represent an entity with a foreign key association. For a database, such as SQL Server, navigation properties represent foreign key relationships in the database (read more).

Next we have to expand the Order_details table that is related to the Products table.  This is to join the ProductID, UnitPrice and Quantity columns of the Order_Details into the Product table.

The Expand operation combines the columns from a related table into a subject table.

When the query runs rows from the related table (Order_Details) are combined into the rows from the subject table (Product).

Expand Product Column

In the Preview editor, click an Entry link in a column that is related to the subject table (Product).

You need to click on this icon    and then deselect everything except for the items you want.

Select ProductID, QuantityPerUnit and UnitPrice.

Once you click OK the columns are added as shown below.

Expand Order Column

Just like we did above we are going to expand and then select OrderID, OrderDate, Shipper, and anything else that might be of use to you.

Once done select OK

Calculate row revenue

Add a custom column to add a calculation to sum Product.UnitPrice * Quantity

Add custom column as shown below.

A pop-up will appear. Give your formula a name that makes sense. Add this formula

= [Quantity]*[Product.UnitPrice]

Change the data type of the new column to currency

Change Date to Year

Select OrderDate. In the Transform tab select Date and then navigate to Year.

Group rows by ProductID and Year

Click the Group By icon in the Transform tab.

Select OrderDate and ProductID.

Give the new column the name of Total sale and select the fields as shown below. Click OK.

Your query should now look like this:

Merge Queries

We are now going to merge both the Products and Order_Details data.

On the home tab select Merge Queries and then Merge Queries as New.

A dialog box will appear as show below. Select

You should now see something like this:

 

This article is based on an article on the Microsoft support site.

 

How To Merge Tables Using Power BI

I recently downloaded an ebook from “Power Query Training” https://www.powerquery.training/magic-tricks-data-wizards/ which I am currently going through. The first chapter deals with merging tables using Excel’s power query so I thought I would show you how to do the same exercise using Power BI.

Having opened Power BI the first thing we want to do is to connect to our data source. In this case we select Excel and then press the yellow connect button.

Once you have selected your file you will see all the sheets you can connect to. It’s a good idea to select each sheet individually first to check the data is correct before proceeding. Once all the tables you want are select click the edit button.

Once the data is in Power BI you want to select the Employee2 table and then select “Merge Queries”. Here you have a choice between creating a merge using the existing query or create a merge using a new query. If you want to keep the data that you have just brought into BI intact, then select “Merge Queries As New”.

Having selected the merge queries button a new merge window pops up.  The Employee2 table is already selected. Now select the BusinessEntity1 table.  Now go back to the Employee2 table and select TerritoryID. Then in the table below select the TerritoryID. This will join both tables on these columns. Notice a Left Out join is selected in the “Join Kind” at the bottom.

Next, select BusinessEntityID columns from both tables. Press the OK button when done.

Change the join type from Left Outer join to Right Outer join and then press OK.

Click on the double arrow at the top of the Sales column. In the drop down box select “Aggregate”.

Next select “Sum of OrderQty” and “Sum of LineTotal”. Press OK.

Select the BusinessEntity1 column. This time we want to have the “Expand” radio button selected. Deselect everything except the “RegionBE” field. Click OK.

Make sure the SalesLineTotal is also sorted by ascending order.

Rename the new columns to make them more understandable to users.

Now when we look at our new table in the table view you can see all the Regions are grouped with sales in descending order.