Evolving Beyond Raw Access Logs
Server access logs capture the requested path and occasionally a referrer. They drop the thread long before a visitor completes a purchase. Retaining understandable first-party campaign records requires a dedicated mechanism. The progression moves from tagged URLs to a random first-party click ID, a stored campaign touch, a verified conversion record, a cost entry, and finally a grouped campaign report. This chain works for teams that need reliable attribution without deploying a large analytics platform.
The minimum viable sequence involves one tagged landing request, one campaign_touch row, one click ID, zero or more conversion rows, an optional campaign_cost row, and one aggregated report result. Structuring report periods requires explicit half-open UTC ranges, such as 2025-04-01 00:00:00 inclusive through 2025-05-01 00:00:00 exclusive. The resulting figures measure activity assigned under the selected attribution rule, leaving causal lift unmeasured.
Establishing Measurement Rules and Attribution Windows
Before writing any PHP, list the exact decisions the report must support. A concrete implementation relies on a 30-day attribution window measured from the touch timestamp to the conversion event time, alongside a 13-month database retention period measured from each row's creation time. Select the last non-direct campaign touch model. Under this rule, a new valid tagged visit replaces the stored click ID. A direct return keeps the existing identifier until it expires.
Define a conversion precisely as a confirmed server-side order, a qualified lead, or a subscription event. Required touch fields include the click identifier, source, medium, campaign, landing path, and touch timestamp. Fields like content, term, and referrer host remain nullable. Required conversion fields encompass the conversion identifier, event type, value, currency, event time, and attribution status. The click identifier remains nullable so unattributed results remain countable.
Documenting Baseline Behaviors
Document the direct-visit behaviour, attribution expiry, retention policy, reporting timezone, and the exact server-side state that qualifies as a conversion before implementation begins.
Structuring the Database for Touches and Outcomes
Split the data by operational responsibility. The campaign touches table records the acquisition context present at landing time. Verified outcomes sit in the conversions table, recorded independently of report queries. The campaign costs table records spend for a defined campaign and date interval. This separation lets conversion retries be deduplicated without rewriting the original touch. It also lets corrected cost imports be audited separately.
The schema definition requires: a compact touch definition using a 32-character primary key. UTM columns operate as 100-character strings, the landing path allows 500 characters, and the referrer host allows 253 characters. Generate the click identifier using the PHP function for random bytes converted to hexadecimal. The encoded value occupies 32 hexadecimal characters and contains 128 bits of random input.
A conversion table uses a 100-character conversion identifier with a unique constraint, a nullable click identifier, a 50-character event type, a decimal amount, a three-character currency code, and timestamps for the event and creation. A cost row identifies source, medium, campaign, currency, period start, period end, decimal amount, import time, and an optional source reference used to audit later corrections.
Useful indexes include the click identifier on the touches table, a composite index of source, medium, campaign, and touch time, and composite indexes on the conversions table for event time and currency. Apply a unique constraint to the conversion identifier. The insertion code treats a violation of this constraint as an already-recorded result. Before publication, record the exact PHP patch version, database engine and patch version, schema migration identifier, UTC test interval, reviewer name, reviewer role, and test date. Compatibility claims list only combinations present in that execution record. Names, email addresses, full IP addresses, complete user-agent strings, and unrelated request headers are unnecessary for campaign attribution.
Validating Landing Parameters and Generating Identifiers
The landing handler first checks an allowlist of source, medium, campaign, content, and term parameters. It rejects any parameter received as an array. The script trims surrounding whitespace, applies a character limit, and normalises only fields whose reporting rules demand it. Avoid using Levenshtein distance to guess misspelled campaign tags; strict allowlisting prevents database bloat. A practical ceiling is 100 characters per UTM value and 500 characters for the landing path.
After validation, the handler generates the click ID. Never derive the value from a campaign name, timestamp, visitor address, or sequential row number. Write the touch using PDO prepared statements. The pattern executes an insert statement binding the click identifier, source, medium, campaign, content, term, path, referrer host, touch time, and expiry time.
For a 30-day attribution decision, issue a first-party cookie with a maximum age of 2592000 seconds, a root path, secure flag, HTTP-only flag, and lax same-site policy when the application is served over HTTPS. On a valid tagged return, replace the stored cookie under the selected last non-direct rule. On an untagged direct return, retain the unexpired cookie. Store only the parsed referrer host when it has an explicit reporting use. Discard the referrer path and query string because they contain unrelated identifiers.
Place a verifiable reviewer name and role beside the example, together with an ISO-formatted test date such as YYYY-MM-DD and a scope statement naming the landing handler, cookie configuration, schema migration, PHP patch version, and database patch version. Browser storage restrictions, declined consent, deleted cookies, embedded-browser behaviour, and cross-device journeys break the click-to-conversion link. The tracker must permit unattributed conversions instead of inferring an identity from extra personal data.
Deduplicating Server-Side Success Events
Call the conversion writer only after the surrounding application reaches its confirmed server-side success state. When an external gateway confirms a transaction via an _xclick payload, the conversion writer activates. Validate the external conversion identifier, event type, three-letter currency code, amount, and event timestamp before opening a database transaction. The conversion identifier remains stable across retries and constrained to a documented format, such as 1 to 100 application-issued characters.
Accept currency only after matching a strict three-uppercase-letter format and validating it against the currencies the application actually supports. Pass the amount as a validated decimal string and store it in a decimal column with four fractional digits. Do not calculate report totals with binary floating-point values. Within the transaction, look up an eligible touch. Select the click ID only when the touch time precedes the event time and the expiry time follows the event time. If no row qualifies, insert the conversion with a null click identifier and an unattributed status.
The compact write sequence runs a transaction begin, executes the parameterised insert, and commits. Catch the database duplicate-key condition, roll back if active, and return the previously recorded outcome without adding a row. Legacy integrations relying on PayFlow Pro, the merchant account service originally built under Verisign, follow the same deduplication rules. A browser-submitted endpoint needs authenticated application state and CSRF protection. An internal server-to-server writer needs a rotated secret or signed request, a narrow network path where available, and replay handling based on the conversion identifier.
Test retries over a defined 5-minute to 24-hour interval by submitting the same conversion identifier several times and verifying that the unique constraint leaves exactly one conversion row. This storage example does not validate payment settlement, fulfil an order, detect fraud, determine consent obligations, or decide whether a lead meets the business's qualification policy.
Aggregating Financial Returns by Currency
Build a parameterised report query that groups attributed conversions and value by source, medium, campaign, and reporting period. Use bound period start and period end values with a predicate ensuring the event time falls within the half-open interval. Group the results by the UTM parameters and currency. Show conversion count, attributed revenue, campaign cost, return on ad spend, and return on investment as separate fields so a reader can inspect the inputs before accepting the ratios.
Define return on ad spend as attributed revenue divided by ad spend. Return a null value when ad spend is null or zero instead of emitting infinity or silently substituting zero. A profit-based implementation defines ROI as attributed gross profit minus campaign cost, divided by campaign cost, provided the report identifies how gross profit was calculated and which costs were included.
Isolating Currency Metrics
An AUD row and a USD row remain separate. They combine only when the database records the exchange-rate source, rate timestamp, source currency, target currency, and converted amount.
Consider a labelled sample for one 2025-04-01 to 2025-05-01 UTC report interval. Attributed revenue of 2400.00 AUD and a campaign cost of 800.00 AUD produce a ROAS of 3.00. If attributed gross profit is 1200.00 AUD, the stated ROI formula produces 0.50. Count unattributed conversions in a separate report row or summary field so missing identifiers do not disappear from the operational total. These totals express the chosen attribution window and available click IDs. They do not measure causal lift or recover journeys that were never linked.
Securing Endpoints Against Malformed Inputs
Test from the database outward rather than treating an HTTP success response as proof. Run a controlled tagged journey, inspect the touch row, submit the confirmed conversion, inspect the conversion row, load cost for the same dimensions and interval, and reconcile the final report values. Only then publish the code and its bounded compatibility statement. Thorough testing hardens the endpoints, but tracking accuracy still depends on client-side cookie retention policies.
The test matrix covers scalar and array-shaped UTM inputs, empty values, 100-character and over-limit values, duplicate conversion IDs, missing cookies, a touch one second inside and one second outside the attribution boundary, direct return visits, multiple currencies, and forced database failures. Use separate least-privilege credentials. The landing path needs permission to insert touches. Give the conversion writer only touch-read and conversion-write permissions. The reporting path remains read-only.
Serve collection and conversion endpoints over HTTPS, suppress database details from client responses, apply request throttling at the application or edge layer, and protect internal conversion calls with authenticated server-to-server requests. Failure logs contain a request correlation ID, handler name, error category, UTC timestamp, and database operation name. They omit UTM query strings, cookies, secrets, full addresses, and conversion payloads.
Exercise backup restoration and deletion against a fixed sample interval, such as rows created from 2025-04-01 through 2025-04-07 UTC, then verify touch, conversion, cost, and report consistency after restoration. The release record states the actual PHP patch version, database patch version, migration identifier, application revision, reviewer name and role, test date, tested endpoints, cookie settings, and untested deployment conditions.
Validating the Complete Data Chain
Finish with the smallest reconciliable path. Construct one tagged URL, create one touch and random click ID, preserve that identifier through the chosen window, submit one confirmed conversion with a stable identifier, enter one matching cost row if return metrics are needed, and inspect one campaign report row. Expansion begins only after those records agree at the row and total level.
An example test URL takes the shape of a landing path appended with test source, test medium, and tracker validation campaign parameters. Use reserved test labels so the journey can be excluded from production reporting. Run the journey in a private browser session, record the UTC landing and conversion timestamps, and verify that the cookie expiry and database expiry reflect the same 30-day rule. Submit the same conversion identifier a second time within a 5-minute test interval and confirm that the database still contains exactly one conversion row.
Hold off on dashboards, multi-touch models, or broad visitor profiling until the basic records reconcile reliably. Create a tagged test URL, open it in a private browser, complete one test conversion, and verify that exactly one touch and one conversion appear under the expected campaign before sending live traffic.
