Bookshelf: Metadata Requirements for Siebel Consumer Sector Analytics Applications

This section covers data setup that needs to be done properly in the Siebel transactional database for it to be displayed accurately in Siebel Analytics.

Siebel eConsumer Goods Sales Analytics extends the base Sales Analytics application to include Trade Funds Management analytics, Trade Promotion Evaluation analytics, Sales Performance analytics, and Retail Audit analytics.

All Consumer Goods specific metadata has been added to a single subject area. In the Server Administration Tool, this metadata is tagged with a red apple icon. The following section covers each fundamental area of Consumer Goods analytics and provides tips for data entry for effective analytics.

The Consumer Goods dashboards and pages available to the end user are described in Table 30.

Table 30. Consumer Goods Dashboards and Page Tabs
Dashboard	Page	Function
Retail Audit	Last Audit	Shows the aggregated results of the last audit, defined the last time a product was audited at a specific account and merchandising location. The aggregated metrics can be drilled into to get a list of accounts or products to target future efforts.
	Trends	Displays key metrics captured in a retail audit over time across accounts or product categories.
	Position	Combines account and product performance with the individual representative responsible.
Promotion	Plan Year To Date	Displays both individual promotional performance with cumulative trends to provide overall perspective on meeting promotional targets.
	Key Accounts	Shows post promotion evaluation from a key account perspective across several levels of detail including plan, promotion, or promoted product detail.
	Corporate	Shows post promotion evaluation from a brand managers perspective, by evaluating corporate promotions.
Funds	Summary	Displays the key analyses for a fund manager, including Remaining Amounts which provides a status of all funds relative to all other funds.
Funds	Accounts	Highlights status of funds and funds spent at a specific account and is targeted to assist key account managers in determining how to fund future promotions.
Sales Performance	Sales Volume Planning	Details the key metrics used in sales volume planning, including incremental, target, shipment and consumption volumes, over time and in comparison to one another. Baseline and estimated incremental volumes are stacked and compared to targets to assist in identifying future progress against targets. This analysis uses a global filter to assist end users in getting to the level of details they want in one selection.
	Hierarchy	Similar in content to Sales Volume Planning, however, this analysis is organized to promote exploration of data, allowing end users to freely drill up or down the account, time, or category product hierarchies. This page should be used to help sales managers identify where sales exceeded expectations.
	Trends	Depicts sales trends across accounts, channels, and categories as well as compares account performance in order to rank them.
	Growth	Displays key sales metrics versus the year ago and charts the rate of growth.
VP Sales	Business Overview	This page focuses on answering key business questions of a sales executive including where are my sales? How effective are my promotions by channel? Which account plans are top and bottom performers? How is my promoted volumes and funds spend trending as compared to last year? what are my top 5 accounts in each category?
VP Sales	Product Overview	This page focuses on answering key product questions of a sales executive including what percentage of total sales is in each category? What products are selling where by whom?
Key Account Manager	Account	This page focuses on answering key business questions of a key account manager including how I am performing versus my account plan? What is my promotional forecast accuracy? What funds are available to plan additional retail activity?
Key Account Manager	Category	This page focuses on answering key product questions of a key account manager including which category is best promoted at my accounts? How are my store conditions trending? Are out of stocks preventing me from reaching my targets?

The data requirements for the Consumer Goods-specific portion of the data model are detailed in Table 31. This includes the fund, promotion, performance and retail audit schema.

Table 31. Data Requirements for Consumer Goods Schema
Page	Function
Funds	Trade Fund Management analytics incorporates the latest Trade fund functionality including aggregation of approved adjustments, transfers, deal allocations, and payments at all statuses.
Promotion	In the transactional database, fund allocations and product movement (incremental volumes) can be assigned to a promotion at the promotion or the promoted product level. The ETL transfers this information at the promoted product level only. If you allocate funds at the promotion level and assign product detail such as shipment quantities at the promoted product level, the fund allocation data will need to be pushed to the promoted product level to be accurately stored in the Siebel Data Warehouse.
Performance	Sales Performance analytics is used primarily in conjunction with the category product hierarchy as defined by Siebel Sales Volume Planning. To create the category-product hierarchy, the SVP adopt flag must be selected in order to aggregate product data up the category product hierarchy. This flag can be found by navigating to Catelog Administration > SVP Category Details View > Products applet. The data warehouse and all prebuilt analyses are built from the end date of a period. To make sure data is accurate, the periods in the Siebel transactional database tables must be of a single duration. If they are different, inaccurate data could be stored in the data warehouse. For example, if a week and a month end on the same date, the actual shipped quantities will be combined during the building of the data warehouse.
Retail Audit	The Last Audit Flag is set during the ETL process. The flag is set to Yes for the most recent record for a product at an account and merchandising location. All other audit records for that combination of product, account, and merchandising location are set to No. The observation date is used to sort the retail audit records to determine which is the last audit. The observation date is populated when the status of a retail audit activity is changed to Done. The field observation date does not show up in the Siebel transactional database user interface and does not have to be the same as the activity date.