The Kill Bill analytics plugin provides business analytics and reporting capabilities. You can use this plugin to create reports and view them via Kaui. Not only that, it allows creating reports against external databases as well. This document explains how to install and use this plugin.

Prerequisites

  • Ensure that you have Kill Bill, Kaui, and the database set up as explained in the Getting Started Guide.

  • Ensure that you have cURL installed. If you are on Windows, we recommend that you use Git Bash to run the cURL commands.

Analytics Plugin Internals

Kill Bill maintains a separate reporting database for analytics purposes. The tables in this database are meant to be used as building blocks for reports. Unlike other tables in the system, this set of tables is stable and potentially changes only between major revisions of Kill Bill. This database is kept current in realtime.

The analytics plugin is a notification plugin. It listens to events generated by Kill Bill when any system change occurs and rebuilds the analytics tables upon receipt of events. So, for example, if a new account is created, Kill Bill triggers an ACCOUNT_CREATION event. The analytics plugin listens to this event and on receipt updates the analytics tables based on the Kill Bill tables.

Plugin Installation

A plugin can be installed either via Kaui or KPM. In order to install a plugin via Kaui, please refer to the Plugin Installation Instructions document. In order to install a plugin via kpm, you may follow the steps given below:

  1. Ensure that you have kpm installed as explained here.

  2. Install the plugin using the following kpm command (Replace <path_to_install_plugin> with the path where you want to install the plugin. This path should match the path specified by the org.killbill.osgi.bundle.install.dir property in the Kill Bill Configuration):

    kpm install_java_plugin analytics --destination=<path_to_install_plugin>
  3. Verify that the plugin is installed properly. This can be done by ensuring that the plugin JAR is present at the path_to_install_plugin specified in the previous step. Alternatively, you can also open Kaui, hover over the plug icon and click on analytics. This should display the following screen: analytics plugin in kaui

  4. If you would like to make changes to the plugin, you can clone the plugin code, build and deploy it as explained here.

Database Configuration

As mentioned earlier, the analytics plugin uses its own reporting tables. To create these tables, please follow the steps given below:

  1. Connect to the Kill Bill database.

  2. Run the analytics plugin DDL.

The "Analytics Tables" section provides an overview of some of the important tables in the analytics database.

Plugin Configuration

You can configure the following properties related to the analytics plugin in the Kill Bill Configuration File:

Property NameDescriptionDefault Value

org.killbill.notificationq.analytics.tableName

The name of the notification queue table

notifications (Same as Kill Bill notification queue table)

org.killbill.notificationq.analytics.historyTableName

The name of the notification queue history table

notifications_history (Same as Kill Bill notification queue history table)

org.killbill.analytics.lockSleepMilliSeconds

How long to sleep between retries to get the lock. See lock notes.

100

org.killbill.billing.plugin.analytics.referenceCurrency

The reference currency. See Currency Conversion

USD

Note
Note: All the properties specified above are optional and the analytics plugin uses suitable defaults in case they are not specified. We however encourage you use different notification queue tables by specifying the org.killbill.notificationq.analytics.tableName and org.killbill.notificationq.analytics.historyTableName properties.

In addition, the analytics plugin also allows some optional tenant level configuration. This can be done by executing the following API endpoint via cURL:

curl -v \
     -X POST \
     -u admin:password \
     -H 'X-Killbill-ApiKey: bob' \
     -H 'X-Killbill-ApiSecret: lazar' \
     -H 'X-Killbill-CreatedBy: admin' \
     -H 'Content-Type: text/plain' \
     -d '!!org.killbill.billing.plugin.analytics.api.core.AnalyticsConfiguration
  refreshDelaySeconds: 10
  lockAttemptRetries: 100
  rescheduleIntervalOnLockSeconds: 10
  enablePartialRefreshes: true
  enableTemplateVariables:false
  blacklist:
    - 468e5259-6635-4988-9ae7-3d79b11fc6ed
    - f7da09af-8593-4a88-b6d4-1c4ebf807103
  highCardinalityAccounts:
    - a8e594e5-1b78-4c2d-876b-f09ec36c611c
    - 31ea22c7-19ae-4316-a432-5e6319e49f97
  ignoredGroups:
    - FIELDS
  pluginPropertyKeys:
    killbill-stripe:
      1: processorResponse
      2: avsResultCode
      3: cvvResultCode
  databases:
    warehouse:
      type: trino
      url: jdbc:trino://example.net:8080/hive/sales?user=admin' \
    http://127.0.0.1:8080/1.0/kb/tenants/uploadPluginConfig/killbill-analytics

