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 #pagesDROP TABLE IF EXISTS #categoriesDECLARE @website varchar(256) = 'www.erm.com'DECLARE @i int = 0DECLARE @j int = 0DECLARE @n int = 0 DECLARE @m int = 0DECLARE @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://',,'/')),'"'), 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 pkIDWITH 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 RecursiveCatQORDER BY recursivePath;SET @n = (SELECT COUNT(*) FROM #categories)SET @catNames = ',,'SET @catIDs = ',,'WHILE @i <= @nBEGIN SET @i = @i + 1SET @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))ENDPRINT @catIDsPRINT @catnamesSET @i = 0SET @m = (SELECT COUNT(*) FROM #pages)SET @n = (SELECT COUNT(*) FROM #categories)DECLARE @pageCatRow varchar(1000)DECLARE @boolPageCat int DECLARE @pID intDECLARE @cID intWHILE @i < @m -- pages / rowsBEGINSET @i = @i + 1;SET @j = 0SET @pageCatRow = ''SET @pageCatRow = (SELECT top 1 CONCAT(pageID,',',linkurl,',"' ,PageName,'"') FROM #pages WHERE pkID = @i)WHILE @j < @n -- categories / columnsBEGINSET @j = @j + 1SET @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)ENDPRINT @pageCatRowENDDROP TABLE #pagesDROP 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.
Comments