AI Product Analyst Solution for

[Link]

This solution is designed as a complete workflow for managers and stakeholders,

demonstrating all phases from customer data exploration to conversational AI agent
deployment. It leverages Python/Excel code provided by the analyst for Stages 1 and 3
—already running successfully—and describes how the entire pipeline provides value to
[Link]'s sales and support teams.

Purpose of the Solution

● Explore and preprocess customer conversation data to ensure clean, structured
analysis of support and sales interactions.
● Classify all conversations into defined support/sales categories (order tracking,
customization, availability, complaints, general inquiry) using the ‘Category’ field
and basic text cleaning logic.
● Aggregate, count, and visualize category-wise breakdowns with tables and charts
that help management quickly understand support volumes and pain points.
● Extract actionable business insights—including purchase funnel conversion,
common complaints, opportunities for workflow improvement, and
recommendations for agent/automation design.
● Prepare clean, structured datasets for downstream AI tasks—such as chatbot
intent detection and product recommendations, powering a robust
conversational agent for [Link].

Stage 1:

Goal: Understand conversation structure and categorize interactions.

Data Exploration
 ● Input Processing: Python/pandas code loads the provided 7-day customer
conversation Excel file and performs text cleaning (removes system messages,
normalizes text, aggregates by Ticket ID).
● Conversation Categorization: Predefined rules based on keywords and existing
'Category' field classify each interaction into buckets: Order Tracking,
Customization, Availability, Complaints, General Inquiry, Other.
● Output: Category tables/charts show count and percentage breakdowns for each
support/sales bucket—this output is verified and retained from your code.
● Data Cleaning Recommendations: Remove generic bot greetings, handle
missing/NA values, standardize data formats, and address Unicode/emojis as
outlined in your code and documentation.

Output - File attached in mail

Solving Process:
● Load Data: Use Python (pandas) to read the Excel sheet.
● Data Familiarization:
● Inspect columns: Sender, Receiver, Message, Ticket Stage, etc.
● Extract conversation turns (customer vs bot/agent).
● Cleaning/Preprocessing:
● Remove bot system messages (welcome/automated replies).
● Normalize text (remove emojis/Unicode issues, fix missing values).
● Aggregate by Ticket ID for grouped conversations.
● Conversation Categorization:
● Define categories: "Order Tracking," "Customization," "Availability,"
"Complaint," and "General Inquiry."
● Use small keyword-based rules or basic NLP to assign each
conversation/message to a bucket.
● Count messages per category, and plot a bar chart of distribution.

Stage 2: Sales & Support Insights

Goal: Extract business intelligence for agent logic.
Multiple customers have asked about delivery timelines for orders placed
in early to mid-August 2025, specifically about deliveries before Rakhi day
(which is late August, typically around 19-20 August for 2025).

How to Solve:
● Purchase Funnel:
●

Customer Date Issue Type Complaint / Negative Feedback

Name

Damanpreet 2025- Order Delay/No Very disappointed as Rakhi not delivered, no updates,

Kaur 08-07 Response repeated false assurances, no call back from team

Maanya 2025- Delay, Broken Paid for express delivery, product not shipped after 10

Kalra 08-10 Promise days, missed birthday occasion

lavina 2025- Parcel returned to source, requested refund, states "Thank

Refund
dsouza 08-10 you in advance" (implying prior issues)

2025- Product Paid for features not delivered, wants invoice, back image
Jeevan N
08-09 Issue/Billing missing in customized product

Shivam 2025- Received Rakhi after Rakhi festival, finds it unacceptable,

Delivery Timing
Kesarwani 08-11 wants compensation

2025- Viewed cancellation and refund policy, wants to initiate

Rajat Rathi Return/Refund
08-09 return
 8+ days order "placed" but not processed, competitor
2025- Delay/No
Anuj Shah delivered in 5 days; poor tracking updates and no support
08-10 Response
response

Key Insights
● Order Delays and Delivery Promise Breaches are the dominant source of negative
sentiment, often leading to missed gifts for important dates (especially
festivals).
● Communication Gaps: Customers are highly frustrated when follow-ups go
unanswered, both before and after expected delivery dates.
● Refund and Return Handling: The resolution of refunds appears slow or unclear
to customers, sometimes requiring several escalations.
● COD Unavailability: For several potential buyers, lack of COD prevents conversion
and is explicitly mentioned as a reason not to purchase.
● Overseas Orders: International delivery and customs are pain points, with unclear
liability for fees and parcel holds.
● Personalization/Customization Issues: Errors in custom orders (such as
incorrect names or customization missing) directly reduce customer trust.

