The key to an effective marketing campaign lies in the ability to make timely adjustments based on results. When one of our customers came to us saying that it took one month to analyze campaign results, they asked us to work with them to build a new business intelligence solution for marketing automation.
Our customer, a public Fortune 1000 services company, provides residential and commercial services. With nearly a century of experience, eight well-known brands, and over 7,000 company-owned and franchise locations around the world, it was evident that their outdated IT infrastructure was hindering their marketing efforts. They sensed they were wasting millions of dollars each year on outdated advertising in newspapers, TV, radio, and even the Yellow Pages, but lacked timely data to support need changes.
Let us walk you through how we transformed their legacy IBM mainframe and SQL Server 2005 data they used each month by running manual queries for Microsoft Excel reports, into a fine-tuned marketing information hub to deliver near real-time results for their marketing campaigns.
Overview of the customer’s legacy system
Understanding the current architecture behind the flow of data with our customer, provided us with the scope of the problem. Marketing analysts for each of the brands ran manual queries against a legacy SQL Server 2005 database to deliver weekly and monthly Excel reports. These reports were rather outdated and didn’t provide the management with the comprehensive information.
In addition, the analysts lacked information from other sources which caused them to miss out on marketing opportunities. Finally, performance of the legacy SQL Server 2005 database server slowed down the results, mainly due to poorly written SQL queries. All of this combined into a marketing platform that failed to listen to customers throughout their various channels, and prevented timely responses to questions, queries, or requests for help.
Generally speaking, our customer’s business grew not because of the marketing activities but despite them and common sense. With our customer determined to change their antiquated system, DB Best’s Solution Architects came together to determine an effective pathway forward. Upon examination, our experts determined that they would have to tackle the biggest problem first: redesigning their data environment. After that, they could address new marketing channels that they never used previously and the development of a powerful bespoke business intelligence suite.
The DB Best approach
DB Best started building a completely new marketing IT infrastructure from scratch. It included the following steps:
- Database upgrade
- Leveraging cloud services
- Building an automated BI solution
Getting their house in order
First of all, we upgraded the customer’s out-of-support SQL Server 2005 databases to the 2014 version. This step was indeed critical as the national company found themselves in a very vulnerable and insecure position with their data and mission-critical applications being exposed to the potential threat. We plan a further upgrade to the latest SQL Server version.
Database upgrade is never easy, especially when the difference between the source and target versions becomes formidable. In this particular case, the upgrade process turned into a full-fledged database migration project.
At the end of the day, the customer enjoyed not only the latest built-in SQL Server features for performance and security. DB Best created a cloud-compatible database system running on the modern software which could easily be integrated with various services. Also, updated the customer’s application to work with the new database. To accomplish this task, we needed to rewrite almost all the queries and packages as they included discontinued syntax.
Adding some cloud magic
With this updated SQL Server database, we could aim for more, much more. In addition to the existing enterprise data sources, we integrated a number of cloud sources. Starting with the basics, such as Google Analytics, email campaigns, and external call centers, we ended up integrating ClickPoint, the industry-leading lead management software. We also leveraged DialogTech AI call tracking and conversation analytics service. Not stopping on reached, we added the Smarty Streets address validation API to improve the efficiency and accuracy of the marketing campaigns.
We automated the ETL processes from the customer’s in-house data sources. These processes extracted the data on a daily basis. However, we imported the data from the cloud data sources every 10 minutes. The total number of imported data reached 10 million records in a day.
An ultimate business intelligence solution for marketing automation
Then we built an automated Power BI enterprise reporting dashboard. This dashboard executes over 2000 API calls per day, refreshing various reports. The dashboards update the key metrics 4 times per day. However, it still provides users with nearly real-time analytics for many reports, critical from the marketing point of view. You can hardly compare this amazing experience with weekly Excel reports.
Here is an example of a dashboard created using Power BI to show the website conversion rates based on the various campaign sources.
In addition to that, we automated the data feed generation for our customer’s partners. We leveraged SSIS packages to pull the up-to-date data from the marketing SQL Server database. These packages control the API calls written in C#. Also, we integrated the high-performance Task Factory SSIS components and used REST API for C# scripting tasks.
Finally, we integrated the customer’s new system with Google Ad Words. The target audiences are now updated on the daily basis via API.
The new fully automated system (shown on the diagram above) allows for tracking all of their marketing expenses.
A breakthrough into a new digital era of marketing
DB Best reduced the analysis time for marketing campaigns from one month to near-real-time. With our business intelligence solution for marketing automation, the customer can clearly see which promotion produced a direct impact on business. The customer now spends their marketing dollars wisely and addresses a lot of new marketing channels that were never used before. This drives more sales with less effort because we automated most of the activities in this new system.
Approaching the customers through social networks like Facebook, Instagram, Twitter, and, of course, Google allows for running marketing campaigns with an extremely high level of personalization. This helps increase the accuracy and efficiency of marketing campaigns.
