For customers billed outside of a supported billing system, billing data can also be imported using CSV, Data Platform, or our Import API.

With the Google Sheets app, ChartMogul generates subscription data from a combination of customers, plans, and invoices. Once you’ve added customers and plans, you can start a subscription by submitting an invoice and churn it by sending a cancellation.

In this article, we cover some common scenarios that you may need to troubleshoot. If you’re just getting started, learn more about importing billing data using Google Sheets.

Here’s what we cover in this article:

• Terms to know
• Best practices
• Sending data to ChartMogul
• Common error messages
• Removing and re-importing data
• A customer’s MRR is not displaying as expected in ChartMogul

Resources and further reading:

• Importing customer attributes using Google Sheets
• Importing trial and lead data from Google Sheets
• Exporting customers and metrics to Google Sheets

Terms to know

Customers — Identifies the owner of the subscription and the person or company that pays for it.

Google Sheet — Google Sheets is a spreadsheet program included as part of a free, web-based software office suite offered by Google within its Google Drive service. Open a new Google Sheet.

Invoices — This is where you define the payment, currency, and service period for each invoice. You can also include taxes, discounts, one-time payments, and refunds. Every time you charge your customer, you should submit a new invoice in Google Sheets.

Plans — The plans you offer to your customers. Most companies have a set of pre-defined plans that can be purchased by any customer.

Sources — Sources act as a container for billing data in your ChartMogul account. You can have multiple sources, but you can’t send a Google Sheets invoice with a plan or customer created in another source.

Subscription ID — Customers can have multiple subscriptions for the same plan. In order to identify if a subscription is to renew, to cancel an existing subscription, or create a new additional subscription, we identify them using a subscription ID (column F of the invoices template).

For example, if you have a customer who is currently paying for a $10 subscription with subscription ID sub_001 but you assign a subscription ID of sub_0001 in the renewal, a second subscription will be generated, the customer’s MRR will be doubled, and their status will be past due.

Best practices

Review our best practices for importing billing data using Google Sheets.

• Keep a record of the data you send, as it can’t be exported from ChartMogul in the same format. This is useful to have as a point of reference when renewing or canceling invoices or if you ever need to re-import your data.
• The data doesn’t have to live in one Google Sheet with multiple tabs. The data is connected to ChartMogul by the API keys you enter in the Google Sheets app and the source you select. The connection is only active when you click Send ... to ChartMogul.
• Only send new data to ChartMogul. If you add three new invoices to a list of 100 invoices that have already been uploaded, all 103 invoices will be reprocessed. This will take longer to upload and will cause metrics to fluctuate while the data is reprocessed.
• Keep the data in one source. You can’t add invoices to customers created in a different source.
• MRR is calculated by the invoice amount/billing interval defined in plans. If you add a $10 invoice to a 1-month plan, the MRR will be $10/1 = $10. If you add a $100 invoice to a 6-month plan the MRR will be $100/6 = $16.67. There are a number of other factors that influence the MRR calculation.
• The service period start and end date define the date the subscription starts and the date when customers are due to renew. These dates will only be used in MRR calculations instead of the plan interval when proration is set to true.
• Only use prorated invoices when there is a change to a customer’s regular billing cycle. For example, if they renew on the 1st of the month and upgrade on the 17th. If you start a subscription with a prorated invoice, it will generate irregular movements when the customer renews.

Sending data to ChartMogul

I uploaded a list of subscriptions that were downloaded from my billing system, but the charts are empty. What happened?

ChartMogul generates subscription metrics from invoice data.

The Google Sheets integration requires users to submit the data in pre-defined templates for customers, plans, and invoices that each have to be uploaded separately. Learn more about importing billing data using Google Sheets.

Can I edit the templates to fit my needs? Add or move columns?

The template cannot be changed. Adding or removing column headers will not change the order in which the data is imported and will often cause errors in importing.

For example, column C of the invoices template will always look for an amount in cents. Adding the company name or invoice date will produce an error when uploaded.

Common error messages

Not exported. Customer not found in your ChartMogul account.

You can only upload a Google sheets invoice to a customer in the same source. When you load the ChartMogul app in Google Sheets, you’ll have the option to choose an existing source or create a new one.

We recommend keeping all Google Sheets data in the same source.

Not exported. Subscription not found.

If you get this error when trying to submit a cancellation, it’s probably because the subscription number or subscription ID (column F of the invoices template) doesn’t match the subscription ID in ChartMogul. You can check the subscription ID by viewing a customer’s record in ChartMogul and viewing the subscription ID in the subscription.

Not exported. Amount field is required. Please add an amount paid in column C.

You cannot add a $0 invoice through the Google Sheets app.

Not exported. Transaction not found.

You might see this error when trying to submit a refund. When submitting a refund, ChartMogul will look for a previous transaction specified on the date in column D and refund that amount. For example, if a customer purchased a $10 subscription on 2019-07-03 and received a refund on 2019-07-15, in Google Sheets we would submit the following:

• Column A: Customer ID or email
• Column B: Refund
• Column D: 2019-07-03

Google Sheets needs the transaction date as an identifier to correctly map the refund. If you want the refund date to differ from the transaction date, you would need to submit it via the Import API.

Not exported due to a formatting error: {“errors” :{ “email”: “The value provided does not appear to be a valid email address.”}}

Check that the email address format is correct, for example, name@email.com. If you are still getting the error, try copying the invoice line onto a new sheet without any formatting applied and uploading again.

Removing and re-importing data

I made a mistake. How do I delete an invoice?

Invoices can be deleted in Data Platform. In your Google Sheets source navigate to Invoices and select the row of the invoice you wish to delete. Then, click DELETE SELECTED ROWS. Repeat the same steps for Invoice Line Items and Transactions.

You can also use the Import API or delete the customer in ChartMogul and re-upload the correct billing history.

I want to re-import everything. How do I export my Google Sheets invoices?

You cannot export invoices in the same format as they were uploaded. We recommend keeping a master list of uploads for your records.

A customer’s MRR is not displaying as expected in ChartMogul

I have an invoice with several line items. However, it’s causing strange movements in ChartMogul, and the customer’s MRR is wrong.

You can only submit one invoice line per invoice ID. If you submit more than one line with the same invoice ID, the second invoice will overwrite the first invoice. The solution is to add a character to the invoice external ID; therefore, instead of two items with the invoice ID invoice_001 you would have invoice_001_1 and invoice_001_2.

Customers are not churning automatically at the end of their service period. How do I cancel a subscription?

ChartMogul will assume that customers are active until they are explicitly canceled. You have to send a cancellation event for all of your customers.

A cancellation is a new invoice that includes:

• Column A: Customer ID or email
• Column B: Subscription_cancellation
• Column D: Cancellation date
• Column F: Subscription ID

A customer in ChartMogul with no invoice received before the renewal date is classified as a Past-due Subscriber. You can automatically cancel out all past-due customers using our Handling Past-due Subscriptions setting.

Still have questions? Contact support.