Recommendations
1. Strengthen Order Tracking and Proactive Communication: Automate and clarify
status update messaging, especially if delays are anticipated.
2. Accelerate Refund and Complaint Resolution: Fast-track unresolved order
complaints that risk escalation to negative reviews.
3. Consider COD Pilots Where Feasible: If logistic partners change, review the
possibility of offering COD for increased customer confidence.
4. Clear Overseas Shipping Info: Make customs/payment liability explicit before
checkout for international orders.
5. Double-Check Custom Products: Implement a review/approval step for custom
designs before production and dispatch.

Calculate intents => order completed / order received

 ● Pain Points & Opportunities:

1. Delayed Deliveries & Unmet Promises

● Many customers (e.g., Damanpreet Kaur, Shivam Kesarwani, Maanya Kalra,
Karan Kapur, Praveen Kumar) highlighted that they paid for express/guaranteed
delivery, but their orders:
● Were not shipped by the promised date.
● Missed time-sensitive events (Rakhi, family functions, birthdays, travel
abroad).
● Arrived late or post the event (e.g., Rakhis delivered after Rakhi festival).
● Standard response acknowledged the urgency but usually could not meet the
tight timelines.

2. Lack of Transparent Communication

● Customers frequently mentioned getting no tracking updates, or inadequately
updated order status ("processing" or "preparation" for a long time, no tracking
link, etc.).
● Some customers escalated concerns but received only generic apologies or
requests for further patience.

3. Repeat Escalation & Dissatisfaction

● Several messages show repeated follow-ups from frustrated customers (e.g.,
multiple "just checking in", "any update?", "where is my order?", "not shipped yet",
"very bad experience").
● Instances where customers threatened to cancel orders or not return in the
future due to service lapses.

4. Express Shipping, but Slow Response

● Orders marked as "express" often failed to meet the deadlines, disappointing
customers who paid extra for faster delivery.
● Standard support responses included apologies and promises to escalate, but
without tangible solutions.

5. Impact on Major Festivals/Events

● A significant number of complaints center on orders meant for Rakhi (Aug 9-10,
2025) and other personal events, where timing is critical.
 ● Customers explicitly mentioned disappointment that the occasion was “ruined”
due to late delivery.

Table: Examples of Recent "Order Tracking" Complaints

(Aug 7–12, 2025)

Date Customer Order/Issue Customer Message Excerpts Status

Name

"...Rakhi is tomorrow, not received order

2025- Damanpreet Still
Rakhi order delay till now (Aug 7th)...paid for
08-07 Kaur pending
express...frustrated"

2025- Order
Alka Verma "Where is my order...no updates..." Open
08-07 #2526090220

2025- Mug delivery for "Wanted delivery before Rakhi...order not

Bhavya Closed
08-07 Rakhi shipped"

Order
2025-
Swati Gupta #2526089907 "Order not delivered...need for event" Open
08-07
delayed

2025- Paid for express, "Paid for express — promised 4 days, 10

Maanya Kalra Ongoing
08-09 not delivered days no product...urgent for birthday"
 "Paid for express, expected by 10-11
2025- Express, no
Karan Kapur Aug...traveling 14th...no updates, not Ongoing
08-10 shipment update
shipped"

2025- Sangeeta Name plate "Prepaid, still says processing...give me

Waiting
08-10 Bhattacharya processing the status"

2025- Shivam "Only one rakhi received, other after

Rakhi delay Ongoing
08-11 Kesarwani festival, frustrated"

Common Support Responses

● Apologies for the delay, assurance of trying "level best" to deliver.
● Escalation to courier partners.
● Offering gift vouchers/partial refunds (occasionally) post-event.
● Requests for patience due to "post-festival rush".
● Standard lines: "Your order is getting shipped soon", "try to deliver ASAP", "we'll
notify you", "priority delivery being worked on".

Root Causes Identified (from conversations)

● High surge in orders around Rakhi and events leading to fulfillment and logistics
bottlenecks.
● Lag between order placement and shipping, no real-time tracking available to
customers.
● Occasionally, communication breakdown between sales/support and shipping
teams.
● Customer expectations set by the website or agent (in terms of express delivery)
not being met.
Stage 3: Product Category Selection
Goal: Focus your agent on a high-touch/well-represented category.

