Results for “bim_sales_cycle_summ”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BIM_SALES_CYCLE_SUMM is a summary table within the Oracle E-Business Suite Marketing Intelligence (BIM) module. It stores aggregated sales cycle metrics computed over user-defined periods, providing a pre-computed analytical layer that supports campaign performance analysis across media, channels, market segments, and geographies. The table is owned by the BIM schema and is classified as VALID in the ETRM repository.

The table is populated by the BIM_SALES_CYCLE_SUMM_PKG concurrent program, which reads transactional lead and opportunity data and materializes it into period-based summaries. Because the table is refreshed by a scheduled concurrent request rather than maintained transactionally, it functions as a denormalized reporting structure intended for high-volume analytical queries. The presence of standard concurrent program audit columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) confirms this batch-populated design.

From a Data Vault modeling perspective, the metadata's heuristic classification is standalone. This reflects the fact that the only documented foreign key is to FND_SECURITY_GROUPS, with no dependency links to dimensional hubs such as campaign or media master tables. In practice, this table behaves like a periodic snapshot satellite that captures measured cycle durations at defined interval boundaries, keyed by a composite of business dimensions rather than by an integrated hub-and-link structure.

Key Information Stored

The table contains 21 documented columns. The most significant are the dimensional keys and the two measured cycle values:

• CAMPAIGN_ID — identifies the marketing campaign to which the summarized cycle data belongs.
• PERIOD_NAME, PERIOD_START_DATE, PERIOD_END_DATE — define the user-defined summary period, forming the temporal grain.
• MEDIA_ID, CHANNEL_ID — identify the media vehicle and channel associated with the activity.
• MARKET_SEGMENT_ID, GEOGRAPHY_CODE, SALES_CHANNEL_CODE — segment, geographic, and sales-channel breakouts for the aggregated measurements.
• LEAD_TO_ORDER_CYCLE — the average elapsed cycle duration from lead creation to order booking.
• LEAD_TO_OPPR_CYCLE — the average elapsed cycle duration from lead creation to opportunity creation.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, enforcing row-level security visibility.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns.

The unique index BIM_SALES_CYCLE_SUMM_U1 spans PERIOD_NAME, PERIOD_START_DATE, PERIOD_END_DATE, CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, MARKET_SEGMENT_ID, GEOGRAPHY_CODE, and SALES_CHANNEL_CODE. This composite unique key is the true business key of the table; no single-column surrogate primary key is documented, so the unique index serves as the de facto identifier for each summarized row.

Common Use Cases and Queries

Typical usage centers on campaign cycle-time analysis and period-over-period comparisons across marketing dimensions. A common pattern joins the summary to campaign and period references while filtering by security group:

• Measuring average lead-to-opportunity conversion velocity by media and market segment for a given PERIOD_NAME.
• Trending lead-to-order cycle times across consecutive periods to assess campaign responsiveness.
• Comparing geographic performance by GEOGRAPHY_CODE and SALES_CHANNEL_CODE.
• Validating summary completeness by inspecting REQUEST_ID and PROGRAM_UPDATE_DATE to confirm the most recent BIM_SALES_CYCLE_SUMM_PKG run.

A representative query aggregates the two cycle measures by dimension:

• SELECT CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, MARKET_SEGMENT_ID, AVG(LEAD_TO_ORDER_CYCLE), AVG(LEAD_TO_OPPR_CYCLE) FROM BIM_SALES_CYCLE_SUMM WHERE PERIOD_NAME = :period AND SECURITY_GROUP_ID = :sgid GROUP BY CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, MARKET_SEGMENT_ID;

Because the table is pre-aggregated, queries avoid the cost of joining raw lead and opportunity tables, making it well suited for Marketing Intelligence dashboards and extract feeds.

Related Objects

The documented relationship footprint is narrow, reflecting the standalone classification:

• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; governs row-level security filtering.
• BIM_SALES_CYCLE_SUMM_PKG — the concurrent program package responsible for populating and refreshing the table.
• BIM Campaign and Media master tables — referenced logically through CAMPAIGN_ID, MEDIA_ID, and CHANNEL_ID, though no formal FK is documented.
• BIM_CHANNELS and market segment/geography reference tables

Because the metadata documents only the SECURITY_GROUP_ID foreign key, downstream dependencies are primarily logical rather than enforced, and consumers should validate dimensional lookups against the appropriate BIM master objects.