Building reports on Customer Lifetime Value by marketing channel

Most of Littledata’s customers are excited to start reporting on the lifetime value by channel or campaign, but be careful! You need to consider whether the reports you build from Google Analytics data are valid, or use a valid combination of dimensions to segment the data.

How to see CLV by customer using a custom dimension:

This can be done in multiple ways:

1. Create a custom report in Google Analytics where you have to add Littledata - Shopify Customer ID and Littledata - Customer Lifetime Value as dimensions, and Users as a metric

note:

You can import this custom report we created in Google Analytics to visualize this:  https://analytics.google.com/analytics/web/template?uid=Cy8hMOWdTBeXEB0nzLBS7g

2. Create a Looker Studio report where you add the Littledata - Shopify Customer ID and Littledata - Customer Lifetime Value as dimensions

How to calculate CLV by Source/Medium or Channel - summing up transactions with last non-direct click:

There are a couple of steps to achieve this.

1. We need to query the data from Google Analytics using Google Sheets

The result of the query should look something like this:

2. Use pivot tables to prepare the data for Looker Studio. Select all the data and select Pivot Table from the Data tab

3. In the Pivot Table settings (Rows section), add the Custom Dimension with the User ID and Source Medium

   

4. In the Values section just add the Transaction Revenue. The end result should look something like this:

At this point, we summed all the transaction revenue/source/customer. Now that the data is prepared for Looker Studio, you can import the data directly to there.

Visualizing the data in Looker Studio

After creating a new data source and importing the Google sheet with the Pivot Table, we need to modify the aggregation type for the transaction revenue. This is done in order to view the MEDIAN CLV per channel.

When creating the chart in Looker Studio, just add Source Medium as a dimension and Transaction Revenue to the Metric section. The results should look like this, showing the MEDIAN CLV per traffic source. If you wish to view the CLV per Channel, repeat the process but instead of Source/Medium use the Default Channel Grouping dimension from Google Analytics.

Why CLV custom dimension value is higher compared to transaction ID even though I just started using Littledata?

Clarifying how we calculate CLV: The CLV calculation can be summarized as the SUM of all of their transaction revenue. We are pulling this data directly from Shopify and pushing this data into Google Analytics using the Custom Dimensions we created. In short, this is all the revenue associated with that customer.

CLV will be always higher than transaction revenue if the customer ordered more than once.

Why can't you just split the CLV custom dimension by channel?

One of the most common mistakes we see is building a report using the Customer ID custom dimension (user scope), Customer Lifetime Value dimension (user scope) and then adding the Source/Medium (session scope).

Why is this an issue? 

A customer can have more than one Source/Medium, leading to duplicates that will inflate your CLV calculations.

For example, a user comes from Google (google/organic) and this source is attributed 4 orders each worth $100.  When they come back to the website from a Klaviyo email campaign (Klaviyo/email) and creates their 5th order, the CLV now will be $500 BUT the CLV dimension will show $400 for google/organic and $500 for Klaviyo/email

Customer ID	Source Medium	CLV
12344555	Google / organic	$400
12344555	Klaviyo / email	$500

To get a better understanding of this, think about it this way: when the user made an order and Klaviyo/email was their source, the CLV at that point in time was $500.

Conclusion

Scope matters. It's very easy to mix them up and get data from which we can draw erroneous conclusions. Although it may seem that getting the MEDIAN CLV visualized is a lengthy process, it is necessary in order for your decisions to be based on accurate data.