Salesman Scorecard: Designed a Power BI-based

dashboard providing stakeholders with a data-driven

view of sales opportunities. Integrated a Random
Forest-based predictive model to optimize sales reps'
daily beat plans, increasing sales conversion rates by
18% and improving route efficiency by 25% through
targeted sales strategies.
1. Data Sources & Integration Process
Sales Data (Transactional Data)
✅ Source: CRM, ERP, or POS systems
✅ Key Fields:
 Store ID / Customer ID: Unique identifier for each store
 Sales Date & Time: Timestamp of transactions
 Product Details: Category, SKUs sold
 Revenue Generated: Total amount spent by customers
 Units Sold: Number of products purchased
 Discounts Applied: Impact of promotions or offers
Salesman Data (Performance & Assignment Data)
✅ Source: HR systems, Sales Team Management Tools
✅ Key Fields:
 Salesman ID: Unique identifier for each sales rep
 Assigned Stores / Territory: List of stores assigned to each rep
 Historical Visit Logs: Date & time of past store visits
 Sales Achievements: Revenue contribution per visit
 Conversion Rate: Number of successful transactions per visit
 Average Time per Visit: Time spent per store
 Daily Route / Beat Plan: Order of store visits for the day

2. How Sales Data is Merged with Salesman Data?

1️⃣ Join on Common Fields:
 Store ID / Customer ID is used as the key to link sales data with the
assigned salesman.
 Salesman ID is matched with each transaction to attribute sales
performance to the right rep.
2️⃣ Aggregation & Feature Engineering:
 Calculate Sales per Store per Salesman to analyze individual
performance.
 Compute Conversion Rate = (Total Transactions) / (Total Visits per
Store).
 Derive Average Revenue per Visit = (Total Revenue) / (Number of
Visits).
 Track Store-Level Trends: High-performing vs. low-performing stores.
3️⃣ Daily Performance Updates:
 A daily ETL pipeline updates the dashboard with the latest sales and
visit data.
 Power BI refreshes data through scheduled refresh / direct query for
real-time insights.

3. How the Integrated Data is Used in the Project?

A. Optimizing Beat Plan (Predictive Model Input Features)
 Past Sales per Store → Identify high-potential stores.
 Conversion Rate Trends → Determine which stores need more focus.
 Salesman Performance Data → Prioritize stores based on rep success
rates.
 Geographical Location Data → Optimize visit sequences to reduce
travel time.
B. Power BI Dashboard Metrics
 Sales Performance per Rep (total revenue, conversion rate, avg. sales
per visit).
 Store Priority Score (based on revenue trends & predictive scoring).
 Beat Plan Optimization (recommends the best route for higher
conversions).
 Comparison Metrics (historical vs. current sales trends).
C. A/B Testing for Model Validation
 Control Group: Reps follow traditional visit patterns.
 Test Group: Reps use AI-driven recommended routes.
 Impact Measured:
✅ Increase in total revenue per rep.
✅ Higher conversion rates at optimized stores.
✅ Reduction in unnecessary travel time.
Example Scenario
📌 Salesman A is assigned 10 stores in a city. Historical data shows:
 Store X: 30% conversion rate, $5,000 avg. revenue per visit.
 Store Y: 10% conversion rate, $1,500 avg. revenue per visit.
 Store Z: 50% conversion rate, but visited less frequently.
🔹 Without AI: The rep follows a static route based on past experience.
🔹 With AI: The model prioritizes Store Z & Store X for higher impact, skipping
low-potential visits to Store Y, improving efficiency.

Conclusion
The integration of sales data & salesman data ensures that:
✅ Sales reps target high-conversion stores.
✅ Route planning is optimized for maximum revenue.
✅ Performance tracking enables data-driven decision-making.

Role of Random Forest in Salesman Scorecard Optimization

The Random Forest model is at the core of optimizing the sales reps' daily
beat plans. It predicts the sales conversion probability of a store based on
historical data and external factors. These predictions help prioritize store visits,
leading to an 18% increase in sales conversion rates and a 25%
improvement in route efficiency.

1. Why Use Random Forest?

Random Forest is chosen because:
✅ It handles non-linearity in sales patterns effectively.
✅ It’s robust to missing or noisy data, common in real-world sales datasets.
✅ It automatically identifies important features, helping in decision-making.
✅ It provides high interpretability compared to complex models like deep
learning.

2. Data Used for Training the Model

The Random Forest model uses sales and salesman data as features to predict
the likelihood of a successful sales transaction at a store.
Input Features for the Model
1️⃣ Sales History (Per Store)
 Average revenue per visit
  Previous month’s total sales
 Product category sales distribution
2️⃣ Sales Rep Performance
 Rep’s historical conversion rate at this store
 Revenue generated per visit
 Frequency of past visits
3️⃣ Customer Behavior & Demographics
 Foot traffic trends (weekday vs. weekend sales)
 Store size & location category (urban/rural)
4️⃣ External Factors
 Holiday & festival season data
 Weather conditions (rainy days impact outdoor store visits)
 Competitor promotions nearby
Target Variable
 Successful Sale (1) or No Sale (0)
