A poor man’s CRM with GA and Google Spreadsheets

One reason I am not convinced by the new User Explorer report in GA is that it is a bit oversold – you cannot monitor activities of an identifiable user, since the report is still anonymous. It’s not just Google’s own TOS that prohibit storing personal info, there is also a bit of a legal issue which at least applies in Europe. If you store personally identifiable information you need to obtain permission by, and must be able to delete the data at the request of, the person in question. The former thing is not quite feasible and the latter is not at all possible. Storing user based data (with their permission) rather is the domain of Customer Relationship Management systems. Yet you can emulate at least some features of a CRM system with the feature set Google offers for free.

Now, don’t cancel your Salesforce subscription just yet. But the following post will show a proof of concept of how you can create and store a lead via a form on your website, and then relate that (potentially personally identifiable) data to data from your Google Analytics account. Other than with my previous tutorial that demonstrated the same thing for Salesforce data this does not require any serverside programming. The GA part is implemented as a GA plugin (for no particular reason except that I thought it would be fun), data is stored in a Google spreadsheet and an extremely basic Apps Script function (Apps Script is basically javascript) pulls traffic data from GA and adds it to the recorded form data.

As per usual you find the code on Github (and yes, I left the UAID of my Analytics property in there. This is a test property and I don’t mind if people accidentally send data there ). See a test page at http://flesheatingarthropods.org/crm/ and look into the network tab to see how it sends the data.

How it works

The plugin will create a form as per configuration (see below). It will store the form data with a clientid and a timestamp in a Google Spreadsheet. It will also send an event to GA with the clientid as label and a timestamp as action, You then run a function that pulls in data from GA into the spreadsheet, using the clientid and the timestamp as key. That way it will display e.g. the traffic source and medium that lead to a conversion for the form.

Google Analytics Tracking Code Setup

<script>
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','https://www.google-analytics.com/analytics.js','ga');

  ga('create', 'UA-883789-7', 'auto');
  ga('set','userId','user1234');

  ga('require', 'gaCrm', {
      sheet: 'https://script.google.com/macros/s/AKfycbyyzo9Ohi0W9Ht60a1_Llw_XXXXXXXXXX/exec/',
      debug: true,
      forms: [{
        id:'contactForm',
        url:'/crm/',
        container:'form1',
        fields : [
          {id : 'name', type:'text', name : "name", class: "textfield", label:"Name"},
          {id : 'message', type:'text', name : "message", class: "textfield", label:"Message"},
          {id : 'email', type:'email', name : "email", class: "textfield", label:"Email"},
          {id : 'check1', value:'check1', type:'checkbox', name : "check", class: "checkbox", label:"Option 1"},
          {id : 'check2', value:'check2', type:'checkbox', name : "check", class: "checkbox", label:"Option 2"},
        ]
      }]
  });

  ga('gaCrm:displayForms'); // this can be called ahead since it's loaded async in any case
  ga('send', 'pageview');


 </script>
  <script async src="http://www.flesheatingarthropods.org/crm/ga-crm.js">  </script>

Forms is an object with form configurations (you can configure multiple forms). “Container” is a unique div element in the page code to display the form in. “Url” is the page (path) at which the form is displayed. Sheet is the url of the published apps script web app that the data is written to. The fields object is fairly self-explanatory – for each field you want to have you enter an id, type, name etc.

Spreadsheet (Webapp) setup

Create a new spreadsheet. Copy the example code from the appscript.js file (barebone example to get you going). Add access to Google Analytics via the “Resources” menu item (advanced Google Services). You need to fill in a few variables at the top – the name of your Spreadsheet, the sheet name and the name of the view where the event data is stored. Run the script once to authorize it. Publish as webapp, access for “anybody (even anonymous)”. The script is now ready to receive data via the doGet (and handleResponse, respectively) function.

To pull in GA data create a trigger that runs the gaData function once a day. There is no point in running it more often, as GA needs some processing time. It selected the timestamp and clientid from each row, looks for an GA event with corresponding data and pulls some information from GA and appends it to the spreadsheet row.

The example selects traffic source and medium, if you want other values change the “metrics” (for numerical data) or “dimensions” (for categorical data) fields in the appscript code:

var metric = 'ga:uniqueEvents';
var options = {
  'dimensions': 'ga:sourceMedium,ga:date',
  'sort': 'ga:date',
  'max-results': 1,
  'filters': 'ga:eventLabel==' + clientids[i][0] + ";ga:eventAction==" + clientids[i][2]
};

How does the result look like ?

See a bit of (silent) video. In the video the function that collects data from GA is called manually via the script editor. In real life you’d set a time-based trigger.

Of course you still need a privacy policy and user consent to store the data. But at least you can easily delete PII from a spreadsheet.

Leave a Reply

Your email address will not be published. Required fields are marked *