Importing billing system data from Google Sheets

You can also integrate with ChartMogul via our Import API. Learn more.

This article explains how to import customers and subscription billing data into ChartMogul from Google Sheets, which is useful when you bill customers outside one of our supported billing systems.

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

Open a Google Sheet and navigate to Add-ons > Get Add-ons and search for ChartMogul.

You can also install the app directly from the Google Workspace Marketplace.

Open the app by creating a new Google Sheet and navigating to Add-ons > ChartMogul Google Sheets integration > Open app. This will open the app in the sidebar of the Google Sheet.

Before importing any data, check the time zone of your Google Sheet under File > Spreadsheet settings.

The time zone in this setting should be similar to what you have in your Data Settings. All dates will be imported using the Google Spreadsheet time zone, not your ChartMogul account time zone. If there is a timezone difference, the date and time for customers and invoices will be adjusted automatically in ChartMogul and could result in incorrect dates.

 

Setting up the Google Sheets app

  1. To send data from Google Sheets to ChartMogul, Admins will need to create a new read & write API key.
  2. Open the ChartMogul Google Sheets app.
  3. Copy your API key and paste it in both the ChartMogul API Account Token and ChartMogul Secret Key fields. Click Save keys
  4. Select Billing Data.

Tip: you can import custom attributes into ChartMogul and export almost all data from ChartMogul using the Google Sheets app.

Creating a Data Source

In the ChartMogul Google Sheets app, enter a name for your Data Source (such as "Google Sheets"), then 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 an existing data source.

Importing Customers

Before importing any invoices or subscription data, you must first import your customers into ChartMogul.

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. Use the template to add your customer data into the Google Sheet. The only required fields are Name and either Email or External Id. All other fields are optional.
  4. Once you have added all your data, click Send customers to ChartMogul. 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 to Google Sheet in the Google Sheets app. You can also find your successfully imported customers by navigating to Customers.

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 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 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 existing data in the sheet will be erased when inserting the template.
  3. Use the template to insert your subscription plan data into the Google Sheet. The fields plan_name, interval_count, and interval are all required. Note that ChartMogul only accepts day, month, and year as valid intervals. If you need a quarterly interval, use month as the interval and 3 as the interval_count.
  4. Finally, click Send plans to ChartMogul. 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 to Google Sheet in the Google Sheets app. You can also find your successfully imported plans by navigating to Profile > Admin > Plans.

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 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. Use the template to 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 Send invoices to ChartMogul. 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 Send Invoices to ChartMogul will create incorrect duplicates, especially when the Invoice ID is missing. We highly recommend importing any new invoices on a new tab within Google Sheets and to use a unique Invoice External ID to avoid duplication.

You can check if your invoices have been imported successfully by navigating to Customers and viewing a profile of one of your customers.

Formatting invoices for import

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. If left blank, ChartMogul will generate a number. 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. 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.

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

When you need to send new data via the Google Sheets app, make sure you use a new Google Sheet tab. Do not re-use previous sheets, as this will re-send the data and cause duplicates in your ChartMogul account. This also affects the speed of the download because each row is processed again.

Tutorials

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 and the plan into ChartMogul using the Google Sheets app or the Import API.

Once you have imported a customer and the necessary 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 filling in the following fields: Email, Type (should be 'subscription'), Amount (in cents), Date, Subscription number, Currency, Service period start, and Service period end. Optional fields include Quantity, Proration, Discount code, Discount 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, 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, 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 would 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 setup 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 autogenerate a number. 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 canceling a customer's subscriptions.

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

Example use case Prorated charge for an additional subscription

Let's imagine that:

  • Your customer Adam Smith bought a monthly subscription to the Bronze Plan on September 1st, 2018, and paid $50.
  • Adam then upgraded his subscription by adding another seat on the Bronze Plan on September 16th.
  • You charged him a prorated amount of $25 for the additional seat for the remaining service period until the end of September 30th.

To add this prorated charge, you must add a row in your Google Sheet with the field prorated set to true. Use the same subscription id or subscription number (depending on your Google Sheets app setting). Add the prorated service_period_start and service_period_end timestamps, the amount_in_cents, and the quantity set to 1.

Here's how the initial subscription and the prorated entry should look like:

Prorations specified correctly: If specified correctly as demonstrated, this example prorated charge will result in the MRR for Adam Smith expanding by $50, from $50 to $100 MRR on September 16th.

Example use case — Prorated charge with plan change

Let's imagine that:

  • Your customer Adam Smith bought a monthly subscription to the Bronze Plan on August 1st, 2018, and paid $50.
  • Adam then changed his plan to the Copper Plan, which costs $60 a month, on August 16th.
  • You invoice Adam a prorated amount of $30 for the Copper Plan until the end of August and credit him a prorated amount of $25 for the unused time on the Bronze Plan.
  • He pays the difference of $5 for the remaining service period ending August 31st.

To add this prorated charge, you must add two rows in your Google Sheet with the field prorated set to true. Use the same subscription id or subscription number (depending on your Google Sheets app setting) and the same service_period_start and service_period_end timestamps. One entry will have the new plan (Silver Plan) with a positive amount_in_cents indicating the additional payment. The other one will have the old plan (Bronze Plan) with a negative amount_in_cents indicating the credit amount.

Here's how the initial subscription and the two prorated entries should look like:

Prorations specified correctly: If specified correctly as demonstrated, this example prorated charge will result in the MRR for Adam Smith expanding by $10, from $50 to $60 MRR on August 16th. The transaction will contribute $5 to the Gross Cash Flow and Net Cash Flow on August 17th.

Example use case — Prorated downgrade with a reduction in subscription quantity

Let's imagine that:

  • Your customer Adam Smith bought an annual subscription to the Gold Plan on January 1st, 2018, and paid $200 for 20 seats.
  • Adam decided to reduce the number of seats midway through his annual subscription to 10 on July 1st.
  • You gave Adam a prorated amount of $50 for the unused time on the Gold Plan for 10 seats as credit.

To add this prorated charge, you must add a row in your Google Sheet with the field prorated set to true. Use the same subscription id or subscription number (depending on your Google Sheets app setting). Add the prorated service_period_start and service_period_end timestamps, a negative amount_in_cents indicating the credit amount, a negative quantity of -10 indicating the reduction in seats.

Here's how the initial subscription and the prorated entry should look like:

Prorations specified correctly: If specified correctly as demonstrated, this example prorated credit will result in the MRR for Adam Smith contracting by $8.24, from $16.67 to $8.43 MRR on July 1st.

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

Successfully imported rows will be highlighted in green. If data has not been successfully imported, the rows will be highlighted in red. Where possible, you will receive information about 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 get in touch with ChartMogul support.

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 Data Sources. From the settings of the selected data source, click DELETE THIS DATA SOURCE.

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 the following limitations:

  • 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".
  • You must keep the Google Sheets app and active while data is exporting. If you close the Google Sheet tab, the data export may stop.
  • There is a daily limit to the calls that you can make depending on your Google account. Limits are in this document https://developers.google.com/apps-script/guides/services/quotas:
    • Consumer (gmail.com): 20,000 / day
    • Google Apps free edition (legacy): 50,000 / day
    • G Suite: 100,000 / day

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 segmentation in ChartMogul

Set up Zapier Zaps to pipe in customer attributes

Create groups of subscription plans for better segmentation

Was this article helpful?