The online marketing channels such as Email, Pay per Click (PPC), Video, Social Media and Mobile are growing more rapidly than traditional offline marketing channels such as Direct Mail, Newspaper and TV Ads. Marketing professionals need to integrate both online behavioural data and offline CRM data together, in order to create a sales and marketing process that takes advantage of the best of both worlds.
The main reasons behind the growth of online CRM include more accurate targeting, more predictable ROI and the availability of free web analytics reporting tools such as Google Analytics (GA). With GA, you can track and measure many types of online campaigns including social media, and conduct A/B and multivariate testing. GA is also capable of tracking and reporting offline campaigns such as phone and TV campaigns, creating an affordable platform for integrated marketing.
But GA metrics are available only at an aggregated level, which means you cannot readily integrate it with offline CRM systems. Based on our recent investigation, this blog post outlines the steps to break down the aggregated metrics into individual visitor and session level data and how to export and integrate it with the offline CRM systems.
There are three steps involved:-
- Extracting the visitor and session level data from the GA tracking process
- Exporting the web metrics at visitor and session level
- Pass data to the offline CRM databases
1. Extracting the visitor and session level data – you can modify the existing GA javascript tracking code to include a new custom variable that collects the visitor and session level data from first party visitor cookies that is written/read by GA on the customer’s PC. The data is stored in GA servers and you will need to access it through GA data export API (explained in the next step).
2. Exporting the web metrics – the visitor or session level data collected in the above step can be accessed via the GA data export API after authentication through the main GA account. You can export the data as a text file which will enable it to be integrated into most offline CRM systems.
3. Pass data to the offline CRM databases – Once you have the data as a text file, the online data can be matched to the offline data with identifiable information such as surname and postcode. This information can be either collected from the order transactions or when the visitor submits some type of form or engages in some mechanism that includes identifiable information.
I read an article recently regarding the inclusion of a new cookie based European internet law, which will see individuals having to ‘opt in’ to sharing their cookie information. I would imagine this would severely affect the volume of visitor and session level data that google analytics uses.
Thoughts?
http://www.silicon.com/technology/security/2011/03/09/uk-delays-enforcing-new-eu-privacy-law-on-cookies-39747111/
Thanks for your comments Chris.
It is still unclear how these regulations will be implemented. But given the likely impact on EU’s e-commerce revenue and competitiveness, the solution may simply require the browser’s cookie settings to be more obvious and easier to understand rather than getting the user to ‘opt-in’ through a pop-up box.
Whatever the solution, the specific impact on GA will be the same as any other cookie based solution provider and GA serves only first party cookies. It’s also worth noting that GA serves only first party cookies, and cookie based tracking is more established than other forms such as web logs (inability to crunch data) and packet sniffers (privacy issues).
Hi Saran,
Great article. Looks like a bit of a manual exercise – “human in the loop” ETL! Can you think of ways to automate this process – can you for example call the GA data export API from SQL Server Integration Services?
Matt
Thanks for your comments Matt.
It is feasible to automate the ETL process and integrate it with SQL server databases by developing custom solutions in Java or PHP, or even Pentaho! Alternatively, there are also many affordable off the shelf solutions available.
Once you have integrated the clickstream data with SQL Server databases, you can apply the data mining structures available through SQL Analysis Services as an added bonus!