Marketing Cloud

How I learned SQL in Marketing Cloud (the hard way).

Date published • 16th October 2018

On 23rd October at 10.00am I will be running a webinar around SQL in Marketing Cloud based on the challenges I found as a Marketing Cloud user, and the best practices I found to overcome these. This article highlights how I came to find these challenges, and give you an idea on the key pieces I will be covering in the webinar. Register for the webinar below!

Blocks image

Last year I was hired to run Marketing Cloud platform, this was a very exciting opportunity for me which I could not pass up. The only problem was the little knowledge I had to actually run it at full scale. Knowing it would be tough I went for the challenge. After the first week I realised that I had bitten off more than I can chew. 

The full build was done by a Salesforce Partner that handed it over to end user. Luckily there was a blueprint but no one who actually knew how to work with Marketing Cloud, that was where I came in. There was a need to build complex customer journeys in Journey Builder and maintain overall integrity of Marketing Cloud. Journeys were something I was quite comfortable with from my previous experience, the only difference being that I was supported by a data team which eliminated me from digging deep into SQL.

I realised quite quickly that there is a huge amount of SQL activates in the Automation Studio. One automation in particular that made my jaw drop, 24 steps and each having 5 – 10 SQL activities, it was called Master Segmentation. It takes anywhere between 1 - 2 hours to complete and had no waiting times in-between! I thought to myself surely this cannot be best practice, Marketing Cloud is not designed for it and I was right, it is not.

A few weeks in, it all became clearer, Marketing Cloud was acting as a data warehouse and any possible transactional and what not data was plugged in without any consideration what might happened to it when scaled. My challenge became clearer when Master Segmentation automation and others started to fail regularly. This was mainly due to a reason that SQL activities were failing due to time out. Marketing Cloud runs on Microsoft SQL Server and timeout for SQL activities is 30 minutes. This issue was mainly encountered when there were days with large amount of transactional row data. At this time, I found myself learning more about SQL than I ever thought possible. First, I was splitting activities into smaller ones and later actually optimizing SQL to be more efficient by using GROUP function and CASE WHEN. 

A few months in I was quite comfortable, and the challenge paid off, I learnt to use SQL at the level I was happy with. However, this did not solve the underlying issue was just simply patching it up. There was need for larger project to be undertaken, but that is another story.

Screen Shot 2018-10-15 at 15.32.16

Words by:

Edgar Sobolev, Consultant

If you would like to know more about what we do, we’re happy to answer any questions you may have for us.

Share this article:

View more from the blog and events