Case Study: Tracking Event Management Progress using Google Data Studio

Case Study: Tracking Event Management Progress using Google Data Studio

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. 

About Client

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.

 

The Challenges

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)

    • Sponsorship

    • Program

    • Marketing

    • Delegate Relations

    • 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.

 

Our Efforts

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,

  1. Idea

  2. Contacted

  3. Proposal

  4. Verbal

  5. Signed
     

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.

 google sheets data

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,

  1. If Column A matches the column G of SES Sponsorship - has the same Sales Stage

  2. If Column B matches the column E of SES Sponsorship - has the same Creation date

 google sheets

This can be fed in Google Data Studio and visualized with a Bar Graph,

 data studio goal report

Similarly, we used the ‘Count-If’ formula to compare the Target values for the number of pre-registrations with the actual values,

 google sheets formula

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.

 data studio chart

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!"

 

The Results

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.

 data studio bar charts
 data studio pie charts

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.

 

 

Rehan

Rehan is pursuing his Bachelors Computer Science from IBA and wants to make sense of the world of ones and zeros. He is also a freelance writer on Technology.