9 Aug 2013

Useful Reporting Queries for Your Business

I have rehashed a list of useful reporting queries from my blog which I hope people would find useful for their business or clients. These are not MySQL specific, by any means.


  • What is the total revenue (by segments)? - You are looking for 'peaks and troughs'. For example, found a peak season? you can plan better for it next year. Found an off-season? perhaps offer some deals for that period.
  • What is the total placed orders, completed orders and abandoned/uncompleted orders?
  • What is the total paid revenue vs outstanding orders? 
  • What is the total number of returned/cancelled orders and lost revenue from them?

Products (Best Sellers)

  • Which are the top 20% best selling products? (businesses love 80/20 comparisons and if you look at reports a lot, you will too)
  • Which are the products that our best customers keep buying from us? - the library example - don't just keep the best selling books, like any supermarket would have. Have a range of books that attract your best customer.
  • Which are the bottom 10-20% worst selling products? - you may want to discontinue these, depending on your top customers
  • Which products generate the most revenue? – (product price * quantity)
  • Which products generate the most profit? – ((product price – product cost) * quantity) 


  • How many new accounts/customers do we get (each day, week, month, half year or year)?
  • Who are our best customers – that buy the most from us? - The top 20% that make 80% of the profits.
  • What is our attrition rate – the numbers of accounts/customers we lose (each day, week, month, half year or year)? - If your attrition rate is high, the sales team needs to find a lot of new people just to keep things the same.
  • What is the customer satisfaction? – You will probably get this from surveys or customer feedback.
  • What are the top reasons for complaints?
  • How many existing customers referred new customers to your company? – You might need to setup something to collect this data. If you send your customers to do your marketing for you, you can save a lot of money.


  • Which product pages get the most views?
  • Which news/blog articles get the most views?
  • How many minutes does the user spend on the website?
  • What is the percentage of abandoned shopping carts?
  • What is the average amount of time a customer spends in the shopping cart/session?
  • Most important – What is the conversion rate of the visitors coming to the site and the people who purchase a product?
  • Which landing pages are the most viewed?
  • Which landing pages have the highest conversion rate?

Again, I hope these are helpful and if you would like me to add some of your own, please comment below.

1 comment:

  1. Comment from Earle West - https://plus.google.com/u/0/109490193695947400246/posts/ddRHzqiqz4V?cfem=1

    My experience suggests businesses are much more interested in things that might have a cause-effect relationship.
    For example, if they give a coupon for hamburger, will they buy the fries and a cola also? How often does this happen? Where is the point where giving away coupons actually reduces total overall sales?
    Another example: When the business defines a "bundled product" which channel sells the most (e.g. drive-through, catering, carry-out).
    Generally, businesses want to know exactly what the results of some advertising, coupons, training will be on specific product sales.
    In another instance, businesses often have business that is driven by external events (weather, ball games, holidays). What kind of forecasting would tell them how many employees to bring in on that day?