Power your SaaS product reports with Klipfolio

Leveraging client accounts, dynamic data sources, company properties and master templates

Data and Integration is king

This is a well-known reality for most companies in the SaaS space. In NewVantage’s latest 2018 partners survey (which analyzes how corporate executives view data related initiatives), 97% of respondents declared to have already invested in data analysis projects, while 73% already observed measurable value from those investments.

Independent of the industry vertical that you are serving, chances are that you are building a product that enables your customers to leverage some form of data to provide business and operational insights. Also, you are likely facing the need to support the flow of this data from your application into other systems or output the data from disparate systems in complex reports.

It is not easy to deliver a software product that meets the criteria of data interconnectivity, flexibility of reporting, and ease of use. We would know because we spent the last two years in search of the right tool to provide this level of reporting to our clients.

In this post, I will share our experience and explain how Klipfolio fits the bill perfectly to deliver this great experience to our clients while keeping ease of maintenance of reports in a SaaS application.

About Wicket, SaaS, and AMS software

From our experience, the need for data insights and integrations is particularly evident in the Association Management Software (AMS) space. For years, professional associations and non-profit organizations have enabled effective multidisciplinary teams by providing them with the tools to manage all aspects of the membership management operations, including marketing, communications, engagement and e-commerce.

Until recently, these organizations had to deal with the problem of data being locked down into proprietary monolithic applications that don’t provide the means to share that data or report on it effectively.

At Wicket, we believe that data and integrations should be king. And our mission is to enable our customers to use the tools they like the most (and know to be the most effective), while still sharing information and having a single source of truth to manage their members and draw insights.

With this mission in mind, our membership management SaaS solution was designed with an integration-first approach. This means that all important data points live in Wicket and information flows to/from Wicket into those other best of breed tools such as MailChimp, Eventbrite, Shopify, Drupal, and Wordpress. Each team can then focus on what they do best while having all member interactions with the ecosystem of tools recorded in Wicket.

The most critical piece of this ecosystem is the reporting and analytics tool. We are firm believers of integration before in-house development, and dashboarding and business intelligence tools are not the exception. However, with such a mission-critical use case, we wanted to make sure we found the right tool for the job.

The dashboarding tool should meet the technical requirements to integrate deeply with our SaaS architecture and also offer a user experience that would make it easy and enjoyable for our clients to use.

The right tool for SaaS reporting

Dashboarding, analytics, and business intelligence tools are abundant. Depending on their focus and the intended audience, they may provide more or fewer features across the data analysis stack:

If you are going to use a third-party tool to power your SaaS product reporting engine, you want to make sure that the tool has a good balance across the data analysis stack. Your requirements may be unique, but I can almost guarantee that you will need flexibility and robustness of features across all layers to deliver a good experience to your customers.

At Wicket, we evaluated a number of other tools according to their strengths across these areas, and we found that most tools would do great in certain areas but would not be good in others.

For our particular use case we needed four core characteristics of the data analysis tool:

• Ability to connect directly and independently to each of our tenants' segregated databases through configuration and without hard-coding connection credentials into each report.
• Ability to create each report once and then run it in the context of each database without having to replicate and maintain the same report separately for each tenant.
• Ease of use of the reporting tool, so customers can build their own reports beyond the initial out of box core reports.
• Be an affordable tool with a flexible billing scheme

After numerous conversations with various vendors, some proof of concepts and other full production implementations that didn’t fulfil some of these requirements, we came across Klipfolio.

The solution components

This problem can be solved by four specific features offered by Klipfolio. Let’s first dive into an overview of each one of these features, and then explain how it is all put together to manage core product reports that can be used by multiple tenants in your SaaS product.

Client Accounts

Klipfolio offers a Partner Program that gives you access to the Parent/Client account model that is key to managing your SaaS clients’ reporting needs. You can create and manage all aspects of the client accounts and sign-in as an administrator to them in order to configure and import dashboards from your parent account.

Furthermore, Klipfolio gives you full flexibility with regards to billing on those client accounts. You can choose to manage the billing of related client Klips on your own or to have your clients be billed directly by Klipfolio.

Dynamic data sources

A dynamic data source is one whose connection and query attributes can be specified via variables. In our case, we needed to dynamically specify the tenant's database name and user for a SQL database source. However, variables can be used in any of the text fields of the New Connection screen.

If you have a single database for all tenants in your SaaS solution, you could use a variable with the tenant's identifier in the SQL Query field itself, in order to filter records for one particular tenant in a given table.

Company Properties

Company properties are the feature that allows you to define values for global variables in the context of a specific client account. Your data sources will use the global variable names to dynamically pull data, but the actual value for those variables will be defined by the corresponding company property defined in each client’s account.

Master Templates

In Klipfolio you have the ability to copy, or rather import, assets (dashboards, klips, data sources) from one account to another. When this copy or import occurs, the assets from the source account are linked to the assets in the destination account in a Master/Instance relationship. This relationship then allows you to publish updates made from the master to the imported instances in the other accounts.