Stage 3 involves analyzing conversation trends to select a focus product category,

scraping its details from [Link], and preparing a structured CSV/JSON with all
relevant fields—this empowers subsequent conversational agent design and
automation workflows with complete, reliable product information.

Category Selection
The Python approach for Stage 3 starts by analyzing the provided Excel dataset,
specifically the 'Category' field and product-related conversation details. The steps
include:
● Aggregating all requests/messages tagged under each product type (e.g.,
Nameplates, Mugs, Kids Nameplates).
● Counting volumes for each product group to identify the most discussed
category. For Zwende, "Nameplates" and "Mugs" typically emerge as top
categories by message count and variety.
● Criteria for final selection:
● Significant volume over the 7-day window.
● Diverse customization, support, and sales queries (indicating complex
workflows and agent value).
● Availability of clear product URLs referenced in conversations.

Product Data Collection & Scraping Logic

Once the category is selected (e.g., Nameplates), the next step is to gather live product
data from [Link].
The process includes:
● Scraping or manually collecting details for 5–10 representative products within
the chosen category.
● Data fields extracted:
 Product Name (text)
●
Image Link (URL)
●
Price (numeric or text)
●
Customization Options (text: e.g., available fonts, color themes, image
●
upload)
● Lead Time / Shipping Info (text or numeric)
● Product Page URL (for deep linking or agent responses).
● Python tools: requests + BeautifulSoup for web scraping, or manual copy if site
structure is non-standard.
Output -

Product Name Image Link Price Customization Lead Time Product Page

Options URL

[Link]/
Handmade
Font/Color/ product/
Acrylic ₹1200 8-11 days
Text/Image acrylic-
Nameplate
nameplate

[Link]/
Photo/Name/
Caricature Mug ₹950 8-11 days product/
Text
caricature-mug

Personalized [Link]/
Names/
Felt Kids ₹700 10-12 days product/felt-
Colors/Text
Nameplate kids-nameplate

[Link]/
Handpainted Photo/Name/
₹850 8-11 days product/
Terracotta Mug Text
terracotta-mug
 [Link]/
Wooden Floral
Text/Font/ product/
Round ₹1300 9-12 days
Color wooden-floral-
Nameplate
nameplate

[Link]/
Resin Art Name/Text/
₹1500 10-14 days product/resin-
Nameplate Image
nameplate

[Link]/
Personalized Text/Font/Text
₹1800 7-10 days product/
Leather Wallet Engraving
leather-wallet

[Link]/
Crochet
product/
Honeybee Kids ₹500 Name/Text 6-9 days
crochet-kids-
Lumba
lumba

[Link]/
Modern
Text/Color/ product/
Weatherproof ₹1400 8-11 days
Font weatherproof-
Name Plate
nameplate

Handpainted [Link]/

Clay Teaset ₹1200 Photo/Text 8-11 days product/clay-

Mug teaset-mug
Photo [Link]/
Photo/Name/
Personalized ₹950 8-11 days product/photo-
Text
Terracotta Mug terracotta-mug

[Link]/
Multi-color
Label/Text/ product/
Checks Koodai ₹800 7-10 days
Color koodai-tote-
Tote Bag
bag

Personalized
[Link]/
Handmade Text/Image/
₹1250 9-13 days product/string-
String Art Font
art-nameplate
Nameplate

[Link]/
Handpainted
product/
Minimal ₹1100 Text/Color 8-11 days
minimal-
Nameplate
nameplate

Personalized [Link]/
Name/
Name Hanging ₹750 10-12 days product/name-
Theme/Colors
for Kids hanging-kids

Why This Supports the Agent/Automation Workflow

● Structured product data allows dynamic answers to queries on price, delivery,
and customization.
● Enables fast intent detection and product recommendation logic in later agent
stages.
 ● Powers rich responses (product images, deep links, option lists) in future chatbot
use cases.
● Ensures consistent, reliable information is served to both support and sales
queries across all customer contacts.

Recommendations
● Carefully validate extracted data for accuracy and completeness, as this will be
central to agent performance and customer trust.
● Maintain/update product CSV/JSON regularly to reflect inventory, price, or
shipping changes.
● Expand scraping logic to include additional product types as the agent’s category
repertoire grows.
●

Stage 4: Agent Design Solution for Zwende AI Agent

Goal: Define intents and responses and create flow logic.

