Design And Implementation Of Advertisement Performance Analysis Data Warehousing System

As the time goes on, Advertising becomes a critical method to increase sale. Currently, most Ad data warehouse systems focused in online advertising and user behavior targeting field; they focus on user browsed web page, the published Ad tracking and clicking records and the billing system.As they can not get sales data, they could not provide the accuracy sales report before/after advertising. Marketing team need a decision support system to provide sales, ad and marketing data together, and help the best ad suite for products.Advertisement Performance Analysis system aims to track the Ad publishing and the sales uplift, and provide accurate report to business team to help them make further Ad planing. APA system populates WholeSales data, Retails data, Ad data and marketing data from several upstream and cleanes and transformes them, then created APA cube. With this cube, the system analyzes the sales uplift from Ad area, Ad time, marketing channel, account and product suites perspective. With it, business team could make new Advertising plan.Additionally, APA system focuses on the specified fields; it has more requirements about data analysis, data cleaning, data evaluation, refreshing schedule and reports.APA project lasted 9 months, and the whole project had 4 phases-requirement analysis/confirmation, system implementation and testing, system maintenance after go live. The author worked at each of them.This paper describes the key modules requirement, the design and implementation, system testing and maintenance plan. For the data warehouse project process is a long-term consistent developing one, this paper discusses some preparation tasks and futher prospects as well.APA system was based on C/S structure, developed based on Microsoft.Net and the Business Intelligence Solution Suites-SQL Server 2000 and SQL Server 2005. Considering the hardware performance and the compability of existing systems on the same server, SQL Server2005 Data Engine was used as backend database service; SQL Server 2005 Integration Service were used to implement the ETL process; SQL Server 2005 Analysis Service & ProClarity 6.0 was used in presentation layer to generate reports.What is more, ProClarity analysis tree report could represent the sales status at every level and makes the report more easy to read.