Milk Moon Studio
Published in

Milk Moon Studio

Creating a no-code Webflow Popular Post Collection List Section

Sort order

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:

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:

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.

You’re gonna want the following:

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

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:

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:

It should look like this:

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:

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

The newly generated sheet will look like this:

Now we want to pull in the Collection list schema.

Setup should look like this:

And here’s my blog post schema:

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:

And the result:

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 don’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.

Originally published at https://www.milkmoonstudio.com.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jakes van Eeden

South African, small time Webflow dev, analytics enthusiast and tech lover. I write on behalf of our Webflow design and dev agency milkmoonstudio.com