Search Results ams_act_metric_hst




Overview
Explore in AI Chat

Overview

The AMS_ACT_METRIC_HST table is a core data object within the Oracle E-Business Suite Marketing (AMS) module, specifically for versions 12.1.1 and 12.2.2. It functions as a historical repository for metric values tracked against various marketing entities. Its primary role is to capture and preserve time-series data for performance indicators, such as Return on Investment (ROI), associated with marketing campaigns, events, or other activities. This historical record is essential for longitudinal analysis, trend reporting, and performance auditing, enabling users to track how a metric's value has changed over specific dates or periods.

Key Information Stored

The table stores a historical snapshot of a metric's value at a specific point in time. Key columns include the unique identifier ACT_MET_HST_ID, which serves as the primary key. The ACTIVITY_METRIC_ID column is a critical foreign key linking the historical record to its parent metric definition in the AMS_ACT_METRICS_ALL table. The METRIC_ID links to the base metric definition in AMS_METRICS_ALL_B, identifying the type of metric being tracked (e.g., 'ROI', 'Cost per Lead'). The SCENARIO_ID links to AMS_MET_SCENARIOS, allowing for scenario-based planning and forecasting. The ACTIVITY_METRIC_ORIGIN_ID references AMS_OBJECT_ASSOCIATIONS, connecting the metric to the specific marketing object, such as a campaign. Finally, the table will contain columns for the actual metric value and the date or period for which that value is recorded, forming the core of the historical data.

Common Use Cases and Queries

A primary use case is generating performance trend reports for marketing campaigns. Analysts query this table to plot metric values over time, calculate period-over-period growth, and assess campaign effectiveness. Common SQL patterns involve joining to related dimension tables to filter by campaign, metric type, or date range. For example, a query to retrieve the historical ROI for a specific campaign might join AMS_ACT_METRIC_HST to AMS_ACT_METRICS_ALL and AMS_OBJECT_ASSOCIATIONS. Another critical use is data auditing and reconciliation, where historical snapshots are compared against current values to verify data integrity and track manual overrides or system-calculated changes.

Related Objects

  • AMS_ACT_METRICS_ALL: The primary foreign key relationship. This table holds the current and master record for the activity metric, of which AMS_ACT_METRIC_HST stores the historical values.
  • AMS_METRICS_ALL_B: Provides the definition and attributes of the metric type (e.g., ROI) via the METRIC_ID foreign key.
  • AMS_MET_SCENARIOS: Associated via SCENARIO_ID, enabling the storage of historical data for different planning or forecasting scenarios.
  • AMS_OBJECT_ASSOCIATIONS: Links the historical metric record to the specific marketing entity (like a campaign) through the ACTIVITY_METRIC_ORIGIN_ID.