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:
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:
- Go to User Login - Chargebee.
- Choose your site on User Login - Chargebee.
- Take note of the name of the site; you will need it upon log on.
- Go to “Settings” in the left menu.
- Click on “Configure Chargebee”.
- Scroll down to “API Keys and Webhooks”:
- Click “API Keys”.
- Create an API key.
- For a test site it is most convenient to use a Full-Access Key.
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.