Google Sheets troubleshooting guide

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 send a cancellation when the customer churns. Using the three templates available in the ChartMogul Google Sheets app you can import this data into ChartMogul.

 import_googlesheet.png

Customers

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

 

Plans

These are the plans you offer your customers. For most companies, they have a set of pre-defined plans that can be purchased by any customer.

 

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 line in Google Sheets.

 

Screenshot_2020-03-10_at_3.52.25_PM.jpg 

 

For full details on integrating your data manually with Google Sheets, including setting up the app and tutorials, check out this article:

Importing Billing System Data from a Google Sheet

 

 

 

 

What are you having trouble with?

Terms to know

Best Practices

Sending data to ChartMogul

Common Error Messages

Removing and re-importing data

Customer's MRR is not displaying as expected in ChartMogul

 

 

Terms to know

Data source: This acts like a data container in your ChartMogul account. When you setup an integration it will automatically create a new data source. You can also set up data sources for the API and Google Sheets. Keep in mind, you can have multiple data sources but you can't send a Google Sheets invoice with a plan or customer created in another data source. You can see a list of your data sources here:

https://app.chartmogul.com/#/data-platform/home

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. You can open a new Google Sheet by typing sheets.new into the taskbar of your browser:

https://sheets.new

Subscription ID: Your customers can have multiple subscriptions for the same plan. In order to identify if an invoice line is to renew or cancel an existing subscription or create a new additional subscription, we identify them using the subscription ID (assigned in 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 customers MRR will be doubled and their status will be past due.

 

 

Best Practices

  •  Keep a record of the data you send as it can't be exported from ChartMogul in the same format. This is a useful point of reference when renewing or canceling invoices or should you ever need to reimport 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 ChartMogul Google Sheets app and the data source you select. The connection is only active when you click Send ... to ChartMogul.
  • Only send new data to ChartMogul. If you add 3 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 data source, you can't add invoices to customers created in a different data 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 they date when customers are due to renew. These dates will only be used in MRR calculations instead of the plan interval when pro-ration is set to true. 
  • Only use pro-rated invoices when there is a change to a customers 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 pro-rated 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, there is templates for customers, plans and invoices which have to be uploaded separately. You can learn more here: 

Import billing system data from a Google Sheet

 

Can I change 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 create an error when you try to upload.   

To add more information to your customers use the enrichment template, you can learn more here:

Importing Customer Attributes with a Google Sheet

 

 

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 data source. When you load the ChartMogul app in Google Sheets you'll have the option to choose an existing data source or create a new one.

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

Screenshot_2020-03-10_at_4.19.21_PM.jpg 

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 going to your ChartMogul account and loading the customer's profile, scroll down to the subscription section to see the subscription ID.

 

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

You cannot add a $0 invoice through the Google Sheets integration. To add lead or trial data, use the customer template or enrichment template:

How to import trial or lead data from Google Sheets

 

Not exported. Transaction not found.

You might see this error when trying to submit a refund. When submitting a refund, we 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 to it. 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 is entered in the correct format, 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 upload again.

 

 



Removing and re-importing data

I made a mistake, how do I delete an invoice line?

It’s not possible to delete an invoice with the Google Sheets app or through the ChartMogul user interface. To delete an invoice you have to use the Import API:

Delete an invoice with the Import API


Alternatively, you can delete the customer in ChartMogul and re-upload the correct billing history. 

Screenshot_2020-03-10_at_4.27.30_PM.jpg

 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. 

 

 

Customer's MRR is not displaying as expected in ChartMogul

Multiple line items for one invoice ID

I have an invoice with several line items, however, it’s causing some strange movements in ChartMogul and the customers 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, instead of 2 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 like which 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 would appear with a past due status. You can automatically cancel out all past due customers using our delinquent handling setting

 

 

 

Still got questions? Drop us an email to support@chartmogul.com