Setting up base to look for trends over time

Krista_Milliken · ‎Feb 16, 2020

Hi!

I am attempting to create a base in Airtable where we can keep track of website data analytics and trends over time as well as tie it back to major page updates and changes. I believe I have a solid understanding of how to create a table for our content changes; where I am struggling is with the structuring of website analytics data. I am currently pulling data every 30 days from AHRefs and Google Analytics, looking at page value, traffic, rank, keywords, etc. We want to track how each landing page performs over time. Historically, I have done this in Excel but we want to integrate updates made to pages and tie that into how page is performing. I am having a hard time visualizing the best way to set up a table for this. In Excel, I would use multiple lines of data for the same URL. Each data pull for the month would get its own row and would be designated by the date of the pull (for last 30 days of data). This would essentially create 2 lines of data per year for one landing page URL. Would I do the same thing in Airtable and create a Record ID for each landing page to track data? Or would you build it all into one “record” or row and build horizontally instead of vertically…or is this something that isn’t going to be done easily in Airtable. I am new to this program and just starting to learn all its capabilities, but want to make sure this is the best route to go.

Mohamed_Swella1 · ‎Feb 16, 2020

Hi @Krista_Milliken,

Welcome to Airtable.

For us to be able to help, we need to understand what you are looking to do exactly. Yes, In Airtable, records are built horizontally, because you need to define the type of each field.

If you have a specific question feel free to ask.

BR,
Mo

Krista_Milliken · ‎Feb 17, 2020

Hi Mo,

Sorry for the poor summary. Let me try again -

I want to know the best way to set up a table that will track webpage performance data over time. Essentially, each landing page/URL will be its own record and I will need to be able to import new data every month to the fields for that record, thus being able to see trends and changes over time for each landing page. My question is to know if it is better to set it up the record so that the data from every month shows on a single row, or if I can group the data by month and have the landing page (record) on multiple rows and linked together by the URL or Record ID.

Does this make sense?

Thanks,
Krista

Mohamed_Swella1 · ‎Feb 17, 2020

Hi Krista,

I would have a table for the URLs and a separate table for the data. This way, you can add the data each on a separate record while linking them together with the URL. You can then use the Chart Block (if you are on the Pro Plan) to summarize this data.

In the URLs table, the URL (or whatever you want to link in the data table) must be on the Primary field.

In the data table, you can put it in the second field (since the primary field cannot be linked). I usually use a Formula field in the primary field in such cases as to have a unique name, but that is up to you.

If you can share a screenshot of your current table and what you want to achieve that would be great. In case you cannot share them here you can send them to my email mohamedswellam@hotmail.com

BR,
Mo

Bill_French · ‎Feb 17, 2020

Ahhh… the joy of data model planning. Are we having fun yet? :slightly_smiling_face:

Many people think that data model planning is some kind of coder-speak for rocket science and when it is uttered, your eyes quickly glaze over as if you’ve just been sucked into a conversation about taxation theory at a cocktail party.

But, this is precisely what you are doing - we all must do this from time-to-time when crafting good Airtable solutions. The key to doing this well is creating a mental model - literally a map of your data shaped in a way that will allow you to see the patterns that help you most in your business.

Mo was on this point the instant he responded…

I encourage clients to always commit their [mental] data models to a physical diagram - even the simplest sketch will help you in many ways and by extension, help others to help you reach your goals faster.

The sketch is one of my favorites because it led to the development of 7 more like it, each helping to transition a team into a clear understanding of a very successful data model for trend-spotting in 17,500 dashboards - one for each of the KFC franchise stores on the planet. This sketch (and the seven that follows) saved KFC more than $250,000 and reduced the time-to-production by 11 months.

Krista_Milliken · ‎Feb 17, 2020

Mohamed - I just sent you an email. Thank you!!

Mohamed_Swella1 · ‎Feb 17, 2020

Hi Krista,

It is my pleasure to be of help.

I would have the following observations and comments:

Analytics Table:

a) For the last 3 fields (% New Sessions , New Users, Bonus Rate), customize these fields to Number instead of Text.

b) In the 2nd Field (Field 19), Customize it to Link To Another Record and choose the URL Master List. This is assuming that all the Records in Field 19 are available in URL Master List first Field (Landin Page). In case they are not, a new record will be created, so take care.

c) For the first Field in this table (Landing Page, which is now empty), if you don’t have something specific you want to show in this Field, I would suggest you add a Formula that is simply {Field19}. In any case, Step b will be creating a new field in the URL Master List and it will be showing whatever is shown in this Field, so you can play around with that no problem.

d) Finally, I would use the Group option and Group By Field 19, this will group each Landing Page on its own.

Question: In this Table you are entering new records for the analytics, correct?

Content Plan Table:

a) I would switch the 1st and 2nd Fields. Best way to do so is Duplicate the 1st Field, copy paste the 2nd Field (Vertical) to the 1st Field, Delete the Duplicate Field. Once you did that, Customize the now 2nd Field (Landing Page) to Link To Another Record (same as step b above).

b) For the Writers Field, I would change it to a Single Select Field. Once you do that, it will add all the names to the select options automatically. As for the Date Published Field, change it to a date field instead of a Text field.

Now comes the fun part :grinning_face_with_smiling_eyes: . I believe you want to see the % New Sessions, New Users, and Bounce Rate? What exactly do you want to see in these numbers? Maximum / Minimum? Count? We have several options to do here.

First, if you are on the Pro Plan, you can use the Blocks feature to add whatever Chart you need from the Analytics Table.

If you are not on the Pro Plan, we can have a work around.

Hope this was not overwhelming and was helpful :slightly_smiling_face:

BR,

Mo

Mohamed_Swella1 · ‎Feb 17, 2020

That is absolutely a good idea. Problem with writing on forums asking for help is that the other side cannot see what you are talking about and what you are trying to achieve, this would definitely help clear things up!

KellenJarvis · ‎Jun 26, 2024

Based on what you've described, structuring your data in Airtable to track website analytics over time can be approached similarly to how you'd do it in Excel. Each data pull could indeed be a new record (or row) in Airtable, with the landing page URL as a primary identifier. This way, you can easily tie updates to page performance metrics like traffic, keywords, and rank.

riyadroyad · ‎Jun 26, 2024

Airtable's strength lies in its flexibility with relational databases, so consider using linked records to connect your content updates to the corresponding analytics data. This approach helps maintain a clear audit trail of changes and their impact over time. Since you're new to Airtable, exploring its capabilities gradually while focusing on your specific needs (like tracking updates and performance metrics, check them out at https://www.scandicfusion.com ) will be key. Don't hesitate to experiment with different setups—Airtable's adaptability can be a big asset once you find the right structure for your workflow.