Community reporting: Create a dashboard

  • 25 October 2017
  • 6 replies
  • 219 views

Userlevel 4
Badge
The setup of your dashboard depends on the data you want to report on. The explanation of creating a dashboard (and updating an existing dashboard) which is provided below is based on the use of Excel, but you’re free to use any other tooling you’d prefer. Contact your Community Consultant for an example dashboard (also created in Excel).

Separate the columns of the exports


The downloaded report is in CSV format, which means the columns first need to be separated with a comma before you’ll be able to create a dashboard.

N.B. Some Excel versions automatically separate the columns when you open the file, if that is the case you can skip step 1 to 5.
  1. Open the downloaded export file in Excel, select the first column and click on Data – Text to columnsThe setup of your dashboard depends on the data you want to report on. The explanation of creating a dashboard (and updating an existing dashboard) which is provided below is based on the use of Excel, but you’re free to use any other tooling you’d prefer. Contact your Community Consultant for an example dashboard (also created in Excel).
  2. Select the option Delimited and click Next
  3. Choose delimiters Tab and Comma, and click Finish.
  4. Copy paste each export in a separate tab in one Excel documentIn addition to the existing columns of each export it is recommended to add extra columns enabling you to filter data for specific time ranges. For example month (=month(X)), week (=week(X)), day (=day(X)), day of the week (=weekday(X)), hour of the day (=hour(X)). Especially in the mark as answer export extra columns are needed to calculate the SLA. To make a calculation on an SLA of 24 hours, use the following formulas: Add a column (Column Y) to the mark as answer export which calculates the difference between the post_answered_creationTime (Column R) and post_1_creationTime (Column K) by using the formula =Integer((R2-G2)*24). Add a column to calculate the SLA based on a conditional formula =IF(Y2<24;”yes”;”no”).

Create a new dashboard


After all exports are added to the Excel document and after adding these columns, start making pivot tables to create the dashboard.
  1. Click on Insert - Pivot table
  2. Select the data you want to analyse by selecting the reach of the specific export
  3. Select where you want the PivotTable report to be placed. This can be either a new worksheet (when no other pivot tables have been created), or an existing worksheet that already contains other pivot tables to create a full dashboard in one worksheet in the Excel file.
  4. Click on OKWhen you have selected the input to create a pivot table you get to see the PivotTable Fields menu. Choose the data you want to show, for example the topics with the most comments in month X (row = topic_title, value = total_comments, filter = month)

Update an existing dashboard


When you already have a dashboard in place there is no need to create a new one. It saves a lot of time to use the existing dashboard and update it with newly derived exports. All exports can be copy-pasted directly under the present exports in the worksheets. However, data such as number of comments, views, public and moderator tags (topic export) and user profile information (user export) won’t be updated in the exports that are already in the worksheet. If you want them updated you should get the full export of that time range as well.
  1. Select the pivot table in the dashboard
  2. Click on Analyse – Change Data Source in the menu bar
  3. Click on the table/range selector to select the new range in the worksheet
  4. Click OK
If you’ve created a filter(s) on your pivot table make sure it is set correctly after you have updated the data source. Continue the above actions until you have created the complete dashboard.

Create a report


The setup of your report depends on the goals of your community and the data you want to report on. Contact your Community Consultant for an example report that shows a basic setup of a community report.

6 replies

Userlevel 5
Badge +3
Great to have an export in csv. One question: why does Insided use different types of date formats? See example:

Userlevel 7
Badge +1
Hey Stéphan,

that's indeed a very good question! I have noticed this as well in the past.

I have spoken to my colleagues, as the "why"-questions are traditionally harder to answer. 😉 To make a long story short, it has historical reasons:

As these fields have been created and added at different stages and in different parts of our data model. This of course was not done intentionally, it is something that happened while the platform was developed over the years.

Of course we are aware of this and we have learned from it. We will make sure that this will not be happening any more once we improve the process of generating exports.

I usually use excel to format the timestamps in a universal format, so that there are no inconsistencies. This way there should not be any issues working with them. If you have challenges here, please let me know and I will have a look at it.

Cheers,

Julian
Userlevel 5
Badge +3
Thanks Julian. I have a challenge :-)

I've exported Topic. The date formats are different (even on the same date).
I changed the format to Date for both columns and added a column to extract the year from the date (=TEXT(Q2,"YYYY")). This works for one format, but not for the other format.

Maybe you have an idea how to solve this.

thanks,
Stéphan

Userlevel 5
Badge +1
Hey Stéphan,

It looks like this could be an Excel only problem as I can't reproduce it on the mac program Numbers or on Google Sheets, so my advice would be to open it in another program and then export it to Excel and it should solve the problem.

If you look into the raw CSV file, I don't see any different timestamp types so it doesn't seem to be an issue with the data we're providing, just how Excel reads it.
Userlevel 5
Badge +3
Thanks Shane. You are right, the csv does not show different formats. I will look for a workaround.
Userlevel 7
Badge +1
Hey Stéphan,

I also wanted to share my thought on this, as I know of this problem and I wanted to present how you can fix this directly within excel.

This is an example column where I saw the issue after opening it in excel:


Select the column which you want to fix, and then right-click into one of the cells. In the pop-up menu, select "Formta Cells":



Here, you can select different pre-defined formats. Please do not go to the "Date" section, but rather choose "Custom" in the bottom of the list. Then type in the correct format in which you want everything to be formatted to:


Once you have done this, excel will automatically interpret and transform the format to what you have selected:


After that, you can proceed with what you actually wanted to do. You can e.g. ask excel to sort the dates from oldest to newest.

Note: It can happen afterwards that excel does not offer "oldest to newest", but rather "A-Z". That is no problem, it only means that excel does not read it as a number, the rest should stay the same.

Please let me know in case you are having problems doing this. Sometimes finding the right format can be a real challenge, however with some practice this will not be an issue for you any more in the future!

Cheers,

Julian

Reply