First of all, it means that subscriber statuses are not simply active or unsubscribed, which is sometimes difficult to explain to marketers — especially when they are scrutinizing subscriber reporting and statistics. There are a couple of pages that on the ExactTarget help site that outline their bounce management process. The one that I’ve found most helpful is this one:

If you’re just looking for an overview, here’s a 3Sixty video tutorial on the subject.

Keep in mind that there are different kinds of unsubscribes:

• list unsubscribe
• master unsubscribe
• global unsubscribe

Here is more info on that.

An NPR Tiny Desk Concert In 223 Takes

Dwight says:

h/t reddit.com/r/nba

However, in the ET Help page for Import Activities, you’ll notice that there are more listed:

%%Year%%
%%Month%%
%%Day%%
%%Hour%%
%%Minute%%
%%Second%%

So if you need some more granularity in files you’re uploading to your ET FTP share, yes, you can use minutes and seconds.

Don’t forget about the case-sensitivity disclaimer. Because ET utilizes non-Microsoft CDNs for FTP and image hosting, your file name specifications must match exactly.

For further reading:

Import Activity
File Transfer Activity
Programs

Joe Bill Spriggs, Bloomington High School, Bloomington, Indiana (ca 1928-1931)

Found this picture of my Granddad (1913-1985) when going through photo albums after my Grandmother died back in October.

Grandmother told me he played against John Wooden from Martinsville, IN in the late 1920′s.

If anyone knows his FT%, please let me know.

via Tastefully Offensive

via neatorama

via Devour

select
de.createdDate
from [dataExtension] de
where 
dateadd(dd,datediff(dd,0,de.createdDate), 0) = 
dateadd(d, -1, dateadd(m,datediff(m,0,dateadd(dd,datediff(dd,0,getDate()), 0))+1,0))

The last portion of the where clause finds the first day of the next month and then subtracts one day.

Normally queries like this run in a daily automation.

For further reading:

• ExactTarget Query Activity
• T-SQL DATEDIFF function
• T-SQL DATEADD function
• SQL-Server find the last day of any month

via viralviralvideos

via likecool

via viralviralvideos

select emailAddress from [dataExtension] 
where datediff(day, [createdDate], getdate()) between 1 and 30

or

select emailAddress from [dataExtension] 
where datediff(day, getdate() , [createdDate]) between -30 and -1

or between two static dates:

select emailAddress from [dataExtension] 
where datediff(day, getdate(), [createdDate]) between datediff(day, getdate(), '2013-03-19') AND datediff(day, getdate(), '2013-04-19')

For further reading:

• ExactTarget Query Activity
• T-SQL DATEDIFF function

Here’s an example:

select
getdate() [date with time]
, dateadd(dd,datediff(dd,0,getdate()), 0) [date without time]

The first column returns the full date and time, the second column of formulas strips the time portion.

For further reading:

• ExactTarget Query Activity
• What’s the BEST way to remove the time portion of a datetime value (SQL Server)?
• How to return the date part only from a SQL Server datetime datatype

(via Lifehacker)

Yes, I’m still bummed about Google Reader closing up on July 1, however, Feedly is growing on me. Unless Digg makes a big splash, I’m planning on sticking with them.

I like:

• I like the sharing features.  I’ll like it even more when the bring the full Gmail integration back.
• The Android app is actually pretty good.  I keep exiting accidentally.
• The Feedly team is listening to users and making improvements.  That kinda thing goes a long way toward adoption, IMO.
• I’m using the Magazine view on a few feed folders.  Didn’t think I would, but I have some image heavy feeds that it fits well.
• Subscribing and organizing feeds is better than Google Reader.  Must read is a nice feature.
• I like the visibility of the “You Might Also Like” in the right column.  That’s something that’s buried in Google Reader.
• I was concerned about how sluggish the site was when I decided it to give it a second look.  It’s gotten a lot better.  The site handles my 240+ feeds pretty well considering the influx of new users.

How about you?  Like it?

via GeekTyrant

You’ll need:

• A subscriber data extension that includes a zip code column
• A store data extension that includes a zip code column
• A zip code data extension that includes X, Y, and Z-axis columns
• A query that writes to a new data extension

Subscriber Data Sample

Store Location Data Sample

Zip code Data Sample

Zip	Latitude	Longitude	XAxis	YAxis	ZAxis
11111	45.429654	-65.982426	0.28563807	-0.64102440	0.71238935
11112	45.427139	-65.995344	0.28550626	-0.64111735	0.71235855
11113	45.427138	-65.995344	0.28550627	-0.64111736	0.71235853

I’ve seen people simply store the latitude and longitude for each zip and then calculate the X, Y, and Z values in the SQL query. However, I prefer calculating those columns beforehand. The queries will be faster and cleaner.

Target Data Extension

EmailAddress	FirstName	Zip	Store_Zip	Store_Phone	Distance
neo@matrix.com	Neo	11111	11115	555-222-2222	4

You can certainly add more store-related columns to this data extension. For simplicity, I only included a few columns.

Geolocation Query

This query pulls it all together. The round function and case statements do the dirty work of the calculations between the two zip codes. The ranking and partition part picks out the closest store per subscriber.

SELECT
emailAddress
, firstName
, zip
, store_zip
, store_phone
, dense_rank() 
     over ( 
           partition by emailAddress 
           order by emailAddress, distance
          ) as ranking
FROM (
  SELECT 
    subscriber.emailAddress
    , subscriber.firstName
    , subscriber.zip
    , store.store_zip
    , store.store_phone
    , ROUND(3961 * ACOS(     
        CASE       
          WHEN (pc1.XAxis * pc2.XAxis + pc1.YAxis * 
                pc2.YAxis + pc1.ZAxis * pc2.ZAxis) > 1 THEN 1       
          WHEN (pc1.XAxis * pc2.XAxis + pc1.YAxis * 
                pc2.YAxis + pc1.ZAxis * pc2.ZAxis) < -1 THEN -1       
          ELSE (pc1.XAxis * pc2.XAxis + pc1.YAxis * 
                pc2.YAxis + pc1.ZAxis * pc2.ZAxis)     
        END),0) distance
  FROM [subscriber_master_list] subscriber
  INNER JOIN [zip_axis] pc1 ON (subscriber.zip = pc1.zip) 
  INNER JOIN [store_locations] store
  INNER JOIN [zip_axis] pc2 ON (store.zip = pc2.zip) 
    on
    ROUND(3961 * ACOS(     
        CASE       
          WHEN (pc1.XAxis * pc2.XAxis + pc1.YAxis * 
                pc2.YAxis + pc1.ZAxis * pc2.ZAxis) > 1 THEN 1       
          WHEN (pc1.XAxis * pc2.XAxis + pc1.YAxis * 
                pc2.YAxis + pc1.ZAxis * pc2.ZAxis) < -1 THEN -1       
          ELSE (pc1.XAxis * pc2.XAxis + pc1.YAxis * 
                pc2.YAxis + pc1.ZAxis * pc2.ZAxis)     
        END),0) <= 10 
  WHERE subscriber.zip is not null
) rankedRows
WHERE ranking = 1

For further reading:
Data Extensions
Query Activities
Import Activities
Microsoft Transact-SQL (T-SQL) Reference
Distances between Zips (for verification)