Follow

Adding manual customers in bulk with a CSV file

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. 

 

  1. What is a CSV import?
  2. How can I upload my customers via a CSV import?
  3. Example CSV templates
  4. Common errors & troubleshooting
  5. Limitations on Manual Customers
  6. Advanced formatting documentation
  7. 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. 

In ChartMogul we call customers imported via a CSV Manual Customers. You can also add manual customers individually through the user interface.

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?

CSV is a file format called Comma Separated Values. You can export to a CSV format from Excel, Google Spreadsheet, etc. 

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 Load (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!

Make sure the date formats are correct when you open the CSV in your editor, as some CSV editors can change the date automatically. The required format is YYYY-MM-DD (e.g. 2015-09-15).

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.

 





 


 

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 support@chartmogul.com.

 

My CSV import failed, and I don't understand the error message.

Please send a sample of your CSV to support@chartmogul.com 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.

 

Required fields 

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.

Customer name

The customer’s name. For example, 'The Bluth Company’.

Plan name

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

Plan interval

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

Currency

ISO_2 currency code, e.g. USD, GBP, EUR, etc.

Amount paid

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)

Quantity

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.

Started at

The start date of the subscription. Earliest allowed date is 2010-01-01.

Date formatting: When specifying a date for started_at or cancelled_at you must use one of the following formats: YYYY-MM-DD HH:MM e.g. 2015-03-23 22:10, or YYYY/MM/DD HH:MM e.g. 2015/03/23 22:10. If you just specify the date without the time we will use midnight 00:00. The timezone set in your ChartMogul account under Data settings will be used. If 20:00 is specified in your CSV, the started at date will be reported as 20:00 in ChartMogul.

Non-required fields

These are fields that can be left blank. You must still include the column header in your CSV import.

Customer email

The customer’s email. For example, 'michael@example.org’. Used for customer search. ChartMogul will never email your customers.

Customer Country

ISO_2 country code, e.g. GB, US, CA, FR, DE, etc. Used for ChartMogul Maps.

Customer state

Just US states supported, format is CA, NY, etc. Used for ChartMogul Maps.

Cancelled at

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:

  • customer_name
  • customer_email
  • customer_country
  • customer_state
  • 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

Example CSV for upgrading a customer (CSV).

Example CSV for upgrading and downgrading a customer (CSV).

Example CSV for cancelling a customer's subscription (CSV).

Example CSV for reactivating a customer's subscription (CSV).

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.

Attachments