Understanding the CRM Warehouse Structure
Data marts are logical divisions within the CRM Warehouse and are comprised of subject-specific dimensional data models designed around a specific institutional process. The CRM Warehouse includes the Marketing Data Mart, Sales Data Mart, Service Data Mart, and Customer Segment Data Mart.
Image: CRM Warehouse data marts and business processes
Each data mart is associated with a business process that helps you answer the questions you need to keep your organization robust and ahead of its competition. With each data mart, PeopleSoft presents the associated business process and the fact tables that will help you answer your critical business questions.
This section discusses:
Marketing data mart
Service data mart
Sales data mart
Customer Segment data mart
Marketing Data Mart
Marketing data mart enables you to evaluate the effectiveness of marketing activities. Use it to analyze the number of leads generated, the number of click-throughs, respondent profiles, lead qualification mechanisms, and program return on investment. The Marketing data mart helps you to understand which activities work and why, identify whom you are attracting, and how to improve the next program.
Marketing data mart includes the ability to analyze the effectiveness of online marketing efforts, in addition to traditional marketing activities. The Marketing data mart delivers comprehensive analysis of Marketing activities from the target of prospects through the close of sales. The Marketing data mart enables you to evaluate the effectiveness of all marketing activities for your target audiences across multiple marketing channels. With the Marketing data mart, you can tie back to sales activities and orders to the marketing program that originated the opportunities.
Marketing Business Process
The Marketing data mart is related to PeopleSoft's Marketing business process, which is also known as Customer Lifecycle Marketing business process. This is the process followed when developing marketing programs and activities. Marketers begin the cycle by analyzing historical and predictive data to determine their marketing strategy. Marketers can then plan marketing programs and related activities by allocating budgets, assigning teams and tasks, and scheduling the timing. Next, you execute these plans by designing and taking the programs live across multiple channels, including direct, online, and telemarketing, and converting prospects into leads and sales. Finally, closed loop reporting enables marketers to assess program performance both in real-time to make any required changes to maximize revenue opportunities, as well as post-program performance to measure its overall success.
Multiple business sub-processes are related to the Marketing data mart:
Marketing data mart reports help marketers with the Marketing Resource Management business sub-process to better manage marketing operations, such tracking budgets and expenses. In addition, all costs associated with a roll-up program are consolidated for a single view of how much the roll-up program cost, broken out by individual activities.
Marketing data mart reports provide insight into Cross-Sell Opportunity Identification business sub-process; the data can help you determine which products tend to be correlated so you can determine potential cross-sell opportunities.
You can also measure Program Performance business sub-process by examining the reach and response of your programs and activities across all channels: direct, telemarketing, and online. This can help you to determine channel effectiveness and identify successful programs that can be cloned and re-executed in the future.
Marketing Data Mart Delivered Fact and Dimension Tables
This section discusses the delivered fact and dimension tables for the Marketing Data Mart.
Marketing Data Mart Fact Tables
Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers. The following table describes the delivered Marketing Data Mart fact tables.
Fact Name |
Fact Record Name |
Description |
Helps Answer |
---|---|---|---|
Marketing Activity |
F_MKT_ACTIVITY |
Maintains cost and time metrics for measuring the effectiveness and efficiency of marketing campaigns, programs, and activities (events). The fact data is a periodic snapshot captured at the marketing activity level. |
The return on investment of a marketing activity. The amount of money budgeted for a marketing activity. Total cost of a marketing program. Revenue generated from a marketing program. Estimated revenue to be generated by a marketing program. Number of leads generated by a marketing activity. Number of customers generated by a marketing activity. |
Online Marketing (OLM) All Response |
F_OC_ALLRSP |
Maintains aggregate email and web page response per action per dialog. The All Response fact table is an aggregated fact table. |
Number of responses received from emails and web pages. Number of audiences in the online marketing list. Number of audiences contacted multiple times. Number of people who responded to an email. |
OLM Bulk Email |
F_OC_BMAIL |
Maintains bulk email information. This base fact is the source to populate OLM aggregate facts. |
— |
OLM Daily Email |
F_OC_EMLDAY |
Maintains aggregate email response metrics per day, per action, per dialog. The Daily Email fact is an aggregated fact table. |
Number of daily emails sent that generated a response. Number of emails sent that were not duplicates or 'Do Not Email' contacts. Number of emails sent that bounced back. |
OLM Document Answer |
F_OC_DOCANS |
Maintains aggregate answer choice metrics for each web page document, per action, per dialog. |
Number of responses to a documented question. Answer responses for a documented question that appeared online. Number of people responding to an online question. |
OLM Document Choice |
F_OC_DOCCHO |
Maintains document choice information. This base fact is the source to populate the OLM aggregate fact F_OC_DOCANS. |
— |
OLM Dynamic Content |
F_OC_DYNCNT |
Maintains aggregate dynamic content information. This is a subset of the F_OC_EMLPFM fact. |
— |
OLM Dynamic Content Web Link |
F_OC_DYNLNK |
Maintains aggregate dynamic content web link information. This is a subset of the F_OC_EMLPFM fact. |
— |
OLM Dynamic Content Section |
F_OC_DYNSEC |
Maintains aggregate dynamic content section information. This is a subset of the F_OC_EMLPFM fact. |
— |
OLM Email Bounce |
F_OC_EMLBNC |
Maintains bounced email information. This base fact is the source to populate OLM aggregate facts. |
— |
OLM Email Impression |
F_OC_EMLIMP |
Maintains email impression information. This base fact is the source to populate OLM aggregate facts. |
— |
OLM Email Performance |
F_OC_EMLPFM |
Maintains aggregate email dynamic content appearance metrics per action, per dialog. This fact table is an aggregated fact table. |
Number of times a document link was offered. Number of contacts who responded on a particular web page link. Number of times documents were sent to known audiences. Number of times documents were sent to unidentified audiences. Number of times document links were clicked by an unknown respondent. |
OLM Email Repeat Response |
F_OC_EMLREP |
Maintains aggregate repeat email response metrics per action, per dialog. The Repeat Email fact is an aggregated fact table. |
Number of repeating emails sent that generated responses. Number of audiences in a specific online marketing list. Number of audiences contacted multiple times. Number of emails offered that were not duplicates or 'Do Not Email'. Number of emails bounced back. Number of people responding to an email. |
OLM Email Response |
F_OC_EMLRSP |
Maintains aggregate email response metrics per action (marketing activity), per dialog (marketing program). The Email Response fact is an aggregated fact table. |
Number of emails sent for each dialog action. Number of audiences in a specific online marketing list. Number of audiences contacted multiple times. Number of times a specified link was clicked. Number of people responding to an email. |
OLM Profile Answer |
F_OC_PROANS |
Maintains profile choice answer information. This base fact is the source to populate the OLM aggregate fact F_OC_PROCHO. |
— |
OLM Profile Answer Choice |
F_OC_PROCHO |
Maintains aggregate online marketing profile choice metrics for each web page profile question, per action, per dialog. |
Number of contacts who responded to a specific web page profile question. Number of visitors for a web page. Number of submitters for a web page. Number of audiences contacted through email. Number of clicks for a specified link by a respondent. Number of responses for a web profile choice. |
OLM Single Email |
F_OC_SMAIL |
Maintains single email information. This base fact is the source to populate OLM aggregate facts. |
— |
OLM Web Click |
F_OC_WEBCLK |
Maintains web click information. This base fact is the source to populate OLM aggregate facts. |
— |
OLM Daily Web Response |
F_OC_WEBDAY |
Maintains aggregate web page responses per day, per action, per dialog. This fact table is an aggregated fact table. |
Number of daily web page submits and visits. Number of identified web page visits. Number of identified web page submits. |
OLM Web Individual Response |
F_OC_WEBIND |
Maintains web individual response information. This base fact is the source to populate OLM aggregate facts. |
— |
OLM Web Repeat |
F_OC_WEBREP |
Maintains aggregate repeat web page response metrics per action, per dialog. This fact table is an aggregated fact table. |
— |
OLM Web Response |
F_OC_WEBRSP |
Maintains aggregate web page response metrics per action, per dialog. This fact table is an aggregated fact table. |
Responses for an online marketing web page. Number of visits to the web page can be identified. Number of identified web page visits. Number of unidentified web page visits. Number of identified web page submits. Number of web page submitters. |
OLM Web Submit |
F_OC_WEBSUB |
Maintains web submit information. This base fact is the source to populate OLM aggregate facts. |
— |
OLM Web Visit |
F_OC_WEBVST |
Maintains web visit information. This base fact is the source to populate OLM aggregate facts. |
— |
Product Correlation |
F_PROD_CORR |
Maintains product and correlated product relationships within sales orders (market basket metrics) and provides insight into cross-selling opportunities. The fact data is derived from the sales order capture transaction at each product combination level. |
Based on history, the likelihood that a customer who purchases one product will purchase another specific product. Products typically sold along with a specific product. |
The following table describes the delivered Marketing Data Mart dimension tables:
Marketing Data Mart Dimension Tables
Dimension Name |
Dimension Record Name |
Description |
---|---|---|
Marketing Audience |
D_AUDIENCE |
Maintains marketing audience information. |
Marketing Status |
D_MKT_STATUS |
Stores campaign life cycle stage information. Campaign and activity statuses are identical objects, although they are used separately. The PeopleSoft CRM application delivers a set of predefined marketing statuses; customers may also define additional values. |
OLM Marketing Action |
D_OC_ACTION |
Maintains online marketing action and action type information. Online marketing actions include actions such as sending broadcast emails, displaying a web page, sending follow-up emails, and so on. Actions roll up to Action Types. A dialog consists of one or more actions. |
OLM Basic Individual |
D_OC_BASIND |
Maintains individual information. The data is basic CDM data for individuals from the E-CRM BORM source. Some of the attributes are associated with the CRM Customer Person dimension. |
OLM Basic Organization |
D_OC_BASORG |
Maintains organization information. The data is basic CDM Data for organizations from the E-CRM BORM source. Some of the attributes are associated with the CRM Customer Organization dimension. |
OLM Dialog |
D_OC_DIALOG |
Defines a specific marketing program for the execution of email or web channels. A dialog consists of one or more marketing actions. |
OLM Doc Choice |
D_OC_DOCCHO |
Maintains document choice, element, document and element type information. The dimension has the following hierarchies: Document Choices, Document Elements, Document; Document Choices, Document Elements, Element Type. |
OLM Document |
D_OC_DOCMNT |
Maintains email messages and web pages that are created for dialogs. |
OLM Audience List |
D_OC_LIST |
Maintains audience list and list type information. Lists of audiences are used in all dialogs to specify for whom the dialog is targeted. The audience list is sourced from the CRM Customer record. Audience Lists roll up to List Types. |
OLM Profile Choice |
D_OC_PROCHO |
Maintains profile choice, profile element, profile, and profile type information about the target audience. The dimension has the following hierarchy: Profile Choice, Profile Element, Profile, Profile Type. |
OLM Repeat Action |
D_OC_REPACT |
Maintains online marketing repeat action information. Online marketing actions include actions such as sending broadcast emails, displaying a web page, sending follow-up emails, and so on. This dimension also contains the number of times that one action has been repeated. |
OLM VC Format |
D_OC_VCFMT |
Maintains information about the format of a web link or email, such as HTML or TEXT. |
OLM VC Object |
D_OC_VCOBJ |
Maintains virtual content, virtual content type, and virtual content link type information displayed in a web page or in an email. The dimension has the following hierarchies: VC Object, VC Link Type; VC Object, VC Type. |
OLM VC Parameter |
D_OC_VCPARM |
Maintains parameter and parameter set information. Parameter sets are groups of content sections (for example, VCType main set); parameters are the content sections embedded in Dynamic Contents (for example, Biking, Surfing, Kayaking, and Skiing). |
Sales Data Mart
The Sales data mart helps you analyze sales processes and evaluate the effectiveness of sales-related activities. Use it to examine pipeline status, forecasting accuracy, discount analysis, sales cycle time, and fallout analysis. Using historical measures of your sales processes helps you to provide opportunities for reducing cycle times within your sales activities.
Sales Business Process
The Sales data mart is related to the Sales business process, which is also called Collaborative Multichannel Selling business process. The Sales business fulfills an organization's requirements for discovering, managing, negotiating, and closing opportunities. This business process includes assigning leads and qualifying opportunities; proposing solutions and providing quotes; negotiating and closing the sales opportunities resulting in contracts, orders, or both; and managing pipelines, forecasts, and territories.
Sales managers monitor key performance metrics to gauge the overall effectiveness and efficiency of the sales force and to allocate resources across the territory structure to ensure adequate distribution of leads, opportunities and account coverage. Quote and proposal data provides additional insight into the details of future revenue streams and is used by sales managers to manage opportunity progression towards closure. Sales managers are responsible for monitoring individual and team performance and manage pipeline coverage and forecast data against quota levels at the individual sales representative, product, and summary levels.
There are two business sub-process related to the Sales data mart: Qualify to Close and Order to Cash. The Sales data mart addresses metrics related to both business sub-processes. The primary business sub-process that is most relevant to the sales manager role is Qualify to Close, which deals with the distribution of qualified sales leads, manages the conversion of leads into viable opportunities, and provides coaching and mentoring to the sales resources to maximize revenue generation for the organization.
A secondary business sub-process relevant for this role is Order to Cash, which is more directly related to the order capture and order management process. Sales managers that also manage this aspect of the overall sales process must monitor the ratio of opportunities to sales orders and the fulfillment of those orders through to collection and recognition of revenue. Order data provides additional information on source, product mix, discounting, and profitability metrics that provide insight into the overall effectiveness of the sales force. Sales managers use this information to adjust their strategy to maximize profitability and ensure that territories, and the resources that are allocated to those territories, are aligned properly.
Sales Data Mart Delivered Fact and Dimension Tables
This section discusses the delivered fact and dimension tables for the Sales Data Mart.
Sales Data Mart Fact Tables
Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers. The following table describes the delivered Sales Data Mart fact tables.
Fact Name |
Fact Record Name |
Description |
Helps Answer |
---|---|---|---|
Sales Lead |
F_LEAD |
Maintains accumulated snapshot sales lead duration and lead effectiveness data. It contains metrics at the Lead ID level. These metrics are used for lead qualification and lead generation analysis and can be rolled up by dimensions such as Customer, Program, Sales Territory, and Marketing Channel. This table is an accumulated snapshot; each time a change is made to a lead, the existing row describing that lead is changed - but the Lead Date remains the same. |
Historical lead conversion rate. Number of opportunities generated from a lead. Number of leads generated. Number of customers generated from a sales lead. Number of orders generated from a lead, Average number of quotes from a sales lead. |
Sales Opportunity |
F_OPPORTUNITY |
Contains snapshot information for expected, forecasted, and closed revenue for each sales opportunity from open to close and provides pipeline analysis for measuring the effectiveness and efficiency of the sales force. This fact data maintains the basis of measurement on the sales opportunity pipeline and fallout analysis. The fact data is a snapshot captured at the Opportunity and Sales Rep grain level. |
Revenue forecasted for an opportunity. Order amount for an opportunity. Number of days estimated for an opportunity to generate an order. Total budget amount for all of the opportunities generated in a specific quarter. Revenue allocation percentage for a sales person. Qualification goal of an opportunity based on its selected qualification. Qualification score of an opportunity compared to the opportunity qualification goal, answering whether the opportunity is qualified. Difference between actual opportunity sales days and estimated opportunity sales days. |
Product Opportunity |
F_OPPY_PRODUCT |
Contains snapshot information for product price and discount for each sales opportunity, which enables pipeline analysis by product or item. The fact data is a snapshot captured at the Opportunity and Product grain level. |
Length of time an opportunity takes to close for a particular product. Difference between the estimated and actual revenue recognition for a particular product. Average confidence of the opportunity to generate an order. Average score of all opportunities that the sales team has won for a given year. Qualification goal of an opportunity based on its selected qualification. The weighted product item price in the opportunity. The confidence percent for an opportunity. Whether a product listed in the opportunity is the primary product. |
Order Association |
F_ORD_ASSOC |
Maintains relationship data between an order capture and its associated objects, such as sales lead, opportunity, service order, RMA, and so on. This fact table describes the association between an order and its source and the data is a transaction at each order association. That is, each order can be tied to each of the preceding business processes to analyze the effectiveness of strategic marketing and selling activities. |
Number of orders generated from a lead. Number of orders generated from an opportunity. " What is the number orders originated through a particular lead" Number of transactions associated with an order. Number of service orders associated with a particular case. Number of RMAs associated with a particular case. |
Order Capture |
F_ORD_CAPTURE |
Captures sales order line transaction metrics, enabling you to generate reports that tell you whether, for example, a sale is generated by a sales rep or partner, through which marketing activity the event occurred, or for which product and segment, and the respective business unit. The fact data is an order line for each transaction. |
Number of orders and corresponding revenue averaged per month, for a particular sales rep. List price for a product. Average quantity ordered for a product. Price of the periodic recurring charge for a product. Frequency of the recurring charge for that instance of the product sale. Actual price used for an order. |
Product Quote |
F_PROD_QUOTE |
Maintains product sales quote price and discount metrics at the product, sales quote grain, which are used in sales quote analysis. These metrics are analyzed by dimensions such as Product and Time. Granularity for this fact table is per Product per Quote. |
Average discount and selling price for a product. Average quote price for a product. Average discount for a product. List price for a product. Quantity of product used for a set of quotes for that product. |
Sales Quota |
F_SALES_QUOTA |
Maintains metrics used to analyze forecast, sales representative sales quotas and actual revenue. These metrics are analyzed by dimensions such as Sales Territory, Forecast ID, and Time Frame. The fact granularity is per sales representative, per forecast, per timeframe. |
Average quota amount for a sales person, Total forecasted revenue amount in a sales timeframe. Actual revenue for a sales person in a sales timeframe. |
Sales Quote |
F_SALES_QUOTE |
Contains sales quote and discount data by sales representative, by sales opportunity (from open to close) and contains metrics such as actual price and actual discount, which are used in sales quote analysis. These metrics are analyzed by dimensions such as Sales Rep and Time. Granularity is per sales representative, per Quote. |
Average discount and selling price for a sales representative. Discount amount offered to a customer by a sales representative. Number of quotes for a particular product opportunity. |
The following table describes the delivered Sales Data Mart dimension tables:
Sales Data Mart Dimension Tables
Dimension Name |
Dimension Record Name |
Description |
---|---|---|
Competitor |
D_COMPETITOR |
Maintains competitor information. A competitor is a company that you are competing against for a sales opportunity. |
Confidence |
D_CONFIDENCE |
Maintains information about sales confidence. The data is EPM defined data through Dimension Mapper. |
Fallout Reason |
D_FALLOUT_RSN |
Maintains fallout reason information. It is the principal reason for losing an opportunity to a competitor. |
Forecasts |
D_FORECAST |
Stores information about forecasts, which are forward looking estimations of income or performance. |
Sales Lead Rating |
D_LEAD_RATING |
Stores information about the sales lead rating. The rating describes the quality of the lead. |
Opportunity Flag |
D_OPPY_FLAG |
Maintains information for the following opportunity flags: forecasted, open, closed, qualified, won, lost. |
Order Status |
D_ORD_STATUS |
Maintains the order status of the order capture. |
Partner Program |
D_PRTR_PGM |
Maintains partner program information. This dimension has the following hierarchy: Partner Program, Partner Subtype, Partner Type Partner Program, Partner Level. |
Qualification |
D_QUALIFICATION |
Maintains qualification information for sales leads and opportunities. |
Sales Quote Dimension |
D_QUOTE |
Maintains sales quote information. |
Sales Representative |
D_SALES_REP |
Maintains information about sales representatives. Some agent attributes are associated with HCM person data. |
Sales Stage |
D_SALES_STAGE |
Maintains sales stage (also known as Sales Cycle) information that indicates the stage of the opportunity during the sales cycle. |
Sales Status |
D_SALES_STATUS |
Maintains sales lead or opportunity status information. |
Order Ship Type |
D_SHIP_TO_TYPE |
Maintains order ship to type information for orders. |
SIC Code |
D_SIC |
Maintains Standard Industrial Classification (SIC) code information. |
Timefame |
D_TIMEFRAME |
Maintains information about timeframes. Timeframes are dynamic time periods used for calculation purposes. |
Service Data Mart
The Service data mart gives organizations the ability to analyze the effectiveness of their contact center by understanding call and case load trends, agent utilization, cost to support products and customers, up-sell and cross-sell success rates, product quality, and performance of service-level agreements. With this knowledge, organizations can lower the cost to serve, as well as increase service revenue, by maximizing each customer contact to provide the most effective service and generate service and sales revenue through targeted offers.
Service data mart helps you analyze all contact center activities, such as support activities, calls, cases, and resolutions, and test their effectiveness through various communication channels. Analysis of contact center communication channels in the context of CRM transactions provides insight into your organization's ability to service your customer base. Use it to better understand product quality, call and case load trends, agent utilization, the cost to support products and customers, and performance to service level agreements.
Support Business Process
Service data mart provides insight into PeopleSoft's Support business process, which is also known as Integrated Service Management business process. This process follows the life span of a service incident, including revenue generation by way of offers. This business process also includes the process of reporting and resolving product defects and the analysis of contact center performance.
Service data mart provides insight into various stages of the Support business process, enabling an organization to improve business process efficiency through this knowledge.
Access, Resolve, and Recommend are the business sub-processes related to the Service data mart. The Access sub-process involves capture, entitle, and assign functions. The Resolve sub-process involves the search and resolve functions. The Recommend sub-process enables management to analyze offer success and loss rates and understand which offers are most effective.
Service Data Mart Delivered Fact and Dimension Tables
This section discusses the delivered fact and dimension tables for the Service Data Mart.
Service Data Mart Data Mart Fact Tables
Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers. The following table describes the delivered Service Data Mart fact tables.
Fact Name |
Fact Record Name |
Description |
Helps Answer |
---|---|---|---|
Case |
F_CASE |
Maintains accumulated snapshot cost and time metrics for measuring the effectiveness and efficiency of support cases. |
How long a case has been open. Number of cases with an open status. Number of opened cases. Number of cases with a closed status. Number of reopened cases. Number of cases closed on the same day. |
Case Association |
F_CASE_ASSOC |
Maintains relationship data between a contact center case and its associated objects, such as sales lead, sales order, service order, defect, and so on. |
Number of times a case is associated with other business processes, such as a case generating a sales lead or an order. Number of cases generated form a sales lead. Number of cases generated for an order. Number of cases generated with an RMA. Number of cases followed with a customer survey. Number of cases related to a product defect. |
Case History |
F_CASE_HIST |
Maintains change history for each case life cycle from open to close. The fact data maintains the basis of the measurement on case reassignment and case escalation. The fact is captured whenever a case is updated. |
Length of time to solve cases for a specific product. |
Case Resolution |
F_CASE_RESOLVE |
Maintains the transaction data to analyze case resolution and agent to solution performance. The fact grain is for each resolution, for each case. |
Number of cases resolved using solutions from the solution library. Average number of resolutions for a support case. Number of cases closed without resolution. |
Case Survey |
F_CASE_SURVEY |
Maintains the survey rating data for customer support cases. |
Number of customer surveys. Weight value for a survey script. Score for a survey script. Customer satisfaction with the results of their cases. |
Case Trend |
F_CASE_TR |
Contains daily (periodic) snapshots of case status data from the F_CASE table. This table enables you to analyze cases over a period of time using measures such as new cases, closed cases, pending cases, or average time to close. This table is sourced from F_CASE_HIST. |
Number of open, closed, or pending cases with respect to the snapshot date. Age of a case with respect to the snapshot date. Average number of days taken to close support cases. Rates of first call resolution. |
Call Detail |
F_CC_CALL |
Maintains aggregate contact center call metrics. The fact is sourced from third-party CTI suppliers. It can be integrated into CRM interactions through the Connection ID. |
Average wait time for customer calls. Average call volume for a specific month. Average duration for customer calls. Number of outbound or inbound calls for a specific day. Average number of agents handling a customer call. Total call time in seconds. Total call conference time in seconds. |
Chat |
F_CC_CHAT |
Maintains metrics to determine agent load, volume of chat activity. |
Number of chats for a specific day. Number of chats related to cases. Number of chats related to an order. Chat length. |
|
F_CC_EMAIL |
Maintains accumulated snapshot email volume, category assignment activity, response time effectiveness, and template accuracy, plus and utilization metrics. |
Total number of inbound emails to support customers. Number of SPAM emails to support customers. Number of outbound emails. Number of closed emails when supporting customers. Number of times an email was assigned. |
Email Assignment |
F_EMAIL_ASSIGN |
Maintains assignment duration metrics for worklists and agents. |
Average agent assignment for handling emails from customer who requested support. Average time duration before an email is handled by an agent. |
Entitlement |
F_ENTITLEMENT |
Maintains periodic snapshot entitlement metrics for customers and products at the entitlement grain. |
Number of entitlements for a specific customer and product. Which agreements and entitlements exist for my customers. Number of days does a particular service agreement expire. Number of times a particular service agreement renewed. |
Customer Interaction |
F_INTERACT |
Maintains customer interaction metrics. |
Total number of customer interactions associated with a sales lead. Total number of customer interactions associated with a support case. Total number of customer interactions associated with a service order. Total number of customer interactions associated with an order capture. Number of calls/emails/chats received for a particular day. |
Solution |
F_SOLUTION |
Maintains accumulated snapshot usage and solved count for each solution in the contact center. The fact data also reflects the solutions submitted by agents. |
Number of times a particular resolution was used to solve a problem. Number of cases were solved with a particular solution. Number of times the solution actually solve the problem. |
The following table describes the delivered Service Data Mart dimension tables:
Service Data Mart Dimension Tables
Dimension Name |
Dimension Record Name |
Description |
---|---|---|
Contact Center Agent |
D_AGENT |
Defines the contact center agent. Some agent attributes are associated with HCM person data. The dimension has the following hierarchy: Agent, Provider Group, Provider Group Type. |
Customer Support Agreement |
D_AGREEMENT |
Maintains customer support agreement information. The dimension has the following hierarchy: Agreement Line, Renewal Num, Agreement. |
Agreement Line Status |
D_AGRMNT_STAT |
Maintains customer support agreement and agreement line status information. The source, PSXLATIEM, is accessed twice to complete the dimension. |
Call Type |
D_CALL_TYPE |
Maintains call type information. The call type is defined in the EPM translate for contact center third-party supplier use. |
Support Case |
D_CASE |
Maintains customer support case information. The dimension has the following hierarchies: Case, Case Type, Case, Case Source, Case, Case Detail, Case Detail Type, Case Category, Case, Case Impact, Case, Reopen Reason, Case, Closed Reason, Case, Resolve, Case, Restore. |
Case Priority |
D_CASE_PRIORITY |
Maintains support case priority information. The dimension has the following hierarchy: Priority, Priority Category. |
Case Severity |
D_CASE_SEVERITY |
Maintains support case severity information. |
Case Status |
D_CASE_STATUS |
Maintains support case status information. The dimension has the following hierarchy: Case Status, Case Status Category. |
Email Mailbox |
D_EML_BOX |
Maintains information about mailboxes associated with emails. The dimension has the following hierarchy: Mailbox, Mailbox Type. |
Email Template Category |
D_EML_CATEGORY |
Defines the template categories assigned to an email. |
Employee |
D_EMPLOYEE |
Tracks employee data for agents and general employees. Attributes for an employee can be employee job code, department, or location. |
Entitlement |
D_ENTITLEMENT |
Maintains customer support service entitlement information. The dimension has the following hierarchy: Entitlement, Entitlement Plan. |
Install Product Component |
D_INST_PROD |
Used by the Installed Product component to determine the type of product that is being registered for the Customer, Site, Department, or Employee. |
Contact Center Interaction |
D_INTERACTION |
Defines contact center interaction transactions. |
Interact Method |
D_INTERACT_MTHD |
Defines the method of an interaction. |
Interaction Status |
D_INTERACT_STAT |
Defines the status (completed, in progress) and detail type (email, chat, phone) of an interaction. The hierarchy is: Status, Detail Type. |
Case Links |
D_LINKS |
Stores information about the case links an agent can associate with a case; for example, service order and sales order links. |
Provider Group |
D_PROVIDER_GRP |
Organizes customer support agents into provider groups. Cases and service orders are assigned first to a provider group, then to an agent in the provider group. Agents roll up to Provider Groups. |
Resolution Source |
D_RESOLVE_SRC |
Maintains customer support case resolution source information. |
Case Solution |
D_SOLUTION |
Maintains customer support case solution information. The dimension has the following hierarchies: 1. Solution, Solution Library 2. Solution, Solution Type. |
Solution Status |
D_SOLUTION_STAT |
Maintains support solution status and resolution status information. The source for both fields is PSXLATITEM. There are two ETL jobs to populate this dimension. |
Survey Instance |
D_SURVEY_INST |
Maintains customer support survey instance information. Survey instances are associated with a support cases. |
Survey Rating |
D_SURVEY_RATING |
Maintains customer support survey rating information. |
Support Survey Script |
D_SURVEY_SCRIPT |
Maintains customer support survey script information. The dimension has the following hierarchies: 1. Script, Script Category 2. Script, Script Type. |
Email Workgroup |
D_WORK_GRP |
Maintains information relating to email work groups. |
Customer Segment Data Mart
Customer Segment data mart provides the analysis of customer behavior as a result of the activities of the CRM business processes. One of an organization's goals is to manage a strategic segment of customers that are identified as having the potential for high value. There are many activities performed throughout the CRM business processes that influence the behavior of these desired segments.
Marketing Business Process
Customer Segment data mart is part of PeopleSoft's Marketing business process, which is also known as Customer Lifecycle Marketing business process. A marketing organization can segment its customer and prospect base using multiple hierarchical structures. In the List Management business sub-process, PeopleSoft provides functionality through the Audience Builder component to define, manage and update segments and hierarchies. PeopleSoft Marketing uses segment groups to define these hierarchies within a set of segments. Within the segment groups, you can segment primary audiences into smaller groups, enabling effective analysis of various campaign strategies. Audience membership can be static or dynamic in nature. You can re-execute criteria used to define audiences to establish an updated snapshot of each segment. This closed loop reporting enables marketers to assess the performance of programs targeting their strategic customer segments.
The List Management business sub-process is related to the Customer Segment data mart. This business sub-process examines the measures by audiences, segments, or both, to help determine "hot lists." This process also helps to identify list quantity issues that marketers can potentially address. Customer segment analysis using customer acquisition and customer segment reports identify the most effective campaigns used to generate leads and sales. You can reuse the attributes of these campaigns to create similar marketing programs that are designed to illicit similar customer behavior in the strategic segment that you have selected. Using the Segment Migration fact, you can perform segment response analysis by tracking the effectiveness of CRM activities in influencing your customer base over time.
Customer Segment Data Mart Delivered Fact and Dimension Tables
This section discusses the delivered fact and dimension tables for the Customer Segment Data Mart.
Customer Segment Data Mart Data Mart Fact Tables
Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers. The following table describes the delivered Customer Segment Data Mart fact tables.
Fact Name |
Fact Record Name |
Description |
Helps Answer |
---|---|---|---|
Customer Acquisition |
F_CUST_ACQUIRE |
Maintains measures of new customers by date of first sale. The grain of the fact table is captured at the customer level. |
Total amount of a customer's first order. Number of sales interactions, such as meetings or phone calls, that occurred before a customer made the first purchase. Number of direct mailings before a customer made the first purchase. Number of inbound calls made before a customer made the first purchase. Number of interactions that occurred before a customer made a first purchase. |
Customer Count |
F_CUST_COUNT |
Maintains periodic snapshot counts of customers. |
|
Customer Segment |
F_CUST_SEGMENT |
Maintains a periodic snapshot of customer segment composite metrics. The grain of the fact table is the customer level. |
Number of interactions, by channel, servicing a specific strategic segment. Average order amount for a specific segment's customers. Number of direct mail interactions for a specific segment's customers. Number of telemarketing interactions for a specific segment's customers. Number of cases requested from a specific segment's customers. Number of cases resolved on the first call from a specific segment's customers. Number of sales orders are generated for a specific segment's customers. |
Customer Segment Migrate |
F_SEG_MIGRATE |
Maintains periodic snapshot customer segment membership information. Each organization tracks strategic segments of customers determined by predefined criteria that are tracked by sales marketing organizations. You can validate activities created to influence a desired migration of customers from one segment to another. The grain of the fact is the customer level. |
Number of customers moving from a low-value segment to a high-value segment. Number of customers in a specific segment migrating to a different segment. |
Segment Count |
F_SEGMENT_COUNT |
Maintains periodic snapshot customer counts for customer segments. |
Number of new customers in a specific segment. Number of existing customers in a specific segment. Number of customers in a specific segment that have not placed an order in the past 18 months. |
The following table describes the delivered Customer Segment Data Mart dimension tables:
Customer Segment Data Mart Dimension Tables
Dimension Name |
Dimension Record Name |
Description |
---|---|---|
Role Type |
D_ROLE_TYPE |
Maintains information about the role played by a contact, partner, company or an individual. Data is sourced from E-CRM BORM. |