I have very little against Google Analytics<\/a>\u2019 default dashboards<\/a>. The reason I shy away from them is because they lack the type of customization I\u2019ve come to expect from a dashboarding tool. On top of that, they only let you look at GA data, and I learned early on in my career that focusing on just one vertical is one of the cardinal sins you can make as an analyst.<\/p>\n Furthermore, over the years I\u2019ve become more and more disenchanted with GA\u2019s clunky user interface, and the less time I spend in it the better for my serotonin levels. In fact, GA\u2019s excellent Core Reporting API<\/a> combined with a platform like Tableau<\/a> or Klipfolio<\/a> to manage and manipulate the data stream has become invaluable in my everyday work.<\/p>\n Well, in this article I want to show you a glimpse of a typical process I follow when creating a dashboard for my clients. My platform of choice is Klipfolio<\/a>. It\u2019s a pretty sweet dashboarding tool, with the added benefit of functioning as a low-level data store, where you can combine data from different data sources (and there are practically limitless integrations in the tool) as the input in your visualizations.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n I shouldn\u2019t even have to say this, as I don\u2019t do commercials, but this article is in no way solicited or requested or paid for by Klipfolio. I\u2019m just a fan of the tool, and I want to show you how it works.<\/p>\n In this guide, I\u2019ll walk you through creating a single Klipfolio widget (or Klip) which pulls its data from Google Analytics. I\u2019ll let you figure out the rest yourself. The data in the final dashboard (seen above) is pulled from a number of customizations I\u2019ve done over the years, mainly:<\/p>\n I\u2019m positive you don\u2019t have all these customizations on your website, but perhaps the following steps will be useful to you when creating your own Klips and visualizations.<\/p>\n \n \n Subscribe to the Simmer newsletter<\/a> to get the latest news and content from Simo Ahava into your email inbox!\n <\/p>\n <\/span><\/p>\n Klipfolio revolves around two data structures: the data sources themselves (which store the pulled-in data), and Klips (which are the widgets you add to your dashboards). This is a pretty self-sufficient system, as once you\u2019ve created a data source, you can set it to refresh anywhere from never to every 15 minutes to every 24 hours. A refreshed data source shows up as a cool animation on the dashboard itself, so people looking at the tool will concretely see how the data set is being updated.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n The Klips themselves offer you a handful of ready-made visualizations, with the option of writing your own HTML styling if you really want to customize the output. When you create a Klip, you add one or more<\/strong> data sources to it, so you can actually visualize data across<\/em> data sets.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Creating a Klip is pretty much like working with Excel. You have flat tables of data (the data sources), and you transform and combine them with your typical spreadsheet functions. It\u2019s pretty intuitive if you\u2019ve ever worked with spreadsheet formulae, but if you haven\u2019t, there\u2019s definitely going to be a learning curve with creating new Klips.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Finally, the data sources themselves. Let me put it this way, you won\u2019t run out of options! In addition to having integrations with some of the most popular SaaS platforms out there (e.g. Google Analytics, Dropbox, Facebook, Salesforce), you can integrate with any<\/strong> HTTP REST API, SQL database (with online access) and RSS feed. And if that isn\u2019t enough, you can also send the data as email attachments, or upload it as CSV or Excel files, for example. Lots and lots of options!<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n In this article, we\u2019ll only use the Google Analytics connector, and the Klip we\u2019ll create is fairly simple and intuitive. But trust me, there are almost limitless possibilities for data transformations and visualizations with this tool!<\/p>\n The Klip we\u2019ll create is the easiest one. It\u2019s a simple table with performance statistics on my articles over the last 30 days. The three things we\u2019ll measure are:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Pageviews and Average Time On Page are GA\u2019s built-in metrics. Average Engagement Time is a customization which measures the time users actually interact (move the mouse, click around, scroll, press keys) on each page. The reason this interests me is because Google Analytics\u2019 Time On Page metric is next to useless. It measures the difference in time between two pageviews. But this is a completely arbitrary time measurement, as it tells you absolutely nothing about how much time the user ACTUALLY spent on the page. They could have just opened it in a browser tab and left it be. With Average Engagement Time, we get an idea of how much the users are actively<\/em> viewing the page.<\/p>\n Anyway, the first thing we need to do is create a data source. Since we only have one dimension (page title) and three metrics, we\u2019ll manage with a single data source. In Klipfolio, go to Library<\/strong> -> Data Sources<\/strong>, and then click Create a New Data source<\/strong>.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Next, choose the Google Analytics<\/strong> connector. You will enter a screen where you need to first authenticate with Google to allow Klipfolio read-only access to your Google Analytics data. So, click Sign in to Google<\/strong>, and follow the prompts to authenticate Klipfolio with the Google account that has access to your data.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Once you\u2019ve authenticated, you should find yourself in a screen where you can Configure your Google Analytics query<\/strong>. You could just use this interface to build the query, but I\u2019ve got a better idea!<\/p>\n Browse to the Google Analytics Query Explorer<\/a>. It\u2019s an amazing tool that lets you test and build Google Analytics API queries on your data. Note! You might need to authenticate again, so that this<\/em> tool, too, can access your GA data.<\/p>\n So, what you need to do first is build the query in this tool. You don\u2019t have to worry about the Date parameters, so choose any dates which give you data. The query I\u2019m building for the current Klip looks like this:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n I can\u2019t choose Average Engagement Time<\/strong>, since it\u2019s a calculated metric<\/a><\/em> and the Query Explorer doesn\u2019t seem to support them yet.<\/p>\n Once the query is built, you can click Run Query<\/strong> and make sure it returns data.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n So now that we know the query works, you can just scroll all the way to the bottom of the page, and copy the whole URL string within the API Query URI<\/strong> text box.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Now, go back to Klipfolio, and paste the copied URL into the Query URI<\/strong> box. You might need to select \u201cAdvanced\u201d in the Mode menu first.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n You can click Get Data<\/strong> to make sure that it returns the same data set you got when using the Query Explorer.<\/p>\n But some things were missing. First of all, we need to modify the date parameters to get only the last 30 days, and we also need to add the calculated metric into the mix.<\/p>\n To add dynamic date values, Klipfolio has those as built-in variables. You can read more about them here<\/a>. For example, to change the {date.add(-30).format()}<\/strong><\/p>\n For To add the Average Engagement calculated metric, you need to get the metric\u2019s external name<\/em>. You\u2019ll find this in the Calculated Metric<\/a> settings of Google Analytics, by clicking open the entry you want to fetch into the query:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n To add this metric to the query, add it after ga:pageViews<\/strong>, separated by a comma.<\/p>\n The final, complete query URL looks like this:<\/p>\n In English, the query is:<\/p>\n \u201cIn the Google Analytics profile with ID XXXXXXXX, fetch me Page Views and the calculated metric Average Engagement 3, together with Average Time On Page. Query these against the Page Title dimension, and set the start date of the query to 30 days ago. The end date should be yesterday. Sort them by Page Views, in descending order, and do not include rows which do not have any values.\u201d<\/p>\n Click Get Data<\/strong> to make sure the data it returns is valid.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n If you\u2019re satisfied with the results, click the big Continue<\/strong> button in the bottom of the screen.<\/p>\n Finally, give your new data source a descriptive name and set its refresh rate. Since in this example the &end-date;<\/strong> parameter is yesterday<\/em>, you don\u2019t need to refresh the data source all the time, just once per day should suffice. Nevertheless, I always choose a shorter refresh rate in case the timezones of Klipfolio and Google Analytics don\u2019t match.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n When you\u2019re done, click Save<\/strong>, and you\u2019ll be transported to the data source view for this new data set.<\/p>\n Next up, building the Klip!<\/p>\n So now we have the data source, and we\u2019re ready to turn it into a widget, also known as a Klip. While still in the data source view, click the big orange button labelled Build a Klip with this Data Source<\/strong>:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n The first thing you need to choose is the component type. You can combine multiple component types in a single widget, but in this case we\u2019re only interested in a Table<\/strong>, so choose that.<\/p>\n You\u2019re taken to the Klip Editor. The editor lets you turn a data table into a visual representation thereof. You can use formulae to modify the data as it\u2019s visualized in the graphs, and you can add data from multiple data sources into a single widget! This is an amazing feature, and turns Klipfolio into a light-weight Business Intelligence reporting tool.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n It\u2019s going to take some time to get acquainted with all the features of the editor, but once you get the hang of it, there\u2019s a very simple flow to it. Here\u2019s a great tutorial<\/a> on how to use the editor.<\/p>\n Anyway, it\u2019s easiest to learn by doing. Select the leftmost column of the table by clicking either the column in the Preview screen or the top-most column in the hierarchy view in the left side of the editor.<\/p>\n The first thing we need to do is select data for this column. Because we want to align page titles with their respective pageview counts and engagement times, we\u2019ll need to add the page titles to this first column. Thus, while having the first column selected, click the header A<\/strong> in the data table to select all the data in this column.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n When you click the column, the following things happen automatically:<\/p>\n The formula bar gets a single entry: A:A<\/strong>, which is spreadsheet language for \u201ceverything in the A column\u201d<\/p>\n<\/li>\n The first column in the table is automatically filled with data in the same order as it is in the data source<\/p>\n<\/li>\n<\/ol>\n So now you\u2019ve created your first formula, congratulations! However, there\u2019s a slight hiccup. As you can see, the header row (ga:pageTitle<\/strong>) is the first row in the chart. We don\u2019t want that! To get rid of it, we need to apply a function<\/em> to the A:A<\/strong> selection.<\/p>\n So, while still having the A:A column selected, click the Wrap Function<\/strong> button in the toolbar.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n This lets you wrap a function around any part of the formula you have selected. You\u2019ll see the function selector appear, and it has quite an impressive list of functions. Many of them should be familiar from spreadsheet tools, but you might want to bookmark Klipfolio\u2019s function reference<\/a>.<\/p>\n Anyway, to get rid of the first row of data, simply select SLICE<\/strong> from the list. It automatically removes the first row of data from the widget. Note that you can specify start<\/em> and\/or end<\/em> as parameters, but we don\u2019t need to do that as SLICE removes the first row by default.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n And that\u2019s our first data column!<\/p>\n Next, we\u2019ll repeat these exact steps for the column with the header ga:pageViews<\/strong> in the second column of the table. If you do it correctly, you should see the following:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n And then we\u2019ll do the same for the two remaining columns, adding ga:calcMetric_AverageEngagement3<\/strong> to the third column, and ga:avgTimeOnPage<\/strong> to the fourth and final column. So now you should see this:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n So now we have our data, but it\u2019s not looking good at all! For one thing, the data is unformatted, and this means that the duration metrics (average engagement and average time on page) are pretty indecipherable. So, let\u2019s start formatting! Select the first column (the one with the page titles) in the Preview, and click the Properties tab.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Let\u2019s start adding some bling to the widget. First, type \u201cPage Title\u201d into the Column Header<\/strong> field. You should see the change in the Preview immediately.<\/p>\n Next, we want to make sure the Page Title has the largest width in the table, since all the other columns only contain numbers. So click the Fix column to a specific width\u2026<\/strong> checkbox, and type 50%<\/strong> into the field that appears. Again, you should see the change immediately. If you want to stretch the Preview table to better see how the widget will look on a full-screen dashboard, you can resize the table by dragging from the right side.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n That\u2019s better!<\/p>\n Next, choose the second column, and go to Properties again. First, set the Column Header to \u201cPageviews\u201d. Then, select Number from the Format As<\/strong> menu. This formats the numbers with commas as the thousands separator.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Finally, since it\u2019s a column of numbers, make sure they\u2019re right-aligned. If you don\u2019t like this practice, you can of course leave it as it is. Now the table should look like this:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Next, choose the third column and click Properties. Set the Column Header to \u201cAvg. Engagement Time\u201d, and choose Duration<\/strong> from the Format As drop-down. This will automatically convert the numerical value in the data set to minutes and seconds. So 78.135353 in the data set, for example, would become 1m18s<\/strong> after the formatting change. Finally choose Right-alignment again to make sure the alignment matches the previous column.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Repeat these properties in the final column, though remember to change the Column Header in the final column to \u201cAvg. Time On Page\u201d.<\/p>\n Now the table should look like this:<\/p>\n![\"Klipfolio](\"https:\/\/www.simoahava.com\/images\/2016\/04\/content-dashboard-klipfolio.jpg#ZgotmplZ\")
<\/p>\n
\n X<\/span><\/p>\n
\n The Simmer Newsletter<\/strong>\n <\/p>\nHow Klipfolio works<\/h2>\n
![\"refresh-time\"\/](\"https:\/\/www.simoahava.com\/images\/2016\/04\/refresh-time.jpg#ZgotmplZ\")
<\/p>\n![\"Multiple](\"https:\/\/www.simoahava.com\/images\/2016\/04\/multiple-data-sources.jpg#ZgotmplZ\")
<\/p>\n![\"Klip](\"https:\/\/www.simoahava.com\/images\/2016\/04\/formula.jpg#ZgotmplZ\")
<\/p>\n![\"Data](\"https:\/\/www.simoahava.com\/images\/2016\/04\/connectors.jpg#ZgotmplZ\")
<\/p>\nArticle performance table<\/h2>\n
![\"article](\"https:\/\/www.simoahava.com\/images\/2016\/04\/article-performance-table.jpg#ZgotmplZ\")
<\/p>\nStep 1: Create the Data source<\/h2>\n
![\"New](\"https:\/\/www.simoahava.com\/images\/2016\/04\/new-data-source.jpg#ZgotmplZ\")
<\/p>\n![\"Sign](\"https:\/\/www.simoahava.com\/images\/2016\/04\/sign-in-to-google.jpg#ZgotmplZ\")
<\/p>\n![\"Google](\"https:\/\/www.simoahava.com\/images\/2016\/04\/query-explorer.jpg#ZgotmplZ\")
<\/p>\n![\"Run](\"https:\/\/www.simoahava.com\/images\/2016\/04\/run-query.jpg#ZgotmplZ\")
<\/p>\n![\"API](\"https:\/\/www.simoahava.com\/images\/2016\/04\/api-query-uri.jpg#ZgotmplZ\")
<\/p>\n![\"GA](\"https:\/\/www.simoahava.com\/images\/2016\/04\/ga-query-klipfolio.jpg#ZgotmplZ\")
<\/p>\n&start-date;<\/code> parameter in the query automatically to 30 days before the query was made, you\u2019d use:<\/p>\n&end-date;<\/code> we\u2019ll just use Google Analytics\u2019 own shorthand yesterday<\/strong>.<\/p>\n![\"Google](\"https:\/\/www.simoahava.com\/images\/2016\/06\/calculated-metric.jpg#ZgotmplZ\")
<\/p>\nhttps:\/\/www.googleapis.com\/analytics\/v3\/data\/ga?ids=ga:XXXXXXXX&metrics=ga:pageViews,ga:calcMetric_AverageEngagement3,ga:avgTimeOnPage &dimensions=ga:pageTitle&start-date={date.add(-30).format()}&end-date=yesterday&sort=-ga:pageViews&include-empty-rows=false<\/code><\/p>\n![\"Get](\"https:\/\/www.simoahava.com\/images\/2016\/04\/get-data-klipfolio.jpg#ZgotmplZ\")
<\/p>\n![\"Save](\"https:\/\/www.simoahava.com\/images\/2016\/06\/save-data-source.jpg#ZgotmplZ\")
<\/p>\nStep 2: Create the Klip<\/h2>\n
![\"Build](\"https:\/\/www.simoahava.com\/images\/2016\/06\/build-a-klip.jpg#ZgotmplZ\")
<\/p>\n![\"Klip](\"https:\/\/www.simoahava.com\/images\/2016\/06\/editor-view.jpg#ZgotmplZ\")
<\/p>\n![\"Select](\"https:\/\/www.simoahava.com\/images\/2016\/06\/select-column.jpg#ZgotmplZ\")
<\/p>\n\n
![\"Wrap](\"https:\/\/www.simoahava.com\/images\/2016\/06\/wrap-function.jpg#ZgotmplZ\")
<\/p>\n![\"Slice](\"https:\/\/www.simoahava.com\/images\/2016\/06\/slice.jpg#ZgotmplZ\")
<\/p>\n![\"The](\"https:\/\/www.simoahava.com\/images\/2016\/06\/second-column.jpg#ZgotmplZ\")
<\/p>\n![\"All](\"https:\/\/www.simoahava.com\/images\/2016\/06\/all-columns-filled.jpg#ZgotmplZ\")
<\/p>\n![\"Properties](\"https:\/\/www.simoahava.com\/images\/2016\/06\/properties.jpg#ZgotmplZ\")
<\/p>\n![\"Resize](\"https:\/\/www.simoahava.com\/images\/2016\/06\/resize-table.jpg#ZgotmplZ\")
<\/p>\n![\"Pageviews](\"https:\/\/www.simoahava.com\/images\/2016\/06\/properties-second-column.jpg#ZgotmplZ\")
<\/p>\n![\"Table](\"https:\/\/www.simoahava.com\/images\/2016\/06\/table-after-second-column-properties.jpg#ZgotmplZ\")
<\/p>\n![\"Duration](\"https:\/\/www.simoahava.com\/images\/2016\/06\/duration-properties.jpg#ZgotmplZ\")
<\/p>\n
![\"All](\"https:\/\/www.simoahava.com\/images\/2016\/06\/all-data-formatted.jpg#ZgotmplZ\")
<\/p>\n![\"Dashboard](\"https:\/\/www.simoahava.com\/images\/2016\/06\/dashboard-view.jpg#ZgotmplZ\")
<\/p>\n