This configures the following:

Property NameDescriptionDefault Value

refreshDelaySeconds

The delay in seconds, before starting to refresh analytics tables after an event is received. For workflows with lots of successive events (e.g. create account, add payment method, create payment), this makes sure that the latest state is available when starting the refresh.

10

lockAttemptRetries

The number of retries to get the lock. See lock notes.

100

rescheduleIntervalOnLockSeconds

The duration in seconds after which the refresh job should be scheduled in case the lock is not available. See lock notes.

10

enablePartialRefreshes

Whether partial refreshes are allowed. If true, the plugin refreshes only a subset of the analytics tables based on the received event. If false, all the analytics tables are updated.

true

enableTemplateVariables

Whether template variables should be allowed in raw SQL queries while creating reports based on SQL queries. Note that this could be prone to SQL injection and should only be enabled in trusted environments.

false

blacklist

A list of account IDs to ignore.

-

highCardinalityAccounts

A list of account IDs with a high cardinality (account with 100k+ subscriptions). This is a performance hint to the plugin and tells the plugin to look up individual subscription rows instead of querying everything based on account_record_id.

-

ignoredGroups

A list of groups that should be ignored for refresh. Possible values are FIELDS, INVOICES, INVOICE_AND_PAYMENTS,OVERDUE, OTHER, SUBSCRIPTIONS, ALL. Thus, database tables corresponding to these groups will be ignored while performing a refresh.

-

pluginPropertyKeys

A payment plugin name and plugin properties related to the payment plugin to be stored into the analytics tables. So, if a payment plugin populates these values, these are fetched and stored in the analytics_payment_auths table.

1:processorResponse, 2:avsResultCode, 3:cvvResultCode, 4:payment_processor_account_id 5:paymentMethod

databases

Specifies the database configuration for external (remote) databases against which reports can be created. The database type and url needs to be specified as part of the database configuration. The analytics plugin currently only supports trino databases. See the "Advanced Report Creation Options" section.

-

Lock Notes: Some critical operations in Kill Bill use a database-based global lock to ensure a single Kill Bill node performs a given operation at a time. Bumping the lockSleepMilliSeconds parameter could help if these critical operations (refresh) take a long time. By default, if a refresh is in progress (i.e. lock is taken) and another node tries to refresh the same account, it will attempt lockAttemptRetries times to grab the lock, sleeping lockSleepMilliSeconds in between retries, before giving up. If the lock is taken and rescheduleIntervalOnLockSeconds is configured, it will reschedule itself rescheduleIntervalOnLockSeconds in the future, before trying the above algorithm.

Reports

Once the plugin is installed and configured, you can access it via Kaui. By default, the analytics plugin does not display any reports. You can either install our canned reports or create your own custom reports. Custom reports can be created by specifying a table/view in the local database or by specifying an SQL query to be executed on a remote database. Depending on the report type, the database table/view/SQL query needs to include certain columns.

Report Types

The analytics plugin supports three types of reports as explained below:

  • COUNTERS: Such reports are displayed as pie charts.

    • Local: For a report based on a local database, the columns tenant_record_id, label, and count are mandatory.

    • Remote: For a report based on a remote database, only the columns label, and count are mandatory. You can optionally specify the placeholder TENANT_RECORD_ID in the query which will be replaced by the tenantRecordId from the Kill Bill database before the query is executed.

  • TIMELINE: Such reports are displayed as line charts.

    • Local: For a report based on a local database, the columns day (or ts) and tenant_record_id are mandatory.

    • Remote: For a report based on a remote database, the column day (or ts) is mandatory. You can optionally specify the placeholders TENANT_RECORD_ID, START_DATE, and END_DATE in the query which will be replaced by the tenantRecordId from the Kill Bill database and dates specified by a user before the query is executed.

  • TABLE: This is used for rendering underlying table data.

    • Local: For a report based on a local database, the tenant_record_id column is mandatory.

    • Remote: For a report based on a remote database, there are no mandatory columns. You can optionally specify the placeholder TENANT_RECORD_ID in the query which will be replaced by the tenantRecordId from the Kill Bill database before the query is executed.