o The model is trained to classify whether a visit to a store will result
in a sale.

3. Model Training Process

Step 1: Data Preprocessing
 Handled missing values: Imputed missing sales with store averages.
 Encoded categorical data: Used One-Hot Encoding for region, store
type, etc.
 Feature Scaling: Standardized numerical features like revenue and store
footfall.
Step 2: Model Training
 Algorithm: Random Forest Classifier
 Hyperparameters Tuned:
o n_estimators: 100 (Number of decision trees)

o max_depth: 10 (Prevents overfitting)

o min_samples_split: 5 (Minimum samples per split)

o criterion: Gini Impurity

 Training Data Split:

 o 80% Training, 20% Testing

o Stratified sampling to handle class imbalance

Step 3: Model Evaluation

 Metrics Used:
o Accuracy: 85%

o Precision & Recall: Ensured balance between false

positives/negatives
o ROC-AUC Score: 0.92 (Great separation between high/low
conversion stores)

4. How Are the Predictions Used?

A. Store Scoring for Beat Plan Optimization
 Each store gets a sales probability score (0-1).
 Stores with higher predicted sales likelihood are prioritized in the route
plan.
B. Power BI Dashboard Integration
 The model’s predictions are embedded into Power BI to show:
✅ Store Priority Recommendations (High, Medium, Low)
✅ Sales Probability per Store (Helps reps make better decisions)
✅ Route Optimization Insights (Minimizing travel time)
C. A/B Testing for Model Validation
 Test Group: Used AI-recommended store visits.
 Control Group: Used traditional visit patterns.
 Results:
✅ 18% Increase in Sales Conversions
✅ 25% Reduction in Unproductive Travel

5. Handling Counter-Questions in an Interview

Q1. Why Random Forest and not XGBoost?
 Random Forest is more robust to noisy and unbalanced data.
 XGBoost requires extensive hyperparameter tuning, while RF performs
well out of the box.
Q2. How do you handle overfitting in Random Forest?
 Limited max_depth of trees to prevent overfitting.
 Used cross-validation and pruned trees that added no value.
Q3. How do you ensure model fairness for new sales reps?
 Avoided bias by excluding sales rep tenure from predictions.
 Ensured fair store prioritization based on store, not just past sales rep
performance.

What would you do to optimize the query for large

datasets?
Indexing is a technique used in databases to speed up the retrieval of rows. An
index creates a data structure (like a lookup table or a sorted list) that allows the
database to quickly locate rows without scanning the entire table.
Key Concepts of Indexing
1. Structure:
Most databases use a B-tree or hash table structure for indexing.
o B-tree Index: Maintains a sorted structure to efficiently support
range queries (<, >, BETWEEN).
o Hash Index: Uses hash functions for exact matches (=).

2. Indexed vs. Non-Indexed Search:

o Without an index, the database performs a full table scan,
checking each row sequentially.
o With an index, the database searches through the index structure,
reducing the rows scanned.
How Partitioning Works in Databases
Partitioning is the process of dividing a large table into smaller, more
manageable pieces, called partitions, based on certain criteria like ranges or
lists of values. Each partition is treated as a separate sub-table while maintaining
the logical integrity of the main table.
Why Partitioning?
1. Improved Query Performance:
o Queries can scan only the relevant partition(s) instead of the entire
table, reducing I/O and improving speed.
2. Manageability:
o Easier to manage subsets of data, such as archiving old partitions or
adding new ones.
3. Parallelism:
o Database systems can process queries on multiple partitions in
parallel.
4. Maintenance:
o Indexes, backups, and statistics can be maintained at the partition
level, reducing overhead.
Types of Partitioning
1. Range Partitioning:
Data is divided based on a range of values in a column (e.g., dates, IDs).
2. List Partitioning:
Data is divided based on a list of discrete values (e.g., regions,
categories).
3. Hash Partitioning:
Data is distributed based on a hash function applied to a column (e.g.,
CustomerID).
4. Composite Partitioning:
A combination of two or more partitioning strategies (e.g., range and
hash).
Python Application: "Can you describe a situation where
you used Python for data analysis? What libraries did
you use, and how did they help you achieve the desired
outcome?"

To automate the process of segregating and splitting the claim PDF document
received from Cipla, you'll need to extract the customer names and their
corresponding details from the document, group the data by customer, and then
create individual PDFs for each customer. Below is the code that can help you
achieve this, using Python libraries like PyPDF2 (for PDF manipulation), pdfminer
or PyMuPDF (for extracting text), and re (for pattern matching).
Assumptions:
1. The claim PDF document has a structured format where customer details
(name, claim amount, SKU, etc.) are mentioned in a predictable way.
2. Customer details are followed by their claims in the same page, and you
will split based on customer names or IDs.
Steps Involved:
1. Extract the text from the PDF to identify customer names and claims.
2. Split the document based on customer names.
3. Create individual PDFs for each customer.
Explanation of the Code:
1. Text and Position Extraction:
o The extract_customer_data_from_page function uses PyMuPDF’s
search_for method to find instances of the customer’s name in the
text. It then extracts text surrounding that position using
get_text("text", clip=rect).
2. Filtering Data for Each Customer:
o For each page, the program extracts the relevant customer’s data
and stores it. If no data is found for a customer on a page, that page
is skipped for that customer.
3. Rebuilding the Page:
o For each customer, a new PDF is created. The text corresponding to
that customer’s claim is inserted on a new page using insert_text.
You can adjust the position and formatting as needed.
4. Saving the PDF:
o After processing all pages for a customer, the resulting PDF is saved
with the customer’s name.
Considerations:
 Text Layout Complexity: If the PDF contains tables, images, or other
