HOW TO USE CHATGPT TO ANALYZE SALES DATA: AN EXCEL AND SHEETS PRACTICE LAB Seller Insider Hub | Jennie Malone Guide: https://sellerinsiderhub.com/playbooks/chatgpt-sales-analysis/ Sources checked: 2026-11-01 WORKING KIT Examples are synthetic. Replace business details and verify the live vendor settings before use. This file is a reusable worksheet, not proof of a completed vendor-account test. START WITH FILE UPLOAD. A PAID AI PLAN IS NOT A PREREQUISITE. ------------------------------------------------------------- Route What you need Cost boundary Upload CSV to ChatGPT An account with the available file and analysis tools. The exercise can use the Free allowance. Higher limits may require a paid plan. Verify in a spreadsheet Your existing Excel access or a Google Sheets account. No AI add-in is needed for the supplied formulas. Any existing software or organizational licensing still applies. Optional official add-in ChatGPT for Excel or Google Sheets, enabled for your account. Usage follows your ChatGPT plan’s current limits or credit terms. Installation does not imply unlimited analysis. ONE ROW IS AN EVENT, NOT NECESSARILY A NEW ORDER. ------------------------------------------------- Column Meaning Rule for this lab A · event_id Identifier of a sale or refund event. Deduplicate only after all fields for a repeated ID match. B · order_id Order associated with the event. A repeated order ID is not automatically a duplicate. C · event_date Date the event occurred. Use the event month, including refund dates. D · event_type sale or refund. Preserve both event types. E · order_status completed or cancelled. Cancelled rows are uncompleted drafts in this exercise. F · product_sku PAD or POUCH. Use this stable key for product grouping. G · product_name Desk pad or canvas pouch. A display label; it does not change the key. H · channel Website or marketplace. Trim surrounding spaces and convert to lowercase. I · quantity Units in a sale event. Refund rows have zero. This is units sold before returns. J · gross_sales_usd Item sales before discounts or refunds. Positive for a sale; zero for a refund. K · discounts_usd Discount amount. A positive amount to subtract once. L · refunds_usd Money refunded. A positive amount to subtract once. M · cogs_usd Item cost assigned to the event. A blank is unknown. A numeric zero is known zero. N · payment_fees_usd Recorded transaction fees. Use supplied amounts; do not invent a fee rate. O · shipping_cost_usd Seller-paid shipping cost. Use supplied amounts; customer shipping revenue is absent. PROMPT 1: INSPECT THE SOURCE ---------------------------- Inspect the attached sales-events-dirty.csv without modifying it. This is fictional September 2026 event-level sales data in USD. A row is a sale or refund event. event_id is the event key; order_id may legitimately repeat. Amounts are totals for each event, not unit prices. Refund amounts are positive deductions. Refund events do not reverse item costs, fees or shipping in this exercise. Report: 1. Filename, number of data rows, columns and date range. 2. Counts by event_type and order_status. 3. Repeated event IDs, including whether every field matches. 4. Missing costs and channel labels before normalization. 5. Questions that must be resolved before calculating contribution. Do not fill blanks, delete records or recommend business changes yet. Show the relevant event IDs and source row numbers. If code execution is unavailable, say so rather than claiming the calculations ran. PROMPT 2: APPLY THE APPROVED RULES ---------------------------------- Create a separate working dataset and a cleaning log. Apply these rules in order: 1. Remove only later exact duplicates of event_id, after confirming all fields match. Stop and report any conflicting duplicate. 2. Exclude cancelled orders. In this exercise they are uncompleted drafts, with no actual costs. 3. Trim channel whitespace and lowercase the supplied labels. Valid results are website and marketplace. Report any other value. 4. Retain every legitimate sale and refund event. Never deduplicate by order_id. 5. Keep the missing cogs_usd value unknown. Do not infer it from another sale or fill it with zero. 6. Compute net_sales_usd = gross_sales_usd - discounts_usd - refunds_usd. 7. Contribution requires known item cost, fees and shipping. Mark affected contribution results unavailable until the missing cost is confirmed. Do not sum partial contributions and label them complete. Return row counts at each stage, the excluded event IDs, changes to channel labels, remaining missing values and net sales. Export a cleaning log and the working file. Show the calculation method or code. PROMPT 3: APPLY A TRACEABLE CORRECTION -------------------------------------- Use owner-confirmation.csv to resolve the missing cost by event_id and field_name. Confirm that the key matches exactly one retained event. Record the old blank, the confirmed value, confirmation date and source in the cleaning log. Do not change any other cost. Now calculate contribution_usd = net_sales_usd - cogs_usd - payment_fees_usd - shipping_cost_usd. Return totals by channel and by channel plus product_sku, with net sales and contribution. Calculate each contribution rate as grouped contribution divided by grouped net sales; do not average row percentages. Count distinct completed sale order IDs separately from retained event rows. State the refund policy and the omitted costs. Export the cleaned CSV, summary table and calculation code. SPREADSHEET FORMULAS FOR THIS EXERCISE -------------------------------------- Clean!P2 — net sales =J2-K2-L2 Clean!Q2 — contribution, preserving an unknown cost =IF(ISNUMBER(M2),P2-M2-N2-O2,"") Clean!R2 — cost status =IF(ISNUMBER(M2),"Complete","Cost missing") Summary: net sales =SUM(Clean!P2:P24) Summary: complete contribution only =IF(COUNT(Clean!M2:M24)