Add customers and subscriptions manually using the custom data source

Many businesses have customers who are invoiced outside their subscription billing system. With custom data source you can add these customers and their subscription billing data into ChartMogul through either our UI or via API integration. If you built your own subscription billing in-house, or want to build your own integration for any reason, then you would also use a custom data source.

However, this article is focused on explaining how to add your customers and subscription billing data into ChartMogul manually using the custom data source. Please follow the steps mentioned below to get started.

Steps

  1. Navigate to data platform
  2. Add a new custom data source
  3. Add a customer
  4. Add a plan
  5. Add an invoice
  6. Add invoice line items
  7. Add transactions
  8. Add cancellations
  9. View subscriptions
  10. Understanding how line items and transactions impact different aspects in ChartMogul
  11. Edit existing data

 

If you are looking for advanced use cases supported by custom data source please click here - https://help.chartmogul.com/hc/en-us/articles/360012519560

 

1. Navigate to data platform

The first step in importing your subscription billing data manually is to move to the data platform tab. Click the data platform icon on the left menu to navigate to the data platform tab

Alternatively, you can click the link below to directly move to the data platform tab - https://app.chartmogul.com/#/data-platform


2. Add a new custom data source

Only Admin users have full access to all features within Data platform

Once you navigate to the data platform tab, you can see the list of existing data sources. There is also an option to add a new data source. 

Screenshot_2020-11-01_at_23.14.27.png

 

When you click the "Add a data source" button, a create new data source modal comes up with options to add different types of data sources. In the popup, select "Custom data source" as your data source type.

Screenshot_2020-03-12_12.59.23_copy.png

Provide a unique name for your data source and click on NextScreenshot_2020-03-12_13.07.48.png

Once you have added a new data source, click on it to navigate to the data source page. Alternatively, you can click the Data sources > Your custom data source option on the left menu bar.

Data source page has options to view and manage

  • Customers
  • Invoices
  • Invoice Line Items
  • Transactions
  • Cancellations
  • Subscriptions
  • Plans

 3. Add a customer

The next step is to import your customers into the custom data source.

To add a new customer navigate to the Custom data source > Customers tab and click the "NEW CUSTOMER" button at the top.

Add the customer details in the new row. Provide a Name and a unique Customer ID and fill in the other optional fields such as Company, Email, etc. Once the details are entered please click the green tick button to add the new customer.

Note: The other fields though optional will help in segmenting your customer data in a better way and unlock more features such as free trial tracking.

Once the customer details are submitted, the Import status notifies if the data was processed correctly or not. On successful processing, the Import status is updated to Processed.

The important fields in the customer table and their supported values are summarized in the table below.

 

Column Name Description Field Type Value
Name Name of the customer - typically the first and last name of the primary contact for this client Required 1. Can have alphanumeric and special characters
2. Should be greater than 0 and less than 255 characters
Company name Company name is required when the client is an organization and not an individual Not required 1. Can have alphanumeric and special characters
2. Should be greater than 0 and less than 255 characters
Email Email address for this customer Not required  Should be a valid email address
Customer ID A unique identifier for this customer - typically the customer ID from your billing system Required 1. Should be unique
2. Can have alphanumeric and special characters
3.Should be greater than 0 and less than 255 characters
Lead created at Date when the lead was created. We use this information to generate lead volume reports Not required Choose the date from the dropdown or enter manually in the YYYY-MM-DD format
Free trial started at  Date when the free trial started. We use this information to generate reports of your free trials and trial-to-paid conversions  Not required  Choose the date from the dropdown or enter manually in the YYYY-MM-DD format

 

Note: The default sorting of the rows in the UI is based on the created time (not visible in the UI) and it may be different from the order in which you have uploaded the data.

4. Add a plan

The next step is to add a subscription plan. To create a new subscription plan manually, navigate to the Custom data source > Plans tab and click the "NEW PLAN" button at the top.

Add the plan details in the new row as shown in the picture below. Provide a Name, a unique Plan ID, Interval Count and Interval Unit. Once the details are entered please click the green tick button to add a new plan.

Please note that ChartMogul only accepts day, month or year as valid interval units for a plan. Interval count should be a positive integer greater than zero. Below are some examples that help understand the usage of interval unit and interval count for different plan types.

Ex 1: For a weekly plan, please choose the interval unit as a day and set the interval count as 7

Ex 2: For a quarterly plan, please choose the interval unit as a month and set the interval count as 3

Ex 3: For a yearly plan, please choose the interval unit as a month and set the interval count as 12. Alternatively, you choose the interval unit as a year and set the interval count as 1.

