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.
You can import this custom report we created in Google Analytics to visualize this: https://analytics.google.com/analytics/web/template?uid=Cy8hMOWdTBeXEB0nzLBS7g
There are a couple of steps to achieve this.
The result of the query should look something like this:
In the Pivot Table settings (Rows section), add the Custom Dimension with the User ID and Source Medium
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.
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.
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.
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).
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.
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.