SPEAKER: A customer's lifetime value
is a common calculation performed by organizations that
sell goods or services and it helps
them approximate future revenue each customer can bring them
by analyzing their purchasing history.
It's an insightful metric that helps
you analyze your long term strategies with your customers.
It's very common to perform this analysis in a Google Sheet
after exporting data from one's invoicing tool
and apply a series of formulas manually for many columns,
which takes time.
So in this episode, I will walk you
through how to use your own formula, called a custom
function, in a Google Sheet to perform
multiple calculations at once.
Welcome to the "Sheets to Apps" show
where we talk about how to tame these tabular beasts
into productive applications.
You will find in this video's description
the link to a sheet that includes a script called an app
script to perform a customer and lifetime value analysis.
Try it out by making a copy.
I've also included step by step instructions.
In the sheet, you will find three columns
of data related to buyer history,
such as customer identifier, purchase date, and sale amount.
Now in order to estimate the revenue a customer can
potentially offer you in the long term,
you must perform the following calculations
such as grouping the transactions of each customer
by their unique ID.
Next, you calculate the average order size per customer.
Next, you find their average order frequency
by dividing the number of orders by the number of customers.
Next, you find the average customer value
by multiplying the order size by the frequency.
Next, we find the average customer lifespan,
which requires subtracting the first and last date
a customer made a purchase.
And finally, we arise to the customer lifetime value
by taking the average customer lifespan
and multiplying it by the average value.
All of these steps are very time consuming
and can be prone to error at any given step, which
is why it's nice to simply enter the following formula
in your sheet and point it to your sales data columns.
This way it automatically populates all these fields
for use simultaneously.
Now if you're wondering how we built this custom function,
you can visit the app script by clicking Tools, Script
Editor at the top of your sheet to visit
the code and its comments.
A few notable highlights I would like to mention
is at the top of the script are several constants.
The first three include the column position
of each of the fields from the sales data tab.
The fourth constant is to help us calculate each customer's
retention period in months since JavaScript uses 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 upper case because this is the format
to call a function as a formula into a cell.
It also uses several array functions,
which is a modern way in JavaScript to map, filter,
and reduce data in order to perform transformations
without having to be too verbose compared to older JavaScript.
It allows you to be more concise, using less text
to write out your logic.
This function performs all the heavy lifting calculations.
Now 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 group
by key, which groups all the 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.
And there you have it, a beautiful formula
that calculates your customer lifetime value.
Hope you found this episode helpful, my friends.
If you did, please click Like and Subscribe
for future videos.
Cheers.
[MUSIC PLAYING]