Diagnosing a Leaky Conversion Funnel: When Your Dashboard Shows Where But Not Why

Photo by Juni Shi on Unsplash

“Conversion is declining” is a symptom. A funnel dashboard tells you the shape of the problem. The diagnosis requires building the instrumentation that did not exist before.

A declining conversion rate is one of the most common and most frustrating analytical problems. Leadership can see the symptom clearly. The data team is asked to explain it. And the data is often insufficient to answer the question being asked.

This article documents a structured diagnostic approach to funnel analysis, drawn from implementation from my work experience where conversion from lead to funded client had declined year-over-year. The approach covers CRM funnel modeling, identification of data gaps, structural fixes to improve future diagnostic capability, and a proof-of-concept for front-end A/B testing.

TECH STACK

CRM / Source: Microsoft Dynamics 365 (T-SQL via SQL Server)

Web analytics: Google Analytics (session and event data)

BI: Power BI — Funnel visual, Decomposition Tree

Python: Matplotlib (funnel visualization), Pandas (cohort analysis)

Step 1: Map the Journey Before Measuring It

Operational systems do not naturally reflect customer journeys. A CRM stores statuses, dates, and activity records but these must be translated into a journey model before analysis can begin. The first step is defining the funnel stages as business events, not system fields.

For a small business lender, a five-stage journey model covers the full client lifecycle:

• Stage 1 — Lead Created: first contact, ideally with a captured source and region

• Stage 2 — Application Started: the client has initiated the online application

• Stage 3 — Application Submitted: the client has completed and submitted the application

• Stage 4 — Adjudication: the application is under credit review

• Stage 5 — Disbursement: the loan is approved and funds are transferred

Two additional states sit outside the forward flow: Withdrawal (client exits the pipeline) and Inactive (no activity logged beyond a defined threshold). These negative events are as important to measure as the positive ones.

Step 2: SQL Funnel Cohort Query

The following query builds a funnel view with stage-level conversion rates and time-at-stage medians, grouped by yearly cohort. Window functions calculate the percentile for median time rather than relying on AVG, which is distorted by outliers in time-to-complete data.

SQL — Funnel Cohort Analysis with Stage-Level Conversion

WITH funnel AS (
    SELECT
        l.lead_id,
        l.created_date             AS lead_date,
        l.region,
        l.channel_category,
        a.started_date             AS app_started,
        a.submitted_date           AS app_submitted,
        ln.adjudication_date,
        ln.disbursement_date,
        w.withdrawal_date,
        w.reason_category,
        -- Days between each stage (NULL = stage not reached)
        DATEDIFF(day, l.created_date,   a.submitted_date)    AS days_lead_to_app,
        DATEDIFF(day, a.submitted_date, ln.adjudication_date) AS days_app_to_adj,
        DATEDIFF(day, ln.adjudication_date, ln.disbursement_date) AS days_adj_to_disb
    FROM leads l
    LEFT JOIN applications a  ON l.lead_id        = a.lead_id
    LEFT JOIN loans        ln ON a.application_id = ln.application_id
    LEFT JOIN withdrawals  w  ON l.lead_id        = w.lead_id
)
SELECT
    YEAR(lead_date)                                   AS cohort_year,
    COUNT(*)                                          AS total_leads,
    SUM(CASE WHEN app_submitted  IS NOT NULL THEN 1 ELSE 0 END) AS reached_application,
    SUM(CASE WHEN adjudication_date IS NOT NULL THEN 1 ELSE 0 END) AS reached_adjudication,
    SUM(CASE WHEN disbursement_date IS NOT NULL THEN 1 ELSE 0 END) AS reached_disbursement,
    SUM(CASE WHEN withdrawal_date  IS NOT NULL THEN 1 ELSE 0 END) AS withdrew,
    -- Stage conversion rates
    CAST(SUM(CASE WHEN app_submitted IS NOT NULL THEN 1.0 ELSE 0 END)
        / COUNT(*) AS DECIMAL(4,3))                  AS lead_to_app_rate,
    CAST(SUM(CASE WHEN disbursement_date IS NOT NULL THEN 1.0 ELSE 0 END)
        / NULLIF(SUM(CASE WHEN app_submitted IS NOT NULL THEN 1 ELSE 0 END),0)
        AS DECIMAL(4,3))                             AS app_to_disb_rate,
    -- Median time at each stage (via PERCENTILE_CONT)
    PERCENTILE_CONT(0.5) WITHIN GROUP
        (ORDER BY days_lead_to_app)  AS median_days_lead_to_app,
    PERCENTILE_CONT(0.5) WITHIN GROUP
        (ORDER BY days_app_to_adj)   AS median_days_app_to_adj
