0

I have a campaign to email customers when the products are back in stock that is in 3 stages. First stage is "Confirmation" where you receive an email once you make a request for a product that is not in stock. Second is "InStock" when you receive an email once the product is back in stock. And third is "Discontinued" when you receive an email if the product will never be back in stock.

I want to find out the customers that have made a request so they received the confirmation email but haven't received the instock email yet.

I can't think of how I could tackle this on so if you can please suggest me a start I would really appreciate

Data extension

enter image description hereenter image description here

0

Assuming that this DE is append only and each time the email (one of the three stages) is sent out, it is added into this DE. (I am basing this off that your DE has no pkeys)

Then it actually shouldn't be as hard as you think. You would just do a Not Exists on itself to remove any records that also have a 'Stage Two' and/or 'Stage Three'.

See below for sample SQL:

SELECT a.SubscriberKey,
a.EmailAddress,
a.EType,
.....
FROM [DataExtension] a
WHERE NOT EXISTS(
  SELECT TOP 1 b.SubscriberKey
  FROM [DataExtension] b
  WHERE a.SubscriberKey = b.SubscriberKey
  AND (
    b.EType = 'InStock'
    OR b.EType = 'Discontinued'
  )
)

This will output only Subscribers that currently have the 'Confirmation' EType, without including any that also have 'InStock' or 'Discontinued' as well.

For example, targeting the below DE:

enter image description here

will output: enter image description here

Now to further narrow this down by product and not just Subscriber, you may need to also include things like ProductID, etc. in your query. See below for sample of that:

SELECT a.SubscriberKey,
a.EmailAddress,
a.EType,
a.ProductID
FROM [DataExtension] a
WHERE NOT EXISTS(
  SELECT TOP 1 b.SubscriberKey, b.ProductID
  FROM [DataExtension] b
  WHERE a.SubscriberKey = b.SubscriberKey
  AND a.ProductID = b.ProductID
  AND (
    b.EType = 'InStock'
    OR b.EType = 'Discontinued'
  )
)

As always, you will need to watch your volume to keep it under the 30 minute time out. If you do face a timeout, you will likely need to split this into multiple queries with intermediary tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.