Marketing & Campaign Analytics

Learn campaign ROI measurement, funnel analysis, and attribution modeling using sample marketing data.

Time: 15-20 minutes Difficulty: Beginner-friendly

---

About This Tutorial

In this tutorial, you'll analyze marketing campaigns for a digital business. You'll learn to:

• Calculate ROI and performance metrics for each campaign
• Analyze conversion funnels to identify drop-off points
• Build attribution models to understand which channels drive conversions

No SQL knowledge required—just follow the prompts and see what Shadowfax creates.

---

Get the Sample Data

Download the Dataset

We've prepared realistic marketing campaign data for you:

Download Marketing Sample Data (placeholder link)

What's included:

• campaigns.csv - 50 campaigns with spend, channels, and dates
• leads.csv - 25,000 leads with source attribution
• conversions.csv - 3,500 conversions (purchases, signups) with touchpoints

Import the Data

1. Download and unzip the files
2. In your Shadowfax Workbook, click "Import Data"
3. Upload all three CSV files
4. Confirm they appear under "Sources" in your sidebar

Check the Schema

Click the Graph tab to see how tables relate:

---

Choose Your Path

Pick an analysis path based on what you want to learn. Each path is independent—do one, two, or all three!

Path A: Campaign ROI & Performance

Goal: Measure which campaigns deliver the best return on investment.

Path B: Funnel Conversion Analysis

Goal: Identify where prospects drop off in your conversion funnel.

Path C: Multi-Touch Attribution

Goal: Understand which channels deserve credit for conversions.

---

Path A: Campaign ROI & Performance

Goal: Calculate ROI for each campaign and identify your best performers.

Step 1: Calculate Campaign Performance Metrics

Prompt:

@[campaigns] @[leads] @[conversions]
For each campaign, calculate:
- Total spend
- Leads generated
- Conversions generated
- Revenue from conversions
- Cost per lead (CPL)
- Cost per acquisition (CPA)
- ROI (%)

What the AI creates:

• View name: campaign_performance (Classification: Combine + Aggregate + Calculate Columns)
• Joins all three tables
• Calculates performance metrics per campaign

Sample data:

campaign_name	spend	leads	conversions	revenue	cpl	cpa	roi_pct
Google Search - Brand	$8,500	425	89	$44,500	$20	$95.51	423%
Facebook Prospecting	$12,200	980	52	$26,000	$12.45	$234.62	113%
LinkedIn Retargeting	$5,800	145	38	$38,000	$40	$152.63	555%
...	...	...	...	...	...	...	...

Step 2: Rank Campaigns

Prompt:

@[campaign_performance]
Rank campaigns by ROI and add quartile classifications:
- Top performers (top 25%)
- Good performers (25-50%)
- Average performers (50-75%)
- Underperformers (bottom 25%)

What the AI creates:

• View name: campaign_rankings (Classification: Calculate Groupwise)
• Uses NTILE window function for quartiles
• Adds rank and performance tier

Step 3: Visualize ROI by Campaign

Prompt:

/visualize @[campaign_rankings]
Create a horizontal bar chart showing ROI by campaign.
Color code by performance tier:
- Top performers: green
- Good: blue
- Average: yellow
- Underperformers: red
Sort by ROI descending.

Insights you might discover:

• LinkedIn Retargeting has 555% ROI but small volume (underinvested?)
• Facebook Prospecting has high volume but only 113% ROI (optimization opportunity?)
• 3 campaigns have negative ROI (pause or fix immediately)

Step 4: Analyze Performance by Channel

Prompt:

@[campaign_performance]
Group campaigns by channel and calculate:
- Total spend per channel
- Total revenue per channel
- Average ROI per channel
- Number of campaigns per channel

What the AI creates:

• View name: channel_performance (Classification: Aggregate)
• Groups by channel
• Aggregates metrics across campaigns

Sample insights:

channel	spend	revenue	avg_roi	campaign_count
Search	$24,300	$98,500	305%	12
Social	$45,200	$112,800	149%	22
Email	$8,900	$52,100	485%	8
Display	$18,700	$22,400	20%	8

