Making usable reports of Windows Mobile Marketplace sales

07 May

[Note: This was first published on 2010-05-07 on my personal blog, at this location. It is duplicated here in an effort to help me pretend I’m running a real company and it’s not just me sitting in my basement. Which it is, really.]

As I mentioned in my last post, I am currently selling two apps on the Windows Mobile Marketplace. These are Carlos (an app intended to remind you where you put the car) and Proximity (a location-triggered alarm and notification system). My previous post detailed the sales I’m currently getting for those apps – I’ll post an update on those numbers at some point, but this post is about how I got those figures into a nice chart.

You can say many things about the Windows Mobile Marketplace, but if you’re an app vendor then one of them will probably not be a long treatise on how wonderful their sales reporting system is. Their primary reporting output is a mysterious “Fulfillment report” XML file that turns out to be SpreadsheetML, the ISO 29500 file format used by Excel. Oddly enough this crosses over into my day job, but enough of that. Here’s how to turn that file into something useful, using Excel.

Using the Template

Download my template reporting spreadsheet, mobileappsalestemplate.xlsm.

Open it in Excel. Enable macros.

Request and download a “fulfillment report” from the Windows Mobile Marketplace developer page:

Leave the report set to All Markets, All OS, All Platforms and choose “90 days” as the time period.

The report downloads as a ZIP file containing two XML files – save FulfillmentReport.xml and use File/Open to open it in the same copy of Excel that your reporting workbook is in and enable editing (if you open it in a new copy you’ll get a VBA error when you try to import the data).

Now you need to add your products. FulfillmentReport.xml has a “SKU detail” tab – copy and paste that data into the same tab of the template sheet. You have to repeat this step whenever your SKUs change (usually when you add a new product, or issue an update).

Now, save the reporting sheet somewhere. It’s going to keep a hold of more history than the Marketplace does.

Without closing the FulfillmentReport.xml workbook, go onto the “Consolidated Purchase History” tab of my template. There’s a button on the right hand side of the table – click this. It will import the fulfillment data from the FulfillmentReport.xml you currently have open, and put it onto the Purchase History tab. It will not import any history that’s already been stored in the template, so it’s fine to just keep requesting 90-day histories from the Marketplace and importing them over the old data. Unless I screwed up the VBA, of course. The VBA is dog-slow – it could take ten minutes for the first import. Yes, I could certainly speed it up. And no, I can’t be bothered. If you do, I’ll be pleased to use yours instead!

Once the import is finished, the yellow columns in this sheet should populate with the right product names – you have all your data now, you’re ready to report on it!

On the sheet imaginatively called “A Chart”, you’ll see that I have the data for the chart I used in my previous blog post. The data on the left is simply to aggregate sales by day. You’ll have to replace my product names in row 1 with yours before you see any data. I was using it to track two products – you can change the chart range if you want to track a different number. You can also create Pivot Tables based upon the data on the Consolidated Purchase History sheet, or add new charts, or what have you.

This is not intended to be a glitzy boxed solution. It’s something I’ve been using myself so there’s not really any error handling and there are all sorts of things hard-coded around the place. If you’re not very familiar with Excel, it’s going to be tricky to use. That said, if you make any improvements to the sheet I’d love to see them (feel free to link them in comments here) and I’ll also post an update if I make a newer version.

Back on my original topic, an interesting switch appears to be happening in my sales figures, so I’ll post updated sales figures for my apps in a month or so. If anyone comes up with a nifty new version of this spreadsheet, I’ll be delighted to do so using that!


Leave a Reply