SELECT * FROM ( SELECT RANK() OVER (PARTITION BY A.EMAIL_ADDRESS_ ORDER BY A.CREATED_DATE_) RNK, A.* FROM $A$ A JOIN (SELECT EMAIL_ADDRESS_ FROM $A$ GROUP BY EMAIL_ADDRESS_ HAVING count(EMAIL_ADDRESS_) > 1) B ON (A.EMAIL_ADDRESS_ = B.EMAIL_ADDRESS_) ) WHERE RNK <> 1

The Jewelry Television iPhone and Android apps are now available in their respective app marketplaces. Feel free to go download them and have a look while you read through my brief overview.

Take a look at the home screen below. It features some banner space, a live refreshing current item on air, links to recent items shown on TV, and a featured item category.

JTV App Home Screen

We also included the ability to watch JTV live through the app, as long as you are connected to 3G or wi-fi. This section features the current item which refreshes below the video, so you can tap the item and buy it if you so desire.

The live broadcast streaming within the app.

Let me say that writing an app for a home shopping network provides unique challenges that most app developers don’t have to contend with, namely, streaming video of your live broadcast. Getting this right and working has been a fairly complex challenge filled with consideration for all sorts of devices, connection speed, and bit rate scenarios to make sure that its available to as many devices as possible and looks good at the same time.

We also included some non-live video in our video library section. Here we have pre-defined categories of video that we can update on the fly for app users.

The video library section of the JTV app.

Education is a big part of what Jewelry Television stands for, and the Gemopedia section features information about gemstones, including pictures and video, and is a convenient little gemstone guide you can keep in your pocket.

The Gemopedia, a mobile gemstone guide.

We also provide some tools in the app, such as a ring sizer, program guide, and channel finder that uses your GPS to tell you what channel Jewelry Television is on in your immediate area.

The JTV Channel Finder

The JTV Ring Sizer

Of course the app wouldn’t be complete without the ability to shop. We have a full shopping experience built in to the app, where you can browse the roughly 30,000 jewelry and gemstone items on JTV.com and purchase, right from your mobile device.

Shopping inside the JTV app.

All in all the app is a solid version 1.0. Keep an eye out for more features rolling out soon.

Download the apps: JTV iPhone App

QR Code for the Android App

QR Code for Android App

Since Titanium allows you to write code in Javascript, you can write functions to do a bunch of stuff for you and make writing your app much easier. I wrote a simple timer function that can be used to fire other events within your app. This might be useful for games, cooking apps, etc.

So here’s the code:

function setTimer(timetowait,context) {

mt=0;
mtimer = setInterval(function() {

    	mt++;
	Ti.API.info(mt);

    if(mt==timetowait) {
        // do something;
	clearInterval(mtimer);
 	Ti.App.fireEvent(context);
        }

},1000);
};

So let’s break it down to understand what it does. First let’s look at the variable we pass into the function: timetowait and context

timetowait simply represents the number of seconds the timer should count to until you want your event to fire.

context is the name of the event that you want to fire when the timer reaches timetowait

Ti.API.info(mt) simply writes out the Titanium debug window the value of mt, which is our variable that increments every second. This is optional, but helpful in the development process to see exactly when the timer stops.

clearInterval(mtimer) tells the setInterval function in Titanium to essentially stop/reset.

Ti.App.fireEvent(context) fires the name of the event we passed in as the variable context.

Let’s look at a couple of examples of how this could be used. Let’s say we have a basic game where the player has to do something in a specific amount of time. We could use our setTimer function to time the game, and then show a game over alert message in the app:

//set the timer for 30 seconds, and after that show the game over message
setTimer(30, 'showGameOver');

//create an event listener for our Game Over alert
Ti.App.addEventListener("showGameOver", function() {
	alert("Game Over!");
});

That’s a pretty simple example. Let’s say we wanted to continuously loop the timer:

//set the timer for 30 seconds, and then call the timer function again
setTimer(30, 'loopTimer');

Ti.App.addEventListener("loopTimer", function() {

	//call the timer function again
	setTimer(30, 'loopTimer');
});

In both of these cases we can add as much complexity to our event listeners (the event that is fired when the timer ends) as we want…we could update the on-screen display, fire off multiple events, write data to the local database, load a remote file, check network connectivity, etc.

Timers can be used for all sorts of things in iPhone apps. Hope this helps someone get started using them.

There was this report regarding this mysterious Qualcomm chip order.

Then there’s AT&T’s comments about the Verizon iPhone (essentially saying the people are ‘stuck’ in their current contracts, so bring it on):

