Challenge #17: Month-over-Month Retention Rate

Here is this week’s challenge, I would like to thank everyone for playing along and for your feedback. The link to the solution for last challenge #16 is HERE. This week’s assignment is listed as an advanced exercise due to the requirement of using a batch macro for the solution.

The use case: A bank is looking to calculate customer retention rate month over month. The denominator in the calculation are all of the accounts open 24 months prior to the start of the month. For example, for June 2013, the denominator would be the total number of accounts open between June 1, 2011 through May 31, 2013. The numerator will be total number of accounts closed in June 2013 or between June 1, 2013 through June 30, 2013.

The objective is to create a batch macro that calculates the retention rate for May, June, July and August.

challenge_17_solution.yxzp

challenge_17_start_file.yxmd

Parse

Macros

Join

Preparation

Advanced

Transform

Interface

Difficult

Accepted answers

All comments

TaraM

The solution has been uploaded

Spoiler

alex

It's nice to see the macro answer and learn something new! I got stuck on this one since I was trying to do it without adding a start of month table and I have very little experience with macros. I was able to solve it without using a macro and without having to manually add a Start of Month table. I had one hard coded filter to remove activity prior to May 2013 so that my results matched the same date ranges shown in the solution. It doesn’t really need to be included to make this work. The 24 month criteria for calculating the numerator (open accounts from prior 24 months) is also hard coded based on the directions. Everything else is dynamic so that if you had accounts older than 24 months they would not be included in the calculation nor would you need to add a record in the Start of Month table to include September 2013. I annotated the best I could while trying to keep the canvas clean. I could have added tool containers, but I need to move on. I chopped it into 2 pictures so that it is legible. I’d be happy to answer any questions on the workflow formulas.

Spoiler

SeanAdams

Quite a different approach within the Batch macro from @GeneR's (or @TaraM's) solution, but the inputs to the batch macro are very similar.

Spoiler

- Added a set of fields to make maintanability easier and the logic more obvious:
- One for the control param being the month in question
- one for a date 24 months previous
- one as a boolean flag to say "is this account open right now"
- one as a flag to say "did this account close in the window in question" etc
- Then we just take a sum across these flags
- Then, same as @GeneR / @TaraM, calculate the ratio and send out to the calling macro

I didn't use the control param through an action component - but instead straight into a formula

challenge_17_SeanSolution_package.yxzp

NicoleJohnson

My solution. Watched the webinar on Batch Macros today, so this one was perfect practice Pretty pleased I was able to do this one in just a few tools!

Note: The instructions indicate numerator is Open & denominator is Closed, but then results in the start file have it showing calculated as the opposite. In addition, I believe to be accurate with identifying a true "Retention Rate," you'd want to know the # of customers remaining after closed accounts... so wouldn't it be (Open - Closed)/Open? Just a thought. I solved for both in my solution, just in case!

Spoiler

challenge_17_NicoleJohnson.yxmc

estherb47

challenge_17_EHB_solution.yxzp

Laurap1228

Here is my solution!

challenge_17_LP.yxzp

DE0413

This one was a struggle for me, but finally managed to solve and it looks to be similar to other solutions.

Spoiler

AlteryxWeeklyChallengeWeek17package.yxzp

LordNeilLord

I had to look at the solution for this to help get my Open/Close formula correct

Spoiler

challenge_17_LNL.yxzp

patrick_digan

Spoiler

challenge_17_start_file.yxzp

MaxGiegerich

Solved! Although not getting the same numbers as the output... My checks seem to add up for instance:

2013-06-01 1/4 = 0.25:
- Open within prior 24 months = 4 (ID 1-4)
- Closed in month = 1 (ID 4)

Output has 1/3. Also denominators are usually on the bottom. Great macro practice though! Feel like I got this one licked quite efficiently, assuming I'm correct!

Weekly Challenge #17 MG.yxzp

nick_ceneviva

Solution is attached. Tried to go at this without a batch macro and was not getting very far. The Batch macro simplified this greatly.

challenge_17_Ceneviva.yxzp

JoshKushner

The tricky part here was realizing I shouldn't count accounts that had already closed.

Batch Macro:

Spoiler

challenge_17_start_file.yxzp

Natasha

Definitely agree with others that the wording for closed/open accounts could be improved.

Spoiler

challenge_17_Natasha.yxmd

Challenge17_BatchMacro.yxmc

dsmdavid

Very confused with the wording of the text...

solved#17_d.yxzp

Phil_L57

I've uploaded 2 solutions, one with a generate rows and one with the batch macro

challenge_17_start_filevPL.yxmd

challenge_17_start_filevPL2.yxzp

samN

the retention percentage seems a little off. Seems it should be 1-% to get the true retention rate instead of reporting churn rate? Anyways good problem

challenge_17.yxzp

ggruccio

A bit convoluted, but able to complete without using a macro.

challenge_17_gg_finish.yxmd

LandonG

My solution.

challenge_17_start_file.yxzp

philipmannering

Solution attached

Spoiler

challenge_17 - Month over Month Retention.yxzp

jasperlch

Solution attached.

challenge_17_JL.yxzp

michalsicak

This challenge showed me there's still a lot to work for me to improve on UI and macros in Alteryx.

Spoiler

challenge_17_start_file.yxmd

Batch_Macro.yxmc

aclaxton

Finally got it working! I took a much different route than other answers but I was trying to keep it dynamic...

Spoiler

jamielaird

This took me longer to nail than any other challenge so far; in particular the logic for the "Last 2 Years" number. Ended up writing and re-writing the logic as comments and trying different approaches until my numbers matched up.

Spoiler

challenge_17_solution_JL.yxzp

DriveMyData

Week 17.yxmd

JosephSerpis

Challenge Completed

Challenge_17_Joe_Serpis.yxzp

asabau

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703