The Failure Mode of Standard Time-Decay in Long-Cycle Journeys
Default algorithmic and rules-based multi-touch attribution (MTA) frameworks routinely undervalue the organic search channel. When enterprise analytics teams deploy standard time-decay models—frequently hardcoded with a 7-day half-life—they inadvertently bias capital allocation toward high-intent, bottom-of-the-funnel (BoFu) branded queries and retargeting campaigns. In high-consideration sectors, such as technical B2B SaaS, luxury goods, and complex catalog e-commerce, the consideration cycle between the initial informational discovery and the terminal conversion event rarely conforms to a 7-day window. Instead, it spans 45, 60, or 120 days.
When an informational guide answers an architectural or integration question at Day 75, a standard 7-day exponential decay assigns it an attribution weight approaching zero ($2^{-(75/7)} approx 0.00059$). Consequently, content operations appear ROI-negative, leading teams to defund top-of-funnel content that introduces qualified buyers into the pipeline. To reverse this structural bias and systematically increase organic revenue, data engineers and technical SEOs must reconstruct the decay function. By engineering content-aware, calibrated time-decay models inside Google BigQuery, you can accurately capture the real conversion lift generated by non-brand organic discovery.
This implementation directly supports the architectural foundations outlined in our core guide on Multi-Touch Organic Attribution Modeling for Assisted Conversions Across Informational Touchpoints, providing the SQL scripts and statistical machinery required to execute custom time-decay at scale.
Mathematical Foundations: Standard vs. Calibrated Informational Decay
The classical exponential time-decay function computes the raw weight $W$ of a touchpoint at timestamp $t_i$ relative to the conversion timestamp $t_c$ as follows:
$$W(t_i) = 2^{-frac{t_c – t_i}{lambda}}$$
Where $lambda$ denotes the half-life parameter. Under a static $lambda = 7$ days, touchpoints older than 21 days retain less than 12.5% of the credit given to a last-click event occurring at $t_c$.
The Dual-Parametric / Content-Classified Half-Life
To prevent the systematic erasure of informational touchpoints, we modify the decay function across two dimensions: touchpoint classification ($C$) and observed journey velocity ($V$). Informational touchpoints are assigned an expanded base half-life ($lambda_{info}$), while transactional/navigational touchpoints operate on a truncated half-life ($lambda_{trans}$):
$$W(t_i, C) = alpha_C cdot 2^{-frac{t_c – t_i}{lambda_C}}$$
Where:
- $lambda_{info} in [30, 90]$ days, derived from empirical latency distributions of first-to-last touch intervals.
- $lambda_{trans} in [3, 7]$ days, reflecting the compressed decision loop of product/pricing page visits.
- $alpha_C$ is an intent-weighting scalar calibrated against historical conversion assist rates (e.g., $1.2$ for informational search entrances, $0.8$ for self-directed homepage visits).
Once raw weights are computed for all touchpoints $i in {1, dots, n}$ within a user journey path $P$, the weights are normalized to sum to $1.0$, preserving total transaction value:
$$W’_{norm}(t_i) = frac{W(t_i, C)}{sum_{j=1}^{n} W(t_j, C)}$$
Extracting and Assembling GA4 Event Paths in BigQuery
To execute this model in Google BigQuery using Google Analytics 4 (GA4) export data, we must resolve user session sequences, isolate organic search touchpoints, classify their page intent, and link them to terminal purchase or lead generation events within a 90-day lookback window.
SQL Implementation: Path Construction and Calibrated Decay Engine
The following production SQL script flattens raw GA4 events, categorizes content types via regex path parsing, calculates touchpoint delta times relative to the conversion event, applies asymmetric half-life calculations, and normalizes revenue across the journey.
WITH raw_events AS (
SELECT
user_pseudo_id,
event_timestamp,
TIMESTAMP_MICROS(event_timestamp) AS event_time,
event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS traffic_medium,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS traffic_source,
(SELECT COALESCE(value.numeric_value, value.int_value, value.double_value, 0)
FROM UNNEST(event_params) WHERE key = 'value') AS event_value
FROM
`your-gcp-project.analytics_123456789.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name IN ('session_start', 'page_view', 'purchase')
),
classified_touchpoints AS (
SELECT
user_pseudo_id,
event_time,
event_name,
traffic_source,
traffic_medium,
page_location,
event_value,
-- Classify intent based on URI patterns
CASE
WHEN REGEXP_CONTAINS(page_location, r'/(blog|learn|guides|resources|documentation)/') THEN 'INFORMATIONAL'
WHEN REGEXP_CONTAINS(page_location, r'/(products|pricing|cart|checkout)/') THEN 'TRANSACTIONAL'
ELSE 'GENERAL'
END AS content_intent
FROM
raw_events
WHERE
-- Filter strictly for touchpoint qualification or conversion
(traffic_medium = 'organic' AND event_name = 'page_view')
OR event_name = 'purchase'
),
conversion_windows AS (
SELECT
c.user_pseudo_id,
c.event_time AS conversion_time,
c.event_value AS conversion_revenue,
t.event_time AS touchpoint_time,
t.traffic_source,
t.traffic_medium,
t.page_location,
t.content_intent,
-- Compute day-fraction difference between touchpoint and conversion
TIMESTAMP_DIFF(c.event_time, t.event_time, SECOND) / 86400.0 AS days_before_conversion
FROM
classified_touchpoints c
INNER JOIN
classified_touchpoints t ON c.user_pseudo_id = t.user_pseudo_id
AND t.event_time <= c.event_time
AND t.event_time >= TIMESTAMP_SUB(c.event_time, INTERVAL 90 DAY)
WHERE
c.event_name = 'purchase'
AND c.event_value > 0
AND t.event_name != 'purchase'
),
decay_weights AS (
SELECT
user_pseudo_id,
conversion_time,
conversion_revenue,
touchpoint_time,
page_location,
content_intent,
days_before_conversion,
-- Dynamic decay parameterization based on content classification
CASE
WHEN content_intent = 'INFORMATIONAL' THEN
1.2 * POWER(2.0, - (days_before_conversion / 45.0)) -- 45-day half-life + 20% boost
WHEN content_intent = 'TRANSACTIONAL' THEN
1.0 * POWER(2.0, - (days_before_conversion / 7.0)) -- 7-day half-life
ELSE
0.8 * POWER(2.0, - (days_before_conversion / 14.0)) -- 14-day default baseline
END AS raw_weight
FROM
conversion_windows
),
normalized_attribution AS (
SELECT
user_pseudo_id,
conversion_time,
conversion_revenue,
touchpoint_time,
page_location,
content_intent,
days_before_conversion,
raw_weight,
-- Normalization over path partition
raw_weight / SUM(raw_weight) OVER (
PARTITION BY user_pseudo_id, conversion_time
) AS normalized_weight
FROM
decay_weights
)
SELECT
page_location,
content_intent,
COUNT(1) AS touchpoint_instances,
ROUND(SUM(normalized_weight), 2) AS attributed_conversions,
ROUND(SUM(normalized_weight * conversion_revenue), 2) AS attributed_revenue
FROM
normalized_attribution
GROUP BY
1, 2
ORDER BY
attributed_revenue DESC;
Empirical Calibration: Setting Half-Life via Survival Analysis
Hardcoding half-lives—even when split into 45-day and 7-day horizons—remains an approximation unless calibrated against your site’s actual latency profile. To validate whether your decay curve reflects actual user behavior, extract the median conversion latency for journeys containing informational touchpoints using a cumulative distribution function (CDF).
WITH path_latencies AS (
SELECT
user_pseudo_id,
TIMESTAMP_DIFF(MAX(event_time), MIN(event_time), DAY) AS journey_duration_days
FROM
classified_touchpoints
GROUP BY
user_pseudo_id
HAVING
LOGICAL_OR(event_name = 'purchase') AND LOGICAL_OR(content_intent = 'INFORMATIONAL')
)
SELECT
PERCENTILE_CONT(journey_duration_days, 0.25) OVER() AS p25_latency,
PERCENTILE_CONT(journey_duration_days, 0.50) OVER() AS median_latency,
PERCENTILE_CONT(journey_duration_days, 0.75) OVER() AS p75_latency
FROM
path_latencies
LIMIT 1;
Set your $lambda_{info}$ equal to the median (P50) latency of journeys containing an informational step. If your informational paths exhibit a median conversion horizon of 52 days, set $lambda_{info} = 52.0$. This roots the decay function in empirical user physics rather than arbitrary agency heuristics.
Allocating Capital to Increase Organic Revenue
When you recalculate touchpoint values using calibrated time-decay, the redistribution of attributed pipeline value reveals the true efficiency of top-of-funnel content. Teams relying on standard first-click over-credit discovery pieces that fail to sustain momentum; teams relying on last-click or short-decay models over-credit navigational branded queries that merely capture existing demand.
By implementing content-sensitive, extended-decay attribution, you produce data that accurately demonstrates how programmatic informational hubs, comparative technical analyses, and problem-aware guides feed downstream conversions. Armed with these BigQuery outputs, technical SEO and growth teams can justify the computational and editorial resources needed to build high-intent informational assets, systematically securing the budget allocations required to accelerate conversion velocity and sustainably increase organic revenue.
Related Guides in This Series
- Shapley Value Fractional Pipeline Allocation for Non-Converting Top-of-Funnel SEO Content
- First-Party Clickstream Identity Resolution for Multi-Session Organic Informational Touchpoints
- Markov Chain Removal Effect Modeling for Measuring Informational Blog Revenue Contribution
- Connecting Early Informational Organic Landing Pages to CRM Closed-Won Value via Data Warehouse Join Keys