Feedback
Tutorial

Create semi-automated reports with Typeform and Google Data Studio

  • 24 February 2021
  • 20 replies
  • 1435 views
Create semi-automated reports with Typeform and Google Data Studio
Userlevel 5
Badge +3

Hey everyone,

 

I'm Technical Lead on the Typeform Support team at Typeform – it's nice to be here in the community with you all :) 

 

I want to share with you a solution I discovered for creating automated reports with Typeform and GSuite

 

As part of my role I need to send a weekly report (pictured below) to my leadership about the incidents in JIRA that are impacting or have impacted customers this week. 


 

 

 

I was wasting a heck of a lot of time before, copying and pasting the numbers and screenshots of the graphs from JIRA into a manual template. Because the report is worked on by all my team members it looks different every week. Also, people were forgetting to share information, sometimes (we are humans after all). 

 

So, what's the solution?

 

So I decided to automate things as much as possible in order to save time and make life easier:

 

  1. I made a typeform to collect the data. It would log all the info I needed for the report (e.g. how many bugs we opened, what the priorities were, what is our cancellation rate, etc.)
     
  2. I send all that data to a shared Google Sheets document.
     
  3. 3. I use the data from the Google Sheet to create an automated report with nice looking graphs using Google Data Studio.
     

Here's how it works:

 

 

How to make it yourself

 

In this post I'll give an overview of the steps needed to make the solutions, with some video explainers. 


You can also download the guide I made 📕 which goes into much more depth and gives an outline of all the questions I used. If you take the short route you'll be up to speed in about 30 mins, if you want to devour everything related to my specific use case it'll take a good two or three hours. 

 

Step 1: Create the typeform

 

I created a typeform that my team could use to submit the information we need to generate the reports. It allows us to capture things like:

 

  • How many bugs were opened

  • Updates about particular issues

  • Information about service disruptions

 

I password-protected the typeform so that only designated team members are able to access and generate the reports. I also made use of the simpler logic capabilities in the new builder to route people through the form in a "conversational" way.

Here's an overview of how the form is made:

 


 

Here's a copy of the form so you can play around with it in your own Workspace and have a look at what's inside.

If you want even more detail on how I made the form get some popcorn ready and settle down to watch this three-part step-by-step video series:
 

  • Part 1: Setting up the password entry, adding question groups and Logic (31 mins)

  • Part 2: Using Layouts to add instructional screenshots, capturing details of bugs reported (21 mins)

  • Part 3: Setting up a typeform to capture service disruptions, checking an publishing the form (12 mins)

 

Step 2:  Connect to Google Data Studio through Google Sheets

 

Once the typeforms are created, they are connected to Google Sheets using the process explained in this Help Center article.

 

One this is set up, the data from the sheet is sent to be modelled and visualized in Google Data Studio. In fact, you could use any other data visualization tool you are familiar with if they accept Google Sheet documents as a source. Because it’s free and because it’s pretty easy to use I'm using Google Data Studio.  

 

Once signed into Google Data Studio you can connect the data by clicking Create>Data Source and finding your Google Sheet. For more detailed instructions on this, check out page 7 of my PDF guide.

 

zyV0W9O1M3QWUBx5AjtIM-GNQ_R9SwQBd5keGnJ5B4MHkRfsImRfKKojNkeTsux2iNdVppSOxJ0o95UFPRpRkoiwsa5s_qOIyaoTwrkmARek6q65c4cS9tzm-lCUksoeGSsGrtJ-


Once connected, you'll move through to the data modelling screen in Google Data Studio. This is where you configure the data sources for your reports, before clicking the 'Create Report' button to generate the report. Here's another video to show you how all this works:

 

 

Step 3: Build the reports

 

Finally, the fun part – creating the reports. Google Data Studio gives you a lot of control on how your finished reports look, as well as what information they will show. The video below will give you an idea of what our finished support incidents report looks like once generated:

 


If you want more specifics on how to design and set up the reports here are some more in-depth videos:
 

  • Part 1: Basic design

  • Part 2: Creating the first visualizations

 

