Page last modified 18:26, 20 Jul 2010 by Admin

Documentation Wiki > Activities > Query Activity

Query Activity

1. What Is a Query Activity
1. 1.1. Query
2. 1.2. Target
2. How to Create a Query Activity
3. SQL Support
4. Known Issues
5. Querying Data Extensions
6. Querying Subscriber Data
7. Performance Considerations
8. Sample Queries
1. 8.1. Samples
  1. 8.1.1. Get a Send List Based on Aggregate Row Count
  2. 8.1.2. Random Sample of 33 Percent Exclusion
  3. 8.1.3. Random Sample of 33 Percent
  4. 8.1.4. Random Sample of 100
  5. 8.1.5. Unique Values for a Data Set
  6. 8.1.6. Geo-Targeting
  7. 8.1.7. Pulling Tracking Information for a Triggered Send

What Is a Query Activity

A query is an activity to retrieve data extension or data view information that matches your criteria and include that information in a data extension. You use SQL to create the query you use in the query activity.

When you create a query activity, you provide name, external key, and description to identify and describe the activity within the application interface and for API calls.

The default timeout time for your SQL statements is 30 minutes. In certain circumstances, you may be able to request a different timeout time, up to 120 minutes. To request approval of an override timeout time, contact your account manager or customer service.

Query

When you create a query activity, you write the SQL statement that defines the query. You can write the query against any existing data extension or the data views provided in the system.

A Query Activity SQL statement is an isolated statement that cannot take input parameters from other activities or other processes.

If your account is a child account in an Enterprise, you can query data extensions in your parent account. Prefix the data extension name in the query with ENT. For example:

Select email from ent.customers

In this example, email is the column name and customers is the data extension residing in the parent account.

You can query the database for the following information:

All data view names are case-sensitive. You can retrieve up to six months' of data from all of the tables except _Jobs, which contains all data for the account. See the table layout for each data view you can query.

You can include AMPscript in the SQL statement of your query. See the AMPscript Guide for more information on using AMPscript.

The query activity interface provides a tool to check the syntax of your SQL.

If your query activity includes a SubscriberKey column, you can set that data type to Text unless the SubscriberKey is an email address. In that case, you can set the data type to Email.

Target

When you create a query activity you identify the data extension to contain the results of the query. You must create the data extension before you create the activity. You also indicate whether the system overwrites all of the rows in the data extension, updates the existing rows, or appends to the rows of the data extension.

How to Create a Query Activity

Before you begin this procedure, you must create the data extension to contain the results of the query. After you complete this procedure, you can start the activity or include the activity in a program.

Use the following steps to create a query activity:

1. Click the Interactions tab on the navigation bar.

2. Click Activities.

3. Click Query.

The Queries workspace appears.

4. Click Create from the toolbar.

5. Complete the information in the Properties section:

Name - The name of the activity. You use this name to identify the activity in the application. Subscribers cannot see the name.

Key - A value you choose that uniquely identifies the activity. You use this value to identify the activity when using the API.

Description - The description of the activity. You use this description to further help identify the activity within the application. Subscribers cannot see the description.

Query - The SQL that makes up the query. You can click the Check Syntax button to check your SQL.

6. Complete the information in the Target section:

Select data extension to populate - The data extension to contain the result of the query.
Update Type - Determines how the system updates the data extension with new data. Value values include:

. Overwrite - The system deletes the existing records in the data extension and adds the results of the query.

. Update - The system updates the existing records in the data extension with information that results from the query.

. Append - The system adds the results of the query to the data extension after the existing records.

7. Click Save.

SQL Support

The SQL support for the Query Activity is based on SQL Server 2005 capabilities.

Only SELECT statements to data extension or data views in an account or in the parent account
- Nested Queries
- UNION
- JOIN
- GROUP BY
Conditional Statements
- IF Constructs
- CASE Statements
Functions
- Most functions (i.e. MIN, MAX, etc) are supported
- CAST and CONVERT
Unsupported elements
- Variables
- Cursors
- User Defined Functions
- Transaction and Locking
- GOTO
- PRINT
- Any sp_* stored procedure
- EXEC
- Temporary Tables and Common Table Expressions
- TEXT and IMAGE Functions

Known Issues

Data Types
- Decimal: If a Data Extension contains a decimal field and that field is used in a query, the output will not be a decimal output
Data Views and Enterprise 2.0 Business Units
- Only data extensions are accessible for queries written at the Enterprise 2.0 business unit

Querying Data Extensions

In an enterprise (standard or 2.0) environments, you can decorate the data extension name with a "ENT." prefix to get access to data extension that reside in the enteprise parent account.
A shared data extension in an Enterprise 2.0, should be accessed using the "ENT." prefix.
Use the data extension Name in SQL queries not the External Key.

Querying Subscriber Data

Using the _Subscriber data view

SELECT *
from _subscribers

Using the _EnterpriseAttribute data view

SELECT *
from _EnterpriseAttribute

This query can be used in Enterprise 2.0 edition accounts only. Core, Advanced, or Enterprise do not support querying subscriber attributes.

Combining the _EnterpriseAttribute data view and the _Subscriber data view

