Marketing Funnel Analysis Skill Skill

Marketing Funnel Analysis Skill

Analyze Improvado's internal marketing attribution data using the internal_analytics.biz_attribution table.

⚠️ TABLE RENAMED: Old name biz_multitouch_all_attribution_metrics_model is deprecated. Use biz_attribution instead (same schema).

When to Use This Skill

Use this skill for:

Marketing performance analysis (funnel metrics, conversion rates)
Channel ROI analysis and ranking
Quarter-over-quarter comparisons
Funnel optimization reports
Attribution analysis for Improvado's own marketing

Core Concepts

Marketing Funnel Structure

The standard funnel flow (ALWAYS use this order):

Website Users (top of funnel)
    ↓
Leads (with email filters)
    ↓
Disco Leads (Discovery Leads - with email filters)
    ↓
Opportunities (NO filter)
    ↓
SQLs (Sales Qualified Leads - NO filter)
    ↓
Closed Won (Actual revenue - NO filter)

CRITICAL RULES:

❌ Never use MQLs (deprecated metric)
✅ Email filtering ONLY on Lead and Disco Lead stages
✅ NO filtering on Opportunity, SQL, Closed Won stages

Key Data Source

Table: internal_analytics.biz_attribution

Access: Use ch internal CLI or internal.query() Python

ch internal "YOUR_SQL"

Grain Filtering (CRITICAL)

Table has pre-computed grain columns: year_grain, quarter_grain, month_grain, week_grain

Use max_event_datetime = {grain}_grain to filter data and avoid double-counting:

-- YEAR grain (for yearly reports)
sumIf(linear_weight, max_event_datetime = year_grain AND metrics_name = 'Website Users')

-- QUARTER grain (for quarterly reports)
sumIf(linear_weight, max_event_datetime = quarter_grain AND metrics_name = 'SQL')

-- MONTH grain (for monthly reports)
sumIf(linear_weight, max_event_datetime = month_grain AND metrics_name = 'Lead')

-- WEEK grain (for weekly reports)
sumIf(linear_weight, max_event_datetime = week_grain AND metrics_name = 'Disco Lead')

⚠️ ALWAYS use grain filtering - without it you get ~15-20% inflated numbers due to double-counting at period boundaries.

Grain column mapping: | Report Period | Use Column | |---------------|------------| | Yearly | year_grain | | Quarterly | quarter_grain | | Monthly | month_grain | | Weekly | week_grain |

Channel Analysis

MANDATORY Organic AI Override:

Always apply this override when grouping channels:

CASE
    WHEN medium = 'referral' AND source IN ('chatgpt.com', 'perplexity.ai', 'gemini.google.com')
    THEN 'Organic AI'
    ELSE channel_type
END AS channel

CRITICAL: Channel Ranking Rule

❌ Wrong: ORDER BY sqls DESC (count) ✅ Correct: ORDER BY sql_arr DESC (pipeline value)

Why: SQL ARR (pipeline value) is the true measure of impact. 3 SQLs worth $200K > 10 SQLs worth $100K.

Conversion Rate Calculations

Use EXACT conversion paths:

Website Users → Leads: (Leads / Website Users) × 100
Website Users → Disco Leads: (Disco Leads / Website Users) × 100 (NOT Lead → Disco!)
Disco Leads → Opportunities: (Opportunities / Disco Leads) × 100 (NOT from Users!)
Opportunities → SQLs: (SQLs / Opportunities) × 100
SQLs → Closed Won: (Closed Won / SQLs) × 100

Terminology Standards

Correct Terms ✅

SQL ARR = "pipeline value" or "SQL pipeline"
Top channel = "Top Pipeline Generator"
Organic = "zero direct cost" or "pure ROI channel"
Direct = "uncontrollable channel reflecting brand strength"

Incorrect Terms ❌

SQL ARR = "revenue" (revenue is Closed Won ARR only!)
Top channel = "Top Revenue Generator"
Recommending to "expand Direct channel" (uncontrollable!)

Common Mistakes to Avoid

❌ Wrong conversion: Opportunities from Users (should be from Disco Leads)
❌ Ranking by SQL count instead of SQL ARR
❌ Calling SQL ARR "revenue" (it's pipeline!)
❌ Recommending to scale Direct channel (uncontrollable!)
❌ Using MQLs in funnel (deprecated!)
❌ Applying email filters to SQLs/Closed Won
❌ Missing Organic AI override in channel grouping

Workflow

When user requests marketing funnel analysis:

Clarify scope: Quarter? Channels? Full funnel or specific metrics?
Choose query approach: Full funnel, channel breakdown, or pace comparison?
Load detailed reference: Use references/funnel_queries.md for SQL templates
Execute query: Use ch internal
Calculate conversion rates: Use correct conversion paths
Rank channels: By SQL ARR (not SQL count!)
Apply terminology: Pipeline (not revenue) for SQL ARR
Flag incomplete quarters: Use pace analysis for current quarter

Detailed Reference Documentation

For complete SQL query templates, metric calculations, and advanced analysis patterns, see:

references/funnel_queries.md - Contains:

Full funnel SQL templates by quarter
Channel breakdown queries
N-day pace comparison (for incomplete quarters)
Cost & efficiency metric calculations
Revenue/pipeline calculations
Email filtering patterns

Load this reference when you need specific SQL query examples or detailed metric calculation formulas.

Report Structure Checklist

Before finalizing reports:

[ ] Conversion rates calculated correctly (users→leads, users→disco, disco→opps, opps→sqls)
[ ] Channels ranked by SQL ARR, not SQL count
[ ] Organic AI override applied
[ ] Email filtering only on Lead and Disco Lead
[ ] MQLs excluded (use Opportunities)
[ ] "Pipeline" terminology (not "revenue" for SQL ARR)
[ ] Direct marked as uncontrollable
[ ] Quarter comparison includes both full and N-day pace
[ ] Spend queries use month grain
[ ] No "expand Direct channel" recommendations

Agent Skills: Marketing Funnel Analysis Skill

Install this agent skill to your local

Skill Files