Guides

Frequency Cap and Send Limit Queries

Abandonment + Catalog Solution 

SELECT
  x.ContactKey,
  x.SeriesGroup,
  x.SeriesType,
  x.SeriesStartDate,
  GETDATE() AS LogCreatedDate
FROM (
  
  SELECT DISTINCT
    s.SubscriberKey AS ContactKey,
    
    /* ---- SERIES GROUP MAPPING ---- */ 
    CASE 
      WHEN sl.WkPurpose IN ( 
        'cart abandonment', 'product abandonment', 'category recap' 
      ) THEN 'Abandonment' 
      
      WHEN sl.WkPurpose IN ( 
        'low stock', 'back in stock', 'price drop' 
      ) THEN 'Catalog' 
    END AS SeriesGroup,

    /* ---- CAMPAIGN MAPPING ---- */
    CASE
      WHEN sl.WkPurpose = 'cart abandonment'    THEN 'Cart'
      WHEN sl.WkPurpose = 'product abandonment' THEN 'Product'
      WHEN sl.WkPurpose = 'category recap'      THEN 'Category'
      WHEN sl.WkPurpose = 'low stock' THEN 'LowStock'
      WHEN sl.WkPurpose = 'back in stock' THEN 'BackInStock'
      WHEN sl.WkPurpose = 'price drop' THEN 'PriceDrop'
    END AS SeriesType,
    s.EventDate AS SeriesStartDate
    
  FROM [Sendlog] sl 

  INNER JOIN _Sent s  
    ON  sl.JobID   = s.JobID
    AND sl.ListID  = s.ListID
    AND sl.BatchID = s.BatchID
    AND sl.SubID   = s.SubscriberID

  WHERE sl.WkSeriesStart = 'Y'
    AND sl.WkPurpose IN (
      'cart abandonment','product abandonment','category recap', 
      'low stock','back in stock','price drop'
    ) 
    AND s.EventDate >= DATEADD(DAY, -5, GETDATE())
) x

LEFT JOIN [wknd_email_series_send_log] existing
  ON  existing.ContactKey      = x.ContactKey
  AND existing.SeriesType      = x.SeriesType
  AND existing.SeriesStartDate = x.SeriesStartDate
WHERE existing.ContactKey IS NULL
SELECT
  l.ContactKey, 

  /* ================================================================
     ABANDONMENT ELIGIBILITY
     ================================================================ */
  CASE 
    WHEN
      SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesType = 'Cart' 
            AND l.SeriesStartDate >= p.AbandonmentSeriesShortLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentSeriesShortLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesType = 'Cart' 
            AND l.SeriesStartDate >= p.AbandonmentSeriesLongLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentSeriesLongLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesStartDate >= p.AbandonmentGroupShortLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentGroupShortLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesStartDate >= p.AbandonmentGroupLongLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentGroupLongLimit)

    /* ---- Calculate above to set value for CartEligible ---- */
    THEN 1 ELSE 0 
  END AS CartEligible,

  CASE 
    WHEN
      SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesType = 'Product' 
            AND l.SeriesStartDate >= p.AbandonmentSeriesShortLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentSeriesShortLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesType = 'Product' 
            AND l.SeriesStartDate >= p.AbandonmentSeriesLongLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentSeriesLongLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesStartDate >= p.AbandonmentGroupShortLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentGroupShortLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesStartDate >= p.AbandonmentGroupLongLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentGroupLongLimit)
    
    /* ---- Calculate above to set value for ProductEligible ---- */
    THEN 1 ELSE 0 
  END AS ProductEligible,

  CASE 
    WHEN
      SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesType = 'Category' 
            AND l.SeriesStartDate >= p.AbandonmentSeriesShortLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentSeriesShortLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesType = 'Category' 
            AND l.SeriesStartDate >= p.AbandonmentSeriesLongLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentSeriesLongLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesStartDate >= p.AbandonmentGroupShortLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentGroupShortLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Abandonment'
            AND l.SeriesStartDate >= p.AbandonmentGroupLongLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.AbandonmentGroupLongLimit)

    /* ---- Calculate above to set value for CategoryEligible ---- */
    THEN 1 ELSE 0 
  END AS CategoryEligible,

  /* ================================================================
     CATALOG ELIGIBILITY
     ================================================================ */

  CASE 
    WHEN
      SUM(
        CASE
          WHEN l.SeriesGroup = 'Catalog'
            AND l.SeriesType = 'LowStock' 
            AND l.SeriesStartDate >= p.CatalogLowStockLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.CatalogLowStockLimit)

      AND SUM(
        CASE
          WHEN l.SeriesGroup = 'Catalog'
            AND l.SeriesStartDate >= p.CatalogGroupLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.CatalogGroupLimit)
    
    /* ---- Calculate above to set value for LowStockEligible ---- */
    THEN 1 ELSE 0 
  END AS LowStockEligible,

  CASE 
    WHEN
      SUM(
        CASE
          WHEN l.SeriesGroup = 'Catalog'
            AND l.SeriesStartDate >= p.CatalogGroupLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.CatalogGroupLimit)
    
    /* ---- Calculate above to set value for BackInStockEligible ---- */
    THEN 1 ELSE 0 
  END AS BackInStockEligible,

  CASE 
    WHEN
      SUM(
        CASE
          WHEN l.SeriesGroup = 'Catalog'
            AND l.SeriesStartDate >= p.CatalogGroupLookbackStart
          THEN 1 ELSE 0
        END
      ) < MAX(p.CatalogGroupLimit)

    /* ---- Calculate above to set value for PriceDropEligible ---- */
    THEN 1 ELSE 0 
  END AS PriceDropEligible,

  GETDATE() AS LastRefreshed

