Import billing system data from a Google Sheet

Tip: You can also integrate with ChartMogul using our Import API.

This article will explain how to import your customers and subscription billing data into ChartMogul from a Google Sheet. This is useful if you have customers that are billed outside of a supported billing system. Follow the steps below to get started.

 

Contents

  1. Installing the Google Sheets app
  2. Setting up the Google Sheets app
  3. Creating a Data Source
  4. Importing plans
  5. Importing customers
  6. Importing invoices
  7. Tutorials
  8. Troubleshooting
  9. Limitations
  10. Next steps

 

Installing the Google Sheets app

Install the Google Sheets app by navigating to the ChartMogul app page in the Google Apps Web Store and then click Install.

Next, create a new Google Sheet and open the ChartMogul app by clicking Add-ons > ChartMogul.

This will open the ChartMogul Google Sheets app in the sidebar of the Google Sheet.

 

 

Setting up the Google Sheets app

1. Enter your ChartMogul API Keys

Note: You will need to be an admin to access your API Keys

You can find your API Keys in ChartMogul > Admin > API. Enter your ChartMogul API Account Token and Secret Key in the fields provided in the ChartMogul Google Sheets app.

2. Select Billing Data

Tip: it is possible to import customer attributes into ChartMogul and export almost all data from ChartMogul using the Google Sheets app.

Select Billing Data from the ChartMogul Google Sheets app navigation menu.

 

Creating a Data Source

In the ChartMogul Google Sheets app, enter a memorable name for your Data Source (such as "Google Sheets") and click Create Data Source.

You will only need to do this once. Next time you open the Import section of the app you will be prompted to select a data source.

 

 

Importing Customers

Before importing your customer invoices or subscription data, you must first import your customers into ChartMogul using the Google Sheets app.

The app requires your customer data to be formatted correctly. Follow the steps below to format and import your customers into ChartMogul from a Google Sheet:

  1. Click Customers from the Billing Data sub-menu.
  2. Click Insert template to insert example data in the required format into the currently active Google Sheet. Please note that all data in the sheet will be erased when inserting the template.
  3. Using the template insert your customer data into the Google Sheet. The only required fields are Name and either Email or External Id. Company and all location-based fields are optional. Trial and Lead fields are also optional.
  4. Finally, click Export customers. This will create the customers in your ChartMogul account. If a customer is not imported the app will inform you. The app will inform you when the import is complete.

You can check if your customers have been imported successfully by clicking Import customers in the Google Sheets app. You can also find your successfully imported customers reported in ChartMogul > Customers.

Note: an import can take some time, depending on the number of rows in your CSV file. Please do not edit or close a Google Sheet while an import is in progress.

 

 

Importing Plans

Before adding subscription invoices to your customers, you must first import your subscription plans into ChartMogul using the Google Sheets app.

Follow the steps below to format and import your subscription plans into ChartMogul from a Google Sheet:

  1. Click Plans from the Billing Data sub-menu.
  2. Click Insert template to insert example data in the required format into the currently active Google Sheet. Please note that all data in the sheet will be erased when inserting the template.
  3. Using the template insert your subscription plan data into the Google Sheet. The fields plan_nameinterval_count and interval are all required.
  4. Finally, click Export plans. This will create the plans in your ChartMogul account. If a plan is not imported the app will inform you. The app will inform you when the import is complete.

You can check if your plans have been imported successfully by clicking Import plans in the Google Sheets app. You can also find your successfully imported plans reported in the Plans table at ChartMogul > Admin > Plans.

Note: an import can take some time, depending on the number of rows in your CSV file. Please do not edit or close a Google Sheet while an import is in progress.

 

 

 

Importing Invoices

Once you have imported your subscription plans and customers into ChartMogul, you can add invoices to your customers using the Google Sheets app.

Invoices are used to generate subscriptions, transactions, refunds, and MRR Movements in ChartMogul.

Follow the steps below to format and import your customer invoices into ChartMogul from a Google Sheet:

  1. Click Invoices from the Billing Data sub-menu.
  2. Click Insert template to insert example data in the required format into the currently active Google Sheet. Please note that all data in the sheet will be erased when inserting the template.
  3. Using the template insert your invoice data into the Google Sheet. The fields Email, Type, and Date are all required. Additional fields are required depending on the Type of invoice selected. See the table below for more information on formatting your invoice data.
  4. Finally, click Export invoices. This will generate subscriptions and other transactional data in your ChartMogul account. If a row is not imported the app will inform you. The app will inform you when the import is complete.