Installing Canned Reports

As explained earlier, we provide a set of canned reports that can be useful as a starting point. On non-Windows machines, these can be installed by running the seed_reports.sh script. On Windows machines, you can install a report by opening the desired report sub-directory from the reports directory and creating the view, stored procedure and report as explained in the next section.

Creating Custom Reports

As mentioned earlier, the analytics plugin allows creating custom reports as per your requirements. A custom report can be created by executing API endpoints or via Kaui. This section covers report creation via API endpoint execution.

There are basically three steps in creating a report:

  1. Create a table/view corresponding to the report.

    Suppose you want to view the number of accounts created per day. This is typically a timeline report and needs to include the tenant_record_id and day columns.

    1. Identify the SQL query corresponding to the report:

      select
        tenant_record_id
      , date_format(created_date, '%Y-%m-%d') as day
      , count(*) as count
      from analytics_accounts
      where report_group = 'default'
      group by 1,2
      order by 1,2 asc
      ;
    2. Create a view corresponding to this query as follows:

      create or replace view v_new_accounts_per_day as
      select
        tenant_record_id
      , date_format(created_date, '%Y-%m-%d') as day
      , count(*) as count
      from analytics_accounts
      where report_group = 'default'
      group by 1,2
      order by 1,2 asc
      ;
  2. Create the actual report.

    You can then create the actual report by executing the following endpoint:

    curl -v \
         -X POST \
         -u admin:password \
         -H "X-Killbill-ApiKey:bob" \
         -H "X-Killbill-ApiSecret:lazar" \
         -H 'Content-Type: application/json' \
         -d '{"reportName": "new_accounts_per_day",
              "reportPrettyName": "New accounts created (per day)",
              "sourceTableName": "v_new_accounts_per_day"}' \
         "http://127.0.0.1:8080/plugins/killbill-analytics/reports"

    This creates a report called new_accounts_per_day based on the v_new_accounts_per_day view. Refer to the "Report Creation Options" section for a description of all the fields.

    Alternatively, you can directly specify an SQL query (instead of creating a view) while creating the report. Refer to the "Advanced Report Creation" section for further information.

  3. Create a stored procedure for the report.

    This is an optional step and should be followed for expensive view queries.

    1. Create a stored procedure that populates a table from the view as follows:

      create table new_accounts_per_day as select * from v_new_accounts_per_day limit 0;
      
      drop procedure if exists refresh_new_accounts_per_day;
      DELIMITER //
      CREATE PROCEDURE refresh_new_accounts_per_day()
      BEGIN
      delete from new_accounts_per_day;
      insert into new_accounts_per_day select * from v_new_accounts_per_day;
      END;
      //
      DELIMITER ;

      This creates a table called new_accounts_per_day and a stored procedure called refresh_new_accounts_per_day which populates the new_accounts_per_day table from the v_new_accounts_per_day view.

    2. Create your report using the table name from the previous step and a refresh schedule as follows:

      curl -v \
           -X POST \
           -u admin:password \
           -H "X-Killbill-ApiKey:bob" \
           -H "X-Killbill-ApiSecret:lazar" \
           -H 'Content-Type: application/json' \
           -d '{"reportName": "new_accounts_per_day",
                "reportPrettyName": "New accounts created (per day)",
                "sourceTableName": "new_accounts_per_day",
                "refreshProcedureName": "refresh_new_accounts_per_day",
                "refreshFrequency": "DAILY",
                "refreshHourOfDayGmt": 4}' \
           "http://127.0.0.1:8080/plugins/killbill-analytics/reports"

      This creates a report called new_accounts_per_day based on the new_accounts_per_day table. The refresh_new_accounts_per_day stored procedure refreshes the table from the view. It runs everyday at 4 AM GMT. Refer to the "Report Creation Options" section for a description of all the fields.

      You can alternatively create the report with a different refresh schedule as follows:

      curl -v \
           -X POST \
           -u admin:password \
           -H "X-Killbill-ApiKey:bob" \
           -H "X-Killbill-ApiSecret:lazar" \
           -H 'Content-Type: application/json' \
           -d '{"reportName": "new_accounts_per_day",
                "reportPrettyName": "New accounts created (per day)",
                "sourceTableName": "new_accounts_per_day",
                "refreshProcedureName": "refresh_new_accounts_per_day",
                "refreshFrequency": "HOURLY"}' \
           "http://127.0.0.1:8080/plugins/killbill-analytics/reports"

      This specifies that the stored procedure should run on an hourly basis (it will run at 5' past the hour).

Advanced Report Creation Options

You can create a report based on SQL executed on a remote database (warehouse) as follows:

curl -v \
     -X POST \
     -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     -H 'Content-Type: application/json' \
     -d '{"reportName": "report_historical_orders",
          "reportType": "TABLE",
          "reportPrettyName": "Historical orders",
          "sourceName": "warehouse",
          "sourceQuery": "select * from warehouse.public.orders"}' \
     "http://127.0.0.1:8080/plugins/killbill-analytics/reports"

This creates a report called report_historical_orders based on a remote database (warehouse). It executes the specified SQL query. Refer to the "Report Creation Options" section for a description of all the fields.

Note
Note: In order to create a report based on a remote database, the database first needs to be configured at a tenant level as explained in the "Plugin Configuration" section.

The above cURL command can also be used to create a report based on a local database by directly specifying an SQL query (instead of a table/view).

Report Creation Options

The following table lists all the report creation options. A subset of these options need to be specified while creating the report, depending on the report being created.

Field NameDescription

reportName

Unique identifier for your report.

reportPrettyName

Description, that will be used in Kaui.

reportType

Type of report. Possible values are COUNTERS, TIMELINE, TABLE.

sourceTableName

Name of the source table/view.

sourceName

Name of the remote database.

sourceQuery

SQL query to be executed.

refreshProcedureName

Name of the stored procedure.

refreshFrequency

Frequency with which the refresh. procedure will be executed. Possible values are HOURLY and DAILY.

refreshHourOfDayGmt

Hour of the day when the procedure will be executed.

Other Report Operations

Once a report is created, you can perform several operations on it as explained below.

Retrieve Report Configuration

You can retrieve a report configuration by specifying the report name:

curl -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     "http://127.0.0.1:8080/plugins/killbill-analytics/reports/new_accounts_per_day"

This returns the report configuration (reportName, reportPrettyName, etc. fields) for the new_accounts_per_day report.

Update a Report

You can update a report (edit all fields except reportName):

curl -v \
     -X PUT \
     -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     -H 'Content-Type: application/json' \
     -d '{"reportPrettyName": "New accounts created (refreshed at 2am GMT)",
          "refreshFrequency": "DAILY",
          "refreshHourOfDayGmt": 2}' \
     "http://127.0.0.1:8080/plugins/killbill-analytics/reports/new_accounts_per_day"

This updates the reportPrettyName, refreshFrequency and refreshHourOfDayGmt fields for the new_accounts_per_day report.

Delete a Report

You can delete a report by specifying the report name:

curl -v \
     -X DELETE \
     -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     "http://127.0.0.1:8080/plugins/killbill-analytics/reports/new_accounts_per_day"

This deletes the new_accounts_per_day report.

Download Report Data

You can download the data behind your report as csv or json:

curl -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     'http://127.0.0.1:8080/plugins/killbill-analytics/reports?name=new_accounts_per_day&format=csv'

This returns the data behind the new_accounts_per_day report in csv format.

Retrieve Report SQL Query

You can retrieve the SQL query corresponding to your report:

