.svg)
When a customer converts after seeing your Facebook ad, clicking a Google search result, and then opening an email, which touchpoint deserves the credit? Time decay attribution offers a compelling answer: give more weight to the interactions closest to conversion. This model recognizes that a prospect clicking your ad yesterday likely had more influence on their purchase decision than the display ad they saw three weeks ago.
For marketing teams running multi-platform campaigns, implementing time decay attribution in SQL provides granular control over how you measure channel performance. You can customize decay rates, adjust lookback windows, and analyze results at whatever level of detail your business requires.
This guide walks you through building a time decay attribution model from scratch using SQL. Whether you work with a data warehouse, a BI tool, or a marketing analytics platform, you will learn how to structure your data, write the core calculations, and generate actionable reports that reveal which touchpoints truly drive revenue.
Before you can build any attribution model, you need clean, well-organized data. Your time decay attribution model depends on having accurate timestamps and consistent user identification across every marketing interaction.
Start by ensuring your database includes these essential fields: user_id (a consistent identifier that tracks the same person across sessions), touchpoint_timestamp (the exact date and time of each interaction), channel (the marketing source like Facebook, Google Ads, or Email), campaign (the specific campaign name), conversion_flag (a boolean indicating whether this user eventually converted), and conversion_value (the revenue or value associated with the conversion).
Most marketing databases organize this information across two primary tables. Your events table captures every touchpoint: ad clicks, page visits, email opens, and other interactions. Your conversions table records completed purchases, form submissions, or whatever action defines success for your business. A user mapping table connects anonymous visitors to identified users once they provide an email or create an account.
Run these data quality checks before proceeding. Verify that every touchpoint has a valid timestamp with no null values. Confirm that user_id values remain consistent across multiple sessions for the same person. Check for duplicate events that might inflate your touchpoint counts. Look for touchpoints that occurred after their associated conversion, which indicates a data pipeline issue. Understanding how to fix attribution data gaps is essential before building your model.
Common problems include timezone inconsistencies where some events use UTC while others use local time, creating artificial time gaps. User identification breaks often occur when someone switches devices or clears cookies. Conversion tracking failures happen when your analytics code fires before the conversion is fully recorded in your database.
Fix timezone issues by standardizing all timestamps to UTC in your source tables. Address user identification gaps by implementing a robust identity resolution strategy that links anonymous sessions to known users retroactively. For conversion tracking, add a small delay or use server-side tracking to ensure conversions register before attribution runs.
Once your data structure is solid, you can build attribution logic that actually reflects reality rather than artifacts of poor data quality.
Time decay attribution only makes sense within a defined timeframe. You need to decide how far back to look when assigning credit to touchpoints before a conversion.
Start by writing SQL that identifies your conversion events. This query should pull all records where a meaningful action occurred, whether that is a purchase, a demo request, or a subscription signup. Your conversion table likely already flags these events, but you may need to filter for specific conversion types or exclude test transactions.
The lookback window determines which touchpoints qualify for attribution credit. If you set a 30-day window, only interactions that occurred within 30 days before the conversion receive any credit. Touchpoints older than 30 days are excluded entirely from the calculation.
Choose your lookback window based on your typical sales cycle. E-commerce businesses with impulse purchases might use 7 days. B2B SaaS companies with longer consideration periods often use 30 or even 60 days. If most customers convert within two weeks of their first interaction, a 14-day window captures the relevant journey without including unrelated touchpoints from months earlier.
Write a SQL join that connects each conversion to all touchpoints for that user within your lookback period. Use a WHERE clause that filters for touchpoint_timestamp between (conversion_timestamp minus your lookback window) and conversion_timestamp. This creates a dataset where each row represents one touchpoint that potentially influenced one conversion. For a deeper understanding of the underlying concepts, explore our guide on the time decay attribution model.
Handle edge cases carefully. When a user converts multiple times, decide whether to attribute each conversion independently or treat them as separate customer journeys. For most models, independent attribution makes sense: each purchase gets its own lookback window and credit allocation.
Watch for touchpoints that occur after the conversion timestamp. These should never receive attribution credit for that conversion, though they might influence a future purchase. Add a filter to exclude any touchpoint where touchpoint_timestamp is greater than conversion_timestamp.
Your query output should now show every valid touchpoint-conversion pair, ready for the next step where you calculate how much time separated each interaction from the final conversion.
The core of time decay attribution is measuring how close each touchpoint was to the conversion moment. This time difference determines how much credit each interaction receives.
SQL provides several functions for calculating time differences, and the syntax varies by database platform. PostgreSQL uses EXTRACT(epoch FROM (conversion_timestamp - touchpoint_timestamp))/86400 to get the difference in days. MySQL uses TIMESTAMPDIFF(DAY, touchpoint_timestamp, conversion_timestamp). BigQuery uses DATE_DIFF(conversion_date, touchpoint_date, DAY).
Convert all time differences to a consistent unit before applying your decay formula. Days work well for most marketing scenarios because they align with how teams think about campaign performance. Hours might be more appropriate for very short sales cycles, while weeks could work for longer B2B journeys.
Create a derived column called days_before_conversion that stores this calculated value. The formula should always produce a positive number: the conversion timestamp minus the touchpoint timestamp. A touchpoint that occurred 5 days before conversion should show 5.0, while one that happened 2 hours before conversion might show 0.08 if you are using fractional days.
Here is what this looks like in practice. If someone clicked a Facebook ad on January 1st and converted on January 8th, days_before_conversion equals 7. An email they opened on January 7th would have days_before_conversion of 1. The Google search they performed on January 5th would show 3. This same logic applies whether you are building multi-touch attribution in SQL or focusing specifically on time decay.
Verify your calculations with sample data before scaling to your full dataset. Pick a few conversions where you know the timeline, manually calculate what the days_before_conversion should be, and confirm your SQL produces matching results. Small errors in date math can completely skew your attribution, so this validation step is critical.
Watch for negative values, which indicate your touchpoint occurred after the conversion. These should have been filtered out in Step 2, but double-check here. Also look for unexpectedly large values (like 500 days before conversion), which might indicate data quality issues or touchpoints that fall outside your intended lookback window.
Now comes the mathematical heart of time decay attribution: translating time differences into attribution weights using an exponential decay function.
The standard formula is weight = 2^(-days_before_conversion / half_life). This exponential function creates a smooth decay curve where recent touchpoints receive dramatically more credit than older ones. The half_life parameter controls how quickly credit diminishes over time.
Think of half_life as the number of days it takes for a touchpoint's credit to drop by 50%. With a half_life of 7 days, a touchpoint that occurred 7 days before conversion receives half the credit of one that happened at the moment of conversion. A touchpoint 14 days back receives one-quarter the credit, and so on.
Common half_life values range from 7 to 14 days. Use 7 days when you want to heavily favor late-stage touchpoints, emphasizing the final interactions that pushed someone to convert. Choose 14 days for a gentler decay that still gives meaningful credit to mid-funnel activities. Some teams experiment with even longer half_lives (21 or 30 days) for complex B2B sales cycles. Learn more about the fundamentals in our comprehensive time decay attribution guide.
Implement this in SQL using your database's power function. PostgreSQL and BigQuery use POWER(2, -days_before_conversion / 7.0) for a 7-day half_life. MySQL uses POW(2, -days_before_conversion / 7.0). Always use floating-point division (7.0 instead of 7) to avoid integer rounding errors.
After calculating raw weights, you must normalize them so all touchpoints for a single conversion sum to 100%. Without normalization, conversions with many touchpoints would appear more valuable than conversions with few touchpoints, distorting your total attributed revenue.
Calculate the sum of all raw weights for each conversion using a window function: SUM(raw_weight) OVER (PARTITION BY conversion_id). Then divide each touchpoint's raw weight by this sum to get the normalized weight. If three touchpoints have raw weights of 0.5, 0.25, and 0.125, they normalize to 57%, 29%, and 14% respectively.
The normalized weight represents each touchpoint's share of the conversion credit. Multiply this percentage by the conversion_value to get the attributed revenue for each touchpoint. A touchpoint with a 29% normalized weight on a $1,000 purchase receives $290 in attributed revenue.
Verify that your normalization works correctly by summing the attributed revenue for any single conversion. The total should exactly equal the original conversion_value. If you see rounding errors beyond a few cents, check your normalization logic.
Individual touchpoint attribution is interesting, but actionable insights come from aggregating results at the channel and campaign level.
Write a SQL query that groups your attributed touchpoints by channel, summing the attributed revenue for each. This shows you how much total revenue each marketing source drove after accounting for time decay. You might discover that email, which looks weak in last-touch attribution, actually contributes significantly when you credit its role in nurturing prospects over time. For email-specific insights, explore email marketing attribution tracking strategies.
Expand your aggregation to include campaign-level detail. Group by both channel and campaign to see which specific initiatives perform best. Add ad group or creative-level breakdowns if your data supports that granularity and your team needs those insights for optimization.
Create comparison views that show time decay results alongside last-touch and first-touch attribution. Calculate last-touch by giving 100% credit to the final touchpoint before conversion. Calculate first-touch by giving 100% credit to the initial touchpoint within your lookback window. Display all three models side by side to understand how credit shifts between channels. Understanding the difference between single source attribution and multi-touch attribution models helps contextualize these comparisons.
You will often find that channels like Facebook and display advertising gain credit in time decay compared to last-touch, because they frequently appear earlier in the customer journey. Meanwhile, branded search and retargeting might lose some credit because they typically occur at the very end, where time decay still favors them but not as exclusively as last-touch does.
Build date-range filters into your reporting queries so you can analyze different time periods. Use WHERE clauses that filter conversion_timestamp between your start and end dates. This flexibility lets you compare month-over-month performance, analyze seasonal patterns, or isolate the impact of specific campaign launches.
Calculate attributed ROAS (return on ad spend) by dividing attributed revenue by ad spend for each channel. If Google Ads generated $50,000 in time-decay-attributed revenue and you spent $10,000, your attributed ROAS is 5.0. Calculate attributed CPA (cost per acquisition) by dividing ad spend by the number of conversions that included at least one touchpoint from that channel.
These aggregated metrics give you a clear picture of which channels and campaigns deserve more budget based on their true contribution to revenue, not just their position in the customer journey.
Before you make budget decisions based on your time decay model, run thorough validation checks to ensure the math is correct and the insights are meaningful.
The most important sanity check: sum all attributed revenue across all touchpoints and verify it equals your total actual conversion revenue. If you had $100,000 in conversions, your attributed revenue should total exactly $100,000. Any discrepancy indicates a normalization error or a problem with how you are handling multiple conversions per user. Learn how to fix attribution discrepancies in data when totals do not match.
Test different half_life values and compare the outputs. Run your model with half_life set to 7, 14, and 21 days. Examine how channel rankings change. A shorter half_life will shift more credit to bottom-funnel channels like retargeting and branded search. A longer half_life distributes credit more evenly across the journey, benefiting awareness channels.
Identify specific channels that gain or lose significant credit compared to last-touch attribution. Create a report showing the percentage change in attributed revenue for each channel. You might find that content marketing gains 40% more credit in time decay, revealing its role in nurturing prospects even though it rarely gets last-touch credit.
Look for unexpected patterns that might indicate data issues. If a channel suddenly shows zero attributed revenue when it had conversions in last-touch, investigate whether touchpoints from that channel are being filtered out incorrectly. If attributed revenue for a channel exceeds its last-touch revenue by 300%, verify that your normalization is working properly. Implementing real-time attribution tracking can help catch these issues faster.
Consider the operational complexity of maintaining custom SQL attribution as your marketing scales. Every new channel requires updates to your queries. Changes in your data structure can break your calculations. Real-time reporting becomes difficult when you are running complex aggregations across millions of touchpoints.
When manual SQL attribution becomes a bottleneck, dedicated attribution platforms handle the complexity automatically. They track touchpoints in real time, apply multiple attribution models simultaneously, and update reports continuously without manual query maintenance.
You now have a working time decay attribution model in SQL that gives appropriate credit to touchpoints based on their proximity to conversion. This approach reveals insights that simpler models miss, particularly for channels that nurture prospects over time.
Quick implementation checklist: structure your touchpoint and conversion tables with required fields including user_id, timestamps, channel, and conversion_value. Define your lookback window based on your typical sales cycle, whether that is 7 days for e-commerce or 30-plus days for B2B. Calculate time differences using your database's date functions, ensuring consistent units and positive values. Apply the exponential decay formula with your chosen half_life, typically 7 to 14 days depending on how heavily you want to favor recent touchpoints. Normalize weights so each conversion totals 100%, preserving total revenue across your attribution. Aggregate by channel and campaign for actionable reports that show attributed revenue, ROAS, and CPA.
As your marketing complexity grows, maintaining custom SQL attribution becomes increasingly challenging. You need to update queries when adding channels, troubleshoot data quality issues that break calculations, and wait for batch processes to refresh reports. Real-time optimization decisions become difficult when your attribution insights are hours or days old.
Platforms like Cometly handle multi-touch attribution automatically, connecting your ad platforms, CRM, and website to track complete customer journeys in real time without manual query maintenance. The AI-driven system captures every touchpoint from initial ad click through CRM events, applies sophisticated attribution models including time decay, and feeds enriched conversion data back to platforms like Meta and Google to improve their targeting algorithms.
Ready to elevate your marketing game with precision and confidence? Discover how Cometly's AI-driven recommendations can transform your ad strategy. Get your free demo today and start capturing every touchpoint to maximize your conversions.
