Atlassian Marketplace Reporting with Google Data Connector

As a vendor with a product on Atlassian’s Marketplace, I am obviously keen to know how the product is performing. Personally, I am interested in answering questions like active trial numbers, the number of conversions, year on year trends, plus more. All these questions are important to answer to enable focus on achieving the most growth.

By integrating with the Atlassian marketplace reporting API, we managed to show our performance in real-time. For example, the following diagram shows how many active trials we have at a given time. It also shows how many (and which) clients have canceled, expired, or converted (paid).

Old Process

Before having this real-time reporting, process of getting insights from the report was manual and tedious. It involved navigating to Marketplace Reports > Licenses on a daily basis via the link on left.

Filter criteria is then chosen, as shown in the screenshot below followed by extracting the report into a CSV file.

Data in the CSV can be used to display different charts / graphs etc.

My aim was to find a way to automate this process with either pre-defined filter choices or provide filter criteria as selectable options.

Marketplace API’s

I started exploring how Atlassian Marketplace APIs can be leveraged to automate this reporting. There are quite a few APIs on offer here but the most relevant ones for my purposes were Export Licenses and Export Transactions.

The export licenses can provide most of the information related to the addon, contact details of the customer, billing details of the customer, the channel used, and the referrer domain. However, if you are interested in purchase details like sale date, type, etc then you may also have to use Export Transactions.

The response from Export Licenses is an array of licenses. The structure of the response is as per below:

{
  "addonLicenseId": "<string>",
  "hostLicenseId": "<string>",
  "licenseId": "<string>",
  "addonKey": "<string>",
  "addonName": "<string>",
  "hosting": "<string>",
  "lastUpdated": "<string>",
  "licenseType": "<string>",
  "maintenanceStartDate": "<string>",
  "maintenanceEndDate": "<string>",
  "status": "<string>",
  "tier": "<string>",
  "contactDetails": {
    "company": "<string>",
    "country": "<string>",
    "region": "<string>",
    "technicalContact": {
      "email": "<string>",
      "name": "<string>",
      "phone": "<string>",
      "address1": "<string>",
      "address2": "<string>",
      "city": "<string>",
      "state": "<string>",
      "postcode": "<string>"
    },
    "billingContact": {
      "email": "<string>",
      "name": "<string>",
      "phone": "<string>",
      "address1": "<string>",
      "address2": "<string>",
      "city": "<string>",
      "state": "<string>",
      "postcode": "<string>"
    }
  },
  "partnerDetails": {
    "partnerName": "<string>",
    "partnerType": "<string>",
    "billingContact": {
      "email": "<string>",
      "name": "<string>",
      "phone": "<string>",
      "address1": "<string>",
      "address2": "<string>",
      "city": "<string>",
      "state": "<string>",
      "postcode": "<string>"
    }
  },
  "attribution": {
    "channel": "<string>",
    "referrerDomain": "<string>",
    "campaignSource": "<string>",
    "campaignMedium": "<string>",
    "campaignName": "<string>",
    "campaignContent": "<string>"
  },
  "evaluationOpportunitySize": "<string>",
  "evaluationLicense": "<string>",
  "daysToConvertEval": "<string>",
  "evaluationStartDate": "<string>",
  "evaluationEndDate": "<string>",
  "evaluationSaleDate": "<string>"
}

Most of the fields above provide information that can be aggregated to get the metrics. e.g. The sum of all records with “Licence Types” as “Commercial” is the paid subscriptions OR the sum of all records with the “Status” as “Cancelled” is discontinued subscriptions.

However, for some other metrics like “Active Trials”, raw data needs to be processed to get the answer. One way of getting `Active Trials`=
Total records of the queried API with the specified date range
(minus)- All the current paid subscriptions which is Sum of all Licence Types as Commercial
(minus)- All inactive records which is Sum of all records with Status as inactive
(minus)- All discontinued subscriptions which is Sum of all records with Status as canceled.

EvaluationSaleData can also be used to determine when the new paid customers have been acquired.

