# 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

{% hint style="success" %}
You can import [this custom report](https://analytics.google.com/analytics/web/template?uid=Cy8hMOWdTBeXEB0nzLBS7g) we created in Google Analytics to visualize this
{% endhint %}

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

![](https://res.cloudinary.com/littledata/w_854,h_353,c_fit/littledata-blog-images/2020/09/Screenshot-2020-09-04-at-13.37.16.png)

The result of the query should look something like this:

![](https://res.cloudinary.com/littledata/w_854,h_418,c_fit/littledata-blog-images/2020/03/Screen-Shot-2020-03-10-at-2.13.06-PM.png)

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

![](https://res.cloudinary.com/littledata/w_855,h_413,c_fit/littledata-blog-images/2020/03/Screen-Shot-2020-03-10-at-2.13.20-PM.png)

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

   ![](https://res.cloudinary.com/littledata/w_433,h_575,c_fit/littledata-blog-images/2020/03/Screen-Shot-2020-03-10-at-2.13.48-PM.png)
4. In the Values section just add the Transaction Revenue. The end result should look something like this:

![](https://res.cloudinary.com/littledata/w_832,h_377,c_fit/littledata-blog-images/2020/03/Screen-Shot-2020-03-10-at-2.14.04-PM.png)

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.

![](https://res.cloudinary.com/littledata/w_802,h_147,c_fit/littledata-blog-images/2020/03/Screen-Shot-2020-03-10-at-2.14.37-PM.png)

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.

![](https://res.cloudinary.com/littledata/w_828,h_386,c_fit/littledata-blog-images/2020/03/Screen-Shot-2020-03-10-at-2.14.46-PM.png)

**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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://help.littledata.io/google-analytics/acquisition/building-reports-on-customer-lifetime-value-by-marketing-channel.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.