Manage Geckoboard Dataset with python

Geckoboard is a tools for visualize your data. especially you have target and you want to hit next level. measure your sale order, purchase order, margin, profit, GMV, people engage you ads. you can update data into geckoboard very easy by google spreadsheet, excel. also have 3rd integration like trello, salesforce, google analytics youtube etc. and Dataset is include one we will focus on today.

Why is dataset?

when you need to push data into geckoboard. you can choose between you spreadsheet. or use tools that system provide to you call Dataset. the advantage of Dataset is you can create update delete with REST API. it make you can automation by coding rather than update spreadsheets by manually every time. it suits you especially when you have own platform and want to pull data from it.

Dataset qualification

• Dataset have limit about 60 request per minute. if excess limit. API will return status code 429 with error message.
• Dataset can contain up to 10 field.
• Dataset can contain up to 5000 records. when dataset exceeds record. the older one will be removed(by insert date)
• Insert or update data will accept 500 records
• You can have dataset up to 100 per account. when exceeds limit you can't add new dataset into geckoboard.

API Key

Geckoboard need authorize by API key. you can find api key in menu account in top right corner.

Let's code with python

first of all. we must install python package name requests for make our code can send requests to server.

sudo pip install requests

then we create simple method for test connection.

import requests 


def testconnection(apikey):''' this method test requests to server '''
    url = "https://api.geckoboard.com/"
    headers = {
        "Authorization": "Basic "+apikey
    }
    response = requests.request("GET", url, headers=headers)
    return response.status_code

result = testconnection('<you-api-key->')
print result

# result status code if successfull it will return 200 otherwise error occur

if that method return status 200. it mean we connect with server successfully. but i have something uncomfortable for fix api key in code. it will have problem if you upload code into public source like github. if you code javascript before you know it have something call .env file. that file will contain environment config and we can upload every file except this .env file for secure. luckily in python have package call python-dotenv

sudo pip install python-dotenv

then let import os and dotenv for import env file

import os
from dotenv import load_dotenv

create .env file in root project directory and add your api key

apikey=<you-api-key->

load .env configuration and make sure you can get api key from .env file

load_dotenv(os.path.join(os.path.dirname(os.path.dirname(__file__)), '.env'))
apikey = os.getenv('apikey')
if not apikey:
    print('can\'t get api key in env file')

the complete code should look like this

import requests 
import os
from dotenv import load_dotenv

load_dotenv(os.path.join(os.path.dirname(os.path.dirname(__file__)), '.env'))
apikey = os.getenv('apikey')
if not apikey:
    print('can\'t get api key in env file')

def testconnection(apikey):''' this method test requests to server '''
    url = "https://api.geckoboard.com/"
    headers = {
        "Authorization": "Basic "+apikey
    }
    response = requests.request("GET", url, headers=headers)
    return response.status_code

result = testconnection(apikey)
print result

# result status code if successfull it will return 200 otherwise error occur

Create Dataset

Next we should design how many data we want to create? how many field we want. in this example i choose to add 3 field. date for sale, order number, total.

def createdataset(apikey, id, payload):''' this method will create dataset in geokoboard '''
    url = "https://api.geckoboard.com/datasets/"+id
    headers = {
        "Authorization": "Basic "+apikey,
        "Content-Type": "application/json"
    }
    response = requests.request("PUT", url, headers=headers, data=payload)
    return response.status_code

after we create method. we must prepare payload

payload = {
    "fields": {
        "timestamp":{
            "type": "datetime",
            "name": "Date"
        },
        "ordernumber":{
            "type": "string",
            "name": "Order Number"
        },
        "total":{
            "type": "number",
            "name": "Total"
        }
    },
  "unique_by": ["timestamp"]
}

don't forget to formatting payload in Json format. use python package json

import json

payload = {
    # ...
}
# convert payload to json format
payload = json.dumps(payload)

call method for create dataset

payload = json.dumps(payload)
result = createdataset(apikey,'saleorder',payload)
print result
# 201 status will return if dataset successful created

Append Dataset

After we create dateset field. it's time to put data into dataset. we can use POST for append data into dataset. so let create method name appenddata

def appenddata(apikey, id, payload):''' this method will append data into dataset '''
    url = "https://api.geckoboard.com/datasets/" + id + "/data"
    headers = {
        "Authorization": "Basic "+apikey,
        "Content-Type": "application/json"
    }
    response = requests.request("POST", url, headers=headers, data=payload)
    return response.status_code

then we add data into payload. it can up to 500 records

payload = {
  "data": [
    {
      "timestamp": "2018-01-01T12:00:00Z",
      "ordernumber": "th_00001",
      "total": 819
    },
    {
      "timestamp": "2018-01-02T12:00:00Z",
      "ordernumber": "th_00002",
      "total": 409
    },
    {
      "timestamp": "2018-01-03T12:00:00Z",
      "ordernumber": "th_00003",
      "total": 164
    }
  ]
}

call method and don't forget to formatting payload into Json format

payload = json.dumps(payload)
result = appenddata(apikey,'saleorder', payload)
print result


# 200 status will return if append data successful
# ...

*** be careful : if you don't include unique_by in dataset. new records will alway append. on the other hand if you include unique_by in dataset. it will replace exist record that have same data in field unique_by

Have a look on Geokoboard

if you goto geokoboard. on menu bar on right hand you will see button Add widget you can click it and choose to import data via Dataset

then you will see new dataset name saleorder that we create appear on side bar. pick it up.

and you will see visualize data. tune it until you get data that you want. have fun with it.

Delete Dataset

in case you don't need that dataset anymore. you can delete it by use DELETE API

def deletedata(api, id):''' this method will delete dataset '''
    url = "https://api.geckoboard.com/datasets/"+id
    headers = {
        "Authorization": "Basic "+apikey
    }
    response = requests.request("DELETE", url, headers=headers)
    return response.status_code


result = deletedata(apikey,'saleorder')
print result
# 200 status will return if delete dataset successful.

Conclusion

The advantage of dataset it easy too maintain data, filter, summary and automate without manual update data on spreadsheets. give more time for your team to focus on another things without worry about update data. and the important things. you can measure data in realtime.

have a look all code on github or read documentation in geckoboard api reference