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)

(via HitReach, h/t GAS)

(via theblazingcenter)

To start, create a “CouponCodes”Data Extension with the following columns:

Next, generate your own list of unique coupon codes in Excel. Include a “CouponCode” column header. Save the worksheet as CSV or Unicode (tab-delimited).

Then, import your coupons into the data extension with the adhoc import button:

Finally, add this AMPScript to your email. This code will try to find a coupon row for the specified email address in the CouponCodes data extension. If it finds one, it’ll return the coupon code. If not, it will “claim” an unclaimed code and associate it with the specified email address. The bit about the message context — that will prevent you from claiming coupon rows when you’re doing a send preview when building the email.

%%[
var @couponRow, @couponCode, @rows, @row
set @rows = LookupRows("couponCodes","EmailAddress",emailaddr)

if rowcount(@rows) > 0 then
  set @row = Row(@rows,1)
  set @couponCode= field(@row,"couponCode")
else

  if _messagecontext == "PREVIEW" then
        set @couponCode = "XX TEST XX"
    else
    set @couponRow = ClaimRow("couponCodes","IsClaimed","EmailAddress",emailaddr) 
        set @couponCode= field(@couponRow,"couponCode") 

  endif 

endif

]%%

Here's your coupon code: %%=v(@couponCode)=%%

Keep these things in mind:

• Make sure you upload enough coupons for your target audience so you don’t run out of unclaimed coupons. For recurring coupon campaigns, you should build a system to monitoring unused coupon codes.
• Make sure the target of the coupon code (e-commerce site, POS system, etc) is using the same set of coupon codes as your campaign.
• You can also do this in an SMS campaign.

For further reading:

Data Extensions
Data Extension AMPScript Functions
MobileConnect Coupon Scenario
Live Offers