6 Replies Latest reply on Feb 6, 2018 12:20 PM by Travis Hall

# How to see customers lifetime value from source.

I have run into a rookie problem. I am trying to see what the lifetime value (sum of subtotal) of a customer is based on their first source (source being, what channel they came in from).

I have run the report to see what each order value is from the source, but not a running total of orders/subtotal after they come in from a channel.

Here is the information that I collect:

Table 1

Order_Id, user_id, ordered_at, Marketingsource, subtotal

Table 2

 user_id first_name last_name order_count phone email

Any direction is greatly appreciated.  My end goal is to be able to say from the Google source a customers lifetime value to date is X.

• ###### 1. Re: How to see customers lifetime value from source.

IF [ordered_at] = {FIXED [user_id]:MIN([ordered_at])} THEN [Marketingsource] END

That formula should give you the first source for each user.

I don't have data to play with so you may have to do a bit more with that to use it - or you may not, you'll have to try and see what happens.

It could be a case of something like {FIXED [user_id]:ATTR([AboveCalc])} - however I'm not at all sure that'll work.

Try it in stages and see what results you get. If no joy then please supply some sample data.

• ###### 2. Re: How to see customers lifetime value from source.

Thank you, Andrew, for taking a look at this! I posted that in my file and it did not take. I have attached some sample info similar to what I am pulling.

• ###### 3. Re: How to see customers lifetime value from source.

Ok, almost there in the first post. Try this:

{FIXED [User Id]:MIN(IF [ordered_at] = {FIXED [user_id]:MIN([ordered_at])} THEN [Marketingsource] END)}

Personally I would make this part, IF [ordered_at] = {FIXED [user_id]:MIN([ordered_at])} THEN [Marketingsource] END, a seperate calculated field and reference that within the other calculated field - i.e. {FIXED [User Id]:MIN([Calculation1])}

1 of 1 people found this helpful
• ###### 4. Re: How to see customers lifetime value from source.

Thank you so much Andrew! So I this might be a noob question. But after I make the calculation how do I apply it? would this be based off the marketingsource?

• ###### 5. Re: How to see customers lifetime value from source.

Andrew, please ignore my last response. I got it!!! Perfect!!!!!

• ###### 6. Re: How to see customers lifetime value from source.

Hi Andrew, Thank you so much for your help long ago! I was wondering if you would be able to help me once more on a problem with this exact data. I am now trying to find out if anyone has used any source and be able to pull their value you from there. For example, if a person comes in through Google (first source) but then later comes in through a Text message. I want to be able to see the lifetime value of that text campaign... any help would be greatly appreciated.