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.

I think the attachment will explain this much better.

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

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

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?