top of page

Complaint Analysis to the Portuguese Administrative Agency

Data analysis of complaints, compliments and suggestions to the Portuguese Administrative Modernization Agency (AMA), over the different services (AMA, social security, pension department) and places (stores, web channels).

Business question: Which are the main reasons for complaints by citizens, and how are services affected?

Tools used: Microsoft Power BI, MySQL.

PowerBI_02.jpg
PowerBI_03.jpg
Key Insights
​

  • 2020 was the year with highest overall number of 1522 actions, 38%, split by 1261 complaints, 139 compliments  and 122 suggestions, with complaints reaching its peak.

  • These numbers may be related to Covid pandemic, where more citizens tried to access public services and some constraints were in place.

  •  2022 was the year with the lowest overall number of 29 actions, 0.7%, split by 10 complaints, 18 compliments and 1 suggestion, excluding the incomplete year of 2023.

  • Portuguese Administrative Modernization Agency (AMA) is the Pulic entity with the highest overall number of actions: 2361 or 59%, and also with the highest number of complaints 1821, 55%, compliments 253, 72%, and suggestions 287, 92%.

  • Local Centers of Social security appear twice on a Top 3 selection of entities, and six times on a Top 10 selection.

  • National Pension Service, ranks 4th overall, and only has complaints as actions, 164 (5% of total complaints).

  • Complaints has the majority of actions with 83% and it is linked to the top reason invoked which is "It was not possible to get the customer service", summing 1132 cases, 28%.

  • The second reason invoked is "Delayed in the response", which accounts for 1090 cases, 27%.

  • The channel most used was Web applications and the average number of days for a response is 577.

  • There is room for improvement, either by expanding the way the services are available to the public and by reducing the time of response or time taken to provide the service.

  • Efficiency and availability are keys for a better customer service,

1. Data Source


Data sources from "dados.gov", provided by the Portuguese Admnistrative Modernization Agency (AMA).
Two datasets used:
 

  • Complaints, compliments and suggestions - Electronic yellow book:

    • Information available was date, type of action, type of communication channel, public service provider, location, cause and average response time.

    • 3 CSV files with a total of 3953 rows and 10 columns.

  • Service Locations:

    • Information available was public service provider, location, latitude and longitude.

    • 1 CSV file with 227 rows and 20 columns.

2. Data Exploration
 

In MySQL, the main skills used were Union, Joins, Case, If statement and Aggregate Functions.
Get aquainted with the data and performed the main tasks:
 

  • Check for duplicate values.

  • Extract the year from the submission date.

  • Substitute null values.

  • Union of the 3 files complaints, compliments and suggestions in a single table.

  • Join the previous table with the service locations table.

3. Data Visualization
 

In Microsoft Power BI created a schema from the different tables.

Then, designed the following visualizations:
 

  • Top bar search for location and type of action.

  • Line chart (time series graph) with the number of actions over a 6-year period.

  • Pie chart with the distribution of the type of action.

  • Pie chart with the distribution of the type of communication channel.

  • Stacked bar chart with the number of actions by service provider and the cause of action.

  • Card for KPI, with the average response time in days.

  • Arcgis map with the locations of service providers and type of action.

  • Each vizualization has its specific tooltip.

Other projects

bottom of page