Google Analytics in FileMaker
In today’s webinar, I discussed some of the advantages of bringing data from Google Analytics into FileMaker Pro. I also illustrated how simple it can be to accomplish. In order to make this task even easier for you, I want to provide you with some helpful links, and even some sample files to get you started.
Helpful Links:
- Getting Started –> This explains the basics like what you need in order to begin using the Google Analytics Data Export API
- PHP GAPI –> This is where you can download the PHP file that contains the class object that you can use in your own database
- Feed Reference –>Discusses the structure of feeds from the Google Analytics Data Export API
- Common Queries –>How to compose queries that are commonly made
- Query Explorer –>Build a query using a simple to use form (includes combo boxes etc…)
- Dimensions & Metrics –>These are two parameters that are mandatory when querying Google’s Data Export API. This page will teach you which values can be used, and what kinds of results can be expected when you use them.
- Valid Query Combinations –>This document will explain which combinations of dimensions and metrics are possible
Sample Code:
This is a .zip file containing sample PHP and batch scripts that can be used to help you get Google Analytics data into your FileMaker database. These files are intended for use in PHP-CLI, and should not be placed in a web-accessible directory (unlike most .PHP files)
What you need to do to get this to work:
- Your server must have PHP installed (should be PHP 5+)
- Extract the .zip file provided above to FileMaker Server’s “Scripts” folder (that folder is necessary if you’re going to be using this to auto-update your solution).
- Modify the “Report.php” file so that the “require_once” lines point to the appropriate paths of both your FileMaker.php(provided with FileMaker Server) and your “gapi.class.php”(link provided above)
- Insert your Google Analytics email address and password into the appropriate spots within “Report.php”
- Specify the filename, path to server, username, and password to your FileMaker Server hosted database within “Report.php” (account must have fmphp privilege)
- runReport.bat might need to be modified to contain the full path to your php.exe file (if that path is not defined within your environment variables)
- parameters should be modified within runReport.bat
Additional Info
- Have the file runReport.bat dynamically generated in order to get the most out of this
- When executing your PHP script (within runReport.bat), you’ll need to provide four parameters (websiteProfileID, startDate, endDate, reportType)
- Additional report types can be added simply by creating new PHP files within the “Reports” subfolder (use other files within that folder as a template for creating new ones)
Also… if you have any questions about what was discussed in my webinar today, please feel free to leave your questions here, and I’ll try to answer them as best as I can.
Edit: FileMaker Inc. has now posted my webinar so you can view it online
Edit: apparently the link posted above is no longer valid. If you would still like to see the webinar, then you can find a downloadable copy of it here.
Please note that you might need the appropriate player and/or codecs installed in order to view it.
[...] This post was mentioned on Twitter by Audrey Akhavan, CoreSolutions. CoreSolutions said: Thanks! RT @fmresourceguide: @CoreSolution did a great presentation on using PHP, #FileMaker Server, & Google Analytics http://bit.ly/cZkiwV [...]
Great webinar! Some really useful techniques. Thanks for sharing.
Very interesting presentation via TechNet. Thanks for the detailed view of this material. Very helpful. edz
On the Mac it should be pretty easy. First open Terminal and make sure that CLI is included in your installation of PHP. At the prompt type "php -v" without quotes.If it has CLI anywhere in the first line of the response then you are good to go. Next add '#!/usr/local/bin/php -q' to the first line of the Report.php file and all the other php files. Then have FileMaker Server call the Report.php file instead of the runReport.bat file. You will need to assign it a local user. I couldn't get the default fmserver account to work.
You're both very welcome. Thanks for the kind words. :) @Sean: Thanks for the tips on getting this to work from OSX
What does the php -q do? Reading man page shows no entry for this flag.
Perhaps PHP installations on OSX accept different arguments. My PHP installation on Windows (5.2.11) does not appear to accept the "-q" argument. It has the following options instead ("-f" is the argument I used): [--] [args...]
php [options] -r
Usage: php [options] [-f]
[--] [args...] php [options] [-B] -R [-E] [--] [args...]
php [options] [-B ] -F [-E ] [--] [args...]
php [options] -- [args...]
php [options] -a
| Look for php.ini file in this directory
-n No php.ini file will be used
-d foo[=bar] Define INI entry foo with value 'bar'
-e Generate extended information for debugger/profiler
-f Parse and execute .
-h This help
-i PHP information
-l Syntax check only (lint)
-m Show compiled in modules
-r -a Run interactively -c
Run PHPwithout using script tags -B Run PHP before processing input lines
-R Run PHPfor every input line -F Parse and execute for every input line
-E Run PHP after processing all input lines
-H Hide any passed arguments from external tools.
-s Output HTML syntax highlighted source.
-v Version number
-w Output source with stripped comments and whitespace.
-z Load Zend extension .
Show information about function .
--rc Show information about class .
--re Show information about extension .
--ri Show configuration for extension . args... Arguments passed to script. Use -- args when first argument starts with - or script is read from stdin
--ini Show configuration file names
--rf
[...] See the original post here: Google Analytics in FileMaker | CoreSolutions Software Inc. [...]
Mac CLI PHP man page does not show -q flag either. OPTIONS --interactive -a Run PHP interactively. This lets you enter snippets of PHP code that directly get executed. When readline sup- port is enabled you can edit the lines and also have history support. --php-ini path|file -c path|file Look for php.ini file in the directory path or use the specified file --no-php-ini -n No php.ini file will be used --define foo[=bar] -d foo[=bar] Define INI entry foo with value bar -e Generate extended information for debugger/profiler --file file -f file Parse and execute file --global name -g name Make variable name global in script. --help -h This help --hide-args -H Hide script name (file) and parameters (args...) from external tools. For example you may want to use this when a php script is started as a daemon and the command line contains sensitive data such as passwords. --info -i PHP information and configuration --syntax-check -l Syntax check only (lint) --modules -m Show compiled in modules --run code -r code Run PHP code without using script tags '' --process-begin code -B code Run PHP code before processing input lines --process-code code -R code Run PHP code for every input line --process-file file -F file Parse and execute file for every input line --process-end code -E code Run PHP code after processing all input lines --syntax-highlight -s Output HTML syntax highlighted source --version -v Version number --stripped -w Output source with stripped comments and whitespace --zend-extension file -z file Load Zend extension file args... Arguments passed to script. Use '--' args when first argument starts with '-' or script is read from stdin --rfunction name --rf name Shows information about function name --rclass name --rc name Shows information about class name --rextension name --re name Shows information about extension name --rextinfo name --ri name Shows configuration for extension name --ini Show configuration file names
I'm really not sure what the "-q" switch does. I've just seen it referenced in many PHP CLI scripts. I have not found a reference to the actual meaning so it may not be necessary. http://www.php-cli.com/ http://blog.johan-mares.be/ict/php/running-php-shell-scripts/
Interesting. The 2nd link you posted actually refers to it as "quiet mode". I'm guessing that this means that you can suppress output to the screen using a switch like this.
Very nice webinar and resource list. Are there any certain requirements or conditions for WriteToFile to write a file inside the FileMaker Server Scripts folder? So far I only get errors when attempting this, yet I can write the file to the Desktop and tmp file without any issues.
Thanks Anders; In my example, I was writing to FileMaker Server's "Scripts" folder. Of course, this was located within "C:\Program Files...". On Windows Server 2003, this was not a problem writing to, but I could see how it could potentially be a problem on Windows Server 2008 with default settings. In order to write a file within the "Program Files" folder, you need to provide administrative permission. I would recommend circumventing this by simply using an intermediary .bat file that calls the FileMaker generated .bat file which was written to a different location.
On Mac OS X I couldn't get my FileMaker 11 Server-side script to run or ScriptMaster to write a file as a server side plug-in. The server schedule showed “Aborted by user†messages, despite permissions set to fmserver/fmsadmin. Also, ScriptMaster's WriteToFile only showed "ERROR." Using the client plug-in worked, but I needed everything to run on the server. So, I turned to the Terminal to set up a cron (a scheduled task on the Mac). This entailed making the dates in the “autoReport.sh†script dynamic, to retrieve data for the previous day’s date. Matt’s process handled this in the FileMaker script that wrote each line into the “autoReport.sh†document generated by the WriteToFile function. Instead, I had to rely on this inside the file using bash and had to convert each line, where the date would become a variable with yesterday’s date. Getting today's date was no problem. Getting yesterday's date also was simple. Setting them as variables proved a little rougher. Eventually I combined two examples and ended up with a pair of lines to create the variable. let YESTERDAY=`date '+%s'`-86400 YDATE=`date -r $YESTERDAY '+%Y-%m-%d'` I then could use $YDATE in place of the start and end date parameters. Before and after: php Report.php GOOGLE_ID 2011-03-16 2011-03-16 Absolute_Unique_Visitors php Report.php GOOGLE_ID $YDATE $YDATE Absolute_Unique_Visitors I updated the script for each of the reports and ran “autoReport.sh†manually in the Terminal, creating my records in FileMaker. To automate the process I created a cron with the command “crontab –e†and edited the cron with the Terminal editor called nano. The crontab line consists of time and day selections separated by tabs, much like a FileMaker Server schedule, then the command and path to the script. 30 1 * * * sh /Library/FileMaker\ Server/Data/Scripts/autoReport.sh The sequence: minute, hour, day of month, month, day of week, command & path. This action would run every day of the week at 1:30AM. My first attempts failed with the message that it could not open “Report.php.†Crontab seems to prefer absolute paths, so I changed the path inside “autoReport.sh†and also added a –f to access the file. I also changed the path inside “Report.php†from relative to absolute. php -f /Library/FileMaker\ Server/Data/Scripts/Report.php GOOGLE_ID $YDATE $YDATE Absolute_Unique_Visitors $filename = '/Library/FileMaker Server/Data/Scripts/Reports/'.$reportType.'.php'; This time the cron job ran without a hitch, and I could see the data populating into FileMaker. I now had an automated method on the Mac to pull in my Google Analytics data into FileMaker using the processed detailed by Matt. I'm not sure why the server script failed on the Mac, and why the plug-in also failed. Thanks again to Matt for his great webinar and the resources that he put together.
That's amazing Anders! I'm so glad that you got it working, and even happier that you're willing to share the knowledge you've gained along the way with others too. :)
Greetings! Very useful advice within this post! It is the little changes that produce the greatest changes. Thanks for sharing!