Use measure in SQL

Question

I would like to use a measure in a SQL request.

Here is my SQL request that works :

SELECT
C.[Civilite] as 'Civilite',
C.[Prenom] as 'Prenom', 
C.[Nom] as 'Nom', 
C.[Email] as 'Email',
C.[SubscriberKey] as 'SubscriberKey',
C.[TopageEmail] as 'TopageEmail',
C.[OptinMail] as 'OptinMail'
FROM [Clients] C
WHERE 
C.[OptinMail]='True'
AND C.[TopageEmail]='False'

And I want to add the measure named "_Total Emails Opened Last 6 Months". But when I write

Left Join [_Total Emails Opened Last 6 Months] S on C.SubscriberKey = S.SubscriberKey

Here is the error message :

An error occurred while checking the query syntax. Errors: [_Total Emails Opened Last 6 Months] is not a known data extension or system data view. You can only query existing data extensions or system data views.

So how my question is : how can I use measure in SQL to filter ?

Answer 1

You cant.
SQL Queries are designed for Data Extensions and Data Views only. Measures are based on Lists.

The contents of your measure is also available in Data Views.

I hope this helps.

Answer 2

Technically you could use a measure in SQL if you wanted to. This would come with the stipulation that you would need to join data from your sendable data extension to the subscriberid/list related data that would be returned from a measure.

1. Create measure.
2. Use the measure in a data filter and populate a pre-defined data extension with the results from it that you want.
3. Use said data extension (created from steps 1/2) with the contents of the measure as it would now be a valid table to query against.

It is also of note that the contents of a measure may not be contained in a data view as they are structured differently from a SQL perspective. Some of the tables that the data views reference would, however, overlap.

I would agree that it would be better to just use the Data view that contained the open/click/etc. data that you were interested in.

Answer 3

Some good workarounds have been suggested already. Let me add that there's also plugins like DESelect that offer this through a UI (instead of having to master SQL and Data Views... ;-) ).