You’ll need to be an Admin or Owner in ChartMogul to add and manage sources. Read more about user roles and permissions.
To get accurate subscription and cashflow metrics, add each of your billing systems as a source in ChartMogul. Add a custom data source and import billing data using CSV when:
- You use a supported billing system but have customers who are billed outside of this system.
- Your billing system is not supported by ChartMogul. Billing data can also be added manually or using the Import API.
- You want to import historical data.
Here’s what we cover in this article:
Resources and further reading:
- Learn more about importing subscription data.
Formatting CSV files
To import subscription data into ChartMogul using CSV, it must be in a specific format. Prepare separate CSV files for each record type (e.g., Customers, Plans, Invoices, etc.) as they appear in Data Platform.
To get started, download a sample CSV file for the data set:
Or, download all sample files as a zip.
CSV files should be in either ASCII or UTF-8 Unicode file format.
Customers
| 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 | Accepts up to 255 alphanumeric and special characters |
| Email address for this customer | Not required | A valid email address | |
| Company | Company name is required when the customer is an organization and not an individual | Not required | Accepts up to 255 alphanumeric and special characters |
| Country | The customer’s country | Not required | Specify region name or region code. Field is not case-sensitive. |
| State | The customer’s state in the US | Not required | US States only. Field is not case-sensitive. |
| City | The customer’s city | Not required | Accepts up to 255 alphanumeric and special characters |
| Zip | The customer’s postal code | Not required | Accepts up to 255 alphanumeric and special characters |
| External ID | The unique identifier for the customer — typically the customer ID from your billing system. Column is Customer ID in Data Platform. |
Required | Accepts up to 255 alphanumeric and special characters. Must be unique. Value is permanent and cannot be changed. |
| Lead created at | Date when the lead was created. Learn more about tracking leads and trials. | Not required | Format date as YYYY-MM-DD |
| Free trial started at | Date when the free trial started. Learn more about tracking leads and trials. | Not required | Format date as YYYY-MM-DD |
Plans
| Column Name | Description | Field Type | Value |
|---|---|---|---|
| Name | The plan’s name | Required | Accepts up to 255 alphanumeric and special characters |
| Interval count | Frequency of billing interval. Value is permanent and cannot be changed. | Required | A positive integer greater than 0. To understand interval unit and interval count, here are some examples:
|
| Interval unit | The unit of billing interval. Value is permanent and cannot be changed. | Required | Specify either Day, Month, or Year. Field is not case-sensitive. |
| Plan ID | A unique identifier for the plan. Typically a plan ID from your billing system. Value is permanent and cannot be changed. | Required | Must be unique, such as your billing system's plan ID. Accepts up to 255 alphanumeric and special characters. |
Invoices
| Column Name | Description | Field Type | Value |
|---|---|---|---|
| Customer external ID | The unique identifier for the customer — typically the customer ID from your billing system. Column is Customer in Data Platform. |
Required | Accepts up to 255 alphanumeric and special characters |
| Invoice external ID | A unique identifier for the invoice — typically the invoice number in your billing system. Column is Invoice ID in Data Platform. |
Required | Must be unique. Accepts up to 255 alphanumeric and special characters |
| Invoiced date | Date on which the invoice was raised | Required | Format date as YYYY-MM-DD |
| Due Date | Date upon which the invoice must be paid | Not Required | Format date as YYYY-MM-DD |
| Currency | The customer’s billing currency | Required | 3 letter ISO code of the currency. Field is case-sensitive. |
Invoice Line Items
It is not currently possible to update Discount Description or Line Item ID using CSV upload. However, you can update these fields in-app using Data Platform.
| Column Name | Description | Field Type | Value |
|---|---|---|---|
| Invoice external ID | The unique identifier for the invoice. This is typically the invoice number in your billing system. Column is Invoice in Data Platform. |
Required | Should be unique. Accepts up to 255 alphanumeric and special characters. |
| External ID | The unique identifier for the customer — typically the customer ID from your billing system. Column is not visible in Data Platform. However, customer’s name appears in the Invoice column. |
Not Required | Should be unique. Accepts up to 255 alphanumeric and special characters. |
| Subscription external ID | The unique identifier for the subscription. This is typically the subscription ID in your billing system. Column is Subscription ID
in Data Platform. |
Required for subscriptions | Should be unique for this source. Accepts up to 255 alphanumeric and special characters. |
| Subscription set external ID | The unique identifier for subscriptions with multiple components. Column is Subscription Set ID in Data Platform. |
Not required | Should be unique for this source. Accepts up to 255 alphanumeric and special characters. |
| Type | The type of charge, subscription or non-recurring (one time) | Required | Specify either Subscription or OneTime. Field is case-sensitive. |
| Amount in cents | The final amount billed towards the invoice line item, in cents. Column is Amount in Data Platform. |
Required | Accepts only numerical characters (no decimal point). Use a negative value to add credit to a customer’s account. |
| Plan | Plan ID for this subscription | Required for subscriptions | The plan’s External ID found by navigating to Profile > Admin > Plans and locating the External ID in the table. |
| Service period start | The start date of the subscription’s service period | Required for subscriptions | Format date as YYYY-MM-DD |
| Service period end | The end date of the subscription’s service period | Required for subscriptions | Format date as YYYY-MM-DD |
| Quantity | Quantity for this line item. Quantity value does not impact calculation of MRR. | Not required | Accepts any non-zero integer. Defaults to 1. Non-prorated line items cannot have a negative quantity. |
| Proration | Specify if this charge is prorated | Required | Specify either TRUE or FALSE. Field is not case-sensitive. |
| Discount code | A reference code for the discount | Not required | Accepts up to 64 alphanumeric and special characters |
| Discount amount | The amount discounted when a discount is applied to this invoice line item | Not required | The amount in cents. Accepts only numerical characters (no decimal point). |
| Tax amount | he amount of tax applied to the invoice line item | Not required | The amount in cents. Accepts only numerical characters (no decimal point). |
| Description | A description of the services provided for one-time transactions, visible from a customer’s profile. | Not required | Accepts up to 255 alphanumeric and special characters |
| Transaction fee | Transaction fees from your billing system or payment provider. Learn more. | Not required | The amount in cents. Accepts only numerical characters (no decimal point). |
| Account Code | The unique account code of the line item used for the purposes of accounting and revenue recognition. Also called 'account number' in some systems. Currently not visible in-app and only supported for Xero. | Not required | Must be unique, such as an account number. Accepts a maximum of 30 alphanumeric characters. |
Transactions
The Transactions CSV records attempted payments and refunds for invoices and does not provide a column for transaction amount. Therefore, all invoices are understood to have been either paid or refunded in full.
| Column Name | Description | Field Type | Value |
|---|---|---|---|
| Invoice external ID | The unique identifier for the invoice. This is typically the invoice number in your billing system. Column is Invoice in Data Platform. |
Required | Must be unique. Accepts up to 255 alphanumeric and special characters. |
| External ID | The unique identifier for this transaction. This is typically the transaction ID in your billing system or payment gateway. Column is Transaction ID in Data Platform. |
Not required | Must be unique. Accepts up to 255 alphanumeric and special characters. |
| Type | The transaction’s type | Required | Specify either payment or refund. Field is case-sensitive. |
| Result | The outcome of the transaction | Required | Specify either successful or failed. Field is case-sensitive. |
| Date | The date of the attempted transaction | Required | Format date as YYYY-MM-DD |
Subscription Events
| Column Name | Description | Field Type | Value |
|---|---|---|---|
| Customer external ID | The unique identifier for the customer — typically the customer ID from your billing system. Column is Customer in Data Platform. |
Required | Accepts up to 255 alphanumeric and special characters |
| Subscription external ID | The unique identifier for the subscription. Typically the subscription ID in your billing system. Column is Subscription ID in Data Platform. |
Required | Should be unique for this data source. Accepts up to 255 alphanumeric and special characters. |
| External ID | A unique identifier for the event, typically provided by your billing system. Column is Event ID in Data Platform. |
Not required | Must be unique for this data source. Accepts up to 255 alphanumeric and special characters. |
| Event Type | The change in a subscription is classified as an event type. Column is Subscription Event Type in Data Platform. | Required | Specify an event type. Field is case-sensitive.
|
| Retracted event ID | A unique identifier required for retracted events | Not required | Accepts up to 255 alphanumeric and special characters |
| Date | The date the subscription change was requested or triggered | Required | Format date as YYYY-MM-DD |
| Effective Date | The date the subscription change will be implemented | Required | Format date as YYYY-MM-DD |
| Plan external ID | The unique identifier for this plan, typically provided by your billing system. Column is Plan in Data Platform. |
Not required | The plan’s External ID found by navigating to Profile > Admin > Plans and locating the External ID in the table. |
| Currency | The invoice’s currency | Not required | 3 letter ISO code of the currency. Field is case-sensitive. |
| Amount in Cents | The final amount billed for the specified quantity after discounts, taxes, and fees have been applied. Column is Amount in Data Platform. |
Not required | The amount in cents. Accepts only numerical characters (no decimal point). |
| Quantity | Quantity of subscription events being billed. Quantity value does not impact CMRR. | Not required | Can be any non-zero integer. Defaults to 1. |
Uploading CSV files
CSV files must be uploaded in a specific order. Import customers and plans and wait for all rows to be processed. Once all customers and plans have been imported successfully, upload invoices, invoice line items, transactions, and cancellations.
When importing 5,000 customers or more, we recommend waiting one hour for all data to be processed before importing the remaining datasets.
Upload CSV files to a custom data source. To create a new custom data source, navigate to Data Platform >Sources and click ADD A SOURCE. Then, select Custom Data Source. Enter a name and click Next.
To import data, select the section for the data you want to import (e.g., Customers) and click IMPORT CSV. Then, upload the corresponding CSV file. Repeat these steps for each section in the custom data source.
After data is uploaded and processed, it will appear in rows in the custom data source. By default, data is filtered to the last uploaded CSV file. View a different data set by using the Filter by CSV import filter. Remove the filter to view all data.
Editing uploaded data in-app
Navigate to Data Platform > Sources, then find and click the source containing the data you’d like to update. Next, select the corresponding record type (Customers, Invoice, Invoice Line Items, Transactions, Subscriptions Events, Subscriptions, or Plans) using the tabs provided.
Finally, click the Edit icon for the row you’d like to update, make the your changes, and click the green checkmark to save.
Alternatively, upload a new CSV file with updated data. When the CSV file includes data you’ve already imported, ChartMogul updates existing rows. When there are additional (new) rows of data in the CSV file, ChartMogul imports them as new data.