Back to top button

Creating a no-code Webflow Popular Post Collection List Section

Creating a no-code Webflow Popular Post Collection List Section
We've always wanted to do this, and today we said WE WILL, and, WE DID!!! We wanted to create a blog post section in Webflow that sorted posts according popularity (PageViews) in your CMS Collection list, but Webflow doesn't count PageViews so came up with a no-code solution. Check out the post and follow along to replicate this for yourself.

We're not gonna lie, this was not easy, it took a lot of trial and error, but we got there in the end. There are probably much simpler ways to do this by just querying the Webflow API and the Google Analytics API and then using the Webflow API to push the pageviews back to Webflow, but we chose Webflow as our platform of choice because we're not coders, and well, we wanted no-code. We promise, this is a no-code solution, just copy paste and some spreadsheet skills. We've been thinking about how to do this for a while and had a half the solution for a few months but gave up (see the thread on the Webflow forum here). Anyhow, today we were sitting around waiting on feedback in Figma from a client and decided to make it work, and weirdly we figured it out this time round. It's only been running a few hours but we added a new post to the blog, published went and click on it a a while later Zapier updated the pageviews in Webflow for the new post as well. So here we go.

So let's start at the beginning. We're assuming you've set no fields in your CMS as required, because that means you have to fill them in when you update a live item via Zapier, so with that assumption things are simpler (If you do have them you're gonna have to fill in those fields in Zapier with the data on the Webflow post export sheet we'll create later).

Our initial idea was the following, if we could get our blog post pageviews out of Google Analytics into a Google Spreadsheet we could update the collection list with a pageview field in Webflow and then simply sort the collection list according to pageviews for that pageview field. It turned out to be more complicated, and we'd like to start off by saying if you have a better solution, or can make even on step below easier, please please comment and let us know. We want this to be as simple as possible, and it's easy implement, but not super simple at the moment.

Anyhow, like we said, our assumption was wrong, we didn't just need Pageviews, in order to update a post via Zapier we needed 3 things:

  • The Post (Collection List Item ID)
  • The Post Name (Collection List Item Name)
  • The Pageviews for the corresponding post from Google Analytics

Unfortunately, there's no easy way to just get the Name and ID out of Webflow with Zapier or just about anything else without using the API, and we wanted none of that, so we came up with a solution that would do alllllll of this in a Google Spreadsheet and then use Zapier to push the pageviews to Webflow.

Let start by listing what you're gonna need and why:

  • Google Analytics - You're gonna need this to track your PageView and you're gonna need to use Google Universal Analytics and not GA4 as the Google Analytics Add-On for Google Sheets uses UA and not GA4. For help on setting up Google Analytics in Webflow follow this post and then this post.
  • A Google Sheet to do all this in. It's free, just sign up. You Google Sheet will include a scheduled import from Google Analytics, a scheduled import of your blog collection list from Webflow and some extra sheets to do some calculations and cleanup on. You could probably do this with a lot less effort than we went through, but our spreadsheet skills also suck, so if you can make anything easier, just shout in the comments.
  • The Google Analytics Google Sheets Add-On. Go ahead and install it here. This allows you to import the latest pageviews for your blog posts from Google Analytics straight into Google Sheets and schedule and import on daily or hourly basis.
  • The Mixed Analytics API Connector Add-On for Google Sheets. Get it here. This allows you to import your Webflow Blog CMS posts right into Google Sheets without any code, giving you the Collection List ID for each item and the item name that you'll need when you update the live items via Zapier. We paid so we had no limitations on how much or how often we could use the tool.
  • Zapier to check for new pageviews in the sheet and instantly update the PageViews field in your Webflow Blog's collection list once new pageviews were imported from GA. We already has a paid account for this as we use it for a lot of other things, but you can probably get away with a free account.
  • You can preview our Google Sheet here if you lose track of what we're doing. (Don't judge our pageviews, nobody's very interested in our posts about Webflow Analytics and whatnot)

Step 1 - Set Up Google Analytics if you haven't done that.

You can follow these two posts on how to do this via Google Tag Manager and then add Google Analytics, which is the way we would go every time and we wrote them specifically for Webflow, but if you're not comfortable with that, just set up a Google Analytics Universal Analytics (GA3) account and add the UA code to your Webflow project settings. If you haven't set this up you're yet you're gonna want to wait a day for some data to come in otherwise you're gonna have no idea what you're doing as all your metrics are gonna be empty.

Step 2 - Import your Google Analytics Pageviews into Google Sheets.

Create a Google Sheet, this is gonna be the sheet we do everything in, so file it away nicely and don't lose it.

Once you've done that, add the Google Analytics Add-On.

  • Go to Add-ons, then get add-ons and look for Google Analytics or just get it here.
  • Create new report (Add-Ons -> Google Analytics -> Create New Report)

