Looker Studio with data from its own API

From this article you will learn:

1. What is business intelligence (BI)
2. What Google Report (Looker Studio- formerly Data Studio) is used for
3. How to connect your own data source to it

Business analytics (business intelligence)

Business analytics (BI) is the process of collecting, processing and presenting data to make sound business decisions. BI consists of a series of tools, methods and processes that allow the collection, analysis and presentation of business data. The goal of BI is to give employees at all these levels of the organization quick access to information that allows them to make more reasoned business decisions.

For example, a company that sells products online can use business analytics to examine what products sell best, what times of the day or week the most transactions occur, and what are the most important sources of website traffic. The company can also study what are the most important factors in a consumer's decision to buy a product, and what are the most important advertising channels that attract the most customers. This information can help the company decide the next steps in the process of selling its products.

Looker Studio

Looker Studio commonly referred to as Google Reports is Google's browser-based answer to business analytics. Formerly its full name was Google Data Studio, this tool is used to create reports and data visualizations. It allows you to create professional-looking business reports easily and quickly. You can use it to create various types of reports, such as:

• Sales reports: you can combine data from various sources such as Google Analytics, e-commerce, CRMs and others to create reports on sales, the impact of marketing channels on sales, etc.
• Marketing activity reports: you can combine data from Google Analytics, Google AdWords, Facebook Ads and other marketing tools so as to create reports on the effectiveness of advertising campaigns, the impact of channels on website traffic, etc.
• Websiteactivity reports: you can combine data from Google Analytics, traffic quality monitoring tools, databases and others, so as to create reports on website traffic, user behavior, conversions, etc.
• Financial reports: you can combine data from various financial systems, such as QuickBooks, Xero and others, so as to create reports on revenue, expenses, profits, etc.
• Custom data reports: you can combine data from various sources such as databases, CSV files, Google Sheets and others so as to create custom data reports that are not available in tools other than Data Studio.

Google Reports are not a very sophisticated tool. They do not allow for data prediction. They work by combining data from multiple sources and visualizing them. However, for large corporations, such a solution may not be enough. In my professional practice, it happened that in a small startup where funds were very tight, creating a connector to send data to Google Reports turned out to be cheaper than setting up a separate application for business clients with a dashboard visualizing all data on charts.

Looker Studio (formerly Data Studio) pulls in data via connectors. At the moment there are more than 600 publicly available connectors, nevertheless nothing prevents you from creating your own.

Custom connector for Looker Studio

For the purpose of this article, we will try, to create a connector that will download exchange rates from the NBP API. This will allow us to generate charts in Google Reports like the one below.

To create your own connector, simply create a new project in Google Script. In this project you will need two files: appsscript.json and a code file, which in the example below is called code.gs.

Appsscript.json is the file that acts as the manifest. It is based on it in the list of data sources for the report that our connector will appear with the appropriate name, or log.

1{
2 "dataStudio": {
3   "name": "NBP Currency",
4   "logoUrl": "https://www.nbp.pl/graph/logo/logo-nbp-w2.jpg",
5   "company": "Bernard van der Esch",
6   "companyUrl": "https://developers.google.com/datastudio/",
7   "addonUrl": "https://github.com/adeptofvoltron/google-data-studio-nbp-connector",
8   "supportUrl": "https://github.com/adeptofvoltron/google-data-studio-nbp-connector/issues",
9   "description": "Get currency rate from polish national bank",
10   "sources": []
11 }
12}

Appsscript.json is enough to publish our code as a connector to Google Reports. However, in order for the connector to work properly, we need to include calls to 4 functions in the code.gs file: getAuthType(), getConfig(request), getSchema(request), getData(request).

The getAuthType() function affects how we can authenticate to our connector. Here we have several authentication methods to choose from, and we can preview all the possibilities in the Google Apps Script documentation. The API we will be connecting to does not require any authentication.

1function getAuthType() {
2 var cc = DataStudioApp.createCommunityConnector();
3 var AuthTypes = cc.AuthType;
4 return cc
5   .newAuthTypeResponse()
6   .setAuthType(AuthTypes.NONE)
7   .build();
8}