complex structures, you may need more advanced layout extraction
techniques. fitz gives you control over coordinates, so you can capture
text sections precisely.
 Text Formatting: The text formatting might need adjustment if the
original PDF contains rich formatting (e.g., bold, italics, tables). You may
need to use libraries like pdfplumber for more complex layouts.
 Performance: For large PDFs, this process can take time, as it involves
extracting and rebuilding the content of each page.
Conclusion:
This solution focuses on extracting only the relevant content for each
customer from a shared page, effectively removing other customers'
information. By using PyMuPDF, you can extract text based on its position and
reconstruct the page with just the required data for each customer, avoiding the
inclusion of other customers' claims.
Data Visualization: "When working with Power BI, how
do you ensure that dashboards are optimized for
performance while handling large data volumes?
When working with Power BI and aiming to optimize dashboards for performance
while handling large data volumes, I follow these strategies:
1. Data Modeling Optimization:
o Star Schema Design: I ensure the data model follows a star
schema, separating fact tables from dimension tables. This reduces
complexity and improves performance.
o Data Types: I use the most efficient data types for each field (e.g.,
integers instead of strings where possible) to reduce memory
usage.
2. Query Optimization:
o DirectQuery: For large datasets that cannot be loaded into
memory, I use DirectQuery mode, which allows Power BI to query
the data source directly without loading all data into memory.
o Aggregations: I create summary tables or aggregations to pre-
calculate values at higher levels, which reduces the amount of data
being processed in real-time.
3. Reducing Columns and Rows:
o Remove Unnecessary Columns: I avoid loading unnecessary
columns into the Power BI model, reducing the dataset's size and
complexity.
o Filter Data: I filter data at the source level, limiting the volume of
data loaded into Power BI (e.g., by date range, region, etc.).
4. Optimizing DAX Calculations:
o Avoid Complex Calculations: I minimize complex DAX
calculations in visuals and prefer using measures for performance.
DAX calculations are computed on the fly, which can slow down
performance.
o Use Variables: I use variables in DAX to store intermediate results
to avoid repeated calculations, improving performance.
5. Incremental Data Load:
o For large datasets, I implement incremental data refresh to only
update data that has changed, which minimizes the load time and
improves refresh performance.
6. Use of Indexes:
 o I ensure indexes are applied on primary columns in the database to
speed up query performance when using DirectQuery or imported
data.
7. Optimizing Visuals:
o Limit Visuals: I avoid using too many visuals on a single page, as
they can slow down the report. I try to keep dashboards clean and
only show the most important information.
o Simplify Visuals: I use simpler visuals (e.g., bar charts, line charts)
instead of complex ones (e.g., maps or pie charts) that require more
rendering time.
8. Use of Power Query for Data Transformation:
o I leverage Power Query to perform data transformations and
aggregations before loading data into the model, which reduces the
load on the report’s performance.

Star Schema Design: Detailed Explanation

The Star Schema is a type of data modeling technique used in data
warehousing and business intelligence. It is designed to optimize queries and
improve performance, especially for reporting and analytical tasks. The star
schema organizes data into two main types of tables:
1. Fact Tables
2. Dimension Tables
The structure of the star schema resembles a star, where the fact table is at the
center, and the dimension tables surround it.
1. Fact Table:
The fact table contains the core quantitative data of your business processes or
transactions. These are the measurements or facts that you want to analyze.
 Characteristics:
o It holds numerical data (e.g., sales revenue, quantities, costs,
etc.).
o It contains foreign keys to link to dimension tables.

o Typically contains aggregated data or transaction-level data,

depending on the level of granularity.
 Example: In a sales data model, the fact table could have data like:
o Sales Amount

o Quantity Sold

o Profit
 o Discount Percentage

These facts are related to specific transactions or events like individual sales or
customer purchases.
 Granularity: The granularity of the fact table refers to the level of detail
in the data. For example, a fact table might store data at the level of
individual transactions, daily summaries, or monthly totals, depending on
the business requirements.
2. Dimension Table:
Dimension tables contain descriptive attributes that provide context to the
facts in the fact table. These tables describe the "who, what, where, when, and
why" of the facts, helping to interpret and filter the data.
 Characteristics:
o Dimension tables typically contain textual or categorical data
such as names, dates, locations, or product descriptions.
o They provide descriptive context that makes it easier to understand
and analyze the facts.
o Each dimension table includes a primary key that uniquely
identifies each record in the table, which is linked to the fact table
through foreign keys.
 Example: In the sales model, dimension tables could include:
