So you decided to use my Azure Functions short code project to create your own short code service. Now you wants to see how your short codes are doing, or at least someone on github asked how to do it, so I decided why not write it once and for all how I would do it.
Firstly we need access to the data, the data is stored in Azure Table storage, which is not super great for analytics if you have a very active short code service. But lets assume you only have limited traffic like me, then it is not a problem to just keep the data in Azure Table storage and run analytics on top of that data store.
My preferred tool for Adhoc analytics is still Microsoft Power BI, and it is quite easy to connect Power BI to an Azure Storage Account, as it is on of the supported data sources.
So simply fire up Power BI, select get data and under Azure select Azure Table Storage, and click connect.
Now you are asked to supply the account name of the storage account, which is found in the azure portal.
So in my case the name would be sjkpshorturlb197.
After supplying the storage account name you will be asked for the access key, that is also found in the Azure portal, under the storage account and under Access Keys.
Finally in the navigator view, in Power BI you select the Analytics table as it contains all the data.
Power BI will load the data, but when it is done you will only see 3 columns (PartitionKey, RowKey and Timestamp), so we are missing some columns. You can add those by going to the Edit Queries View, and expanding the Content Column
Now we have all the data that we collected inside of Power BI and we can build any report we like.
Actually during the process of generating an report for my own short code service, I found out that the IP addresses stored are badly formatted, some of them have port numbers because of the Azure Load balancer I suppose, and sometimes they contain simply invalid IPs.
I have updated the code in github with a more robust way of getting the client IP. Obviously, the IP address alone is not very interesting in a report, but you can do a geo lookup based on the IP and find out roughly where in the world your visitors are coming from and that could be interesting. I will save how to do so with Power BI for a separate blog post, in the meantime you and read how others have done it here.
If you want to take a look at how my report looks I have published it to web. (Note I didn’t connect the my azure table storage account using the Access Key, because the access key should be kept private as it gives read, write and delete access to the storage account. But I wanted to be able to share my pbix file, so Instead I used the Odata endpoint of the table storage service, which unfortunately is not as well supported in Power BI).