2

I have to filter a data extension on some customer attributes, but the names of these attribute-columns are not fix / can change. I know this is a crazy setup, but I'll heave to deal with this.

I have a products DE:

| PID | Price |      Name     | Weight |
----------------------------------------
| 111 |  3,99 | Produkt Alpha |  ...   |
| 222 | 12,00 | Produkt Beta  |  ...   |

And a customers DE, like this

| email | likesProductA | likesProductB |
-----------------------------------------
|  .@.  |    true       |      false    |
|  .@.  |    false      |      true     |

And additional a kind of reference table :

|    customer   | product |
---------------------------
| likesProductA |   111   |
| likesProductA |   222   |

My goal is, to have a target audience (100k contacts) in a new data extension: all customer that are interested in a certain product (e.g. "Produkt Alpha"), which relates to "likesProductA", but the name of this boolean attribute is not set and has to be established via the reference table.

My first attempt was to use an SSJS automation, but Rows.Retrieve is limited to 2500 rows and I think it's performace is not good enough anyway.

Thanks for any kind of advice!

4

I think you'll need to pivot your Boolean columns into row values first, then join on the other data extensions. The CROSS APPLY is the primary method of unpivoting that I know works in SFMC Query Activities.

The inner query unpivots the columns and the outer query provides a place to join the results to the reference and product data sets.

You should be able to update the WHERE clause to select just those folks that "like" a specific product.

select
x.email
, x.product
, x.likes
, p.pid
from (
    select
      cs.email
    , c.Product
    , c.Likes
    from [Customers] cs
    cross apply (
      select 'likesProductA', cs.likesProductA union all
      select 'likesProductB', cs.likesProductB 
    ) c (Product, Likes)
) x
inner join [ProductReference] pr on pr.product = x.Product
inner join [Products] p on p.pid = pr.pid
where p.pid = 111
and x.likes = 1

I modeled this in my SQL Server Management Studio. Here are the results:

full query

Here are the results from the inner query, so you can see what you'll need to join on in the outer part:

inner query

| improve this answer | |
  • Uh, great answer, thanks a lot for your effort! But there's still the problem I don't know the name of the columns in cross apply selects (meaning it could anything else but "likesProductA" for PID 111). The relation between pid and column has to be looked up dynamically in the reference table. This is where the real pain comes from... ; ) – Martin Cousin Mar 17 at 22:32
  • I was thinking that perhaps you can select the columns by ordinal value, but unfortunately, there's no access in SFMC to those system views that contain that data. – Adam Spriggs Mar 17 at 23:14
  • I see this Product1, Product2, Product3 schema a lot in SFMC. It's just lazy. And worse, you're incurring technical debt. Count on going back at a later date and having to copy a bunch of convoluted code to normalize the data -- like it should have been done in the first place. /rant – Adam Spriggs Mar 18 at 12:41

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.