Once the plan details are submitted successfully, a plan UUID is automatically generated.

 

5. Add an invoice

The next step is to add an invoice for a customer. Invoices along with invoice line items are used to generate subscriptions, transactions, refunds and MRR movements in ChartMogul.

To create a new invoice manually, navigate to your Custom data source > Invoice tab and click the "NEW INVOICE" button at the top. Add the invoice details in the new row. Once the details are entered please click the green tick button to add a new invoice.

Note: Even after creating a new invoice, we will see an error in the Line items column and the import status will be shown as Failed as shown in the image below. This will change to processed after we successfully add invoice line items to this invoice. Check Section 6 for more details.

 03_-_Invoices.png

The important fields in the Invoices tab and their supported values are summarized in the table below.

 

Column Name Description Field Type Value
Customer Customer name for whom we want to add an invoice Required Choose the customer name from the dropdown
Invoice ID A unique identifier for the invoice. This is typically the invoice number in your billing system Required 1. Should be unique
2. Can have alphanumeric and special characters
3. Should be greater than 0 and less than 255 characters
Invoice date Date on which the invoice was raised Required  Choose the invoice date from the dropdown or enter manually in the YYYY-MM-DD format

Due date

Date within which the invoice needs to be paid Not Required Choose the due date from the dropdown or enter manually in the YYYY-MM-DD format
Currency 3 letter ISO code of the currency in which the invoice is billed Required

Choose the currency from the dropdown

 

6. Add invoice line items

Invoice line items along with invoices are used to generate subscriptions and MRR movements in ChartMogul. For an invoice to be processed properly it needs to have at least one line item.

To add a new invoice line item to an existing invoice, navigate to the Custom data source > Invoice line items tab and click the NEW INVOICE LINE ITEM button at the top. Add the invoice line item in the new row.

Once the details are entered please click the green tick button to save your new invoice line item.

Please see Section 10 for invoice line item examples that can be used for different use-cases, e.g. new subscriptions, renewals, etc.

The important fields in invoice line items table and their supported values are summarized in the table below.

Column name Description Field type Value
Invoice ID A unique identifier for the invoice. This is typically the invoice number in your billing system Required Choose the invoice id from the dropdown
Subscription ID A unique identifier for the subscription. This is typically the subscription ID in your billing system Required 1. Should be unique
2. Can have alphanumeric and special characters
3. Should be greater than 0 and less than 255 characters
Type Choose if it is a one time or a subscription Required Choose the type from the dropdown
Service period start Date on which the subscription starts Required for subscriptions Choose the due date from the dropdown or enter manually in the YYYY-MM-DD format
Service period end Date on which the subscription ends Required for subscriptions Choose the due date from the dropdown or enter manually in the YYYY-MM-DD format
Prorated Choose it if it is a prorated subscription or not Not required Choose yes or no from the dropdown
Amount The final amount charged towards the invoice line item Required Should be a number and can have up to 2 decimal places
Transaction fees Amount paid to your billing system and/or payment processor for this particular invoice line item Not required Should be a number and can have up to 2 decimal places
Plan Plan for this subscription Required for subscriptions Choose the plan from the dropdown
Quantity  Quantity of the invoice for this line item Not required Should be a non-zero integer
Discount amount The amount discounted when a discount is applied to this invoice line item Not required Should be a number and can have up to 2 decimal places
Tax amount Tax amount is the amount of tax applied to this invoice line item Not required Should be a number and can have up to 2 decimal places

 7. Add transactions

The next step is to add a transaction to your invoice. Transactions represent the payment or refund attempts related to an invoice. Transactions do not impact your subscriptions or MRR movements but they determine your cash flow metrics and revenue recognition schedules (if you are a Revenue Recognition customer).

To create a new transaction manually, navigate to the Custom data source > Transactions tab and click the "NEW TRANSACTION" button at the top. Add the transaction details in the new row.

Choose the invoice from the dropdown and add other fields. The type should be set to either Payment or Refund. The result can be either Successful or Failed. The date will be the date on which the transaction happened. Once the transaction details are submitted, the Import status notifies if the data was processed correctly or not.

Failed transactions

You can also include failed transaction attempts for an Invoice to enable reporting on failed charges in ChartMogul.

 

8. Add cancellations

If you want to cancel an existing subscription for a customer you can do that from the Cancellations tab.

To create a new cancellation manually, navigate to the Custom data source > Cancellations tab and click the "NEW CANCELLATION" button at the top. Add the cancellation details in the new row.

