Back to Blog Posts

How did you hear about us? A look inside custom reporting

By Chris / January 2nd, 2013

Finding out where your new clients came from is a great way to track where advertising is doing well, and where to increase efforts. There are a hundred different ways to find out this information using tools like Google Analytics, to PPC ads, but how about asking them directly?

With WHMCS you can create Custom Fields for each product where you can specify anything from "What software do you want installed?", to "Where did you hear about us?". In this Blog article, we'll be using the "Where did you hear about us?" example - and create a report for it.

To get started, I created two Hosting Packages, "Basic Hosting" and "Advanced Hosting". During creation I used the Custom Fields tab under Setup >> Products/Services >> Product to input my required data. In the Field name, I asked the customer the question "Where did you hear about us?". Then set the field type as Drop Down menu, and gave them four options based on where I was advertising; Google.com, WebHostingTalk.com, Friend (personal referral), and Other (for everyone else). Next, I set it as a Required Field and to Show on Order Form to ensure I'm collecting the data I need.

product.jpg

At this point, as I obtain New Signups they'll be required to tell me where they found out about my company. So now I wait.

A few days later, I've got 21 New Signups for the time and money spent on Advertising - So let's find out where it paid off!

Creating specialized reports for your needs in WHMCS is easy. WHMCS provides a slue of open sourced reports already, but sometimes I want to create my own and have it easily accessible and displayed directly in my primary company portal. Our online documentation starts us off with the understanding of how to generate the tables for the report. Now I just need to fill it with data.

The database tables I'll be using are tblhosting, tblproducts, tblcustomfields, and tblcustomfieldsvalue. From investigating during creation, I know that tblcustomfields.id corresponds with tblcustomfieldsvalues.fieldid. I also know that tblhosting.packageid corresponds with tblproducts.id. My next step is to craft the SQL query around that information to count the amount of each product against the value stored at tblcustomfieldsvalues.value. Here's the query I used:

sql.jpg

This provided a count for each Hosting plan in relation to the value stored in tblcustomfieldsvalues.value.

Our last step is to combine everything we've just learned into a unified report. Using Report Creating Documentation, Custom Fields, and a little MySQL we've been able to create a report we can easily reference and expand on for new products, and new marketing ventures. So let's take a look at that report!
report.jpg

Looks good! Using WHMCS' full range of features, and a little ingenuity we're able to create this fantastic looking report. We can now track where our new visitors are coming from. However, the above report's sizing really only works for two products, any more and you'd have a very clustered view. So let's change that! WHMCS is using Google Chart Tools, so I can simply edit the line:

$reportdata["headertext"] = $chart->drawChart('Chart',$chartdata,$args,'400px');

And change "Chart", to "Bar":

$reportdata["headertext"] = $chart->drawChart('Bar',$chartdata,$args,'400px');

So let's take a look at that change:
Inspired by your Feature Request.
http://requests.whmcs.com/responses/ability-to-have-a-how-did-you-find-us-drop-down

Liked this article? Share it