- Download the generated report from the Analytics function.
- Open a new excel file > click on Data ribbon > click Get Data from Text or csv > select downloaded report file.
- Ensure that Delimited is selected, click Next.
- Ensure that Tab is checked, click Finish.
- You can refer to Current generated report header on the download panel to understand how the data is organised.
- Delete any unwanted columns within the imported data e.g. system generated IDs.
- In case you need to share the raw data file, do remove links to external files. You can so so by copying all data and 'paste special' (value only) into a new tab in excel, and then delete the old tab.
- Do note that all timestamps are based on Coordinated Universal Time (UTC). If you wish to analyse data in a certain timezone, for instance, in Singapore's timezone (UTC +8), you will need to convert the current timestamps. To do this, use the following formula in a new column beside the timestamp column:
"=current timestamp cell + 8/24"
- Right click on the top row of the spreadsheet in order to insert an extra row for column headers at the top of the spreadsheet.
- Put in corresponding titles to each column within the spreadsheet.
- Convert Timestamps into a comprehensible format by highlighting the time columns.
- Now, you will have a clean set of data to start your analysis!
Although there are many ways to analyse your reports, pivot tables are a common tool to discover patterns and insights inside your data.
- To get started with your Pivot Table, highlight the entirety of the data, then click the ‘data’ tab followed by the ‘pivot table’ button
- This will create a new spreadsheet with a customizable pivot table
- The ‘PivotTable builder’ allows for an easy comparison between data columns, simply tick the data column titles you wish to compare and drag them into rows, columns or values depending on how you want to compare the data.
The example below shows a channel activity summary using the data from the channel and status column (where channel is used as row while status is used both as column and as value (in the form "Count of status")).
Pivot tables can be used to identify a number of useful information, including which users were most active, each user's completion rate, when they were active and what content they have completed. If you need help with analysis your data, please get in touch with your Account Manager