Calculating Customer Lifetime in Google Data Studio aka Looker Studio

[Priyanka1311]

I have a transactions table and I want to use that to calculate customer life time value using it. I want to apply the below formula 

Customer Lifetime Value = number of months between max date and min date for any customer

How can I achieve that in Looker Studio? 

[sam8]

You should be able to get something like this with the DATETIME_DIFF function!

You could create a new field with the following formula:

DATETIME_DIFF(MAX(<date>), MIN(<date>), MONTH)

[Poala_Tenorio]

To calculate Customer Lifetime Value (CLV) in Looker Studio (formerly known as Google Data Studio) using the formula you provided, you'll need to follow these steps:

1. Connect Your Data: Make sure your transactions table is connected to Looker Studio. This can be done through a connector like Google Sheets, BigQuery, or other supported data sources.

2. Prepare Your Data: Ensure your transactions table includes at least the following columns:

   • customer_id (unique identifier for each customer)
   • transaction_date (date of the transaction)

3. Create a Custom Query: If your data source supports SQL (e.g., BigQuery), you can write a custom query to calculate the number of months between the max date and min date for each customer. Here's an example SQL query for BigQuery:

   Show More

   SELECT
   customer_id,
   TIMESTAMP_DIFF(MAX(transaction_date), MIN(transaction_date), MONTH) AS customer_lifetime_months
   FROM
   `your_project.your_dataset.your_transactions_table`
   GROUP BY
   customer_id

4. Import the Custom Query into Looker Studio:

   • Go to your data source in Looker Studio.
   • Click on Add Data and choose your data source.
   • Select Custom Query and paste your SQL query.

5. Create a New Data Source:

   • Use the custom query as a new data source.
   • Name your data source appropriately.

6. Create a Report in Looker Studio:

   • Open or create a new report in Looker Studio.
   • Add a new chart (e.g., a table) to your report.
   • Set your new custom query data source as the data source for the chart.

7. Configure the Chart:

   • In the chart settings, add customer_id as a dimension.
   • Add customer_lifetime_months as a metric.

8. Visualize the Data:

   • Customize the chart to visualize the customer lifetime value as needed.
   • You can use different types of charts like bar charts, tables, or pivot tables to display the data.

By following these steps, you can calculate and visualize the Customer Lifetime Value for each customer in Looker Studio based on the number of months between their first and last transactions.

[Priyanka1311]

Thankyou for your response. My data source is Google sheets. Can I still
create custom queries?

[Priyanka1311]

Thankyou for your response. My data source is Google sheets. Can I still
create custom queries?

[Carlos_Leon]

No, not from Google Sheets but I think that the formula provided by Sam8 works 

DATETIME_DIFF(MAX(<date>), MIN(<date>), MONTH)