This is a powerful feature that gives you the ability to maintain a single set of reports in the form of dashboards, klips and data sources, without having to manually replicate the same setup and configuration across all your SaaS tenants.

Step by step implementation

Although the specifics of your SaaS implementation may differ, here is a general guideline for how to use these features to deliver product reports for your multiple SaaS tenants.

The following sections explain the steps to get it all working.

Define the global variables for tenant data sources.

The first thing you want to do is to define the variables that will uniquely identify each tenant's data. This could be a combination of hostname, API endpoint, database name, uuid of the tenant, etc. You will use these later on when configuring the data sources.

Steps:

1. Click Klips in the left navigation bar.
2. Click Build a Custom Klip.
3. Drag the Value Pair component into the Klip Editor workspace. (You won’t actually use this Klip, it is just to access the Advanced Tools)
4. Click the Advanced Tools button and select Create New Variable... from the drop-down list.
5. Type a Variable Name and a Value. For example, tenant_db_name and tenant12345.
6. Click Create Variable.
7. Repeat steps 4-6 for any additional variables.
8. Cancel the Klip creation screen

You will perform these steps in your parent account before creating client accounts for your tenants. You also have to use variable values that will actually resolve to a valid data source (I would suggest using a demo or test tenant for this purpose). These values will be replaced for the variable in later steps at the client’s company property level.

Create the data source for your report.

Now we can create one or several data sources according to the needs of your reports. It is important to create the data sources as dynamic data sources from the beginning. In these example steps, we create a SQL Data Source, but the steps are similar for any data source type.

Steps:

• Click Data Sources in the left navigation bar.
• Click Create a New Data Source, select the SQL Query option
• In the New Data Source page, enter the general connection details for your database.

Type the variable name in place of a hard-coded value, using the format: {props.}, replacing with the variable you want to use.

These variables can be used in any of the provided text boxes of the New Data Source page.

• Save the Data Source

Repeat these steps as necessary to create the data sources for your reports at your parent Klipfolio account.

Build your dashboard

At this point, you can go ahead and create your klips and dashboard(s) as necessary using the dynamic data sources you configured. Creating dashboards is beyond the scope of this article and you can find lots of information here.

Create client accounts

Now you will proceed to set up your tenant’s version of your generic reports. If you don’t already have client accounts for your tenants, this is the time to create them.

Create client company properties

Once you have your client accounts you can use the “Sign in as.. Admin” option from the Client List page to enter into the client account.

Once inside the client account, follow these steps to create the necessary custom company properties:

Steps:

1. While logged in to a client account as an Admin, click on the Account name and select Account > Company.
2. Click Properties.
3. Click Add New Company Property, then fill in the Property Name and assign a Company Value.

It is important to enter the Property Name to match exactly the global variable that you created before.

The value, in this case, should be the value that will allow the data source to resolve to this particular tenant data.

1. Click Create Property.

This is what will allow your tenant dashboards to pull data for this tenant in the context of your generic reports. Don’t forget to create one property for each one of the variables that you created before.

Import the dashboards into the client account

Now that you have everything set up, you can then import the dashboards that you created as generic reports for your product. The specific steps with detailed screenshots can be found here.

Remember, these imported dashboards (and corresponding Klips and data sources) will be linked to your master template. When you make updates to the main account’s dashboard, you will be able to publish your changes via the update client instances option following these steps.

Things to consider

I recommend that you start by making an assessment of what reports you want to build, so you can plan your data sources ahead of time. This will save you from having to reconfigure data sources later on.

When you import dashboards into a client account as we did here, the assets are actually copied to that account and when you publish changes to these instances any updates made to the klips at the client account level will be overwritten.

Keep in mind that data sources are different in this sense, and they cannot be updated via the update client instances option. If the changes to your reports require updates to the data source configuration or queries, then you will need to re-import the dashboard(s) related to that report. This implies following the import steps for each tenant again.

Imported Klips are copies, and as such, they count as billable towards the Klip allocations that you or your client has in the account.

Where to go from here

This is a very good way to maintain a set of generic core reports for your product that run on each tenant’s specific data context.

It is still required for your tenants to have a Klipfolio account, and the way your clients will access these reports will be via their Klipfolio user login. At Wicket, we are looking at the next step to be a more seamless experience for our clients.

It could be that client’s don’t need a fully fledged Klipfolio account and all they want is our core reports. Or they simply want a tighter integration of Klipfolio with our product. Either case our focus is now on embedding these core dashboards into Wicket’s user interface.

In a later post, I will talk about how we are exploring to deliver this enhanced user experience by building from what we have and leveraging Klipfolio’s embedding, white labelling and Single Sign-On capabilities.

About Juan Silva, CTO - Wicket.io

Juan provides sound technical leadership in all aspects of Wicket's business. He leads the development team and oversees the technical architecture of the product and SaaS infrastructure. He is passionate about R&D, especially in the fields of Analytics, Data Science and Machine Learning.