Rogers Communications: E-commerce Data Management
Database Design & Product Information Management
The Rogers Cable/Internet and Wireless business units had separate Billing systems when the original E-commerce website was being defined, and the company was in the process of implementing a new single billing system for all units.
The E-commerce team decided to create a single product catalogue application combining each business unit's billing system's data schema.
Collaborated with the Dev team's DBA and the Wireless subject matter experts to define the SQL Server database tables, fields and formats, and business rules needed to facilitate the Wireless online sales and support processes.
Created a catalogue specifications document outlining the field definitions, relations to other tables, procedures for managing the Wireless data, and trained team members.
Created requirements to integrate the content management (CMS) product page templates with the product catalogue to display product comparisons and related products.
Partnered with Marketing to create English / French content and meta-data style guides for the product catalogue, CMS pages, and the site's internal Google Search Appliance.
E-commerce Master Data
The Cable and Internet divisions had a separate Billing system, and we collaborated to create the requirements, fields and formats for a single Customer and Orders database for all divisions to facilitate online bundled offers and discounts and omni-channel marketing.
All division's Billing systems were integrated into the website's payment services application to allow existing customers to purchase products and pay with their existing account.
Sales Operations
Partnered with UPS Logistics to define the order and shipping file formats, operational processes, reporting requirements and Service Level Agreements.
Provided Merchandising quarterly sales forecasts and min / max inventory levels by SKU to trigger inventory replenishment.
Partnered with Finance, Legal, Customer Service, and Fulfillment to create manual and automated anti-fraud procedures and processes, including a suspected / confirmed fraud orders table.
Day-to-day duties included overseeing and coaching the Sales and Operations team's Fulfillment, Fraud, and Marketing Analysts, and managing the operational relationship with the Fulfillment and Payment Processing vendors.
Data Analysis
Created product sales versus forecast reports to ensure inventory levels were maintained and customers’ orders were shipped according to service levels.
Created KPI reports (Sales, Returns, Shipping service levels, Fraud, etc.) for business stakeholders.
Created return reports with reason codes to qualify why products were returned, and update website product descriptions where needed.
Analyzed website traffic, internal search and shopping cart abandonment data to locate site problems and recommend solutions to increase usability and conversion rates.
Results
There were multiple product and program launches each quarter, and over six years our Sales and Operations team achieved:
Sales
13% yearly average increase in Wireless product sales revenues, and a 5% yearly average increase in the number of Wireless items sold.
Recurring Revenues
Generated an average of ~ $400,000 per month in Wireless price plan gross revenues, through combined phone and price plan purchases.
Revenue Retention
12% yearly average price plan downgrade rate from combined phone and price plan purchases, compared to a 18% price plan downgrade rate through the retail channel.
Promotions
Collaborated with Merchandising to execute online only promotions for slow moving and end-of-life products, including a refurbished phone promotion which yielded a 12% increase in sales.
Fulfillment Operations
Exceeded the shipping service level agreement of delivering orders within 3 business days across Canada 98% of the time, and consistently met the order item quality service level of 98.5%
Anti-Fraud Operations
Stopped an average of $230,000 per year in confirmed fraudulent purchases, and achieved a 6% average year-over-year decline in the number of chargebacks, resulting in a total savings of approximately $55,000.