BigQuery Queries for E-commerce Conversion Funnel and Attribution Analysis
BigQuery queries to analyze conversion funnels, channel attribution, and browsing behavior in e-commerce.
Build conversion funnel analysis queries in BigQuery (Google Analytics 4 export) to identify drop-off points, optimize conversion rates, and attribute revenue to acquisition channels.
At a glance
Access
Free prompt
Open to copy — no account or payment needed.
Prompt objective
Build conversion funnel analysis queries in BigQuery (Google Analytics 4 export) to identify drop-off points, optimize conversion rates, and attribute revenue to acquisition channels.
Real use case
[COMPANY NAME] exports GA4 data to BigQuery and needs to understand why conversion rate dropped from 2.8% to 1.9% over the past 2 months, particularly on mobile, and which paid traffic campaigns actually generate revenue.
Customize these fields first
Replace the placeholders with your own context before you run the prompt. That usually improves the first output more than adding more instructions later.
Prompt
Write BigQuery queries to analyze the conversion funnel for [COMPANY NAME] e-commerce, using exported GA4 data. Main table: `[PROJECT].analytics_[ID].events_*` **Query 1 — Conversion Funnel by Stage:** Analyze stages: session_start -> view_item -> add_to_cart -> begin_checkout -> purchase - Volume and conversion rate between each stage - Segmented by: device_category, source/medium, country - Comparison: current period [START_DATE to END_DATE] vs prior period - Use UNNEST(event_params) to extract parameters **Query 2 — Drop-off Points by Page:** - Pages with highest exit rate after add_to_cart - Average time between add_to_cart and begin_checkout - % of users who return to cart in another session - Segmented by product category **Query 3 — Multi-Touch Revenue Attribution:** - Linear model: distributes revenue equally across all touchpoints - Time-decay model: higher weight for recent interactions - First-click vs last-click vs linear comparison - ROAS per channel with each model - Use window functions and session stitching by user_pseudo_id **Query 4 — Buyer Cohort Analysis:** - First purchase as cohort month - Repurchase month-over-month (month 1 to month 12) - Cumulative revenue per user by cohort - [MONTH] cohort vs historical average **Query 5 — Automated Alerts:** Query to identify daily anomalies: - Drop > [X]% in conversion rate vs 7-day average - Increase > [Y]% in bounce rate on key pages - Products with zero stock receiving traffic Use BigQuery Standard SQL syntax with DATE_SUB, PARSE_DATE, SAFE_DIVIDE functions. Comment each CTE.
Open directly in an AI — the text is pre-filled:
How to use this prompt
- 1Replace the key placeholders first: COMPANY NAME, PROJECT, ID, START_DATE to END_DATE.
- 2Replace any bracketed placeholders like [this] with your own context.
- 3Add extra background information when you want more tailored results.
- 4Combine multiple prompts in one conversation when you need a richer output.
- 5Save your best-performing prompts so they are easy to reuse later.
Next best step
Open the guide first, then branch only if you still need more.
A guide for technical builders choosing between prompts, coding workflows, and agent-based implementation.
If this prompt is close but not quite right, generate variants next. If the job is recurring, move into the course library after the guide.
Related prompts
View allSales analysis queries with CTEs and window functions
Writes advanced SQL queries using CTEs and window functions for sales performance analysis.
Best for
Create a set of reusable SQL queries that analyze sales by period, product, seller, and region using CTEs (Common Table Expressions) and window functions for rankings and comparisons.
Database Schema Design for Recurring Subscription Systems
Designs an optimized database schema for a subscription business with SaaS metrics.
Best for
Create a complete relational model for a recurring subscription system, including tables, indexes, constraints, and ready-to-use queries for calculating MRR, churn, and LTV.
Slow Query Optimization with EXPLAIN ANALYZE and Indexes
Diagnoses and optimizes slow SQL queries using execution plans, strategic indexes, and query rewriting.
Best for
Learn to identify SQL query performance bottlenecks using EXPLAIN ANALYZE, create efficient indexes, and rewrite queries to reduce response time.
Stored Procedures for Automatic Sales Commission Calculation
Creates stored procedures that automate commission calculations with complex tiered rules and bonuses.
Best for
Develop SQL stored procedures that calculate sales commissions considering tiered quotas, product mix bonuses, cancellation clawbacks, and team splits.
Explore other prompt categories
Move sideways into adjacent libraries when the current category is not the full answer.
Every prompt here is free. The course teaches the thinking behind them.
Copy as many prompts as you like. When you want to move from single prompts to a repeatable AI workflow, Learn AI in 30 Days walks through it, one day at a time.
Buy the course once ($15/$20 by length), or go all-access for $10/mo with a verifiable certificate.