o Product Dimension: Contains product details like Product ID,
Product Name, Category, Brand.
o Time Dimension: Contains date-related details like Date ID, Year,
Month, Week, Day.
o Customer Dimension: Contains customer details like Customer ID,
Customer Name, Region, Age Group.
How Star Schema Works:
 The fact table stores the transactional data (e.g., total sales or quantity
sold) and uses foreign keys to link to the dimension tables, which
provide detailed context (e.g., customer details, product categories, time
of purchase).
 In a query, analysts can join the fact table with various dimension tables
to break down, filter, and aggregate the data based on attributes (e.g.,
"Total Sales by Product Category" or "Sales in Q3 2024 by Region").
Example in Action:
Let’s say you have a Sales Fact Table:
 Transaction ID
 Customer ID (Foreign Key)
  Product ID (Foreign Key)
 Date ID (Foreign Key)
 Quantity Sold
 Sales Amount
Dimension Tables:
1. Product Dimension:
o Product ID (Primary Key)

o Product Name

o Category

o Brand

2. Customer Dimension:
o Customer ID (Primary Key)

o Customer Name

o Customer Address

o Region

3. Time Dimension:
o Date ID (Primary Key)

o Date

o Month

o Quarter

o Year
Scenario - Data Challenge: "Imagine you receive a
dataset with missing and inconsistent values from
multiple sources. How would you clean, validate, and
prepare this data for analysis?"
Step 1: Understand the Data Sources and Structure
Before cleaning, it's important to understand the structure of the dataset and the
sources from which it came. For example, if data is coming from multiple
systems (e.g., CRM, ERP, or external APIs), each system may have different
formats, measurement units, or data quality levels. I would perform an initial
exploratory data analysis (EDA) to identify the inconsistencies and missing
values.
Step 2: Handle Missing Data
There are several ways to handle missing data:
 Imputation: For numerical columns, I would use techniques like mean,
median, or mode imputation based on the nature of the data. For example,
if customer age data is missing, I could impute with the median age.
 Forward/Backward Fill: For time-series data or other sequential data, I
would use techniques such as forward or backward filling (carrying the
previous or next value forward).
 Deletion: If the missing data is minimal and doesn't have a lsignificant
impact, I would consider deleting rows or columns with a high percentage
of missing values.
Step 3: Handle Inconsistent Data
 Standardization: If there are inconsistent values like different date
formats (MM/DD/YYYY vs. DD/MM/YYYY), I would standardize these to one
format. Similarly, if units of measurement differ (e.g., miles vs kilometers),
I would convert them to a consistent unit.
 Outlier Detection: I would use statistical methods (e.g., Z-score, IQR) to
detect outliers in numerical data and decide whether to remove or adjust
them. For example, in sales data, I might find values that are far outside
the expected range and treat them as errors or outliers.
Step 4: Data Validation
After cleaning, I would validate the data by:
 Cross-checking with source systems: Ensure that the data matches
expected ranges or types.
 Business Rules Validation: For example, if the dataset includes
customer purchase dates, I will check for logical anomalies like future
dates or purchases with negative amounts.
 Consistency Checks: Ensure that certain columns (e.g., total sales vs
individual sales) are consistent.
Step 5: Data Transformation
I would then transform the data as required for analysis by normalizing or scaling
it, converting categorical variables to numeric (using techniques like one-hot
encoding or label encoding), and aggregating data where needed.

Example:
In a project I worked on for OLA Electric, I was tasked with cleaning data from
sensors on electric scooters. The dataset had missing timestamps and
inconsistent sensor readings (e.g., negative charging times). I handled missing
data by replacing missing timestamps with the previous valid timestamp
(forward fill) and corrected negative readings by checking the sensor’s status
and replacing erroneous values with the median charging time. After cleaning,
the data was validated to ensure no timestamps were missing, and the charging
times were within logical limits.
Customer Segmentation
1. Understanding the Problem and Defining the Objective
 Objective: The goal is to segment customers based on their behaviour,
preferences, and usage patterns to help optimize marketing strategies,
personalized offers, and improve customer experience. For example, you
might aim to identify high-value customers, frequent riders, or price-
sensitive users.
 Business Impact: Customer segments can help tailor promotions,
discounts, and communication strategies. For example, targeting high-
frequency users with loyalty offers or promoting discounts to occasional
riders.
2. Data Collection and Preprocessing
 Data Sources: The first step is gathering data from Ola’s various systems
such as ride history, user demographics (age, location, income, etc.), app
usage patterns, payment methods, ride frequency, time-of-day
preferences, and feedback scores.
 Data Cleaning: This includes handling missing values, outliers, and
normalizing data. For example, ride frequency might need to be scaled
because some users take rides more frequently than others.
 Feature Engineering: You would create features that help in
segmentation. For example:
o Recency, Frequency, Monetary (RFM): How recently a customer
used the service, how often they use it, and how much money they
spend.
o Average trip distance: This can help segment customers based
on their trip preferences.
o Time of booking: To understand if a customer prefers rides during
peak or off-peak hours.
o Location: Frequent pickup/drop-off areas.

3. Choosing the Machine Learning Model

