In this post we will explain how to save Google Analytics (GA) acquisition channel information into your own database – namely the sourcemediumtermcontentcampaign, and gclid parameters that were present on a user’s first visit to your website. For an explanation of these parameters, check out the Google Analytics documentation. Then, we will explore some of the powerful marketing analyses that can be performed with this information in RJMetrics.

Why?

If you’re just looking at the default Google Analytics conversion and acquisition metrics, you aren’t getting the whole picture. While seeing the number of conversions from organic search versus paid search is interesting, what can you do with that information? Should you spend more money on paid search? That depends on the value of customers coming from that channel, which is not something Google Analytics provides. [Note: Google Analytics eCommerce Tracking does mitigate this problem by storing transaction data in GA, but this solution doesn’t work for non-eCommerce sites, and certain tools like cohort analysis are not easy to do in the GA interface].

What if you want to email a follow-up deal to all customers acquired from a certain e-mail campaign? Or integrate acquisition data with your CRM system? This is impossible in GA – in fact, it is against the Terms of Service for Google Analytics to store any data that identifies an individual.  But that doesn’t mean you can’t store this data yourself.

The Method

(Special Note: If you are using Magento to power your eCommerce site, we’ve already done the work for you. Check out our free acquisition source tracker extension. Not only does it track each user’s acquisition source data, it also tracks each order’s source data.)

Google Analytics stores visitor referral information in a cookie called __utmz. After this cookie is set (by the Google Analytics tracking code), its contents will be sent with every subsequent request to your domain from that user. So in PHP, for example, you could check out the contents of $_COOKIE['__utmz'] and you would see a string that looks something like this:

100000000.12345678.1.1.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=rj metrics

There is clearly some acquisition source data encoded into the string, and I have done some testing to confirm that this is the visitor’s first acquisition source. Now we just need to know how to extract the data. Luckily, Justin Cutroni has previously described how this encoding works, and shared some javascript code to extract the key bits of information.

We took this code and translated it into a PHP library hosted on github.   To use the library, include a reference to ReferralGrabber.php and then call

$data = ReferralGrabber::parseGoogleCookie($_COOKIE['__utmz']);

The returned $data array will be a map of the keys source, medium, term, content, campaign, gclid and their respective values.

We recommend adding a new table to your database called, for example, user_referral, with the columns like: id INT PRIMARY KEY, user_id INT NOT NULL, source VARCHAR(255), medium VARCHAR(255), term VARCHAR(255), content VARCHAR(255), campaign VARCHAR(255), gclid VARCHAR(255). Whenever a user signs up, grab the referral information and store it to this table.

How to use this data

Now that we’re saving user acquisition source, how can we use it?

Lets suppose we are using a SQL database and have a users table with the following structure:

id email join_date acq_source acq_medium
1 john@abc.com 2012-01-24 google organic
2 jim@abc.com 2012-01-24 google cpc
3 joe@def.com 2012-01-25 direct
4 jess@ghi.com 2012-01-26 referral techcrunch.com
5 jen@ghi.net 2012-01-30 other organic

For starters, we can count the number of users coming from each referral channel by running the following query against your database:

SELECT acq_source, COUNT(id) as user_count FROM users GROUP BY acq_source;

The result will look something like this:

acq_source user_count
google 294
direct 156
referral 55
other 16

This is interesting, but of limited use. What we would really like to know is the growth rate of these numbers over time, the amount of revenue generated by each acquisition source, a cohort analysis of users coming from each source, and the probability that a user from one of these channels will return as a customer in the future. The queries required to do these analyses are complex – which is why we built RJMetrics. Armed with this information we can determine our most profitable acquisition channels and focus our marketing time and money accordingly.

My colleague Xiao has also written a blog post detailing how to generate these and other useful marketing analyses using RJMetrics for you to check out.

