In Predicting Bill Shocks at Telus Mobility the goal was to develop a model to predict unexpectedly high bills (bill shocks) based on customer profile and usage patterns, to allow Telus to identify customers who are likely to have a bill shock and determine their main characteristics. Although there was available a big amount of data related to bill shocks, such as customer bills, bill shock credits and customers’ call records, there was no way of knowing if a given bill had caused a bill shock or not.

Solution: a SQL query was created to read though call memos looking for bill shock related keywords using the LIKE comparison operator. Examples of such keywords are: “shock”, “roaming charg”, “changed his plan”, “went over her data” or French words like ‘pas couvert’ referring to features not covered. Also, by looking at the memo types individually it was noticed that certain memos should not be flagged as bill shock due to the context of the call. These memo types where filtered using a NOT IN condition. The query skimmed through the calling memos and flagged the matching entries as bill shock calls.