Building Predictive AI Dashboards: From Churn Prediction to LLM Generated Recommendations
Executive Summary
This article demonstrates how to build intelligent, role-adaptive analytics using Omni, BigQuery ML, and Gemini. You'll learn to implement row-level access control, predictive churn models with explainability, and AI-generated customer retention recommendations, all in a single unified dashboard.
Evolution of BI
We're in the middle of a fundamental shift in how organizations use data. For years, business intelligence meant looking backward through dashboards that told you what happened last week, last month, last quarter. It was not "data driven decision making," but was "data informed hindsight."
Modern Landscape
Today's BI ecosystem is dominated by platforms like Tableau, PowerBI, Looker and newer entrants like Omni and Sigma. Each takes a different approach to the question: “where should your analytics logic live?”.
Highly Decentralized Tools - think SQL runners, notebooks like Jupyter, or even extract facing tools where data gets pulled into spreadsheets. These tools maximize user freedom. Analysts can move quickly, write custom queries, and answer questions without waiting for anyone's approval. The problem? Organizations struggle to govern them. There's no shared understanding of metrics. Ten different people calculate "monthly active users" ten different ways. Change management becomes impossible.
Highly Centralized Tools - on the opposite end are the enterprise-grade platforms with governed, centralized modeling layers. Everything flows through a central BI team who maintains the canonical data model. These tools ensure consistency and control. The problem? They move intentionally slowly. When a small product team needs to answer a quick question about a new feature, they need to open a ticket with the centralized BI team and hope their request makes it through the backlog.
Modern platforms aim to bridge this gap by combining the best of both worlds. They provide a governed, shared semantic layer with consistent metric definitions across the organization. This hybrid approach is critical for what we're building in this article. We need the governance to ensure access controls work consistently and we also need the flexibility to rapidly iterate on ML models, AI-generated insights, and embedded analytics without waiting for a central team to approve every change. While Omni and Sigma are two standout products that implement this approach, Omni will serve as the primary BI environment for the examples and workflows discussed in this article.
The AI Promise: Why AI Integrations in BI Often Fall Short
Most modern BI platforms now offer AI features as standard capabilities, and they're genuinely useful for common analytical tasks like
Natural Language Query
Auto-Generated Summaries
Pattern Detection
Where Built-In AI Falls Short
Auto-generated summaries that describe what happened but don't tell you what to do about it
Predictions divorced from operational context: "Customer #47392 has an 87% churn probability" okay, but who is that? What should I do about it? Why are they at risk?
Generic recommendations that could apply to anyone: "Consider sending a retention email" instead of "Call this customer today about their sizing issues"
Three Core Problems When Operationalizing AI in Analytics
Organizations run into three fundamental problems when trying to operationalize AI and make analytics truly intelligent.
Problem 1: Fragmented Analytics Foundations
Most companies try to build AI on top of analytics systems that were never designed to work together. Each team has built its own dashboards and behind each dashboard is a different data model.
Marketing view: Customers and prospects structured around campaigns and purchases.
Sales View: Structured around leads, opportunities, and stages.
Customer Success view: Structured around accounts, subscriptions, and health scores.
These models define the business differently. When you try to build something unified, like a churn prediction model, you’re forced to choose which definition and which data model to base it on. No matter what you pick, it won’t match how other teams see the customer.
Problem 2: Intelligence That Never Reaches Decision Makers
Data science teams build powerful models like propensity scores, recommendations, anomaly detection, etc. In many companies these insights stay in Jupyter notebooks or database tables that not many know about. The people who actually need the information like a customer success director trying to reduce churn don’t see the predictions.
Even when predictions make it into dashboards, they’re often just numbers with no context. A score like “Customer A: 87% churn probability” doesn’t explain why or what action to take.
Problem 3: Access Control
Now layer on a global operation. Your CEO needs to see conversion trends across all markets US, Europe, Asia Pacific. Your regional VPs need deep dives into their specific territories. Your country managers need granular views of their markets. Your customer success representatives need to see individual at risk customers within their assigned accounts.
Traditional approaches force an impossible choice:
Option A: Build separate dashboards for every role.
Option B: Give everyone access to everything and hope they filter correctly.
Bringing It Together
These three problems compound each other. Fragmented foundations make it hard to build consistent AI. Models that never reach decision makers can't drive action. And access control challenges mean even when you build something useful, you can't safely deploy it to the people who need it.
What if you could solve all three at once? What if the same dashboard could automatically adapt to show each user exactly what they need to see, with AI-powered predictions that explain themselves and recommend specific actions, all built on a unified analytics foundation that ensures consistency?
That's exactly what we're going to build.
Adaptive access control that personalizes every view based on user role
Predictive ML & AI Insights that forecast customer churn, explains why each customer is at risk, and generates personalized action recommendations.
Embedded Analytics that integrate everything into an operational portal while maintaining access controls.
The result?
Executives get strategic visibility
Managers get tactical focus
Front line teams get actionable next steps
All from the same unified analytics layer.
The Dataset: Look eCommerce BigQuery
We’ll be using the Look eCommerce dataset in BigQuery. It is a publicly available dataset designed for analytics learning and experimentation. It contains web and transaction data: users, product views, purchases, traffic sources, and customer behavior patterns across multiple markets.
For our scenario, we’ve simulated a global organization operating in the US, China, the UK, and Germany, each with its own user roles and access requirements.
Part 1: Adaptive access control
The Business Challenge
Imagine you're running e-commerce operations across the US, China, UK, and Germany. Your CEO needs to:
Track overall conversion trends across all markets
Identify which traffic sources drive the most revenue globally
Spot opportunities and risks wherever they appear
Meanwhile, your China Country Manager needs to:
See conversion trends only for China
Understand churn risk among their top customers
Get actionable recommendations tailored to their market
These are fundamentally different questions requiring different perspectives on the data. But here's the key idea, they can all be answered by the exact same underlying datasets. The CEO, the China Manager, and the Customer Success rep are all looking at the same customer transactions, the same web events, the same order history. The difference is in which subset of that data each person should see and how it should be aggregated for their needs.
The Technical Approach: Role-Based Access at the Data Layer
Modern BI platforms apply access controls at multiple levels
Dataset Level
Topic Level
Row Level
Column Level
For our use case, we're primarily leveraging row-level access control. The entire organization uses the same customer data, order data, and web event data. But the rows each person sees are automatically filtered based on their assigned geography. So, instead of creating multiple dashboards, you create one dashboard with user attributes and access filters that automatically adapt what each person sees.
Here's how it works conceptually:
Step 1: Define User Attributes
User Attributes are properties assigned to individual users that describe their access scope and context. When a user logs in, the system knows who they are and what attributes they carry. These attributes then drive what data they can see.
Step 2: Configure Access Filters
At the dataset level, create filters that automatically apply to every query based on user attributes:
Step 3: Build Once, Adapt Automatically
Now when you build any visualizations, it automatically respects these access rules:
The CEO sees aggregate data across US, China, UK, Germany
The China manager sees only China data
The same queries, same dashboard different perspectives
Thus achieving Same queries. Same dashboard. Different perspectives.
What This Looks Like in Practice
The dashboard we built includes:
Traffic Source Breakdown – How visitors arrive (organic, paid, social, etc.)
Customer Journey Funnel – Movement through stages: Visitors → Product Views → Cart → Purchase
Stage by Stage Conversion Rates – Percentages at each critical transition
Performance by Traffic Source – Customer status broken down by acquisition channel
Monthly Trends – Time-series showing conversion patterns and seasonality
All of these components automatically filter based on who's viewing them. No manual intervention, no duplicate dashboards, no risk of showing sensitive data to the wrong people.
Part 2: Predictive ML models and AI-generated insights
Historical dashboards are valuable, but reactive. By the time churn appears in your metrics, you've already lost customers.
The real value comes from predicting what's about to happen and knowing what to do about it.
This required building a complete ML and AI pipeline with three goals:
Predict which customers are at risk of churning
Explain why each customer is at risk
Recommend specific actions tailored to each customer's behavior
And critically deliver all of this within the same Omni dashboards while respecting the same role based access controls we built in Part 1.
The Five Stage ML Pipeline
Stage 1: Build the Prediction Model
All modern data warehouses now offer native ML capabilities that let you build models where your data already lives. This vertical integration keeps your entire pipeline in one platform, reducing data movement, security concerns, and the complexity of orchestrating across multiple systems.
Our data was already housed in BigQuery so we decided to use BigQuery ML. BigQuery ML reports both training and evaluation metrics, making it easy to monitor model quality. For production use, you should also track these metrics over time as customer behavior shifts, which we'll discuss in the Technical Considerations section.
For more details on how BigQuery ML handles training, see BigQuery ML training documentation.
AutoML handles many of the complex parts automatically:
Feature engineering (creating meaningful variables from raw data)
Model selection (choosing the best algorithm)
Hyperparameter tuning (optimizing model performance)
Train/test splitting (help’s avoiding overfitting)
Cross-validation
You provide historical customer data with a label (churned: yes/no)* and key behavioral signals:
Days since last purchase
Total purchases and average order value
Email engagement rates
Customer support ticket history
Traffic source and browsing patterns
The model learns patterns: "Customers who haven't purchased in 60+ days, have declining email engagement, and opened 3+ support tickets typically churn within 30 days."
Note: If a customer doesn't return in 90 days, we can consider them to have churned.
Implementation approach:
Stage 2: Generate Predictions with Risk Levels
Once trained, run predictions on your current customer base. The model outputs a probability score for each customer (0-100% chance of churning).
We categorize these into risk levels:
Critical Risk: 80%+ probability
High Risk: 60-79% probability
Medium Risk: 40-59% probability
Low Risk: <40% probability
This risk categorization helps teams prioritize. Your customer success manager can focus on the 15 critical risk accounts that represent more in annual revenue.
Implementation approach:
Stage 3: Explain the Predictions
A prediction score isn't enough. If a customer has an 85% churn probability, your team needs to understand why.
This is where explainability becomes critical. We use SHAP (Shapley Additive explanations) values to break down each prediction into individual feature contributions.
For example, Customer A's 85% churn probability might be driven by:
days_since_last_order: 67 days (contribution: +31%)
return_rate: 45% (contribution: +19%)
email_engagement_score: 0.2 (contribution: +15%)
This tells the story: "This customer is at risk primarily because they haven't ordered in over two months, they return almost half their purchases, and they've stopped opening our emails."
Implementation approach:
Stage 4: Build Customer-Specific Context
We build detailed, customer specific prompts instead of just giving raw SHAP values to an AI. This is how our approach stands out from generic churn systems.
For each at risk customer, we build a detailed context that includes:
Their complete behavioral profile (orders, revenue, engagement)
The top factors driving their churn risk with attribution scores
Spending patterns and product preferences
Clear instructions for generating personalized, actionable recommendations
Example prompt structure:
You are a retention analyst. Analyze this customer: PROFILE: - Churn probability: 87% - Total orders: 23 | Revenue: $3,450 | Avg order: $150 - Days since last order: 67 - Return rate: 43% - Email engagement: 0.18 TOP RISK FACTORS: 1. days_since_last_order (impact: +0.31) 2. return_rate (impact: +0.19) 3. email_engagement (impact: +0.15) Provide a response with: - 3 specific churn reasons ranked by severity - 1 tailored action recommendation based on THIS customer's behavior - Expected outcome and why this approach fits their profile
This structured approach ensures the AI has complete context to generate truly personalized insights.
Implementation approach:
Stage 5: Generate Personalized Insights with AI
Now comes the critical decision: how do you actually generate these insights? We explored two approaches:
Approach A: BigQuery AI with Table Creation
This works, but has drawbacks:
Requires scheduled jobs to keep recommendations fresh
Creates data latency (insights could be hours old)
Adds storage and maintenance overhead
Separates the AI generation from the analytics layer
Approach B: BI-Layer AI Generation (Our Choice)
Instead of pre-generating AI insights in database tables, modern BI platforms can call LLMs directly from the semantic layer. Example
Omni uses views with SQL that calls ML.GENERATE_TABLE
Sigma makes this accessible with custom functions
Tableau and Power BI can achieve this through custom SQL or stored procedures
Here's how in Omni:
label: churn_risk_explanations
sql: |-
SELECT
user_id,
reason_1,
severity_1,
reason_2,
severity_2,
reason_3,
severity_3,
recommendation_action,
recommendation_outcome,
recommendation_why
FROM ML.GENERATE_TABLE(
MODEL `gemini-2-flash-lite`,
(
SELECT
user_id,
CONCAT(
'You are a customer retention analyst. Analyze this customer data and respond with structured data. ',
'Customer metrics: ',
'Churn probability: ', CAST(ROUND(churn_probability * 100, 1) AS STRING), '%, ',
'Total orders: ', CAST(total_orders AS STRING), ', ',
'Total revenue: $', CAST(ROUND(total_revenue, 2) AS STRING), ', ',
'Average order value: $', CAST(ROUND(total_revenue / NULLIF(total_orders, 0), 2) AS STRING), ', ',
'Days since last order: ', CAST(days_since_last_order AS STRING), ', ',
'Return rate: ', CAST(ROUND(return_rate, 1) AS STRING), '%, ',
'Engagement score: ', CAST(ROUND(COALESCE(engagement_score_attribution, 0), 2) AS STRING), ', ',
'Customer lifetime days: ', CAST(customer_lifetime_days AS STRING), ', ',
'Unique categories purchased: ', CAST(unique_categories_purchased AS STRING), '. ',
'Top 5 feature impacts: ',
(SELECT STRING_AGG(CONCAT(fa.feature, ': ', CAST(ROUND(fa.attribution, 3) AS STRING)), ', ')
FROM UNNEST(top_feature_attributions) AS fa), '. ',
'Provide 3 specific reasons why this customer is at risk (each with a severity level of high, medium, or low), ',
'plus one personalized recommendation with the action to take, expected outcome, and why it fits their profile. ',
'Base severity on feature impact values. Keep all text concise and business-focused. ',
'IMPORTANT: Tailor the recommendation based on the customer' || "'" || 's specific metrics - ',
'consider their order frequency, spending patterns, engagement level, and return behavior. ',
'High-value customers need different strategies than low-engagement ones. ',
'Recent buyers need different approaches than long-dormant ones.'
) AS prompt
FROM `high_risk_customers`
),
STRUCT(
"reason_1 STRING, severity_1 STRING, reason_2 STRING, severity_2 STRING, reason_3 STRING, severity_3 STRING, recommendation_action STRING, recommendation_outcome STRING, recommendation_why STRING" AS output_schema,
500 AS max_output_tokens,
0.5 AS temperature
)
)
dimensions:
reason_1: {}
severity_1: {}
reason_2: {}
severity_2: {}
reason_3: {}
severity_3: {}
recommendation_action: {}
recommendation_outcome: {}
recommendation_why: {}
user_id:
format: ID
measures:
count:
aggregate_type: count
When a Country Manager opens this dashboard, they see visually rich cards for each at-risk customer. No JSON parsing, no custom frontend code, just clean structured data rendered.
Key advantages:
Because the AI generation happens in the view layer, these report cards respect role-based access automatically and are generated on-demand when they load the dashboard.
A critical advantage of this approach is that analysts can iterate on prompts and output structure directly in the workbook layer without needing to modify database tables or redeploy pipelines.
Part 3: Bringing It All Together – The Embedded Experience
All of this intelligence needs to be accessible where people actually work. We embedded these dashboards directly into the company's internal operations portal using Omni's SDK. Here's the technical approach:
Secure Embedding with SSO
The embedding layer handles authentication and authorization:
Step 1: Set up a serverless function (we used Google Cloud Functions) that generates signed embed URLs:
Step 2: In your web application, request the embed URL and display it in an iframe:
The beauty of this approach: the embed URL is signed with the user's identity, so Omni automatically applies the correct access filters we configured in Part 1. A country manager automatically sees only their country's data with no additional code needed.
Technical Considerations for Production
If you're building something similar, here are critical considerations based on best practices
Model Monitoring is Critical
ML models degrade over time as customer behavior shifts. Set up automated monitoring:
Prediction accuracy tracking: Compare predicted churn against actual churn on a weekly or monthly basis. If your model predicted 100 customers would churn and only 60 actually did, that's a valuable signal about drift.
Feature drift monitoring: Track whether the distributions of your input features are changing. If "days_since_last_order" used to average 30 days and now averages 50 days, your model's learned patterns may no longer apply.
Automated retraining pipelines: Set up scheduled retraining (quarterly is a reasonable starting point for churn models) or trigger-based retraining when accuracy drops below acceptable thresholds.
Cost Management for AI Inference
Running Gemini on every customer would be expensive. Our approach:
Only generate AI insights for customers above 70% churn probability
Use Gemini Flash Lite (cheaper, faster) for standard cases
Cache recommendations as churn drivers don't change hourly
Batch processing overnight for non-urgent insights
Feature Quality Drives Everything
The quality of your ML explainability directly impacts insight quality. Invest time in:
Feature engineering: create meaningful, interpretable features
Feature importance analysis: understand what drives your model
Validation with domain experts: do SHAP values align with business intuition?
Conclusion: From Data to Decisions
What We Actually Built
Let's recap what this system does in practice:
A CEO logs in and sees global conversion trends across all markets, spots 47 high risk customers representing $2.1M in annual revenue across all regions. They drill into the AI-generated insights and notice a pattern. Customers acquired through Email have 40% higher churn rates than organic customers. This sparks a strategic conversation about marketing channel quality, not just volume.
A China Country Manager logs in to the exact same dashboard. They automatically see only Chinese market data, same metrics, same visualizations, but scoped to their geography. They review their 12 critical risk customers and see AI-generated explanations: "Customer A hasn't ordered in 67 days, returns 45% of purchases, and has stopped opening emails." The recommendation is specific: "Offer personalized product suggestions based on their past successful purchases, combined with a time limited return shipping credit to address return friction." This is advice tailored to this customer's specific behavioral pattern.
A Customer Success Representative sees their assigned accounts, prioritized by churn risk and customer value. They don't need to understand SHAP values or machine learning. They see clear reasons and specific actions. They can immediately reach out to high risk customers with interventions rather than generic "we miss you" emails.
Same data foundation. Same dashboard. Three completely different experiences, each perfectly suited to the user's role and decision-making needs.
The Three Pillars That Made This Possible
1. Adaptive Access Control Through Semantic Layer
2. Predictive ML With Explainability
3. AI-Generated Recommendations That Respect Data Governance
Why This Matters
The traditional distinction between "BI tools" and "AI/ML platforms" is blurring. Data warehouses now have native ML capabilities. BI platforms can call LLMs directly. Most organizations are bolting AI onto existing BI systems as an afterthought. They build ML models, write predictions to database tables, and hope someone builds a dashboard that surfaces them. The predictions exist, but they're disconnected from the context decision makers need.
We've shown a different path: build AI into the analytics foundation itself. Use the semantic layer as your governance backbone. Generate insights on-demand in the BI layer. Make predictions inherit the same access controls as the dashboards they appear in.
If you're building something similar in your organization, start with the foundation:
Invest in your semantic layer first. Get your metric definitions consistent, implement user attributes and groups, and ensure row level access control works correctly. Everything else builds on this.
Start simple with ML. Use your data warehouse's native ML capabilities to reduce friction.
Make explainability non negotiable. SHAP values or similar explainability techniques should be part of your pipeline from day one, not added later.
Generate insights in the BI layer. The ability to rapidly iterate on prompts and test different recommendation strategies is more valuable.
Final Thoughts
The analytics landscape is shifting from looking behind reporting to forward looking intelligence. Dashboards that just show what happened aren't enough anymore. The competitive advantage goes to organizations that can predict what's about to happen and recommend what to do about it and make that intelligence accessible to everyone who needs it, with appropriate access controls and personalization.