FROM funnel
GROUP BY YEAR(lead_date)
ORDER BY cohort_year;

Step 3: Visualizing Funnel Drop-off in Python

The SQL output is then used to generate a year-over-year funnel comparison — a quick visual that makes the drop-off pattern immediately obvious in a stakeholder presentation:

Python — Year-over-Year Funnel Comparison Chart

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick

# Sample data: output from the SQL funnel query
funnel_data = {
    'Stage': ['Leads', 'Applications', 'Adjudication', 'Disbursement'],
    '2022': [1200, 480, 360, 290],
    '2023': [1350, 459, 324, 248],
}
df = pd.DataFrame(funnel_data)

# Calculate conversion rate relative to leads for each year
for yr in ['2022', '2023']:
    df[f'rate_{yr}'] = df[yr] / df[yr].iloc[0]

fig, axes = plt.subplots(1, 2, figsize=(13, 5))
colors = ['#2563EB', '#64748B']

for i, yr in enumerate(['2022', '2023']):]
    bars = axes[i].barh(df['Stage'][::-1], df[f'rate_{yr}'][::-1],
                        color=colors[i], alpha=0.85)
    axes[i].set_title(f'Funnel Conversion Rates — {yr}', fontsize=12)
    axes[i].xaxis.set_major_formatter(mtick.PercentFormatter(xmax=1))
    axes[i].set_xlim(0, 1.05)
    for bar, rate in zip(bars, df[f'rate_{yr}'][::-1]):
        axes[i].text(bar.get_width() + 0.01, bar.get_y() + bar.get_height()/2,
                     f'{rate:.0%}', va='center', fontsize=10)

plt.suptitle('Lead-to-Disbursement Funnel: 2022 vs 2023', fontsize=14, y=1.02)
plt.tight_layout()
plt.savefig('funnel_yoy.png', dpi=150, bbox_inches='tight')

Comparison of conversion for 2022 vs 2023

Step 4: Identifying the Structural Gap — Why Data Cannot Explain the Drop

The funnel visualization tells where clients are leaving the pipeline. Without knowing why, fixing the decline is guesswork. In my experience, two gaps blocked the diagnosis:

• The withdrawal reason field in the CRM was either blank or populated with generic values (“client withdrew,” “no response”) that carried no diagnostic value. These reflected what staff entered under time pressure — a description of what happened, not its cause.

• The richest touchpoint data — the actual conversations between business development managers and clients — lived in Outlook email threads, outside the CRM entirely and invisible to any query.

The Outlook gap is an upstream problem that requires a process change (CRM email synchronization) rather than an analytics fix. The withdrawal reason gap is solvable through structured data design.

Step 5: Designing the Withdrawal Reason Dropdown

The fix for the withdrawal reason gap is deliberately simple: a required CRM dropdown that business development managers complete whenever a file is marked as withdrawn. The design of the dropdown matters — too many options reduces compliance; too few reduces diagnostic value.

The categories are derived by aggregating what business development staff are already hearing anecdotally, then validated with team leads:

SQL — Withdrawal Reason Schema and Reporting

-- Add structured withdrawal reason to the CRM data model
-- (or as a supplemental table if the CRM cannot be modified)
CREATE TABLE withdrawal_reasons (
    withdrawal_id    INT IDENTITY PRIMARY KEY,
    lead_id          INT NOT NULL,
    withdrawal_date  DATE NOT NULL,
    reason_category  VARCHAR(50) NOT NULL,
    -- Controlled vocabulary (enforced by application layer)
    -- Values: 'Process Duration', 'Eligibility Unclear',
    --         'Found Alternative Funding', 'Portal Friction',
    --         'Communication Gap', 'Business Plans Changed', 'Other'
    reason_detail    VARCHAR(300),  -- Free text for "Other"
    logged_by        VARCHAR(50),
    logged_date      DATETIME DEFAULT GETDATE()
);

-- Withdrawal reason analysis once data is collected (6+ months)
SELECT
    reason_category,
    COUNT(*)                          AS withdrawal_count,
    CAST(COUNT(*) * 100.0
        / SUM(COUNT(*)) OVER ()       AS DECIMAL(5,1)) AS pct_of_total,
    AVG(DATEDIFF(day, l.created_date, w.withdrawal_date)) AS avg_days_in_pipeline