ad-13

  • km

    thanks for sharing. works well, however i have 50% of rows with ‘-‘ for all columns except the email and join date. any help in resolving this would be greatly appreciated 🙂

  • Marco

    Integrating GA with your CRM is actually pretty straightforward. The trick consists in adding a few lines of code to your contact form that will fetch Google Analytics’ cookies’ information and send it to your CRM system.
    Here’s an article on how to do ithttp://www.clicksolutions.es/en/blog-articles/80-integrating-google-analytics-with-your-company-s-crm

  • Pingback: Attribution Modeling for Startups - Ecommerce Blog

  • Pingback: 5 Best Practices for UTM tagging - Ecommerce Blog

  • Ray Troy

    Chris, this is a great article. I have a question on how you connect a ACQ_MEDIUM to a user if you are not using special landing pages? How do you know which user is which that signed up with CPC? Thanks!

  • Stu

    Hi Chris. We’ve been capturing this info on our form submissions for some time now, but with the coming of universal analytics they no longer add this to their cookie. DO you have any thoughts on a work around?

    • http://rjmetrics.com/ Tristan Handy

      Hey Stu. We’re actually in the middle of figuring out a solution to this right now; I don’t have a good answer for you at the moment. I’ll respond with more info as we have it.

      • Stu

        We’re in the final testing stage of our solution so will post an update when we’re happy with it.

        • pnegri

          How this is going? A month ago, have some clients unhappy with this.

        • http://www.georgemc.co.uk/ George McEntegart

          Can you give me a shout when you figure this out as well please? 🙂

        • Nikhil Vij

          Any updates?

        • Stu

          Sorry folks, let me grab some info for you

        • Tim Smith

          Stu’s colleague Tim here with an update of what we did, which was to replace the Google Analytics cookie with some of our own design.

          It’s been a couple of months so excuse me if I ramble while I go through my code.

          High level summary:
          1. Javascript code checks whether this is the first page of the visit, detects URL Parameters and saves them as cookies
          2. These cookies are read and inserted in the form with jQuery

          I can highly recommend the Mozilla Developer Network’s article about cookies with a framework for reading/writing
          https://developer.mozilla.org/en-US/docs/Web/API/document.cookie

          You will also discover that some of the information from Google is now in the form of a VED code. The Deed Poll Office in the UK has done some excellent work on decoding these, which you can find at
          /https://deedpolloffice.com/blog/articles/decoding-ved-parameter

          You may also want the Base64 http://code.google.com/p/stringencoders/source/browse/#svn/trunk/javascript
          code to decode the Vedcode in IE (every other browser can just decode Base64).

          It’s not perfect as I’m storing each URL parameter as a separate cookie, which won’t give the best performance.
          There’s always the next version!

          The curious discovery about the process is that not only does the search engine involved affect the information passed through the URL variable but the /browser/ as well. I had to test all the combinations of browser and search engine.

          An alternative approach might be to use the Data Layer – I’ve used that for getting data into Universal Analytics Events. Don’t seem to get enough rainy days to play with all the different options!

    • Peyton Sherwood

      Also interested in this — have been searching Google’s docs with no success — is there some kind of API access to data now that there’s a customer ID?

    • Chris Merrick

      Hi Stu. Universal Analytics no longer stores this same information in a cookie, so this approach won’t work. We’re working on a product enhancement to make this completely seamless for RJMetrics users, but until then, your best bet is to write a javascript routine that will store the data in your own cookie, similar to http://www.webvanta.com/post/2011-02-08/using-referrer-urls-to-better-understand-your-visitors

  • HamzaYounas94

    i don’t know why happened i followed the same instructions but getting this error

    Notice: Undefined index: __utmcsr in /home/aklpcp2k/public_html/dissertationpros.co.uk/includes/footer.php on line 3
    array(6) { [“source”]=> string(1) “-” [“medium”]=> string(1) “-” [“term”]=> string(1) “-” [“content”]=> string(1) “-” [“campaign”]=> string(1) “-” [“gclid”]=> string(1) “-” }

  • Nischay Nahata

    Does this work with Universal Analytics?

    • Голубев Евгений

      No