En cours

Excel expert needed to create a cohort analysis spreadsheet

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.

Compétences : Excel

Voir plus : find an excel expert, excel expert up, excel expert com, excel 0 to expert, 12 month spreadsheet, data analysis excel expert needed, excel spreadsheet expert needed, query excel sheet excel expert needed, excel expert needed virtual assitant, sql 2005 create job import excel spreadsheet sql, create xml file excel spreadsheet, create questionnaire fill excel spreadsheet, create web calculator excel spreadsheet free, create dat files excel spreadsheet

Concernant l'employeur :
( 1 commentaire ) Gurgaon, India

N° du projet : #8490774

Décerné à :

DanishHussain786

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

1550 ₹ INR en 3 jours
(1 Commentaire)
0.0

9 freelance ont fait une offre moyenne de 5793 ₹ pour ce travail

prameswara

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 :)

1750 ₹ INR en 3 jours
(134 Commentaires)
5.8
expertatwork

Can you please share your spreadsheet ? we like to have a look at it to understand better, thank you !

2900 ₹ INR en 2 jours
(108 Commentaires)
5.5
kamrananjum1

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

13333 ₹ INR en 3 jours
(16 Commentaires)
4.5
zu02

A proposal has not yet been provided

7777 ₹ INR en 3 jours
(1 Commentaire)
2.7
mithiseo

A proposal has not yet been provided

1500 ₹ INR en 3 jours
(0 Commentaires)
0.0
rajonhassan10

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

7777 ₹ INR en 1 jour
(0 Commentaires)
0.0
benfirmeza19

A proposal has not yet been provided

7777 ₹ INR en 3 jours
(0 Commentaires)
0.0
arsa89

A proposal has not yet been provided

7777 ₹ INR en 3 jours
(0 Commentaires)
0.0