FROM [wknd_email_series_send_log] l

CROSS JOIN (
    SELECT

      /* ABANDONMENT PARAMETERS */
  
      /* Same abandonment series: 1 per 7 days */
      1 AS AbandonmentSeriesShortLimit,
      DATEADD(DAY, -7, GETDATE()) AS AbandonmentSeriesShortLookbackStart,
  
      /* Same abandonment series: 2 per 30 days */
      2 AS AbandonmentSeriesLongLimit,
      DATEADD(DAY, -30, GETDATE()) AS AbandonmentSeriesLongLookbackStart,
  
      /* All abandonment series: 2 per 7 days */
      2 AS AbandonmentGroupShortLimit,
      DATEADD(DAY, -7, GETDATE()) AS AbandonmentGroupShortLookbackStart,
  
      /* All abandonment series: 4 per 30 days */
      4 AS AbandonmentGroupLongLimit,
      DATEADD(DAY, -30, GETDATE()) AS AbandonmentGroupLongLookbackStart,
  
      /* CATALOG PARAMETERS */
  
      /* Low Stock: 2 per 7 days */
      2 AS CatalogLowStockLimit,
      DATEADD(DAY, -7, GETDATE()) AS CatalogLowStockLookbackStart,
  
      /* All Catalog series: 4 per 14 days */
      4 AS CatalogGroupLimit,
      DATEADD(DAY, -14, GETDATE()) AS CatalogGroupLookbackStart
) p

WHERE
  (
    l.SeriesGroup = 'Abandonment'
    AND l.SeriesStartDate >= p.AbandonmentGroupLongLookbackStart
  )
  OR (
    l.SeriesGroup = 'Catalog'
    AND l.SeriesStartDate >= p.CatalogGroupLookbackStart
  )

GROUP BY
    l.ContactKey

Abandonment-Only Solution

SELECT
    x.ContactKey,
    x.SeriesGroup,
    x.SeriesType,
    x.SeriesStartDate,
    GETDATE() AS LogCreatedDate