In the getConfig (request) function in this method, we define the configuration of our coupling software. First, we create a form that the user will have to fill out before adding our coupler to their report. In our case, we will add a text field, for the code of the currency whose rate we would like to retrieve. We will also add a text field, with instructions for our connector. Finally, we will indicate that our data will always be retrieved for some date range.

1function getConfig(request) {
2 var cc = DataStudioApp.createCommunityConnector();
3 var config = cc.getConfig();
4  config.newInfo()
5   .setId('instructions')
6   .setText('Enter currency you are interested in.');
7  config.newTextInput()
8   .setId('currencyCode')
9   .setName('ISO 4217 currency code')
10   .setHelpText('e.g. USD or EUR')
11   .setPlaceholder('EUR');
12
13
14 config.setDateRangeRequired(true);
15  return config.build();
16}

The getSchema (request) function is responsible for telling our connector what data it can return. I took the liberty of excluding the list of fields to another function, as it will still be useful later.

1function getSchema(request) {
2  var fields = getFields(request).build();
3  return { schema: fields };
4}
5
6
7function getFields(request) {
8  var cc = DataStudioApp.createCommunityConnector();
9  var fields = cc.getFields();
10  var types = cc.FieldType;
11  
12  fields.newDimension()
13            .setId('effectiveDate')
14            .setName('effectiveDate')
15            .setDescription('date')
16            .setType(types.YEAR_MONTH_DAY);
17  
18  fields.newMetric()
19    .setId('mid')
20    .setName('value')
21    .setType(types.CURRENCY_PLN);
22  
23  return fields;
24}

The biggest thing going on, in the getData(request) function . In large projects, it's worth reorganizing the code to make it more readable. For the sake of the article, however, I left everything directly inside the getData function.

In the first lines, I extract from the request object, the currency to be queried and the date range. From the request object, I also extract information regarding the requested fields. Our function must return values only for those, fields it has been asked for.

After that comes the most important part of the function. Using the UrlFetchApp object, I retrieve data from the bank's API. Finally, we format the data from the API and return it.

1function getData(request) {
2    var currencyCode = request.configParams.currencyCode;
3    var startDate = request.dateRange.startDate;
4    var endStart = request.dateRange.endDate;
5
6
7    var requestedFieldIds = request.fields.map(function(field) {
8        return field.name;
9    });
10    var requestedFields = getFields().forIds(requestedFieldIds);
11
12
13    var url = [
14      'https://api.nbp.pl/api/exchangerates/rates/A', currencyCode, startDate, endStart, '?format=json'
15    ];
16
17
18    try {
19        var response = UrlFetchApp.fetch(url.join('/'));
20    } catch (err) {
21      console.log(err);
22        return {};
23    }
24
25
26    var rates = JSON.parse(response).rates;
27
28
29    var returnData = {
30        schema: requestedFields.build(),
31        rows: []
32    };
33    rates.forEach(function(rate){
34      var newRow = [];
35      requestedFieldIds.forEach(function(fieldName){
36        if(fieldName == 'effectiveDate') {
37          newRow.push(rate[fieldName].replace(/-/g, ''))
38        } else {
39          newRow.push(rate[fieldName])
40        }
41      })
42      returnData.rows.push({values: newRow});
43    });
44
45    return returnData;
46}

All the code can be found in the github repository. The files prepared this way, are now ready to create a connector. All you need to do is to press the "Deploy" button in Google Script. You can also test such a connector. Just click on the arrow next to the "Deploy" button. From the list, select "Test deployments." There you just need to copy the identifier. In the Google report, as a data source, you need to select "Create your own" and paste the previously specified identifier.

Summary

Looker Studio (formerly Data Studio) is becoming a powerful analytical tool, giving us great possibilities. They become even greater when we learn to plug our own data into it. In my professional practice, it happened that a client preferred to get data there, which he could manipulate in any way he wanted, rather than using a custom dashboard. I recommend trying it out.