Excel tasks
- or -
Post a project like this2051
$$
- Posted:
- Proposals: 16
- Remote
- #2113142
- Awarded
Excel Expert, Excel Spreadsheets, Excel VBA & Access Database Developer, Data modelling & Analysis
London
4874639032720827048649553555566003497011710176291169305122821713718271914922
Description
Experience Level: Intermediate
Hi there
I would like to perform some basic analysis on our customer/order data that we have in CSV/Excel format.
I've attached a sample of the data. Please note the main file is circa 150,000 records, so the proposed formula/solution will need to be able to handle that volume of data.
Each row on the sheet represents a single order. A customer (Customer User ID, column D) may have multiple orders linked to them. An order MAY be linked to a particular promotional campaign, identified in column T (GCID). GCID is usually only attached to a customer's first order.
This is what I'd like to find out...
Step 1 - Calculate total profit (Lifetime Value/LTV) per customer
Essentially just sum the Profit per Order (column W) linked to each customer, so we end up with a list of unique customers with a "Lifetime Value" attached to each customer.
Step 2 - Calculate average LTV per campaign (GCID)
I would like to understand the average LTV for each campaign (GCID) so we can compare the effectiveness of each campaign. Essentially we'd need to calculate the LTV per customer (as above) and then sum the LTVs of all customers linked to each campaign.
Please could you let me know the price to provide the above? Ideally I would just like the relevant formulas/queries so we can run the calculations on different/new data sets.
Any questions, please let me know.
Thanks
Andy
I would like to perform some basic analysis on our customer/order data that we have in CSV/Excel format.
I've attached a sample of the data. Please note the main file is circa 150,000 records, so the proposed formula/solution will need to be able to handle that volume of data.
Each row on the sheet represents a single order. A customer (Customer User ID, column D) may have multiple orders linked to them. An order MAY be linked to a particular promotional campaign, identified in column T (GCID). GCID is usually only attached to a customer's first order.
This is what I'd like to find out...
Step 1 - Calculate total profit (Lifetime Value/LTV) per customer
Essentially just sum the Profit per Order (column W) linked to each customer, so we end up with a list of unique customers with a "Lifetime Value" attached to each customer.
Step 2 - Calculate average LTV per campaign (GCID)
I would like to understand the average LTV for each campaign (GCID) so we can compare the effectiveness of each campaign. Essentially we'd need to calculate the LTV per customer (as above) and then sum the LTVs of all customers linked to each campaign.
Please could you let me know the price to provide the above? Ideally I would just like the relevant formulas/queries so we can run the calculations on different/new data sets.
Any questions, please let me know.
Thanks
Andy
Andy S.
0% (0)Projects Completed
1
Freelancers worked with
1
Projects awarded
100%
Last project
6 Sep 2018
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Do you need the campaign calculations to add all orders from that customer or just the ones with the campaign code?
Andy S.21 Aug 2018It needs to add all orders from that customer.
697094
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies