Connecting Klipfolio to Pipedrive - 2

In the last blog post, we explained how the ETL system Pentaho Data Integration (also called Kettle) can be installed, opened and linked to Google Analytics, as well as how different data from the tracking tool can be pulled with the ETL tool and finally stored in a Microsoft Excel file in an automated way.

In this post, we will show you how to link multiple Google Analytics accounts with Pentaho Data Integration so that web analysts or users don’t have to keep switching back and forth between accounts in Google Analytics to examine the metrics of the different accounts.

Bar chart creation

Now, to add a bar chart to our clip created in the last blog post, we open our dashboard and click on the menu button (see Figure 1) and select “Edit”.

Ausschnitt Karte

Figure 1: Klipfolio – Select dashboard menu

Now the Klip editor opens and we can select the bar/line Chart on the right side, where the respective Klip display forms are located.

Linien Diagramm

Figure 2: Klipfolio – Bar Chart Klip

We drag this clip below the map clip. In the folder structure in the clip editor we can configure our bar chart.

Balken-/Liniendiagramm

Figure 3: Klipfolio – Klip configuration

In the first configuration step, we adjust the name of the clip for better recognition. This is done by clicking on the three dots (more Action Button) next to the Bar/Line Chart. (see figure 3)

We name this bar chart clip “Bar Chart – App Downloads“, because we want to display the number of downloads of our app by country, just like on the map.

Fill bar chart with data

To visualise the downloads in a bar chart, we click on “Series Unititled” under “Bar Chart – App Downloads” in the Klip Editor and select the “Data” tab further down (see Figure 4).

Balkendiagramm Serie unbenannt

Figure 4: Klipfolio – Klip Configuration Data

Now we click the left mouse button in the input area of the Data window and enter the following formula:

GROUPBY(slice(@TableXXX,C:C),(slice(@TableXXX,B:B;)))

Because from our table in Figure 5, we sum up the number of all downloads (column B) for all countries (column C – optionally also column D), which can also occur multiple times (because per country there can be more than one company that downloaded the app).

Ausschnitt Excel Tabelle

Figure 5: Klipfolio – imported Excel table

Now we have to define the x-axis for the correct display of the values. For this we click on “x-Axies” and enter the following as formula:

GROUP(slice(@Table1,C:C;))

So we group all values except the column title from column C. Optionally, it is possible to select the column D instead of column C. Thus, only the respective country abbreviations would be displayed on the x-axis.

Balkendiagramm

Figure 6: Klipfolio bar chart

Format Bar Chart - Sorting and Filtering

As can be seen in Figure 6, the display is not particularly helpful because a large number of countries are shown and this appears too confusing. As a result, sorting by the number of downloads could improve the display format.

Sorting:

For this, we again click on “Series” in the Klip editor and select the “Sort” drop-down function in the “Properties” tab (see Figure 7).

Sorte: keine Sorte

Abbildung 7: Klipfolio – Daten sortieren

We select “from highest to lowest” as the sort order.

Note:

Optionally, it would also be possible to use the Sort() function under the “Data” tab to output the data sorted.

More precisely: sort(GROUPBY(SLICE(@Table1,C:C;),SLICE(@Table1,B:B;)), “descnumeric”)

Filter:

Now, to display only countries that had at least one download from a company, we can use the filter function from figure 7 in the “Properties” tab for “Series”.

Filter: Serie Unbenannt

Figure 8: Klipfolio filter function

For this we click on “Filter” and switch from the “Range” tab to the “Condition” tab. In the next step we select the condition. In this case, it is all values greater than or equal to one.

Remark:

Filtering could also be implemented using a function, namely select or an extended groupby formula.

More precisely:

  • For Series:  Select(Groupby(Slice(@Table1,C:C),Slice(Table1,B:B)),Groupby(Slice(@Table1,C:C), Slice(@Table1,B:B))>0).
  • For x-axis: Select(Group(Slice(@Table1,C:C)),Groupby(Slice(@Table1,C:C), Slice(@Table1,B:B))>0)

The description of these formulas “Select” as well as “Groupby” will be presented in more detail in the next blog posts.

Ausschnitt farbige Weltkarte

Figure 9: Klipfolio Dashboard – Map & Bar Chart

At this point, it might make sense to copy the bar chart and set filtering for the two charts in Klipfolio. For one chart, we will only show values larger than the 0.75 quantile value by filtering. For the second chart, only values smaller than the 0.75 quantile value are displayed.

This way, the top-performing countries can be identified more quickly, e.g. for management.

IT-WINGS NEWSLETTER

Stay up to date!

More blog posts