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.
Hello Dear, I'm Danish Hussain. I saw your requirement in your job post and luckily i fulfill all those requirement. I can do this job perfectly. I will try my best to deliver in time with a best quality work. You can Plus
9 freelance font une offre moyenne de ₹5793 pour ce travail
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 :)
Can you please share your spreadsheet ? we like to have a look at it to understand better, thank you !
We are team of Professional Accountants (ACAs, ACCAs, CPAs, CFAs, MBAs), IT Professionals (Web/Software development), and Professional Writers headed by a qualified chartered accountant (ACA, FCCA-UK) with over 11 ye Plus
Hi, Thanks for giving me the opportunity to bid on your job. I have reviewed your job description very carefully. After reviewing your job descriptions, I am offering myself as a candidate for this project. I am ensu Plus