If you have invoices in your sheet that have already been sent to ChartMogul, then clicking on Send Invoices to ChartMogul with them still in the sheet will create incorrect duplicates in ChartMogul, especially when the Invoice ID is missing. We highly recommend clearing out any invoices already sent to ChartMogul from the sheet before sending new ones.

You can check if your invoices have been imported successfully by navigating to a customer in ChartMogul > Customers.

 

Formatting invoices for import

Note: If a time is not specified in a date (e.g. 2017-04-04 15:21), a date will be imported with the time set at midnight (e.g. 2017-04-04 00:00).

Column name Value
Email or External id

REQUIRED

Customer's email address or unique identifier specified by you (e.g., the customer's id in your database).

Type

REQUIRED


The type of invoice.

Options:

  • one_time
  • subscription
  • subscription_cancellation
  • refund
Amount

REQUIRED FOR ONE_TIME AND SUBSCRIPTION TYPE INVOICES

The amount charged towards this line item for the specified service period, after discounts and taxes have been applied. Expected in cents (or pence for GBP, etc.).

Date REQUIRED FOR ALL INVOICE TYPES

The date on which this invoice was raised. Must be a date in the past. Must be a properly formatted Google Sheets date.

Plan name REQUIRED FOR SUBSCRIPTION TYPE INVOICES

Display name of the plan. Accepts alphanumeric characters. Irrelevant for one_time, refund or subscription_cancellation type invoices.

MUST ALREADY BE CREATED USING THE CHARTMOGUL GOOGLE SHEETS APP.

Subscription number

REQUIRED FOR SUBSCRIPTION AND SUBSCRIPTION_CANCELLATION TYPE INVOICES

The ID of the subscription. Can be 1, 2, 3 etc. If the customer already has a subscription with the number '1', this will update the subscription. Defaults to 1. Irrelevant for one_time and refund type invoices.

If you would like to set a specific subscription ID for the customer, for example to update an existing customer's metrics, you can change this field to subscription ID in the Google Sheets app Settings > enable "Use subscription ID, not subscription number" > click Save settings.

Currency

REQUIRED FOR ONE_TIME AND SUBSCRIPTION TYPE INVOICES

The 3-letter currency code of the currency in which this invoice is being billed, e.g. USD, EUR, GBP. Irrelevant for refund or subscription_cancellation type invoices.

Service period start

REQUIRED FOR SUBSCRIPTION TYPE INVOICES

The start of the service period for which this subscription is being charged. Must be a date in the past. Irrelevant for one_time, refund or subscription_cancellation type invoices.

Service period end

REQUIRED FOR SUBSCRIPTION TYPE INVOICES

The end of the service period for which this subscription is being charged. Irrelevant for one_time, refund or subscription_cancellation type invoices.

Quantity

Only relevant for subscription invoices.

The quantity of the line item being billed. Defaults to 1. Irrelevant for one_time, refund or subscription_cancellation invoices.

Proration

Only relevant for subscription invoices.

If this is a prorated charge, then set this attribute to true. Irrelevant for one_time, refund or subscription_cancellation invoices.

Discount code If any discount applies to this invoice, then a reference code for the discount. Only relevant for subscription and one_time invoice types.
Discount amount If any discount has been applied to this line item, then the discount amount in cents. Only relevant for subscription and one_time invoice types. Defaults to 0.
Tax amount The tax that has been applied to this line item in cents. Only relevant for subscription and one_time invoice types. Defaults to 0.
Description

Only relevant for one_time invoice types.

A short description of the non-recurring item being charged to the customer. Irrelevant for subscription, refund or subscription_cancellation type invoices.

External ID

A unique identifier specified by you for the invoice. Typically the Invoice Number in your system.

Note: All dates will be imported using the Google Spreadsheet timezone, not your ChartMogul account timezone. You can change your Spreadsheet timezone in Google Sheets > File > Spreadsheet settings.

Tutorials

Tip: Import customers and plans into ChartMogul before importing invoices. See invoice formatting guidelines above.

Subscribe a customer to a plan

