狠狠撸

狠狠撸Share a Scribd company logo
Telecom Analytics Day III
Detecting Subscriber Fraud . . .
? High number of calls to Black Listed numbers
? High Roaming charges
? High Internet Usages
? High number of VAS calls
? Frequent Change of Address
? Pre-Subscription Check:
? Verify address
? Verify home number
? Set Credit Limits
? Check PAN number, UID against Credit Violations
? Check IMEI against Black Listed IMEI
? Check for matching names with black listed customers.
? Check for matching PIN codes.
? Check for addresses from notorious localities.
? Match subscriber usage profile with black listed subscribers :
? Called numbers
? Matching tower locations
? Calling patterns (short calls, long calls)
Detecting Recharge Voucher Fraud . . .
? Unusual top-ups
? High number of recharges in a given time-period
Detecting Pre-paid Balance Fraud . . .
? Track employees with high number of manual balance change
? Subscribers with high balances
Detecting Unauthorized Service Fraud . . .
? HLR vs. Postpaid subscriber profile reconciliation
? HLR services vs Postpaid Subscriber services Profile mis-match
? Sudden change in Subscriber usages (??)
Detecting SIM Cloning . . .
? Velocity Check
? Call Collision
Telecom analyticsday
Telecom analyticsday
Churn problem at Bad-Idea. . .
The CEO of Bad-Idea has come back Praxair Analytics Inc. with a new problem. He was very impressed
with the fraud solution implemented by Praxair and hopes that they will be able to help him out. Over
the last two years after Mobile number portability was introduced, about 20 million subscribers has
become inactive or has left Bad-Idea(post-paid users).
Bad-Idea… Let’s open a dating service
The CEO of Bad-Idea is under a lot of pressure from the board to increase his ARPU. The marketing
team has come up with a set of ideas. One of this idea is to start a service similar to Shadi.com. He
plans to start with sending a trial offer of the current subscriber base. He does not want to spam all the
subscribers with this offer. He wants to find out the subscribers who will be interested in this service.
Assignment Fraud Detection (20) . . .
1. Make a presentation to the CEO of Bad-Idea about how you can help him with Fraud
detection.
? Introduction to the company (make your imagination go wild)
? Typical Fraud Scenarios
? Case studies of companies who benefitted from your solution
? How you differentiate from the competitors.
2. Design the Star Schema for fraud analysis.
3. Collect at least 25 phone bills for the same month. Load this data into a database as a fact
table.
4. Write SQL queries to detect fraudulent conditions. (You will get results if your thresholds
are unrealistically low).
5. Deliverables:
? Power point presentation
? ER Diagram of the star schema
? SQL queries and output.
? Project Plan
Assignment Churn Reduction (20) . . .
1. Make a presentation to the CEO of Bad-Idea about how you can help him with Churn
Reduction.
? Introduction to the company (make your imagination go wild)
? Typical Churn Scenarios
? Case studies of companies who benefitted from your solution
? How you differentiate from the competitors.
2. Create a survey and ask your friends and families to complete it. The survey should
capture instances when the had a bad experience with their telecom provider. The survey
should also capture what will it take for these people to switch telecom providers.
(Collect at least 25 surveys)
3. List how you can detect these churn scenarios along with the subscribers.
4. Design the Star Schema for detecting Churn candidates.
5. Write Pseudo SQL queries to detect these conditions.
6. Deliverables:
? Power point presentation
? ER Diagram of the star schema
? Survey Questions
? Survey results in a quantifiable manner
? SQL queries
? Project Plan
Assignment Campaign Management (20) . . .
1. Make a presentation to the CEO of Bad-Idea about how you can help him with Location based services.
? Introduction to the company (make your imagination go wild)
? Example of location based services
? Case studies of companies who benefitted from your solution
? How you differentiate from the competitors.
1. List at least 20 businesses that will benefit from Location Based Services. (Hint: Stuff we buy on
impulse..). List 10 businesses that will not benefit from Location Based Services.
2. Identify the potential profiles of a person that will probably be influenced by the message for each of
these selected services.
3. Design the Star Schema for detecting the profiles.
4. Write Pseudo SQL queries to detect these conditions.
5. Create a survey and ask your friends and families to complete it. Ask them whether they will be
influenced by the location based offers. Also capture relevant demographic information. For each
service category list the following:
? False Positives (People who will receive the offer and will not take it up)
? False Negatives (People who would have taken this offer but were filtered out based on your
criteria)
? Positives
? Negatives.
6. Deliverables:
? Power point presentation
? ER Diagram of the star schema
? SQL queries
? Survey Questions
? Survey results in a quantifiable manner
? Project Plan
Ad