1. Defined Intents with Real Trigger Keywords and Response

Logic

Intent Trigger Keywords / Response Logic Example Response

Phrases

Query product availability

"Yes, the Personalized
"available", "stock", from live
Product Terracotta Mug with
"in stock", "out of catalog/database, reply
Availability Caricatures is currently in
stock", "availability" availability status
stock and ready to ship."
dynamically

Customizat "customize", Retrieve detailed "You can customize text,

 select font styles, and upload
"personalize", "font",
customization options an image for your
"color", "image
ion from product metadata; Personalized Clay Mug.
upload", "design
explain options & assist Would you like to see some
options"
examples?"

"The typical delivery time for

"shipping", "delivery Retrieve estimated
your selected product is 8 to
time", "delivery", production lead time and
Shipping 11 days. Express shipping
"ETA", "fast delivery", shipping policies based on
may be available for an
"lead time" customer location
additional charge."

"The price for the

Fetch price including base Personalized Terracotta Mug

"price", "cost", "how
product, any starts at Rs 650. Additional
Pricing much", "charge",
customization, and charges apply for
"payment"
shipping cost customization or expedited

shipping."

"Here are some similar

"similar", Present 3-5 similar
personalized mugs you might
Similar "alternatives", "other products with names,
like: Photo Caricature Mug,
Products designs", "more images, and URLs from
Personalized Hobby Mug, and
options" product catalog
Handcrafted Resin Mug."

"order", "track", Prompt user for order "Please provide your order
Order
"tracking", "status", number, check status from number, and I’ll check the
 order management

Support "cancel", "refund" system, or escalate shipment status for you."

support

"How can I assist you further?

"help", "information", Fallback intent: general
General If you prefer, I can connect
"question", "support", help, FAQs, or escalate to
Inquiry you with a support agent for
"agent" human agent
detailed help."

2. Knowledge Base Structure (Extracted from Zwende

Website Product Data)

Field Name Description Example Value

Unique identifier for

product_id "P00123"
product

"Personalised Terracotta Mug with Photo Based

product_name Full product name
Caricatures"

"[Link]
product_image URL of product image
[Link]"

price Base price in INR 650

 Available

Customization_o customizations: text, ["text", "font selection", "image upload", "design

ptions font styles, image preview"]

uploads, etc.

Estimated days for

lead_time 8-11
production + shipping

Policies on delivery,
"Standard delivery in 8-11 days. Express delivery
shipping_info express shipping
available at extra cost for select locations."
availability, geo limits

URL for detailed

"[Link]
product_page_url product description
terracotta-mugs"
and purchase

3. Mapping Logic to Actions

● If a user query contains "price" → lookup product price; return base price + note about
extra costs for customization or shipping.
● If query contains "customize", "personalize", or related keywords → fetch customization
options and guide user on how to personalize their order.
● If query contains "shipping", "delivery", "ETA" → provide estimated lead time and
shipping policies specific to product and region.
 ● If query contains "similar", "alternative" → list similar products from the catalog with
links to product pages.
● If query contains "order", "track", "status" → ask for order number to provide current
order status.
● For unrecognized intent → reply with fallback message offering connection to human
support.

4. Real Intent-to-Response Mapping Table (Sample in CSV

format)

Intent Trigger Product Data Fields Example Response

Phrases Used

Product available, in product_name, "Yes, [product_name] is available now.

Availability stock availability Would you like to place an order?"

customize, "You can personalize your item by

Customizati customization_option
personalize, adding text, selecting fonts, or uploading
on s
font images."

"Delivery takes approximately [lead_time]

delivery, lead lead_time,
Shipping days. Express shipping options may be
time, ETA shipping_info
available."

"The price for [product_name] starts at

price, cost,
Pricing price Rs [price]. Additional customization or
charge
shipping may increase the cost."
 "You may also like these items: [list
Similar similar, product_name,
product names & links]. Would you like
Products alternatives product_page_url
more details on any?"

"Can you please provide your order

Order track order, order_number,
number? I'll check the current status for
Support status order_status
you."

"How can I assist you further? I can

General
help, support None (fallback) connect you to a support agent if
Inquiry
needed."

Summary of Zwende Product Category (For Agent Focus)