You're gonna want the following:

  • Your View ID from Google Analytics, they help you with this in the setup.
  • A start and end date, so from when till when you want the report, we went with the start of the year till today (just add today or yesterday in the end date field if you're gonna schedule this to run daily our today for hourly.
  • What Metrics you want to see, we went with users and pageviews, but you just need pageviews.
  • What Dimensions you need to pull, we went with Page Title and Page Path, but you just need the path.
  • And we added a filter so we only get the pageviews from our blog posts. Our collection list is just titled posts, which means the page path for all our blog posts start with /post/ so our filter is set to contain /post/.

That's it, it should look something like this in the spreadsheet once you're done:

Google Analytics Add-On Settings in Google Sheets

Now test by running the report. Add-ons -> Google Analytics - Run Reports.

You'll end up with a new sheet containing the report that looks something this:

Google Analytics Report in Google Sheets.

If you're happy that everything worked you can go ahead ad schedule it, our's runs every hour as our end date is set to today, but you can just go ahead and do it once a day if you're happy with that.

Step 3 - Import your posts from Webflow

You need to do this so you can get the Post ID and Title that you'll need when you push the pageviews to Webflow via Zapier. Go ahead and instal the API Connector foe Google Sheets. Do it through Add-ons like before or get it here.

Now follow these steps to import your posts:

  • Get your Webflow API Key and save it somewhere. Navigate to your site’s dashboard and open Settings and go to the section Integrations, followed by API Access.
Webflow API Key
  • Get your Site ID. Go to Add-ons in Google Sheets, then API Connector, then Open, then Create or Add New.
  • For this step the API URL Path is: https://api.webflow.com/sites
  • The Value Pairs are Authorization - Bearer {API_TOKEN_HERE} and Accept-Version - 1.0.0

It should look like this:

API Connector Webflow Site Settings.

Give the destination sheet field a name and then name, save and run the API request. You'll end up with a new sheet that will contain the Site ID. It should look like this:

Webflow API Site ID

Next you want to import all your collections so you can get the ID for your blog post collection list.

  • Same process as before, just a different URL for the request: https://api.webflow.com/sites/:site_id/collections
  • Copy the above API URL into the API Connector sidebar, being sure to fill in :site_id with your corresponding site_id from previous step in the new sheet that's called _id in the first column, along with the key: value pairs as your headers, just like in the previous step.
Webflow Collection List ID API ID Request

The newly generated sheet will look like this:

Webflow Collection List ID

Now we want to pull in the Collection list schema.

  • Same process, just a different URL and you're gonna use the new blog collection list ID. Your new API request URL is: https://api.webflow.com/collections/:collection_id

Setup should look like this:

Webflow Post Schema APi Request.

And here's my blog post schema:

Webflow Blog Post Schema in Google Sheets.

Last step, pull the actual blog posts and you're done, same as before, here's your new URL: https://api.webflow.com/collections/:collection_id/items

and the screenshot of the setup:

Webflow Post Import API Request.

And the result:

Webflow API Post Import into Google Sheets.

Now you have all you blog content in the Google Sheet and the two columns you will need for Zapier, items.name and item.id. That's the hard part done, now it just cleanup, formatting and pushing the pageviews to Webflow via Zapier. If all this worked, go ahead and schedule that last pull of the blog posts to run in the API addon so that it'll update when you create new posts.

So from this point I'm not gonna no into tons of detail, because to be honest, we're crap at spreadsheets and it's not the best implementation. If you need the details the sheet is here. If I knew any regex this would have been much simpler but I had to do things the long way. I'll give you the broad strokes and you can do a better job than me.

To work it backward you want to end up with a sheet that that contains 3 columns. The Post Name, the Post ID and the Pageviews for the post. I used a pivot table for this, but before I did that I had to clean everything up and match up the Google Analytics Pageviews to the ID and Name from the API post export.

I created a sheet called Blog Views and matched up the two different sets of data using the url slug from the Webflow page export to match to a cleaned up slug from the page path in the Google Analytics export.

In the Blog Views sheet I queried the sheet that contained the stats from Google Analytics: =QUERY('Milk Moon Studio GA Blog Posts'!A15:D)

I got the slugs for the Google Analytics stats using an array and the little bit of regex I know to drop the /post/: =ArrayFormula(REGEXREPLACE(B2:B,"/post/",""))

There were all kind of crap from some ads we ran at one stage on Facebook, but since I'm not great with regex I just used split to remove the Facebook ?fbclid string: =ArrayFormula(SPLIT(E2:E, "?"))

Then I used a VLOOKUP Array to match the Webflow IDs to the Google Stats: =(arrayformula(IFERROR(vlookup(F2:F,Webflow_blog_post_export!G2:Z,16,FALSE))))

Then another to get the titles: =(arrayformula(IFERROR(vlookup(H2:H,J2:K,2,FALSE))))

Then another for items and names on that sheet: =QUERY(Webflow_blog_post_export!V1:V) and =QUERY(Webflow_blog_post_export!F1:F)

Then the pivot Table to sum all the stripped slugs' pageviews together and to get the pageview, name and id on the one pivot table sheet.

From there it's easy.

Head to Zapier, set up a Zap that will check the Google sheet for changes to the pageview column on the pivot sheet as a trigger, then add Webflow to the mix selecting update live items in CMS and use the Pageviews, ID and Name Columns to update Webflow.

Now all you have to do is wait for data to filter into Webflow and sort the collection list by Pageview.

Like we said before, if you have any tips on how to make this simpler, please please post in the comments and we really hope this works for you.

UPDATE

So it's been running for like 12 hours and everything seems to be updating and working. Once thing that did pop up was that when this post was being written saving it as a draft did break one of the array formulas till it was published as the number of columns in the sheet changed, so something you'll need to keep in mind if you dodn't have any drafts in the beginning is that a new column will be added meaning your formulas will shift to the right.

For more how-to check out the how-to section on the blog.

recent projects