And that's it! I hope all this information is helpful and that you'll find a way to apply it to your own use cases. Do let me know if you have any questions. We would love to know what you think of the post – be honest – so our content curator @James  can use your feedback to plan future share outs like this. 

 

Got a sweet, sweet workflow like Nordin's that you'd like to share? Let us know below and we'll get back to you to discuss how we might be able to feature it to help inspire others 💪​​​​​

 

 


20 replies

Userlevel 7
Badge +3

@Nordin thanks for sharing this amazing workflow :pray:  I can imagine so many cool things beyond your use case that this can be useful for (reporting sales orders, survey results, employee satisfaction reports, etc.) Loved the videos, too. In fact, I'm gonna suggest to my son that we watch all 2 hours of footage tonight instead of the Real Madrid game :laughing:

Userlevel 7
Badge +6

@Nordin - thank you thank you thank you!!! 

I agree with @James  - my evening is now shot (along with the next several i fear) lol.. will be trying to get through all of this to augment what I have already able to make Google Sheets and Docs, along with Document Studio add-in, to make reports. 

i wish there was some way to ‘more than like’ a post - this one gets 5 upvotes minimum from me

des

Userlevel 5
Badge +3

Thanks @john.desborough  🙂 I’m a report freak too, when I get started I even dream with them. I’ll be sharing some more use cases with you all once I get some time. If you tell me what’s yours maybe I can add it to the list and show some cool forms / dasbhoards for your use  case 

Userlevel 7
Badge +6

@Nordin - are you ok if i DM you with the use case details, once i have a few minutes to collapse it down into some pithy bullet points?? 

 

des

Userlevel 5
Badge +3

@Nordin - are you ok if i DM you with the use case details, once i have a few minutes to collapse it down into some pithy bullet points?? 

 

des

Yes, if you don’t mind you can also share here so other members can upvote or add to that too :) 

Userlevel 7
Badge +6

