Data pipeline and dashboard for 200+ online channels

We are now able to utilise data from countless different digital channels and to see in an instant the key performance metrics, thanks to the new dashboard. This creates a common ground for organisational units to cooperate, evaluate and develop channels based on user data. Paula Lahti, Project Manager, City of Helsinki


Key objectives

The city of Helsinki owns hundreds of different digital channels in social media and web. The data from these individual channels has been located separately in each platform, making it challenging to get an overview of the different channels and comparing their performance. This lack of being able to see the big picture has made it difficult to understand the value of each channel in comparison to other channels.

The city of Helsinki wanted to have a centralised dashboard, which would combine data from different sources into one location. This dashboard then would be tested for approximately 10 months to see what further improvements could be made.

The dashboard would need to contain data from Facebook, Instagram, Twitter, LinkedIn, Siteimprove, and Google Analytics. The amount of channels from these platforms totals in over 200 individual channels, making it a very unusually large and complex dashboard project.


Our approach

Considering the number of data sources or channels from different APIs, the platform for the dashboard needed to be chosen carefully. We ended up choosing PowerBI as the dashboard platform. PowerBI Data Flows (ETL) together with Azure Data Lake were used to store data. To build the data queries, we used Supermetrics API tool and their own query builder. These queries would then be connected to the Azure Data Lake by PowerBI Data Flows.

columbia-road-data-pipeline

When the data funnel had been validated, we started gathering data rights from different stakeholders and centralised those rights on key accounts of the city of Helsinki. This was important as we didn’t want to rely on Public APIs from different data sources as they are somewhat restricted.

We started to build the architecture by building queries with Supermetrics API tool and dataflows in PowerBI. Dataflow entities allowed us to create automatically refreshing databases, which would then be used in creating the dashboard. Since there was so much data to fetch that queries could take up to a couple of hours, we built a systematic night-time refreshing system to keep the data evenly updated. After this, we had our first minimum viable product ready for comments and feedback.

Finally, we built a finalised version of the dashboard with a focus on the usability and visual side. The dashboard was approved by the client, and we started our support period, during which we would provide maintenance support for 10 months and further improvements would be implemented.


Impact

The dashboard we built allows the city of Helsinki to gain insights and deeper understanding of their hundreds of digital channels. Most importantly, it helps them to compare different channels’ performance. This information can be used to evaluate the importance of each individual channel and to help with decision making.

With an understanding of the overview, they can meaningfully dive deeper into more detailed metrics of the dashboard to gain insights on the underlying reasons for performance.

Highlights

  • Over 200 different online channels from 6 different APIs combined into a single dashboard
  • Helped to further develop a new custom metric, “performance index”
  • Better understanding of the performance of each individual channel
  • More holistic view and clarity in comparing different channels
  • Ability to compare different pages from different operational branches
  • Coordinated data-refreshing architecture to store the data in the cloud

About the City of Helsinki

Helsinki seeks to be the most functional city in the world. Functionality is about providing a convenient and enjoyable everyday life for residents, visitors, businesses and communities. The city’s digitisation programme harnesses the potential of digitalisation to provide better quality public services. Tomorrow’s digital services aim to be proactive and tailor-made to meet the needs and profiles of different customers, and are available conveniently, around the clock and through a range of channels.

digi.hel.fi


Download reference case whitepaper


Building an ecommerce platform that scales for multiple product and customer areas

This is how we built a trustworthy and versatile platform and infrastructure for selling numerous product areas with various use cases online.


Hockey stick growth in partner engagement with a lead-to-order platform

Framery joined forces with us to build Framery 360, a channel that brings together all necessary tools for Framery’s global network.


Building a scalable end-to-end digital sales and marketing platform to drive growth

Building a holistic digital sales and marketing platform customised for Valokuitunen's needs, including all business and customer-facing processes.