text: Google Sheet: Once the data from ExactTarget was in a Google Sheet, it was time process it as follow:a) Build a unique key using the Job/Send ID field. It will later be used to link imported Email data to Web traffic data.b) Getting the data set schema This consists of attributing the imported fields mentioned above to a unique or join key. So each row of data will have a join key. This is mandatory for a successful campaign widening in GA. The join key will actually be the utm_id which I’ll cover in more details below.c) Build a table with all dimensions and metrics imported from ExactTarget so they can be ready to be stored in BigQuery. The reason we had to store all data in Bigquery is that Google Sheet has a limit 2 million cells and we were going to reach that limit in a matter of months if not weeks, so we had to use BigQuery as our main database.
  d) Write scripts that will automatically import campaign widening data into GA and also send all dimensions and metrics to BigQuery for storage. These scripts were scheduled to run daily.
  URL Tagging: This seemingly small step of the process is probably the most important piece of the puzzle. Without it, everything simply falls apart. Here we had to make sure that every outgoing email sent by ExactTarget was tagged with the parameter “utm_id”. This parameter must be added to every landing page URL along with other standard utm parameters (utm_campaign, utm_source, utm_medium…etc.). It’s crucial is to ensure that value passed to this “utm_id” does exist, basically it has to match one of those built under point 2a above. That’s why it’s called a Join Key (its purpose is to stitch imported data to specific web sessions/visits).
  BigQuery: The role is of BigQuery is simple, it stores all dimensions and metrics imported from ExactTarget as mentioned above. Another important reason we used BigQuery was to store metrics since we weren’t able to import them into GA successfully (I’ll probably go over this in another post).
  Google Analytics: Using the Data Import feature of Universal Analytics, we created a data set schema for the campaigns widening. You need to do this so GA knows how to interpret the imported data from the Google Sheet and stores it in the appropriate dimensions. Once the daily data import is successful, GA will then take the utm_id and link it to any interactions generated by Email campaigns users. In order to visualize all of this, we had to create custom reports (see some of them below). There could also be a delay (up to 24 hrs) before any imported data is available for reporting, make sure to plan this ahead of time whenever you import data into GA.