Matt Pallatt

Apr 7, 2025

434

(0 votes)

Category Management - Going old school & trying not to break anything.

You wait a hour for a bus and then 3 come at once.

The same thing happened to me recently where multiple clients with ageing websites (Opti 11) and a new content strategy wanted to see all of their content and categories mapped out so that they could update them outside of the singlular management experience that Optimizely provides.

We looked around for options and needless to say that whilst there were some viable solutions for Opti 12, our clients options were limited to zero with Opti 11.

So, I set around creating a solution to allow them to better, one off, manage their categories using .... Excel!

That's right, I fired up SQL Management Studio, and VBA for Excel, and I now share with you what has been so helpful for us.

Use this at your own risk.

Get content and category data out of Opti

Temporary tables and the messages view to the rescue...

DROP TABLE IF EXISTS #pages

DROP TABLE IF EXISTS #categories

DECLARE @website varchar(256) = 'www.erm.com'

DECLARE @i int = 0

DECLARE @j int = 0

DECLARE @n int = 0

DECLARE @m int = 0

DECLARE @catName varchar(100)

DECLARE @catNames varchar(max)

DECLARE @catIDs varchar(max)

CREATE TABLE #pages(pkID int identity(1,1), pageID int, linkurl varchar(500), PageName varchar(1000)) INSERT INTO #pages (pageID, linkurl, PageName) SELECT pkid, CONCAT(REPLACE(linkurl,'~/',CONCAT('"https://',@website,'/')),'"'), Name FROM tblContent INNER JOIN tblContentLanguage ON pkID = fkContentID WHERE linkurl is not null AND fkLanguageBranchID = 1 ORDER BY pkID

CREATE TABLE #categories(pkID int identity(1,1), catID int, catName varchar(100)); --INSERT INTO #categories(catID, catName) SELECT pkid, CONCAT('"',CategoryName,'"') from tblCategory ORDER BY pkID

WITH RecursiveCatQ AS (

SELECT pkid, fkParentID, CategoryName, CAST(pkid AS VARCHAR(MAX)) AS recursivePath

FROM tblCategory

WHERE fkParentID IS NULL

UNION ALL

    SELECT t.pkid, t.fkParentID, t.CategoryName, CAST(r.recursivePath + '-' + CAST(t.pkid AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS HierarchyPath

FROM tblCategory t

INNER JOIN RecursiveCatQ r

ON t.fkParentID = r.pkid

)

INSERT INTO #categories (catID, catName)

SELECT pkid, CONCAT('"',CategoryName,'"')

FROM RecursiveCatQ

ORDER BY recursivePath;

SET @n = (SELECT COUNT(*) FROM #categories)

SET @catNames = ',,'

SET @catIDs = ',,'

WHILE @i <= @n

BEGIN

SET @i = @i + 1

SET @catNames = CONCAT(@catnames,',',(SELECT TOP 1 catName FROM #categories WHERE pkID = @i))

SET @catIDs = CONCAT(@catIDs,',',(SELECT TOP 1 catID FROM #categories WHERE pkID = @i))

END

PRINT @catIDs

PRINT @catnames

SET @i = 0

SET @m = (SELECT COUNT(*) FROM #pages)

SET @n = (SELECT COUNT(*) FROM #categories)

DECLARE @pageCatRow varchar(1000)

DECLARE @boolPageCat int

DECLARE @pID int

DECLARE @cID int

WHILE @i < @m -- pages / rows

BEGIN

SET @i = @i + 1;

SET @j = 0

SET @pageCatRow = ''

SET @pageCatRow = (SELECT top 1 CONCAT(pageID,',',linkurl,',"' ,PageName,'"') FROM #pages WHERE pkID = @i)

WHILE @j < @n -- categories / columns

BEGIN

SET @j = @j + 1

SET @pid = (SELECT pageID FROM #pages where pkID = @i)

SET @cid = (SELECT catID FROM #categories where pkid = @j)

SET @boolPageCat = (SELECT COUNT(*) FROM tblContentCategory WHERE fkContentID= @pid AND fkCategoryID = @cid)

SET @pagecatrow = CONCAT(@pageCatRow,',',@boolPageCat)

END

PRINT @pageCatRow

END

DROP TABLE #pages

DROP TABLE #categories

This produces something that looks like this...

Give it a copy and drop it into Excel - you might have to do your own formatting to allow your categorisers to better see what's happening - but from there, they can add 1s and 0s to the content and categories that they want to.

Push content and category data back into Opti

Once everything has been updated, take to Developer mode in Excel and you can use this...

Sub MakeMeSomeSQL()

Dim ws As Worksheet
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long
Set ws = ThisWorkbook.Worksheets("Sheet2")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

For i = 1 To lastRow

For j = 4 To lastCol

If ws.Cells(i, j).Value = "1" Then

Debug.Print ("INSERT INTO tblWorkContentCategory (fkWorkContentID,fkCategoryID,CategoryType,ScopeName) VALUES ((SELECT TOP 1 pkID FROM tblWorkContent WHERE fkContentID = " & ws.Cells(i, 1) & " ORDER BY pkID DESC)," & Left(ws.Cells(1, j), InStr(ws.Cells(1, j), "-") - 1) & ",0,0)")
Debug.Print ("INSERT INTO tblContentCategory (fkContentID, fkCategoryID, CategoryType, fkLanguageBranchID, ScopeName) VALUES (" & ws.Cells(i, 1) & "," & Left(ws.Cells(1, j), InStr(ws.Cells(1, j), "-") - 1) & ",0,2,0)")

End If

Next j

Next i

End Sub

You'll want to ensure that your language branch is set, but the above will create your own SQL to reinsert content and category data, which will look a bit like...

Just make sure to remove all the existing data first before you run the SQL that's created by the above...

DELETE FROM tblContentCategory WHERE fkContentID IN (SELECT pkID FROM tblContent WHERE fkParentID = 329) -- only delete the data from pages you'll be reimporting

DELETE FROM tblWorkContentCategory WHERE fkWorkContentID IN (SELECT pkID FROM tblWorkContent WHERE fkContentID IN (SELECT pkID FROM tblContent WHERE fkParentID = 329)) -- only delete the data from pages you'll be reimporting

As I say, use at your own risk, but if you have hundreds of pages, and tons of categories, the automation here has saved us and clients DAYS if not weeks.

Apr 07, 2025

Comments

Please login to comment.