Complex Cohort Analysis: Taking a transaction table & breaking expansion revenue out

[jmorris24]

Hello,

So... I am trying to solve something a bit more complex. In essence I need to take a transaction table and understand based on someones new business start date how likely they are to reorder (also known as upsells or expansion revenue) each quarter after they start. And then based on that look at active clients based on where they are in their journey and assign an expansion revenue forecast to each accordingly each quarter for the next year. By looking at an order type of New you can identify a client's start date. Reorder=expansion

Here is my sample data:

Sales Order	Account Name	Start Date	End Date	Order Type	Amount	Class
1111	XYZ Corp	1/15/15	1/15/18	New	25000	Software
1111	XYZ Corp	1/15/15	1/15/18	New	5000	Hardware Support
1112	The Best Company	4/15/13	4/15/16	New	50000	Software
1113	The Best Company	8/16/15	4/15/16	Reorder	65000	Software
1114	The Best Company	4/16/16	4/16/19	Renewal	115000	Software
1114	The Best Company	4/16/16	4/16/19	Renewal	5000	Hardware Support
1115	The honest company	8/15/15	8/15/18	New	100000	Software
1116	The honest company	12/13/16	8/15/18	Reorder	80000	Software
1117	The honest company	8/16/18	8/16/21	Renewal	180000	Software
1118	Just another company	2/15/20	2/14/23	New	250000	Software

Here is how I envision one of the outputs:

	Year 1
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Quarter 1	Quarter 2	Quarter 3	Quarter 4
XYZ Corp
Software
Hardware support
The Best Company
Software
Hardware support
The honest company
Software
Hardware support
Just another company
Software
Hardware support

Here is another possible output:

	Start Date
XYZ Corp
The Best Company
The honest company
Just another company

Here are some of the questions I am trying to answer:

1. We need to know what percent of clients have additional Revenue come in Q1, Q2, etc

2. Of those clients what percent of their revenue is additional

3. Ideally it would be nice to know that X % of clients have reorder revenue come in the 1st year, x % in year 2, etc

4. Clients that have expansion in the first year typically expand their revenue by y%, clients that expand 2nd year expand their revenue by x%

5. What percent of total revenue expands each quarter

6. Once we answer those then we need to look at clients that are up for renewal and assign expansion revenue based on the Quarters they are currently in

Hopefully that makes sense. I understand the complexity. I am newer to alteryx and have never done something so complex in it. Excited to learn more!

[PhilippK]

Hi @jmorris24 ,

welcome to the community! 🙂

This sounds like a consulting project as it is quite complex (as you said). That is not the best fit for a community post in my opinion.

Alteryx is all about learning by doing. Therefore, I recommend that you take this project as an opportunity to learn.

As a starting point, check out the "Getting Started Learning Path": 

https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117

Then start building your workflow.

When you got stuck or if you can break this project down to smaller chunks, I expect more answer from the community for you.

In addition, you can book a Virtual Solution Center session with real Alteryx experts, who are going to help you in the building process:
https://community.alteryx.com/t5/Virtual-Solution-Center/tkb-p/vsc

I wish you a great day

Phil 

[jmorris24 · Accepted solution]

Thanks. I found what I needed here. I really just listed the rest of it to give context to what I was trying to solve. No big deal. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Cohort-Workflow/td-p/439021

Thanks for the helpful info. 

Cheers!