Key insight: Email has the highest average ROI (485%) but lowest spend—opportunity to scale!

Step 5: Create Performance Dashboard

Prompt:

/visualize @[channel_performance]
Create a scatter plot with:
- X-axis: Total spend
- Y-axis: Average ROI
- Bubble size: Revenue generated
- Label each channel
- Add quadrant lines at median spend and median ROI

Quadrant analysis:

• High spend, high ROI: Scale these (Search)
• Low spend, high ROI: Invest more (Email)
• High spend, low ROI: Fix or cut (Display)
• Low spend, low ROI: Test or pause

What You Learned

Multi-table joins (campaigns + leads + conversions) Performance metrics (CPL, CPA, ROI calculations) Ranking and classification (NTILE quartiles) Channel aggregation Scatter plot visualizations (multi-dimensional analysis)

Next: Try Path B: Funnel Analysis!

---

Path B: Funnel Conversion Analysis

Goal: Understand where prospects drop off in your conversion funnel and optimize conversion rates.

What is Funnel Analysis?

A conversion funnel tracks the customer journey through stages:

1. Awareness: Ad impression or website visit
2. Interest: Lead captured (form submission, signup)
3. Consideration: Engaged with product (demo, trial)
4. Conversion: Purchased or subscribed

We analyze drop-off rates at each stage.

Step 1: Define Funnel Stages

Prompt:

@[leads] @[conversions]
For each lead, determine which funnel stages they reached:
- Stage 1 (Awareness): All leads start here
- Stage 2 (Engaged): Opened email or clicked ad
- Stage 3 (Qualified): Requested demo or started trial
- Stage 4 (Converted): Made a purchase

What the AI creates:

• View name: lead_funnel_stages (Classification: Combine + Calculate Columns)
• Joins leads with conversion events
• Flags which stages each lead reached

Step 2: Calculate Funnel Metrics

Prompt:

@[lead_funnel_stages]
Calculate for the entire funnel:
- Count at each stage
- Conversion rate to next stage (%)
- Drop-off rate at each stage (%)

What the AI creates:

• View name: funnel_metrics (Classification: Aggregate + Calculate Columns)
• Counts leads at each stage
• Calculates stage-to-stage conversion rates

Sample data:

stage	count	conversion_to_next	drop_off_rate
Awareness	25,000	42%	58%
Engaged	10,500	31%	69%
Qualified	3,255	89%	11%
Converted	2,897	-	-

Major insight: Biggest drop-off is Awareness → Engaged (58% drop). Focus optimization here!

Step 3: Visualize the Funnel

Prompt:

/visualize @[funnel_metrics]
Create a funnel chart showing:
- Each stage with count
- Width represents count (narrowing funnel)
- Display conversion rates between stages
- Color gradient from blue to green

Step 4: Funnel Performance by Channel

Now let's see which channels have the best funnel conversion.

Prompt:

@[lead_funnel_stages] @[campaigns]
Calculate funnel metrics separately for each channel:
- Awareness to Engaged rate by channel
- Engaged to Qualified rate by channel
- Qualified to Converted rate by channel

What the AI creates:

• View name: funnel_by_channel (Classification: Combine + Aggregate)
• Joins with campaigns to get channel
• Calculates conversion rates per channel per stage

Sample insights:

channel	awareness_to_engaged	engaged_to_qualified	qualified_to_converted
Search	58%	45%	92%
Social	35%	28%	85%
Email	72%	62%	91%
Display	18%	15%	87%

Key finding: Email leads convert better at every stage! Display has lowest engagement.

Step 5: Compare Funnels Side-by-Side

Prompt:

/visualize @[funnel_by_channel]
Create multiple funnel charts side-by-side, one for each channel,
so we can compare funnel shapes visually

Bonus: Identify High Drop-Off Segments

Prompt:

@[lead_funnel_stages] @[leads]
Find segments with abnormally high drop-off at the Awareness → Engaged stage.
Break down by:
- Lead source
- Industry (if available)
- Geographic region
- Time period