Authentication

The Marketplace API uses HTTP basic authentication. The username is your Atlassian Account email and the password is a generated API token. Once authenticated, you can view and modify most properties of your apps and your account. If you are not logged in, you are considered an unauthenticated user and will not be able to edit apps or vendor information.

Dashboard & Visualisation Tool

The next problem to solve was how to visualize this data from the API sources. There are a number of paid tools available like Klipfolio, Dash, Databox, Kibana, etc. I chose Google Data Studio because it was free and fully customizable. Moreover, the aim of this article is not to compare different visualization tools. So I chose Data Studio as a starting point. I might go into the comparisons in another article. For now, let me take you through my journey of using data studio.

In order to connect my internet-accessible data source, in this case, the Marketplace APIs to the data studio, I had to build a Google Data Studio connector. The connector needs to follow a certain specification which can be found here.

And it requires knowledge of App Scripts and there are many resources on getting familiar with App Scripts however, I found the best way is to use Codelab. There is a great lab on how to build a connector for data studio here. This is what I followed to get a basic understanding of how to build the connector. It was surprising how straightforward it was to build a basic connector. However, as with everything the devil is in the detail, and depending on the objective the complexity may increase.

The first thing for me to figure out was how to make my data connector secure as I am building for private data. The community connector specification lists the required functions to build the connector including the different authentication options. There are six supported authentication methods:

• OAuth 2.0
• Path/Username/Password
• Username/Password
• Username/Token
• Key
• None

As Marketplace supports basic authentication, the Username and Token method were sufficient for my purposes. I did successfully implement the OAuth method.

I won’t go into the detail of the app script to write the basic connector as there are already plenty of resources that describe that. However, I think of the things that I found very helpful was the logger feature. I used it a lot while I was experimenting with the different connector specification functions and to understand how they work.

Just one of the examples of using the Logger.log() function.

var userProperties = PropertiesService.getUserProperties();
Logger.log(userProperties.getProperty('dscc.username'));
Logger.log(userProperties.getProperty('dscc.token'));

It allows you to see all your executions under My Executions in your App Script project.

One of the sample reports that I built using the connector looked something like this (as shown at the beginning):

With MarketPlace Licence API, I also had to code some scenarios to handle metrics for fields that are optional. e.g. for some records “channel” is not valid and that will throw an error while the studio is rendering the data.

You can specify the date range with the GET request of the Licence Marketplace API. This date range can be specified in the getConfig() function as inputs and retrieved while invoking the API within the getData() function. However, Data Studio also offers a built-in date range feature that can be enabled with this line in the getConfig() function.

config.setDateRangeRequired(true);

Once the above is done, the date range can be included in the Data Studio reporting using the Date Range control.

The date range control will appear like this on the report:

This date range selection is passed as a request to the getData() function and can be accessed and utilized using the following format:

request.dateRange.startDate
request.dateRange.endDate

One other thing worth mentioning is that I also had to base64 encode the token to be used in the “Authorization” header. I did this using the Utilities library part of App Scripts.

var username = userProperties.getProperty('dscc.username');
var token = userProperties.getProperty('dscc.token');
var stringToken = username + ':' + token;
var base64Token = Utilities.base64Encode(stringToken);

There are however still things that I am not quite sure the best way of achieving. E.g if you look at the bar chart, the data is segmented weekly. This is the graph that gets generated with me manually keeping track of data every week.

The Marketplace API currently doesn’t provide this segmented view. So either I’ll have to code this within the connector or I am yet to use the full power and features of Data Studio.

There may be a way of doing this natively within the report by creating a new field within the metrics but that’s something I am still investigating.

Hope the above article was helpful to anyone reading and I will continue to post learnings from the next iteration of connector in later parts. I am planning to cover topics related to publishing the connector, embedding the report on site that supports the HTML iFrame tag etc.

I am also interested to hear if there are any specific areas of interest or any other challenges that you may have faced yourself related to this topic.