The selection of the machine learning model for customer segmentation depends
on the nature of the data and the specific business goals. Common ML models
for segmentation include:
 Clustering (Unsupervised Learning): Since customer segmentation is
an unsupervised problem (there are no predefined labels), clustering
techniques are commonly used.
o K-Means Clustering: This is one of the most popular techniques
for segmentation. K-Means algorithm groups customers into clusters
based on their features, trying to minimize the distance between
customers within the same cluster.
 o DBSCAN (Density-Based Spatial Clustering of Applications
with Noise): This is used for identifying clusters of varying shapes
and can handle outliers better than K-Means.
o Hierarchical Clustering: This method builds a tree of clusters and
can be useful for understanding the hierarchy of customer
segments.
 Model Selection:
o K-Means Clustering: If you are looking for a simple and efficient
model that performs well on large datasets, K-Means is a good
choice. You would start by determining the optimal number of
clusters (K) using the elbow method or silhouette analysis.
o DBSCAN: If your data is noisy and has irregular clusters (e.g.,
customers with different ride patterns), DBSCAN might be preferred
because it doesn’t require specifying the number of clusters upfront
and can identify outliers.
 Model Training: Once the model is selected, you would train it using the
relevant features (e.g., frequency of rides, total spend, time spent per ride,
etc.). After running the model, you'll have a set of customer segments
based on the similarity of their data points.
4. Evaluating the Model
 Cluster Quality Evaluation: After running clustering, you would evaluate
the quality of the clusters using metrics like:
o Silhouette Score: A measure of how similar customers are within
their own cluster compared to other clusters. A higher silhouette
score indicates better clustering.
o Elbow Method: Used to find the optimal number of clusters (K) by
plotting the sum of squared distances from each point to its cluster
center. The "elbow" point indicates the ideal number of clusters.
5. Interpreting the Results
 Cluster Analysis: After segmentation, each cluster will represent a group
of customers with similar behaviours. You’ll analyse the characteristics of
each cluster to understand the customer profiles better.
o Example Clusters:

 High-Frequency Users: Customers who use Ola regularly,

perhaps multiple times a week. They might be identified by
high ride frequency and shorter ride distances.
 Occasional Riders: Customers who use Ola infrequently,
such as once a month or only on weekends. These segments
might show lower ride frequency but potentially higher
spending per ride.
  Price-Sensitive Users: Customers who tend to book rides
during discounts or use promo codes frequently.
 Premium Users: High-value customers who consistently
book higher-priced rides (e.g., Ola Lux), possibly showing a
higher average fare or spending pattern.
 Cluster Profiling: You would profile each cluster based on key features
(e.g., frequency, total spend, geographic location) to label each group with
a meaningful name.
o Example:

 Cluster 1: "Frequent Budget Riders"

 Cluster 2: "Premium Commuters"
 Cluster 3: "Occasional Luxury Riders"
 Cluster 4: "Discount Seekers"
6. Naming the Segments
 The names of the segments come from analysing the key traits of each
cluster. These names should reflect the group’s behaviour or
characteristics.
o Frequent Riders: If the segment has high trip frequency but
moderate spending, it could be named “Frequent Budget Riders.”
o High-Spending Users: If another segment is characterized by high
spending per ride, it could be labelled “Premium Users.”
 The names should help in understanding the value of the group and its
potential for targeted marketing campaigns.
7. Deploying and Using the Model
 Customer Targeting: Once the segmentation model is ready, it can be
deployed in Ola’s marketing systems. For example, "Frequent Budget
Riders" can be targeted with specific discounts, while "Premium Users"
might get loyalty rewards or exclusive offers.
 Personalized Offers: Based on the segment, personalized offers can be
sent through the app, email, or SMS to improve customer experience and
increase retention.
8. Iterating and Refining the Model
 Ongoing Improvements: Customer segmentation is not a one-time task.
Over time, customer behaviour may change, and new segments may
emerge. The model needs to be retrained periodically with updated data
to ensure it remains relevant.
 Feedback Loop: Continuous monitoring of the model’s effectiveness is
crucial. If certain segments are not performing well (e.g., not responding
to offers), the model might need to be adjusted or re-clustered.
LLM-Powered Driver Assistant: Built a chatbot
integrating GPS, fleet data, and vehicle telematics to
provide real-time support on charging, diagnostics,
earnings, and route optimization, enhancing driver
efficiency.
Your project involves integrating LLMs with GPS, fleet data, and vehicle
telematics to provide real-time assistance to drivers. Let's break it down into
architecture, functionality, technologies, and potential counter-
questions.

1. Project Overview
The LLM-Powered Driver Assistant is a chatbot designed to help drivers with:
 Route Optimization: Suggesting the best routes based on traffic, fuel
efficiency, and vehicle condition.
 Charging & Fuel Management: Locating nearby charging stations or
fuel pumps, considering pricing and availability.
 Vehicle Diagnostics: Alerting drivers about engine health, tire pressure,
and maintenance schedules.
 Earnings Optimization: Analysing trip history and suggesting ways to
maximize revenue (e.g., peak-hour driving, fuel efficiency tips).
 Real-time Alerts & Notifications: Providing weather warnings, road
closures, and vehicle performance issues.