FROM withdrawal_reasons w
JOIN leads l ON w.lead_id = l.lead_id
WHERE w.withdrawal_date >= DATEADD(month, -6, GETDATE())
GROUP BY reason_category
ORDER BY withdrawal_count DESC;

The most powerful analytical intervention in this project was a dropdown field — not a model. The diagnostic question had been asked for two years. The answer required not better analysis, but better data design upstream.

Step 6: Google Analytics Integration for Front-End Visibility

CRM data captures behaviour inside the managed relationship. A portion of the application process, the public-facing website and loan portal, sits outside the CRM. Google Analytics provides a complementary data stream for this front-end visibility.

Key GA4 events to configure for a loan application funnel:

• page_view on the portal landing page — measures reach

• form_start — measures intent (client clicked into the application)

• form_submit — measures completion

• Custom event: document_upload — measures progress through a high-friction step

Cross-referencing GA4 session drop-off data with CRM stage data identifies whether attrition is front-end (portal confusion before the CRM relationship is established) or back-end (post-application communication gaps). The two data streams answer different questions and together provide a more complete diagnosis.

**Google Analytics Academy** — Free Google-certified courses covering GA4 setup, event tracking, and funnel analysis → analytics.google.com/analytics/academy

PROOF OF CONCEPT SPOTLIGHT

PROOF OF CONCEPT: LANDING PAGE A/B TEST DESIGN

A key hypothesis that emerged from the combined CRM and Google Analytics analysis was that the application start rate was affected by whether users were redirected to an external portal or kept on the organization’s landing page to begin their application.

A structured A/B test was designed as a proof of concept to validate this hypothesis. The PoC scoped and documented the experimental design but was not yet fully executed — the value of documenting it here is the methodology, which is applicable to any digital intake funnel.

Experimental design:

• Control (Group A): Users directed from the marketing landing page to the external application portal immediately on click

• Treatment (Group B): Users complete an embedded short-form on the landing page before being directed to the portal

• Primary metric: Form start rate (% of page visitors who begin the application)

• Secondary metric: Form completion rate among those who start

Guardrail metric: Application quality score (completeness at submission)

Python — A/B Test Sample Size Calculator (PoC Planning Tool)

from scipy import stats
import math

# Required sample size for the A/B test
def required_sample_size(baseline_rate, min_detectable_effect,
                          alpha=0.05, power=0.80):
    """
    baseline_rate: current form start rate (e.g., 0.12 = 12%)
    min_detectable_effect: smallest relative improvement worth detecting (e.g., 0.20 = 20%)
    """
    p1 = baseline_rate
    p2 = baseline_rate * (1 + min_detectable_effect)
    p_avg = (p1 + p2) / 2

    z_alpha = stats.norm.ppf(1 - alpha / 2)
    z_power = stats.norm.ppf(power)

    n = ((z_alpha * math.sqrt(2 * p_avg * (1 - p_avg))
           + z_power * math.sqrt(p1*(1-p1) + p2*(1-p2))) ** 2)
        / (p1 - p2) ** 2
    return math.ceil(n)

# Example: 12% baseline start rate, detect 20% relative improvement
n = required_sample_size(baseline_rate=0.12, min_detectable_effect=0.20)
print(f"Required users per variant: {n:,}")
print(f"Total required: {n*2:,}")

# At 200 daily visitors: ~{n*2/200:.0f} days to reach significance

The PoC validated that the test is feasible given current traffic volumes and established the event tracking configuration required in Google Analytics to capture the result. Full execution is planned for the next campaign cycle.

Key Takeaways

• Map the journey before querying the data. Operational systems store events; analytics requires a journey model on top of them.

• Funnel dashboards diagnose where clients exit, not why. Diagnosing why requires redesigning the data capture upstream.

• Multiple data streams (CRM + GA) answer different diagnostic questions and together provide a more complete picture than either alone.

• Sometimes the most impactful analytics intervention is a dropdown field, not a model.

• A/B test design is an analytical output — even before the test runs, the experimental design documents a testable hypothesis and a measurement plan.

**Google Analytics 4 — Event Tracking Guide** — Official GA4 guide to configuring custom events for funnel tracking → support.google.com/analytics/answer/9322688

📚 **Evan Miller — A/B Test Sample Size Calculator** — Simple, trustworthy sample size calculator for proportion-based A/B tests → evanmiller.org/ab-testing/sample-size.html

📚 **Microsoft Learn — Power BI Decomposition Tree** — Guide to the Decomposition Tree visual for root-cause drill-down analysis → learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-decomposition-tree