From the export of our orders, add two columns called First Purchase Cohort and Customer Age.
Second, find the date of each customer's first order. Since Excel does not have built-in rank/partition functions, you have to hack this. For example, use a pivot table to return a unique list of customers then an array function ([url removed, login to view]) to calculate the date of first order. For the array function, You will need nested IFs inside a minimum function, applied over the entire original dataset.
Third, use vlookup based on email address to pull the date of first purchase back into your aggregate dataset (the cohort column you left blank in step #1). Transform this datetime to the granularity desired, e.g. use the date function to get first day of month. You now have a list of every order with the customers first purchase date listed.
Fourth, calculate the difference between first purchase and order dates in your Customer Age column according to months.
Finally, you have the dataset to analyze. Create another pivot table with your first purchase cohort in rows and customer age in columns. You can now sum revenue or count orders on a normalized timeline.
Now create first month revenue, months 2-12 as a percent of month 1, and cumulative revenue per customer per month.
The workbook should be set up so that I can simply paste in updated data every week and change the range (if required) and the data will then get automatically updated.
9 freelancers are bidding on average ₹5793 for this job
Hello Sir Im excel VBA macro and formula Expert :) i can do anything in excel :) please give me a chance for doing your job and im able to start doing your job right now :) Thank You Sincerely prameswara :)