Completed29659.2%
Pending7114.2%
Cancelled7515.0%
Returned5811.6%
One recorded transaction row
Transactions
Transaction_ID, Date, Customer_ID, Product_ID, Quantity, Discount_Pct, Total_Amount and Status.
← All selected work
CASE 02 / Retail · Commercial performance
What happens between an order and revenue?
A commercial review of recorded transactions, product mix and category budgets turns scattered tables into focused sales and marketing decisions.
02
RetailCo
Anonymized collaboration case
$13.82KCompleted transaction value
59.2%Completed share · 500 records
−1.0%Actual vs. budget · monthly summary
THE BUSINESS QUESTION
Which commercial issues deserve action before more demand is generated?
THE KEY FINDING
40.8% of transaction records are not completed. Investigate pending orders, cancellations and returns separately before increasing acquisition spend.
01 / THE INPUTS & THE WORK
From raw records
to a defensible answer.
Connect product, customer, transaction and budget tables to identify which operational and commercial questions need attention before increasing demand generation.
500Transaction rows · 499 unique IDs
31 + 150Products + customer records
48Monthly category budget rows
Product and customer lookup tables
Products & Customers
Product category and cost; anonymized customer identity, region and loyalty tier.
12 months × 4 categories
Monthly_Budget
Budget_Revenue, Actual_Revenue, Budget_Orders and Actual_Orders. Coverage differs from the transaction sample.
A look at the source records +
| Transaction | Product | Status | Quantity | Value (USD) |
|---|---|---|---|---|
| T-10001 | P-1021 | Pending | 1 | 15.29 |
| T-10002 | P-1003 | Completed | 4 | 139.96 |
Two source transaction records, selected columns. Customer identifiers are omitted from this public sample.
DATA JUDGMENT
What needed attention before charting.
One transaction ID occurs twice, with different details.
Handling decisionKeep both records and flag the identifier for source-owner review; do not silently deduplicate.
Why it mattersDeleting one could discard a legitimate transaction. Headline counts are row counts, not verified unique orders.
One completed transaction has no matching customer.
Handling decisionKeep its $29.99 in completed value; label its customer segment “Unmatched.”
Why it mattersAn inner join would silently remove revenue and bias customer comparisons.
The raw workbook’s customer sheet contains repeated header values.
Handling decisionUse the structured, anonymized Customers sheet in the supplied Sales_Analytics workbook.
Why it mattersCustomer segmentation needs valid keys and dimensions; this is a source choice, not a claim that the malformed sheet was repaired.
Transaction and monthly-budget totals do not reconcile.
Handling decisionAnalyze their scopes separately: $13.82K completed transaction value versus $268.83K actual revenue in the monthly summary.
Why it mattersCombining them would produce an invalid completion or budget-performance metric.
ANALYTICAL APPROACH
How the answer was built.
- 01
Map the tables and join keys
Inspect sheets and use Product_ID to attach categories. Match Customer_ID to loyalty tiers while preserving unmatched transaction rows.
OUTPUTA transaction-level analytical view with product and customer dimensions.
- 02
Define revenue before measuring it
Filter Status = Completed before summing Total_Amount. Calculate completed share as 296 ÷ 500 and AOV as completed value ÷ 296.
OUTPUT$13,816.93 completed value, 59.2% completion and $46.68 completed AOV.
- 03
Compare status, category and customer views
Group statuses as a snapshot, rank completed category value, compare tier completion with sample counts, and calculate budget variance from its own table.
OUTPUTStatus distribution, category ranking, loyalty comparisons and a separate budget variance view.
- 04
Prioritize a measurable intervention
Separate pending-order follow-up from cancellation and return investigations. Use category gaps to propose targeted tests rather than a universal discount.
OUTPUTA Sales follow-up priority and a Marketing testing plan.
Inspect the Python calculation logic +
Representative logic from the website reconstruction. This code was added for the portfolio; it is not presented as the original project team’s source code.
# Website reconstruction: preserve unmatched customers
for row in transactions:
row['category'] = products[row['Product_ID']]['Category']
customer = customers.get(row['Customer_ID'], {})
row['tier'] = customer.get('Loyalty_Tier', 'Unmatched')
completed = [r for r in transactions if r['Status'] == 'Completed']
revenue = sum(r['Total_Amount'] for r in completed)
completion_rate = len(completed) / len(transactions)
average_order_value = revenue / len(completed)ONE QUESTION, TRACED END TO END
Should the team spend more to generate orders, or investigate completion first?
Completed share = 296 ÷ 500 = 59.2%. Pending value = sum(Total_Amount where Status = Pending) = $2,907.95 across 71 rows.
40.8% of recorded rows are pending, cancelled or returned. These are different outcomes, not steps of a sequential funnel.
Review pending-order age and fulfillment / payment blockers first. Pending value is a review opportunity, not a forecast of recoverable revenue.
EXPLORE THE EVIDENCE
A closer look
at the numbers.
Switch views to explore the analysis.
Open the underlying values for more detail.
01 / ORDER SNAPSHOT
Every transaction has a status
500 recorded rows. These are mutually exclusive statuses, not stages of a sequential conversion funnel.
02 / PRODUCT MIX
Where completed value comes from
Completed transaction value only, joined to product category. USD.
View underlying values +
| Status | Recorded transactions | Value (USD) |
|---|---|---|
| Completed | 296 | 13816.93 |
| Pending | 71 | 2907.95 |
| Cancelled | 75 | 3216.69 |
| Returned | 58 | 2320.64 |
SOURCE: RetailCo_Sales_Analytics.xlsx · Transactions, Products, Customers, Monthly_Budget · 2025
03 / APPLYING AI TO THE BUSINESS PROCESS
Make the analysis
useful in the workflow.
Proposed AI extension.
The analysis is available; the AI workflow below is a design for a future pilot.
WORKFLOW DESIGN · NOT A DEPLOYED AI SYSTEM
An order-exception and campaign insight assistant
INPUT CONTRACT
Validated status / category aggregates. A future operational pilot would additionally need order age, fulfillment events and permissioned cancellation / return reasons; those fields are not in this dataset.
- 01Code identifies status exceptions and category gaps.
- 02If reason text becomes available, AI groups it into a reviewed taxonomy.
- 03The model drafts a team brief linking each recommendation to evidence.
- 04A human checks ambiguous classifications and approves any outreach or campaign test.
What this case demonstrates +
- Supplied: raw and analytical Excel workbooks, source tables and an executive dashboard.
- Built for this portfolio: joined aggregates, explicit unmatched handling, interactive customer and category views.
- AI extension: proposed exception workflow. Customer messaging, reason classification and campaign uplift have not been implemented or measured.
Project context: graduate AI/analytics club industry collaboration. The technical walkthrough documents the reproducible portfolio implementation.
RECOMMENDATIONS BY TEAM
What happens next?
Specific actions. Clear measures.
Select a department to see its priorities.
RECOMMENDED ACTION / SALES
Start with the 71 pending transactions.
Review pending-order age and payment or fulfillment blockers. Assign owners to follow-up; investigate cancellation and return reasons separately.
THE EVIDENCE
Pending transactions total $2,907.95. A further 75 records are cancelled and 58 are returned.
HOW TO MEASURE IT
Track pending-to-completed conversion, time to resolution and recovered transaction value.
Recommendations derived from the case analysis. Business outcomes have not been measured.
METHOD & CONTEXT
Show the work.
Know its limits.
Selected work completed through a graduate AI/analytics club’s industry collaborations. Company names are portfolio pseudonyms.
- 01Join transactions to products and the corrected customer sheet in the analytics workbook.
- 02Retain source rows and report completed, pending, cancelled and returned records separately.
- 03Calculate category variance from the monthly budget table, independently of the transaction sample.
- 04Compare loyalty tiers descriptively, retaining the unmatched record as its own category.
Definitions & data limitations +
There are 500 transaction rows but 499 unique transaction IDs. Both distinct rows sharing an ID are retained pending source review. One completed record ($29.99) has an unmatched customer. Statuses are a snapshot, not sequential funnel stages. Monthly budget actuals and transaction totals have different coverage and are not combined.