Hi all - thanks in advance.
I typically run analysis in Excel, but this file is quite large, so need to use Alteryx. I am relatively new and have been able to do some formulas, but I need to add on a few that I am not so comfortable with. If anyone has thoughts on any of the following functions (even if only 1-2), that would be immensely helpful. Message if any questions!
See attached current data file structure – few things to note:
- Account_ID = each account ID represents a customer account
- Month = Month / Year that customer / account were active. Note: companies with “-“ or “0” in a given month should be considered as non-active
- ARR = ARR for account in given month / year
Tasks I am trying to do
- Churn Indicator – I want to add in a column that flags each account on whether they have churned or not. My definition is if they have revenue in 2023-09 (in ARR column), they should be tagged as “Active” and all other Account IDs should be “Churned”
- Length of customer (# of months)– how many months was the account_ID active for? (i.e., if they have revenue across 3 months, they get a 3 value, etc.)
- Spend buckets – I ideally want to tag each account_ID based on their ARR value in 2023-09 – i.e., “high”, “medium”, “low”, where maybe everyone with <$5M ARR goes in “Low”, etc. à I can determine the buckets after (or potentially do groupings based on 25th percentile, 25-50th, 50th – 75th, top 75th) but would be helpful to know the mechanics
- Spend growth buckets – ideally, I want to calculate for each customer their respective CAGR value on a monthly basis (taking their second active month against their latest) – we want to use their second active month because the first one is not fully realized ARR because they might have signed mid-month. Once we tag each account_ID to their CAGR, then ideally we would want to bucket them similar to #3
