How to calculate conversion window in SQL
Contents:
What conversion window is and why it matters
Picture a Monday morning where the head of growth at a Stripe-sized fintech walks over and asks why paid social ROAS dropped 18% week-over-week. You pull the dashboard and everything looks fine on a 7-day click window. You re-pull on a 28-day window and suddenly half the revenue reappears. The number did not actually drop; your attribution window was just too short for the way users actually buy. That gap between the window your ad platform reports and the window your customers actually take is the single largest source of paid-marketing confusion in analytics.
Google Ads defaults to a 30-day click window. Meta defaults to 7-day click plus 1-day view since the iOS 14 changes. If your real sales cycle is closer to six weeks, a 7-day window misses most of the revenue that the campaign actually drove, and a 28-day window misses the tail. The job of an analyst is not to pick a window because the platform suggests it. The job is to look at the data, see where the conversions actually land, and tell the marketing team where to draw the line.
Conversion window is the time between a touchpoint (an ad click, an email open, a referral visit) and the first qualifying conversion (a paid order, a trial start, a subscription). Computing it well lets you set sensible attribution windows, benchmark channels honestly, and stop arguing about whose campaign deserves credit when the answer is in the data. This post walks through the SQL end-to-end, including the per-channel breakdown that hiring managers at DoorDash, Uber, and Airbnb actually ask candidates to write on a whiteboard.
The SQL formula
The base table you need has two columns of timestamps: when the user was touched and when the user converted. From there everything is MIN(converted_at) - touched_at, but the joins and the cap are where candidates lose points. Here is the worked example on Postgres-flavored SQL. Snowflake and BigQuery have the same shape with minor function renames.
WITH touchpoint_conversion AS (
SELECT
t.user_id,
t.touched_at,
t.channel,
MIN(c.converted_at) AS first_conversion
FROM touchpoints t
LEFT JOIN conversions c
ON c.user_id = t.user_id
AND c.converted_at >= t.touched_at
AND c.converted_at < t.touched_at + INTERVAL '180 days'
GROUP BY t.user_id, t.touched_at, t.channel
)
SELECT
AVG(EXTRACT(EPOCH FROM (first_conversion - touched_at)) / 86400)
AS avg_days_to_convert,
PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM (first_conversion - touched_at)) / 86400
) AS median_days,
PERCENTILE_CONT(0.9) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM (first_conversion - touched_at)) / 86400
) AS p90_days
FROM touchpoint_conversion
WHERE first_conversion IS NOT NULL;A few details worth narrating. The 180-day cap in the join is deliberate; without it, a single user who clicked an ad two years ago and finally bought today will pull the average into the stratosphere. The cap should be longer than your suspected real window and shorter than the lifetime of the user. For a fast consumer app like a food-delivery service you can drop this to 90 days; for B2B SaaS sold to enterprises, 180 days is closer to honest.
The LEFT JOIN matters because not every touch converts. If you use an INNER JOIN, you only see users who eventually bought and you systematically underestimate the window by ignoring the tail of slow-burning users. Report a non-conversion rate alongside the window so the marketing team knows what fraction of touches are unaccounted for.
The median and the p90 matter more than the average. Time-to-convert distributions are heavily right-skewed. The average gets dragged up by a long tail of users who came back months later. The p90 is the honest answer to the question "how long do I have to wait before I can say this campaign failed?"
Distribution of time-to-convert
The single most useful chart you can hand a growth lead is a histogram of days-to-convert, bucketed in a way that maps to platform defaults. This makes the "should we extend the window?" conversation a five-minute meeting instead of a quarterly debate.
WITH days AS (
SELECT
t.user_id,
t.touched_at,
EXTRACT(EPOCH FROM (MIN(c.converted_at) - t.touched_at)) / 86400
AS days_to_convert
FROM touchpoints t
JOIN conversions c
ON c.user_id = t.user_id
AND c.converted_at >= t.touched_at
AND c.converted_at < t.touched_at + INTERVAL '180 days'
GROUP BY t.user_id, t.touched_at
)
SELECT
CASE
WHEN days_to_convert <= 1 THEN '0_within_1_day'
WHEN days_to_convert <= 7 THEN '1_within_1_week'
WHEN days_to_convert <= 30 THEN '2_within_1_month'
WHEN days_to_convert <= 90 THEN '3_within_1_quarter'
ELSE '4_over_1_quarter'
END AS bucket,
COUNT(*) AS conversions,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM days
GROUP BY 1
ORDER BY 1;Read the output the way an investor reads a cap table. If 92% of conversions land within seven days, you have an impulse-purchase product and a 7-day window is fine. If 35% land between days 8 and 30, the platform-default 7-day window is hiding a third of your wins and reattributing them to organic, which inflates organic ROI and starves paid budgets. If 15% land past 90 days, you have a B2B-style cycle and you need to talk to finance about how revenue attribution rolls up at quarter close.
A useful extra bucket for marketplaces is "30 to 90 days with at least one re-engagement email in between." That tells you whether slow conversions are organic patience or driven by lifecycle marketing, and it is a one-line correlated subquery on a touch table that includes opens.
Breakdown by channel
The aggregate window hides the punchline. Branded search converts in hours because the user already knew your brand and Googled it. Display ads convert in weeks because the user did not know your brand existed and needed three more touches first. Treating them with the same window is how you end up cutting the display budget that was actually feeding the search budget.
WITH ttc AS (
SELECT
t.channel,
t.user_id,
t.touched_at,
EXTRACT(EPOCH FROM (MIN(c.converted_at) - t.touched_at)) / 86400
AS days_to_convert
FROM touchpoints t
JOIN conversions c
ON c.user_id = t.user_id
AND c.converted_at >= t.touched_at
AND c.converted_at < t.touched_at + INTERVAL '180 days'
GROUP BY t.channel, t.user_id, t.touched_at
)
SELECT
channel,
COUNT(*) AS conversions,
ROUND(AVG(days_to_convert)::numeric, 2) AS avg_days,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_convert) AS median_days,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY days_to_convert) AS p90_days
FROM ttc
GROUP BY channel
ORDER BY median_days;A typical output for a US consumer subscription product looks like this: branded search has a median of 0.3 days and a p90 of 2 days. Paid social comes in around 3 days median and 14 days p90. Display lands at 9 days median and 42 days p90. Refer-a-friend trails everything because friends pass the link around and people open it when they have time. When you show this table to a CMO, the conversation immediately stops being "display is broken" and starts being "display needs a 30-day window and a different success metric."
Common pitfalls
The first trap is conflating conversion window with lifetime conversion rate. A 30-day window means a user who converts on day 60 is invisible to the calculation, which makes the channel look worse than it is. Lifetime CR is the unbounded version and is the right metric for evaluating product fit; conversion window is the right metric for evaluating ad spend. Mixing them in the same dashboard column is how marketing teams get pulled into hour-long debates about why two numbers that "should be the same" are different.
The second trap is multi-touch ambiguity. A user can be touched five times across three channels before they convert. Which touch owns the window? Most marketing platforms default to last-touch, which means the channel closest to the conversion gets credit and earlier channels get punished. If you want a defensible answer, compute first-touch and last-touch windows separately and report both. The two numbers tell different stories: first-touch window tells you how long awareness campaigns take to bear fruit, last-touch tells you how long retargeting takes to close.
The third trap is outlier conversions with no cap. A user who clicked a banner two years ago and finally bought a subscription this week is technically attributable, but they will pull your average days-to-convert up by an order of magnitude. Pick a sensible cap based on the product. For a fast app, 90 days. For an enterprise contract, 180. For a one-time high-consideration purchase like a mattress or a car, you may want to argue with finance about going to a year, but document the choice in a metric definition so the next analyst does not redo the work.
The fourth trap is comparing your homegrown window to platform-reported windows on a different basis. Google Ads reports a 30-day click default; Meta reports 7-day click plus 1-day view. If you compute your internal window on click-only data and compare it to Meta's number that includes view-through, the platforms will look wildly different and the difference is the methodology, not reality. Pin down whether you count view-through, and apply the same rule to all channels before you draw conclusions.
The fifth trap is time-zone confusion. Touchpoints come from ad servers in UTC. Conversions come from your product backend, which might be in a regional zone or in the user's local zone. A touch at 11pm UTC followed by a conversion logged at 9am local time can look like a same-day conversion or a next-day one depending on which zone the analyst joins on. Pick UTC across the board and convert everything once at the edge of the warehouse, not in each query.
Optimization tips
For a touchpoints table with billions of rows per quarter, the join is the bottleneck. Partition touchpoints by touched_at::date and cluster conversions by user_id. On Snowflake or BigQuery this turns a full scan into a small range scan because the conversion side only joins to a 180-day forward window.
If you run these queries daily, materialize a daily_touch_to_first_conversion rollup that runs incrementally on yesterday's touches. Dashboards then query the rollup, which is orders of magnitude smaller than the raw facts. Rebuild nightly for windows up to 30 days, weekly for 180-day windows.
For wide channel breakdowns, swap PERCENTILE_CONT for APPROX_PERCENTILE where supported. On BigQuery and Snowflake this cuts runtime by an order of magnitude with negligible loss of accuracy at the p90.
Related reading
- SQL window functions interview questions
- How to calculate install attribution in SQL
- How to calculate CAC by channel in SQL
- How to calculate cohort retention in SQL
If you want to drill marketing-analytics SQL questions like this every day, the NAILDD app is launching with 500+ SQL problems across exactly this pattern.
FAQ
How do I pick the right window length?
Start from the distribution, not from the platform default. Run the bucketed query above, find the smallest window that contains 80-90% of conversions, and pin that as your reporting window. For consumer subscriptions you usually land between 7 and 30 days. For B2B, between 30 and 90 days. Anything past 90 days is the long tail and is better reported as an "additional conversions" footnote than as the main number.
How does Google Ads compare to Meta on default windows?
Google Ads defaults to 30-day click attribution, which is the most generous standard window in mainstream paid advertising. Meta moved to 7-day click plus 1-day view after the iOS 14 changes, which is aggressive in the opposite direction. The implication is that if you naively compare ROAS between the two platforms using their dashboards, Google looks better by construction, not by performance. Recompute both on the same internal window before deciding where to shift budget.
Should attribution window match conversion window?
Generally yes, but with awareness. A short attribution window favors last-touch credit because only recent touches qualify. A long attribution window favors first-touch credit because the earliest qualifying touch gets weight. Most teams report on a "data-driven" model from the platform and a simple first-and-last touch internal model, then triangulate. If the two disagree, the right next step is to look at the channel-level distribution from this post and figure out which channels are being squeezed by the window choice.
How does the window differ for B2C versus B2B?
For B2C consumer apps and subscriptions, 7 to 30 days captures the bulk of conversions and aligns with how ad platforms report. For B2B SaaS, the buying committee adds weeks; 30 to 180 days is more honest. For enterprise deals over six figures, think in quarters and treat paid attribution as a directional pipeline-contribution signal rather than booked revenue. The window then becomes touchpoint-to-opportunity rather than touchpoint-to-revenue.
Does the window affect the conversion rate I report?
Yes, mechanically. A longer window captures more conversions, so the conversion rate goes up. You cannot compare CR across teams that picked different windows because they are measuring different things. Publish CR with the window stamped next to it ("CR-30D = 4.2%") and never ship a bare CR number on a dashboard. Hiring managers at growth-heavy companies like Notion and Linear catch this in a screen.
How do I handle users with multiple touches before converting?
Run the calculation twice, once on first touch per user-conversion pair and once on last touch, and compare. First-touch window is your awareness lag; last-touch window is your closing lag. The gap between them sizes your nurturing funnel; if they diverge, consider a position-based or data-driven attribution model rather than picking a single winner.