iPhone Application Developers, this post is for you. Don’t be scared away by the length of this post. The results are going to be extremely useful
If you are an iPhone Application Developer, iTunes Connect provides you daily and weekly sales trends for your application(s) as a CSV file. Sure, you can open that up with Excel or Zoho Sheet and do some basic analysis. But we have a better solution for you in Zoho Reports.
What kind of reports can you do with Zoho Reports? Here are few examples (click to enlarge).
If you really want to see this in action, I made this sample database public with some reports created.
Now that I got your attention (hopefully), let me drill down into the details on how you can create similar reports for your iPhone applications. Before we get started though, make sure you download all your daily reports (.CSV files) downloaded from iTunes Connect.
Obviously, the first step is to create a Zoho Account and enter http://reports.zoho.com. Once you are in, Create a Database from ‘Import .XLS, .CSV, .HTML…’ option. Give your Database a name like ‘iPhone Reports’ and select the ‘Data Location’ as ‘Local Drive’. Now browse and select one of the downloaded CSV files (you can add others later) and click ‘Next’ and then select the ‘Create’ option in the next screen. This will create a table from the CSV file with 24 columns and multiple rows.
Once the table is created, you are all set to create Reports from this data. Before jumping there, let us make sure you have all the data imported from all the .CSV files you downloaded from iTunes Connect. To Do this, use the ‘Import’ option available on the top and select ‘Import into this table’ option and import all your CSV files using the ‘Local Drive’ as the ‘Data Location’. You’ll end up using this ‘Import’ option daily as and when you have reports available from Apple.
Now that we have the data in Zoho Reports, we can now start building different types of reports. Let us start with a simple report that shows ‘Daily Sales’. For this, select the ‘New Report’ option (top left) and choose the ‘Chart View’ option and select the database we just created. Here, you’ll see a simple drag & drop interface where you can drag the columns from the left to the right to generate the report you need. Let’s try this together.
Application Daily Sales
Drag the ‘End Date’ column from the left to the ‘X-Axis’ column on the right. Similarly, Drag the ‘Units’ column from the left to ‘Y-Axis’ column on the right. After you dragged the ‘Units’ column, change the ‘Actual (M)’ option to ‘Sum’ for ‘Units’ and ‘Actual (M)’ to ‘Actual(D)’ for the ‘End Date’. The top section will look like this.
Now select the ‘Click Here to Generate Graph’ option and you’ll see the Graph created for you with the daily sales of your application. You just created for the first graph which might look like this.
You can change your graph type with the ‘Other Charts’ option. There are many options available including the 3D Graphs. The nice part about the Graphs in Zoho Reports is, these are click-able. Which means, you can click on the graph to view the underlying data.
Now, Save the Graph, give it a name (like ‘Daily Sales Report’) so that you have this graph saves permanently that updates automatically whenever you import the data.
You can also make changes to the Graph. For example, if you want to see the number of units you sold every day displayed on the graph, simply drag the ‘Units’ column from the left to the ‘Text’ section on the right and you’ll see the number of units sold in the graph.
But there is an issue with this graph. As we added the ‘Units’ column to ‘Y-Axis’, it counts every downloaded unit as a sale which may not be the case as your application might have several versions and users download it as free upgrade. It is important to filter this data out to get the correct numbers. To filter out the right information from this graph, let us do this…
Select the ‘Filter’ tab (the vertical tab) and drag the ‘Royalty Price’ column to the ‘Filter’ section and select the ‘Actual Values’ from the drop down. In the next column (Royalty Price), select ‘Individual Values’ and select ’0′. Select the ‘Exclude Items’ in the drop down in the next column. What we are basically doing here is excluding all the units where ‘Royalty Price’ is 0. Now Refresh the Graph and you should see the correct values for daily sales in your graph.
Now, let’s try another graph. This time, let us analyze the daily sales and separate them based on country. For this, it’ll pretty much be the same graph with one simple modification. So let us use the ‘Save As’ option to save this Graph as another graph and call it ‘Daily Sales By Country’.
Daily Sales By Country
Now that we created a new Graph, simply drag the ‘Country Code’ column from the left to the ‘Color’ column on the right and refresh the graph. You’ll see the daily changes color coded by country. Again, you can mouse-over or click on the colors to see the underlying data for this graph.
Let’s quickly try some more graphs.
Application Sales By Country:
Create a New Report from ‘Chart View’ option and drag the ‘Country Code’ from ‘X-Axis’ and ‘Units’ to ‘Y-Axis’ and voila, you have sales report by Country. But then, don’t forget to filter the upgrades. For this, select the ‘Filter’ tab and drag the ‘Royalty Price’ column to the ‘Filter’ section and select the ‘Actual Values’ from the drop down. In the next column (Royalty Price), select ‘Individual Values’ and select ’0′. Select the ‘Exclude Items’ in the drop down in the next column.
You can change the graph type to fit your taste. Here is how it looks.
Application Sales By Currency:
Drag the ‘Customer Currency’ to ‘X-Axis’ and ‘Units’ to ‘Y-Axis’. You can also drag the ‘Units’ column to the ‘Text’ section to see the number of units displayed. Select the ‘Filter’ tab and drag the ‘Royalty Price’ column to the ‘Filter’ section and select the ‘Actual Values’ from the drop down. In the next column (Royalty Price), select ‘Individual Values’ and select ’0′. Select the ‘Exclude Items’ in the drop down in the next column.
Application Sales By Currency & Country:
This graph is pretty much similar to the above graph with one additional step. Drag the ‘Country Code’ to the ‘Color’ section. This chart also lets you see which country accepts what currency.
Application Sales By Royalty Price
Drag the ‘Royalty Price (Actual (D)) to ‘X-Axis’, ‘Units (Sum)’ to ‘Y-Axis’ and ‘Country Code’ to color. If the ‘Royalty Price’ is 0, they are upgrades.
Application Total Sales
To see the total sales of the app, simply drag the ‘Vendor Identifier’ to the ‘X-Axis’ and ‘Units’ to the ‘Y-Axis’ and ‘Text’ sections. Select the ‘Filter’ tab and drag the ‘Royalty Price’ column to the ‘Filter’ section and select the ‘Actual Values’ from the drop down. In the next column (Royalty Price), select ‘Individual Values’ and select ’0′. Select the ‘Exclude Items’ in the drop down in the next column.
You’ll see a graph something like this.
Now that you some of these graphs created, what do you need to do to keep them updated? It is simple. Download your reports fromiTunes Connect and Import them to the table. All the charts are automatically updated.
All the graphs I talked above assumes that you have a single app. If you have multiple apps, you can create other interesting graphs. More on that later.
Once you have these graphs created in Zoho Reports, you have many other options too. For example, you can right click on any graph and Export the graph as a PDF, Image etc. You can also embed these graphs in your blogs or website like I did above which means, when the data changes in the DB, these images are also updated.
I hope you find Zoho Reports useful for this use case. As you play with it you’ll uncover more of its power. Looking forward to see how innovative you’ll get with Zoho Reports.
BTW, did I mention that you can do all of this for FREE?
Olympics Dashboard using Zoho Reports