curl -v \
     -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     "http://127.0.0.1:8080/plugins/killbill-analytics/reports?name=report_accounts_summary&startDate=2018-01-01&endDate=2022-01-01&sqlOnly=true"

This returns the SQL query corresponding to the report_accounts_summary report.

Refresh a Report

You can refresh a report:

curl -v \
     -X PUT \
     -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     -H 'Content-Type: application/json' \
"http://127.0.0.1:8080/plugins/killbill-analytics/reports/new_accounts_per_day?shouldRefresh=true"

This refreshes the table/view corresponding to the new_accounts_per_day report.

Data API

The analytics plugin provides a Data API that supports some account level operations as explained below.

Retrieve Analytics Data for Account

You can retrieve all analytics data for an account as follows:

curl -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     "http://127.0.0.1:8080/plugins/killbill-analytics/<account_id>"

This returns the data from the analytics tables for the specified account_id.

Refresh an Account

You can force a refresh for a given account as follows:

curl -v \
     -X PUT \
     -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     "http://127.0.0.1:8080/plugins/killbill-analytics/<account_id>"

This refreshes the analytics tables for the specified account id.

Refresh all Accounts

You can refresh all accounts as follows:

curl -v \
     -X PUT \
     -u admin:password \
     -H "X-Killbill-ApiKey:bob" \
     -H "X-Killbill-ApiSecret:lazar" \
     "http://127.0.0.1:8080/plugins/killbill-analytics/"

This refreshes the analytics table for all accounts. This happens asynchronously and may take a while to complete depending on the number of accounts.

Analytics Tables

This section gives a brief overview of some of the important tables in the analytics database.

Accounts

The table analytics_accounts maintains information about Kill Bill accounts. The table doesn’t contain historic records (it will contain at most one record per Kill Bill account).

Subscriptions

The table analytics_subscription_transitions maintains information about Kill Bill subscriptions. It describes the history of changes (both past and future) on a per subscription basis. It contains all phase transitions for all subscriptions and can be used to build subscription timelines. A few remarks:

  • Given a subscription ID, events are sorted chronologically when stored (you can rely on record_id asc).

  • prev_ fields will always be NULL for creation events.

  • next_ fields will always be NULL for cancellation events.

  • A CHANGE event corresponds to a user initiated change (e.g. upgrade) whereas SYSTEM_CHANGE corresponds to a phase event (e.g. transition from trial to evergreen phase).

  • The requested_timestamp field reflects when the transition was requested; the next_start_date field reflects the effective transition time.

Invoices

The following tables maintain information about invoices posted by account:

  • analytics_invoices

  • analytics_invoice_adjustments

  • analytics_invoice_item_adjustments

  • analytics_invoice_credits

  • analytics_invoice_items

A few remarks:

  • EXTERNAL_CHARGE, FIXED, and RECURRING items are treated as regular line items and are stored in analytics_invoice_items.

  • ITEM_ADJ items are treated as invoice item adjustments and stored in analytics_invoice_item_adjustments.

  • CBA_ADJ items (gained or consumed) are treated as account credits and stored in analytics_invoice_credits.

  • CREDIT_ADJ items that are not on their own invoice are treated as invoice adjustments and are stored in analytics_invoice_adjustments. Note that these should not be mixed with account level credits; when an administrator creates an account credit (different from CBA credits generated by the system), a negative CREDIT_ADJ item is added to a new invoice. This triggers a positive CBA_ADJ item (to bring the balance back to zero), which effectively becomes the account credit (and eventually stored in analytics_invoice_credits). In this case, the CREDIT_ADJ item will not be stored directly in the analytics tables.

  • Kill Bill repair items (REPAIR_ADJ) are not stored directly in any of these tables, but are combined with the new repaired item as invoice item adjustments (in analytics_invoice_item_adjustments). For example, if a recurring item of $20 was repaired into a $5 recurring item, analytics_invoice_items would contain the original $20 line item and analytics_invoice_item_adjustments would contain a $-15 adjustment item.

  • Any invoice item has a revenue recognizable attribute which is used to tell if the item is recognizable from a finance perspective (tied to cash). This is used for credits only today and we assume that any administrator generated credit (CREDIT_ADJ and CBA_ADJ on their own invoice) is not recognizable. All other credit items, including system generated ones, are.

