# Difficult: Counting unique transactions in each month, then summing "lifetime value"

1. ## Difficult: Counting unique transactions in each month, then summing "lifetime value"

This is proving a lot more difficult than I anticipated. I need to calculate the number of first time transactions in each month, then sum the "lifetime value" of each transaction in that month +30 days, then +90 days /from the date of purchase./

Then I need to do that for each individual animal, like cat or dog.  Register To Reply

2. ## Re: Difficult: Counting unique transactions in each month, then summing "lifetime value"

I think the attachment will explain this much better.

https://www.dropbox.com/s/sed7iajd3w...mple.xlsb?dl=0  Register To Reply

3. ## Re: Difficult: Counting unique transactions in each month, then summing "lifetime value"

Hi,
Could you describe logic how to calculate each of positions?
And does your output example for 01.08.2016 contains correct data?

p.s. I think I already saw those data here   Register To Reply

4. ## Re: Difficult: Counting unique transactions in each month, then summing "lifetime value" Originally Posted by kasan Hi,
Could you describe logic how to calculate each of positions?
And does your output example for 01.08.2016 contains correct data?

p.s. I think I already saw those data here Hi! You were great last time 1. Count the number of users that transacted for the *First time* in each month. So under 8/1/16, count unique users that have never transacted before from 8/1/16 to 8/31/16. For example, in August this would exclude users that transacted already in July.

2. Second step: Look at column E (amount). For the users in step 1, I need to sum how much those users in each month spent +30 days *from when they first transacted*. Then do the same for +90 days. For example, if I first transacted on 8/15/16, I need to calculate the sum of amount from 8/15/16 to 9/15/16 (+30 days), then do the same for 8/15/16 to 11/15/16 (+90 days). I need to figure this out for EACH user in August, then sum each of the lifetime values.

3. Step 3 is easy average formula (see workbook rows 11 and 12).

4. Step 4 adds another condition to step 1: here, we're calculating the number of users that purchased cat for the *first time* in August (then September, October, etc), and do this for all animals. This could mean that if you purchased dog in July, and cat in September, you would be counted once each in July and September for the different animals (since you purchased each animal for the first time in different months). Then do the same lifetime value calculation for each of these animal cohorts.

Hopefully that makes sense?  Register To Reply

5. ## Re: Difficult: Counting unique transactions in each month, then summing "lifetime value"

Hi,
Lets test steps 1,2,3 (calculation without specific animals).
I will not attach the file this time, so
2) add "01.02.2017" in cell P7, to ensure EndPeriod for the code

Try to run code below, my results is in attached file.
If you'll confirm that results seems OK, than we can implement one more criteria for each animal.
My assumption: code calculates correct,I've manually checked first month and last month (for the last month 30 days and 90 days amounts are the same - as they should be).

One thing to clarify: for the logic with specific animals - in case we found first user #123 transaction for the cat in August then in +30 and +90 we also should looking only for cat transactions by user #123? Or should we look at all this user transactions? ``Please Login or Register  to view this content.``  Register To Reply