Recommended

On Database for Mobile Phones Ownership
On Database for Mobile Phones Ownership
Coldbeans Software
?
Telecom KPIs definitions
Telecom KPIs definitions
Ritesh Gupta ITIL? Certified
?
Reveneu frauds and telcos
Reveneu frauds and telcos
mrkhanlodhi
?
Fraud in Telecoms
Fraud in Telecoms
Erick O'Connor
?
Frauds in telecom sector
Frauds in telecom sector
sksahu099
?
Understanding Telecom SIM and USIM/ISIM for LTE
Understanding Telecom SIM and USIM/ISIM for LTE
ntel
?
Full gsm overview (modified)
Full gsm overview (modified)
Advanced group of Institutions
?
Telecom Roaming Overview
Telecom Roaming Overview
Shilpin Pvt. Ltd.
?
A Multi-Faceted Approach to CRM at United
A Multi-Faceted Approach to CRM at United
Vivastream
?
Customer Intelligence & Analytics - Part II: Exploring the Idea & Value of Ma...
Customer Intelligence & Analytics - Part II: Exploring the Idea & Value of Ma...
Vivastream
?
Strategic decision making using Big Data
Strategic decision making using Big Data
QlikView-India
?
Customer in control
Customer in control
Francisco J. Jariego, PhD.
?
Helping Your Marketing Department Fly Higher 4 12 11
Helping Your Marketing Department Fly Higher 4 12 11
Eric Webb
?
Project Examples
Project Examples
ls737100
?
Where Do We Go From Here?
Where Do We Go From Here?
cVidya Networks
?
Where do we go from here?
Where do we go from here?
cVidya Networks
?
Disney Field Study
Disney Field Study
Ashwanth Vemulapalli
?
When Worlds Collide - Big Data & Web Analytics in 2013 - Jean-Francois Belisle
When Worlds Collide - Big Data & Web Analytics in 2013 - Jean-Francois Belisle
Jean-Francois Belisle
?
Make Money with Big Data (TCELab)
Make Money with Big Data (TCELab)
Stephen King
?
Airtel’s customer relationship model
Airtel’s customer relationship model
ahirbansal
?
Service marketing
Service marketing
Moulish Bala
?
Service marketing
Service marketing
Moulish Bala
?
Symposium Data-Driven Marketing: Rogier van Nieuwenhuizen - Powering growth w...
Symposium Data-Driven Marketing: Rogier van Nieuwenhuizen - Powering growth w...
Marian Dragt
?
An Integrated IMC Data Framework
An Integrated IMC Data Framework
St. Edward's University
?
Customer Data Quality for CRM Systems
Customer Data Quality for CRM Systems
St. Edward's University
?
Data Management In Cellular Networks Using Activity Mining
Data Management In Cellular Networks Using Activity Mining
IDES Editor
?
B2B Marketing Clinic – Customer-Centric Content Development
B2B Marketing Clinic – Customer-Centric Content Development
Pardot
?
Social listening-insights-emetrics-presentation
Social listening-insights-emetrics-presentation
Performics
?
REGRESSION DIAGNOSTIC I: MULTICOLLINEARITY
REGRESSION DIAGNOSTIC I: MULTICOLLINEARITY
Ameya Patekar
?
apidays New York 2025 - Life is But a (Data) Stream by Sandon Jacobs (Confluent)
apidays New York 2025 - Life is But a (Data) Stream by Sandon Jacobs (Confluent)
apidays
?

