Data Studio is one of the amazing data visualization tools and we have been working with it since it's release. By the time, we have been sharing various templates and tutorials to master Data Studio. Today, we are going to share a case study to let you know how our analytics experts help our awesome clients get the results that look for.
Student Energy is a global non-profit dedicated to creating the next generation of leaders who will transition the world to a sustainable energy future.
Student Energy's International Student Energy Summit (SES) is a global event that brings together the world’s brightest students to learn and discuss the current issues and trends in energy.
SES had data coming in from multiple sheets, each for a different aspect of conference(event) management, and they wanted a comprehensive, central reporting tool to quickly visualize their performance, in comparison to the targets they had originally set to meet for a particular time period.
We had to adapt and transform the data into a useable format and feed it into Google Data Studio.
Primarily, we had two major goals;
Create or adapt existing Google Sheet data inputs that will feed the Google Data Studio dashboard (in five reporting categories) and add the ability to see quarterly and cumulative progress based on set targets (goals)
Hospitality and hotel status (assigned, not yet assigned)
Design a Dashboard in Google Data Studio to visualize the data including the goals and current status for each reporting category.
The data given to us comprised of multiple Google Sheets, some of which were being auto-updated through Google Forms while others were being manually fed in by student teams.
Primarily, there were 6 sources,
An auto-updated Pre-Registration Google Sheet linked with Google Forms, which contained details about the participant’s registration for the event
An auto-updated Bursary Application Google Sheet linked with Google Forms, containing information about the participants who have applied for a bursary
The Bursary Application Google Sheet also contained two additional attributes for Visa and Hotel, which would supply the data for the Hospitality, and would be entered manually by delegates
A Speakers Google Sheet including details about the speaker and the Stage (Idea, Signed, etc.), manually updated by delegates
A Sponsorships Google Sheet including details about the speaker and the Stage (Idea, Signed, etc.), manually updated by delegates.
A Delegates Registration Sheet containing details about the delegates who have applied to be a part of the event’s organizing team
They had also provided us with Goals and Targets they had to meet in every category. For example, for the ‘Sponsorship’, we had five different types of deals depending on the stage they were in,
Each type of funding deal had a corresponding Target for each quarter, and we had to show how much of that target had been met. We made separate sheets for each type of report, detailing the Target values for each report, and calculating the actual aggregated values from the given figures using advanced built-in formulas in Google Sheets. One of our objectives was to make sure to work with the same data collection process that they already had in place in order to cause minimum disruption.
This sheet titled ‘SES Sponsorship’ contains the original data, as filled in by the delegates.
Here, we demonstrate the use of ‘Sum-If’ functionality for calculating the actual sponsorship acquired. This formula searches the values in column ‘H’ of ‘SES Sponsorship’ and sums only those values that satisfy the following conditions,
If Column A matches the column G of SES Sponsorship - has the same Sales Stage
If Column B matches the column E of SES Sponsorship - has the same Creation date
This can be fed in Google Data Studio and visualized with a Bar Graph,
Similarly, we used the ‘Count-If’ formula to compare the Target values for the number of pre-registrations with the actual values,
This formula is used to count all the values in the ‘Form Responses’ sheet that have a valid Entry ID (greater than zero) and have the same Quarter listed in the B column of this sheet as in the O column of the ‘Form Responses’ sheet.
Feedback From Client
"Hussain and MarketLytics teamwork with utmost care and professionalism. We had a fantastic experience building a reporting dashboard with them, they took the time to understand our needs and were cognizant that it was our first time using Google Data Studio. All communication was answered promptly, and conference calls were scheduled if we needed to review feedback in detail. Would highly recommend working with Hussain and team!"
We successfully managed to transform the data into a useable format and fed it into Google Data Studio, creating a five-section report the SES team was eagerly waiting for.
The Google Data Studio Dashboard creates live reports for all the data that is being fed in. They can now easily visualize the performance of their departments using the automated reporting system, allowing them to focus their efforts and quickly double down on the areas that need improvement instead of worrying about daily reporting.
They can also gather valuable insights about the event registrations, delegate registrations, and speakers, by breaking them down using different dimensions - geography, gender, and more.