<?xml version="1.0" encoding="utf-8"?><feed xmlns="http://www.w3.org/2005/Atom"><title type="text">Blog posts by Matt Pallatt is not a developer</title><link href="http://world.optimizely.com" /><updated>2026-07-02T08:52:15.0000000Z</updated><id>https://world.optimizely.com/blogs/matt-pallatt-is-not-a-developer/</id> <generator uri="http://world.optimizely.com" version="2.0">Optimizely World</generator> <entry><title>LanguageMaster! From Managing to Mastering Languages!</title><link href="https://world.optimizely.com/blogs/matt-pallatt-is-not-a-developer/dates/2026/7/from-managing-to-mastering-localisation/" /><id>&lt;p class=&quot;MsoNormal&quot;&gt;Two years ago, I released &lt;a href=&quot;https://nuget.optimizely.com/packages/mp.languagemanager.deepltranslate/1.0.10&quot;&gt;my first Optimizely add-on&lt;/a&gt;. It was an extension to the Labs.LanguageManager tool from Optimizely that allowed the user to choose to use DeepL as their translation provider. I noticed recently that it has the highest number of downloads of any NuGet package used by LanguageManager &amp;ndash; I&amp;rsquo;m very sure more to do with how many people are using DeepL rather than with the stunning code quality.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/0f2f8ab3a2e549a2a0d268b442f581c0.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&amp;nbsp;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;In the two years since, we have used DeepL and LanguageManager with a few of our clients, and I&amp;rsquo;m sure each of them would have a significant list of issues with both the functionality and usability of LanguageManager, and more than a few would have complaints about the quality of DeepL too &amp;ndash; and I don&amp;rsquo;t particularly think the issues or complaints they&#39;re raised (and internalised) are unfounded.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;As a tool, LanguageManager (which has been newly updated to support CMS 13) allows CMS users to select a root node, then either choose to include all children, and/or all associated blocks, and then fire them at a translation endpoint, where what is returned is used to create or update a new language branch version of the source page. It&amp;rsquo;s basic, though does the job, but also has some shortcomings &amp;ndash; especially (in my humblest of opinions) with Page References in content areas, where suddenly those &amp;ldquo;children and associated content items&amp;rdquo; checkboxes can result in what you thought was a 15 item content translation job turning into 15,000 content items as the spider crawls outwards, upwards and forever on and on (this has happened, huge costs have been incurred, people have not been happy).&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;And you&amp;rsquo;re tied to one translation provider at a time &amp;ndash; maybe Azure is great for general content, but products would benefit going through DeepL? And translations into Chinese must use Baidu because it&amp;rsquo;s got the best vocab for that locale. LanguageManager can&amp;rsquo;t do that without constantly updating the settings to match the provider you have in mind.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;And the locale granularity problem, where in China for example, the many variations of dialect and character mean that the target slug as a translation target isn&#39;t ideal - you want Hong Kong oriented translations on /cn? No way!&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;In small demo sites, translation management can look simple, but in the real-world of CMS and Commerce builds, content relationships, reusable blocks, products, variants, regional language requirements and even provider functionality make it much more complicated.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;strong&gt;Enter Language Master!&lt;/strong&gt;&lt;/p&gt;
&lt;ul&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;Powerful selection tools for choosing what you want to translate, including full CMS and Commerce content tree, and Smart Select functionality for batch selections!&lt;/li&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;Multi-provider functionality for different scenarios, with translation endpoints for MS Azure, DeepL, Google Cloud, Amazon Translate, Claude, Baidu, ModernMT, OpalAI and Smartling &amp;ndash; or build your own!&lt;/li&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;Per provider translation caching &amp;ndash; so you can&amp;rsquo;t accidentally end up repeatedly sending the same source language and content items to be translated!&lt;/li&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;Provider routing so you can choose which configured provider delivers translations for which content!&lt;/li&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;Chained provider routing allows you to make better use of monthly free character tiers across multiple providers. Once Azure&amp;rsquo;s free allowance is used, for example, fallback can seamlessly continue through Google&amp;rsquo;s free allowance!&lt;/li&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;Language target override allows you to use a specific provider-supported locale, such as zh-Hant-HK, even when your site branch uses a simpler slug such as /cn/!&lt;/li&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;Projects allow you to build translation packages over time and run them once when you&amp;rsquo;re ready to translate all your content!&lt;/li&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;Full audit logging allows you to see if anything went wrong and why!&lt;/li&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;Commerce support for Categories, Products and Variations!&lt;/li&gt;
&lt;li class=&quot;MsoNormal&quot;&gt;And&amp;hellip; Multisite support!&lt;/li&gt;
&lt;/ul&gt;
&lt;p&gt;&lt;strong&gt;What all of that looks like in practice...&lt;/strong&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Language Master&amp;rsquo;s &lt;strong&gt;Tree View&lt;/strong&gt; allows an editor to select page content nodes, select block content node, select child nodes - you have full individual control over what is going to be translated. It also shows what&amp;rsquo;s already been translated, and what&amp;rsquo;s still in need of a new language version.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/fb1b443dc74346248e1a98eee837a852.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;For larger trees, &lt;strong&gt;Smart Select&lt;/strong&gt; allows for batch selections based on defined criteria &amp;ndash; content types, publish status, parent node, date modified &amp;ndash; completely extensible interface to build your own criteria for selection.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/075fa458c86c410d9f5736e9123d6a64.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Language Master has pre-built &lt;strong&gt;Translation Providers&lt;/strong&gt; for MS Azure, DeepL, Google Cloud, Amazon Translate, Claude, Baidu, ModernMT, OpalAI and Smartling &amp;ndash; all of which can be configured and activated at the same time! And a framework to allow you to build your own provider if it&amp;rsquo;s not in the list.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/f1a593308ea34f318f851a425ff135b1.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Your activated providers can then be used to create &lt;strong&gt;Routing Rules&lt;/strong&gt; for different language pairs &amp;ndash; with a fallback for if you&amp;rsquo;ve got no specific language requirements.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/3306ada08cd545cbb3e3f15e73120a84.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;The same language pairs can be configured multiple times &amp;ndash; essentially &lt;strong&gt;Chaining Providers&lt;/strong&gt; - to provide fallback in the event of error &amp;ndash; or having no translation credits left.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;strong&gt;Provider Overrides&lt;/strong&gt; mean that for content specific cases, you can abandon routing rules and send individual content items to a selected provider.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/78fc054736444a4a99faa737b4bb539c.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;strong&gt;Language target override&lt;/strong&gt; allows the editor to define the specific language code to be used to translate into &amp;ndash; depending on provider availability!&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/6b12e4d576ff4660bedb900abccec400.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;strong&gt;Batch Update&lt;/strong&gt; means that selected content items can have their translation properties &amp;ndash; publish status, provider choice, name and slug translation options - updated all at once.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/cdb91802d9cb424ab7d353badb2be47b.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;strong&gt;Projects &lt;/strong&gt;update the tree view to show what is in the project and what is not.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/5602c4f19674499b952caf3dc535d6c3.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Projects can support multiple language pairs, making it possible to prepare translation work for different source and target content items, and run them as a single package when the content is ready. This means you don&amp;rsquo;t need to have separate projects for different languages &amp;ndash; though you can if you want to!&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/3c870f05a673462ba93d71073c8ecf63.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;And provides a way of batching together multiple updates over time, to be translated all at once.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/3b051cb389d64417890eebe6ba48edb5.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;strong&gt;Audit Log&lt;/strong&gt; allows the editor to see what has been translated and when, whether any errors occurred, and if needed allows the editor to invalidate any cache that exists for the content item and provider.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/f092b61977e742a6930861a4ab034956.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;And it has &lt;strong&gt;Commerce support. &lt;/strong&gt;Language Master covers Categories, Products, and Variations &amp;mdash; not just CMS pages and blocks.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/6e02d19e9ec44c48ad683bd90b8440f6.aspx&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Going back to that DeepL extension: releasing it and watching it used across real projects showed me exactly where LanguageManager struggles. Not because it&#39;s a bad tool &amp;mdash; it does what it does &amp;mdash; but because real multilingual projects in production are messier than it was built to handle.&amp;nbsp;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Language Master has been designed to tidy up that mess.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;The packages are available on &lt;a href=&quot;https://nuget.optimizely.com/?q=mp.languagemaster&quot;&gt;Optimizely&#39;s Nuget Feed&lt;/a&gt; for CMS and optionally Commerce.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;If you try it, I&#39;d genuinely like to hear what works, what doesn&#39;t, and what you&#39;d want to see next - I&#39;m thinking a SaaS version...?&lt;/p&gt;</id><updated>2026-07-02T08:52:15.0000000Z</updated><summary type="html">Blog post</summary></entry> <entry><title>Environmental DXP badges</title><link href="https://world.optimizely.com/blogs/matt-pallatt-is-not-a-developer/dates/2026/6/an-indicator-for-your-particular-dxp-environment/" /><id>&lt;p class=&quot;MsoNormal&quot;&gt;I recently released my first Optimizely add-on in a while, that was based on some client feedback on the Optimizely DXP &amp;ndash; they wanted to be able to &lt;a href=&quot;https://nuget.optimizely.com/packages/mp.dxpcontenttransfer/&quot;&gt;push content from preproduction to production&lt;/a&gt; due to their workflow involving content creation taking place in the non-live environment.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Whilst working to make the UI for that tool as clean and helpful as I could - chiseling all the corners and gilding all the lilies, etc. - I ended up putting in a little UI mechanism to allow editors and administrators to see what environment they are in.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Just a little badge, that makes it clear where you are so that you know where your content is coming from...&lt;span style=&quot;mso-no-proof: yes;&quot;&gt;&lt;!--[endif]--&gt;&lt;/span&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/811af14eb171458a837399012fd247c0.aspx&quot; width=&quot;424&quot; height=&quot;155&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Having put it into the content transfer tool, I then came across this&amp;hellip;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;a href=&quot;https://feedback.optimizely.com/forums/966081-cms-paas-content-management-system/suggestions/50995978-ui-change-to-distinguish-prod-from-preprod-and-int&quot;&gt;https://feedback.optimizely.com/forums/966081-cms-paas-content-management-system/suggestions/50995978-ui-change-to-distinguish-prod-from-preprod-and-int&lt;/a&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&amp;hellip;and thought it might actually be nice to also give people who don&amp;rsquo;t want to copy content between DXP environments the same UI niceties to help them know which environment they&amp;rsquo;re working in - so it&#39;s now standalone, removed from content transfer tool, and working in CMS 12 and 13.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;It&#39;s mega simple and also completely configurable on a per environment basis, so you&#39;re not stuck with integration, preproduction and production if your pipeline uses different terminology - TEST, UAT, LIVE, etc.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/29491f1ced1c4d00a3f24131fa3599c5.aspx&quot; width=&quot;602&quot; height=&quot;201&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;You can nuget it over at&amp;nbsp;&lt;a href=&quot;https://nuget.optimizely.com/packages/mp.dxpenvironmentindicator/&quot;&gt;https://nuget.optimizely.com/packages/mp.dxpenvironmentindicator/&lt;/a&gt; if you&#39;re interested.&lt;/p&gt;</id><updated>2026-06-18T09:02:56.0000000Z</updated><summary type="html">Blog post</summary></entry> <entry><title>Pushing Content Between Optimizely DXP Environments</title><link href="https://world.optimizely.com/blogs/matt-pallatt-is-not-a-developer/dates/2026/6/pushing-content-between-optimizely-dxp-environments/" /><id>&lt;p class=&quot;MsoNormal&quot;&gt;We&#39;ve been working with a client who has been on Optimizely 11 for a long time - self-hosted, on their own server infrastructure, with a content workflow that for various reasons sees their editorial team creating and getting internal sign off in staging, then manually recreating it on production. Because the editors don&#39;t have the admin access required to export and import content packages, every approved piece of content has to be rebuilt field by field in the production environment. It&#39;s time-consuming, it&#39;s error-prone, and it&#39;s the kind of friction that quietly chips away at a team&#39;s confidence in their CMS.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Recently we convinced them to upgrade to Optimizely 12 (13 is a bit too shiny new and a reach too far for them for the time being) and move into the DXP, and we&#39;ve had a genuinely &amp;ldquo;interesting&amp;rdquo; time migrating them and ensuring that their hundreds of pieces of functionality, page types and blocks survived the transition intact. I&amp;rsquo;d been most excited about showing the performance gains from moving off the client&#39;s server farm and into the DXP - they are significant with substantial Lighthouse score improvements to back up the perception.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;The client was seemingly pleased.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;And then they asked: &lt;em&gt;&quot;Does moving to the DXP mean we can push content between environments?&quot;&lt;/em&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;It was a requirement that nobody had mentioned before, and yet something that their editorial team desperately wanted.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Let&amp;rsquo;s face it, it&#39;s a completely reasonable question. The DXP is a managed cloud platform - surely it should handle that kind of thing? As we know, the answer is no, that&#39;s not what the DXP is built for. Content promotion between environments isn&#39;t part of the offering. Watching that land wasn&#39;t the highlight of my day.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;So, I built something.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;&lt;img src=&quot;/link/41202fde18944b328bfbd4347ce569d8.aspx&quot; width=&quot;544&quot; height=&quot;332&quot; /&gt;&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Using the Content Delivery API and Content Management API, I developed a gadget that sits inside the CMS and does exactly what the client needed.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;You choose a page, pick the target environment, decide what publication state you want it to arrive in, choose whether to include child pages, and decide whether to overwrite anything that already exists at the destination. Hit Transfer, and within seconds the content you approved in Preproduction is live in Production. It does pages, images and blocks, and respects local and global assets.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;It&#39;s a straightforward tool, but it solves a real problem - and for a team that has been manually duplicating content across environments for years, it should make a meaningful difference to their day. If you&#39;re on the DXP and this workflow sounds familiar, it&#39;s something worth exploring.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;And it also potentially gets around circumstances where an export is just too huge to be reimported &amp;ndash; one of my own bug bears &amp;ndash; although it&amp;rsquo;s only been tested on trees of 20 pages or so, there&amp;rsquo;s no reason it shouldn&amp;rsquo;t be able to handle hundreds or thousands of pages.&lt;/p&gt;
&lt;p class=&quot;MsoNormal&quot;&gt;Have a look over at &lt;a href=&quot;https://nuget.optimizely.com/packages/mp.dxpcontenttransfer/&quot;&gt;https://nuget.optimizely.com/packages/mp.dxpcontenttransfer/&lt;/a&gt;&lt;/p&gt;</id><updated>2026-06-02T09:06:23.0000000Z</updated><summary type="html">Blog post</summary></entry> <entry><title>Category Management - Going old school &amp; trying not to break anything.</title><link href="https://world.optimizely.com/blogs/matt-pallatt-is-not-a-developer/dates/2025/4/category-management---going-old-school--trying-not-to-break-anything/" /><id>&lt;p&gt;You wait a hour for a bus and then 3 come at once.&lt;/p&gt;
&lt;p&gt;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.&amp;nbsp;&lt;/p&gt;
&lt;p&gt;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.&amp;nbsp;&lt;/p&gt;
&lt;p&gt;So, I set around creating a solution to allow them to better, one off, manage their categories using .... Excel!&lt;/p&gt;
&lt;p&gt;That&#39;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.&lt;/p&gt;
&lt;p&gt;&lt;strong&gt;Use this at your own risk.&lt;/strong&gt;&lt;/p&gt;
&lt;h2&gt;Get content and category data out of Opti&lt;/h2&gt;
&lt;p&gt;Temporary tables and the messages view to the rescue...&lt;/p&gt;
&lt;div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DROP TABLE IF EXISTS #pages&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DROP TABLE IF EXISTS #categories&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @website varchar(256) = &#39;www.erm.com&#39;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @i int = 0&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @j int = 0&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @n int = 0&amp;nbsp;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @m int = 0&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @catName varchar(100)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @catNames varchar(max)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @catIDs varchar(max)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;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,&#39;~/&#39;,CONCAT(&#39;&quot;https://&#39;,@website,&#39;/&#39;)),&#39;&quot;&#39;), Name FROM tblContent INNER JOIN tblContentLanguage ON pkID = fkContentID WHERE linkurl is not null AND fkLanguageBranchID = 1 ORDER BY pkID&amp;nbsp;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;CREATE TABLE #categories(pkID int identity(1,1), catID int, catName varchar(100)); --INSERT INTO #categories(catID, catName) SELECT pkid, CONCAT(&#39;&quot;&#39;,CategoryName,&#39;&quot;&#39;) from tblCategory ORDER BY pkID&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;WITH RecursiveCatQ AS (&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; SELECT pkid, fkParentID, CategoryName, CAST(pkid AS VARCHAR(MAX)) AS recursivePath&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; FROM tblCategory&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; WHERE fkParentID IS NULL&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; UNION ALL&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; SELECT t.pkid, t.fkParentID, t.CategoryName, CAST(r.recursivePath + &#39;-&#39; + CAST(t.pkid AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS HierarchyPath&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; FROM tblCategory t&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; INNER JOIN RecursiveCatQ r&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; ON t.fkParentID = r.pkid&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;INSERT INTO #categories (catID, catName)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SELECT pkid, CONCAT(&#39;&quot;&#39;,CategoryName,&#39;&quot;&#39;)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;FROM RecursiveCatQ&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;ORDER BY recursivePath;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @n = (SELECT COUNT(*) FROM #categories)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @catNames = &#39;,,&#39;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @catIDs = &#39;,,&#39;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;WHILE @i &amp;lt;= @n&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;BEGIN&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; SET @i = @i + 1&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @catNames = CONCAT(@catnames,&#39;,&#39;,(SELECT TOP 1 catName FROM #categories WHERE pkID = @i))&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @catIDs = CONCAT(@catIDs,&#39;,&#39;,(SELECT TOP 1 catID FROM #categories WHERE pkID = @i))&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;END&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;PRINT @catIDs&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;PRINT @catnames&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @i = 0&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @m = (SELECT COUNT(*) FROM #pages)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @n = (SELECT COUNT(*) FROM #categories)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @pageCatRow varchar(1000)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @boolPageCat int&amp;nbsp;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @pID int&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DECLARE @cID int&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;WHILE @i &amp;lt; @m -- pages / rows&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;BEGIN&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @i = @i + 1;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @j = 0&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @pageCatRow = &#39;&#39;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @pageCatRow = (SELECT top 1 CONCAT(pageID,&#39;,&#39;,linkurl,&#39;,&quot;&#39; ,PageName,&#39;&quot;&#39;) FROM #pages WHERE pkID = @i)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;WHILE @j &amp;lt; @n -- categories / columns&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;BEGIN&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @j = @j + 1&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @pid = (SELECT pageID FROM #pages where pkID = @i)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @cid = (SELECT catID FROM #categories where pkid = @j)&amp;nbsp;&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @boolPageCat = (SELECT COUNT(*) FROM tblContentCategory WHERE fkContentID= @pid AND fkCategoryID = @cid)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;SET @pagecatrow = CONCAT(@pageCatRow,&#39;,&#39;,@boolPageCat)&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;END&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;PRINT @pageCatRow&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;END&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&amp;nbsp;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DROP TABLE #pages&lt;/code&gt;&lt;/div&gt;
&lt;div&gt;&lt;code&gt;DROP TABLE #categories&lt;/code&gt;&lt;/div&gt;
&lt;/div&gt;
&lt;p&gt;&lt;br /&gt;This produces something that looks like this...&lt;/p&gt;
&lt;p&gt;&lt;img src=&quot;/link/859e4ac746a8492bbdab0d2ba5557389.aspx&quot; /&gt;&lt;/p&gt;
&lt;p&gt;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&#39;s happening - but from there, they can add 1s and 0s to the content and categories that they want to.&lt;/p&gt;
&lt;p&gt;&lt;img src=&quot;/link/8ac05cce0c4f436ea4e19d4893e8aa85.aspx&quot; /&gt;&lt;/p&gt;
&lt;h2&gt;Push content and category data back into Opti&lt;/h2&gt;
&lt;p&gt;Once everything has been updated, take to Developer mode in Excel and you can use this...&lt;/p&gt;
&lt;p&gt;&lt;code&gt;Sub MakeMeSomeSQL()&lt;/code&gt;&lt;/p&gt;
&lt;p&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; Dim ws As Worksheet&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; Dim lastRow As Long, lastCol As Long&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; Dim i As Long, j As Long&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; Set ws = ThisWorkbook.Worksheets(&quot;Sheet2&quot;)&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp;&amp;nbsp;&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; For i = 1 To lastRow&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp;&amp;nbsp;&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; For j = 4 To lastCol&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;&amp;nbsp;&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; If ws.Cells(i, j).Value = &quot;1&quot; Then&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;&amp;nbsp;&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; Debug.Print (&quot;INSERT INTO tblWorkContentCategory (fkWorkContentID,fkCategoryID,CategoryType,ScopeName) VALUES ((SELECT TOP 1 pkID FROM tblWorkContent WHERE fkContentID = &quot; &amp;amp; ws.Cells(i, 1) &amp;amp; &quot; ORDER BY pkID DESC),&quot; &amp;amp; Left(ws.Cells(1, j), InStr(ws.Cells(1, j), &quot;-&quot;) - 1) &amp;amp; &quot;,0,0)&quot;)&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; Debug.Print (&quot;INSERT INTO tblContentCategory (fkContentID, fkCategoryID, CategoryType, fkLanguageBranchID, ScopeName) VALUES (&quot; &amp;amp; ws.Cells(i, 1) &amp;amp; &quot;,&quot; &amp;amp; Left(ws.Cells(1, j), InStr(ws.Cells(1, j), &quot;-&quot;) - 1) &amp;amp; &quot;,0,&lt;span style=&quot;background-color: rgb(241, 196, 15);&quot;&gt;2&lt;/span&gt;,0)&quot;)&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;&amp;nbsp;&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; End If&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;&amp;nbsp;&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; Next j&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp;&amp;nbsp;&lt;/code&gt;&lt;br /&gt;&lt;code&gt;&amp;nbsp; &amp;nbsp; Next i&lt;/code&gt;&lt;/p&gt;
&lt;p&gt;&lt;code&gt;End Sub&lt;/code&gt;&lt;/p&gt;
&lt;p&gt;You&#39;ll want to ensure that your &lt;span style=&quot;background-color: rgb(241, 196, 15);&quot;&gt;language branch is set&lt;/span&gt;, but the above will create your own SQL to reinsert content and category data, which will look a bit like...&lt;/p&gt;
&lt;p&gt;&lt;img src=&quot;/link/b0c5e707ea504b538c55c04987ecbdea.aspx&quot; /&gt;&lt;/p&gt;
&lt;p&gt;Just make sure to remove all the existing data first before you run the SQL that&#39;s created by the above...&lt;/p&gt;
&lt;p&gt;&lt;code&gt;DELETE FROM tblContentCategory WHERE fkContentID IN (SELECT pkID FROM tblContent &lt;span style=&quot;background-color: rgb(45, 194, 107);&quot;&gt;WHERE fkParentID = 329) -- only delete the data from pages you&#39;ll be reimporting&lt;/span&gt;&lt;/code&gt;&lt;/p&gt;
&lt;p&gt;&lt;code&gt; DELETE FROM tblWorkContentCategory WHERE fkWorkContentID IN (SELECT pkID FROM tblWorkContent WHERE fkContentID IN (SELECT pkID FROM tblContent &lt;span style=&quot;background-color: rgb(45, 194, 107);&quot;&gt;WHERE fkParentID = 329)) -- only delete the data from pages you&#39;ll be reimporting&lt;/span&gt;&lt;/code&gt;&lt;/p&gt;
&lt;p&gt;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.&lt;/p&gt;
&lt;p&gt;&amp;nbsp;&lt;/p&gt;</id><updated>2025-04-07T14:12:34.0000000Z</updated><summary type="html">Blog post</summary></entry> <entry><title>Integrating HubSpot CRM without the MA Connector</title><link href="https://world.optimizely.com/blogs/matt-pallatt-is-not-a-developer/dates/2024/6/integrating-crm-without-the-ma-connector/" /><id>&lt;p&gt;Have HubSpot CRM? Want to push user data into it from Optimizely? Don&amp;rsquo;t have any personalisation requirements with that data? Don&amp;rsquo;t want to pay $800 a month for the Marketing Automation Connector? &lt;a href=&quot;https://nuget.optimizely.com/package/?id=MP.OptiForm2HubSpotPush&quot;&gt;Then I have something that might interest you&lt;/a&gt;.&lt;/p&gt;
&lt;p&gt;Recently we were working with a client who sat very much in the audience segment outlined above &amp;ndash; they have HubSpot, they collect data from their website, they aren&amp;rsquo;t quite at the point where they want to start using any data from HubSpot to deliver personalisation, and they definitely didn&amp;rsquo;t have a budget for Marketing Automation Connector monthly charges.&lt;/p&gt;
&lt;p&gt;Their requirement was for two specific forms &amp;ndash; 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 &amp;ndash; 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.&lt;/p&gt;
&lt;p&gt;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.&lt;/p&gt;
&lt;p&gt;With &lt;a href=&quot;https://github.com/mattpallatt/MP.OptiForm2HubSpotPush?tab=readme-ov-file#mpoptiform2hubspotpush&quot;&gt;very little configuration&lt;/a&gt; (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.&lt;/p&gt;
&lt;p&gt;It works alongside all the existing features of Optimizely Forms &amp;ndash; so you can continue to hold data in Optimizely if you want to use Visitor Groups/Audiences on the data you&amp;rsquo;re collecting, you can use the mail functionality to let users know you have their data (assuming you&amp;rsquo;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 &amp;ndash; simples.&lt;/p&gt;
&lt;p&gt;And there&amp;rsquo;s little reason that the same thing can&amp;rsquo;t be done with Salesforce, Dynamics and other CRM platforms.&lt;/p&gt;
&lt;p&gt;Anyway, it was so simple and easy to use I thought I&amp;rsquo;d carve it off and make it available for anyone who wanted to send their user data to HubSpot &amp;ndash; or extend it to other CRM platforms of their need. So by all means steal, doctor and reuse the code at &lt;a href=&quot;https://github.com/mattpallatt/MP.OptiForm2HubSpotPush&quot;&gt;https://github.com/mattpallatt/MP.OptiForm2HubSpotPush.&lt;/a&gt;&lt;/p&gt;</id><updated>2024-06-27T11:11:41.0000000Z</updated><summary type="html">Blog post</summary></entry></feed>