Adding data using data tables is a non-technical method for importing your business’ data. This method requires you to add each customer, invoice, transaction, etc. individually, so it works best for smaller datasets. Before adding data, confirm this method is best for your business: learn more about other import methods for Subscription Analytics or CRM.
Once imported, ChartMogul gives you visibility into your data as it exists in our platform through a series of data tables. Edit these tables for accurate reporting and metrics. Use this article to understand how ChartMogul organizes datasets in a source.
Here’s what we cover in this article:
Once you’ve learned how to add and edit data, review these tables for guidance on importing data:
- Customers
- Contacts
- Plans
- Invoices
- Invoice Line Items
- Transactions
- Subscription Events
- Manual Subscriptions
- Subscriptions
Before you begin
- If you’re new to ChartMogul Subscription Analytics, consider adding sample data before importing your business’ data.
- The datasets you’ll use depend on how your business uses ChartMogul. Before adding data, review Getting started with importing data for Subscription Analytics and Getting started with importing data for CRM.
- Include data in optional fields for deeper insights with segmentation.
Required datasets
Follow the steps below to add data for Subscription Analytics and CRM within ChartMogul’s data tables.
All datasets are required, with the exception of contacts and transactions. Add contacts to use ChartMogul CRM. Add transactions to access cash flow reports.
Add data to a custom source. To create a new custom source, navigate to Settings & Data > Sources and click Add Source. Then, select Custom Source. Enter a name and click Next. ChartMogul creates your custom source and a table for each dataset.
How to add data for Subscription Analytics using data tables
To add data for Subscription Analytics, start by adding:
The remaining datasets you’ll add depend on how your business stores data: using invoices or as subscriptions. Learn more about which method is right for your business in Getting started with importing data for Subscription Analytics.
To import using invoice data:
To import using subscription data:
ChartMogul processes the data and generates subscriptions, which are visible in the Subscriptions tab.
How to add data for CRM using data tables
To use ChartMogul CRM, add the following datasets to import your leads:
ChartMogul creates customer records with contacts. Review our setup guide to get started with CRM.
Adding and editing data within data tables
It’s possible to edit data within the data tables of custom and Google Sheets sources. If you’re using Stripe, Chargebee or the latest version of our Recurly integration, ChartMogul allows you to edit invoices, invoice line items, transactions and subscription events imported from the billing system and preserves your edits during a resync. Learn more.
To keep your metrics and customer records accurate, edit your data within ChartMogul’s data tables.
Navigate to Settings & Data > Sources and click a source. Then, select the data table you want to edit:
- Customers
- Contacts
- Plans
- Invoices
- Invoice Line Items
- Transactions
- Subscription Events
- Manual Subscriptions
The Subscriptions tab is a record of the subscriptions generated by ChartMogul and cannot be edited.
Click the edit icon to update a row and green checkmark to submit your changes.
To delete a row, click the checkbox to select it, and then click Delete Selected Rows.
Customers
Companies or consumers with a potential, current, or past relationship with your business. ChartMogul uses this data to create customer records. This dataset is required.
To add a customer, click New Customer.
Column Name | Description | Field Type | Value |
---|---|---|---|
Company Name | Add a company name when the customer is a business and not an individual. To add an individual customer, leave this field blank and add a contact. | Optional | Accepts up to 255 alphanumeric and special characters. |
Customer ID | A unique identifier for this customer — typically the customer ID from your billing system. Value is permanent and cannot be changed. | Required |
Must be unique. Accepts up to 255 alphanumeric and special characters. |
Full name (legacy) | On April 4, 2023 we released ChartMogul CRM. The contact name field on customer records will be phased out as it’s now possible to add contact names to your customer records. Use the Company Name field to store the customer/account name, and add Contact records for people associated with an account. When adding B2C (consumer) customers, leave the Company name field blank and add a single contact to the customer record. If (like many ChartMogul users) you've been storing Company names in this field please contact support. | Optional | Accepts up to 255 alphanumeric and special characters. |
Email (legacy) | On April 4, 2023 we released ChartMogul CRM and this field will eventually be phased out. You can now add Contacts (each with their own email address) to your customer records. | Optional | Should be a valid email address. |
Country | The customer’s country. | Optional | Choose the country from the drop-down. |
State | The customer’s state in the US. | Optional | Choose the state from the drop-down. |
City | The customer’s city. | Optional | Accepts up to 255 alphanumeric and special characters. |
Zip | The customer’s postal code. | Optional | Accepts up to 255 alphanumeric and special characters. |
Lead created at | The date and time when the lead was created. We use this information to generate lead volume reports. | Optional |
Choose the date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Free trial started at | The date and time when the free trial started. We use this information to generate reports of your free trials and trial-to-paid conversions. | Optional |
Choose the date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Contacts
Personal and contact details of individuals with a current or past relationship with your business. ChartMogul stores contacts on customer records. This dataset is optional for Subscription Analytics and required to use ChartMogul CRM.
To add a contact, click New Contact.
Column Name | Description | Field Type | Value |
---|---|---|---|
Customer | Customer associated with the contact. | Required |
Choose the customer from the drop-down. |
First Name | The contact’s first name. | Optional | Accepts up to 255 alphanumeric and special characters. |
Last Name | The contact's last name. | Optional | Accepts up to 255 alphanumeric and special characters. |
Title | The contact’s job title, e.g., CEO. | Optional | Accepts up to 255 alphanumeric and special characters. |
Email address for this contact. | Optional |
Should follow email format. Accepts up to 255 alphanumeric and special characters. Must be unique within the customer record. |
|
Phone | Phone number for this contact. | Optional | Accepts up to 255 alphanumeric and special characters. |
URL for the contact’s LinkedIn profile. | Optional |
Accepts up to 255 alphanumeric and special characters. Must end in a domain name (.com, .co.uk, etc) to generate a hyperlink. |
|
URL for the contact’s X (formerly known as Twitter) profile. | Optional |
Accepts up to 255 alphanumeric and special characters. Must end in .com to generate a hyperlink. |
|
Note | Additional details you wish to add to the customer record. | Optional | Accepts up to 255 alphanumeric and special characters. |
Plans
The name, unique ID, and frequency of the product/services (or sets thereof) you offer on a subscription (recurring) basis. This dataset is required for Subscription Analytics.
To add a plan, click New Plan.
To add a plan, you’ll need to specify the interval unit and interval count. For example:
- For a weekly plan, choose Day as the interval unit and set the interval count to 7.
- For a quarterly plan, please choose Month as the interval and set the interval count to 3.
- For a yearly plan, please choose Month as the interval unit and set the interval count to 12. Alternatively, choose Year as the interval unit and set the interval count to 1.
Column Name | Description | Field Type | Value |
---|---|---|---|
Name | Name of the plan. | Required | Accepts up to 255 alphanumeric and special characters. |
Plan ID | Unique identifier for the plan. Value is permanent and cannot be changed. | Required | Must be unique. Accepts up to 255 alphanumeric and special characters. |
Interval Unit | Unit of billing interval. Value is permanent and cannot be changed. | Required | Choose Day, Month, or Year from the drop-down. |
Interval Count | Frequency of billing interval. Value is permanent and cannot be changed. | Required | Accepts up to 255 numeric characters. |
Invoices
The customer name, unique ID, and due date for the product/services being billed. Invoices must include invoice line items. ChartMogul uses this data to calculate subscription analytics. This dataset is not required for manual subscriptions or CRM.
To add an invoice, click New Invoice.
When a new invoice is created, ChartMogul categorizes the Import Status as Failed. ChartMogul categorizes an invoice as Processed when invoice line items are successfully added. Learn more.
Open (unpaid) invoices contribute to your recurring revenue. Don’t add open invoices if you don’t want them included in your MRR.
Column Name | Description | Field Type | Value |
---|---|---|---|
Customer | Customer name for whom we want to add an invoice. | Required | Choose the customer from the drop-down. |
Invoice ID | A unique identifier for the invoice. This is typically the invoice number in your billing system. | Required |
Must be unique. Accepts up to 255 alphanumeric and special characters. |
Invoice date | The date and time when the invoice was raised. | Required |
Choose the invoice date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Due date |
The date and time by which the invoice needs to be paid. | Optional |
Choose the due date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Status | The invoice status based on the invoice’s transactions or user selection. | Optional | Choose open, written_off, or voided from the drop-down. If you leave this field blank, ChartMogul will classify the invoice as open, paid, or refunded based on its transactions. |
Currency | The three-letter ISO code of the currency in which the invoice is billed. | Required |
Choose the currency from the drop-down. |
Invoice Line Items
Details of the service (or products) you’ve sold, including customer name, plan (or product), service period, the subscription’s unique ID, and amount billed. ChartMogul uses this data to generate subscriptions. From there, it calculates subscription analytics. This dataset is not required for manual subscriptions or CRM.
You must add an invoice before adding a line item. To add an invoice line item, click New Invoice Line Item.
Column name | Description | Field type | Value |
---|---|---|---|
Invoice | A unique identifier for the invoice — typically the invoice number in your billing system. | Required | Choose the invoice ID from the drop-down. |
Type | Choose if it is a one-time (non-recurring) item or a subscription. | Required | Choose Non-recurring or Subscription from the drop-down. |
Amount | The final amount charged towards the invoice line item. | Required | Accepts numbers with up to two decimal places. |
Subscription ID | A unique identifier for the subscription — typically the subscription ID in your billing system. | Required | Accepts up to 255 alphanumeric and special characters. |
Subscription Set ID | Identifier for subscriptions with multiple components. | Optional | Accepts up to 255 alphanumeric and special characters. |
Service Period Start | The date and time when the subscription starts. | Required for subscriptions |
Choose the date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Service Period End | The date and time when the subscription ends. | Required for subscriptions |
Choose the date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Prorated? | Specifies whether this is a prorated line item. | Required for subscriptions | Choose Yes or No from the drop-down. |
Proration Type | Type of prorated charge. Learn about different proration types. | Optional |
Choose Differential, Full, or Differential MRR from the drop-down. If not selected, this defaults to Differential. |
Plan | Plan for this subscription. | Required for subscriptions | Choose the plan from the drop-down. |
Quantity | Subscription quantity for this line item. | Optional |
Should be a non-zero integer. Defaults to 1 if not provided. |
Transaction Fees | Amount paid to your billing system and/or payment processor for this invoice line item. | Optional | Accepts numbers with up to two decimal places. |
Tax Amount | Tax amount is the amount of tax applied to this invoice line item. | Optional | Accepts numbers with up to two decimal places. |
Discount Code | Reference code for the discount. | Optional | Accepts up to 64 alphanumeric and special characters. |
Discount Amount | The amount discounted when a discount is applied to this invoice line item. | Optional | Accepts numbers with up to two decimal places. |
Discount Description | Description of the discount. | Optional | Should be a text string. |
Line Item ID | Unique identifier for the invoice line item. | Optional |
Must be unique for this source. Accepts up to 255 alphanumeric and special characters. |
Event Order | Specifies the order of processing line items with the same service period start date. Learn more. | Optional | Should be an integer. |
Balance Transfer | Indicates that the amount should be credited to or debited from the customer’s balance. Positive values represent credit added to the customer’s account. Negative values represent credit used as payment. This applies only to non-recurring line items. | Optional | Choose False or True from the drop-down. |
Transactions
Attempted payments and refunds to invoices. ChartMogul uses this data to generate cash flow reports. This dataset is not required for manual subscriptions or CRM.
To add a transaction, click New Transaction.
Column Name | Description | Field Type | Value |
---|---|---|---|
Invoice | The invoice associated with the transaction. | Required | Select the invoice from the drop-down. |
Type | The type of transaction: payment or refund. | Required | Select Payment or Refund from the drop-down |
Amount | Final transaction amount charged. If left empty, the invoice will be paid in full. | Optional | Accepts numbers with up to two decimal places. |
Result | The transaction outcome: successful or failed. | Required | Select Failed or Successful from the drop-down. |
Date | Date stamp of the attempted payment. | Required | Choose the date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Transaction ID | Unique identifier for the transaction. | Optional |
Must be unique. Accepts up to 255 alphanumeric and special characters. |
Subscription Events
Any change to a subscription that affects the subscription’s calculated MRR. ChartMogul relies on cancellation events to definitively know when a subscription has ended. Add scheduled events to track Committed Monthly Recurring Revenue (CMRR). This dataset is not required for manual subscriptions or CRM.
To add a subscription event, click New Subscription Event.
Column name | Description | Field type | Value |
---|---|---|---|
Customer | Customer associated with the subscription. | Required | Choose the customer from the drop-down. |
Subscription ID | A unique identifier for the subscription. Typically the subscription ID in your billing system. | Required for all subscription event types except Retracted. | Accepts up to 255 alphanumeric and special characters. |
Subscription Set ID | Identifier for subscriptions with multiple components. | Optional | Accepts up to 255 alphanumeric and special characters. |
Event ID | A unique identifier for the event, typically provided by your billing system. | Optional |
Must be unique for this source. Accepts up to 255 alphanumeric and special characters. |
Subscription Event Type | The change in a subscription is classified as an event type. | Required |
Choose the event type from the drop-down. |
Retracted Event ID | The Event ID of the subscription event being retracted. | Optional |
Must be unique for this source. Accepts up to 255 alphanumeric and special characters. |
Event Date | The date and time when the subscription change was requested or triggered. | Required |
Choose the date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Effective Date | The date and time when the subscription change will be implemented. | Required |
Choose the date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Plan | A unique identifier for this plan, typically provided by your billing system. | Required for Started, Start (Scheduled), Updated, and Updated (Scheduled) event types. |
Choose the plan from the drop-down. |
Currency | The three-letter ISO code of the invoice’s currency. | Required for Started, Start (Scheduled), Updated, and Updated (Scheduled) event types. |
Choose the currency from the drop-down. |
Amount | The final amount billed, for the specified quantity, after discounts, taxes, and fees have been applied. | Required for Started, Start (Scheduled), Updated, and Updated (Scheduled) event types. | Accepts numbers with up to two decimal places. |
Tax Amount | Tax applied to a subscription event. Defaults to zero. If specified, tax is excluded from CMRR. | Optional | Accepts numbers with up to two decimal places. |
Quantity | Quantity of subscription events being billed. Can be any non-zero integer. Defaults to one. Quantity value does not impact CMRR. | Required for Started, Start (Scheduled), Updated, and Updated (Scheduled) event types. |
Should be a non-zero integer. |
Manual Subscriptions
Track subscriptions in ChartMogul without uploading invoice data. Learn more. Do not use this dataset when importing data for subscription analytics using invoices.
To add a manual subscription, click New Manual Subscription Event.
Column Name | Description | Field Type | Value |
---|---|---|---|
Customer | Customer associated with the manual subscription | Required | Choose the customer from the drop-down. |
Event Type | The change in a subscription is classified as an event type. | Required | Choose the event type from the drop-down. |
Subscription ID | A unique identifier for the subscription. Typically the subscription ID in your billing system. | Required | Accepts up to 255 alphanumeric and special characters. |
Event Date | The date and time when the subscription change was requested or triggered. | Required | Choose the date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Effective Date | The date and time when the subscription change will be implemented. | Required | Choose the date from the drop-down or enter the date and time manually in the ISO 8601 format. If not set, time defaults to midnight. |
Plan | A unique identifier for this plan, typically provided by your billing system. | Required when the event type is Started or Updated | Choose the plan from the drop-down. |
Currency | The three-letter ISO code of the invoice’s currency. | Required when the event type is Started or Updated | Choose the currency from the drop-down. |
Amount | The final amount billed, for the specified quantity, after discounts, taxes, and fees have been applied. | Required when the event type is Started or Updated | Accepts numbers with up to two decimal places. |
Quantity | Quantity of manual subscription events being billed. Can be any non-zero integer. Defaults to one. | Optional | Should be a non-zero integer. If not set, defaults to 1. |
Report Cash Flow | An option for ChartMogul to automatically generate a transaction to report the MRR amount in cash flow reports. This does not impact your billing system. | Optional | Choose No or Yes from the drop-down. If not set, defaults to No. |
Subscriptions
View existing subscription details for each customer from the Subscriptions tab.
View a list of existing subscriptions for each customer. Cancel a subscription by clicking Cancel Subscription.