How to run a SQL query on Chargebee API

In this topic you will learn how to retrieve customer data (including custom fields) from the Chargebee API. Make sure to have an Chargebee API key available as well as Invantive Query Tool to execute the Chargebee queries:

Chargebee log on

Invantive SQL covers the Chargebee API across dozens of APIs. Practically all endpoints of the API v2 are covered.

Chargebee data can be downloaded but also uploaded. This enables SaaS companies to fill a data warehouse with subscription, billing and payment data from all payment sources. The SQL driver also features Power BI reports and dashboards on the Chargebee API.

Data can not only be downloaded, but also loaded into Chargebee using simple SQL statements that directly translate into Chargebee API calls. This enables SaaS companies to easily upload data from their backoffice ERP system into Chargebee and keep the data synchronized: subscriptions, accounts, payments, etc.

Prepare API Key

An API key is required to identify your user on the API. No Chargebee data can be retrieved without credentials. The Chargebee API key is text of 30-40 characters of digits, lowercase and uppercase characters.

To acquire an API key execute the following steps:

Log on to Chargebee API

First step is to log on to Chargebee using the following steps:

  • Start the Invantive Query Tool.
  • Open the database group “Business Apps”.
  • Choose “Chargebee”.
  • Enter your API key in the first field “API Key”.
  • Enter the site name in the second field “Site”.
  • Log on “Connect”.

Retrieve First Chargebee Data

As a first step you will retrieve some payment source data, which is relatively simple seed (reference) data using the following steps:

  • Click on the bottom tab “Available Objects”.
  • Open the “PaymentSources” schema in the tree.
  • Click on the table “PaymentSources (Payment Sources)”.
  • On the right side a list of columns appears.
  • Above the columns are tabs.
  • Click on the tab “Data”.
  • The first 50 payment sources appear in a grid.

API v2 Data Formats

The SQL driver for the Chargebee API follows general conventions. Some knowledge on the data and data types is helpful even when using Invantive SQL.

Identifiers

IDs within Chargebee have various formats. The payment source ID can be something like “pm_16CHQ2S9FKpykauA” with a similar format ID on Events like “ev_6olyVSIlySWm57D”. Note that the first two characters are contained in the table name, like “pm” for “Payment” and “ev” for “Event”. However, the ID column of other tables like a price plan typically has a human-readable code format like “agency-gbp-year” and will be visible in the user interface. The same holds for customers.

Amounts

Data from the Chargebee API has a specific format for amounts. All amounts are returned in cent units. The Invantive SQL driver automatically and fully transparently converts the amounts between currency and cent units.

Custom Fields

The Chargebee API supports custom fields on:

  • Add-ons
  • Customers
  • Plans
  • Subscriptions

All custom fields have a text format. Since Chargebee does not yet provide metadata on the custom fields, Invantive SQL provides additional tables to retrieve the custom field values:

  • AddonCustomFieldValues
  • CustomerCustomFieldValues
  • PlanCustomFieldValues
  • SubscriptionCustomFieldValues

For every combination of record and custom field, one record is present in these custom field tables. For example, when the Chargebee subscription type has two custom fields “erp_id” and “chamber_of_commerce”, the table SubscriptionCustomFieldValues will have up to two rows for every customer.

Simple Query on Chargebee API

The data from Chargebee can easily be combined into new reports. For example, the following query provides you with a list of subscription-based invoices, including the customer VAT number:

select ctr.last_name
,      ctr.vat_number
--
,      ssn.plan_id
,      ssn.mrr
--
,      ive.amount_due
,      ive.amount_paid
,      ive.currency_code
,      ive.po_number
from   invoices ive
join   customers ctr
on     ctr.id = ive.customer_id
join   subscriptions ssn
on     ssn.id = ive.subscription_id

SQL Query using Chargebee Custom Fields

Some Chargebee data contains custom field values. Since there can be multiple custom field values for every main entity, it can be handy to pivot the custom field values. This especially holds when synchronizing the data to a data warehouse or operational data store in a traditional relation data model. The following sample shows how to pivot the Chargebee customer data:

select pvt.customer_id
,      pvt.erp_id
,      pvt.chamber_of_commerce
from   customers cmr
join   customercustomfieldvalues
pivot  ( min(value)
         for key
         in
         ( [erp_id]
         , [chamber_of_commerce]
         )
       ) pvt
on     1=1
and    cmr.id = customer_id

Register new Chargebee customer through API and update

However, as said before, the SQL driver for Chargebee API not only covers data retrieval, but also enables DML (data manipulation language) on Chargebee. The following statement creates a new customer in Chargebee:

insert into customers
( id
, last_name
)
values
( 13031406
, 'Invantive BV'
)

And the following statement changes the first name of the customer:

update customers
set    first_name = 'John'
where  id = 13031406

--
-- Ensure data is not retrieved from cache.
--
set use-http-memory-cache false

set use-http-disk-cache false

select *
from   customers
where  id = 13031406

Note that the ID is provided in the insert statement and used by Chargebee.