Show segments where drop-off exceeds 70%

What the AI creates:

• View name: high_dropoff_segments (Classification: Combine + Aggregate + Select Filter)
• Joins with lead attributes
• Filters to high drop-off segments

Actionable insight: "Leads from Facebook in Q1 from Healthcare industry had 82% drop-off—messaging mismatch?"

What You Learned

Funnel stage definition (sequential progression tracking) Stage-to-stage conversion rates Funnel visualizations Channel-specific funnels (comparative analysis) Drop-off segmentation (finding problem areas)

Next: Try Path C: Attribution Modeling!

---

Path C: Multi-Touch Attribution

Goal: Understand which marketing touchpoints deserve credit for conversions when customers interact with multiple channels.

What is Attribution?

Most customers don't convert on their first touchpoint. They might:

1. See a Facebook ad (touchpoint 1)
2. Click a Google search ad (touchpoint 2)
3. Receive an email (touchpoint 3)
4. Finally convert (touchpoint 4)

Attribution modeling answers: "How much credit does each channel get for the conversion?"

Step 1: Map Customer Journeys

Prompt:

@[leads] @[conversions]
For each converted customer, show their complete journey:
- List all touchpoints chronologically
- Show days between touchpoints
- Show which touchpoint resulted in conversion

What the AI creates:

• View name: customer_journeys (Classification: Combine + Calculate Columns + Reshape)
• Joins leads with all their touchpoint events
• Creates journey paths per customer

Sample journey:

customer_id	touchpoint_1	touchpoint_2	touchpoint_3	final_touchpoint	days_to_convert
C1001	Facebook Ad	Google Search	Email	Direct	14
C1002	Email	(none)	(none)	Email	0
C1003	Display Ad	LinkedIn	Google	Email	28

Step 2: Last-Touch Attribution

Prompt:

@[customer_journeys]
Attribute 100% of conversion credit to the last touchpoint before conversion.
Sum total conversions and revenue by channel using last-touch attribution.

What the AI creates:

• View name: attribution_last_touch (Classification: Aggregate)
• Credits final touchpoint
• Sums conversions and revenue per channel

Last-touch results:

channel	conversions	revenue	avg_revenue_per_conversion
Email	1,245	$622,500	$500
Direct	892	$535,200	$600
Google Search	520	$364,000	$700
Facebook	240	$144,000	$600

Step 3: First-Touch Attribution

Prompt:

@[customer_journeys]
Attribute 100% of conversion credit to the first touchpoint in the journey.
This shows which channels are best at creating initial awareness.

What the AI creates:

• View name: attribution_first_touch (Classification: Aggregate)
• Credits first touchpoint
• Sums conversions and revenue per channel

First-touch results:

channel	conversions	revenue
Facebook	1,102	$661,200
Google Search	845	$507,000
Email	625	$375,000
Display	325	$162,500

Interesting! Facebook gets much more credit in first-touch (awareness driver) vs last-touch (closer).

Step 4: Linear Attribution

Prompt:

@[customer_journeys]
Use linear attribution: split credit equally among all touchpoints in the journey.
If a customer had 3 touchpoints, each gets 33.3% credit.

What the AI creates:

• View name: attribution_linear (Classification: Calculate Columns + Aggregate)
• Calculates fractional credit per touchpoint
• Distributes conversions and revenue proportionally

Linear results:

channel	conversions	revenue
Email	892.5	$535,500
Google Search	745.2	$447,120
Facebook	678.8	$407,280
Direct	445.3	$267,180

Step 5: Compare Attribution Models

Prompt:

Combine all three attribution views side-by-side so we can compare
how each channel's credit changes across models.

What the AI creates:

• View name: attribution_comparison (Classification: Combine)
• Joins all three attribution models
• Shows credit per channel per model

Step 6: Visualize Attribution Differences

Prompt:

/visualize @[attribution_comparison]
Create a grouped bar chart with:
- X-axis: Channel
- Y-axis: Revenue attributed
- Three bars per channel: First-Touch, Last-Touch, Linear
- Different color per attribution model

Strategic insights:

• Facebook: Strong first-touch, weak last-touch → Great for awareness, not closing
• Email: Strong last-touch, weak first-touch → Great for nurturing and closing
• Google Search: Balanced across models → Consistent performer throughout journey

Recommended strategy: Allocate budget based on linear attribution (most fair), but recognize role differences.

Bonus: Use AI to Recommend Budget Allocation

Prompt:

@[attribution_comparison] @[campaign_performance]
Create an AI Table that analyzes each channel's attribution profile
and current spend, then recommends:
- Optimal budget allocation (%)
- Strategic role (Awareness / Nurture / Closer)
- Specific optimization recommendations

What the AI creates:

• AI Table: budget_recommendations_ai
• Input: attribution data + current spend
• AI outputs: recommended_budget_pct, strategic_role, optimization_advice

After refreshing the AI Table:

channel	current_spend_pct	recommended_budget_pct	strategic_role	optimization_advice
Facebook	28%	25%	Awareness Driver	"Strong at top-of-funnel. Reduce spend slightly and focus on audience targeting to improve mid-funnel performance."
Email	12%	22%	Closer	"Exceptional at closing. Increase investment in email nurture sequences and retargeting campaigns."
Google Search	35%	30%	Full-Funnel	"Consistent across journey. Maintain strong presence but optimize bidding for efficiency gains."
Display	25%	8%	Underperforming	"Poor performance across all attribution models. Dramatically reduce spend or test new creative approaches."

What You Learned

Customer journey mapping (multi-touchpoint tracking) Attribution models (first-touch, last-touch, linear) Model comparison (understanding credit distribution) Strategic channel roles (awareness vs nurture vs closing) AI-powered recommendations (AI Tables for optimization advice)

---

Putting It All Together

You've now mastered three essential marketing analytics workflows:

• Campaign ROI → Know what's profitable
• Funnel analysis → Know where to optimize
• Attribution → Know which channels deserve credit

Create a Marketing Performance Dashboard

Prompt:

Create a comprehensive marketing dashboard with:
- Top campaigns by ROI
- Overall funnel visualization
- Attribution comparison by channel
- Recommended budget reallocations

---

What's Next?

Explore More

Try these follow-up analyses:

Campaign Optimization:

• How does creative type (video vs image) affect performance?
• What's the relationship between ad frequency and conversion rate?
• Which messaging angles have the best ROI?

Funnel Improvements:

• What content or offers improve Awareness → Engaged conversion?
• How does time-to-engagement affect ultimate conversion rate?
• What segments have the shortest time-to-convert?

Advanced Attribution:

• Build a time-decay attribution model (recent touches get more credit)
• Implement position-based attribution (40% first, 40% last, 20% middle)
• Use AI to create a data-driven attribution model unique to your business

Apply to Your Data

Import your own marketing data and:

1. Map your tools' data to these structures (campaigns, leads, conversions)
2. Adapt attribution logic to your customer journey stages
3. Customize funnel stages to match your sales process

Learn More

• Prompt Engineering Guide - Advanced techniques
• Use Cases - More examples
• FAQ - Common questions

---

Quick Reference: Prompts Used

Goal	Prompt Pattern
Campaign ROI	@[campaigns/leads/conversions] Calculate spend, leads, conversions, revenue, CPL, CPA, ROI
Channel aggregation	@[campaigns] Group by channel and calculate totals
Funnel definition	@[leads] For each lead, flag which stages they reached
Funnel metrics	@[stages] Count at each stage, calculate conversion and drop-off rates
Customer journey	@[leads/conversions] Show all touchpoints chronologically per customer
Attribution model	@[journeys] Attribute credit to [first/last/all] touchpoint(s)
Model comparison	Combine attribution views side-by-side

---

Congratulations! You've completed the marketing analytics tutorial. Ready for another domain?

• E-commerce & Sales Analytics →
• SaaS & Subscription Analytics →
• Finance & Operations Analytics →