Yuhui's Data Science Blog

When it comes to using Google Analytics data in R, you have quite a number of packages to work with. One such package that has seen recent, frequent updates is googleAnalyticsR , written by Mark Edmondson. googleAnalyticsR works great when you're running it in your own script. But when you need to use it on a remote server, for example, to generate scheduled reports, you may find yourself jumping through hoops and hurdles just to set this up, particularly with authorization. Here's a guide based on my experience of setting up googleAnalyticsR on a Google Cloud Platform (GCP) Compute Engine instance. It covers all the required setup steps, from software installation to authorization to running a quick test. How to setup googleAnalyticsR on a Google Cloud Platform Compute Engine instance Setup a Compute Engine instance The bare minimum specifications for your Compute Engine instance should be: n1-standard-1, i.e. 1 vCPU with 3.75GB memory 10GB boot disk running the ...

Recently, I was presented with a problem in BigQuery: How do I make a table's metrics be returned as rows? Normally, metrics are presented as columns and dimensions as rows. But in this case, I needed the metrics to appear as rows too. In short, I needed to "unpivot" a table like this: Dimension1 Dimension2 Metric1 Metric2 Metric3 to this: Dimension1 Dimension2 Metric Data Metric1 Metric2 Metric3 Metric1 Metric2 Metric3 Metric1 Metric2 Metric3 The solutions lay with three BigQuery features: STRUCT type ARRAY s CROSS JOIN with the UNNEST operator How to get unpivoted results in BigQuery Let's say I have data like this in a table called "Campaign_Results": Row Superhero Title Date Impressions Clicks Conversions 1 Superman Man of Steel 2013-06-14 5670096 796191 82538 2 Batman Batman v Superman: Dawn of Justice 2016-03-25 9742525 876292 63812 3 Wonder Woman Wonder Woman 2017-06-02 4847506 420674 23428 Bu...