Posted in Workflows

Analyzing frevvo Workflows With Google Data Studio

Through the years, we know that customers just want their data so they can use their preferred tools and methods to analyze it. As a result, our product strategy maximizes customers’ ability to push or export form and workflow submission data into other systems.

We provide a variety of connectors e.g. the SQL Database Connector, the Google Connector (Google Drive and Google Sheets), the File System Connector, the SharePoint Connector and others. While these connectors have varied capabilities , a common thread is that they can export your submission data into another system.

Analyzing frevvo Workflows With Data Studio

In this post, we start with a simple purchase order workflow. We’ll modify it and send submission data to a Google Sheet. We’ll then build a Google Data studio report to help visualize and analyze the submission data.

Along the way, we’ll see how to export submission data from frevvo into Google cloud. We’ll modify the data a bit to make it easier to report against. We’ll also see how to report on the data with Google Data Studio.

What Is Google Data Studio?

Google Data Studio lets you visualize your data through highly configurable and interactive dashboards, charts, and tables.

Google provides Data Studio as part of its marketing platform. However, it is generally useful as a reporting and data visualization tool, with numerous chart, table and user interface “widgets”. It provides both a report design and runtime environment.

It also provides a plethora of data connectors. Using these connectors, your data is available for reporting. Connectors to Google Sheets, external databases, uploaded CSV files and many others are available.

For this post, we will be using a purchase order workflow from our web site. To download this workflow, click here. An employee creates a purchase order with multiple detailed line items. There are two approval levels: manager and finance. See the form below.

Purchase order form

This workflow produces historical purchase order data. We’ll build a simple report in Google Data Studio to analyze the data.

Prepare the Workflow

Download the workflow. You may use either your frevvo cloud account or your on-premise Live Forms instance. Upload the workflow into your designer account in any convenient application. There are a couple of relatively simple changes we want to make to this workflow:

  1. Configure a flow document action to save the submission data to a Google Spreadsheet.
  2. Copy the purchase order detail items data into hidden fields. This is only required for Google Sheets. We need to work-around an issue with repeating data delimiters with numeric data.

We detail these two changes below. If you would rather work directly with the updated workflow, download the modified version here and upload it directly into your account.

Request a demo of our modern, cloud-based software.

1. Save Submission Data to Google Sheets

We edit the uploaded workflow and follow the instructions to Save your Submissions to a Google Sheet found in the frevvo docs. For step 2 in the instructions you will be using the uploaded flow.

For step 3, create a new Google sheet with the following column names.

Google Sheet with column names.
FirstName
LastName
ManagerId
PODate
PONumber
GrandTotal
MgrCheckbox
Reference
Items
Qtys
UnitPrices
Subtotals

The name of the sheet does not matter but I suggest “basic_purchase_order”.

2. Copy the Purchase Order Detail Items Data

If your workflow doesn’t have currencies, you don’t need to worry about this workaround. Since currencies are common, we provide details here.

The purchase order workflow has a place to enter multiple purchase items along with a quantity, unit price and sub-total for each. We use a table for this.

When the system pushes this data to the Google sheet, it ends up in a single cell with comma-delimited values. For example, consider three line items with unit prices, $11, $12 and $13 respectively. The Google Sheet puts all three into a single cell in the format (11,12,13).

Unfortunately, currency data (which we have here) causes problems when the values exceed 1000. Google Sheets formats currencies using commas. For example, $12142 becomes $12,142. You can easily see why this is a problem.

To work around this problem, we’ll have to use a different delimiter. First, we create 4 new hidden fields on the form. Edit the first step and add a hidden section and the 4 fields shown below. The names must be exactly as shown.

Create hidden fields in the form.

Next, we add a new business rule named “Encode Item Details”. The rule copies the values from the each column (Item, Qty, UnitPrice and Subtotal) into the hidden fields. Instead of a comma, it uses the ‘|’ character as a “safe” delimiter.

Add a new rule to the first step and paste in the Javascript below.

// Encode the repeating items with a | instead of a comma
var event = itemListRepeat.itemAdded || itemListRepeat.itemRemoved || form.load;
let items = [];
for (let i = 0; i < Item.value.length; i++) {
  if (Item[i].value) {
    items.push(Item[i].value);
  }
}
Items.value = items.join('|');

items = [];
for (let i = 0; i < Qty.value.length; i++) {
  if (Qty[i].value) {
    items.push(Qty[i].value);
  }
}
Qtys.value = items.join('|');