@Nordin - we should probably use a standard format for a use case lol.. otherwise you will get the ramblings like what i have  put in below lol

  •  
  • I have a collection of data based on historical survey responses (collected using another product/method)
  • I am adding to the data set now based on using Typeform to collect the data
  • When a user completes the typeform - an assessment - their data goes into a Google Sheet (currently)
  • The Google Sheet has a 'report' tab where I extract the raw input from the typeform deposit and have some calculations and other lookups to bring data from other tabs into the row for merge with Google Document using Document Studio add-in
    • The lookups bring in the industry-specific aggregate numbers that correspond to the user's industry - so that I can show the user score versus the industry-specific avg score for that question

 

 

 

 

  • There is a tab in this Sheet for the 'industry question response' data - created from the survey data that has been/is collected, averaged and pushed into this tab. See below for an example of how this is set up for the industry.. (when a new user row is added to the report generation tab, the lookup in the {{q1a_ind}} field goes to the q1a column on the 'by industry' tab and grabs the data from the intersection of industry and q#

 

 

 

There is also a version of the report that is individual user response vs entire population, regardless of industry report ..

 

Now to the data stuff and tie to reporting - this is what happens "after" the fact (ie post the report being created to the user and delivered via Document Studio integration):

  • When user enters the data via Typeform ,in another Google sheet,  the user's entries are "added" to the entire population response table's values ie add another row to the raw input - previous row_num=1240, new row_num 1241 - currently on a Google Sheet
  • Entire population aggregate average scores to question responses are updated on a separate tab in that sheet
  • Industry-specific data tab is updated with the row results and the aggregates are pushed into the industry summary response tab
  • Given the frequency of the responses into the environment, I have several of these set up at the moment, I can use the Trello integration for the user report request as a trigger or a daily calendar reminder to manually update the various 'reporting Sheets'

 

I say these things happen "after" the fact due to the current choice of technology (Google Sheets) and the daisy chain of calculations that triggered by the new typeform response being received all happens in a process separate from the user submitting the survey and their response report being prepared and going back to them.

Ideally I would like to be able to include the user's results into the comparative numbers being sent back, especially on the industry level, immediately but I am not losing sleep over that issue.

 

If I moved stuff off the Google Sheets into a database world it would be so much easier but as a solopreneur at the moment, I will get there when I have the time.. Lol..

 

 

sorry for the rambling.. 

 

Userlevel 7
Badge +6

@Nordin @James @Liz @Gabriel 

i had tried to have two additional images in the post above but i got this message: 

 

the images were probably (at least in my head) germane to the content of the previous post but could not add one of them back without triggering the error message above.. 

just a note to check this on the forum software.. 

des

 

Userlevel 7
Badge +6

@Nordin - we should probably use a standard format for a use case lol.. otherwise you will get the ramblings like what i have  put in below lol

  •  
  • I have a collection of data based on historical survey responses (collected using another product/method)
  • I am adding to the data set now based on using Typeform to collect the data
  • When a user completes the typeform - an assessment - their data goes into a Google Sheet (currently)
  • The Google Sheet has a 'report' tab where I extract the raw input from the typeform deposit and have some calculations and other lookups to bring data from other tabs into the row for merge with Google Document using Document Studio add-in
    • The lookups bring in the industry-specific aggregate numbers that correspond to the user's industry - so that I can show the user score versus the industry-specific avg score for that question

 

 

 

 

  • There is a tab in this Sheet for the 'industry question response' data - created from the survey data that has been/is collected, averaged and pushed into this tab. See below for an example of how this is set up for the industry.. (when a new user row is added to the report generation tab, the lookup in the {{q1a_ind}} field goes to the q1a column on the 'by industry' tab and grabs the data from the intersection of industry and q#

 

 

 

There is also a version of the report that is individual user response vs entire population, regardless of industry report ..

 

Now to the data stuff and tie to reporting - this is what happens "after" the fact (ie post the report being created to the user and delivered via Document Studio integration):

  • When user enters the data via Typeform ,in another Google sheet,  the user's entries are "added" to the entire population response table's values ie add another row to the raw input - previous row_num=1240, new row_num 1241 - currently on a Google Sheet
  • Entire population aggregate average scores to question responses are updated on a separate tab in that sheet
  • Industry-specific data tab is updated with the row results and the aggregates are pushed into the industry summary response tab
  • Given the frequency of the responses into the environment, I have several of these set up at the moment, I can use the Trello integration for the user report request as a trigger or a daily calendar reminder to manually update the various 'reporting Sheets'

 

I say these things happen "after" the fact due to the current choice of technology (Google Sheets) and the daisy chain of calculations that triggered by the new typeform response being received all happens in a process separate from the user submitting the survey and their response report being prepared and going back to them.

Ideally I would like to be able to include the user's results into the comparative numbers being sent back, especially on the industry level, immediately but I am not losing sleep over that issue.

 

If I moved stuff off the Google Sheets into a database world it would be so much easier but as a solopreneur at the moment, I will get there when I have the time.. Lol..

 

 

sorry for the rambling.. 

 

adding in image one - at least an attempt - for the previous post

 

Userlevel 5
Badge +3

Hey John, 

Yeah, we could look at your case, looks pretty cool :) 

I think we would need to clarify the part of the Google sheet formulas and look-ups a bit because I think that can be a bit messy to implement in a tutorial but if we were able to clear that up I like the idea of having different tables and stuff. 

How would we go about it, I think the best would be to re-create your case with some mock data and then share it with us so we can check it because I read your post twice but without seeing the real thing I don’t get it 100% :grin:

 

Also, next time if you want to draw something quick like in the screenshot you shared above you can use this little tool which stole my heart, and I’m sure will steal yours ;)  

 

Have a nice weekend!

Userlevel 7
Badge +6

@Nordin - i use my Remarkable (writing tablet) to sketch stuff on all the time and just pulled that from the tablet into the message.. i know my handwriting is bad but lol.. but your tool looks cool. 

 

