365D+ Lapsed

Measuring Reactivated Customers in Triple Whale SQL

By default, in Triple Whale purchases as either “new” or “returning”, but not all “returning” customers are equal. There’s a big difference between someone who just purchased last month, and someone who hasn’t ordered in 6+ months and was reactivated by paid media.
Many brands fall into the trap of thinking only net-new customers bring value. But in reality, reactivated customers, especially those who had long since disengaged, represent incremental profit that wouldn't have occurred without advertising.
If you’re on the Professional plan, you have access to the SQL Editor, which allows you to dig deeper into reactivation performance.
In this guide, we’ll show you how to:
  • Identify reactivated customers based on time since their last order (e.g., 90, 180, or 365 days).
  • Calculate how NC ROAS and NC % change when including those reactivated customers.
  • Export the results for further analysis or reporting.
Paste the below queries into the SQL Editor. 
WITH
previous_events_cte AS (
  SELECT
    pot.customer_id AS customer_id,
    argMax(pot.event_date, pot.event_date) AS previous_event_date,
    arrayStringConcat(arrayDistinct(groupArray(pot.channel)), ', ') AS previous_channel
  FROM
    pixel_orders_table AS pot
  WHERE
    pot.model = 'Triple Attribution'
    AND pot.attribution_window = '7_days'
    AND pot.event_date < CURRENT_DATE() - 60
  GROUP BY
    pot.customer_id
),
orders AS (
  SELECT  
    pot.order_id AS order_id,
    pot.campaign_name AS campaign_name,
    pot.event_date AS event_date,
    COALESCE(pot.order_revenue, 0) AS order_revenue,
    pot.is_new_customer AS is_new_customer,
    pot.channel AS channel,
    CASE  
      WHEN pot.is_new_customer = false AND pe.previous_event_date IS NOT NULL THEN dateDiff('day', pe.previous_event_date, pot.event_date)
      ELSE NULL
    END AS days_since_previous_event,
    CASE
      WHEN pot.is_new_customer = true THEN 'new'
      WHEN pot.is_new_customer = false AND pe.previous_event_date IS NOT NULL AND dateDiff('day', pe.previous_event_date, pot.event_date) >= 365 THEN 'reactivated_365'
    END AS revised_customer_type,
    pot.customer_id AS customer_id,
    pe.previous_event_date AS previous_event_date,
    pe.previous_channel AS previous_channel
  FROM
    pixel_orders_table AS pot
    LEFT JOIN previous_events_cte AS pe ON pot.customer_id = pe.customer_id
  WHERE
    pot.model = 'Triple Attribution'
    AND pot.attribution_window = '7_days'
    AND pot.channel = 'applovin'
    AND pot.event_date BETWEEN CURRENT_DATE() - 60 AND CURRENT_DATE() - 1
),
spend AS (
  SELECT
      pjt.event_date AS date,
      pjt.campaign_name AS campaign_name,
      SUM(pjt.spend) AS spend
  FROM
      pixel_joined_tvf AS pjt
  WHERE
      pjt.model = 'Triple Attribution'
      AND pjt.event_date BETWEEN CURRENT_DATE() - 60 AND CURRENT_DATE() - 1
      AND pjt.attribution_window = '7_days'
      AND pjt.channel IN ('applovin')
  GROUP BY
      pjt.event_date,
      pjt.campaign_name
),
reactivated AS (
  SELECT
    event_date,
    campaign_name,
    s.spend AS spend,
    COUNT(order_id) AS total_transaction,
    SUM(CASE WHEN is_new_customer = true THEN 1 ELSE 0 END) AS new_transaction,
    SUM(CASE WHEN is_new_customer = false THEN 1 ELSE 0 END) AS return_transaction,
    SUM(CASE WHEN revised_customer_type = 'reactivated_365' THEN 1 ELSE 0 END) AS reactivated_365,
    SUM(order_revenue) AS total_rev,
    SUM(CASE WHEN is_new_customer = true THEN order_revenue ELSE 0 END) AS new_rev,
    SUM(CASE WHEN revised_customer_type = 'reactivated_365' THEN order_revenue ELSE 0 END) AS reactivated_365_rev
  FROM orders o
  JOIN spend s ON s.date = o.event_date AND s.campaign_name = o.campaign_name
  WHERE s.spend > 300
  GROUP BY
    event_date,
    campaign_name,
    s.spend
  ORDER BY event_date DESC
)
SELECT
event_date,
campaign_name,
spend,
total_rev / spend AS "Reported ROAS",
new_rev / spend AS "Reported NC ROAS",
(new_rev + reactivated_365_rev) / spend AS "NC + 365D Reactived ROAS",
'' AS " ",
CONCAT(ROUND(new_transaction / total_transaction * 100, 1), '%') AS "1st time customer % of total",
CONCAT(ROUND(reactivated_365 / total_transaction * 100, 1), '%') AS "Reactivated (no purchase for 365D+)",
CONCAT(ROUND((new_transaction + reactivated_365) / total_transaction * 100, 1), '%') AS "1st time + Reactivated (365D+)"
FROM reactivated;
Step-by-Step Instructions
    Open the SQL Editor
From your Triple Whale dashboard:
  • Click More on the left-hand menu
  • Select SQL
    Paste and Run the Query
Once inside the SQL editor:
  • Paste the full query below
  • Click Run
Before running, review the attribution model and window info at the bottom of this page to ensure consistency with your preferred setup.
    Export the Results
Once the data loads:
  • Click the three-dot menu in the top right corner of the table
  • Select Export > CSV
Output Breakdown
Your query output will include:
  • % of Total Customers Reactivated based on your definition of reactivated customers
  • Adjusted NC ROAS and NC %, showing the lift from including reactivated customers
  • Daily performance breakdown by campaign and spend

Important Notes

Attribution Window
This query uses a default attribution window of '7_days'.
If you prefer a different window, replace all instances of '7_days' with one of the following:
  • '1_day'
  • '14_days'
  • '28_days'
  • 'lifetime'
Attribution Model
This query uses 'Triple Attribution' as the default attribution model.
You can replace it with any of the following if needed:
  • 'Last Click'
  • 'First Click'
  • 'Linear All'
  • 'Linear Paid'
  • 'Total Impact'
You're All Set!
Use this analysis to better understand how many customers are reactivating through your campaigns — and how that impacts your new customer performance metrics.