Definitions:

  • invoice_balance: invoice_amount_charged + invoice_amount_credited + invoice_amount_adjusted_for_account_credit - (invoice_amount_paid + invoice_amount_refunded). Note that invoice_amount_adjusted_for_account_credit is an internal adjustment that applies for account level credits only (CREDIT_ADJ items on their own invoices) and is not represented in Analytics. See the "Invoice Balance" section in the Invoice Examples document for further information.

  • invoice_amount_charged: Sum of all analytics_invoice_items, analytics_invoice_adjustments, and analytics_invoice_item_adjustments items for that invoice.

  • invoice_amount_credited: Sum of all analytics_invoice_credits items for that invoice (positive if granted, negative if used).

  • invoice_amount_paid: Sum of all analytics_payments items for that invoice.

  • invoice_amount_refunded: Sum of all analytics_chargebacks and analytics_refunds items for that invoice.

  • invoice_original_amount_charged: Sum of all analytics_invoice_items items for that invoice at the time of invoice creation.

References:

Payments

The following tables maintain information about Kill Bill payments, refunds and chargebacks:

  • analytics_payment_auths

  • analytics_payment_captures,

  • analytics_payment_purchases

  • analytics_payment_refunds

  • analytics_payment_credits

  • analytics_payment_chargebacks

  • analytics_payment_voids

Entitlement States

The table analytics_entitlement_states maintains information about Kill Bill entitlement states, including overdue statuses. The table contains historic status for a given account.

Tags and Custom Fields

The following tables maintain information about tags and custom fields:

  • analytics_account_tags

  • analytics_bundle_tags

  • analytics_invoice_tags

  • analytics_payment_tags

  • analytics_account_fields

  • analytics_bundle_fields

  • analytics_invoice_fields

  • analytics_invoice_payment_fields

  • analytics_payment_fields

  • analytics_payment_method_fields

  • analytics_transaction_fields

These tables don’t contain historic records (they will contain the current state of tags and custom fields).

Currency Conversion

Each table containing monetary columns (e.g. price, balance, …​) will also contain a currency column, expressing the currency of the monetary values. This can be difficult to work with as tables can contain rows in various currencies: any arithmetic across them won’t make any sense.

To work around this problem, the analytics plugin has the concept of converted currency, which is a global constant currency of reference. All analytics tables contain an additional column (prefixed with converted_) corresponding to each monetary column. The converted_ column stores the value in the converted currency. So, for example, the balance column (which has a monetary value) in the analytics_accounts table has a corresponding converted_balance column.

By default, the converted_ column stores the value in USD. You can however change this to any other currency as required.

For example, to use EUR as the converted currency, you need to do the following:

  1. Set EUR as the reference currency in the Kill Bill configuration file as follows:

    org.killbill.billing.plugin.analytics.referenceCurrency=EUR
  2. Populate the analytics_currency_conversion table to contain the rates you’d like to use for various currencies, for a given period of time.

    For example, the following query populates this table with the USD to EUR conversion rate of 0.88 for the time period 2022-01-01 to 2022-02-01:

    insert into analytics_currency_conversion (currency,start_date, end_date,reference_rate, reference_currency) values ('USD', '2022-01-01', '2022-02-01',0.88,'EUR');

Once the above change is done, the converted_ columns will store the monetary value in EUR using the specified conversion rate.

Health Check

The analytics plugin provides the following healthcheck related endpoints.

Checking Status

curl -v \
     -u admin:password \
     "http://127.0.0.1:8080/plugins/killbill-analytics/healthcheck"

Put out of Rotation

curl -v \
     -X DELETE \
     -u admin:password \
     "http://127.0.0.1:8080/plugins/killbill-analytics/healthcheck"

Put in Rotation

curl -v \
     -X PUT \
     -u admin:password \
     "http://127.0.0.1:8080/plugins/killbill-analytics/healthcheck"