Geographical Information Systems (GIS) is evolving from a standalone system utilized by Planning/Engineering/Operations to more of a management information system and an enterprise system, used across departments for various uses like Marketing/Sales decision support, Customer Relationship management, asset management, work management & work scheduling and field force management.
Realising and appreciating this potential of spatial data and functionality, the Rogers Cable marketing business wanted to build a centralised database for Business Intelligence activities in Marketing. As subscriber base and product offering continue to grow, the data integrity becomes essential, critical and fundamental to deliver accurate reporting, drives sales, improve serviceability and to perform network efficient network planning strategies. To handle changing business dynamics and meet customer expectations, for Rogers with multiple products and multiple networks, it is very important that they able to accurately target based on serviceability for any and all products across all addresses.
TCS has successfully implemented this GIS based Business Intelligence solution for the marketing department for enabling address mapping capabilities to support aggressive subscriber growth targets and strategic planning activity. This will enhance Rogers capability to view the serviceability, product penetration, network coverage data etc., for residential & commercial addresses. This information will be available to corporate (authorized) users in the form of centralized data, maps and reports. This application will be used by marketing departments of all lines of Business (Cable, Wireless and Media) as well as their senior executives. The system is already being used by more than 100 corporate users, with the numbers likely to grow with future phase implementation.
“…across the globe the companies are striving to build and develop integrated systems to make informed decisions. The integrated view of spatial and existing business intelligence systems enables the decision making quick, easier and accurate. “
“….adding spatial component to Rogers’s line of business – cable, wireless and media introduces a new dimension to view, identify, analyze and report business data and transforms the way of doing business. The tight integration of geographic boundaries and business reality has helped view new information which is a definite competitive edge.”
The key issue with any company’s marketing/sales department is to understand the competition against them. Secondly, provide better services and coverage with reduced cost has been a challenge since ages. All enterprises whether big or small needs a business intelligent utility for analyzing and making better decisions to cope up with these key issues. GIS (Geographical Information System) for that matter is evolving from a standalone system utilized by Planning/Engineering/Operations to more of a management information system and an enterprise system, used across departments for various functions like Marketing/Sales decision support, Customer Relationship management, asset management, work management & work scheduling and field force management. In today’s competitive environment GIS based Business Intelligence solution is a mandate for enhanced ability to target sales & marketing activity, improved operational efficiencies with removal of redundant processes, and capabilities to drive strategic sales and network planning activity to support long term business growth.
The Business Challenges
The cable engineering division has geographic details. The engineering division creates and manages the GIS data. To derive serviceability and penetration levels of products, the marketing department relies on data sourced from different departments. For cable products the footprint is available at address level but for non-cable products the details exists only at postal code level. The updates not being at a centralized location, the accuracy of results is always less.
The existing GIS data if moved to centralized spatial repository it will allow having a single source of truth for serviceability. Mapping of customers thereafter based on network coverage, penetration levels becomes easier.
The Cable & wireless business and GIS data maintained in isolation is also area of concern. The updates to these happen in different times depending on needs and requirement. Lack of synchronization between spatial and non-spatial causes inaccurate and incomplete data for reporting.
The manual and ad-hoc procedures for maintaining business and GIS address data causes redundancy or close to duplicate information.
Lack of Feedback on updated addresses back to source system is also one another challenge. This causes duplication of work and thus leads to inefficiency in the system.
The BI tools in the market are doing a good job on analyzing the basic what, who, when and how questions. Almost every transaction would have captured this information. The where of business data though exists in the present system in various forms like customer contacts, Ship-to, bill-to, region names, and central business districts their spatial analysis is limited. The centralised data when built along with spatial component enables the business reporting in a different angle. Business information like customer contacts, Ship-to, bill-to, region names, and central business districts exists in the present system as non-spatial data. These dataset are converted into actual geographic location in this project using the geo-spatial technology. This integration of spatial element to the business data in the warehouse enhances the data intelligence and improves the way it views and targets customers/prospects by providing enhanced information for every address location. This implementation gives the marketing department a competitive advantage with ability to identify what Rogers’ products exist and what products Rogers can provide at any given address (commercial & residential).
Spatially Enabled Solution
TCS Solution for addressing Rogers Cable marketing vision was based on below considerations.
Subscriber / Customer information was distributed among varied Oracle data sources
The data sources were updated on daily basis (5million cable/wirelines & 8 million wireless/mobile)
Multiple geographic files come from internal and external sources
The Spatial (Map) and BI integration solution architecture is given (Figure 1)
Figure 1: The Spatial (Map) and BI integration solution architecture
Backroom Design and ETL Load
The existing line of business namely the cable, wireless and address data are spread among various Oracle data sources in the 9i version. The target data warehouse (in this case Mapmart), the spatially enabled solution is delivered as Oracle 10g spatial database. With focus on providing a single source of truth across business and departments, the design of spatial enabled database was carried out. Inventory of all geographic files (MapInfo - *.TAB, CAD, SpatialInfo) files are done with respect to their purpose, type and frequency of update. The geographic files – administrative boundaries (province, county, and municipalities), central business units, road network are converted as oracle spatial tables in the target (mapmart database). This activity is carried out using the Spatial ETL tool (MapInfo Easy loader). The business data (non-spatial) are pushed to target using Informatica ETL. The entire ETL workflow is automated as part of the solution and is triggered through CONTROL-M job scheduler.
The target spatial database across all address / location specific table-records is ensured for a Address geocoding process using Post – SQL procedures. The spatial tables are marked by SDO_GEOMTERY columns. The update to these columns completes the spatial datawarehouse integration
The business GIS functionalities are delivered over the web browser using the webservices. Geocoding engine of Envinsa (see spatial Integration of Address records) is used for converting standard and non-standard addresses into geographic co-ordinates. These co-ordinates are then by Presentation Service, WFS, WMS, Cogo Service, Co-ordinate service, Find Location service, Directory service for addressing various functionalities. Each service has specific roles associated with it, based upon which it processes the incoming request.
Address Data Spatial Integration
Figure 2 presents the steps for integrating the address spatial component to the designed datawarehouse.
Figure 2: Steps for Integrating the Address Spatial Component to the Designed Datawarehouse.
Source System – (Various ) with customer address details ( Address Sequence, Street#, Streetname, Street direction, City/province and Postal Code)
The Business ETL task is triggered through a CONTROL – M (Scheduler) script
- The database scripts (inclusive of spatial tables) gets executed to create the staging, target and reject table structure
- Extraction of customer address data from the source systems
Comparison of customer address for address accuracy and completeness
- Loading of customer address to Staging tables
- Matched customer address are populated to staging table based on address sequence
- Unmatched records are pushed to Reject table
- Reject table records undergo Data Cleansing
GIS Layers are converted as Spatial table records using Spatial ETL
- Customer address data is concatenated into a single field data and populated to target table
The address field data is taken for geocoding process
- Cleansed data is again put into cycle until being populated into staging table
- Street address given to Geocoding engine
- Geocoding script is executed to convert the street address into Latitude and Longitude
Summarized data is stored as table reports & views
Spatial boundary overlays with customer addresses
Spatially enabled web application
Web / Application Server
- Lat, Long is populated in target address table (geoloc column) using a post sql query
Mapmart application code is deployed inside web/application server. The Web Server establishes the communication between mapmart presentation layer and application layer through HTTP protocols. The user requests generated from the mapmart application at presentation layer are passed on to the business logic implemented inside custom code at application layer. Once the request is processed, the results are streamed back from the application server to the web browser through this web server.
Intranet users access the Mapmart Application thorough their standard desktop web browsers (Internet Explorer). The web application displayed in the web browser has two parts. One part is
customized application functionalities built over and above the base GIS product and the other part is the Map viewer section for the end users to display and interact with location intelligent data.
The application is developed as an n-tier web application with Presentation Layer, Application Layer and Database Layer as the three main high level tiers. Mapmart application is built on centralized Oracle spatial database.
At the application layer the custom code, designed using Java is deployed in the Application Server. The code interacts with the end user as well as the Web Service(s) through SOAP/HTTP protocols and enables viewing of map data across the enterprise by implementing the required functionalities. The custom code primarily makes use of the Envinsa API’s which are exposed in the form of web services to display spatial business functionalities.
End user accesses the application though web browser. Mapmart application is embedded with a map viewer/component to enable map viewing inside it. Request generated from the application is passed on to application custom code via web-server. The application code, which is residing inside the application server, processes these client requests using server API’s exposed in the form of web services. The Web Services further communicates with the Oracle Spatial database to fetch the required data and to build/render the final map. The rendered map is routed back to the custom code, which further passes it back to the web browser. The browser displays the results of the rendered map onscreen through the embedded map viewer control (Figure 3).
Customized User Interface Screen Layout
Map Window Tools
Map working area
- Drop-Down Menus
- Map window tools
- Info Attribute Details
- Layer Control
- Integrated Online help
- Logo Display
Selecting a business / land base feature
- Zoom Tools: Zoom In, Zoom Out, Zoom Extent, Zoom to Scale
- Measure Tool (Length for Linear features)
- Identify – Displays attribute information on Customer and Product details
- Clear selection
- Executive dashboard
- Blackberry Application
Theme Maps and Information
- Find Address search
- Address search based on Street Intersection
- Draw Map based on Geographic and Business regions
- Zoom-to-location ( render map based on important cities, towns, municipalities for further drill down)
- Render maps based on Address penetration – connected / disconnected
- Render maps based on Products subscribed against provided services
- Render maps based on penetration levels in the geographic region – by municipality, Forward sorting area, postal code and business districts
- Display Information attributes based on Select type
- Render maps based on Store locations, Landscape and competitors
- Churn Addresses
- Campaign activities – measuring effectiveness of campaign
- New Acquisitions
- Matched customer base ( cable and wireless) mapping
- Customer satisfaction – gain / loss analysis
- Enable door-to-door Sales
- Access subscriber information on the field
- Improved dashboard reporting through user-friendly view, bar charts and graphs
- Dashboard with dial-gauge reporting for Instant view of customer and product data
- Integrated table-map report showing customer count across geographic region based on time
- Comparative map representation – Monthly, Quarterly and Yearly
Figure 3: Mapmart Application
This project was a unique blend of GIS and BI capabilities. It was the first GIS telecom project for TCS in the Canada geography and the first TCS delivered GIS application for marketing analysis in the North American geography (Highlights given in Figure 4)
The solution revolves around an Oracle Spatial database which is updated daily through ETL processes running on Informatica and MapInfo Easyloader. These ETL processes were run as an initial load to load data the first time. Subsequent to that, delta (incremental) data is loaded on a daily basis using automated ETL processes.
There are various database level processes which are triggered on data load, which populate spatial reports, views and materialized views.
A geocoding process to populate Lat/Long values for address/subscriber locations is triggered as part of the entire process. MapInfo Envinsa is used as the geocoding engine.
Figure 4: Solution Environment Datawarehouse
CONTROL-M scheduling is used to trigger all processes automatically without the need of any manual intervention.
There are more than 30 data sources (internal and external) for the system; 130 Informatica ETL workflows and 150 Windows Batch processes to load data into the Oracle Spatial database.
A front-end web application has been developed using Java/JSF utilizing the MapInfo Envinsa web services for map presentation, geocoding and dynamic content management.
Map administration and authoring is handled through MapInfo Envinsa Content Manager and Map Manager.
Improved target marketing capabilities for Rogers, both for cable (wireline) and wireless subscribers. This was achieved through provision of the following capabilities:
Improved Campaign management capabilities:
- Capability to visualize at an address level, the products/services subscribed/not subscribed at an address.
- Roll up of this information at a postal code, district, municipality or province level.
- Identify cold, active and never connected addresses visually.
- Visualize product penetration levels starting from a building level to a postal code, district, municipality, Business Unit or province level. Identify areas or low/high penetration.
- Identify low/high performing areas.
Executive Dashboard capabilities:
Identify visually the areas where a campaign has been run.
- Identify and locate addresses campaigned.
- Measure campaign success
- Identify areas where a particular type of campaign can be more successful.
Improve customer satisfaction:
- Instantaneous and user-friendly view to information. No need for time consuming or cumbersome reporting processes.
- Better presentation of data in the forms of maps, dial gauges and snapshot reports.
- Enhanced decision making capability.
Demographic analysis :
- Identify geographically areas where service shut-offs have been more, in order to avoid future shut-offs; improve the servicing of that area and mitigate customer complaints.
- Improve customer retention (Decrease defections):
- Identify reasons to customer defections.
- Attribution of customer defections to network outages, network issues. Take remedial measures for the same.
View and analyze product penetration and marketing campaigns with respect to demographics.
- Calculate penetration percentages for wireless services/products.
- Target marketing campaigns for ethic groups; age groups etc. for promotion of certain products.
To help make effective operational decisions in the business, the organizations need to handle huge amount of datasets. It is also a fact that 80% of organization data are associated with geographic location. The GIS and BI industry has recognized this and have started to complement each other. The integration between these two technologies are growing with advent of internet, service oriented architectures and availability of spatial datasets commercially. The scope of applications and analysis being enormous, the Industries should quickly adopt to visualize and realize their benefits