More Related Content

Similar to Telecom analyticsday (20)

A Multi-Faceted Approach to CRM at United
A Multi-Faceted Approach to CRM at United
Vivastream
?
Customer Intelligence & Analytics - Part II: Exploring the Idea & Value of Ma...
Customer Intelligence & Analytics - Part II: Exploring the Idea & Value of Ma...
Vivastream
?
Strategic decision making using Big Data
Strategic decision making using Big Data
QlikView-India
?
Customer in control
Customer in control
Francisco J. Jariego, PhD.
?
Helping Your Marketing Department Fly Higher 4 12 11
Helping Your Marketing Department Fly Higher 4 12 11
Eric Webb
?
Project Examples
Project Examples
ls737100
?
Where Do We Go From Here?
Where Do We Go From Here?
cVidya Networks
?
Where do we go from here?
Where do we go from here?
cVidya Networks
?
Disney Field Study
Disney Field Study
Ashwanth Vemulapalli
?
When Worlds Collide - Big Data & Web Analytics in 2013 - Jean-Francois Belisle
When Worlds Collide - Big Data & Web Analytics in 2013 - Jean-Francois Belisle
Jean-Francois Belisle
?
Make Money with Big Data (TCELab)
Make Money with Big Data (TCELab)
Stephen King
?
Airtel’s customer relationship model
Airtel’s customer relationship model
ahirbansal
?
Service marketing
Service marketing
Moulish Bala
?
Service marketing
Service marketing
Moulish Bala
?
Symposium Data-Driven Marketing: Rogier van Nieuwenhuizen - Powering growth w...
Symposium Data-Driven Marketing: Rogier van Nieuwenhuizen - Powering growth w...
Marian Dragt
?
An Integrated IMC Data Framework
An Integrated IMC Data Framework
St. Edward's University
?
Customer Data Quality for CRM Systems
Customer Data Quality for CRM Systems
St. Edward's University
?
Data Management In Cellular Networks Using Activity Mining
Data Management In Cellular Networks Using Activity Mining
IDES Editor
?
B2B Marketing Clinic – Customer-Centric Content Development
B2B Marketing Clinic – Customer-Centric Content Development
Pardot
?
Social listening-insights-emetrics-presentation
Social listening-insights-emetrics-presentation
Performics
?
A Multi-Faceted Approach to CRM at United
A Multi-Faceted Approach to CRM at United
Vivastream
?
Customer Intelligence & Analytics - Part II: Exploring the Idea & Value of Ma...
Customer Intelligence & Analytics - Part II: Exploring the Idea & Value of Ma...
Vivastream
?
Strategic decision making using Big Data
Strategic decision making using Big Data
QlikView-India
?
Helping Your Marketing Department Fly Higher 4 12 11
Helping Your Marketing Department Fly Higher 4 12 11
Eric Webb
?
Project Examples
Project Examples
ls737100
?
When Worlds Collide - Big Data & Web Analytics in 2013 - Jean-Francois Belisle
When Worlds Collide - Big Data & Web Analytics in 2013 - Jean-Francois Belisle
Jean-Francois Belisle
?
Make Money with Big Data (TCELab)
Make Money with Big Data (TCELab)
Stephen King
?
Airtel’s customer relationship model
Airtel’s customer relationship model
ahirbansal
?
Symposium Data-Driven Marketing: Rogier van Nieuwenhuizen - Powering growth w...
Symposium Data-Driven Marketing: Rogier van Nieuwenhuizen - Powering growth w...
Marian Dragt
?
Data Management In Cellular Networks Using Activity Mining
Data Management In Cellular Networks Using Activity Mining
IDES Editor
?
B2B Marketing Clinic – Customer-Centric Content Development
B2B Marketing Clinic – Customer-Centric Content Development
Pardot
?
Social listening-insights-emetrics-presentation
Social listening-insights-emetrics-presentation
Performics
?

