Core FileMaker Blog

Keep up to date with CoreSolutions

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)

Download Sample Code

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.

Leave a comment

16 comments

  • ca lottery winners handbook
    ca lottery winners handbook 23 June 2013 Report

    Greetings! Very useful advice within this post! It is the little changes that produce the
    greatest changes. Thanks for sharing!

    Comment Link

  • Matthew Leering
    Matthew Leering 21 March 2011 Report

    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.
    :)

    Comment Link

  • Anders Monsen
    Anders Monsen 18 March 2011 Report

    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.

    Comment Link

  • Matthew Leering
    Matthew Leering 07 February 2011 Report

    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.

    Comment Link

  • Anders Monsen
    Anders Monsen 01 February 2011 Report

    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.

    Comment Link

  • Matthew Leering
    Matthew Leering 18 October 2010 Report

    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.

    Comment Link

  • Sean Walsh
    Sean Walsh 14 October 2010 Report

    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/

    Comment Link

  • Bruce Robertson
    Bruce Robertson 14 October 2010 Report

    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

    Comment Link

  • Google Analytics in FileMaker | CoreSolutions Software Inc. | SFWEBDESIGN.com
    Google Analytics in FileMaker | CoreSolutions Software Inc. | SFWEBDESIGN.com 14 October 2010 Report

    See the original post here: Google Analytics in FileMaker | CoreSolutions Software Inc.

    Comment Link

  • Matthew Leering
    Matthew Leering 13 October 2010 Report

    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):


    Usage: php [options] [-f] [--] [args...]
    php [options] -r [--] [args...]
    php [options] [-B ] -R [-E ] [--] [args...]
    php [options] [-B ] -F [-E ] [--] [args...]
    php [options] -- [args...]
    php [options] -a


    -a Run interactively
    -c | 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 Run PHP without using script tags
    -B Run PHP before processing input lines
    -R Run PHP for 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 .


    args... Arguments passed to script. Use -- args when first argument
    starts with - or script is read from stdin


    --ini Show configuration file names


    --rf Show information about function .
    --rc Show information about class .
    --re Show information about extension .
    --ri Show configuration for extension .

    Comment Link

  • Bruce Robertson
    Bruce Robertson 13 October 2010 Report

    What does the php -q do? Reading man page shows no entry for this flag.

    Comment Link

  • Matthew Leering
    Matthew Leering 13 October 2010 Report

    You're both very welcome. Thanks for the kind words.
    :)

    @Sean: Thanks for the tips on getting this to work from OSX

    Comment Link

  • Sean Walsh
    Sean Walsh 12 October 2010 Report

    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.

    Comment Link

  • ed zinkiewicz
    ed zinkiewicz 12 October 2010 Report

    Very interesting presentation via TechNet.

    Thanks for the detailed view of this material.

    Very helpful.

    edz

    Comment Link

  • Tim Cimbura
    Tim Cimbura 12 October 2010 Report

    Great webinar! Some really useful techniques. Thanks for sharing.

    Comment Link

  • Tweets that mention Google Analytics in FileMaker | CoreSolutions Software Inc. -- Topsy.com
    Tweets that mention Google Analytics in FileMaker | CoreSolutions Software Inc. -- Topsy.com 12 October 2010 Report

    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

    Comment Link

CoreSolutions Logo

London Office:
1-1615 N Routledge Park
London, ON N6H 5L6

Toronto Office:
2425 Matheson Blvd East
7th Floor
Mississauga, ON L4W 5K4

Toll Free: (800) 650-8882
London: (519) 641-7727
Toronto: (416) 410-8649
Email: This email address is being protected from spambots. You need JavaScript enabled to view it.

SUBSCRIBE

Click the 'Subscribe' button to sign up for news & software tips.

Beyond Software

Copyright © 2018 CoreSolutions Software, Inc. All Rights Reserved