items = [];
for (let i = 0; i < UnitPrice.value.length; i++) {
  if (UnitPrice[i].value) {
    items.push(UnitPrice[i].value);
  }
}
UnitPrices.value = items.join('|');

items = [];
for (let i = 0; i < Subtotal.value.length; i++) {
  if (Subtotal[i].value) {
    items.push(Subtotal[i].value);
  }
}
Subtotals.value = items.join('|');

That is it for the workflow changes. The flow will now send submission data to the Google sheet created above. Try it out and see for yourself.

Now, let’s transform the data a bit. The goal is to make it easier to report against in Google Data Studio.

Prepare the Data

The purchase order workflow essentially consists of a main purchase order with multiple detail purchased items. This fits the classic master-detail data pattern.

In relational data modeling, we typically represent this with two tables: one for the master record and one for the detail items. Reporting tools support this sort of representation in two tables very well and Google Data Studio is no exception. Typically, the two tables are JOINed to associate details with the corresponding master record. This allows powerful reports and analytics.

So far, we have the master record data (PO header fields) and the details (Item, Qty, UnitPrice, Subtotal) all in the same table. We’ll put a script in place to extract the detail items and place it into a separate Google sheet. With this in place, we now two separate sheets. The first represents the master PO record and the second represents detail items for the purchase orders.

In order to extract and create this PO details sheet we simply use a Google Apps Script that runs periodically. This script extracts details data from the main sheet along with the PONumber and writes them into the details sheet.

If you are not familiar with Google Apps Script, you can find out more about it here. Follow these steps below to setup a script that will create and maintain the PO details sheet.

  1. Create a new Google sheet in the same folder. The name does not matter but I suggest “basic_purchase_order_item”. Create a single row with the column headers of: PONumber, Item, Qty, UnitPrice, Subtotal. The exact spelling and capitalization is important here.
  2. Open the first (master) PO sheet (basic_purchase_order), and select “script editor” from the tools menu. The Google Apps Script editor will open. Copy the code below and paste it into the script editor. This script is a bit complicated, but basically it extracts the repeating item detail data from each PO sheet and places each one into its own row in the PO details sheet.

    Paste the script and make two very minor change. Change the two IDs (look for ‘CHANGE THIS ID!’). These are the IDs of your Google sheets. You can find your specific IDs by getting the share link for each sheet in Google drive. The ID is in the share link URL. Save the script after making the modifications!
function periodicallyMaintainPOItems(e) {
  // Lets open the master purchase order sheet the frevvo writes too
  _maintainPOItems(SpreadsheetApp.openById("1ZaaAg28Fse9iyxEp8DlvRAxpxrSvcYAMaJjhcL5CgZk"));    // CHANGE THIS ID!  basic_purchase_order
}


function _maintainPOItems(ss) {
  Logger.log('maintainPOItems spreadsheet name: %s ID: %s Sheet: %s %s', ss.getName(), ss.getId(), ss.getSheetName(), ss.getSheets()[0].getName());
  
  var sheet = ss.getSheets()[0];
  var range = sheet.getDataRange();
  var poMasterValues = range.getValues();
  Logger.log('poMasterValues: '+ poMasterValues[0]);
  
  // Now lets open the PO Details Sheet.
  var poItemDetailSS = SpreadsheetApp.openById("1TzAvZlW35AYh5OmvNOn7itvGMqK6DGDe-aLnYe1aM2M");   // CHANGE THIS ID! basic_purchase_order_items
  var poItemSheet = poItemDetailSS.getSheets()[0];
  poItemSheet.deleteRows(2, poItemSheet.getLastRow()-1);
  
  // Now extract the PO line items from the master PO sheet and put into the PO Details sheet
  // Figure out which columns to extract
  // PONumber	Item	Qty	UnitPrice	Subtotal  are all possibly repeating
  
  
  Logger.log("Finding the columns to transfer: "+poMasterValues[0].length+"   "+poMasterValues.length);
  var colPONumber, colItem, colUnitPrice, colSubtotal;
  for (var col=0; col<poMasterValues[0].length; col++) {
    if (poMasterValues[0][col] === 'PONumber')
      colPONumber = col;
    else if (poMasterValues[0][col] === 'Items')
      colItem = col;
    else if (poMasterValues[0][col] === 'Qtys')
      colQty = col;
    else if (poMasterValues[0][col] === 'UnitPrices')
      colUnitPrice = col; 
    else if (poMasterValues[0][col] === 'Subtotals')
      colSubtotal = col; 
  }
  Logger.log("colPONumber: %s "+colPONumber);

    
  // Now extract the desired cells for each row
  var xferData = [];
  for (var row=1; row<poMasterValues.length; row++) {
    //Logger.log("row: %s data: %s", row, poMasterValues[row]);
    // Figure out if there is repeating data
    var itemsData = poMasterValues[row][colItem];
    var items = itemsData.split('|');    // Using the '|' as a delimiter
    Logger.log("items: " + items);
    if (items.length > 1) {
      var qtys = poMasterValues[row][colQty].split('|');
      var unitPices = poMasterValues[row][colUnitPrice].split('|');
      var subTotals = poMasterValues[row][colSubtotal].split('|');
      for (var i=0; i<items.length; i++) {
        Logger.log("adding item: " + items[i]);
        xferData.push(buildXferDataRow(poMasterValues[row][colPONumber], 
                                       items[i], 
                                       qtys[i], 
                                       unitPices[i], 
                                       subTotals[i]));
      }
    }
    else {
      Logger.log("adding item: " + poMasterValues[row][colItem]);
      xferData.push(buildXferDataRow(poMasterValues[row][colPONumber], 
                                     poMasterValues[row][colItem], 
                                     poMasterValues[row][colQty], 
                                     poMasterValues[row][colUnitPrice], 
                                     poMasterValues[row][colSubtotal]));
    }
  }
  Logger.log("xferData: "+xferData[0]);
  poItemSheet.getRange(poItemSheet.getLastRow()+1, 1, xferData.length, xferData[0].length).setValues(xferData);
  
}

