The main goals of the multichannel marketing (MCM) project can be defined as follows:
- putting in place the infrastructure and tools to track all the marketing related communication with healthcare professionals (in Veeva CRM)
- putting in place the reporting tools on the efficiency of the marketing campaigns and progress towards the goals (in SAS)
The metrics are captured on various systems and in various ways, depending on the type of communication: mails and calls are registered directly in Veeva, live webinar attendance in another tool …
The metrics on the country-specific web sites and the webinars on demand are gathered in Google Analytics. Since the team managing the Veeva project had no clear way to extract the data from Google Analytics and load it into Veeva they reached out to the integration team.
The schema below shows the data flows specific to the MCM-project and for which the integration team was actively involved. Only the green arrows are relevant to the main topic of this blog:
- Source is Google Analytics where the data is spread over different accounts. Data can be queried using REST, the response is in JSON format.
- The SAS system: running on an Oracle database. Connectivity is handled by simple JDBC connections.
- Veeva CRM: running on the Salesforce cloud. Connectivity is handled by the Salesforce Adapter.
- Both destinations have custom data models in place to store the incoming data. Veeva however has the added requirements of data validation checks and data enrichment. For each registered session in Google Analytics (someone visiting the web site) multiple records have to be created in different objects.
In the end the solution turned out to be pretty straightforward:
- Query Google Analytics for new sessions since the last extract.
- Deliver the full dataset to the data warehouse staging tables.
- Walk through the same dataset, perform the necessary checks for each record and create the necessary additional data before delivery in Veeva.
Surprisingly, getting Google Analytics configured for REST connectivity was not trivial. The documentation of Google is truly a maze. This excellent blog post confirmed I was not the only one struggling.
And as often is the case with projects having a considerable amount of stakeholders and external parties, getting hold of the right information, finalize on requirements, coordinate testing … was also a very time-consuming task in this albeit straightforward point to point integration.
Author: Olivier M.
PS: the blue arrows in the image above are part of another solution built for the MCM-project. For this we extended the Tibco architecture with API Exchange and created a vendor-facing web service to expose specific functionality required to complete the solution build by the vendor. More to come in another blog.