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?
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.
With this new tool, we can create documents for any marketing source and see what is working and what isn't rather than guessing!
I've been using Xojo since 1998 and FileMaker since 1991. For years, I've used the both FileMaker and Xojo separately, using FileMaker for database solutions and Xojo for building apps. Internally, we'd mix it up and use FileMaker to track our sales of our Xojo apps, but never build a hybrid solution.
Last last year, Tim Dietrich told me that he was considering moving away from developing FileMaker solutions in favor of building apps especially iPhone apps that could be sold on the App Store. I was also interested in building iPhone apps as I've tried many methods to avoid coding in C like FMTouch, PhoneGap, LiveCode, and others that I can't recall. It was different this time though. Xojo was working to build iPhone apps with their superb object oriented 'BASIC like' language. I was already developing desktop apps for Mac and Windows so being able to build iPhone apps with the same language was a no brainier!
Xojo iOS App on the App Store
Once Xojo announced that we could build iOS Apps, we were all over it. Reference for FileMaker one of the first Xojo iPhone apps on the App Store and has been updated to include the latest changes in FileMaker 14. While Reference for FileMaker includes an internal SQLite database, Tim has been working on iOS apps that utilize FM EasyAPI so his iOS app can access data from a FileMaker Server.
Xojo Web Apps for FileMaker Deprecated Runtimes
FileMaker has placed Runtimes on their to be deprecated list earlier this year. You can continue to build Runtimes in FileMaker 14. After an outcry from developers, FileMaker said that it'll be in the next version too, but no promises after than and nothing has been suggest what may replace Runtimes. If you haven't heard, we've been using Xojo Web Apps as a replacement for Runtimes. We have much more on our blog where we discuss Xojo Xanadu and EasyData.
Tell Us What You Really, Really Want
This is your call to action! Tim and I have the same short survey below and also on Tim's Blog where we'd like to find out what interest you about Xojo. If there's a significant number of people wanting to learn more, we'll move forward with the idea!
Xanadu is the name for our Xojo Web App which we'll use to start our Web App projects for folks we work with. It's still in its infancy but it's quickly evolving into a fantastic foundation for database solutions. We're in love with the Xanadu logo that Ntstuan on Fiverr created for us. If you need an icon created, try out his services!
If you're a database user, you'll want to read this to learn more about what we can do for you. If you're a developer, you'll be interested in the Audit Log and the open source code!
Sometime in the near future, we'll add a web page specifically for Xanadu like we have for our FileMaker Template: FMSBFoundation. We wish we could make Xanadu Open Source. It's really amazing so far, but there are just too many bits of open source and purchased code from other developers. The great news is that everything we use in Xanadu can be included with the apps we develop. That means that if you need an app built, we can build it and you won't need to pay for all the add ons!
Xojo SQLite Audit Log
That said, we can make some things available as open source. We're incredibly appreciative of the Xojo community and love to give back when we can. So, we're making our Audit Log code that uses SQLite Triggers away!
The idea is simple. If you have a SQLite database all you need to do is add one table and one method to add SQLite Triggers to automatically create a log entry for each INSERT, UPDATE, and DELETE for the tables you care about. Check out the video below and the source code below. You'll see that it's really easy to implement.
Once you have it up and running, keep an eye on how much storage the LogAudit takes up. From the start, we wanted to store the Audit Log records in a separate but attached SQLite database file, but that didn't work as SQLite records that are inserted from triggers must be in the same file as the trigger. So, we plan to create a 'dbAuditLogTransporter' method to move the Log records to an attached SQLite database file. We'll make that available sometime, but we need to write it first!
The 'dbAuditLogTriggers' method is close to magical! The method will create three triggers for every table in your database file except for the tables you don't want to log! Our 'People_Insert_TG' looks like this: While that looks like a hot mess, it's really simple. First we, define the trigger name and for this trigger, we want it to run AFTER INSERTS. Then between the BEGIN and END, we first update the record to set the Modification Timestamp and Modification Seconds expressed in a fraction of a second. Next, we add a log record to the LogAudit Table. Most of the trigger easy to read, but the value to be stored in the 'FieldValues' is messy. All it does is takes the field name and value and wraps them in an xml like tag. We mixed square and curly brackets on purpose to reduce the chance of a conflict with the embedded data.
Watch How it Works, Source Code Below!
Automatically create Audit Log Triggers in your SQLite Database - Defining the Triggers for the Audit Log is the hard part, but this method makes it really easy! - Download
View the Audit Log in a HTMLViewer - We're showing the Audit Log on a WebDialog to be displayed as a sheet. Just add a sheet and drop a HTMLViewer on the sheet, add this method and call it when you want to see the Audit Log. - Download
We've been using Google Analytics for a while now and while it's neat to see 'stuff' I really think it's not worth the reduced privacy to the folks that visit. While I'd prefer to have the analytics it comes along with the data being used for more that I would want.
I have the amazing Privacy Badger from the EFF installed. It's great as it 'blocks spying ads and invisible trackers' and it's from EFF.org who has been fighting for our privacy for years.
Now, when I load our website the Privacy Badger show ZERO trackers on our site. I do plan to add a localized php log to track IP addresses and count the number of page loads, but that would all be private information.
We did some preliminary testing of the Art Licensing Manager 3 on Mac, Windows, iPad, and Android and it's looking great.
You may notice that we're showing two pics for iPad. In 'iPad Safari' we launched Safari and then typed in the server address. After that pic, we saved that web page to the iPad Home Screen 'iPad Saved to Home Screen' as we show in the video for GoLaunch for FileMaker page. That simply saves a bookmark to the home screen, but it also hides the address bar, which saves a bit of space as you can see in the 'iPad Home Screen' pic.
Longer, Getting Faster, Plus Data Separation
It's getting very exciting to see the Art Licensing Manager 3 come so far considering we haven't spent a ton of time on it. We're finding that it takes a a bit longer to develop in Xojo but as we build a reusable code library, we're getting faster and faster. Plus, since the database is using SQLite we get true data separation from the app code. That means with Xojo, we won't have to import data from the prior version. We'll simply use the exact same database file and add any tables the updated version requires.
Reducing Costs with Xojo
Using Xojo to build this app will reduce the costs to our users significantly too. Currently, the ALM is released as a single user FileMaker database (Runtime) which has zero cost to our users who only need single user acess. As soon as someone needs to go to two users, a minimum of two copies of FileMaker Pro would be needed. Beyond five users, FileMaker Server would be needed. As you can imagine the cost of FileMaker adds up quickly, but developing in Xojo takes ALL those costs away. With the purchase the Xojo development environment, we can build and distribute as many apps as we want which means those who use our apps, don't need to pay anything beyond the cost of our product.
Need an app?
Let us know if you need an app. With Xojo, we can help you build an app for iPhone, iPad, Mac, Windows, or a Web app that can run in a browser. All we need to do is talk about what you need and then we can provide a ballpark estimate regarding the costs.