SECONDS_BETWEEN

Syntax

SECONDS_BETWEEN(firstInputTime, secondInputTime)

AMC specifics

• Typical use in AMC: time from touchpoint to purchase, for example for an attribution window. Amazon's own workflow example filters on at most nine days this way.
• The result is negative if the second time is before the first.

Example

Purchases at most seven days after the touchpoint.

SELECT
  campaign,
  SUM(purchases) AS purchases_within_7_days
FROM
  amazon_attributed_events_by_traffic_time
WHERE
  SECONDS_BETWEEN(traffic_event_dt_utc, conversion_event_dt_utc) <= 60 * 60 * 24 * 7
GROUP BY
  1

Checked against the AMC schema, not executed on an AMC instance.