If you queried a group of marketers about their general requirements for selecting a marketing automation solution, among the likely answers would be the following response: “System speed is critical. Our programs need to be implemented and executed in minutes or hours, not days or weeks.”
Marketers want to improve the speed of the process but often have trouble dealing with the contributing factors to improving speed because of their technical nature. Understanding the factors that affect application performance can help marketers understand the limitations and trade-offs associated with performance. Such an understanding will allow marketers to partner with information technology to make the best overall decisions.
First, you must isolate the factor that has the greatest effect on speed of execution. Marketing automation solutions rely primarily on the underlying database engine for speed. The applications present requests to the database in the form of SQL statements and procedural language. The database engine is then responsible for the heavy lifting associated with decision support systems, activities such as reading vast amounts of data, filtering, grouping, joining tables, applying functions, and expressions and sorting records.
To optimize that performance, you need to consider a number of factors before designing a solution. Give special consideration to the following areas:
· Content of the database.
· Design of the database.
· Size of the database (initially and expected growth).
· The number and types of knowledge workers accessing the database.
· The marketing automation applications that will be applied to the database.
Complex queries with expressions and functions require considerably more computer resources to process than basic select statements. You can avoid aptly named “queries from hell” if summary and aggregate fields can be populated during the database update process. Some systems create aggregate tables during the build stage to improve performance. You should also avoid correlated subqueries and queries with many-to-many joins. These can run for days or not finish at all on some systems.
If numerous “where” clauses are needed to identify a common customer segment, you should establish a derived field to simplify the process for the user as well as the database engine. This approach greatly simplifies query writing for the business users. Casual users will be able to use simple query statements instead of queries that look like programs.
Queries that draw data from multiple tables rely on the database to join the tables on indexed fields. Each join in a query adds a load to the selection process, as the database engine must select a set of records from each table before proceeding to the next. This can be avoided by applying a dimensional modeling technique, also known as a star schema, when designing the database. The dimensional tables contain repeating values (denormalized), but improvement in performance justifies data redundancy.
Database indexes can dramatically accelerate queries. You can apply different types of indexes depending on data type and the cardinality of values. A bit map index works well when there are few discrete values for a field. Unfortunately, it is impractical to put an index on every field in a large database, as it will slow the load process to a crawl and double its size.
As databases age, they tend to grow dramatically. Periodically evaluate your data. The age, and thus quantity, of transaction data on the database should be dictated by program requirements. Evaluate ways to reduce your dependency on old data. Instead of perpetually storing transactions from the very first purchase, create a “date of first purchase” field to establish tenure.
A scalable database management system allows you to sustain performance by increasing cost per unit, RAM and disk capacity at a rate that complements your increasing business needs, whether they be for additional data, processes, users or applications. A three-tier architecture is common for campaign management applications. This allows the customer database to function on a separate server while the CM application resides on its own server. The user then operates the application from a thin client. The advantage is that because the majority of processing is done on the database server, you minimize network traffic. As needs grow, the appropriate tier can be scaled up to meet them.
Campaign management applications incorporate a range of strategies to maximize speed of execution. They handle many processing-intensive steps, including selection of multiple segments, de-duping, prioritizing, excluding, sorting, grouping, splitting, merging, updating and outputting records. These processes can become time-consuming when adjustments produce reruns to “get it just right.” To avoid these problems, use representative sample database sets for developing and tuning campaigns. By reducing your load, other users will also benefit.
A popular technique applied in CM tools is the use of temporary tables. Instead of passing through the customer database repeatedly from start to finish for each campaign segment, a file of customer IDs is created and managed at each processing step. At the final step, the file is joined to the appropriate tables to select the required output. Another approach is to copy the required database fields to the local workstation from a global query. When campaigns use a limited number of fields for selection criteria, this can be a very efficient method. Keep in mind that a powerful workstation and a high-capacity network are needed to process data locally and manage the large volume of data transferred to and from a server.
CM applications often rely on open database connectivity — or ODBC — and native drivers to pass SQL server requests and communicate with database systems. Native database drivers tend to be more efficient because they do not carry as much overhead as ODBC. When you are evaluating systems, seek performance benchmarks from systems that are identical to your planned architecture. Differences in hardware platforms, operating systems, database systems or versions, and networks can affect performance results.
With a general understanding of the factors that affect system performance, you will be able to design and manage a marketing automation environment that provides acceptable performance and meets your customer relationship management objectives.