SELECT
b.[first name],
b.[last name],
b.[age],
a.[Status] as 'Subscription Status',
a.[EmailAddress] as 'Email'
FROM
_subscribers a
INNER JOIN
_EnterpriseAttribute b
ON
a.subscriberID = b._subscriberID

This example assumes the [first name],[last name], and [age] attributes exist in the account
in which the query is run. You could choose to query for any attribute that you created in your own account.

Performance Considerations

Queries have a 30 minute timeout. If a query is timing out, it may be possible to make the query perform faster with indexes applied. Contact customer support if you believe we would like to have your query validated by our internal teams for performance.

Sample Queries

Sample queries that demonstrate the possible functionality of the query activity.

Samples

Get a Send List Based on Aggregate Row Count

Use Case: Generating a Sendable list from a detailed data set

select visitorid from [cart abandonment segment] group by visitorid having count(visitorid) > 1

Random Sample of 33 Percent Exclusion

Use Case: A/B split testing and general segmentation

Select top 33 percent

offerid,score,name,description,url

from Offers

inner join Offer_Ext1 a on a.offerid <> offers.offerid
order by newid()

Random Sample of 33 Percent

Use Case: A/B split testing and general segmentation

Select top 33 percent

offerid,score,name,description,url

from Offers
order by newid()

Random Sample of 100

Use Case: A/B split testing and general segmentation

Select top 100

offerid,score,name,description,url

from Offers
order by newid()

Unique Values for a Data Set

Use Case: Testing

Select distinct
'109406145' as linkage_id,
'109406145' as ticket_id,
'28480048' as family_id,
'73334' as merchant_id,
'25.32' as Original_Transaction_Amt,
transaction_type_cd,
Max(TRANSACTION_DATE) as Transaction_Date,
TRANSACTION_STATUS,
MERCHANT_TYPE,
'0' as Group_Points,
'.00' as Group_Comp,
'.00' as Group_Credit,
'.00' as Group_Dues,
'0' as Group_Miles,
'.00' as Group_BonusComp,
'102' as Group_BonusMiles,
'0' as Group_BonusPoints,
'.00' as Group_BonusCrdt,
'.00' as Total_Comp_Earned,
'.00' as Total_Credit_Earned, 
'10' as Total_Miles_Earned,
'103' as Total_Points_Earned,
MERCHANT_CATEGORY,
RA_SEQUENCE,
'FFAK' as fam_campaign_cd

FROM Subscriber_Activity

GROUP BY transaction_type_cd,  TRANSACTION_STATUS, MERCHANT_TYPE,  RA_SEQUENCE,  MERCHANT_CATEGORY

Geo-Targeting

Prerequisites: Zip Code Data Extension. See attached zipcode.csv file.

Todo: Join Zip Codes to a Subscriber Data Extension

Use Case: Find all cities/zips (and therefore subscribers) within 15 kilometers of a zip code.

-- 6378.137 earth circumference

SELECT   
  city,   
  zip,   
  ROUND(6378.137 * ACOS(     
    CASE       
      WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 46254)))) > 1 THEN 1       
      WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 46254)))) < -1 THEN -1       
      ELSE (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 46254))))     
  END),0) AS Distance 
FROM   
  [ZipCode] AS geo 
WHERE   
  ROUND(6378.137 * ACOS(     
    CASE       
      WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 46254)))) > 1 THEN 1       
      WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 46254)))) < -1 THEN -1         
      ELSE (SIN(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] where zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] where zip = 46254))))     
    END),0) <= 15

Pulling Tracking Information for a Triggered Send

Use Case: Viewing available tracking information regarding a triggered send

SELECT *
FROM _Open 
WHERE TriggeredSendCustomerKey = 'External Key of Triggered Send'

This page was last updated by Ryan Williams. To send Ryan direct feedback, fill out the form below:

Retrieved from "http://wiki.memberlandingpages.com/Activities/Query_Activity"

Tag page: Edit tags
What links here

Files 1

Attach file or image

	Size	Date	Attached by
zipcode.csv No description	2.38 MB	18:26, 20 Jul 2010	Admin	Actions
zipcode.csv No description	2.38 MB	13:30, 22 Dec 2009	Admin
zipcode.csv No description	2.38 MB	21:37, 16 Dec 2009	Admin
zipcode.csv No description	2.38 MB	04:13, 23 Jun 2009	Admin
zipcode.csv No description	2.38 MB	19:23, 15 May 2009	Admin
zipcode.csv No description	2.38 MB	13:01, 4 May 2009	Admin
zipcode.csv No description	2.38 MB	16:07, 22 Apr 2009	Admin

Images 0

You must login to post a comment.

Query Activity

Table of contents

What Is a Query Activity

Query

Target

How to Create a Query Activity

SQL Support

Known Issues

Querying Data Extensions

Querying Subscriber Data

Using the _Subscriber data view

Using the _EnterpriseAttribute data view

Combining the _EnterpriseAttribute data view and the _Subscriber data view

Performance Considerations

Sample Queries

Samples

Get a Send List Based on Aggregate Row Count

Random Sample of 33 Percent Exclusion

Random Sample of 33 Percent

Random Sample of 100

Unique Values for a Data Set

Geo-Targeting

Pulling Tracking Information for a Triggered Send

Files 1

Images 0

Table of contents





Enter the digits 24440 backwards: