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.
