Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a project which requires me to calculate lifetime value using pivot tables, sumifs and indexing.
Solved by O. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 02/09/2018 - 04:24
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User 02/09/2018 - 04:24
hi
User 02/09/2018 - 04:24
https://docs.google.com/document/d/1HS-FmAduRC9zZ5LH58x4roHqu-sDv_u4jDHdWaT-IoM/edit
User 02/09/2018 - 04:24
this is a case study I need help with, can you please see it?
User 02/09/2018 - 04:24
also, the link to excel file is in there
Excelchat Expert 02/09/2018 - 04:25
Sure thing. Let me go over it and get back to you for a while. Likewise, kindly open the excel file for editing please.
User 02/09/2018 - 04:26
https://docs.google.com/spreadsheets/d/1XqPIchFL5JfgBHP736GekmVeBhFNTAkfV49S3_K8Y74/edit#gid=1772314688
User 02/09/2018 - 04:26
here is the link
User 02/09/2018 - 04:26
for the excel file
Excelchat Expert 02/09/2018 - 04:26
Do you need this to be done in Excel or googlesheets?
User 02/09/2018 - 04:26
excel please
User 02/09/2018 - 04:26
not google sheet
Excelchat Expert 02/09/2018 - 04:27
Sure thing, I'll just download the file then for me to work on. :)
User 02/09/2018 - 04:27
okay
User 02/09/2018 - 04:29
Some tips that I got for part 1:LTV should NOT be the sum of purchases from customers in those categories. However, the metric should be at the customer level. By this I mean you need to divide by the number of customers in that category.You can't just divide by the count on the pivot table because that would be the count of unique purchases in that category. For example, in the 30 purchases category, it will say 30, but that's just 1 customer.
User 02/09/2018 - 04:30
for Part 2: use index match and sumifs. Start by grouping the customers into the cohorts. To do this, you need to find their first purchase date or month. Then, apply this to all of their purchase records in the order level data set. -- Once you have the order level file with the first purchase month associated w/ all customers, you can jump directly into creating a table. -- The table should have rows for each cohort (first purchase month) and columns for each purchase month. Then, use sumifs with similar formulas to what you are using to create the sum of purchases that are in each cohort and match the purchase month. But remember, LTV is cumulative, so your final chart should keep increasing up and to the right. -- Lastly, remember to divide by the number of customers in that cohort to get it to the customer level and not just the sum of purchases.
User 02/09/2018 - 04:30
^these are the hints I got
User 02/09/2018 - 04:30
let me know if it's not clear
Excelchat Expert 02/09/2018 - 04:31
Thanks for the hints; let me consider them in here. For part 1, I believe this just involves the February cohort, while for part 2, this one will look at the months of Feb '15 through Jul '15. Let me work on this for a while and update you the soonest once done.
User 02/09/2018 - 04:32
yes, part 1 is only February and we need to create that table for which an example is shown in the case study doc
User 02/09/2018 - 04:32
and for part 2 we need to create a line graph for months Feb'15-Jul'15
User 02/09/2018 - 04:32
are the hints helpful?
Excelchat Expert 02/09/2018 - 04:33
I believe they're good direction to start with. However, if I see any way the hints would be further improved, I'll let you know. :)
User 02/09/2018 - 04:33
thanks so much
User 02/09/2018 - 04:43
any luck?
User 02/09/2018 - 04:43
do you have any questions?
Excelchat Expert 02/09/2018 - 04:44
Part 2 is easy. Part 1 is the tricky one though. Btw, will it be okay if I separate for part 2 the purchases done in Feb '15 to Jul '15? This is since trying to process the huge volume of original data makes my Excel slow.
User 02/09/2018 - 04:44
sure
Excelchat Expert 02/09/2018 - 04:45
Noted on that. Let me just do the part 2 and proceed to part 1.
User 02/09/2018 - 04:45
okay
Excelchat Expert 02/09/2018 - 04:53
Already done for 2. Now to twist my brain for part 1. I believe this should be the easier part though.
User 02/09/2018 - 04:53
I think they're similar aren't they?
User 02/09/2018 - 04:53
maybe if you read the hint again, it will help
User 02/09/2018 - 04:54
they're very particular about how it needs to be calculated. I would suggest if you see the hints again, it will make it more clear on how to approach it
User 02/09/2018 - 04:54
:)
Excelchat Expert 02/09/2018 - 04:54
Sure thing, thanks for that. :)
User 02/09/2018 - 04:56
my email address is prtikku@gmail.com
User 02/09/2018 - 04:56
but I have another session remaining so I'll add it if need be
User 02/09/2018 - 04:57
I don't know if I can continue adding in the same chat though
Excelchat Expert 02/09/2018 - 04:58
We still have another free extension for this session before it ends. Hopefully, we can do it prior the end of the session. :)
User 02/09/2018 - 04:58
phew! thanks :)
User 02/09/2018 - 04:59
I was trying to do this with someone and they never sent the file and haven't heard back from support yet, and the deadline to submit this is in a few hours.
User 02/09/2018 - 05:00
so just a little nervous, thats all
Excelchat Expert 02/09/2018 - 05:00
Oh, that's too bad. I got the gist of it though. I made an error with part 2, but already fixed this. We have the whole next extension for part 1, and that's more than enough time to do it. :)
User 02/09/2018 - 05:01
sounds great, thank you so much. Yea the hints help. that's why I asked you to refer to them
User 02/09/2018 - 05:01
:)
User 02/09/2018 - 05:14
were you able to tackle it? :)
Excelchat Expert 02/09/2018 - 05:15
Yep, got the part 1. 5 minutes to do this.
User 02/09/2018 - 05:15
amazing
User 02/09/2018 - 05:15
thank you
User 02/09/2018 - 05:15
:)
Excelchat Expert 02/09/2018 - 05:16
Btw, the part 1 will depend on a pivot table result.
User 02/09/2018 - 05:17
yes
User 02/09/2018 - 05:17
they want to see the pivot table too
User 02/09/2018 - 05:17
they mentioned about that in the hint too
Excelchat Expert 02/09/2018 - 05:17
Nice! :)
User 02/09/2018 - 05:22
is it done?
User 02/09/2018 - 05:22
just have a minute left here
User 02/09/2018 - 05:22
:|
User 02/09/2018 - 05:24
hello?

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.