Initial Analysis for Embedding Dashboards on Squarespace

What are we doing and why?

Data visualization is rarely someone’s key responsibility, but nearly all roles either require or benefit from visualized input or output. In aggregate, job functions are just transforming the perceived value of input into a greater perceived value of output. We like to slice that up into lots of categories like “Technology” or “Management”, but being able to speak to how your contributions transform what is used by the company (including your cost) into something better is really powerful. Raw data tells the same story, but can leave the interpretation to others. Would you rather trust someone to accurately read thousands of records of data, or to see a little line go up?

So, we’re going to visualize some data. That could mean a lot of things, but for now that’s going to mean adding a dashboard to Squarespace. Embedding will enable quick and easy access, and it’ll help showcase the value of data visualization. Maybe it will generate a few new ideas for users (and myself) for how similar tools could be used.

Requirements

Business Requirements

While we don’t need to dive too deep into the system side yet, it’s worth noting that there are separate areas of requirements. The dashboard won’t be in charge of transforming data (for the most part), it’s job is to visualize the data it’s given. Here’s a better way to “visualize” that:

Data Input
Collected from external sources

Data Transformation
Processes and refines raw data

Data Storage
Keeps processed information

Dashboard
Visualizes insights for users

Each of these pieces could present different requirements based on the use case. But, most of these are also somewhat portable. This also kind of ignores differences in ETL or ELT, and acts as if “Storage” is a single bucket - in reality, you oftentimes need raw data storage and separate storage for the transformed data that you actually want to visualize. But, if I decide to use Tableau for visualization, Tableau doesn’t care where the data comes from. Things like transformation can be more complex, but given that my use cases are fairly simple (I won’t be building and training home grown models, probably), I would rather move quickly than spend an overt amount of time modeling future needs. Starting with dashboard functionality and determining what systems would work well with that should be plenty.

Dashboard Functionality

Considering that I don’t care about data or transformation yet, what are the baseline expectations for the dashboard itself?

• Compatible with Squarespace: Using tools that work with iframe embedding simplifies implementation

• Cost-efficient: Ideally this will be free or low-cost

• Be interactive: Allow users to filter data or dive deeper into data they care about

• Ample visualization options with minimal coding requirements

• Loads quickly and works well on all devices

Tool Comparison Summary

Data Visualization Dashboard	Pro	Con
Looker Studio	Free, integrates with existing resources, and supports embedding via iframe	Less customizable
Tableau Public/Server	Advanced visuals and interactivity	Higher costs and setup complexity
Power BI Embedded	Advanced visuals and other powerful features like generative insights	High licensing costs
Chart.js/D3.js	Fully customizable with coding	Requires significant development effort

Recommendation

Looker Studio is the most practical choice for this setup—it’s free, integrates well with Google Cloud, and seems to appease any functionality needs. But, I want to build this knowing that in the future I may want to use a different tool if Looker Studio’s limited customization gets in the way.

System Requirements

Google Cloud and Looker Studio

Considering the cost benefits, I’ll be going forward with Google Cloud. While some of the upstream jobs may not be needed, I’ve generated a diagram for how this will likely work. Some pieces are optional, and some data could be pulled directly into the dashboard. But, this is only to understand the complexity and rough cost for now.

API Source
(e.g., External Data Service)

Cloud Scheduler
Automates API requests

Cloud Function #1
Ingests data from API

Cloud Storage
Stores raw data

Cloud Scheduler
Triggers data processing

Cloud Function #2
Transforms and loads data

BigQuery
Stores analytics-ready data

Looker Studio
Interactive dashboard

API Source - Assuming the solution pulls data from an API

Cloud Scheduler - Used to schedule Cloud Functions

Cloud Functions - Used to get data and perform data preparation tasks like cleaning or transformation

Cloud Storage (Optional) - Depending on the data I’m working with, I can use this for backups as needed. If not needed, I can combine the Cloud Functions into single pull and transform script

BigQuery - This will be the data warehouse to query directly from Looker Studio

Looker Studio - Where visualization happens!

Putting It All Together

Would this meet my requirements?

Probably. It’s good enough that I feel comfortable going forward, at minimum. It will require some basic coding and a familiarity with Google Cloud/Looker Studio, but the real complexity will come in regarding how the data should be transformed. With that in mind, I want to break this work up into chunks.

1. Proof of Concept for Squarespace to Looker Studio (data source simplified to ensure user experience of Looker Studio on Squarespace is acceptable) - This will just prove out that I’m happy with Looker Studio on Squarespace. I’ll build a simple dashboard and use random data that won’t rely on Google Cloud.

2. Next, I want to use BigQuery as the data source. Using the same or different data, I want a simple dashboard that uses BigQuery as the data source.

3. If all goes well, I’ll think through data and design needs for a real use case - this includes the use of resources outlined above like Cloud Functions.

4. Implement systems for real use case.

5. Build new Looker Studio Dashboard.

6. Embed and test dashboard.

Time to get started!