The Seotistics newsletter is written by Marco Giordano, a Data/Web Analyst with the goal of combining business and web data. Tired of the usual boring Analytics content without any business impact? Seotistics teaches you how to use Analytics, web data and even content in your workflow while helping you with Strategy.
Share
Data Modeling & Slow Dashboards
Published about 17 hours agoΒ β’Β 5 min read
β
Use Data Or Be Used By Data!
The April 13 issue of Seotistics is here for you!
Data modeling is a big deal and yet, you often see it discussed as a marginal topic.
Do you know how many companies are using outdated practices and paying the consequences?
This issue is a short introduction to a topic that I will dissect in my new course and future content (also check my past article in the resources).
P.S. The Seotistics Sales end up tomorrow night, be quick π
Please move this email to your Primary inbox or reply to it. This is to prevent Seotistics goes into spam by accident. Gmail users can read this tutorial to do it.
This will be for Analysts or everyone wanting to explore Web Data in general, not only SEO.
The course is split in 4 amazing modules: β β GA4, GSC and other data sources: how to use them β LLM Workflows, BigQuery, SQL & Python β Dataform & Pipelines β Real Use Cases at work
Oh, there are also the Seotistics sales on all the products until tomorrow (April 14)!
Instructions and code here:
β
Product updates will follow soon, very soon!
Data Modeling: Again
It's a way to represent how your data is structured and it connects with each other.
As you know Analytics isn't about a single data source but multiples.
To make order out of chaos, you need a proper structure that makes the most out of your data.
You can say this is needed to activate your data, aka make it usable for business purposes.
Common Problems
Let's just list what are the common problems you face when NOT doing proper data modeling.
This is what I see with 99% of the companies.
Now Data Studio again as the name Looker Studio is obsolete!
Slow Dashboard/Data Products
"Data Studio is slow!"
No, it isn't lol
The reason is often that you are using the wrong data model so the dashboard loads a LOT more data.
Imagine you open a dashboard and see 5 blends and a lot of operations on big data... would you say this is fast?
Of course not, Data Studio is trying to load a LOT of data at once.
Maybe you didn't even remove the rows you don't need, rookie mistake!
But this is actually a fast one! And not because the data is small... there are no blends! It's 4 separate tables, the 3rd one is GA4 + GSC, no blend!
If stakeholders get bored of your product, they won't use it.
A slow dashboard kills their attention span, whereas they can just go back to a tool like GA4 and see what they want.
The reason why this and other problems happen is that data isn't stored and processed:
Yeah need to update this one with Data Studio!
Once you understand the logic, it becomes obvious why Data Studio is not performing.
It's not its fault!
Slow and/or Expensive Queries
An underrated problem is the cost of your operations.
Cool, you can tell me what was my revenue last year and even have dedicated metrics...
but how much did it cost you?
The other day I was supposed to run a query of 2GB π
But with a proper data model you can spend like below to get the same result:
Same query after using an "optimized" table that is already unnested. This is the ga4 events table from GA4Dataform.
You also have to adapt to multiple scenarios and learn how to query bigger tables:
This query here helps you save money and time because you are just scanning a piece of the data. This is for exploratory purposes though!
Managing costs is a priority once you have quite some data and operations in place.
The Common Solution
Doing your data modeling properly and understanding what is actually needed.
This means not using the data as it comes from the source and applying some operations on top.
Common Schemas
Without spending too much time on the schemas, I usually go for 2 approaches:
star schema
marts
Both have their use cases and are must haves today.
The star schema simply consists in having fact and dimension tables.
Facts contain quantities or events, like our beloved GA4 events table.
Dimensions are all about qualities and properties, imagine having a table listing all URLs with their categories.
Dimension connect to fact tables so you need to blend (or join) them together.
A common example I share to describe modeling. If you add fact and dim here and there, this is a star schema.
A common example is splitting the original events table from GA4 separate into separate tables that are connected to each other.
The issue with this approach is that you need too many joins to get what you want.
Consider getting how many users had transactions on a specific page...
The marts approach is what I prefer the most nowadays and it's actually simpler.
You have dedicated tables that represent reports or describe business logic.
GA4Dataform does it properly.
The tables created via GA4Dataform are also marts and do NOT follow the star schema.
It's normal to apply some changes on top of them to make the tables more tailored to your business...
or just join different data sources.
Mandatory Activity
I've been working recently on some examples of data modeling for different companies.
Remember, there is no supreme or better approach, you need to make it tailored to your use case.
If you want to visualize your work, you need to prepare an ERD (Entity Relationship Diagram).
There are many tools out there like dbdiagram.io that allow you to prepare something like this:
Quick prototype of GA4, GSC and Google Ads data for a PowerBI use case.
This is a must to visualize your data model and explain it to people.
(Otherwise use Miro and do it manually, they have templates for that).
Data modeling gives you a way to better understand your business and data needs.
There is no Analytics without good Engineering and common sense.
This issue was just a simple intro and reminder on data modeling.
The next ones will cover some more details about Web Data that companies get very wrong!
π₯ Join Our Community
Our Discord community offers a small place where we can talk business and web data.
If you hate all the noise of social media, then this place is for you.
I will start posting more there as we have a forum channel now.
This is the best way to stay updated in real time on Seotistics:
The Seotistics newsletter is written by Marco Giordano, a Data/Web Analyst with the goal of combining business and web data. Tired of the usual boring Analytics content without any business impact? Seotistics teaches you how to use Analytics, web data and even content in your workflow while helping you with Strategy.
Read more from Seotistics - Web Analytics + Business + Strategy
Use Data Or Be Used By Data! The April 7 issue of Seotistics is here for you! The other time we talked about Claude Code... and now it's time for part 2! We will cover business context and those juicy leaks from last week. I still need some time to figure it out but hey, get this preview! P.S. Won't link the actual leaks for clear legal reasons π Please move this email to your Primary inbox or reply to it. This is to prevent Seotistics goes into spam by accident. Gmail users can read this...
Use Data Or Be Used By Data! The March 30 issue of Seotistics is here for you! Claude Code is the absolute GOAT and today I will show you how to actually use it. Or well, what you need to start and avoid writing random prompts. If you are half-serious about Web Analytics, you can NOT skip Claude Code. AI is the divider: forget about learning GA4/GTM and call it a day. This issue will introduce you to the key concepts that we will cover deeply in the upcoming weeks. Please move this email to...
Use Data Or Be Used By Data! The March 23 issue of Seotistics is here for you! After a pleasant experience at MeasureCamp Milan, it's time to talk about new ideas and projects. In the last issue I mentioned what you can actually build today... now I give you even more ideas. Not all of them actually come from this Saturday but I will mention them anyway π P.S. Check the very bottom as usual to find my presentation and ALL the resources. π Please move this email to your Primary inbox or reply...