FROM (
    SELECT DISTINCT
        s.SubscriberKey AS ContactKey,

        /* ---- SERIES GROUP MAPPING ---- */
        CASE
            WHEN sl.WkPurpose IN (
                'cart abandonment',
                'product abandonment',
                'category recap'
            ) THEN 'Abandonment'
        END AS SeriesGroup,

        /* ---- CAMPAIGN MAPPING ---- */
        CASE
            WHEN sl.WkPurpose = 'cart abandonment' THEN 'Cart'
            WHEN sl.WkPurpose = 'product abandonment' THEN 'Product'
            WHEN sl.WkPurpose = 'category recap' THEN 'Category'
        END AS SeriesType,

        s.EventDate AS SeriesStartDate

    FROM [Sendlog] sl

    INNER JOIN _Sent s
        ON sl.JobID = s.JobID
        AND sl.ListID = s.ListID
        AND sl.BatchID = s.BatchID
        AND sl.SubID = s.SubscriberID

    WHERE sl.WkSeriesStart = 'Y'
        AND sl.WkPurpose IN (
            'cart abandonment',
            'product abandonment',
            'category recap'
        )
        AND s.EventDate >= DATEADD(DAY, -5, GETDATE())
) x
LEFT JOIN [wknd_email_series_send_log] existing
    ON existing.ContactKey = x.ContactKey
    AND existing.SeriesType = x.SeriesType
    AND existing.SeriesStartDate = x.SeriesStartDate
WHERE existing.ContactKey IS NULL
SELECT
    l.ContactKey,

    /* ================================================================
       CART ELIGIBILITY
       ================================================================ */
    CASE WHEN
        SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesType = 'Cart'
                    AND l.SeriesStartDate >= p.AbandonmentSeriesShortLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentSeriesShortLimit)

        AND SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesType = 'Cart'
                    AND l.SeriesStartDate >= p.AbandonmentSeriesLongLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentSeriesLongLimit)

        AND SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesStartDate >= p.AbandonmentGroupShortLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentGroupShortLimit)

        AND SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesStartDate >= p.AbandonmentGroupLongLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentGroupLongLimit)

    THEN 1 ELSE 0 END AS CartEligible,


    /* ================================================================
       PRODUCT ELIGIBILITY
       ================================================================ */
    CASE WHEN
        SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesType = 'Product'
                    AND l.SeriesStartDate >= p.AbandonmentSeriesShortLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentSeriesShortLimit)

        AND SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesType = 'Product'
                    AND l.SeriesStartDate >= p.AbandonmentSeriesLongLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentSeriesLongLimit)

        AND SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesStartDate >= p.AbandonmentGroupShortLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentGroupShortLimit)

        AND SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesStartDate >= p.AbandonmentGroupLongLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentGroupLongLimit)

    THEN 1 ELSE 0 END AS ProductEligible,


    /* ================================================================
       CATEGORY ELIGIBILITY
       ================================================================ */
    CASE WHEN
        SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesType = 'Category'
                    AND l.SeriesStartDate >= p.AbandonmentSeriesShortLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentSeriesShortLimit)

        AND SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesType = 'Category'
                    AND l.SeriesStartDate >= p.AbandonmentSeriesLongLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentSeriesLongLimit)

        AND SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesStartDate >= p.AbandonmentGroupShortLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentGroupShortLimit)

        AND SUM(
            CASE
                WHEN l.SeriesGroup = 'Abandonment'
                    AND l.SeriesStartDate >= p.AbandonmentGroupLongLookbackStart
                THEN 1 ELSE 0
            END
        ) < MAX(p.AbandonmentGroupLongLimit)

    THEN 1 ELSE 0 END AS CategoryEligible,

    GETDATE() AS LastRefreshed

FROM [wknd_email_series_send_log] l

CROSS JOIN (
    SELECT
        /* Same Abandonment series: 1 per 7 days */
        1 AS AbandonmentSeriesShortLimit,
        DATEADD(DAY, -7, GETDATE())
            AS AbandonmentSeriesShortLookbackStart,

        /* Same Abandonment series: 2 per 30 days */
        2 AS AbandonmentSeriesLongLimit,
        DATEADD(DAY, -30, GETDATE())
            AS AbandonmentSeriesLongLookbackStart,

        /* All Abandonment series: 2 per 7 days */
        2 AS AbandonmentGroupShortLimit,
        DATEADD(DAY, -7, GETDATE())
            AS AbandonmentGroupShortLookbackStart,

        /* All Abandonment series: 4 per 30 days */
        4 AS AbandonmentGroupLongLimit,
        DATEADD(DAY, -30, GETDATE())
            AS AbandonmentGroupLongLookbackStart
) p

WHERE
    l.SeriesGroup = 'Abandonment'
    AND l.SeriesStartDate >= p.AbandonmentGroupLongLookbackStart

GROUP BY
    l.ContactKey

Updated 10 days ago

Did this page help you?