will put some stuff together on the use case - the mock data and such - and get that out to you Monday (am running workshop all weekend) 

 

but great to get it started!!

 

des

Wow, this is awesome! Thanks so much for sharing this great tutorial 🤩

 

 

Could this process be used to create a bespoke report for the individual that filled in the survey?

I want to create a scorecard for lead generating. People answer the questions and they get a report based on their answers. 

Despite the guidance it still seems like a quite an advanced task. I was hoping to find Typeform had something baked in.

Any response or advice welcome. Thanks

 

Userlevel 7
Badge +6

@jonniejensen - to get to the bespoke level about which you speak, you will probably need to use something beyond the internal components of Typeform: a CRM system or a Google Sheets/Docs integration coupled with an add-in like Document Studio for Google Sheets (which is what i use) to generate custom reports. 

from my end, there are several threads here where i describe the process used BUT i take the user data inputs and dump it to a Google Sheet, using the Document Studio add-in I merge the data with a Google Doc template and push it out via a custom email set up inside Document Studio and attach the pdf version of the merged document. This allows me to add some value to their inputs and provide matching industry metrics (based on the industry they select in the questions) etc. 

des

 

Userlevel 2
Badge

Super well described here, for sure. There are some other product out there that don’t require us to set this up, so wondering if there are plans in the future to build this into TypeForm? Or a service we can purchase in house-to set up an assessment through TypeForm that integrates with this Google tool so that every time someone fills out that assessment it produces a report. I don’t have time to do it myself, it’s complicated to explain to someone to order it on Fiverr, so if we could hire a TypeForm expert (for now until it’s part of one of your packages) I’d be grateful. Any possibility of this?
Sarah

From, Niagara Wine Country (Ontario, Canada)

Userlevel 7
Badge +6

@sarahmcvanel - welcome to the community from a fellow user. 

you can find agency partners for Typeform here - one of them may be able to help. 

I have done a bunch of these types of ‘custom reports from assessments’ for my own business (data quality/governance maturity assessments, etc.) and they are not that difficult to set up and run. Happy to help you with questions if you want to try and build some. There are a number of other folks who inhabite this community who can help out with advice as well.. 

des

Userlevel 2
Badge

John thanks so much. This is very kind of you. What a generous community this is!
Sarah

Userlevel 7
Badge +6

@sarahmcvanel - just to show that is easy enough, try out this assessment (from my data quality world) and if you put in your email address, you will receive a customized report. Once you have your assessment figured out, have created the template for your report (and sketched some of the logic out on napkins), and have your spreadsheet in place (I use Google Sheets) the actual process to set up the merge and mail is really quite simple and quick. 

des

Userlevel 2
Badge

@john.desborough sooo cool. So you set it all up yourself? Didn’t outsource anything? Once you had the IP created, how long did the TypeForm and Google Sheets building process take? Also, do you connect this with your CRM? (I use ActiveCampaign since it’s a native integration...what about you?) If so do you have it populate into the contacts details?

Thanks for all your help.

S

Userlevel 7
Badge +6

@sarahmcvanel - i just got the notification email that you did the assessment, your report should show up automatically within the hour (depending on when Document Studio processes the report ‘hourly’ in the background)

Yes - necessity is the mother of invention! All on my own. It took me about 4 hours to figure out the arrayformula() function in Google Sheets (i know excel much better) and all the if score is between x and y statements, it took me about an hour to create that Typeform and 15 minutes to do the Doc Studio (merge and mail). 

I current push the data into Hubspot and am looking at Convertkit for another one. 

Once you have the basics worked out, it is pretty easy to leverage the process across assessments. I currently have about 10 of these types of assessments built out and in production for my own ‘day-business’. I also use the same process in a side-gig printing tshirts with coffee quotes to create ‘order notifications’ for me - a nice ppt-esque order form. 

des

Userlevel 2
Badge

@john.desborough so cool. Thanks for generously sharing your insights and process.

Reply