This feature is only accessible to customers that signed up before September 1, 2016. All new customers can use our Google Sheets app to import manual customers in bulk.
- What is a CSV import?
- How can I upload my customers via a CSV import?
- Example CSV templates
- Common errors & troubleshooting
- Limitations on Manual Customers
- Advanced formatting documentation
- Making changes to manual customers' subscriptions
What is a CSV import?
Most businesses have some customers they invoice outside of their subscription billing system, and they are likely to keep a record of these customers in an Excel file.
The CSV import enables you to add these customers to your ChartMogul account and include their data in your dashboard charts and metrics.
A CSV formatted for import:
Every manually imported customer will have an account page:
Every manual customer will contribute to your recurring revenue metrics:
How can I upload my customers via a CSV import?
ChartMogul has fairly strict formatting requirements for CSV imports. Below we outline the requirements and the format the CSV must be in.
In the CSV file one row is equal to one subscription and contains everything you need to create a new customer and define their subscription. The top row of the CSV is reserved for the column headers.
Import a CSV file by navigating to your Admin > Data Sources (admin permissions required) and clicking Upload CSV file.
Example CSV templates
Make use of the sample CSV files below to learn how to format your data for import!
Google sheets demo:
Read the notes attached to this Google Sheets document, then export the file as a CSV and input your own data! Go to the Google Demo Sheet.
Demo CSV downloads:
Example CSV files for editing a subscription
Common errors & troubleshooting
My CSV did not get accepted by ChartMogul.
Firstly, check that the CSV file is a 100% comma separated file, you can do this by opening the CSV file with a text-editor like Sublime Text or TextEdit. Secondly, compare your CSV file to our Default template.csv, are all the headers the same, and does the syntax in your file match the sample CSV? If there are errors, ChartMogul will not import the CSV file and will send you an email listing the validation errors so that you can correct them. Help is also at hand, if you can't find what's preventing your CSV from importing. Please reach out to us at [email protected].
My CSV import failed, and I don't understand the error message.
Please send a sample of your CSV to [email protected] and we'll be happy to help diagnose the problem.
I'm confused about the formatting requirements, is there advanced documentation?
Take a look at this section of the article: Advanced formatting documentation.
Limitations on Manual Customers
There are a number of limitations on what can and can't be done with manual customers.
- Manual subscriptions can only be added to a manual customer. You cannot add a manual subscription to a customer that was imported automatically from your billing system.
- Manual customers can only have one active subscription at a time.
- Monthly recurring revenue (MRR) from manual customers will be included in all of your ChartMogul graphs and metrics, except for the Cash Flow charts (Net Cash FLow, Gross Cash Flow, Non-Recurring Revenue, Refunds).
- One-time (non-recurring) revenue cannot be added to Manual customers
Advanced formatting documentation
ChartMogul requires a 13 column comma-separated CSV file with the headers shown in the example CSV. All 13 column headers are required. Only 8 fields in a row require values.
With the CSV file, one row is equal to one subscription and contains everything you need to create a new customer and define their subscription.
Customer external id
This is your unique identifier for your customer. It can be a combination of letters and numbers. For example, ‘123A’. You can later use this ID to update a manual customer’s subscription. If a customer external ID is not recognised, ChartMogul will create a new manual customer for that customers external ID.
The customer’s name. For example, 'The Bluth Company’.
The name of the plan. If there is an existing manual plan with the same plan_name, plan_interval and plan_interval_count then this subscription will be associated with the existing plan, otherwise you must rename the plan.
For example, if you have customers on a 'Gold plan' with monthly billing, their plan_name could be 'Gold Monthly', their plan_interval would be 'month', and their plan_interval_count would be '1'.
If you have some customers on, for example, a 'Gold plan' with annual billing, their plan_name could not be 'Gold Monthly' (as the billing interval length is different and this would throw an error), so the plan_name could be 'Gold Annual', the plan_interval would be 'year' and the plan_interval_length would be '1'.
Required. Only accepted values are 'day', 'year' and 'month’.
Plan interval count
(Integer) The number of years or months in a single subscription cycle. E.g. for monthly plans this value would be 1 if the Plan interval is 'month'.
ISO_2 currency code, e.g. USD, GBP, EUR, etc.
Total amount paid for the subscription (a single subscription cycle), you must deduct any discounts. (2 decimal points allowed, do not include currency symbol, e.g. $100.52 should be 100.52)
The number of licenses/seats. Used purely for cosmetic reasons within ChartMogul and is not part of the MRR calculation. If you are not sure what to put for this field, please enter a 1.
The start date of the subscription. Earliest allowed date is 2010-01-01.
These are fields that can be left blank. You must still include the column header in your CSV import.
The customer’s email. For example, '[email protected]’. Used for customer search. ChartMogul will never email your customers.
ISO_2 country code, e.g. GB, US, CA, FR, DE, etc. Used for ChartMogul Maps.
Just US states supported, format is CA, NY, etc. Used for ChartMogul Maps.
The end date of the subscription. Earliest allowed date is 2010-01-01. If this is left blank, ChartMogul will assume the subscription is still active and will run forever. (You can upload another CSV later with just customer_external_id and cancelled_at if you need to cancel subscriptions at a later date).
CSV rules and specifications
- The CSV data file must contain no more than 10,000 rows of data (one row for the header and the rest for customers).
- In order to cancel an active manual/csv subscription, the cancelled_at date should be populated with a value. There is no need to provide a cancelled_at date during customer creation. You can cancel a subscription later by simply uploading a new CSV file specifying customer_external_id and cancelled_at date and we will cancel customer’s active subscription. It is not possible to set a future cancellation date.
Making changes to manual customers' subscriptions
You can update attributes and subscriptions of previously uploaded customers by simply uploading another CSV file in the same format outlined above. You can make multiple updates to the same customer within the same CSV file but rows should be in date order with older events above more recent ones.
As long as you use the same customer_external_id you can update the following properties of the customer:
- plan_name (will trigger an upgrade / downgrade)
- amount_paid (will trigger an upgrade / downgrade)
- quantity (will trigger an upgrade / downgrade)
You can upgrade, downgrade or cancel an active subscription. To do this you only need to include the customer_external_id and the cancelled_at fields.
Example CSV files for editing a subscription
How MRR is calculated for manual customers
MRR is calculated from the plan_interval ('month' or 'year'), plan_interval_count and amount_paid.
E.g. if your billing period is 3 months:
plan_interval = 'month'
plan_interval_count = 3
And if the amount_paid is $300, the MRR would be $300 / 3 months = $100 MRR
The MRR for a manual subscription will run forever unless you explicitly cancel that subscription.