An analyst's daily work can get repetitive. One day a request from a product manager can sound like:
“Hi Derek, could you please pull the 10 most active customers in Minneapolis? I want to talk to customers in a newer market for insights into a new feature we're thinking about building.”
A few weeks later, a marketing manager might ask:
“Yo, Derek! We're hosting an event in San Diego and want to invite about 500 customers. Could you pull those email addresses for me?”
But it doesn't have to be repetitive: since the SQL queries behind these requests are nearly identical, you can use Mode parameters to add a bit of time-saving magic to your workflow. Writing queries with parameters can help you pass repetitive requests back to requestors and enable them to self-serve queries—with variations—even if they don't know SQL. Less time spent on rote work means more time for the fun, high-impact analyses you were hired to do.
Here's a few quick tips for incorporating parameters into your workflow. We'll cover advanced uses of parameters like looping functions and date filtering in an upcoming post.
How Parameters Work
To the product manager or marketing manager, parameters appear as easy-to-use input forms in the report view.
On the backend, there are two components of each parameter: the tag and the form description. The tag appears in the query and the form description (right below the query) and defines what the user will see.
Example 1: Customer Lookups
One of the most common uses for parameters is to allow support, customer success, and sales teams to look up information about individual customers. Any situation in which you might want to enter a Customer ID and get some information back is a great opportunity to use parameters. You can take these reports a step further by incorporating them into the tools your customer-facing teams use every day.
Here's a simple query that allows a customer success manager to enter a User ID and get back the user's most recent events by device. While we're keeping things simple for this post, it's important to note that your query can be as complex as needed. Regardless of query complexity on the backend, the report still looks like one simple “User ID” field to the customer success manager.
Open this query in a new tab to see how it looks from the report view. Notice that the URL takes you to the most recent run:
https://modeanalytics.com/emily/reports/526d66580dc4
By adding run=now to the URL, you can tell Mode to run the query on page load:
https://modeanalytics.com/emily/reports/526d66580dc4?run=now
It gets pretty magical when you start passing in query parameters and the run=now command:
https://modeanalytics.com/emily/reports/526d66580dc4?param_user_id=10899&run=now
If you've mapped your user_id field into your support or CRM software, it's pretty easy to auto-generate these URLs on account records or support tickets. For example, CrowdFlower's community support team incorporated parameterized query links into Desk.com to pull up details about the contributor's last project. With automatic access to relevant information, team members are able to respond up to 10x faster.
At Zinc they've added links into their Salesforce Account object so salespeople can quickly reference a customer's in-product activity.
In the above screenshot, you can see that a link has been added to the Account object, which pulls the OrganizationAccountID from the above field and populates a link to a Mode report.
Here's how to build the field in Salesforce:
- Make sure you have a field with an ID
- Create a field of the type "Formula (text)." Name it something sensible like "Mode Report Link."
- Paste in the following formula and adjust the values so they fit your Salesforce fields and direct to the correct report:
IF( OrganizationAccountID__c = 0,
NULL,
HYPERLINK("https://modeanalytics.com/modeanalytics/reports/526d66580dc4?param_organization_id="&TEXT(OrganizationAccountID__c)&"&run=now","Mode Org Report"))Note: This works when your ID is a numerical column. If it's a string, then you will need to modify the first line slightly.
Example 2: Email Lists
Corralling a list of email addresses for an email campaign can be a huge pain. In many cases, requests can get complex. For example, a list might include all users who:
- Have not logged in for 30 days
- Used feature X but not feature Y
- Last used the version of the Android app you no longer plan to support (and haven't since updated or switched to iOS)
- Speak English
All this while also excluding users who have opted out, are currently in the sales process, and are members of the press.
There are a lot of marketing automation tools out there, many of which profess to solve this problem. I've never seen an implementation that didn't also require marketers to do regular one-off pulls of email lists and load them manually.
API integrations offer robust solutions to this problem, but they come at a high cost. Automating the SQL queries that pull these lists can also be an effective solution with a much lower cost. Parameterized queries also help reduce a lot of human error: since marketers will be re-using the same query with only slight variations, you can be confident that it's excluding the appropriate users.
Here's an example of how you might write the query with parameters (check out the report in Mode, here):
Helpful Resources
We use the Liquid template language to power our parameters. Shopify originally developed Liquid to enable its users to customize their online stores but the syntax is so powerful and easy to understand that it's been adopted widely across the open source software community.
If you're brand new to Liquid, you might want to start with this video or take a look at our parameter help docs.
These are just a few examples of how Mode customers are using parameters. We'll follow up soon with more advanced functions and other ideas for using parameters to save even more time in your workflow.
Questions? We're always here to help via email or Intercom.