2. System Architecture
The project is built on a modular architecture integrating various data sources
and LLMs for contextual responses.
High-Level Flow:
1. Input Sources:
o GPS and Fleet Data (Real-time location tracking, routes, and traffic
data)
o Vehicle Telematics (Engine health, fuel levels, speed, braking,
acceleration)
o External APIs (Weather, traffic conditions, fuel prices, earnings data)

o User Queries (Driver asks questions about route, fuel, vehicle

health, etc.)
2. Data Processing Layer:
 o ETL Pipeline (AWS Glue / Apache Spark / Kafka): Cleans and
transforms incoming data.
o LLM Integration (OpenAI GPT / Llama / Custom Fine-tuned
LLM): Processes driver queries, fetches relevant data, and
generates responses.
3. Response Generation:
o LLM interprets the query and calls relevant APIs (Google Maps,
vehicle API).
o Fuses real-time data with learned knowledge.

o Returns concise, driver-friendly responses.

4. Delivery Mechanism:
o Mobile App or In-Vehicle Assistant: Voice and text-based
interactions.
o Push Notifications & Alerts: Emergency alerts, fuel warnings,
maintenance notifications.

3. LLM Usage in the Project

LLMs play a critical role in understanding driver queries, interpreting data,
and generating responses. Below is how LLMs are used:
1. Natural Language Understanding (NLU):
o Detects intent (e.g., “Where is the nearest charging station?” vs.
“Why is my fuel consumption high?”).
o Extracts entities (location, vehicle type, fuel level, charging
station).
2. Contextual Data Fusion:
o Maps driver intent with real-time GPS, fleet, and vehicle data.

o Example: If a driver asks for "the best route to maximize fuel

efficiency", the LLM combines:
 Traffic data from Google Maps.
 Vehicle fuel efficiency patterns from telematics.
 Road conditions from external APIs.
 Historical earnings data (if applicable).
3. Response Generation & Action Execution:
o LLM generates dynamic responses based on structured data.

o Example Response:
“Your vehicle's fuel efficiency is currently 15 km/l. Based on
 the traffic on Route A, I recommend taking Route B, which
saves 12% fuel.”
4. Voice & Multimodal Capabilities:
o If integrated with a voice assistant (e.g., AWS Lex, OpenAI Whisper),
the chatbot can interact via voice commands.
6. Expected Interview Counter-Questions & Answers
Q1: How does your system handle real-time data ingestion?
 We use Kafka for streaming fleet and GPS data, ensuring low-latency
updates.
 AWS Glue or Apache Spark can be used for batch processing.
Q2: How do you fine-tune the LLM for domain-specific responses?
 Fine-tuned LLM with driver-specific data (historical trip data, vehicle
logs).
 Used embedding models to enhance context retrieval.
Q3: How does your chatbot handle incorrect or vague queries?
 Fallback mechanisms redirect users to customer support if the LLM is
uncertain.
 Autosuggestions help refine unclear queries.
Q4: How do you prevent hallucinations in LLM responses?
 Restrict LLM outputs using structured data and predefined APIs.
 Reinforcement Learning from Human Feedback (RLHF) to optimize
response quality.
Q5: How do you ensure scalability?
 Microservices architecture allows independent scaling of LLM, API, and
telematics services.
 Redis caching optimizes query response times.
7. Key Takeaways
 Real-time data fusion with LLM enhances driver assistance.
 Modular architecture ensures scalability & reliability.
 Fine-tuned LLM with APIs prevents misinformation.
 Hands-on implementation using FastAPI, LangChain, and telematics
integration.
AI-based model to predict credit default probabilities
and optimize credit card distribution, reducing defaults
by 40%
1. AI-Based Credit Default Prediction Model
Objective:
To predict the probability of credit default and optimize credit card distribution,
ultimately reducing defaults by 40%.
Steps:
1. Data Collection & Processing:
o Gathered historical customer data, transaction history, repayment
behavior, credit scores, income levels, etc.
o Performed data cleaning, handling missing values, and treating
outliers.
2. Feature Engineering:
o Engineered 100+ features using SQL, including:

 Credit utilization ratio

 Average transaction amount
 Payment delay trends
 Frequency of minimum payments
 Recent loan applications
 Customer segmentation based on spending patterns
3. Model Selection & Training:
o Implemented classification models such as Logistic Regression &
XGBoost.
o Trained the models using historical default data.

o Optimized hyperparameters using Grid Search/Random Search.

4. Model Evaluation:
o Used performance metrics like AUC-ROC, Precision-Recall, and
F1-score.
o Addressed class imbalance using SMOTE (Synthetic Minority
Over-sampling).
5. Deployment & Optimization:
o Integrated model outputs into the credit card distribution
strategy.
 o Adjusted approval criteria for high-risk customers, leading to a 40%
reduction in defaults.
2. ETL Pipeline with AWS Glue, Step Functions, S3, and Lambda
Objective:
To automate risk data processing by designing an ETL pipeline that extracts
customer credit data, transforms it, and loads it into a centralized data
warehouse, reducing manual data processing by 50%.
Step-by-Step Implementation:
Step 1: Data Extraction & Storage in S3
 Raw Data Sources:
