ChartMogul’s Google Sheets app is a non-technical method for importing plans, customers, and invoices to generate subscriptions. Learn more about Getting started with the Google Sheets app. 

For information about other import methods, review Getting started with importing data for Subscription Analytics.

Here’s what we cover in this article:

• Before you begin
• Setting up the Google Sheets app
• Using Google Sheets to generate subscriptions
• Formatting and sending your data
• Common scenarios

Before you begin

• Assign each customer a unique email address or External ID, if they do not already have one.
• ChartMogul uses customer, plan, and invoice data imported from the Google Sheets app to generate subscriptions. If your business doesn’t bill customers using invoices and instead keeps records of subscriptions, you may need to use an alternative import method. Learn more in Getting started with importing data for Subscription Analytics. 
• Read our guide to getting started with the Google Sheets app.
• Make sure the time zone of your Google sheet matches the time zone in your Data Settings.

1. Setting up the Google Sheets app

To set up the Google Sheets app, create an API key, install the app, and connect it to ChartMogul. Learn more.

2. Using Google Sheets to generate subscriptions

To import customers, plans, and invoices using Google Sheets, create a Google Sheets source.

Each service period (ex: month) you bill a customer for your product/service, add a new sheet and use a unique invoice external ID to send data to ChartMogul. Add a row for each invoice. Using a sheet with existing invoices creates duplicate data in ChartMogul.

3. Formatting and sending your data

To generate subscriptions, import data in the following order using these templates:

1. Customers – Companies or consumers with a potential, current, or past relationship with your business. ChartMogul uses customer data to create customer records.

When importing 5,000 customers or more, we recommend waiting one hour for ChartMogul to process all data before importing the remaining datasets.

2. Plans – The name, unique ID, and frequency of the product/services (or sets thereof) you offer on a subscription (recurring) basis. Manage plans by navigating to Settings & Data > Manage > Plans.
3. Invoices – The customer name, unique ID, and service period dates for the product/services being billed. ChartMogul uses invoice data to generate subscriptions, transactions, refunds, and MRR movements. From there, it calculates subscription analytics and cash flow reports.

Replace the template data with the data you want to import into ChartMogul and click Send customers/plans/invoices to ChartMogul.

Common scenarios

In this section, we outline how to add invoice data for common billing scenarios using the Google Sheets app.

Each time you import invoices, use a new sheet. Using a sheet with existing data creates duplicates in ChartMogul. To import data in bulk, add a new sheet and add a row for each invoice. For example, to renew subscriptions at the start of a service period for multiple customers, create a new sheet and add a row for each customer’s invoice.

Click Send invoices to ChartMogul to import your data.

Subscribing a customer to a plan

Complete the following fields: Email or External id, Type (select subscription), Amount in cents, Date, Plan name, Subscription number or Subscription ID, Currency, Service period start, and Service period end. Include optional fields such as Quantity, Proration, Discount code, Discount amount, and Tax amount.

Renewing a customer’s subscription

To renew a customer’s subscription for another billing period, specify the Subscription number or Subscription ID. Update information such as Amount in cents, Plan name, Service period start, and Service period end. If the change is prorated, select True in the Proration column.

Updating an invoice

To update a customer’s invoice, for example, to change the amount paid, resend the invoice to ChartMogul. You must provide the same Invoice External ID for ChartMogul to update the invoice. To delete an invoice, use data tables or the API.

Adding a non-recurring payment to a customer

To add a non-recurring (one-time) payment to a customer (such as a setup fee), select one_time in the Type column. Complete the following fields: Email or External id, Amount in cents, Date and, optionally, Discount Amount, Discount Code, Tax, and Description.

Adding additional subscriptions to a customer

If a customer has multiple subscriptions, specify a unique Subscription number or Subscription ID for each subscription. If this field is left blank, ChartMogulautogenerates a number.  Keep track of a customer’s subscription numbers or IDs. To cancel one of a customer’s subscriptions, refer to it by its number or ID.

Canceling a customer’s subscriptions

To cancel a customer’s subscription, select, subscription_cancellation in the Type field. Complete the following fields: Email or External id, Date, and Subscription number or Subscription ID.

Refunding a customer’s invoice

To refund a customer’s payment, complete the Email or External id 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.

Examples

Prorated charge for an additional seat

Imagine that:

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

To add this prorated charge, 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 a Quantity of 1.

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

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

Prorated charge with plan change

Imagine that:

• Your customer Adam Smith bought a monthly subscription to the Bronze Plan on August 1, 2023, and paid $50.
• Adam then changed his plan to the Silver Plan, which costs $60 a month, on August 16.
• You invoice Adam a prorated amount of $30 for the Silver 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 on August 31.

To add this prorated charge, 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:

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

Prorated downgrade with a reduction in subscription quantity

Imagine that:

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

To add this prorated charge, 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:

If specified as demonstrated, this prorated credit will result in ChartMogul contracting the MRR by $8.24, from $16.67 to $8.43 MRR on July 1.

Next Steps

Use the Google Sheets app to:

• Import lead and trial data
• Import custom attributes and tags
• Export customers and metrics

To learn about common scenarios you may encounter when using the app, review our Google Sheets troubleshooting guide.