Churn Rate From Contract Start & End Dates

Question

I am trying to calculate the churn rate of customer and all I have to play with is contract start & end dates.

When the date year is 9999 it just means they are still a customer and I would like to consider account only who have a tenure of more than 3 months (as in have been with the company for 3 or more months).

CREATE TABLE #temp
(
 ACCOUNT varchar(20)NOT NULL
,CONTRACT_START_DATE DATETIME NOT NULL
,CONTRACT_END_DATE DATETIME NOT NULL
)
;


INSERT INTO #temp
VALUES('64074558792','20160729','20170805');
INSERT INTO #temp
VALUES('54654654664','20160810','20170110');
INSERT INTO #temp
VALUES('21454654764','20160112','20160812');
INSERT INTO #temp
VALUES('21654765134','20160101','20161231');
INSERT INTO #temp
VALUES('13214868794','20160811','99991231');
INSERT INTO #temp
VALUES ('88321546894','20160427','20160627');

SELECT *,
CASE WHEN CONTRACT_END_DATE <> '99991231' THEN DATEDIFF(DAY, CONTRACT_START_DATE,CONTRACT_END_DATE) ELSE null END AS TENURE
FROM #temp

I am trying to find out how many customer stay with the company 12 months later. — Danielle, May 02 '19 at 01:16
Total number of customers lost during a year, the percentage of customers lost in relation to the total number of customers. — Danielle, May 02 '19 at 01:19

score 0 · Answer 1 · answered May 02 '19 at 06:57

The below will work for a single reporting year. There's a few things it seems like you're trying to get:

-Number of customers who stay longer than x months

-% of customers that stay for more than x months

-% of customers lost during the year

DECLARE @records TABLE
(
 ACCOUNT varchar(20)NOT NULL
,CONTRACT_START_DATE DATETIME NOT NULL
,CONTRACT_END_DATE DATETIME NOT NULL
);

DECLARE @ReportingYear INT = 2016;
DECLARE @MinTenureMonths INT = 3;


INSERT INTO @records
VALUES('64074558792','20160729','20170805'),
('54654654664','20160810','20170110'),
('21454654764','20160112','20160812'),
('21654765134','20151011','20161231'),--I changed this one
('13214868794','20160811','99991231'),
('88321546894','20160427','20160627');

SELECT *,
[CustomerGained]    = IIF(YEAR(CONTRACT_START_DATE) = @ReportingYear,1,0),
[CustomerLost]      = IIF(YEAR(CONTRACT_END_DATE) = @ReportingYear,1,0) ,
[ExistingCustomer]  = IIF(CONTRACT_START_DATE < DATEFROMPARTS(@ReportingYear,1,1)
                        AND CONTRACT_END_DATE > DATEFROMPARTS(@ReportingYear,12,31), 1, 0),
[CurrentCustomer]   = IIF(CONTRACT_END_DATE = '99991231 00:00:00.000',1,0),
[MinTenureExceeded] = IIF(DATEDIFF(MONTH, CONTRACT_START_DATE, CONTRACT_END_DATE) >= @MinTenureMonths,1,0)
FROM @records;

With recordCTE AS
(
SELECT 
    [CustomersGained]   = SUM(IIF(YEAR(CONTRACT_START_DATE) = @ReportingYear,1,0)),
    [CustomersLost]     = SUM(IIF(YEAR(CONTRACT_END_DATE) = @ReportingYear,1,0)),
    [MinTenureExceeded] = SUM(IIF(DATEDIFF(MONTH, CONTRACT_START_DATE, CONTRACT_END_DATE) >= @MinTenureMonths,1,0)),
    [ExistingCustomers] = SUM(IIF(CONTRACT_START_DATE < DATEFROMPARTS(@ReportingYear,1,1)
                            AND CONTRACT_END_DATE > DATEFROMPARTS(@ReportingYear,12,31), 1, 0)),
    [CurrentCustomer]   = SUM(IIF(CONTRACT_END_DATE = '99991231 00:00:00.000',1,0)),
    [TotalCustomers]    = COUNT(1)
FROM @records
)

SELECT  c.CustomersGained,
        c.CustomersLost,
        c.MinTenureExceeded,
        c.ExistingCustomers,
        [% Lost] = CAST(((c.CustomersLost * 1.0) / c.TotalCustomers) * 100 AS DECIMAL(14,2)),
        [% Gained] = CAST(((c.CustomersGained * 1.0) / c.TotalCustomers) * 100 AS DECIMAL(14,2))
FROM recordCTE c;

You'll need to decide whether you're just looking for a single reporting year, a reporting period, or in total for everything. If you want everything, just do all the calcs using CurrentCustomers instead.

Churn Rate From Contract Start & End Dates

1 Answers1