Blog posts by Matt Pallatt is not a developer2025-04-07T14:12:34.0000000Zhttps://world.optimizely.com/blogs/matt-pallatt-is-not-a-developer/Optimizely WorldCategory Management - Going old school & trying not to break anything.<p>You wait a hour for a bus and then 3 come at once.</p>
<p>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. </p>
<p>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. </p>
<p>So, I set around creating a solution to allow them to better, one off, manage their categories using .... Excel!</p>
<p>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.</p>
<p><strong>Use this at your own risk.</strong></p>
<h2>Get content and category data out of Opti</h2>
<p>Temporary tables and the messages view to the rescue...</p>
<div>
<div> </div>
<div><code>DROP TABLE IF EXISTS #pages</code></div>
<div><code>DROP TABLE IF EXISTS #categories</code></div>
<div> </div>
<div><code>DECLARE @website varchar(256) = 'www.erm.com'</code></div>
<div><code>DECLARE @i int = 0</code></div>
<div><code>DECLARE @j int = 0</code></div>
<div><code>DECLARE @n int = 0 </code></div>
<div><code>DECLARE @m int = 0</code></div>
<div><code>DECLARE @catName varchar(100)</code></div>
<div><code>DECLARE @catNames varchar(max)</code></div>
<div><code>DECLARE @catIDs varchar(max)</code></div>
<div> </div>
<div><code>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 </code></div>
<div><code>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</code></div>
<div> </div>
<div><code>WITH RecursiveCatQ AS (</code></div>
<div><code> SELECT pkid, fkParentID, CategoryName, CAST(pkid AS VARCHAR(MAX)) AS recursivePath</code></div>
<div><code> FROM tblCategory</code></div>
<div><code> WHERE fkParentID IS NULL</code></div>
<div> </div>
<div><code> UNION ALL</code></div>
<div> </div>
<div><code> SELECT t.pkid, t.fkParentID, t.CategoryName, CAST(r.recursivePath + '-' + CAST(t.pkid AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS HierarchyPath</code></div>
<div><code> FROM tblCategory t</code></div>
<div><code> INNER JOIN RecursiveCatQ r</code></div>
<div><code> ON t.fkParentID = r.pkid</code></div>
<div><code>)</code></div>
<div> </div>
<div><code>INSERT INTO #categories (catID, catName)</code></div>
<div><code>SELECT pkid, CONCAT('"',CategoryName,'"')</code></div>
<div><code>FROM RecursiveCatQ</code></div>
<div><code>ORDER BY recursivePath;</code></div>
<div> </div>
<div><code>SET @n = (SELECT COUNT(*) FROM #categories)</code></div>
<div><code>SET @catNames = ',,'</code></div>
<div><code>SET @catIDs = ',,'</code></div>
<div> </div>
<div><code>WHILE @i <= @n</code></div>
<div><code>BEGIN</code></div>
<div><code> SET @i = @i + 1</code></div>
<div><code>SET @catNames = CONCAT(@catnames,',',(SELECT TOP 1 catName FROM #categories WHERE pkID = @i))</code></div>
<div><code>SET @catIDs = CONCAT(@catIDs,',',(SELECT TOP 1 catID FROM #categories WHERE pkID = @i))</code></div>
<div><code>END</code></div>
<div> </div>
<div><code>PRINT @catIDs</code></div>
<div><code>PRINT @catnames</code></div>
<div> </div>
<div><code>SET @i = 0</code></div>
<div><code>SET @m = (SELECT COUNT(*) FROM #pages)</code></div>
<div><code>SET @n = (SELECT COUNT(*) FROM #categories)</code></div>
<div> </div>
<div><code>DECLARE @pageCatRow varchar(1000)</code></div>
<div><code>DECLARE @boolPageCat int </code></div>
<div><code>DECLARE @pID int</code></div>
<div><code>DECLARE @cID int</code></div>
<div> </div>
<div><code>WHILE @i < @m -- pages / rows</code></div>
<div><code>BEGIN</code></div>
<div><code>SET @i = @i + 1;</code></div>
<div><code>SET @j = 0</code></div>
<div><code>SET @pageCatRow = ''</code></div>
<div> </div>
<div><code>SET @pageCatRow = (SELECT top 1 CONCAT(pageID,',',linkurl,',"' ,PageName,'"') FROM #pages WHERE pkID = @i)</code></div>
<div> </div>
<div><code>WHILE @j < @n -- categories / columns</code></div>
<div><code>BEGIN</code></div>
<div><code>SET @j = @j + 1</code></div>
<div><code>SET @pid = (SELECT pageID FROM #pages where pkID = @i)</code></div>
<div><code>SET @cid = (SELECT catID FROM #categories where pkid = @j) </code></div>
<div><code>SET @boolPageCat = (SELECT COUNT(*) FROM tblContentCategory WHERE fkContentID= @pid AND fkCategoryID = @cid)</code></div>
<div><code>SET @pagecatrow = CONCAT(@pageCatRow,',',@boolPageCat)</code></div>
<div><code>END</code></div>
<div><code>PRINT @pageCatRow</code></div>
<div> </div>
<div><code>END</code></div>
<div> </div>
<div><code>DROP TABLE #pages</code></div>
<div><code>DROP TABLE #categories</code></div>
</div>
<p><br />This produces something that looks like this...</p>
<p><img src="/link/859e4ac746a8492bbdab0d2ba5557389.aspx" /></p>
<p>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.</p>
<p><img src="/link/8ac05cce0c4f436ea4e19d4893e8aa85.aspx" /></p>
<h2>Push content and category data back into Opti</h2>
<p>Once everything has been updated, take to Developer mode in Excel and you can use this...</p>
<p><code>Sub MakeMeSomeSQL()</code></p>
<p><code> Dim ws As Worksheet</code><br /><code> Dim lastRow As Long, lastCol As Long</code><br /><code> Dim i As Long, j As Long</code><br /><code> Set ws = ThisWorkbook.Worksheets("Sheet2")</code><br /><code> lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row</code><br /><code> lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column</code><br /><code> </code><br /><code> For i = 1 To lastRow</code><br /><code> </code><br /><code> For j = 4 To lastCol</code><br /><code> </code><br /><code> If ws.Cells(i, j).Value = "1" Then</code><br /><code> </code><br /><code> 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)")</code><br /><code> 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,<span style="background-color: rgb(241, 196, 15);">2</span>,0)")</code><br /><code> </code><br /><code> End If</code><br /><code> </code><br /><code> Next j</code><br /><code> </code><br /><code> Next i</code></p>
<p><code>End Sub</code></p>
<p>You'll want to ensure that your <span style="background-color: rgb(241, 196, 15);">language branch is set</span>, but the above will create your own SQL to reinsert content and category data, which will look a bit like...</p>
<p><img src="/link/b0c5e707ea504b538c55c04987ecbdea.aspx" /></p>
<p>Just make sure to remove all the existing data first before you run the SQL that's created by the above...</p>
<p><code>DELETE FROM tblContentCategory WHERE fkContentID IN (SELECT pkID FROM tblContent <span style="background-color: rgb(45, 194, 107);">WHERE fkParentID = 329) -- only delete the data from pages you'll be reimporting</span></code></p>
<p><code> DELETE FROM tblWorkContentCategory WHERE fkWorkContentID IN (SELECT pkID FROM tblWorkContent WHERE fkContentID IN (SELECT pkID FROM tblContent <span style="background-color: rgb(45, 194, 107);">WHERE fkParentID = 329)) -- only delete the data from pages you'll be reimporting</span></code></p>
<p>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.</p>
<p> </p>2025-04-07T14:12:34.0000000ZBlog postIntegrating HubSpot CRM without the MA Connector<p>Have HubSpot CRM? Want to push user data into it from Optimizely? Don’t have any personalisation requirements with that data? Don’t want to pay $800 a month for the Marketing Automation Connector? <a href="https://nuget.optimizely.com/package/?id=MP.OptiForm2HubSpotPush">Then I have something that might interest you</a>.</p>
<p>Recently we were working with a client who sat very much in the audience segment outlined above – they have HubSpot, they collect data from their website, they aren’t quite at the point where they want to start using any data from HubSpot to deliver personalisation, and they definitely didn’t have a budget for Marketing Automation Connector monthly charges.</p>
<p>Their requirement was for two specific forms – a contact us form, and a newsletter subscription, and we reasoned we could give them something completely rigid, with set fields that would only answer their current need – but we also knew, as I think every agency does, that what was a single requirement today could easily snowball tomorrow; so we got to looking at Optimizely Form actors and the HubSpot API.</p>
<p>And it turns out that half the job of what the multi-thousand-dollar-a-year Marketing Automation Connector does is relatively straight forwards to replicate using the scaffolding that Optimizely have in place around their Forms implementation and the straightforward API implementation that HubSpot has.</p>
<p>With <a href="https://github.com/mattpallatt/MP.OptiForm2HubSpotPush?tab=readme-ov-file#mpoptiform2hubspotpush">very little configuration</a> (for my money, simpler than the MA connector), we built out an extension to Optimizely Forms that on data submission, makes a request to HubSpot for a form schema, then matches field names from HubSpot to Optimizely before submitting the data to HubSpot once the comparison is completed.</p>
<p>It works alongside all the existing features of Optimizely Forms – so you can continue to hold data in Optimizely if you want to use Visitor Groups/Audiences on the data you’re collecting, you can use the mail functionality to let users know you have their data (assuming you’re not sending that from HubSpot), as well as thank you pages and all the standard and bespoke fields that Optimizely offers. All you have to do is make sure that your Optimizely Form name is the same as your field in HubSpot – simples.</p>
<p>And there’s little reason that the same thing can’t be done with Salesforce, Dynamics and other CRM platforms.</p>
<p>Anyway, it was so simple and easy to use I thought I’d carve it off and make it available for anyone who wanted to send their user data to HubSpot – or extend it to other CRM platforms of their need. So by all means steal, doctor and reuse the code at <a href="https://github.com/mattpallatt/MP.OptiForm2HubSpotPush">https://github.com/mattpallatt/MP.OptiForm2HubSpotPush.</a></p>2024-06-27T11:11:41.0000000ZBlog post