{"id":922,"date":"2014-12-09T11:57:57","date_gmt":"2014-12-09T18:57:57","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?p=922"},"modified":"2015-04-24T09:46:28","modified_gmt":"2015-04-24T16:46:28","slug":"indentifying-bill-shock-calls-using-sql","status":"publish","type":"post","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/2014\/12\/09\/indentifying-bill-shock-calls-using-sql\/","title":{"rendered":"Text mining with SQL to find bill shock calls"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" class=\"alignright wp-image-970\" src=\"https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/12\/query.png\" alt=\"\" width=\"161\" height=\"128\" \/>In <a href=\"http:\/\/www.sauder.ubc.ca\/Faculty\/Research_Centres\/Centre_for_Operations_Excellence\/Industry_Projects\/~\/media\/0FCA79DF8D95419392BB3AE8F3621B7A.ashx\" target=\"_blank\">Predicting Bill Shocks at Telus Mobility<\/a> 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\u2019 call records, there was no way of knowing if a given bill had caused a bill shock or not.<\/p>\n<p>Solution: a SQL query was created to read though call memos looking for bill shock related keywords using the <a title=\"Comparison Keywords\" href=\"https:\/\/blogs.ubc.ca\/coetoolbox\/databases\/comparison-keywords\/\">LIKE comparison operator<\/a>. Examples of such keywords are: \u201cshock\u201d, \u201croaming charg\u201d, \u201cchanged his plan\u201d, \u201cwent over her data\u201d or French words like \u2018pas couvert\u2019 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 <span style=\"color: #0000ff;\">NOT IN condition<\/span>. The query skimmed through the calling memos and flagged the matching entries as bill shock calls.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":22982,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5015,2763,286926],"class_list":["post-922","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-analytics","tag-databases","tag-sql"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/posts\/922","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=922"}],"version-history":[{"count":10,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/posts\/922\/revisions"}],"predecessor-version":[{"id":971,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/posts\/922\/revisions\/971"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=922"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/categories?post=922"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/tags?post=922"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}