function buildXferDataRow(poNumber, item, qty, unitPrice, subTotal) {
  var rowData = [];
  rowData.push(poNumber);
  rowData.push(item);
  rowData.push(qty);
  rowData.push(unitPrice);
  rowData.push(subTotal);
  return rowData;
}

3. The script above will run periodically every 12 hours. As the system adds new records to the main sheet, the script transforms them into details within 12 hours. You can choose whatever frequency you wish, but be sure balance this against Google’s App Script quota limits. To do this, we are going to enable an installable trigger for the script. You can find Google’s help documentation on this here, but you can do this as follows:

  • In the script editor window/tab, select ‘Current Project’s Triggers’ from the Edit menu. The triggers window will open.
  • Select the ‘Add Trigger’ button. On the dialog, select ‘periodicallyMaintainPOItems’ for the function to run, select ‘time-driven’ for the event source and below that select ‘Hour Timer’ and ‘Every 12 hours’ and save it. The script will now run periodically and make sure that all new PO records have their details transformed into detail row in the second items sheet. When complete, it should look similar to this:
Setup Google Apps Script

With this last step, we now have a frevvo purchase order flow sending new submissions to a Google sheet and the details being broken out into a separate sheets.

Setup Google Data Studio

In order to use Google Data Studio you must have an account with Google and login. There are several ways to access the tool, but for simplicity here is a link. If you have never used the tool, you will see something like the following screen.

Google Data Studio setup

I encourage you to experiment with Google Data Studio and the starter/tutorial report at some point. For now, follow the steps below to build the report.

How to Build the Report

Here’s a snapshot of our desired report.

Google Data Studio Report

We’ll use 4 widgets and aggregate much of the data across the purchase order details data. We explain each widget configuration below. This report just scratches the surface of Google Data Studio’s capabilities but demonstrates its usefulness for reporting against Frevvo generated data.

1. Connect to the Data

We’ll use a connector in Google Data Studio to configure data sources to connect to our two Google sheets. Remember that, one sheet (basic_purchase_order) contains the PO records and the other sheet (basic_purchase_order_item) contains the PO line items details.

  • In Google Data Studio, select the Data Sources tab and then the Create button and select Data Source. You may see a welcome screen with usage terms, etc.
  • When presented with the list of connectors, select “Google Sheets”. You will likely have to authorize the connection to your Google account.
  • In the connector configuration screen, select “basic_purchase_order” and the first and only sheet as shown below. Select connect and Data Studio presents you with a list of fields corresponding to the columns in the sheet. At this point the data source is ready to use.

Repeat the above steps with the “basic_purchase_order_item” sheet to create a second data source.

Connect to a data source from Google Data Studio

2. Create a New Report

Now that we have the data sources configured, it’s time to create the report. On the Reports tab of the Data Studio page, use the Create button to create a new report. The report designer will open. Click on the name and give it a new appropriate name. On the right side, add a data source. Add the “basic_purchase_order” data source created earlier. After adding the first data source, the designer changes so that you can begin adding chart widgets.

