Case Study 1: Alex's Campaign Analytics — From Spreadsheet to Story in 2 Hours

The Situation

Vantara Systems has just completed its largest paid advertising campaign to date — a six-week push across paid search, LinkedIn advertising, email, and content marketing for a new product feature. Alex has three weeks of campaign data, a Thursday afternoon, and a presentation to the CEO on Monday morning.

The presentation needs to answer one question cleanly: was the campaign worth it?

Alex has the raw data across four separate spreadsheets: Google Ads performance, LinkedIn campaign manager exports, HubSpot email analytics, and Salesforce pipeline data showing which leads have progressed to opportunities. The spreadsheets use different date formats, different column naming conventions, and measure slightly different time periods.

In previous quarters, this analysis would have taken Alex and her coordinator a full day — standardizing the data, building pivot tables, creating charts in PowerPoint, and writing the narrative. She has two hours. She decides to try an end-to-end workflow in ChatGPT Advanced Data Analysis.

Data Preparation (20 Minutes)

Before touching the AI tool, Alex spends twenty minutes standardizing the data manually. She builds a single Excel workbook with four sheets: one per channel, all using consistent date formats and the same column names for shared metrics (date, impressions, clicks, spend, leads). She flags one data quality issue: the Salesforce export includes leads from before the campaign period — she filters those out manually.

She also writes a brief context document she will paste into her initial prompt: what the campaign was (six-week awareness and lead generation push), what success metrics matter (cost per lead, lead quality as measured by opportunity progression, and total leads relative to target), and what the key questions are.

This preparation step is not AI-assisted, and it matters. The quality of what AI produces is directly proportional to the quality of the data and context it receives.

Exploratory Analysis (25 Minutes)

Alex uploads the Excel workbook and pastes her context document with the following opening prompt:

"I've uploaded a four-tab Excel workbook with campaign performance data for a six-week B2B marketing campaign at a software company. Context: [pastes context]. Before I ask specific questions, please explore the data across all four tabs and give me: (1) the date range and completeness of each dataset, (2) any data quality issues I should know about, (3) the total spend and leads across all channels combined, and (4) the three most interesting patterns you notice before I direct the analysis."

The EDA response takes approximately ninety seconds to generate. Key outputs: - Total campaign spend: $87,400 across all channels - Total leads generated: 312 - Blended cost per lead: $280 - Date ranges are consistent across all four sheets - One notable data quality issue: LinkedIn campaign manager shows a two-day gap in impression data (days 23-24) that appears to be a tracking issue

The three "most interesting patterns" the tool identifies: email has the highest click-through rate but the third-lowest lead volume; paid search has the highest lead volume but the second-highest cost per lead; LinkedIn has the lowest cost per lead at $197 but also the lowest lead volume.

Alex spot-checks the total spend calculation: $31,200 (paid search) + $28,600 (LinkedIn) + $18,900 (email — a paid distribution service) + $8,700 (content promotion) = $87,400. Correct. She checks the cost per lead: $87,400 / 312 = $280. Correct.

Channel Efficiency Analysis (20 Minutes)

Alex runs a focused channel efficiency analysis:

"Calculate cost per lead and lead-to-opportunity conversion rate for each channel. The opportunity data is in the Salesforce tab — leads marked 'Opportunity' in the Stage column have progressed. Show me: (1) a table comparing all four channels on CPL, lead volume, and opportunity conversion rate, and (2) a scatter plot with CPL on the x-axis, opportunity conversion rate on the y-axis, and each channel labeled. Size the dots by lead volume."

The resulting table and scatter plot show a clear pattern: LinkedIn has the lowest CPL ($197) and the highest opportunity conversion rate (31%), but generated only 68 leads. Paid search generated 187 leads at $254 CPL but 19% opportunity conversion. Email generated 42 leads at $450 CPL but 24% conversion.

Alex pauses on this finding. LinkedIn appears to be the most efficient channel, but the sample size is small — 68 leads converting to opportunities generates a noisy conversion rate. She asks:

"The LinkedIn conversion rate is 31% based on 68 leads. How confident should I be in this number compared to paid search at 19% based on 187 leads? Is the difference statistically meaningful?"

