CSV files and Google Sheets are among the most common ways to store data manually input by your users. Data from these sources can provide more business context that are not captured in your transactional data.

In this tutorial, you will learn how to integrate Google Sheets and CSV to Holistics, then build dashboards with these integrations in five simple steps:

1. Setup a SQL Database as a Data Warehouse
2. Model Data, Import Data, Transform Data
3. Build and Explore a Dataset to create meaningful visualizations
4. Build Interactive Dashboards with Filters
5. Schedule Your Dashboard to Email

What is Holistics?

Holistics is a BI platform that helps you set up and run you data analytics stack. It supports you to consolidate multiple data sources, model your data and generate meaningful insights. It's also free, so you can register and start building your dashboards following this guide.

1. Prepare a Data Warehouse

Data Warehouse is a central database to store data from different sources (BigQuery, MySQL, MongoDB, Google Analytics, Google Sheets...) and optimized for analytical purposes.

Since Holistics itself is not a data warehouse and does not store your data, you need a SQL data base (PostgreSQL, BigQuery, MySQL..) to pull data from CSV / Google Speadsheets and other data sources to perform analysis.

Checkout these guides if you don't have a Data Warehouse yet:

• Set-up Google Big Query for Data Warehousing
• Setup A Free PostgreSQL Database In 10 Seconds using ElephantSQL

After creating your data warehouse, you can connect it to Holistics. For example, here I'm connecting to my PostgreSQL Database:

2. Model Your Data

Data Modeling is the process of applying business logic to make sense of physical data. In Holistics, this process aims to:

• Consolidate data from multiple data sources
• Transform data into a user-friendly form that is easier to query and interpret
• Give business meaning to raw data generated from your system with metadata
• Promote data self-service and collaboration

2.1. Import CSV (CSV Integration)

In Data Modeling page, create a Data Model from Data Import and choose to integrate CSV. After that, you can click to add, or drag-and-drop your file into the blank space to upload it, then click Create. This will automatically create an empty table in your data warehouse, and then import data from the CSV file to this table.

From Advance Settings, you can edit the destination schema and table, as well as select the correct data type for the columns. By default, all columns are interpreted as Text columns.

In this example, I'm going to import my CSV file which stores categories information into the data warehouse that I have connected above.

Note: A Data Model is like a view, a "virtual table" that sits on top of your actual data table

After successfully creating data models from these sources, you can open a Data Model to see its structure or preview actual data sitting in your data warehouse.

2.2. Import Google Sheets (Google Sheets Integration)

Similarly, you can import data from Google Spreadsheet into my data warehouse by using the same Data Import mechanism.

After selecting Google Spreadsheet icon, paste the URL of your Google Spreadsheet into the box, then click Validate to preview your data. If this is the first time you connect to a spreadsheet, you will be prompted to grant Holistics permission to connect to your Google Account.

Then, select any sheets that you want to import. In Advance Settings you can set import schedule, import mode, destination schema and table, and select the correct type for your columns. By default, all columns from your Google Sheets are interpreted as Text.

In the example below, I have a Google Sheet containing the list of contestants took part in our company's offline event, their win - lost status and the value of the gift to the winners. I want to put this data into the data warehouse, so I'm selecting the sheet named contestants:

2.3. Add Custom Dimensions and Measures to CSV / Google Sheet data

Custom Dimensions are created by using non-aggregate functions to transform one or multiple columns, for example, CASE ... WHEN, CONCAT(), or field_a + field_b...

From the Data Modeling page, you can add Custom Dimensions to your CSV/Google Sheet models to extend on the available fields. For example, on contestants model I can add a a field that calculate contestants' ages. Select  Add > Calculated Column, input field name and the following SQL snippet:

date_part('year',current_date) - date_part('year',{{ #THIS.birth_date }})

Click Create, and a new column named Age will be created.

After that, I create another field named Age Group to group my contestants by their age with this snippet:

case
	when {{ #THIS.age }} < 20 then 'under 20 yrs old'
	when {{ #THIS.age }} >= 20 and {{ #THIS.age }}<30 then 'from 20 to under 30 yrs old'
	else 'from 30 yrs old'
end

Metric formula is one of the main sources of headaches when different departments report on the same metrics. With Holistics, you can pre-define your calculations in Measures so everyone can have the same definition, and can get insights without writing SQL by themselves.

Measures are created from SQL's aggregate functions and operations such as COUNT(), SUM(), AVG(), SUM(field_a) + SUM(field_b)... For example, I can create a simple Measure to count the number of winners:

count(case when {{ #THIS.win}} then {{ #THIS.id }} else null end)

Or something more interesting, like the average gift value of young winners (this will combine a base field Gift Price and the Age Group field you created earlier):

avg(case when {{ #THIS.age_group }} = 'under 20 yrs old' 
		then {{ #THIS.gift_price }} else null end)

2.4. Add Relationships between model

Adding Relationships to your models allows you to combine fields and measures across models, and answer complex business questions.

For example, I will connect contestants model and users model by adding a relationship on email field:

Later if I create a data set out of these two models, I can check if certain contestants are already our customers, and also see some usage statistics about those contestants.

You can also click on the Relationship tab to view the relationship diagram of all models in your data warehouse.

2.5. Transform Your Data and Import from other data sources

Once your data is already in a SQL data storage, you can write transformations using SQL, or continue to combine them with data from other sources such as MongoDB, Google Analytics, Facebook Ads,... However, I want to keep this guide as concise and to-the-point as possible. If you are interested, you can refer to these docs for more information:

• Data Transform
• Data Import

3. Build and Explore a Dataset

Dataset is a collection of Data Models that share the same interest and have relationships with one another. You can explore a dataset by mixing dimensions & measures from different data models together. Behind the scene, Holistics's engine will generate the proper SQL queries to be run against your data warehouse.

3.1. Create a dataset

Here I'm creating a dataset named Ecommerce Dataset from 7 data models. All dimensions and measures from these models will be available for you to combine.

3.2. Explore a dataset

After successfully creating a dataset, you can easily explore your data just by dragging in fields, measures, applying conditions, and tweaking the visualizations.

Here I'm creating a column chart showing total revenue based on order status:

4. Build an interact dashboards with some filters

Finally, you can collect all your data exploration into a dashboard and add some filters to make your dashboard more useful. Click on Add filter icon on the top right corner, and choose from the five types of filters (Field, Date, Text, Number, True/False) to filter your data. For more information on how these filters work, please refer to our docs about Holistics’s Filters.

In this example, I’m creating filter named Date from field ecommerce_dataset.orders.created_at and map it to orders.created_at fields of all widgets. When a viewer changes the Date filter value, a filtering condition will be applied to all widgets that are mapped to this filter.

5. Deliver Insights

Schedule your fresh dashboards to send to your partner's mailboxes or internal communication tools such as Slack.

Click on Schedule icon on your dashboard, select New Email Schedule. Here you can type in the emails you want to your dashboard to, schedule when to send and overwrite default filter values.

Conclusion

I hope by following this guide, you got the basics of how to work with CSV/Google Sheets integrations in Holistics.

Now let's jump in and figure out how to apply this guide in your specific use cases! If you face any problem, feel free to leave a comment or contact me!