The aha moment
In the wise words of Julie Andrews, let’s start at the very beginning, a very good place to start. AI Assist didn’t stem from user research, though it was certainly built for, iterated on, and shaped by our customers.
AI Assist was born during an internal company hackathon event in June of 2023. Side note, if you ever have a few hours to waste, please dare to ask me how I feel about Mode’s hackathon events. Spoiler alert, I’m a pretty big fan. That said, I wasn’t sure how I felt going into this one as we’d been prescribed a subject area to focus on—artificial intelligence.
Ultimately, I joined a group of engineers who were particularly excited about experimenting with LLMs and text-to-SQL workflows. The 48 hours that followed were nothing short of a blur. One of the benefits to having to produce results in such a ridiculously short timeframe is that it forces you to set constraints, follow your gut instincts, and just try the damn thing.
We applied numerous constraints to finish, of course, but I’d argue that the most impactful constraint was the groups’ early decision to focus the solution on our core, analyst audience. Now at first glance, this may not seem like a revolutionary or controversial constraint, but at the time we were in the midst of a multi-year effort to try and expand Mode’s feature set to better support folks beyond the analytics team (more on that in a different case study). And we could’ve just as easily argued that it made more sense to build a SQL generator for, well, non-SQL writers. But instead we followed our instincts, and harkened back to Mode’s OG mantra, “by analysts, for analysts”.
Once we made the decision to hone in on SQL writers, assumptions and focus areas quickly fell into place. One major assumption we made was that analysts would already have a solid understanding of how their data was modeled and organized. This allowed us to punt on trying to solve for really vague, open-ended requests that would require AI to conduct both broad, expensive data interpretation and discovery work while also translating text to SQL. Instead, we decided to laser focus on the translation step and require the user to provide at least one table name to give context on the data they wanted to query. This made the resulting workflow much, much simpler.
The pitch
Our goal was to help analysts (at all experience levels) write SQL more efficiently. But as we started brainstorming, the way we wanted to do it surprised us. We decided to let customers add English phrases directly to their SQL. That way, they could still write more request style prompts. But they could also use natural language to wireframe out a query, kind of like when you’re starting a draft of an outline and vaguely describe what you want to go where. Once you were finished with that outline, we’d leverage AI to translate the combination of SQL and natural language comments into valid syntax, at which point we’d run the completed query and surface the generated results.
With this plan in motion, I quickly got to work on the designs needed to make it happen.
Context switcher to engage this
new mode of query writing
Custom syntax highlighting to differentiate english from SQL, initiated by the bang symbol (!)
Source tab so user could quickly review the AI-generated syntax that was executed and compare it with their original edits
When we finished we had managed to get both a working demo and a short hype video prepared to really sell the dream. We submitted the finished project under the codename FlexQL, as we were especially drawn to the flexibility offered by this “psuedo code” approach. Thankfully, we weren’t the only ones intrigued by the idea, as the entry earned us the two most esteemed Hack Day awards 🏆 — Best in Show and Hacker’s Choice.
It hadn’t occurred to me to do it this way, but in hindsight it seems almost obvious, our cofounder said
Validation and iteration
Hack Day left us with a lot of internal momentum, and shortly after the news broke that we were being acquired by ThoughtSpot, a well known BI player in the data space who was ahead of the game when it came to AI functionality. But despite growing momentum to ride the generative AI wave, stakeholders still weren’t entirely convinced that FlexQL would resonate with our customers (specifically more advanced analysts) and worried there wasn’t a clear path to evolving the UX.
In terms of inspiration, the market had a few options I could look to. There were code-complete solutions for software developers, and conversational chatbots for practically everyone else. But our workflow and idea didn’t really fall neatly into either camp, hence the stakeholder nerves.
To de-risk the project, I conducted customer research to better understand how analysts were working with ChatGPT already, and how they felt AI could make their jobs easier. The thing we heard every analyst say was they were spending the majority of their time in Mode on edits or revisions to the same, or similar, queries. If they could lean on AI to expedite these SQL edits, or even encourage others to attempt them on their own, they could spend more of their time on impactful work and be able to respond to even more questions, unlocking additional insights.
In addition to validating the problem, I also used these sessions to help evolve the prototype and solidify the UX, specifically the mental model and role that natural language would play within our query editor.
Customer reactions to seeing the hack day prototype demo
After getting initial reactions to our FlexQL proof of concept, I closed each session by sharing three design prototypes that teased a different approach to leveraging AI. Each had a shared goal of helping analysts preview and iterate through AI generated SQL more quickly:
AI as on-demand code snippets: in this model, english phrases could act like a variable name or facade for more complex statements that AI would translate behind the scenes. The phrases could potentially remain in your SQL queries indefinitely, allowing less technical users to parse the logic more easily
AI as a collaborative reviewer: this iteration allowed analysts to traverse through AI-code generations as if they were suggested edits in a Google Doc. Though I anticipated this design would feel overwhelming, I was interested in whether users might want to give and receive feedback from AI as if it were another analyst
AI as a side-by-side coding environment: this approach took the most inspiration from code editors, and allowed users to quickly compare the differences between their original edits and any AI generated SQL, before consciously accepting and inserting the updates in place of their own
Ultimately, customers resonated most with the third approach. Analysts felt it gave them a clear path to review and validate any AI generated SQL, well doubling as a teaching tool for their more novice users.
Option 1: AI as on-demand code snippets
Option 2: AI as a collaborative reviewer
Option 3: AI as a side-by-side coding environment
Alignment and sequencing
Shortly after we presented the research findings, the project was officially green-lit and a team was staffed. We were also given a rather ambitious deadline to try and soft launch the MVP by, and settled on a safer feature name we could go to market with (AI Assist).
Despite Nic’s early partnership, this was my first initiative that didn’t have a dedicated PM.
As a result, I got to take on a lot of product management aspects of the project. I led regular working sessions to help align the team on the research findings and discuss what we ought to prioritize before the initial launch.
I also got to dive head first into the world of prompt engineering, as we figured out how exactly to leverage OpenAI’s API to generate SQL, and what meta-data we’d need to send along. Though I have the privilege of working closely with engineering on typical projects, I got to pair even more closely than I normally would, allowing us to work that much more iteratively and collaboratively. 100/10 experience, would do again.
Design explorations
We didn’t have much time to iterate on the MVP after the soft launch goal was announced, but I did explore a handful of UI and copy iterations with the design team. I also dogfooded the heck out of this thing.
A few weeks out ahead of the soft launch, we were able to conduct our first usability tests with customers. This led to a few enhancements and prompt fixes before launch, helped us create valuable customer facing demos and onboarding materials, and enabled us to document more concrete use cases after seeing analysts use it in the wild.
The final product included updated syntax highlighting, a side-by-side interface for users to quickly generate AI output and compare it alongside their edits, a few handy keyboard shortcuts, and 👍🏼 👎🏼 feedback mechanisms for customers to score a given AI generation (with the opportunity to expand after scoring a general as “unhelpful”). These scores gave us training data to work with as we updated the model over time.
MVP workflow:
Write and run your SQL in the editor on the left as you would normally
Incorporate natural language into your edits by adding a special AI comment type via the --! symbol combination
When you’re ready, generate new SQL on the right by clicking the Generate button (Shift + Opt/Alt + N)
When you generate, Mode pulls the currently selected warehouse name, column names & data types for all tables explicitly referenced in your SQL, and sends them and your SQL edits to OpenAI for context
Once the generated code returns, you can compare it with your original code, insert the SQL in place of your original edits (Shift + Opt/Alt + I), or copy and paste manually as needed. Alternatively, you can revise your edits on the left and regenerate
