Adding and editing data using data tables

You’ll need to be an Admin or Owner in ChartMogul to add and manage sources. Read more about user roles and permissions.

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:

Before you begin

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:

  1. Plans
  2. Customers
  3. Contacts (optional)

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:

  1. Invoices
  2. Invoice Line Items
  3. Transactions (optional)
  4. Subscription Events

Screenshot highlighting the data tables required for importing invoice data (Customers, Invoices, Invoice Line Items, Subscriptions Events, and Plans) as well as two optional data tables: Contacts and Transactions.

To import using subscription data:

  1. Manual Subscriptions

Screenshot highlighting the data tables required for importing subscription data (Customers, Manual Subscriptions, and Plans) as well as the optional Contacts table.

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:

  1. Customers
  2. Contacts

ChartMogul creates customer records with contacts. Review our setup guide to get started with CRM.

Adding and editing data within data tables

Data tables aren’t available for Braintree or the previous version of our Recurly integration.

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:

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.

Screencap of editing a row in the Invoice Line Items table.

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.

Default sorting for table rows is by time created, which is not visible. This may be different from the order in which you uploaded the data.

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 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.
LinkedIn 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.

Twitter 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.

Screenshot of the Invoices table with one newly created invoice. The Line Items and Import Status fields show errors.

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.Screenshot of the Subscriptions table with the Cancel Subscription option highlighted.

Was this article helpful?

We’re sorry to hear that. Would you like to share more feedback?


Thanks for your feedback!