Search Results bim_lead_conv_summ




Overview
Explore in AI Chat

Overview

The BIM.BIM_LEAD_CONV_SUMM table is a summarized marketing analytics object within the Oracle E-Business Suite Marketing Intelligence module (BIM). It stores pre-aggregated lead-conversion metrics segmented by user-defined reporting periods and multiple analytical dimensions. The table is populated by the BIM_LEAD_CONV_SUMM_PKG concurrent program, which reads detailed transactional marketing data and rolls it up into this summary structure to accelerate reporting performance. The Marketing Intelligence module is flagged as obsolete in the ETRM documentation, meaning the object is retained for backward compatibility with existing 12.1.1 and 12.2.2 installations but is no longer the focus of active development.

From a Data Vault modeling perspective, the table is classified heuristically as standalone. Because it holds no foreign keys to other detail tables (aside from the security context reference to FND_SECURITY_GROUPS), it is best modeled as an independent satellite-style aggregate — a periodic snapshot keyed by its business dimensions rather than as a hub or link. This classification suggests the table is a denormalized summary rather than part of a normalized entity relationship core.

Key Information Stored

The table contains 24 documented columns. The most significant analytical dimensions and measures are:

While no explicit surrogate primary key is documented in the metadata, the unique index BIM_LEAD_CONV_SUMM_U1 serves as the business-key candidate. It is defined on the composite of PERIOD_NAME, PERIOD_START_DATE, PERIOD_END_DATE, CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, MARKET_SEGMENT_ID, SALES_CHANNEL_CODE, GEOGRAPHY_CODE, which together uniquely identify each summary row. Operational lineage columns include REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE and standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

Typical usage centers on campaign effectiveness reporting and lead-funnel conversion analysis. A common query aggregates conversion rates by campaign and period:

SELECT campaign_id, period_name,
       SUM(num_of_leads)                AS leads,
       SUM(num_of_opportunities)        AS opps,
       SUM(num_of_orders_from_leads)    AS orders_from_leads,
       ROUND(SUM(num_of_orders_from_leads)/NULLIF(SUM(num_of_leads),0)*100,2) AS lead_conv_pct
FROM   bim.bim_lead_conv_summ
WHERE  period_start_date >= :p_from
AND    period_end_date   <= :p_to
GROUP  BY campaign_id, period_name
ORDER  BY campaign_id, period_name;

Reports frequently join this table to campaign and geography dimensions to build performance dashboards. Because the data is pre-summarized, it is well suited to period-over-period trend analysis and to feeding BI Publisher or OBIEE reports without hitting high-volume source tables.

Related Objects

The most significant related objects documented or implied by the schema are:

  • FND_SECURITY_GROUPS — joined via BIM_LEAD_CONV_SUMM.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID to enforce organizational data security.
  • BIM_LEAD_CONV_SUMM_PKG — the concurrent program package that populates the summary.
  • Campaign dimension table (BIM_CAMPAIGNS / AMS_CAMPAIGNS variant) — joined on CAMPAIGN_ID for campaign attributes.
  • Media, Channel, and Market Segment dimension tables — joined on MEDIA_ID, CHANNEL_ID, MARKET_SEGMENT_ID.
  • Geography reference data — joined on GEOGRAPHY_CODE.

Given the module's obsolete status, DBAs should confirm dependencies before modification and validate that existing concurrent program schedules still reference BIM_LEAD_CONV_SUMM_PKG correctly in 12.1.1 and 12.2.2 environments.