Recently uploaded (20)

REGRESSION DIAGNOSTIC I: MULTICOLLINEARITY
REGRESSION DIAGNOSTIC I: MULTICOLLINEARITY
Ameya Patekar
?
apidays New York 2025 - Life is But a (Data) Stream by Sandon Jacobs (Confluent)
apidays New York 2025 - Life is But a (Data) Stream by Sandon Jacobs (Confluent)
apidays
?
Untitled presentation xcvxcvxcvxcvx.pptx
Untitled presentation xcvxcvxcvxcvx.pptx
jonathan4241
?
Module 1Integrity_and_Ethics_PPT-2025.pptx
Module 1Integrity_and_Ethics_PPT-2025.pptx
Karikalcholan Mayavan
?
Grade 10 selection and placement (1).pptx
Grade 10 selection and placement (1).pptx
FIDELISMUSEMBI
?
最新版美国威斯康星大学拉克罗斯分校毕业证(鲍奥–尝毕业证书)原版定制
最新版美国威斯康星大学拉克罗斯分校毕业证(鲍奥–尝毕业证书)原版定制
Taqyea
?
apidays Singapore 2025 - What exactly are AI Agents by Aki Ranin (Earthshots ...
apidays Singapore 2025 - What exactly are AI Agents by Aki Ranin (Earthshots ...
apidays
?
Section Three - Project colemanite production China
Section Three - Project colemanite production China
VavaniaM
?
Hypothesis Testing Training Material.pdf
Hypothesis Testing Training Material.pdf
AbdirahmanAli51
?
Fundamental Analysis for Dummies.pdf somwmdw
Fundamental Analysis for Dummies.pdf somwmdw
ssuserc74044
?
apidays Singapore 2025 - Enhancing Developer Productivity with UX (Government...
apidays Singapore 2025 - Enhancing Developer Productivity with UX (Government...
apidays
?
Grote OSM datasets zonder kopzorgen bij Reijers
Grote OSM datasets zonder kopzorgen bij Reijers
jacoba18
?
QUALITATIVE EXPLANATORY VARIABLES REGRESSION MODELS
QUALITATIVE EXPLANATORY VARIABLES REGRESSION MODELS
Ameya Patekar
?
FME Beyond Data Processing: Creating a Dartboard Accuracy App
FME Beyond Data Processing: Creating a Dartboard Accuracy App
jacoba18
?
apidays Singapore 2025 - 4 Identity Essentials for Scaling SaaS in Large Orgs...
apidays Singapore 2025 - 4 Identity Essentials for Scaling SaaS in Large Orgs...
apidays
?
apidays New York 2025 - API Security and Observability at Scale in Kubernetes...
apidays New York 2025 - API Security and Observability at Scale in Kubernetes...
apidays
?
apidays Singapore 2025 - Building Finance Innovation Ecosystems by Umang Moon...
apidays Singapore 2025 - Building Finance Innovation Ecosystems by Umang Moon...
apidays
?
apidays New York 2025 - Using GraphQL SDL files as executable API Contracts b...
apidays New York 2025 - Using GraphQL SDL files as executable API Contracts b...
apidays
?
最新版美国亚利桑那大学毕业证(鲍础毕业证书)原版定制
最新版美国亚利桑那大学毕业证(鲍础毕业证书)原版定制
Taqyea
?
Data-Driven-Operational--Excellence.pptx
Data-Driven-Operational--Excellence.pptx
NiwanthaThilanjanaGa
?
REGRESSION DIAGNOSTIC I: MULTICOLLINEARITY
REGRESSION DIAGNOSTIC I: MULTICOLLINEARITY
Ameya Patekar
?
apidays New York 2025 - Life is But a (Data) Stream by Sandon Jacobs (Confluent)
apidays New York 2025 - Life is But a (Data) Stream by Sandon Jacobs (Confluent)
apidays
?
Untitled presentation xcvxcvxcvxcvx.pptx
Untitled presentation xcvxcvxcvxcvx.pptx
jonathan4241
?
Module 1Integrity_and_Ethics_PPT-2025.pptx
Module 1Integrity_and_Ethics_PPT-2025.pptx
Karikalcholan Mayavan
?
Grade 10 selection and placement (1).pptx
Grade 10 selection and placement (1).pptx
FIDELISMUSEMBI
?
最新版美国威斯康星大学拉克罗斯分校毕业证(鲍奥–尝毕业证书)原版定制
最新版美国威斯康星大学拉克罗斯分校毕业证(鲍奥–尝毕业证书)原版定制
Taqyea
?
apidays Singapore 2025 - What exactly are AI Agents by Aki Ranin (Earthshots ...
apidays Singapore 2025 - What exactly are AI Agents by Aki Ranin (Earthshots ...
apidays
?
Section Three - Project colemanite production China
Section Three - Project colemanite production China
VavaniaM
?
Hypothesis Testing Training Material.pdf
Hypothesis Testing Training Material.pdf
AbdirahmanAli51
?
Fundamental Analysis for Dummies.pdf somwmdw
Fundamental Analysis for Dummies.pdf somwmdw
ssuserc74044
?
apidays Singapore 2025 - Enhancing Developer Productivity with UX (Government...
apidays Singapore 2025 - Enhancing Developer Productivity with UX (Government...
apidays
?
Grote OSM datasets zonder kopzorgen bij Reijers
Grote OSM datasets zonder kopzorgen bij Reijers
jacoba18
?
QUALITATIVE EXPLANATORY VARIABLES REGRESSION MODELS
QUALITATIVE EXPLANATORY VARIABLES REGRESSION MODELS
Ameya Patekar
?
FME Beyond Data Processing: Creating a Dartboard Accuracy App
FME Beyond Data Processing: Creating a Dartboard Accuracy App
jacoba18
?
apidays Singapore 2025 - 4 Identity Essentials for Scaling SaaS in Large Orgs...
apidays Singapore 2025 - 4 Identity Essentials for Scaling SaaS in Large Orgs...
apidays
?
apidays New York 2025 - API Security and Observability at Scale in Kubernetes...
apidays New York 2025 - API Security and Observability at Scale in Kubernetes...
apidays
?
apidays Singapore 2025 - Building Finance Innovation Ecosystems by Umang Moon...
apidays Singapore 2025 - Building Finance Innovation Ecosystems by Umang Moon...
apidays
?
apidays New York 2025 - Using GraphQL SDL files as executable API Contracts b...
apidays New York 2025 - Using GraphQL SDL files as executable API Contracts b...
apidays
?
最新版美国亚利桑那大学毕业证(鲍础毕业证书)原版定制
最新版美国亚利桑那大学毕业证(鲍础毕业证书)原版定制
Taqyea
?
Data-Driven-Operational--Excellence.pptx
Data-Driven-Operational--Excellence.pptx
NiwanthaThilanjanaGa
?
Ad

Telecom analyticsday

  • 2. Detecting Subscriber Fraud . . . ? High number of calls to Black Listed numbers ? High Roaming charges ? High Internet Usages ? High number of VAS calls ? Frequent Change of Address ? Pre-Subscription Check: ? Verify address ? Verify home number ? Set Credit Limits ? Check PAN number, UID against Credit Violations ? Check IMEI against Black Listed IMEI ? Check for matching names with black listed customers. ? Check for matching PIN codes. ? Check for addresses from notorious localities. ? Match subscriber usage profile with black listed subscribers : ? Called numbers ? Matching tower locations ? Calling patterns (short calls, long calls)
  • 3. Detecting Recharge Voucher Fraud . . . ? Unusual top-ups ? High number of recharges in a given time-period
  • 4. Detecting Pre-paid Balance Fraud . . . ? Track employees with high number of manual balance change ? Subscribers with high balances
  • 5. Detecting Unauthorized Service Fraud . . . ? HLR vs. Postpaid subscriber profile reconciliation ? HLR services vs Postpaid Subscriber services Profile mis-match ? Sudden change in Subscriber usages (??)
  • 6. Detecting SIM Cloning . . . ? Velocity Check ? Call Collision
  • 9. Churn problem at Bad-Idea. . . The CEO of Bad-Idea has come back Praxair Analytics Inc. with a new problem. He was very impressed with the fraud solution implemented by Praxair and hopes that they will be able to help him out. Over the last two years after Mobile number portability was introduced, about 20 million subscribers has become inactive or has left Bad-Idea(post-paid users).
  • 10. Bad-Idea… Let’s open a dating service The CEO of Bad-Idea is under a lot of pressure from the board to increase his ARPU. The marketing team has come up with a set of ideas. One of this idea is to start a service similar to Shadi.com. He plans to start with sending a trial offer of the current subscriber base. He does not want to spam all the subscribers with this offer. He wants to find out the subscribers who will be interested in this service.
  • 11. Assignment Fraud Detection (20) . . . 1. Make a presentation to the CEO of Bad-Idea about how you can help him with Fraud detection. ? Introduction to the company (make your imagination go wild) ? Typical Fraud Scenarios ? Case studies of companies who benefitted from your solution ? How you differentiate from the competitors. 2. Design the Star Schema for fraud analysis. 3. Collect at least 25 phone bills for the same month. Load this data into a database as a fact table. 4. Write SQL queries to detect fraudulent conditions. (You will get results if your thresholds are unrealistically low). 5. Deliverables: ? Power point presentation ? ER Diagram of the star schema ? SQL queries and output. ? Project Plan
  • 12. Assignment Churn Reduction (20) . . . 1. Make a presentation to the CEO of Bad-Idea about how you can help him with Churn Reduction. ? Introduction to the company (make your imagination go wild) ? Typical Churn Scenarios ? Case studies of companies who benefitted from your solution ? How you differentiate from the competitors. 2. Create a survey and ask your friends and families to complete it. The survey should capture instances when the had a bad experience with their telecom provider. The survey should also capture what will it take for these people to switch telecom providers. (Collect at least 25 surveys) 3. List how you can detect these churn scenarios along with the subscribers. 4. Design the Star Schema for detecting Churn candidates. 5. Write Pseudo SQL queries to detect these conditions. 6. Deliverables: ? Power point presentation ? ER Diagram of the star schema ? Survey Questions ? Survey results in a quantifiable manner ? SQL queries ? Project Plan
  • 13. Assignment Campaign Management (20) . . . 1. Make a presentation to the CEO of Bad-Idea about how you can help him with Location based services. ? Introduction to the company (make your imagination go wild) ? Example of location based services ? Case studies of companies who benefitted from your solution ? How you differentiate from the competitors. 1. List at least 20 businesses that will benefit from Location Based Services. (Hint: Stuff we buy on impulse..). List 10 businesses that will not benefit from Location Based Services. 2. Identify the potential profiles of a person that will probably be influenced by the message for each of these selected services. 3. Design the Star Schema for detecting the profiles. 4. Write Pseudo SQL queries to detect these conditions. 5. Create a survey and ask your friends and families to complete it. Ask them whether they will be influenced by the location based offers. Also capture relevant demographic information. For each service category list the following: ? False Positives (People who will receive the offer and will not take it up) ? False Negatives (People who would have taken this offer but were filtered out based on your criteria) ? Positives ? Negatives. 6. Deliverables: ? Power point presentation ? ER Diagram of the star schema ? SQL queries ? Survey Questions ? Survey results in a quantifiable manner ? Project Plan

Editor's Notes

  • #3: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #4: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #5: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #6: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #7: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #8: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #10: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #11: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #12: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #13: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.
  • #14: Why we had the second UAT. Impression of ICTEAS not being serious, cause we should be able to generate the right report, right accountability.