I am looking to do some analytics on our mobile business. We unfortunately, do not have great analytics at the moment so I am looking to branch out and provide some for the management team. These would be some pretty basic pieces of information but i am struggling to get there.
I will first explain what I am trying to do and then give you a flavour of what I have available.
I am looking to be able to show the user the number of bundles (by bundle name if required) purchased by month, how many MB, Mins, SMS were allocated to each bundles as well as what percentage of this bundle was used for each allocated resource (MBs, min,sms).
But then I would like to be able to link these to a trend bar or line graph that shows for each of these months, the daily usage for each of these bundles, and to be able to filter the usage when clicking on any of the months the bundles were purchased or by bundle names within those months.
My biggest stumbling block seems to be how I get started on this. I have a “Transaction” or “TDR” tables that presents me with the information broken down into the different entries in the table, namely, the “BUNDLE SUBSCRIPTION” – which is the cost price of the bundle, then “BUNDLE”, which is the part that shows how many units of each of the resources are given (i.e. MB, Mins, SMS), then another transaction called “EXPIRATION”, which shows the date of expiry as well as how many of the units were left when the bundle expired. To complicate matters, a customer can purchase a bundle before the bundle expires, and this simply shows as another “BUNDLE_SUBSCRIPTION” and “BUNDLE” entry.
Example data of transactions for bundles:
I am not sure how I can get all this information into Measures to create the “START DATETIME” of the BUNDLE" and “END DATETIME” of the bundle.
Once I have this, I think I might be able to use this information to bring in the usage information from the three usage tables for MB, Min, SMS.
But any guidance on how I can start on this would be most appreciated.
Please let me know if you require any information regarding my model. But initially this is simply just one table that all the information is sitting in.