The discussion that follows details how to create and configure the various charts to display the data. Styling and look and feel choices and how to do this in Google Data Studio is not discussed since everyone has their own preferences.

Chart 1: Year To Date Purchases Summary Bar Chart

From the chart menu on the top bar of the designer, select a bar chart. Drag it to the top left and make it full width. Google Data Studio automatically attaches the data source and makes a guess at dimensions and metrics, but we need to change that.

This chart blends data from both the basic purchase order data source and the basic purchase order item data source. Initially, aggregates purchase total by month for the year-to-date period. Additionally, it supports a drill down (either for a specific month or the whole year) to purchase total aggregated by item. In order to drill down, either right click anywhere and select drill down or first select a month bar and then right click. The drilled down chart now shows aggregated purchase total by item (Figure below).

YTD Purchases Summary Bar Chart

First, we have to edit the basic_purchase_order data source. You can do that in the right pane by clicking the pencil icon on the data source. Select the PODate field and change type to “Month (MM)” (shown below). We want the bars in this chart to aggregate purchase order grand total by the month.

YTD Purchases Summary - Set Aggregate Field.

Next, we have have to “blend” the basic_purchase_order data source with the basic_purchase_order_item data source to bring in item details. The term blend is very much like a JOIN for those familiar with relational databases and SQL.

In the right pane with the chart selected, select the “Blend Data” link and then “Add Another Data Source”. Then, select basic_purchase_order_item from the list. Google Data Studio makes a guess at configuring the blended data source but it’s not what we want.

Configure it as shown below. Note the PODate dimension from the first data source and the Item dimension from the second data source. This is where our drill down comes from. Select Save and then Close.

YTD Purchases Summary - Blend Data
YTD Purchases Summary - Final Configuration

The final config for the chart is shown to the left.

Use PODate and Item as the dimensions, select Drill down and SUM of the grand total as the metric.

You can adjust any formatting as desired e.g. add a title using text widget, turn off the legend, turn on the y-axis title, etc.

Chart 2: Purchase Request Breakdown Table

Purchase Request Breakdown Chart

This table shows a purchase aggregation by manager and requestor.

From the chart menu, add a “Table with Bars” widget to the design canvas. We want to show the requesting user’s full name in the table but we only have first and last as two separate fields.

Select the new table widget. Then, select add dimension on the right side and then select create field. In the resulting dialog, create a new full name field with the formula shown below.

Full Name Field Formula

Add ManagerId as a dimension and SUM of grand total as the metric. See the final configuration below.

Purchase Request Breakdown - Final Configuration.

Chart 3: Purchase Item Breakdown

Purchase Item Breakdown Chart

The Purchase Item Breakdown Chart shows a simple breakdown of the purchase total by item. The data source used by this chart is the basic_purchase_order_item sheet.

If we want to add additional drill downs or filtering then a blended data source would be needed. Add the bar chart and the data source and set the chart configuration as shown below.

Purchase Item Breakdown Final Configuration

Chart 4: Purchases Approved By Manager Pie Chart

Purchases Approved by Manager

This chart illustrates using a pie chart with a drill down. See the chart at left which shows the first level – total purchases aggregated by approval manager.

Purchases Approved by Requestor

You can then drill down in each pie slice to purchases aggregated by requestor (at left).

This chart uses the basic_purchase_order data source. See the final configuration below.

Purchase Orders Pie Chart - Final Configuration

You Can Do a Lot With Google Data Studio

We have just scratched the surface on Google Data Studio’s capabilities and functionality. It has many chart and user interface widgets in addition to the ones we discussed. Google Data Studio also allows you to share and embed reports, add user interaction to charts, download reports, schedule email delivery of reports, build multi-page reports, etc.

Other Approaches to Your Data

In this post we have demonstrated using Google Data Studio to report against frevvo submission data using Google sheets. Consider these other options too:

  • Use the frevvo Database Connector (DBC). The workflow can push submissions to SQL database table(s) using DBC. Google Data Studio has several connectors for configuring data sources to remote databases.
  • Export your submission data from frevvo as CSV data using the submissions view export button in frevvo Live Forms. Google Data Studio has a file upload connector specifically designed to build a data source from an uploaded CSV file.

Final Thoughts

Hopefully, this post has given you a taste of how you can use Google Data Studio to analyze your frevvo-generated submission data. With a little data manipulation up-front, it is truly easy to create a powerful visualization of your data in Google Data Studio. Happy reporting!

Sign up for a free 30-day trial.

Leave a Reply