o Banking transactions (RDBMS)

o Credit history data (API-based ingestion)

o Customer demographic & income data (S3)

 Storage:
o Raw data ingested into Amazon S3 in Parquet format for better
compression & performance.
Step 2: AWS Glue for ETL
 Why AWS Glue?
o Managed ETL Service → Scales automatically.

o Supports PySpark → Efficient for large-scale processing.

 Implementation:
o Created AWS Glue Crawler to infer schema.

o Developed Glue ETL Jobs (PySpark) for:

 Cleaning missing values.

 Standardizing column formats.
 Joining multiple datasets.
 Aggregating customer behavior data.
Step 3: Orchestration Using AWS Step Functions
 Why Step Functions?
o Automates workflow execution.

o Handles errors & retries.

 Workflow:
1. Trigger Step Function → Checks new data arrival in S3.
 2. Runs Glue Job → Transforms & cleans data.

3. Stores output → Writes transformed data back to S3.

Step 4: AWS Lambda for Event-Driven Processing

 Lambda Triggers:
o Detects new files in S3 and triggers Glue ETL Jobs.

o Sends completion notifications via SNS (Simple Notification

Service).
3. Tableau Dashboards for Credit Default Tracking
Objective:
To create real-time dashboards for monitoring credit default rates and
model performance, improving efficiency by 60%.
Implementation:
 Key KPIs Tracked:
o Default Rate by Customer Segment.

o Credit Utilization Trends.

o Loan Approval vs. Rejection Trends.

o Feature Importance from AI Models.

 Performance Optimization:
o Data Pre-Aggregation → Reduced load time by 30%.

o Incremental Data Refresh → Avoids full data reload.

AI-Based Credit Default Prediction Model

Q1: How did you deal with data imbalance?
 Problem: In credit default prediction, the number of defaulters is much
smaller than non-defaulters, leading to class imbalance.
 Solution:
1. SMOTE (Synthetic Minority Over-sampling Technique)
 Generates synthetic samples for the minority class to balance
the dataset.
2. Class Weights in XGBoost
 Assigned higher weights to the defaulter class to ensure the
model learns from it effectively.
3. Threshold Tuning
  Instead of using a 0.5 probability threshold, I adjusted it
based on Precision-Recall trade-off to reduce False
Negatives (missed defaulters).
Q2: Why did you use XGBoost instead of Random Forest?
 XGBoost Advantages:
o Handles Missing Data Natively (Unlike Random Forest, which
requires imputation).
o Boosting Algorithm → Learns sequentially and reduces bias.

o Feature Importance Ranking → Helps in feature selection.

o Better Computational Efficiency → Parallelized execution speeds

up training.
Q3: How did you ensure model interpretability?
 Used SHAP (SHapley Additive Explanations) to explain feature
importance and understand how each variable affects predictions.
 Logistic Regression as a Baseline Model for interpretability.
 Feature Importance Analysis in XGBoost to validate critical features.
Q4: How would your model react to economic downturns or external
shocks?
 Credit default models depend on historical data, which may not fully
capture sudden economic shifts.
 Solution:
o Macroeconomic Indicators as Features → Included variables like
inflation rate, interest rate changes, and unemployment rate.
o Retraining Frequency → The model was designed to retrain
quarterly to adjust for new market conditions.
o Stress Testing → Simulated high-default scenarios to evaluate
robustness.

ETL Pipeline with AWS Glue, Step Functions, S3, and Lambda
Q5: Why did you use AWS Glue instead of Apache Spark on EMR?
 AWS Glue Benefits:
o Serverless → No need to manage clusters (vs. Spark on EMR).

o Automatic Schema Inference → Glue Crawlers detect schema

changes automatically.
o Tightly Integrated with AWS → Easy connectivity with S3,
Redshift, Athena, Step Functions.
  When would Spark on EMR be better?
o If we needed fine-grained cluster control and cost
optimization for long-running jobs.
Q6: What was the role of AWS Step Functions in your pipeline?
 Step Functions were used to orchestrate and automate the ETL
pipeline.
 Workflow Example:
o Trigger → Step Function starts when new data arrives in S3.

o Execute Glue Job → Cleans and processes data.

o Store Processed Data → Saves back to S3 or Redshift.

o Notify Completion → Sends an SNS alert.

 Why not use AWS Lambda alone?

o Step Functions handle retries, failures, and complex
dependencies more efficiently.
Q7: How does AWS Lambda enhance the ETL process?
 Event-Driven Processing:
o Lambda detects new S3 files and triggers Glue jobs instantly.

 Error Handling & Alerts:

o If Glue fails, Lambda sends alerts via SNS or CloudWatch.

 Cost-Efficiency:
o Runs only when triggered, reducing idle costs.

Q8: How did you ensure data quality and consistency?

 Implemented Data Validation Rules in Glue Jobs:
o Checked for missing values, schema mismatches, and outliers.

 Maintained a Data Quality Log:

o Used AWS CloudWatch for monitoring data transformation issues.

 Automated Schema Evolution Handling with Glue Crawlers:

