Trees for Representing Referrals and Marketing Sources in Xojo and FileMaker
Nov 28, 2015 12:28 PM
For some time, we struggled with how to visualize word of mouth referrals and marketing sources for one our favorite clients. We knew that both word of mouth and the advertising were doing something, but it was hard to see the which was stronger with standard reporting tools.
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:
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.
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.
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!