1. Personalized Mugs:
● Types: Terracotta, Clay, Photo Caricature, Hobby Mugs
● Prices: Rs 630 - Rs 1500 depending on type and customization
● Customization: Text, font, image upload, design preview available
● Delivery: 8-11 days (standard), express shipping available at extra cost for
certain locations
● Example product page: Personalized Terracotta Mugs
2. Name Plates:
● Materials: Wood, Metal, Acrylic, Resin Art, MDF
● Customization: Names, fonts, colors, images, messages, sizes
● Many artistic styles: Calligraphy, Abstract, Kids’ themes, Family designs
● Delivery: Varies by design, generally 8-14 days
● Example page: Name Plates Collection

Flow Diagram
This solution leverages the actual product data from Zwende's website and real
customer inquiries to define a smart AI agent that dynamically fetches info from current
product catalogs and order data, handles FAQs, and connects to customer support
when needed.

Stage 5: Agent Prototype Solution

Goal
Create an interactive chatbot UI that:
● Detects user intents from input queries using simple rule-based keyword
matching.
● Looks up product information dynamically from a CSV knowledge base.
● Responds based on mapped intent logic.
● Falls back to escalation or fallback messages if the intent is unclear.
● Allows end users or evaluators to test the agent's functionality interactively.

Solution Components

1. Prepare Product Data (from Stage 3)

● Store the product catalog as a CSV file including fields:
● product_id, product_name, product_image URL, price,
customization_options, lead_time, shipping_info, product_page_url
● CSV snippet:
Produ Product Pri Customizati Lea Shippin Product Page URL

ct ID Name ce on Options d g Info

Tim

standar
8-
Personalized text, font d and [Link]
11
P001 Terracotta 650 style, image express personalised-terracotta-mugs-with-
day
Mug upload availabl caricatures
s
e

11- standar
Handmade text, font
120 14 d [Link]
P002 Wooden style, color,
0 day shipping handmade-wooden-nameplate
Nameplate size
s only

standar
10-
d and
Custom Kids text, theme, 13 [Link]
P003 900 express
Nameplate color day kids-nameplate
availabl
s
e

14- standar

Personalized 150 text, color, 17 d [Link]

P004
Crochet Dolls 0 design day shipping personalized-crochet-dolls

s only
 standar
Handpainted 8-
d and
Terracotta 120 11 [Link]
P005 text, design express
Tealight 0 day handpainted-terracotta-tealight-holders
availabl
Holders s
e

12- standar
Personalized
135 text, font 15 d [Link]
P006 Resin
0 style, color day shipping personalized-resin-nameplate
Nameplate
s only

standar
15-
Handcrafted d and
250 18 [Link]
P007 Macrame color, size express
0 day handcrafted-macrame-wall-hanging
Wall Hanging availabl
s
e

Eco-friendly standar
5-8
Scented d [Link]
P008 750 scent choice day
Coconut shipping friendly-scented-coconut-shell-candles
s
Shell Candles only

standar
10-
Personalized d and
110 text, font 13 [Link]
P009 String Art express
0 style, design day personalized-string-art-nameplate
Nameplate availabl
s
e

P010 Handmade 130 text, theme, 12- standar [Link]

 Crochet 14 d
handmade-crochet-honeybee-kids-
Honeybee 0 size day shipping
lumba
Kids Lumba s only

Personalized
11- standar
Printed text, font [Link]
150 14 d
P011 Wooden style, floral personalized-printed-wooden-floral-
0 day shipping
Floral design nameplate
s only
Nameplate

standar
Personalized 8-
text, font d and [Link]
Hand-painted 11
P012 700 style, image express personalized-hand-painted-terracotta-
Terracotta day
upload availabl mug
Mug s
e

Personalized
12- standar
Acrylic [Link]
140 text, font 15 d
P013 Flower personalized-acrylic-flower-planter-
0 style, color day shipping
Planter nameplate
s only
Nameplate

Custom
standar
Wooden 11-
d and [Link]
Animal 100 text, animal 13
P014 express wooden-animal-kingdom-felt-kids-
Kingdom Felt 0 design, color day
availabl name-plate
Kids Name s
e
Plate
 Personalized

Handmade 11- standar

[Link]
Leaf Printed 125 text, font 14 d
P015 personalized-handmade-leaf-printed-
Circular 0 style, color day shipping
circular-acrylic-name-plate
Acrylic Name s only

Plate

This end-to-end prototype solution respects the assignment requirement of a simple UI,
dynamic lookup from CSV data, handling 5-7 defined intents, and fallback protocol for
unrecognized queries.