o Ensured the ETL pipeline adjusted when new columns were added.

Feature Engineering with SQL & Model Implementation

Q9: How did you decide which features to include?
 Feature Selection Process:
 1. Business Understanding → Selected features relevant to credit
risk.
2. Correlation Analysis → Removed redundant variables.
3. Feature Importance from XGBoost → Used SHAP values to rank
feature impact.
4. Domain Expert Feedback → Validated features with risk analysts.
Q10: How did you ensure SQL queries were efficient?
 Query Optimization Techniques:
o Used Indexed Columns to speed up JOIN operations.

o Replaced SELECT * with specific column selection.

o Used Window Functions (OVER PARTITION BY) for aggregations

instead of subqueries.
o Partitioned Large Tables to improve read speed.

Q11: How did you handle missing values in your dataset?

 Categorical Features:
o Mode Imputation for high-frequency categories.

o "Unknown" category for unrecorded values.

 Numerical Features:
o Mean/Median Imputation for continuous variables.

o Used predictive modeling (e.g., KNN Imputer) for high-impact

missing data.

Tableau Dashboards for Credit Default Tracking

Q12: What key insights did the dashboard provide?
 Segment-Wise Default Rates: Identified high-risk customer profiles.
 Loan Approval vs. Rejection Trends: Helped adjust approval policies.
 Spending vs. Repayment Behavior: Highlighted early warning signs for
potential defaults.
Q13: How did you optimize Tableau for large datasets?
 Extract Mode Instead of Live Connection → Improved performance for
large datasets.
 Pre-Aggregated Data in SQL → Reduced computational load in Tableau.
 Incremental Data Refresh → Instead of full data reload, only new data
was added.
Q14: How did you ensure real-time updates in Tableau?
 AWS Lambda & Glue Updated Data in S3 Every 24 Hours.
 Tableau Extracts Were Refreshed on a Schedule (every 6 hours).
 Connected Tableau to Redshift Instead of S3 Directly → Faster
queries

Natural Language Processing (NLP) – Detailed

Explanation
1. What is NLP?
Natural Language Processing (NLP) is a branch of Artificial Intelligence (AI) that
helps computers understand, interpret, and generate human language. It bridges
the gap between human communication and machine understanding.
NLP is used in chatbots, search engines, speech recognition, sentiment
analysis, and more.

2. Key Terminologies in NLP

A. Basic NLP Terms
1. Tokenization – Breaking text into words (word-level) or sentences
(sentence-level).
2. Stemming – Reducing words to their root form (e.g., "running" → "run").
3. Lemmatization – Similar to stemming but returns a meaningful word
(e.g., "better" → "good").
4. Stop Words – Common words (like "the", "is", "and") that are removed as
they don’t carry much meaning.
5. Part-of-Speech (POS) Tagging – Identifying nouns, verbs, adjectives,
etc., in a sentence.
6. Named Entity Recognition (NER) – Identifying proper nouns (e.g.,
"Google" as an organization, "India" as a location).
7. Word Embeddings – Representing words in numerical form (e.g.,
Word2Vec, GloVe, FastText).
B. Advanced NLP Concepts
8. TF-IDF (Term Frequency-Inverse Document Frequency) – Measures
how important a word is in a document relative to the entire dataset.
9. Bag of Words (BoW) – Converts text into a frequency-based numerical
vector.
10.N-grams – Sequences of ‘n’ words (e.g., bigrams: "machine learning",
trigrams: "New York City").
 [Link] Mechanism – Focuses on important words in a sentence (used
in Transformers).
[Link] – Advanced deep learning models (e.g., BERT, GPT) for NLP.
[Link] Analysis – Detecting emotions in text (positive, negative,
neutral).
[Link] Summarization – Extracting key points from text (extractive &
abstractive summarization).

3. Python Libraries for NLP

A. Traditional NLP Libraries
1. NLTK (Natural Language Toolkit) – Basic NLP tasks (tokenization,
stemming, stop words, POS tagging).
2. spaCy – Faster alternative to NLTK with pre-trained models.
3. TextBlob – Simplifies sentiment analysis and text processing.
4. Gensim – Topic modeling and word embeddings (Word2Vec, LDA).
5. Scikit-learn – TF-IDF, BoW, and classical ML models for NLP.
B. Deep Learning & Transformer-Based Libraries
6. TensorFlow & PyTorch – Building deep learning NLP models.
7. Hugging Face Transformers – Pre-trained transformer models like BERT,
GPT, RoBERTa.
8. fastText – Efficient text classification and word embeddings.
SQL
"Detect duplicate transactions where the same customer made the same
payment within 1 minute."
SELECT CustCode, Invoice_No, Revenue, SALES_DATE
FROM (
SELECT *,
LEAD(SALES_DATE) OVER (PARTITION BY CustCode, Revenue ORDER BY
SALES_DATE) AS Next_Transaction,
DATEDIFF(MINUTE, SALES_DATE, LEAD(SALES_DATE) OVER (PARTITION BY
CustCode, Revenue ORDER BY SALES_DATE)) AS Time_Diff
FROM Sales
) AS t
WHERE Time_Diff BETWEEN 0 AND 1;