“Stephenson couldn’t dodge questions about the looming loss of AT&T’s exclusive iPhone deal. But he did play down its importance, saying that two thirds of iPhone owners were long-term AT&T users before they signed up for the iPhone service, saying they’re unlikely to switch. He threw out the stat that 80 percent of the iPhone base is either in a family talk plan or in a business relationship with AT&T, saying that those customers are ‘very sticky.’ Stephenson emphasized the “extended array” of smartphones Apple subscribers can pick from, which reads as AT&T saying it’s not too reliant on Apple” [Read more here]

The timing is perfect as well. How best to minimize the sales of Android devices during the holidays? Right before the holiday buying season, leak a story that you are selling the iPhone in January on Verizon. Coincidentally Apple’s annual event is in January, perfect to announce the availability. CES is also in January, which gives Verizon its podium to announce it as well.

Now the fact that Verizon is selling it’s iPad in Verizon stores is just the first step in that partnership.

Image via Wikipedia

If you think about it, Apple really doesn’t have an option – they have to find another carrier if they want to expand their market share. Google and the Android platform are eating Apple’s lunch right now in terms of growth, and long term handset projections are not pretty if you are Steve Jobs. Apple may have had the upper hand with their smartphone play early on, but all they did was spur the rapid adoption of these smart devices, essentially helping to make them mainstream. While they arguably have the best interface and are continuously ranked high in customer satisfaction, they cannot afford to sit out a round. They have to make a move in January 2011 or risk falling behind.

Update: Crunchgear today is reporting that Steve Wozniak, co-founder of Apple, has stated that he thinks that Android will eventually win the mobile platform race. Surely Steve Jobs is not going to stand for that!

Disclosure: I own shares of Apple stock.

Keeping a fresh list clean and free of ‘weeds’ is important to maintain quality scores and to your bottom line. If you are like a lot of companies, you have a set number of emails you can send in a year, so getting the most of those emails you do send is critical. That’s where our email list cleaning comes in.

Undoubtedly, you have subscribers to your email list that have stopped opening your emails, stopped clicking links, and in general have displayed no behavior that indicates they are interested in your product or service. Therefore, there’s no need to send emails to these people. Cleaning out these ‘weeds’ will leave more room for more ‘flowers’. Here’s a quick step-by-step list of how to clean out the ‘weeds’ from your garden.

1. Identify the Weeds – (i.e. the inactive subscribers on your list). Do this by creating a list of subscribers that did not open or click on any of your emails in the last 6 months. This will give you a list of subscribers who have been inactive, which we can then use for multiple purposes.

2. Create a ‘Last Chance’ Email – This email is designed to re-engage the inactive subscribers, by asking them to re-subscribe to your email list, or if they chose not to respond, be removed from your list entirely. They also have the option to unsubscribe themselves right then and there.

Note: You might ask why we would provide them the opportunity to unsubscribe so easily. Really, if the user chooses this route, you didn’t want them on your list to begin with. Remember, these are people who haven’t engaged with you in the last 6 months. If this is the only engagement you get, be thankful, because they have removed themselves from your list, and they are no longer inflating your active subscribers numbers.

This email should be designed to talk about what the subscriber would be missing out on if they decided not to continue to receive emails from you. Don’t clutter up the email with images – make it clear to the user they have 2 options. Here’s an example of a campaign I ran at DenTek.

An example of a no frills direct message to your subscriber.

As you can see, its obvious we are encouraging them to click the orange button and remain on our list by opting in again. Essentially, they aren’t really opting in, they are just registering a click on the email, so we can then track that click as an engagement by that user in our campaign dashboard later on. The opt-out button in this case is a one click unsubscribe, so the user is removed immediately from the list.

Tip: It’s important not to incentivize the user to click the opt-in button. Don’t offer them anything in the email for staying a subscriber. We are trying to determine their true feelings about being subscribed to your list, and do not wish to prolong their subscription with bribery. Instead, when the user decides to re-opt-in, that click should go to a page on your site that thanks them for sticking with you, and offers them some coupon, say 20% off their next purchase on your site. Never miss an opportunity to surprise a user with an offer, especially in this case where they may have forgotten why they were subscribed to your list in the first place. It’s a chance to make a second first impression of sorts. This approach rewards the the opt-in behavior, but on the backside of the user action, and affirms the user’s motives for re-opt-in was not based on the incentive.

3. Gather the Results – So now we have engaged those inactive users, and you’ll notice that some users have decided they really really like you so they clicked through the opt-in link, and others have decided to opt out. A majority of the users you tried to re-rengage, will indeed still be unengaged, and will not open or click this email either. Shocking I know! These actions will determine our final filter list against which we can filter for our regular email campaigns. Now run your collection again like in step 1 – gather all the non-openers and non-clickers into one list, and you have a list of subscribers who have not opened or clicked any of your emails in the last 6 months (including our re-engagement email). You now have your final ‘Inactives’ list.

4. Apply Your Inactive List To Your Campaigns – With your newly created Inactive List in hand, you now have some options. You can use it as an exclude filter when you send out campaigns, or you can use it to unsubscribe all of those users from your global subscriber list. If you have multiple lists you manage within one company, using the Inactive list on a per list basis as a filter may be the best option, as inactive users on one list may not be inactive on another list within the same company, for example.

5. Watch Your Open and Click-through Rates Spike – Once your remove the weeds, watch your flowers bloom! OK enough of the spring references. Seriously though, you will see a marked improvement. At DenTek, after we ran our re-engagement program, we saw open rates go as high as 40%, up from 10-15%. Click-through rates also doubled with our new clean list.

Keep in mind that this can be performed a few times a year, and it will keep your list nice and clean, full of active users – the kind who like to buy stuff.

In the world I work in on a daily basis, the CPG world, one thing is for sure: The way consumers get their CPG items is changing. While a vast majority of consumers still shop for toilet paper and toothpaste at their local Supercenter, more and more shoppers are finding that with a little planning, they can get the same goods without leaving their homes, and various online startups and established retailers and CPG manufacturers want to make it possible.

In the past I have written about Alice.com, a newer site dedicated to selling CPG directly to consumers. The whole idea behind Alice is to give CPG manufacturers a way to sell directly to consumers, and give consumers a convenient way to purchase those necessities on a regular schedule. Alice.com has enjoyed lots of positive press which has propelled interest, not to mention the company was founded by a couple of internet veterans with a successful track record.

Big online retailers have begun to transform their own online offerings, typically filled with higher ticket items like electronics, and added lower priced CPG items. Recently, Amazon.com has increased it efforts to bring on board CPG manufacturers to round out their offerings online. Walmart.com, who until very recently didn’t sell lower priced CPG items online, has begun to sell multi-packs of lower priced items to make them more viable to online shoppers who aren’t looking to pay $6 shipping for an item priced $2.99. Now you can buy a pack of 3 items, for example, to make your cart-to-shipping-cost ratio more acceptable. Target is in the middle of a major online redesign, so look for them to come at swinging on CPG items too when their new store comes online in 2011.

Sales and Marketing teams at major retailers aren’t the only one’s with their eye on the direct-to-consumer CPG sales. As a not-so-sublte push back on retailers who are exerting more and more control over CPG companies, the largest CPG manufacturer in the world, Proctor & Gamble, is dipping their big toe into the direct-to-consumer waters as well. Recently, P&G announced a ‘test’ where they would sell direct to consumers to try to learn about consumer buying habits for their brands, and all learnings would be shared with their retailers. Personally, I think what’s really happening is an experiment to see how viable selling direct to consumers is for them. If it proves successful, look for the ‘test’ to continue. The question remains how much P&G want to invest in their fulfillment operations to facilitate these sales, but most likely they will leave that to a third party to figure out if they ever get to the point where it becomes a viable sales channel for them.

Stay tuned as the race heats up to sell everyday items online. It could get interesting.

I had a real world use for this just today, where I needed a quick font match for an email, so I tried out the WTF machine. Here is what I did. First, I took a snippet of the font I wanted to identify, pictured here.

The snippet of font I wanted to identify.

On the WhatTheFont site I uploaded my sample and clicked the continue button, which then showed me a series of smaller images that represented the individual letters of my snippet that had been recognized by the tool, as seen below.

WhatTheFont recognizes individual letters in your snippet.

If WhatTheFont doesn’t correctly identify some of your letters, you can change the letter designation in the boxes below each letter image. Once you have confirmed that each letter breakout is correctly identified, then you can continue, after which WhatTheFont shows you what it thinks are the best matches for the font you are trying to identify.

WhatTheFont shows you the closest matches to your font.

And that’s about all there is to it. The service did pretty well matching up the font, but I haven’t tried any script fonts to see how well it performs with a larger challenge. If you use WTF, let me know how it works for you.

I decided that because of the potentially massive amount of data in my dropshipper’s XML feed file, that I should save the file to my server before attempting to process any data. I set up a cron job to use CURL to fetch the file. First I created a file called ‘importproducts.php’ and added the following code:

importproducts.php

//my dropshipper's feed URL - replace with your dropshipper's feed
$myFeed = 'http://www.mydropshipperssite.com/feedurl.xml';

// specify the name of the file you want to save
$myFilename = 'product_feed.xml';
	
	
	if ($myFeed != '') {
	//  Initialize the cURL session
	$ch = curl_init();
	curl_setopt($ch, CURLOPT_URL, $myFeed);
	//Create a new file
	$fp = fopen($myFilename, 'w');
	// Save to file
	curl_setopt($ch, CURLOPT_FILE, $fp);
	// Execute the cURL session
	curl_exec ($ch);
	//Close cURL session and file
	curl_close ($ch);
	fclose($fp);
	
	echo "Feed Imported Successfully!";
	
		
		//check the file size - if its 0 then my dropshipper didn't provide any updates, so don't let me know
		if (filesize($myFilename) > 0) {
		
			// send email to me when executed
			$to = "youremail@yourdomain.com";
			$subject = "Feed Imported";
			$body = "Hi,\n\nThe latest product data for YOURSITE has been downloaded on ";
			$body .= date('l jS \of F Y h:i:s A')."\n\n File Size: ".filesize($myFilename) . " bytes";
			
				if (mail($to, $subject, $body)) {
				  echo("
Message successfully sent!
");
					 } else {
				 echo("
Message delivery failed...
");
				}
			
		}

	
	} 

Now that we have this script written, we need to process the data. Before that, let me show you an example of the xml data I am working with in the xml file we just saved with the import script. Here is sample xml:

product_feed.xml

Now we can talk about the next file, the one that takes the XML data and uses it to update products in Magento via the API. We’ll name this file ‘processupdates.php’.

processupdates.php

//include the Magento API file - replace with your server path to the file
require_once('/some/path/to/public_html/app/Mage.php');

// ***** Configuration ******
$myDomain = 'http://www.yourdomain.com';
$myAPILogin = 'username';
$myAPIKey = 'password';

//calculate the price markup - useful if you have a standard markup you apply
$myMarkUp = .30;
$defaultMarkup = .30;

// get my saved file name
$myFilename = 'product_feed.xml';
	
//load up local xml file for processing
$feed_xml = simplexml_load_file($myFilename);

// Begin SOAP Requests
$client = new SoapClient($myDomain.'/api/?wsdl');
$session = $client->login($myAPILogin, $myAPIKey);

$updatedProducts = "";

//some counters - counting loops this way lets me see and set where the count increments
$x = 0;

//some filter date to pass to the API - add more to filter your results further - see Magento API docs
$filterData = array('type'=>'simple');

//get all my database products into an array
$products = $client->call($session, 'catalog_product.list', array($filterData));


//loop through my product array
foreach ($products as $product) {

echo "Starting product loop...

";


//get my database product sku - for cleaner reference in the code
$mysku = $product['sku']

//search directly in the product sku attribute in the xml for my sku
$res = $feed_xml->xpath("//product[@sku='$mysku']");


// if we find one, lets process it
if(!empty($res)) {

	//matched - make updates
	echo "Matched: ".$mysku;
	
	//price updates
	if ($res[0]->price['list'] != '') {
		$newprice = $res[0]->price['your_price'] + (($res[0]->price['list'] - $res[0]->price['your_price'])*$myMarkUp);
	} else {
		$newprice = $res[0]->price['your_price'] + ($res[0]->price['your_price']*$defaultMarkup);
	}
	
	//build my array to pass to the magento API
	$fieldPriceData = array('cost'=>$res[0]->price['your_price']*1, 'price'=>$newprice, 'upc'=>$res[0]->product['upc']);
	//update magento with price data
	$client->call($session, 'catalog_product.update', array($product['sku'], $fieldPriceData));
	
	//qty and stock updates
	if (($res[0]->inventory['quantity']*1) != 0) {
		$fieldQtyData = array('qty'=>$res[0]->inventory['quantity']*1, 'is_in_stock'=>1);
		echo "In Stock
";
	} else {
		$fieldQtyData = array('qty'=>$res[0]->inventory['quantity']*1, 'is_in_stock'=>0);
		echo "Out of Stock
";
	}
	
	//update magento with quantity and stock data
	$client->call($session, 'product_stock.update', array($product['sku'], $fieldQtyData));
	
	//record the updated product for emailing later
	$updatedProducts .= "SKU: ".$mysku." - ".$res[0]->description['short']."\nPrice: ".$newprice."\nCost: ".$res[0]->price['your_price']."\nQty: ".$res[0]->inventory['quantity']."\n\n";
	
	//increment my counter
	$x = $x + 1; 

} else {

echo "no match

";

}



// send mail to me
$to = "you@yourdomain.com";
$subject = "Feed Processed";
$body = "Hi,\n\nThe latest data for ".$myDomain." has been processed on ".date('l jS \of F Y h:i:s A')."\n\n";
$body .= "Products Updated: ".$x."\n\n".$updatedProducts;
if (mail($to, $subject, $body)) {
  echo("
Message successfully sent!
");
	 } else {
 echo("
Message delivery failed...
");
}

Now all you need to do is set up the cron jobs to call your import file at one interval, and your process file at another interval. Note that this works for me because I have a limited number of database products. If you have thousands of products you might want to have your site updating products by category, for example. If anyone has any better ideas on how to do any of this, please let me know. I always like to learn new stuff!