The response: given the sample sizes, the difference is at the edge of statistical significance (the confidence intervals overlap), and she should describe LinkedIn as "showing stronger early conversion signals" rather than "proven to be the highest-converting channel." She notes this qualification.

Trend Analysis (15 Minutes)

"Show me week-by-week trends for leads generated by channel over the six-week campaign. Use a line chart with one line per channel, different colors, and a legend. Also show me whether there's a consistent pattern in which days of the week generate the most leads."

The weekly trend chart reveals: paid search lead volume declined in weeks 4-5 but recovered in week 6. LinkedIn was steady throughout. Email spikes correspond to send dates (weeks 1, 3, and 5). Alex recognizes the week 4-5 paid search dip — there was a budget reallocation mid-campaign that she had forgotten about. The chart makes the reallocation and its effects visible.

The day-of-week analysis shows B2B lead generation heavily concentrated Tuesday-Thursday. Fridays and weekends are near zero. "Useful for future campaign scheduling," Alex notes.

Narrative and Synthesis (15 Minutes)

"Based on everything we've analyzed, write a 300-word executive summary of campaign performance that: states the bottom line in the first sentence, covers channel efficiency with the key comparative finding, addresses lead quality (opportunity conversion), notes the LinkedIn sample size caveat, and ends with two specific recommendations for next campaign."

The AI summary is well-structured. Alex reads it carefully. She makes three changes: 1. The first sentence calls the campaign "successful." She changes it to specific: "$87,400 investment generated 312 leads at $280 CPL, with 22% progressing to opportunities — within target range for our segment." Facts first, not adjectives. 2. The LinkedIn caveat is in the fourth paragraph. She moves it to immediately after the LinkedIn finding — it should not be buried. 3. The recommendations do not include context about the budget reallocation in weeks 4-5. She adds a sentence about investigating the reallocation decision's impact before attributing the dip to performance factors.

Visualization for the Presentation (15 Minutes)

Alex asks for production-ready versions of two charts:

"Create a high-quality bar chart comparing cost per lead by channel. Use this color scheme: paid search in dark blue, LinkedIn in green, email in orange, content in purple. Include exact dollar values above each bar. Title: 'Campaign Cost Per Lead by Channel.' Make this suitable for a CEO presentation — clean, professional, large fonts."

"Create a 2x2 quadrant chart with CPL on the x-axis (lower is better — label the axis 'Lower CPL →') and opportunity conversion rate on the y-axis (higher is better — label 'Higher Conversion →'). Plot the four channels. Draw dashed lines at the median CPL and median conversion rate to create the quadrants. Label each channel's bubble with its name and lead volume in parentheses."

Both charts require minor adjustments — she asks for the font size increased and the title reformatted. The final versions are presentation-ready.

Total Time: 110 Minutes

Alex has, in under two hours, produced: - A comprehensive channel efficiency analysis with verified numbers - Two presentation-quality charts - A 300-word executive summary (edited and improved from AI output) - A clear answer to "was the campaign worth it?" (Yes — CPL is within target; LinkedIn efficiency warrants budget increase in next campaign; paid search volume is strong but should be evaluated against the budget reallocation impact)

Pre-AI workflow estimate for the same analysis: six to eight hours across two people.

What Alex Attributes the Success To

In her post-campaign documentation, Alex notes the factors she considers critical:

Data preparation before AI interaction was essential. The twenty minutes spent standardizing the spreadsheets saved the session from data quality problems that would have derailed the analysis. AI cannot fix bad data structure; it just produces wrong analysis faster.

Spot-checking numbers before building the narrative. She verified the total spend and blended CPL calculations immediately. If those were wrong, everything downstream would have been wrong. She caught no errors — but the checking took two minutes and would have caught errors if they existed.

Challenging the LinkedIn finding. Noticing that 68 leads is a small sample and asking AI to assess the statistical confidence was the most analytically important thing she did in the session. The qualification changed how she presented the finding.

Rewriting the executive summary rather than accepting it. The three changes she made to the AI-generated summary — specificity in the first sentence, repositioning the caveat, adding the budget reallocation context — reflect knowledge that only she had. The AI draft was a scaffold; the final product was hers.