Funnel Dashboard From Click To Purchase

Why this matters

A funnel dashboard shows how people move from ad click to purchase. Marketing Analysts use it to spot drop-offs, size opportunities, and prioritize fixes. Real tasks you will do:

• Compare conversion rates by channel, campaign, device, or landing page.
• Quantify the biggest leaks (for example, Add to Cart to Checkout).
• Track impact of experiments and promotions on each stage, not just purchases.
• Align paid media metrics (clicks) with onsite behavior and sales.
• Estimate revenue lift from improving a single step by X%.

Who this is for

• Marketing Analysts and Growth Marketers who report performance.
• BI/Analytics practitioners who build dashboards stakeholders use daily.

Prerequisites

• Basic SQL or familiarity with your BI tool's data model.
• Understanding of events like page_view, add_to_cart, purchase.
• Access to ad clicks and web/app analytics or event tables.

Concept explained simply

A funnel is a sequence of steps users pass through. A common e-commerce funnel:

• Click → Landing → View Product → Add to Cart → Checkout → Purchase

Core metrics:

• Stage count: number of sessions or users who reached a step.
• Stage conversion rate (step-to-step): current_step / previous_step.
• Cumulative conversion rate: current_step / first_step.
• Drop-off rate: 1 - stage conversion rate.
• Time-to-next-step: median time between steps (optional).

Mental model

Think of a funnel like a series of gates. Each gate filters some people out. Your job is to identify the gate with the biggest and most fixable loss. Then quantify impact: improving that gate’s pass-through moves more people to all later gates.

Data you need

• Ad clicks: campaign, channel, timestamp, device.
• Onsite/app events: page_view, view_product, add_to_cart, begin_checkout, purchase with session_id or user_id and timestamps.
• Order value for revenue metrics (optional but useful).

Design the dashboard

1. Define the question. Example: Which campaign has the largest drop-off from View Product to Add to Cart?
2. Fix the funnel steps. Keep 5–7 steps maximum. Map each step to an event name.
3. Choose your unit. Session-level funnel first; add user funnel later if needed.
4. Aggregate. Create one table per date and segment (e.g., campaign) with counts for each step.
5. Visualize. Use a bar funnel (horizontal bars by step) plus a trend chart of conversion rate over time.
6. Segment. Add filters: channel, campaign, device, region, landing page.
7. QA and publish. Run checks below before sharing.

Worked examples

Example 1: Compute rates and find the leak

Counts for one week (sessions): Click 50,000; Landing 40,000; View Product 24,000; Add to Cart 9,600; Checkout 7,200; Purchase 6,120.

• Stage CRs: Landing/Click = 80%; View Product/Landing = 60%; Add to Cart/View Product = 40%; Checkout/Add to Cart = 75%; Purchase/Checkout = 85%.
• Cumulative Purchase Rate: 6,120 / 50,000 = 12.24%.
• Largest drop-off: View Product → Add to Cart (only 40% pass).

Example 2: Segment by device to prioritize work

Mobile vs Desktop purchase rate from Click: 9% vs 16%. The biggest gap is Add to Cart → Checkout on Mobile. Action: simplify mobile checkout and test wallets.

Example 3: Estimate impact of a fix

If you improve Add to Cart → Checkout from 75% to 80% with 9,600 Add to Carts:

• Extra checkouts = 9,600 × (0.80 - 0.75) = 480.
• Assuming Purchase/Checkout stays 85%: extra purchases ≈ 408.
• With AOV $60: incremental revenue ≈ $24,480. Varies by country/company; treat as rough ranges.

Quality checks before launch

• Totals monotonic: each subsequent step must be ≤ previous step.
• Date filters align: same timezone and date grain across all metrics.
• Unit consistent: do not mix session and user counts on one chart.
• Attribution choice noted: e.g., last click within 7 days.
• Sampling/offline delays documented if your source has latency.

Common mistakes and self-checks

• Mixing clicks (ad platform) with sessions (analytics) without mapping. Self-check: confirm Clicks ≥ Landings and investigate large gaps.
• Counting events, not sessions/users. Self-check: deduplicate by session_id or user_id per step.
• Ignoring order. Self-check: ensure each step happened after the previous step in the same session for sequential funnels.
• Comparing different date windows across sources. Self-check: align to the same calendar and timezone.
• No segmentation. Self-check: add at least channel, campaign, device.

Exercises

Everyone can do the exercises and test for free. Progress saving is available when you are logged in.

Exercise 1: Compute conversion and identify the leak

Use this one-day dataset (sessions): Click 10,000; Landing 7,000; View Product 4,200; Add to Cart 2,100; Checkout 1,500; Purchase 1,200. Calculate:

• Stage conversion rates for each step.
• Cumulative purchase rate from Click.
• Which step has the biggest percentage drop-off?

Check your work in the solution block of Exercise 1 below.

Exercise 2: Build a session-level funnel query

Assume a table events(session_id, user_id, campaign, device, event_name, event_time). Event names: ad_click, landing, view_product, add_to_cart, begin_checkout, purchase. For the last 7 days, produce one row per campaign with sequential session counts for each step (a session is counted for a step only if it also contains all previous steps). Return: campaign, clicks, landings, product_views, add_to_carts, checkouts, purchases.

Check your solution pattern in the solution block of Exercise 2 below.

Build checklist

• Decided on steps and unit (session vs user).
• Created an aggregated table with one row per date and segment.
• Verified monotonic counts and aligned timezones.
• Added filters and a trend chart of conversion rate.
• Documented attribution assumptions on the dashboard.

Practical projects

• Project 1: Launch a channel comparison funnel. Add a bar funnel + time trend and enable Channel, Campaign, Device filters.
• Project 2: Product page A/B test funnel. Split by experiment variant and show per-step lift with confidence intervals from your experimentation tool.
• Project 3: Landing page optimization. Segment the funnel by landing page and quantify which page fixes will yield the highest purchase lift.

Learning path

• Before this: Event tracking basics; Sessionization and deduplication.
• This lesson: Click-to-purchase funnel, design, QA, and segmentation.
• Next: Cohort retention dashboards; Revenue and AOV dashboards; Marketing mix and attribution basics.

Mini challenge

ROAS looks stable, but revenue fell 8% week over week. Using your funnel, propose two hypotheses and one quick experiment to validate the top hypothesis. Write your answer in bullet points focusing on the largest step change.

Next steps

• Add anomaly alerts when any stage conversion deviates by more than 2 standard deviations week over week.
• Introduce time-to-convert metrics to detect slow checkouts.
• Layer in AOV and Revenue per Click to connect media and revenue.