How to Build a Google+ Community Dashboard with Klipfolio
In my previous blog I showed how import.io Magic extractor could be used as a data source in Klipfolio. Continuing where we left off, I will share some tips and tricks I learned while developing the GA G+ Community Dashboard.
Learn to use XPath references
I was unable to reference the right data elements and initially thought it was a limitation or a bug caused some of the JSON returned by import.io had attributes with “/” in their names. Yvonne from customer support quickly solved my issue: switch from “Simple” to “XPath” data selection. For example, if the JSON attribute is header_link/text: “http://www.stephanehamel.net/” [formerly immeria.net], the simple selection method won’t work, but an XPath will. In this case, the reference needed to be /tables/results[@name=’header_link/text’] and that did the trick.
More information about XPath can be found here.
Filtering based on a date range
When using a data source that doesn’t allow you to specify starting & ending dates, you have to filter the data directly from your formula. Suppose you want to show the last 30 days of data - the formula would be something like:
SELECT( value, BETWEEN( date_field, DATE_ADD( MAX( date_field ), day, -30 ), MAX( date_field ) ) ) )
Let’s look at each part:
- MAX( value ) will retrieve the most recent date from your data source
- DATE_ADD( value, unit, amount) will let us subtract 30 days from the most recent date
- BETWEEN( value, start, end ) will return TRUE or FALSE depending if the value is within the specified range.
- SELECT( data, condition ) will keep the given data only when the condition is true.
This article from the Klipfolio knowledge base offers another example. I had to apply the formula to each and every elements of my Klip, which makes the overall dashboard editing process more cumbersome and error prone. It would be nice if there was a way to apply a filter directly at the data source level or for all Klips within a dashboard so we don’t have to create lots of nested formulas.
Using the SET function
Continuing on the previous example, you might have noticed the MAX( date_field ) is referenced twice in the formula. If you create a new variable, you will be able to assign it a value with the SET function and simplify your formulas. The syntax is:
SET( variable name, value, expression using this variable )
So for my example, I would do:
SET( “maxDate”, MAX( date_field ), SELECT( value, BETWEEN( date_field, DATE_ADD( max_date, day, -30 ), max_date ) ) )
Putting it all together
To count the number of posts over the past 30 days, the complete formula looks like this:
SET( “maxDate”, MAX( DATE( “/tables/results[@name=’fi_link/title’]”, “MMM d, yyyy, h:mm:ss a”) ),COUNT( SELECT( fi_link, BETWEEN( DATE( “/tables/results[@name=’fi_link/title’]”, “MMM d, yyyy, h:mm:ss a” ), DATE_ADD( maxDate, day, -30 ), maxDate ) ) ) )
Klipfolio’s ability to work with virtually any data source and the flexibility in the way Klips are created makes it immensely powerful. However, an example like the one above will easily baffle those who are not familiar with programming concepts and data manipulation techniques.
You can get going very fast with KlipStarts - pre-built dashboards and Klipfolio offers tons of training, support and knowledge base articles. In my experience, building a sophisticated dashboard requires fundamental knowledge of visualization and data concepts, as well as experience with the tool at hand - and this experience won’t happen magically: you need to test, experiment, and build expertise over time.
It took me about 20 hours from start to finish:
- Working with import.io Magic connector to test how data of a G+ community would show up;
- Setting up the import.io API as a new data source in Klipfolio and learning about the use of XPath to get the right data;
- Gradually building individual Klips with increasing level of complexity;
- Finalizing with the use of a date-range filter using the SET() function;
- Quality assurance: making sure the results are fine.
The result looks like this:
Subscribe to Happy Dashboarding
Learn the art and science of building world-class dashboards