Tuesday, July 27, 2010

Calculating Tenure when your Cancel Date is in the future

A configuration on our EComm system requires that every subscriber have a NOT NULL Cancel Date.  This presented some difficulty for calculating tenure, particularly since the default for paying subscribers is 2999-01-01.  Wow!  That's some great tenure.  They've been paying subscribers longer than we've been in business.

For the past few weeks I've been grappling with how to adjust the CancelDt and calculate tenure.  Since I'm using Tableau on top of Teradata, I've dug into both and decided on the below.

These calculations are included in a monthly churn workbook I produce for our product team.  The workbook is updated at the beginning of each month and analyzes churn and media adoption for the previous month, it's preceding three month average, and the same periods in the previous year.  The dates are configured to update dynamically, so the current_date assignment is valid for the entire month the workbook is valid.  Once the workbook updates for the following month, you'll be ahead and the current_date assignment will remain in the month ahead of the study month.

Previous method for adjusting the CancelDt and calculating churn:

In Teradata create a view or use custom query for Tableau

select RptMonth
, Id
, SignupDt - (EXTRACT(DAY from SignupDt) -1) as SignupMo
, CancelDt - (EXTRACT(DAY from CancelDt) -1) as CancelMo
from foo


Then in Tableau, create a custom metrics to resolve the future date
"CancelMo Truncated"
IIF([CancelMo] > TODAY(), DATETRUNC('month',TODAY()),[CancelMo])

and  to calculate "Tenure"

DATEDIFF('month',[SignupMo],[CancelMo Truncated])


Updated Method
Push it all back to the database:


select RptMonth
, Id
, SignupDt 
, CASE WHEN CancelDt = '2999-01-01' THEN current_date ELSE CancelDt END CancelDtUpd
, (CancelDtUpd - SignupDt) month(4) as Tenure
, CASE WHEN CancelDt = '2999-01-01' THEN 1 ELSE 0 END PayingFlag
from foo

4 comments:

  1. My Brothers and Sister all over the world, I am Mrs Boo Wheat from Canada ; i was in need of loan some month ago. i needed a loan to open my restaurant and bar, when one of my long time business partner introduce me to this good and trustful loan lender DR PURVA PIUS that help me out with a loan, and is interest rate is very low , thank God today. I am now a successful business Woman, and I became useful. In the life of others, I now hold a restaurant and bar. And about 30 workers, thank GOD for my life I am leaving well today a happy father with three kids, thanks to you DR PURVA PIUS Now I can take care of my lovely family, i can now pay my bill. I am now the bread winner of my family. If you are look for a trustful and reliable loan leader. You can Email him via,mail (urgentloan22@gmail.com) Please tell him Mrs Boo Wheat from Canada introduce you to him. THANKS.

    ReplyDelete
  2. Hello Everybody,
    My name is Mrs Sharon Sim. I live in Singapore and i am a happy woman today? and i told my self that any lender that rescue my family from our poor situation, i will refer any person that is looking for loan to him, he gave me happiness to me and my family, i was in need of a loan of S$250,000.00 to start my life all over as i am a single mother with 3 kids I met this honest and GOD fearing man loan lender that help me with a loan of S$250,000.00 SG. Dollar, he is a GOD fearing man, if you are in need of loan and you will pay back the loan please contact him tell him that is Mrs Sharon, that refer you to him. contact Dr Purva Pius,via email:(urgentloan22@gmail.com) Thank you.

    BORROWERS APPLICATION DETAILS


    1. Name Of Applicant in Full:……..
    2. Telephone Numbers:……….
    3. Address and Location:…….
    4. Amount in request………..
    5. Repayment Period:………..
    6. Purpose Of Loan………….
    7. country…………………
    8. phone…………………..
    9. occupation………………
    10.age/sex…………………
    11.Monthly Income…………..
    12.Email……………..

    Regards.
    Managements
    Email Kindly Contact: urgentloan22@gmail.com

    ReplyDelete
  3. Hello Everybody,
    My name is Mrs Sharon Sim. I live in Singapore and i am a happy woman today? and i told my self that any lender that rescue my family from our poor situation, i will refer any person that is looking for loan to him, he gave me happiness to me and my family, i was in need of a loan of S$250,000.00 to start my life all over as i am a single mother with 3 kids I met this honest and GOD fearing man loan lender that help me with a loan of S$250,000.00 SG. Dollar, he is a GOD fearing man, if you are in need of loan and you will pay back the loan please contact him tell him that is Mrs Sharon, that refer you to him. contact Dr Purva Pius,via email:(urgentloan22@gmail.com) Thank you.

    BORROWERS APPLICATION DETAILS


    1. Name Of Applicant in Full:……..
    2. Telephone Numbers:……….
    3. Address and Location:…….
    4. Amount in request………..
    5. Repayment Period:………..
    6. Purpose Of Loan………….
    7. country…………………
    8. phone…………………..
    9. occupation………………
    10.age/sex…………………
    11.Monthly Income…………..
    12.Email……………..

    Regards.
    Managements
    Email Kindly Contact: urgentloan22@gmail.com

    ReplyDelete
  4. My name is.Mrs.Anna Daniel. I live in Ukraine i am a happy woman today? i need to use this time to tell all people how i got my loan from this honest and God fearing man loan lender that help me with a loan of $84,000 please contact him. if you also need a loan without any problem he name is Dr Purva Pius email (urgentloan22@gmail.com) tell him that is Mrs.Anna Daniel that refer you to he contact Email (urgentloan22@gmail.com)

    ReplyDelete