Page 1 of 1

How to visualise (Google Ads, Facebook, Bing, Google Analytics, DoubleClick, ...) data in Microsoft Power BI

Posted: Sat May 16, 2020 7:40 pm
by niklaskolster
The platform supports API calls to retrieve and send information. In this article, we explain how to extract data and visualise it in Microsoft Power BI.

The prerequisites are:
  • account
  • API key
  • Microsoft PowerBI (Important: This feature is only available on PowerBI on Windows)
Here are the steps required to load data into Microsoft Power BI
  1. In PowerBI open a new window and click on Get Data and then Web
  2. In the URL box enter your API query
    Here is an example query:

    Code: Select all
    The highlighted fields:
    pied_piper_llcshould be replaced with your username
    pied_piper_llc_attributions_and_costs should be replaced with the table you want to load
    abcd should be replaced with your API key
    &_select=date,source,medium,campaign,sum:totalcost should be replaced with the columns and metrics which you want to visualise
    &_groupby=date,source,medium,campaign should be replaced by the columns you want to group by
    &_order=-date should be replaced by the columns you want to order by

    The API documentation can be found here and the available fields can be found in the table definitions here.
  3. Click on To Table
  4. Keep the Select or enter delimiter dropdown to it’s defaul value None and click OK
  5. Click on the button with the two arrows to expand the columns
  6. Click on OK
  7. Rename the columns to your liking so it will be easy to work with them once they are available in Power BI as fields
  8. Click on Close & Apply
  9. Great! Now you have the data integartion set up and the data will be available in Power BI for visualisation. Let’s create a simple chart showing us spend over time. Click on the Line chart
  10. Fill date into axis and cost into values
  11. Repeat and add other charts and tables.
If you would like to build another query to get different dimensions and metrics into Power BI please have a look at our API documentation, which can be found here, and our table definitions, which can be found here.