Our goal was to show how much each customer spent and a total of how much each referral spent. Knowing this can tell us if a particular marketing source is working or not. For example, is it better to spend money on Groupons or Radio Ads? So, I quickly sketched this to represent showing referrals from a radio staton advertisement:



Organizational Trees

Last week, I had an idea to show this information as an organizational chart. I messaged my good friend Tim Dietrich if he ever used or knew of a good org chart and he found Google Charts Org Chart. The example source was very simple. There's some javascript, a bit of html, and one row of data for each item on the tree.

Upside Down Trees

In this database, we have a field for the Referral Source. When people are asked, 'How did you hear about us?', that information is entered. It could be a referral from a friend, radio ad, groupon, or one of many other options. If it's from a friend, we store the friend's name and ID which allows us to show a list of all the people that a particular person referred. Since Tom can refer Sue and Sue can refer Joe, the org chart is a perfect way to show the relationships.

Adding Nutrients

So we created two methods / scripts so we could build the chart from the perspective of a particular customer or a marketing source. The fun part was creating the recursive code. Org charts are represented by a data structure called a "tree" where each node is a "parent" of their "children". This means that a customer / parent can have one or many referrals / children. It's recursive since the code simply adds the customer to the chart and then the code is called again for each of the childrens and yet again for each of their children until someone has no referrals.

Once we had the chart drawing correctly with the customer name, we added a dollar amount for the total of each invoice. Since we knew the total of all the invoices for a particular customer was, it was easy to add total the invoices for each customers direct referrals. Finally, we added a total for all the every one below on the same branch of the tree.

Strong Roots

Below is what one of our Marketing Source Referral Trees look like. The top row represents handing out flyers at the Cantina. The second row are the customers who came in and said they heard about it from the Cantina Flyer. Any customers below the second row are people referred by someone above them.

The first dollar amount is a total of the invoices for that customer. The second amount is the total for their direct referrals. The third amount is the total of everyone below them.

As an extra bonus, we added a Tooltip and a URL to take us to the FileMaker database and find the clicked customer. That same method will work in Xojo too!

With this new tool, we can create documents for any marketing source and see what is working and what isn't rather than guessing!