Promo Genie: Turning Trade-Promotion Data into Actionable Decisions
Promo Genie is a Databricks-native trade-promotion intelligence application built for Bisky Delight, a fictional FMCG biscuit brand. It combines governed Lakehouse data with a Genie Agent to help commercial teams move from raw promotion data to evidence-backed decisions using natural language.
→ What problem, opportunity, or creative idea does your app address?
Trade-promotion data is often scattered across sales, campaign, margin, marketing-spend, and basket datasets. Even when dashboards are available, business users still need to know which filters to select and how to interpret the results.
Promo Genie addresses this gap by providing an AI-powered investigation experience. Instead of requiring users to search through dashboards, the app proactively investigates four important business areas:
- Promotions requiring immediate attention
- Margin erosion caused by discounts and promotion costs
- Product cannibalization
- Growth, bundle, and cross-sell opportunities
Each investigation produces a business explanation, supporting data, visualizations, and a recommended next action. Users can then continue asking follow-up questions within the same Genie conversation.
→ Who is it designed for?
Promo Genie is designed primarily for trade marketing managers, category managers, sales leaders, revenue-growth teams, and commercial analysts in FMCG and retail organizations.
These users understand the business but may not write SQL or know the underlying data model. The application lets them ask questions in everyday business language and receive answers grounded in governed enterprise data
→ How does Genie power the app’s main experience?
Genie is the analytical and recommendation engine at the center of Promo Genie.
The landing page is a Genie Agent Command Center, rather than a fixed dashboard. When a user launches one of the predefined business missions, the application sends a structured analytical question to Genie. Genie interprets the intent, queries the governed Gold tables, evaluates the evidence, and returns:
- A narrative analysis
- Supporting query results
- Data visualizations
- Suggested follow-up questions
- A specific recommended business decision
For example, Genie can identify a promotion with poor ROI or margin exposure, quantify its commercial impact, and recommend a concrete action such as reviewing the discount, stopping the campaign, reallocating spend, or testing a stronger product bundle. The recommendation displayed by the application is extracted directly from Genie's response—it is not invented by the app.
The application preserves Genie's conversation and message identifiers. A user can open any recommendation and continue investigating it within the same conversation by asking questions such as “Why did this happen?”, “Is the pattern consistent across regions?”, or “Which alternative promotion performed better?”
Optional external-LLM summarization :
Promo Genie also includes an optional LLM Key Findings feature. After Genie has completed the analysis, users can choose to generate a shorter executive summary using an external LLM endpoint.
The external LLM receives the Genie response and a limited sample of the returned result data. It converts that material into three to five concise, business-ready findings highlighting commercial impact, anomalies, and supported next steps.
This creates a deliberate separation of responsibilities:
- Genie performs the governed data analysis and produces the recommendation.
- The external LLM optionally summarizes Genie's answer for faster executive consumption.
- Promo Genie presents the recommendation, supporting evidence, visualization, summary, and follow-up investigation in one workflow.
→ Technical architecture and data flow
Data generation — Historical and incremental FMCG data is generated for sales, promotions, baskets, marketing spend, products, regions, and costs.
Unity Catalog Volume — New CSV files are uploaded to a governed landing zone.
Lakeflow Declarative Pipeline — Auto Loader ingests the files into Bronze, cleans and validates them in Silver, and creates business-ready Gold tables.
Governed Gold layer — Curated tables cover promotion ROI, margins, cannibalization, cross-sell, marketing effectiveness, sales, and inventory.
Genie Agent — Genie uses the Gold tables, business definitions, instructions, and sample questions to produce grounded answers and recommendations through a SQL Warehouse.
Databricks Page — A Databricks Page was created to present curated visual insights and make important promotion metrics easier to explore.
Databricks App — The Streamlit app provides the Agent Command Center, ingestion controls, pipeline monitoring, Genie chat, evidence tables, and charts.
Optional external LLM — After Genie completes its analysis, users can generate a short executive summary. Genie remains responsible for the analysis and recommendation; the external LLM only summarizes the supplied answer and evidence.
Flow:
📥 Data → Unity Catalog → Bronze/Silver/Gold → SQL Warehouse → Genie → Recommendation → Optional LLM Summary → Databricks App/Page
→ How does Genie power the app’s main experience?
Genie is the analytical engine at the center of Promo Genie.
The landing page is a Genie Agent Command Center, not a fixed dashboard. When a user launches an agent mission, the application sends a clearly defined business question to Genie. Genie interprets the intent, queries the governed Gold tables, and returns an explanation with supporting evidence.
The app preserves the Genie conversation and message identifiers, allowing users to move from a proactive insight into a deeper investigation without losing context. They can ask why a result occurred, compare it across regions, inspect trends, or test another business hypothesis in the same conversation.
Genie therefore supports both sides of the experience:
- Proactive discovery: predefined missions surface risks and opportunities before users know what to ask.
- Interactive investigation: users ask their own questions and continue with contextual follow-ups.
Because every answer is linked to governed data, result tables, and visual evidence, Genie helps turn conversational analytics into a trusted decision workflow rather than a generic chatbot experience
→ What can users ask the Genie Agent?
Users can ask questions about promotion performance, profitability, sales uplift, cannibalization, regional or channel performance, marketing effectiveness, and basket affinity.
Example questions include:
- Which promotion requires the most immediate business attention?
- Which promotion delivered the best or worst ROI?
- Where are discounts and promotion costs eroding margin?
- Which SKU, region, or channel has the greatest margin exposure?
- Are promotions generating incremental category sales or shifting demand from another product?
- Which products are most frequently purchased together?
→ What did you learn while building and testing the app?
The biggest lesson was that high-quality conversational analytics depends as much on data design and business context as it does on the user interface.
Clear Gold-table modeling, meaningful table and column comments, consistent metric definitions, Genie instructions, and tested question-to-SQL examples materially improved response quality. Business concepts such as ROI, incremental margin, cannibalization, and cross-sell need explicit definitions rather than relying on the agent to infer their meaning.
I also learned that a useful agent experience should preserve evidence and context. Users need to see the question that was asked, the underlying results, and the recommended decision—and then be able to continue investigating without starting over.
