Search Results bil_do_ld_oppty_dly_pk

Overview

The table BIL_DO_LD_OPPTY_DLY is a data warehouse object within the Oracle E-Business Suite (EBS) Sales Intelligence (BIL) module. Its primary function is to serve as a collection point for daily aggregated metrics tracking the conversion of marketing leads into sales opportunities. This table is part of the "Data Out" layer, which typically structures operational data for analytical reporting and business intelligence consumption. By storing key performance indicators aggregated by date and various business dimensions, it enables performance analysis of lead generation campaigns, sales channel effectiveness, and lead qualification processes. The metadata indicates this specific object is "Not implemented in this database," suggesting it may be a predefined template or a legacy structure not deployed in all instances, but its design intent is to support historical trend reporting for sales and marketing operations.

Key Information Stored

The table's structure is designed to store aggregated counts or amounts, pivoted by a combination of critical business dimensions that define a unique record. The primary key columns, which together define the grain of the data, are:

• COLLECTION_DATE: The date for which the metrics are aggregated.
• SALES_GROUP_ID: Identifier for the sales team or organization responsible for the opportunity.
• LEAD_RANK_ID: Identifier for the priority or quality score assigned to the original lead.
• SALES_CHANNEL_CODE: Code representing the channel (e.g., Direct, Partner, Online) through which the lead/opportunity was generated.
• VEHICLE_RESPONSE_CODE: A code identifying the specific marketing vehicle or campaign element (e.g., a particular email blast, webinar, trade show) that prompted the lead's response, directly relevant to the user's search term.
• SOURCE_PROMOTION_ID: Identifier linking to the broader marketing promotion or campaign.

Non-key columns, while not listed in the excerpt, would typically contain aggregated factual metrics such as LEAD_COUNT, OPPORTUNITY_COUNT, CONVERSION_AMOUNT, or similar measures calculated for the combination of dimensions defined by the primary key.

Common Use Cases and Queries

This table is foundational for analytical reporting on marketing return on investment (ROI) and sales pipeline health. A primary use case is analyzing the effectiveness of different marketing vehicles by joining the VEHICLE_RESPONSE_CODE to descriptive tables. For example, a query to determine the lead-to-opportunity conversion rate by marketing vehicle for a given period would be common. The SQL pattern often involves grouping and filtering on the dimensional keys:

SELECT vrc.MEANING AS VEHICLE_NAME, SUM(f.OPPORTUNITY_COUNT) / SUM(f.LEAD_COUNT) AS CONVERSION_RATE
FROM BIL_DO_LD_OPPTY_DLY f,
    FND_LOOKUP_VALUES vrc
WHERE f.VEHICLE_RESPONSE_CODE = vrc.LOOKUP_CODE
  AND vrc.LOOKUP_TYPE = 'BIL_VEHICLE_RESPONSE'
  AND f.COLLECTION_DATE BETWEEN :P_START_DATE AND :P_END_DATE
GROUP BY vrc.MEANING
ORDER BY CONVERSION_RATE DESC;

Other scenarios include trend analysis of conversion rates by sales channel or lead rank, and performance dashboards for sales groups tracking daily pipeline generation from marketing-sourced leads.

Related Objects

As a data warehouse table, BIL_DO_LD_OPPTY_DLY has relationships with several other EBS objects. Its dimensions are typically foreign keys to reference tables or views:

• FND_LOOKUP_VALUES: Likely source for descriptive names for codes like SALES_CHANNEL_CODE and VEHICLE_RESPONSE_CODE.
• Sales Group/Organization Tables: SALES_GROUP_ID may relate to HR_OPERATING_UNITS or sales-specific hierarchy tables.
• Lead and Opportunity Source Tables: SOURCE_PROMOTION_ID may link to campaign tables in the Marketing or Trade Management modules.
• BIL Fact and Dimension Tables: It exists within a constellation of other BIL fact tables (e.g., for orders, invoices) that share conformed dimensions like SALES_CHANNEL_CODE.
• BIL_DO_LD_OPPTY_DLY_PK: The primary key constraint enforcing the uniqueness of the dimensional combination.