To add a subscription to a customer in ChartMogul you first need to import the customer into ChartMogul using the Google Sheets app or the Import API and import plans into ChartMogul using the same method.

Once you have imported a customer and the necessary subscription plans, navigate to the Billing Data section of the Google Sheets app, click Invoices and then click Insert template. You can add the customer's subscription invoices by adding new rows to the Google Sheet for each invoice, and then filling in the following fields: Email, Type (should be 'subscription'), Amount (in cents), DateSubscription numberCurrencyService period start, and Service period end. Optional fields include: QuantityProrationDiscount codeDiscount amount, and Tax amount.

Once you have filled in the necessary fields click Send invoices to ChartMogul to add subscription invoices to your customers.

Making changes to a customer's subscription

To update a customer's subscription, simply add a new row to the Google Sheet indicating the new amount paid each service period. If the change is prorated simply add "true" under the Proration header.

Renew a customer's subscription

To renew a customer's subscription for another billing period, simply add a new row to the Google Sheet and fill in the fields . If the change is prorated simply add "true" under the Proration header.

Update an invoice

To update a customer's invoice, perhaps to change the amount paid, simply export the invoice again using the Sheets app. Please note that you must specify the same Invoice External ID for the invoice to be updated. If you did not specify an Invoice External ID, it will not be possible to update the invoice from Google Sheets. You can Delete an Invoice using the API. 

 

Adding a non-recurring payment to a customer

To add a one-time payment to a customer (e.g. a set up fee), add a new row to your Google Sheet and add the customer's email address to the Email field, enter "one_time" in the Type field, and then fill in the Amount and Date fields. If required you can also fill in the Discount Amount, Discount Code, Tax, and Description fields.

Adding additional subscriptions to a customer

If a customer has multiple subscriptions, you can add additional subscriptions by specifying a Subscription number. If this field is left blank, ChartMogul will assume that the subscription number is "1". You can add a completely new subscription to a customer by entering a different value in the Subscription number field, for example "2". Remember to keep track of a customer's subscription numbers, as if you add multiple subscriptions to a customer you will need them when cancelling a customer's subscriptions.

Cancelling a customer's subscriptions

To cancel a customer's subscription, use the customer's email address in the Email field, enter "subscription_cancellation" in the Type field, also add the Date and the Subscription number. If the customer only has one subscription, or if you left this field blank when creating a customer's subscription, enter "1" for the Subscription number field, otherwise enter the corresponding subscription number that you would like to cancel.

Refunding a customer's invoice

To refund a customer's payment, use the customer's email address in the Email field, enter "refund" in the Type field, and add the date of the invoice paid as reported in ChartMogul in the Date field. An invoice can be refunded twice.

 

Note: an import can take some time, depending on the number of rows in your CSV file. Please do not edit or close a Google Sheet while an import is in progress.

 

Troubleshooting

If data has not been successfully imported the app will inform you by highlighting a row in red. Imported rows will be highlighted green. The app will, where possible, inform you why your data did not import successfully.

If you are unsure of why your data is not importing successfully, or if the imported data is not reported in a way that you expected, please contact ChartMogul support for guidance, we'll be happy to assist.

You can delete a customer by navigating to their customer profile in ChartMogul and clicking Delete.

You can delete a plan by navigating to Admin > Plans and clicking Delete.

You can delete a data source (and all imported data) by navigating to Admin > Data sources > your data connector and clicking Delete data source.

Please note: It takes some time for data to be reported in ChartMogul. You can check if data has been successfully imported by exporting data from ChartMogul to the Google Sheet using the Google Sheets app. Learn more.

 

Limitations

The ChartMogul Google Sheets app has some limitations. You can find them listed below:

  • Every customer must have a unique email address or External ID. If they do not have one in your system you would need to assign them.
  • When importing invoices, plan names must be exactly what was used at import. For example "Gold Plan", not "gold plan" or "Gold-Plan".
  • The Google Sheets app and Google Sheet must be kept open and active while data is exporting. If the Google Sheet tab is closed the data export may stop.

 

Next steps

Once your data is successfully imported you can start to learn more about and ultimately grow your subscription business. Here are some common next steps that will make ChartMogul more powerful for you:

Learn about ChartMogul Segmentation

Set up Zapier Zaps to pipe in customer attributes

Learn how to create groups of subscription plans for better segmentation