About Customer Churn

Customer churn is a problem faced by every business. However in telecoms customer churn is a major issue. This is due to the fact that companies that provide a measurable service are constantly being evaluated by their customers.

Basic Definition: "The customer base which has been silent (no activity) in the network from a specific time period."

Churn Criteria 

Churn calculation varies company to company depending silent duration and network activities

Criteria can be based on following point.

• No single network activities for a specific period time (Weekly, Monthly, N Days)
• No direct revenue generating usages for a specific period time (Weekly, Monthly, N Days)
• No Direct (MOC, SMSMO, Service Fee, Data, MMSMO) & Indirect revenue generating (MTC, SMSMT – MO-MT) usages for a specific period time (Weekly, Monthly, N Days)

Best Practice: Subscriber who has not a single event (VOICE, SMS, DATA, Recharge) for N days; Where N = 30, 60, 90 Days

How to calculate churn - Idle Solution:

-------------------------------------------

Maintain a repository for all MSISDN as below

MSISDN	LAST_VOICE_ACTIVITY_DATE	LAST_DATA_ACTIVITY_DATE	LAST_VAS_ACTIVITY_DATE

Data Source:

Prepaid Subscriber: CCN Voice, CCN Data, CCN SMS & VAS, Refill

Postpaid: Billing System/ BSCS

Please email to webappslnbd@gmail.com for hints how to prepare the above table.

Query Hints:

SELECT MSISDN FROM TABLE WHERE LAST_VOICE_ACTIVITY_DATE < SYSDATE-90 AND LAST_DATA_ACTIVITY_DATE < SYSDATE-90 AND LAST_SMS_VAS_ACTIVITY_DATE < SYSDATE-90

How to calculate churn - Quick Solution:

--------------------------------------------

Maintain a repository for all MSISDN as below from MSC

MSISDN	LAST_ACTIVITY_DATE

Data Source: MSC

Please email to webappslnbd@gmail.com for hints how to prepare the above table.

Query Hints:

SELECT MSISDN FROM TABLE WHERE LAST_ACTIVITY_DATE < SYSDATE-90

Net Churn:

Net Churn of a month = Total Churn within this month – Churn Back (become active from churn)

Top Reason for Customer Churn:

• User has switched to other operator/network
• Better network on switched operator
• Better value  = price+quality+service on switched operator
• More suitable product on switched operator
• Attractive Campaign/offer on switched operator
• User is using different MSISDN on same network
• Attractive start-up offer on new MSISDN

Why I need to know churn base:

http://rafm360.blogspot.com/2013/11/how-to-sustain-revenue-growth.html

Customer Churn Statistics: