Sadly, Google is killing Google Reader on 01-July-2013. If you are reading this blog using the RSS feed via Google Reader, I suggest you start looking for another RSS reader. I have been trying feedly. It is ok, but so far I prefer Google reader. What is your favourite Google Reader alternative?
Online businesses generate a lot of data. Sales data, marketing data, traffic data etc. Pivot tables can be a useful tool for analysing and extracting useful information from this sea of data. A lot of people seem to have heard of Pivot Tables without understanding what they are or how to use them. Despite the slightly cryptic name they are really just a way of summarizing tables of data. Nothing to be intimidated by. I’m going to try to demystify them here with a simple example.
Let’s imagine you have got an Excel spreadsheet with a month of (fictitious) sales transaction. It looks like this:
You want to find out:
- The total value of sales of each product
- The number of sales of each product
- The total value of sales of each product by country
- The total value of sales of each product by day of the week
How would you do it? You can crunch the numbers using a calculator, but that is very tedious and error prone. If you are a programmer you can export the data to a .csv file and write a small program in your favourite language to read it in, crunch the data and write the results out. You probably think you can do it in 10 minutes, but I bet it will take you at least an hour to get it working and debugged. I did all the above in 1 minute and 20 seconds using Excel pivot tables.
Here is how you can do it (screenshots from Excel 2007). You can download the spreadsheet if you want to try it yourself.
Select all the data, including the header (A1 to D222).
Click on Insert in the Excel Ribbon bar.
Click on PivotTable.
The Create Pivot Table window will appear. Click OK.
The PivotTable Field List will appear. We want to find the number of sales for each product. Drag the product field to Column labels and the value field to Values. This gives us the total value of sales by product.
To change this to the number of sales pull down the Sum of value drop-down list and select Value Field Settings….
Change Summarize value field by from Sum to Count and click OK.
The number of sales of each product are now shown.
Now drag the country field to Row labels. Then click on Count of value, select Value Field Settings…, change it back to Sum and click OK. The total value of sales of each product by country is now displayed.
We now need to add a day of the week column. Click on Sheet 1 and add a new column as shown.
Now select all the data from A1 to E222 and insert a new pivot table, as before.
Drag the fields as shown to get the total value of sales of each product by day of the week.
(We’ll quietly ignore the fact that the number of days aren’t divisible by 7 in the data.)
We can even display by product, day of the week and country with one more drag.
Excel also supports pivot charts if you prefer your results in graphical form.
Obviously this is a very simple example, but pivot tables can be used to quickly analyse much larger and more complex data sets. Next time you have got some data to analyse consider whether you would be better using pivot tables or pivot charts.
*** UPDATE ***
You can also use my Hyper Plan software to create pivot tables. It is a lot simpler than the above! There is a free trial.
I got a tip from Anna-Jayne Metcalfe of C++ and QA specialists Riverblade to check out Cppcheck, a free static analyser for C and C++. I ran >100 kLOC of PerfectTablePlan C++ through it and it picked up a few issues, including:
- variables uninitialised in constructors
- classes passed by value, rather than as a const reference
- variables whose scopes could be reduced
- methods that could be made const
It only took me a few minutes from downloading to getting results. And the results are a lot less noisy than lint. I’m impressed. PerfectTablePlan is heavily tested and I don’t think any of the issues found are the cause of bugs in PerfectTablePlan, but it shows the potential of the tool.
The documentation is here. But, on Windows, you just need to start the Cppcheck GUI (in C:\Program files\Cppcheck, they appear to be too modest to add a shortcut to your desktop), select Check>Directory… and browse to the source directory you want to check. Any issues found will then be displayed.
You can also set an editor to integrate with, in Edit>Preferences>Applications. Double clicking on an issue will then display the appropriate line in your editor of choice.
Cppdepend is available with a GUI on Windows and as a command line tool on a range of platforms. There is also an Eclipse plugin. See the sourceforge page for details on platforms and IDEs supported. You can even write your own Cppcheck rules.
Cppcheck could be a very valuable additional layer in my defence in depth approach to QA. I have added it to my checklist of things to do before each new release.
Software piracy is a real issue for every software company, large and small, and it isn’t going away any time soon. So when I heard that fellow microISV owner Nikos Bozinis had created a tool to help software vendors fight piracy, I asked him to write a guest post. He kindly agreed to write this post about software piracy, the Digital Millennium Copyright Act and his CrackTracker product.
Why buy something when you can download it ‘for free’? Billions of dollars are lost every year from illegal downloads of music, movies and software. People around the world seem to have very lax morals when it comes to abusing digital content. Downloading the latest movie or windows software from rapidshare.com somehow doesn’t strike them as theft — it’s not like stealing a loaf of bread! The traditional music industry is already down on its knees as a result, and software may be the next to follow.
Software authors and music enterprises are fighting back by tightening the DRM (Digital Rights Management) of their products in a futile effort to stop online piracy. But usually crackers have no problem circumventing any protection system that we can dream up. To add insult to injury legitimate customers are usually hurt by such reinforced software protection and activation systems. A little bit like the war on terror, isn’t it?
A different line of defense for ailing copyright owners is the Digital Millennium Copyright Act (DMCA), a US law with global reach for copyright protection (the european EUCD equivalent is not as broadly known). This law is very broad, and not without controversy, but it works – closing down websites that distribute illegal content and removing copyright infringing downloads from file-hosting websites with summary procedures, among other things. So if you discover your software illegally distributed in some warez website, you can send a so called “DMCA section 512 takedown notice” to the website host and they are expected to remove that particular file from circulation — or risk the wrath of the law.
I have been a microISV for over 10 years so lets forget about the entertainment industry and concentrate on my field, software. There are over 200,000 programs listed on download.com and that’s just for Windows. Many are created by very small to medium sized companies — many even run by a single programmer/webmaster/marketer/entrepreneur. I bet that all these programs are cracked in one way or another — at least those popular enough for crackers to care about them. If you search for warez or torrents you will find the software you want for free, either the latest or an older working version.
Piracy statistics from Business Software Alliance report 2009 (click image to enlarge).
I sell a file manager called xplorer². I track how many people install the program every day and also I have a good guesstimate for the number of people using cracked versions of xplorer². I estimate over 70% of the regular users use one of the known keygens. Imagine if this 70% didn’t exist or it was converted to regular paying customers!
How is it done?
Downloadable software falls into 2 categories: those that run in trial mode until you buy a key to unlock the full functionality; and those that are special downloads for customers that pay the registration fee. In all cases some sort of unlocking takes place using a plain key, or a license file, or online activation, or some combination thereof. Many ISVs write their own licensing code, while others rely on off-the-shelf protection and licensing products (Armadillo, WinLicense etc).
Imagine you shipped your source code along with your program, then it would be trivial for even amateur crackers to bypass your protection and run the program without paying. Very few vendors supply source code, but people in the know can read off your licensing logic like an open book using specialized reverse engineering tools (softICE, IDA and other debuggers and disassemblers). Then they can create a ‘patch’ or modification to your executable that bypasses the protection.
An even worse type of compromise is a keygen. When the cracker uncovers the logic of your unlock keys, he can create a program to generate such keys which look and behave exactly like the legitimate ones you sell to your customers. Then he doesn’t need to patch your program, he just supplies this keygen to the warez community and everyone can help themselves to your program. You can guard yourself against such attacks using asymmetric encryption algorithms for your keys.
Is there a perfect protection system?
In short, no. If you consider that your program is presenting its logic to anyone with moderate experience in machine language, then sooner or later any protection can be circumvented. Professional protection schemes utilize encryption to protect sensitive parts of your code, but even they won’t withstand the cracker test. And remember the harder your DRM the more likely your program will be mistaken for malware (!) as many viruses and trojans use encryption tricks.
Even if there was a perfect system, your sales would still be at risk. All that’s required is some of your customers to post their unlock key in a warez site, and the game is lost. You would then blacklist that serial, until another one was leaked and so on.
The warez scene
There are people who don’t spend any time in Facebook or YouTube. They surf the internet for free stuff. Cracked versions of commercial software (aka warez) circulate in some shady forums that bring together the crackers with the downloaders e.g. http://www.warez-bb.org. Browse a warez site and you will find any software, movie or music you fancy, with an assortment of popups and dodgy advertisements of the usual internet 3P products (Pills, Poker and Girls [sic]). For your convenience there are even specialized search engines that search a number of such forums simultaneously, e.g. http://www.warez.com.
These forums do not host the actual files. They refer the traffic to specialized file hosting services like rapidshare.com. To make the most of warez you need to buy a subscription to access such file hosting sites (e.g. unlimited downloads from $9/month). Incurable cheapskates could get away without paying anything though, as you can download for free after a forced (nag) waiting of a minute or two.
A bit more up-market are download sites where to gain access you need to purchase a subscription, e.g. http://www.nowdownloadall.com. I have never paid to enter such a site, but they promise access to any download you can imagine. So you pay a monthly fee to download as much as you like. Note that this is different from paid-for hosting mentioned above. I suppose that you need a file hosting subscription on top to get the actual files downloaded. With so much stuff available for free I don’t know if this approach makes economic sense.
Finally there are traditional peer-to-peer file sharing networks, where people share their software music and video through torrents. After the demise of Napster torrents are still strong, with completely decentralized databases immune to legal intervention. The downside of torrents is their inherent unreliability, so people in a hurry will prefer the immediate gratification of a full download from rapidshare.com and the like.
Why do they do it?
It is easy to understand why someone will prefer ‘free’ software instead of paying up. But what about the crackers, the people who circumvent the DRM and distribute these warez. Why do they do it? Here are a few plausible motives:
- For kicks. The traditional hacker stereotype is a geeky person whose pastime is breaking into computer networks. Cracking into a software’s protection and stripping it clean must be a pleasure in itself, a ritual destruction of the evil Death Star.
- For glory. Marxist theory claims that private property is theft. This concept has struggled with real tangible property, but digital property is the ideal trophy. Many groups feel that software and music should be free (!) so taking down the big media and software corporations is a noble cause for them. But many small ISVs fall victims too, and the real motives are far less revolutionary…
- For profit. Marx is dead; long live Das Kapital. Warez downloads are big business in a number of ways:
- Direct subscriptions charges to access the downloads
- Selling password unlockers (e.g. you download something in a ZIP archive which is locked and you need to buy some software to unlock it)
- Distributing malware. Many downloads are packed with malware (sample report for a keygen), from straightforward scams and ransomware to trojans that turn your computer to a zombie, waiting for instructions to launch a DDoS attack or send spam.
You *can* remove illegal downloads
If your software is available to download from warez sites, either compromised (patched or keygened) or simply accompanied by a simple serial number to unlock it, you will definitely lose sales. The good news is that, using DMCA provisions, you can have these unauthorized downloads removed. Without these downloads prospective users will have no choice but to buy your software — or move on to your competitor’s cracked software.
Here is how to remove illegal downloads:
- Find your download links. All illegal downloads end up in a host like rapidshare.com or megaupload.com (I know of more than 100, but there are 10-20 big player websites). A standard Google search for your software name plus ‘crack’, ‘keygen’ or ‘rapidshare’ will find some hits, especially if you search in groups or blogs. Even better use specialized warez search engines like http://www.filestube.com with just your software name as a keyword — the results will be just downloads.
- Validate download URLs. Some of the download links you discover may be dead (e.g. very old). Click on each one to see if they are valid or 404.
- Send DCMA notices. Group the download links by provider (rapidshare, hotfile, etc), and send a DMCA notice to the abuse email address of each website. Usually this is firstname.lastname@example.org (e.g. email@example.com). Each website lists the steps for filing DMCA notices for file removal.
This sounds like a lot of hard work, and it can be, but it works. File sharing websites like rapidshare.com run a legitimate business — they are not responsible for cracks — so if you send them a polite DMCA takedown notice they will remove the copyright infringing downloads.
The DCMA takedown notice
Strictly speaking when you send a DMCA notice you are making allegations of copyright infringement, which is a serious crime. You would imagine that a formal complaint should be launched under the guidance of a solicitor/lawyer. Given the amount of copyright infringement that goes on, the red tape would bring everything to a standstill. The beauty of the DMCA law is that it simplifies the procedure. Sometimes a plain English email explaining the situation to the download site, along with a list of your download locations is all that’s required to have the links removed.
A few websites require a more formal DMCA email including details such as your company address, contact telephone numbers, and some boilerplate statements like “I swear, under penalty of perjury, that the information in the notification is accurate…”. You can find many sample DMCA notices online so I won’t repeat them here. The general idea is that you present yourself as the copyright owner and declare the download URLs as unauthorized, and therefore infringing your copyright.
Torrents slip by
DMCA is very good for removing illegal downloads hosted in popular file sharing websites, but it is powerless against torrents. There is no single source for the download, as the files are kept in many computers. You would have to contact each and every person who shares illegal copies of your software in the peer-to-peer network. This would be hopeless and a waste of effort. Thankfully for the ISV, torrent use is on the decline. People prefer direct downloads of the full package instead of slower peer-to-peer downloads.
The sales pitch
Anyone can search and remove illegal downloads manually. I was doing it the hard way for quite some time, each time I released a new version of my software tool (there’s a lot of cracker activity for each release as they need to update their patches and keygens). However this is very tedious, as you must:
- enter shady warez forums to search for your keyword, facing annoying popups and adverts you wouldn’t want your wife to see
- search many locations to ensure you get as many download URLs as possible
- validate each download URL to see if it is still alive or dead
- organize download URLs and write DMCA takedown emails for each file hosting website
Even if one wipes all the illegal downloads, new ones will appear over time. So the locate-report-remove cycle must be repeated regularly. This was the motivation for writing Crack Tracker, a tool that simplifies the removal of illegal downloads.
Crack Tracker is a desktop tool, with a meta search engine that securely scans warez databases for your downloads. You supply the search keyword (e.g. your software title or company name) then crack tracker will do an exhaustive search, collect a list of suspect download locations and verify the links with robotic efficiency. After you examine the results you just hit a button and the relevant DMCA emails are sent automatically. It doesn’t get any easier than that.
Crack Tracker doesn’t have a fancy user interface but it is very easy to use. It knows of more than 120 file hosting websites and works with 6 major warez search engines (the list is expanding). It is free to try as a search engine; to send the actual DMCA emails you need a registration, but I believe the price is very reasonable, especially if you consider the money you lose in pirated versions of your software.
Why don’t you try it for free and see how many cracks of your software it finds?
Nikos Bozinis ditched his Process Systems Engineering PhD to run his own microISV ZABKAT since 1999. He also writes a weekly blog focusing on file management and occasionally on programming, debugging and running a software business.
I have my email client set up so that it makes a cash register ‘ka-ching!’ noise every time I make a sale. I found this a real morale booster in the early days of Perfect Table Plan. Even now, several years later and with considerably higher sales volumes, I still haven’t grown tired of it. I particularly enjoy hearing ‘ka-ching!’s coming from my laptop while I am not working – it is wonderful to be able to earn money while drinking a glass of wine in front of the television.
If you are running Mozilla Thunderbird you can set this up quite easily with a message filter and the Mailbox Alert add-on:
- Install the Mailbox Alert add-on.
- Create a Thunderbird message filter to send emails denoting incoming sales to a specific folder (‘Tools’>’Message filters…’).
- Set the Mailbox Alert add-on to play an appropriate sound whenever a new email arrives in this folder (select this folder, then select ‘Tools’>’Mailbox Alert Preferences’).
You shouldn’t have too much problem finding an appropriate .wav file to play. I use C:\Program Files\Microsoft Office\Office12\MEDIA\CASHREG.WAV.You can also find some online here.
Thank you to whoever wrote Mailbox Alert and to Nick Hebb of FlowBreeze Flowchart software for pointing me at it originally.
This blog is hosted on WordPress.com. This has its advantages, but it means that I can’t use the huge range of add-ins that are available to those that host their own WordPress server. In my attempts to find a simple way to add social bookmarking to WordPress posts I stumbled across GetSocial, a Windows desktop program that generates the social bookmarking icons you see at the bottom of my recent posts. GetSocial is donationware – the author requests a small donation if you find the software useful. But the software is not crippled or time limited in any way and the donation is optional. I found the software useful so I made a small donation.
I use a number of donationware products. Human nature being what it is, I rarely get round to making donations – despite the best of intentions. It just never quite makes it to the top of my ever expanding TODO list. I have also heard various tales about how dismal the donation rates are. So I was curious about how well the donationware model works in this particular case. I emailed the author of GetSocial, Hillel Stoler, and he was kind enough to do this interview.
What was the motivation behind GetSocial?
GetSocial is not a business – it’s my contribution to the WordPress.com community. I needed a way to generate social bookmarking buttons for my own blog, and when I saw none was available I made GetSocial. I decided to request donations because I too was curious about the feasibility of donationware, and wanted to investigate the subject. I hate spammy “business models” such as installing Toolbars, embedding ads and so forth and wanted to make software that I would like to use.
Does anyone actually make a donation?
Surprisingly, yes. Many people donate, and I think all of them are glad to do so.
What is the average donation?
At the beginning I was only asking for a fixed amount (5 USD). The reason for this was that a fixed donation simplifies the donation process (because the potential benefactor needs to make one less decision). I’ve selected 5 USD because it was the lowest sum of money for which the PayPal commissions amounted to less than 10% of the donation.
Recently I’ve enabled donations in different currencies and variable amounts (but only on my websites, donations made from inside the application are still fixed). I’ve seen some decline in the ratio of donations per download (although it could be explained by many factors, and cannot be directly attributed to the added complexity of the process without applying proper A/B testing methods). However, the average donation has increased to 9.19 USD, and I’ve also received donations of over 20 USD. This is interesting because 19.99 USD is enough to purchase many commercial software products. To date, no one has donated less than 5 USD.
What is the donation/download ratio?
First of all, please consider that GetSocial is upgraded frequently, and I cannot differentiate between a new download and an upgrade download. Also, I can only count downloads which originated from my own websites. That said, dividing the number of the donations by the total number of documented downloads yields a donate/download ratio of about 0.55 percent (e.g. a single donation is received on average about every 182 downloads).
Can you make any money out of donationware?
I do make some money out of GetSocial, but I’m far from making a living out of it. With the current donation/download ratio, GetSocial will only begin to become economically interesting when it hits the 500k download mark. It’s not impossible market-size wise (there are about 10 million bloggers in WordPress.com) but it’s not easy.
The amount of money one can make with donationware is directly proportional to the number of people involved. For example, in the case of GetSocial, take a million downloads, divide by 182 and multiply by 5 dollars and you have 27k USD (before PayPal commissions). This amount of money can cover the development costs for many small software products.
That said, a million is a big number, even for free software. If you’re thinking about making real money out of a donation based product, I would recommend that you research the size of your market carefully. Getting those million downloads is not an easy task.
I personally don’t think that money is the sole motivation for doing things though. When discussing profits, we should also take into account the indirect benefits I receive from GetSocial such as incoming links, a user base, visits to my website, comments, world fame (or at least some publicity), and even fan mail!
And hey, the donationware model works for Wikipedia, doesn’t it?
Why did you choose a donation model instead of selling licences?
The reason I made GetSocial was that when I started hillelstoler.com (on a WordPress.com platform), I wanted to add social bookmarking buttons for my visitors. When I realized no one was doing that (there was an old text file floating around for manual use) I decided to make GetSocial. I wanted to attract visitors to my new blog, and I knew that distributing a hyped piece of free software would help me build credibility and acquire an international audience. It did.
Why did you choose donationware over freeware?
Out of curiosity, I guess. I wanted to know if one could make any money this way, and if people actually pay when they don’t have to (especially in cases where no one is looking). Today I can clearly say that I was pleasantly surprised. I think that Donationware is a beautiful (and very user-friendly) concept, and I’m glad it’s not just another web myth. Besides, I knew that people needed GetSocial, but to be honest I didn’t really think that anyone would actually pay for such a service at the time. In the end, I think that my potential buyers are also the ones who made the effort and donated, even though they didn’t have to. I’ve actually received some donations larger than what I could possibly charge if GetSocial was a commercial product!
Another important factor in my decision was the fact that I could do it rather easily. Recall the old days, when Donationware DOS programs asked you to kindly snail-mail some cash to a P.O box? That’s the kind of thing I would never bother with, especially when we’re talking about an international market.
Do you think you have made more money through donations than you would have through selling licences?
Absolutely! When I’ve received my first donation I was surprised (so people do donate after all), and as donations kept pouring in I realized that there is a donation culture. Selling licenses also meant becoming a part-time police officer, and that’s not what I was after.
What really amazed me, is that even in this very specific niche of social bookmarking for WordPress.com blogs (where I offer an industry grade solution for free) competition still sprung!
How did you promote GetSocial?
I didn’t. I’ve posted about it on the WordPress.com forums several times, and wrote about it on my website, hillelstoler.com. Other people wrote about it too. No paid ads or anything like that. You’ll notice that I didn’t even include a link on the toolbar itself (the viral ‘Get one!’ link you see everywhere else) because it was important to me not to impose.
You now have a web version of GetSocial. How long did that take to create compared to the desktop version? How do the desktop and web version compare in terms of the amount of use and the amount of donations?
GetSocial Live (the on-line version) started as a weekend project actually. GetSocial is a Windows application, and many people wanted a Mac version. Since I don’t even own a Mac, I decided to make a cross-platform web service (currently, about 40% of GetSocial Live visitors are indeed Mac users). It was easy to make, because I copied some of the code directly from GetSocial. The images are all photos I took of the plants in my house. In the end it did mean additional costs (hosting, domain, etc), but originally it was hosted for free on (the late) Google Pages service.
Later on, I discovered that the on-line version made GetSocial much more flexible and dynamic. I can now post updates much more quickly and effectively. The web version is also much easier to upgrade and maintain because it lacks some of the internal complexity of the GetSocial application (things like self encryption).
Do you get any useful revenue from the Google ads on getsociallive.com ?
As in the case of the donations, I was curious about AdSense. I know for a fact that I never click sponsored links myself, but I guess some other people do because Google makes a living out of it. I didn’t bother with A/B testing and other cash boosters, I just added a single ribbon of ads.
So far revenue has been disappointing (this is also the place to mention that the process of getting my AdSense account approved was very annoying and arbitrary, with zero support). There were some cases where I got more than 1 dollar per click, but I currently get more money through donations than through AdSense. Interestingly, the ratio of ad clicks per page view is similar to (though a bit lower than) the ratio of donations per download.
This can be incredibly useful. So far I have used it for:
- support – Sometimes email just doesn’t cut it. If your customer has Skype, you can use screen sharing to see exactly what your customer is doing while talking to them.
- remote usability testing – Usability testing is very important. But luring a stream of fresh victims to your office to take part is a logistical headache. If you use Skype screen sharing neither of you has to leave the comfort of your own computer. I have used it successully to do usability testing with people on the other side of the world.
Skype screen sharing has its limitation. The images are bit blurry, there is some latency and you can’t interact with the remote computer (as you can with services such as Copilot). But it is good enough for most purposes, and it’s free!
If you are going to be using Skype much, then I strongly recommend buying a USB headset. It is much more comfortable than holding a phone to your ear for extended periods and it keeps your hands free for typing. I use a Logitech headset and I have been quite happy with it. I sometimes get sweaty ears during a long call, but it seems a small price to pay.