How to download, extract, transform and combine archived data from Weni Flows

Ilanna Lins Updated by Ilanna Lins

Something very important for you to know about Weni's data is that it is stored for 180 days, right after this period the data is archived and stored in Json format in the Archives menu.

Getting files through Weni Flows

You can download this archived data from the home menu, as shown in the figure below:

You can access both the contents of the Messages table and the Runs table by making the selection as demonstrated:

The files in the Messages table will come with the initial message.

The files in the Runs table will come with the initial run.

Being able to check what content, period (month) and number of records in the file, as shown below:

Loading and transforming archived data into Power BI

  1. After downloading the files and extracting them in Power BI, through Power Query, you will be able to upload them by selecting new source and choosing the JSON option.
  1. By clicking Connect you must change the check box option for All Files, as shown below:
  2. Select the desired file and click open.

In Power Query will appear the option to Import the files, if you want to import them into Power BI, or the DirectQuery option, which makes the direct connection to the file without making its import. I recommend using Import because the file can be changed or removed from the folder.

When you import the file, you'll see that it will already expand all columns that contain data.

It is important that we delete these steps, as shown in the figure below, to get the data clean and to be able to join the tables of the archived data next to the data from the tables that were obtained through the connector.

Combinando as tabelas

  1. To combine the tables, simply select the Combine option and right after, Add Queries, Add Queries as new, as shown in the figure below:
  2. Once this is done, simply select the desired tables and finish the process by clicking ok.
  3. At the end of the process, a new query will be generated with all the data selected in a single table.

We hope this tutorial will be useful to generate more insights for your business.

It's important that you know!

Data connection occurs through the Power BI integration API. The date is structured in JSON format. To access all data fields, you must expand the columns in Power Query in Power BI.

Good Practices

  • Select only the relevant data to optimize the extraction process;
  • Transform your data to make it useful;
  • Make sure your data is well formatted, cleaned, and organized before you start creating your dashboards, reports, and so on;
  • Maintain a data catalog with all the data fields, descriptions, and types of your chatbot project. Also, make sure that your variables, such as contact fields and results, clearly describe their purpose;
  • Understand concepts such as flows, runs, contacts, and messages to interpret and create project insights accordingly;
  • Explore the RapidPro API documentation to familiarize yourself with the availability of the data and how it is structured.

How did we do?

Contact history

Contact