Urban Airship to BigQuery

This page provides you with instructions on how to extract data from Urban Airship and load it into Google BigQuery. (If this manual process sounds onerous, check out Stitch, which can do all the heavy lifting for you in just a few clicks.)

What is Urban Airship?

Urban Airship is a cloud-based mobile marketing platform that aims to improve customer engagement.

What is Google BigQuery?


Getting data out of Urban Airship

Urban Airship offers a REST API that lets you access data stored in the system. The API exposes 16 endpoints, including channels, segments, and schedules. To get information about static lists, for example, you could call GET /api/lists with an optional type parameter, in which you can specify user, lifecycle, or all lists.

Sample Urban Airship data

The Urban Airship API returns data in JSON format. For example, the results of a call to retrieve lists might look like this.

   "ok" : true,
   "lists" : [
         "name" : "platinum_members",
         "description" : "loyalty program platinum members",
         "extra" : { "key" : "value" },
         "created" : "2017-08-08T20:41:06",
         "last_modified" : "2017-11-01T18:00:27",
         "channel_count": 3145,
         "status": "ready"
         "name": "gold_members",
         "description": "loyalty program gold member",
         "last_updated": "2017-11-01T18:00:27",
         "channel_count": 678,
         "status": "ready"

Preparing Urban Airship data

If you don't already have a data structure in which to store the data you retrieve, you'll have to create a schema for your data tables. Then, for each value in the response, you'll need to identify a predefined datatype (INTEGER, DATETIME, etc.) and build a table that can receive them. Urban Airship's documentation should tell you what fields are provided by each endpoint, along with their corresponding datatypes.


Loading data into Google BigQuery

Google Cloud Platform offers a helpful guide you can follow to begin loading data into BigQuery. Use the bq command-line tool, and in particular the bq load command, to upload files to your datasets. The syntax is documented in the Quickstart guide for bq. You can supply the table or partition schema, or, for supported data formats, you can use schema auto-detection. Iterate through this process as many times as it takes to load all of your tables into BigQuery.

Keeping Urban Airship data up to date



And remember, as with any code, once you write it, you have to maintain it. If Urban Airship modifies its API, or the API sends a field with a datatype your code doesn't recognize, you may have to modify the script. If your users want slightly different information, you definitely will have to.

Other data warehouse options


Easier and faster alternatives


Thankfully, products like Stitch were built to move data from Urban Airship to Google BigQuery automatically. With just a few clicks, Stitch starts extracting your Urban Airship data via the API, structuring it in a way that's optimized for analysis, and inserting that data into your Google BigQuery data warehouse.