Choose the customer for whom you want to add a cancellation and add the corresponding subscription id for cancellation. Add the date on which you want to cancel the subscription and click the green button to save your cancelation. Once the cancellation details are submitted, the Import status notifies if the cancellation was processed correctly or not.

9. View subscriptions

To view your existing subscription details for each customer navigate to the Custom data source > Subscriptions tab.

You can see the list of existing subscriptions for each customer and you can also cancel a subscription by clicking on the Cancel Subscription button to the right.

10. Understanding how line items and transactions impact different aspects in ChartMogul

Invoice line items form the core of how ChartMogul processes recurring and non-recurring billing information.

MRR Calculation

ChartMogul uses several attributes from the invoice line items to calculate the MRR. Below, we describe some of the attributes in the context of their impact on MRR calculations.

  • Amount  - The amount billed for a subscription is the primary driver in calculating MRR.
  • Transaction Fees - Transaction fees are not deducted from MRR
  • Plans - With monthly plans the MRR is simply the price paid each month for the subscription. If customers are paying for plans with longer billing intervals (e.g. 12 months) ChartMogul simply divides the amount paid for the subscription by the number of months in the subscription period.
  • Discounts - We assume that discounts have already been deducted from the specified Amount value. For this reason, if Discount amount is specified it doesn’t impact MRR but enables more detailed analysis in ChartMogul.
  • Taxes - Taxes must be excluded from the calculation of MRR. If specified, Tax amount is used to deduct the tax prior to the calculation of MRR.
  • Quantity - Subscription quantity does not factor in the calculation of MRR. If specified, quantity is used to generate the Subscriptions Quantity and Quantity Churn Rate charts.
  • Prorated - Prorated items in invoices need to be explicitly identified for us to calculate their MRR contribution correctly. If specified as Yes the Prorated attribute impacts the calculation of MRR. For more details on how proration works, read our tutorial on adding pro-rated invoices.
  • One-time payments are excluded from MRR.

Mentioned below are some basic scenarios and how to define line items for each of them

Scenario 1: Subscribe a customer to a plan - Monthly

  • Your customer Wayne Enterprises buys a license of your monthly bronze plan for the month of October starting from October 1st, 2018
  • Your monthly bronze plan is $100
  • In this case, MRR = $100

For the above customer add these details as an invoice line item

Line Item Value
Invoice JL_FG_13400001 (Wayne Enterprises)
Subscription ID  jl_subid_01
Type  Subscription
Prorated No
Service period start 01/10/18
Service period end 01/11/18
Amount $100
Plan JL Bronze Plan Monthly (jlbronzemonthly)
Quantity 1

 

Scenario 2: Subscribe a customer to a plan - Annual

  • Your customer Daily Metropolis buys an annual bronze plan starting from Dec 1st, 2018
  • Your annual bronze plan is $1050 that includes a tax of $50
  • You have also provided a discount of $200 on the total purchase amount
  • The transaction fee is $40
  • MRR = [Amount charged - Tax amount] / Plan duration in months
  • In this case MRR = [$850 - $50]/ 12 = $66.67

For the above customer add these details as an invoice line item

Line Item Value
Invoice JL_FG_13400002 (Daily Metropolis)
Subscription ID  jl_subid_02
Type  Subscription
Prorated No
Service period start 01/12/18
Service period end 01/12/19
Amount $850
Transaction fees $40
Plan JL Bronze Plan Annual (jlbronzeannual)
Quantity 1
Discount amount $200
Tax amount $50

 

Scenario 3: Renew an existing subscription

  • Your customer Wayne Enterprises is currently on a monthly bronze plan with a charge of $100 per month for the month of October, starting 1st of October, 2018
  • At the start of the new month 1st of November, the customer has renewed their existing subscription
  • In this case, MRR will remain $100

For the above customer add these details as an invoice line item

Line Item Value
Invoice JL_FG_13400004 (Wayne Enterprises)
Subscription ID  jl_subid_01
Type  Subscription
Prorated No
Service period start 01/11/18
Service period end 01/12/18
Amount $100
Plan JL Bronze Plan Monthly (jlbronzemonthly)
Quantity 1

 

 

Note: Please ensure that the subscription id does not change during a renewal

11. Edit existing data

If there is a failure in processing the information or if there are any issues with data entered, you can edit the data in any of the tabs. If you want to edit data, navigate to that tab and click on the edit icon in the row. Click on the green tick to submit your changes.

Note: Some fields cannot be edited once created successfully - e.g. Customer ID.

04_-_Edit_Customer.png05_-_Edit_Customer_in_Row.png