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

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

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

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?

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1