Apps Script tutorial: calculate multiple operations (customers lifetime value)

Course 4 of 5 Apps Script + Sheets tutorials

LEVEL: Advanced to build, but anyone can use this sample.

Use a Google Sheet + Apps Script to predict a customer’s future buying behavior by calculating their customer lifetime value.

Introduction

A common financial prediction done by businesses is analyzing their customer’s lifetime value. This calculation takes a look at the buyer purchasing history and formulates the potential revenue customers can bring in the long term. This helps fine tune strategies to nurture relationships and bring repeat customers.

In this article I will share how to automate this calculation by creating a custom function in a Google Sheets’ Apps Script editor, and save time from having to calculate by hand after pulling customer data into a spreadsheet. The function analyzes the length of time a customer has been with you, takes an average of their buying history, and compares it to your entire customer buyer history. The logic of your custom function is called into your spreadsheet like a formula ex: =LIFETIME_VALUES(A2:C).

Note: The code for this scripted is listed at the end of this article.

4 min overview

Table of contents

A. Get your sheet

B. Use custom function

• View results on the same sheet as with the raw data
• View results in different sheet

C. How the code works

A. Get your sheet

• Make a copy of this spreadsheet.

B. Use custom function

• View results on the same sheet as with the raw data
• Locate the Sales data tab in your sheet.
• You will see the raw sales data in three headers called Customer ID, Date, and Amount.
• Since we want to make our calculations on the same page as the raw data, we turned column headers from E to J to orange for the items we need to analyze in order to arrive at the Customer Lifetime Value.

Which includes:

• Customer ID: to track all purchases made by each customer.
• Average order size: average value of sales
• Average order frequency: number of orders divided by number of customers
• Average customer value: average order size multiplied by frequency
• Average customer lifespan: 1st order date subtracted by the last order date
• Customer lifetime value: average customer lifespan multiplied by average value
• In cell E2, paste the following formula =LIFETIME_VALUES(A2:C) and click enter.
• All orange columns, including Customer Lifetime Value will be automatically populated like this:

View results in a different sheet

• It’s nice to have your raw data side by side with your calculations in order to check for errors, however once that’s verified, you may want to present the calculations in a separate sheet for reporting purposes (such as building a free dashboard in Google Data Studio so that you can share or embed it in a report-like format with real time filters).
• Visit the tab Import data here where you will find the same orange columns (minus the 3 sales data columns).

• In column A2 enter the same formula, however this time its source is pointing to another sheet =LIFETIME_VALUES(‘Sales data’!A2:C) and click enter.

Note: if you were to rename the tab Sales data to Total sales for example, the formula will automatically update itself within the new sheet’s name, preventing your formula from breaking (yay!😀).

C. How the code works

• By visiting Tools > Script editor in your sheet, you will arrive at the JavaScript logic that runs your custom formula.
• At the top of the script are the constants. These include the column position of each of the 3 fields of the sales data tab (Customer ID, Date, and Amount). Since we also need to calculate a customer’s retention period (to calculate that period we need to know the delta between the first order and the last order and we do that because JavaScript stores dates in Unix time as milliseconds)

• We then arrive at LIFETIME_VALUES which is the most relevant function for a user at a high level. It is in uppercase because this is the format to call a function as a formula into a cell. It also uses several Arrow functions which is a modern way in JavaScript to map(), filter(), and reduce() data in order to perform transformations without having to be so verbose. It allows you to be more concise, using less text to write out your logic. This function performs all the heavy lifting calculations.

Note: When using custom functions, if your formula only returns one value, Sheets will update one row, however in this case the custom function returns an array of arrays, and so Google Sheets updates multiple rows within multiple columns for us.

• Scroll all the way to the bottom of the script, and we find a function named groupByKey which groups all sales transactions by customer ID, making it easier to perform calculations on each customer’s order history in bulk. Once grouped, it is called by LIFETIME_VALUES,

Code