Author: Franz Malten Buemann

Last Updated on March 21, 2022 by Rakesh Gupta Big Idea or Enduring Question: How to sign up for the product-specific developer orgs? Objectives: After reading this blog, you’ll be able to: Sign up for Financial Service Cloud developer org Sign up for Salesforce CPQ developer org Sign up for Tableau
The post Sign up for Product Specific Free Developer Edition Org appeared first on Automation Champion.

Last Updated on March 21, 2022 by Rakesh Gupta Big Idea or Enduring Question: How to track your organization daily maximum and the remaining allocation limits? Objectives: After reading this blog, you’ll be able to: Understand maximum and remaining allocated limit Track all limits in once place via REST API
The post Easiest Way to View Organization Daily Limits appeared first on Automation Champion.

submitted by /u/AlistairVigier [link] [comments]

Numerous resources talk about how to keep remote employees engaged — and that’s hundreds more than there were before much of the global workforce went remote in 2020.In 2018, I shared a few of our People team initiatives focused on internal community-building, but a lot has changed in four years, and we have new activities on the horizon for 2022.In addition to the ever-evolving landscape of remote work in a time of a pandemic, we instituted a four-day workweek in 2020, which had an impact on the amount non-work things we wanted to ask of our team. We just switched from quarterly to monthly All Hands, which are recorded for anyone who cannot attend.Our team engagement scores, which I measure with a survey every six months, have decreased from the beginning of 2021. Decreasing engagement scores is something tied to many factors including team turnover, product direction, and external influences. One thing that has been highlighted in our surveys is the reduction in team-building events like casual Zoom hangouts, guest speakers, and in-person events.And we’re not alone. In our 2022 State of Remote Work, a small majority (52%) of folks who started working remotely due to COVID-19 say they feel less connected to their coworkersIn 2022, we’re diving back into more intentional team-building both asynchronously, synchronously, plus we’re planning the occasional in-person meetup where available. Here’s how we’re approaching all of these events and all of our current initiatives.Culture and connection: Who is responsible?Culture is a continual evolution at any company. While every team and individual contributes to the broader culture, the People team at Buffer is responsible for helping facilitate the processes and events for connection and engagement to thrive.Many other pieces feed into the larger, nebulous thing that is culture: values, company goals, events, internal communication, managers and so much more. That said, events and teammate connection are two of the main areas of focus for me going into 2022.Last year, the important but never-as-urgent work of team-building easily fell to the bottom of the list in the scheme of other projects.  Now, we’ve intentionally re-shifted a few of the larger roles within the People-Finance team to have more clear ownership. We hired a new teammate to focus completely on recruitment and hiring (hi, Janet!) and have optimized the existing strengths of our broader People-Finance team, to make my singular focus on culture and engagement.This was the first strategic decision we made to address our overall team engagement: someone has to be the designated “driver” of a more values-based culture of connection and engagement. Without clear ownership and burden on one or more individuals, the team can flounder.Remote workplace event philosophyIn 2022, we’re moving forward with initiatives of all categories: synchronous, asynchronous, in-person, and remote, plus, we’re getting more creative with how we hold those events and are looking to be as inclusive as possible of all timezones and personalities.We tend to approach projects with a mentality that there is no one-size-fits-all. Some of the things we try will work for some and not for others. Here’s how we break down event categories along with expectations are for attendance. Note: Our required events and timezone guidance are in flux as we experiment with different approaches.A screenshot of our internal event guidelinesBeing explicit about the attendance expectations helps to alleviate confusion and ensures we don’t ask too much of teammates — especially with a four-day workweek.Most of the initiatives we’re going to do in 2022 fall into the “extracurricular” category, as we have heard that most people can get their work done in four days, but that for about a quarter of the company, they regularly have to work a bit over the four days to get their work accomplished. We want to provide the opportunity to engage with the rest of the team, without adding additional work to their plates.The tried-and-true initiativesI’m the broken record when it comes to this: Pair calls and masterminds are invaluable. If teammates can make it work, we ask them to first give these a chance.Pair calls (casual, one-on-one chats)We love Donut for automating our rotating weekly one-on-one chats with a different teammate.Donut artificially creates those moments that would happen naturally if we were all located in the same office: You’d see someone in the hall, stop for 10-20 minutes, and catch up about life. Those moments don’t happen in a remote environment. You have to make them happen, and you have to create an expectation of team-wide participation.In the past few years, usage of this program has ebbed and flowed — and the general sentiment is that teammates love it but sometimes opt-out during busier periods of work expectations. We ask all new teammates to participate in this program, especially for the first 90 days of their onboarding.For 2022, we put all teammates into our #people-pairs channel and allowed them to opt-out individually. Pair calls take 20 to 30 minutes and are meant to be very light-weight and yet, they add up over time in a really powerful way.Masterminds (deeper connections)Masterminds are meant to create a deep bond with a teammate you don’t work with directly, but hopefully have some things in common — it’s a space to share successes, failures, life events. Some matches work great, others might take some time. Some partnerships last well past tenure at Buffer!Teammates are welcome to opt-out any time of masterminds for any reason, but we’ve seen clear data to show that when mastermind pairings work, it works very well. Here’s some of the feedback we’ve received in the past:“I’ve found that opening up with my buddy has helped me open up with my managers.”“Talking to someone outside of my team is really valuable for me.”“I think it’s been really helpful to have a designated space to share and grow with another Bufferoo who isn’t my manager or someone on my team.”“A great opportunity to connect with teammates and dive deeper into conversations that might not happen in a typical pair call.”New cultural initiatives for 2022SynchronousBuffer TED talksOne of our first experiments for 2022 is monthly TED-style/lunch-and-learn talks from amongst our teammates! These will be 20-minutes long and then we’ll have 10-20 minutes of optional hanging out after. We might also bring in external speakers for these. Each talk will then be shared with a recording and a space for comments for the conversation to continue asynchronously. We hope to build out a huge library of teammate talks.Specific groupsSome of our most successful team initiatives have been around specific, special-interest groups with a clear topic. Whereas whenever we have broad, open-invitation events, we see a very poor turnout. Book clubs and side-project discussions are two groups that we’ll encourage even more this year. These are two of the most active Slack channels we have, so it’s clear where the most passionate teammate conversations are happening, and beneficial to move those to a Zoom call.Monthly All-HandsWe’re moving to monthly All-Hands (compared to our quarterly cadence last year), which allows for more frequent touch-points and chances to see teammates on Zoom. Some of the most popular features of All-Hands meetings include small-group breakouts and unique teammate speakers.We’re planning to invite more external speakers, especially to support our DEI goals and initiatives. We’ll share a lot more on this approach in future posts.AsynchronousSlack as our team water coolerSlack continues to be our virtual water cooler and we have seen great success with recurring prompts to spark conversation in that channel.Weekly Slack prompts (that we’ve tried before and will do again!)In our water cooler channel, we use Donut’s feature to send out regular questionsIn our gratitude channel, we might ask What are you grateful for today?In our books channel, we might ask What are you currently reading? What book has made the most impact on your life?In a self-improvement channel, we might ask What are you working on about yourself lately?Donut prompt in SlackFor November 2021, we tried out a daily gratitude prompt (inspired by Passion Planner’s 21-day gratitude challenge) and ended up with really great discussions over the days we posted this in November.Screenshot from the gratitude challengeInternal podcast episodesAnother asynchronous initiative we are leveraging this year: regular, internal podcast episodes. We’re launching this in a lightweight, quick way, simply recording some of the existing conversations that already happen every week. We might iterate on a more polished version in the future, but for now, we want to share the latest thoughts on our exec’s team’s mind or share a profile of a teammate.Quick questions with teammatesIn the same theme as using audio and other asynchronous formats, we’ve done weekly “Quick Question” video interviews with new teammates (and some longer-tenured teammates!) in our weekly company newsletter.Here’s a sample with Dave and Lexi from our Advocacy team!0:00/1×Here’s Lexi and Dave from Buffer’s advocacy team answering some quick questionsIn-PersonRegional meetupsWhile this is very much in the early stages, we are exploring the model of regionally-based meetups and encouraging local gatherings for our company in 2022. This is in place of a company-wide, one-location meetup, which we feel is still a bit too difficult and unpredictable given international travel considerations.We’ll share a lot more coming up as this takes shape, but it’s a piece of the larger engagement puzzle as we look ahead.Over to YouWhen it comes to keeping a team of nearly 90 teammates spread out across the globe connected and engaged, there isn’t one solution or one easy approach. It takes time, intentionality, and team accountability. What have you tried with your team, or which of these would you like to learn more about? Send us a tweet!

How to Query a SQL Database:

Make sure that you have a database management application (ex. MySQL Workbench, Sequel Pro).
If not, download a database management application and work with your company to connect your database.
Understand your database and its hierarhcy.
Find out which fields are in your tables.
Begin writing a SQL query to pull your desired data.

Ever heard of SQL? You may have heard about it in the context of data analysis, but never thought it would apply to you as a marketer. Or, you may have thought, “That’s for the advanced data users. I could never do that.”
Well, you couldn’t be more wrong! The most successful marketers are data-driven, and one of the most important parts of being data-driven is collecting data from databases quickly. SQL is the most popular tool out there for doing just that.
If your company already stores data in a database, you may need to learn SQL to access the data. But not to worry — you’re in the right place to get started. Let’s jump right in.

Why Use SQL?
SQL (often pronounced like “sequel”) stands for Structured Query Language, and it’s used when companies have a ton of data that they want to manipulate. The beauty of SQL is that anyone working at a company that stores data in a relational database can use it. (And chances are, yours does.)
For example, if you work for a software company and want to pull usage data on your customers, you can do that with SQL. If you’re helping develop a website for an ecommerce company that has data about customer purchases, you can use SQL to find out which customers are purchasing which products. Of course, these are just a few of many possible applications.
Think about it this way: Have you ever opened a very large data set in Excel, only for your computer to freeze or even shut down? SQL allows you to access only certain parts of your data at a time so you don’t have to download all the data into a CSV, manipulate it, and possibly overload Excel. In other words, SQL takes care of the data analysis that you may be used to doing in Excel.
How to Write Simple SQL Queries
Before we begin, make sure you have a database management application that will allow you to pull data from your database. Some options include MySQL or Sequel Pro.
Start by downloading one of these options, then talk to your company’s IT department about how to connect to your database. The option you choose will depend on your product’s back end, so check with your product team to make sure you select the correct one.
Understand the hierarchy of your database
Next, it’s important to become accustomed to your database and its hierarchy. If you have multiple databases of data, you’ll need to hone in on the location of the data you want to work with.
For example, let’s pretend we’re working with multiple databases about people in the United States. Enter the query “SHOW DATABASES;”. The results may show that you have a couple of databases for different locations, including one for New England.
Within your database, you’ll have different tables containing the data you want to work with. Using the same example above, let’s say we want to find out which information is contained in one of the databases. If we use the query “SHOW TABLES in NewEngland;”, we’ll find that we have tables for each state in New England: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland, and people_vermont.
Finally, you need to find out which fields are in the tables. Fields are the specific pieces of data that you can pull from your database. For example, if you want to pull someone’s address, the field name may not just be “address” — it may be separated into address_city, address_state, address_zip. In order to figure this out, use the query “Describe people_massachusetts;”. This provides a list of all of the data that you can pull using SQL.
Let’s do a quick review of the hierarchy using our New England example:

Our database is: NewEngland.
Our tables within that database are: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland, and people_vermont.
Our fields within the people_massachusetts table include: address_city, address_state, address_zip, hair_color, age, first_name, and last_name.

Now, let’s write some simple SQL queries to pull data from our NewEngland database.
Basic SQL Queries
To learn how to write a SQL query, let’s use the following example:
Who are the people who have red hair in Massachusetts and were born in 2003 organized in alphabetical order?
SELECT
SELECT chooses the fields that you want displayed in your chart. This is the specific piece of information that you want to pull from your database. In the example above, we want to find the people who fit the rest of the criteria.
Here is our SQL query:
SELECT
     first_name,
     last_name
;
FROM
FROM pinpoints the table that you want to pull the data from. In the earlier section, we learned that there were six tables for each of the six states in New England: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland, and people_vermont. Because we’re looking for people in Massachusetts specifically, we’ll pull data from that specific table.
Here is our SQL query:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
;
WHERE
WHERE allows you to filter a query to be more specific. In our example, we want to filter our query to include only people with red hair who were born in 2003. Let’s start with the red hair filter.
Here is our SQL query:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE
     hair_color = ‘red’
;
hair_color could have been part of your initial SELECT statement if you’d wanted to look at all of the people in Massachusetts along with their hair color. But if you want to filter to see only people with red hair, you can do so with a WHERE statement.
BETWEEN
Besides equals (=), BETWEEN is another operator you can use for conditional queries. A BETWEEN statement is true for values that fall between the specified minimum and maximum values.
In our case, we can use BETWEEN to pull records from a specific year, like 2003. Here’s the query:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE
     birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
;
AND
AND allows you to add additional criteria to your WHERE statement. Remember, we want to filter by people who had red hair in addition to people who were born in 2003. Since our WHERE statement is taken up by the red hair criteria, how can we filter by a specific year of birth as well?
That’s where the AND statement comes in. In this case, the AND statement is a date property — but it doesn’t necessarily have to be. (Note: Check the format of your dates with your product team to make sure they are in the correct format.)
Here is our SQL query:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE
     hair_color = ‘red’
AND
     birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
;
OR
OR can also be used with a WHERE statement. With AND, both conditions must be true to appear in results (e.g., hair color must be red and must be born in 2003). With OR, either condition must be true to appear in results (e.g., hair color must be red or must be born in 2003).
Here’s what an OR statement looks like in action:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE
     hair_color = ‘red’
OR
     birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
;
NOT
NOT is used in a WHERE statement to display values in which the specified condition is untrue. If we wanted to pull up all Massachusetts residents without red hair, we can use the following query:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE NOT
     hair_color = ‘red’
;
ORDER BY
Calculations and organization also can be done within a query. That’s where the ORDER BY and GROUP BY functions come in. First, we’ll look at our SQL queries with the ORDER BY and then GROUP BY functions. Then, we’ll take a brief look at the difference between the two.
An ORDER BY clause allows you to sort by any of the fields that you have specified in the SELECT statement. In this case, let’s order by last name.
Here is our SQL query:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE
     hair_color = ‘red’
AND
     birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
ORDER BY
     last_name
;
GROUP BY
GROUP BY is similar to ORDER BY, but aggregates data that has similarities. For example, if you have any duplicates in your data, you can use GROUP BY to count the number of duplicates in your fields.
Here is your SQL query:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE
     hair_color = ‘red’
AND
     birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31′
GROUP BY
     last_name
;
ORDER BY VS. GROUP BY
To show the difference between an ORDER BY statement and a GROUP BY statement, let’s step outside our Massachusetts example briefly to look at a very simple dataset. Below is a list of four employees’ ID numbers and names.

If we were to use an ORDER BY statement on this list, the names of the employees would get sorted in alphabetical order. The result would look like this:

If we were to use a GROUP BY statement instead, the employees would be counted based on the number of times they appeared in the initial table. Note that Peter appeared twice in the initial table, so the result would look like this:

With me so far? Okay, let’s return to the SQL query we’ve been creating about red-haired people in Massachusetts who were born in 2003.
LIMIT
Depending on the amount of data you have in your database, it may take a long time to run your queries. This can be frustrating, especially if you’ve made an error in your query and now need to wait before continuing. If you want to test a query, the LIMIT function lets you limit the number of results you get.
For example, if we suspect there are thousands of people who have red hair in Massachusetts, we may want to test out our query using LIMIT before we run it in full to make sure we’re getting the information we want. Let’s say, for instance, we only want to see the first 100 people in our result.
Here is our SQL query:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE
     hair_color = ‘red’
AND
     birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
ORDER BY
     last_name
LIMIT
     100
;
INSERT INTO
In addition to retrieving information from a relational database, SQL can also be used to modify the contents of a database. Of course, you’ll need permissions to make changes to your company’s data. But, in case you’re ever in charge of managing the contents of a database, we’ll share some queries you should know.
First is the INSERT INTO statement, which is for putting new values into your database. If we want to add a new person to the Massachusetts table, we can do so by first providing the name of the table we want to modify, and the fields within the table we want to add to. Next, we write VALUE with each respective value we want to add.
Here’s what that query could look like:
INSERT INTO
  people_massachusetts (address_city, address_state, address_zip, hair_color, age, first_name, last_name)
VALUES
  (Cambridge, Massachusetts, 02139, blonde, 32, Jane, Doe)
;
Alternatively, if you are adding a value to every field in the table, you don’t need to specify fields. The values will be added to columns in the order that they are listed in the query.
INSERT INTO
  people_massachusetts
VALUES
  (Cambridge, Massachusetts, 02139, blonde, 32, Jane, Doe)
;
If you only want to add values to specific fields, you must specify these fields. Say we only want to insert a record with first_name, last_name, and address_state — we can use the following query:
INSERT INTO
  people_massachusetts (first_name, last_name, address_state)
VALUES
  (Jane, Doe, Massachusetts)
;
UPDATE
If you want to replace existing values in your database with different values, you can use UPDATE. What if, for example, someone is recorded in the database as having red hair when they actually have brown hair? We can update this record with UPDATE and WHERE statements:
UPDATE
  people_massachusetts
SET
  hair_color = ‘brown’
WHERE
  first_name = ‘Jane’
AND
  last_name = ‘Doe’
;
Or, say there’s a problem in your table where some values for “address_state” appear as “Massachusetts” and others appear as “MA”. To change all instances of “MA” to “Massachusetts” we can use a simple query and update multiple records at once:
UPDATE
  people_massachusetts
SET
  address_state = ‘Massachusetts’
WHERE
   address_state = MA
;
Be careful when using UPDATE. If you don’t specify which records to change with a WHERE statement, you’ll change all values in the table.
DELETE
DELETE removes records from your table. Like with UPDATE, be sure to include a WHERE statement, so you don’t accidentally delete your entire table.
Or, if we happened to find several records in our people_massachusetts table who actually lived in Maine, we can delete these entries quickly by targeting the address_state field, like so:
DELETE FROM
  people_massachusetts
WHERE
  address_state = ‘maine’
;
Bonus: Advanced SQL Tips
Now that you’ve learned how to create a simple SQL query, let’s discuss some other tricks that you can use to take your queries up a notch, starting with the asterisk.
* (asterisk)
When you add an asterisk character to your SQL query, it tells the query that you want to include all the columns of data in your results.
In the Massachusetts example we’ve been using, we’ve only had two column names: first_name and last_name. But let’s say we had 15 columns of data that we want to see in our results — it would be a pain to type all 15 column names in the SELECT statement. Instead, if you replace the names of those columns with an asterisk, the query will know to pull all of the columns into the results.
Here’s what the SQL query would look like:
SELECT
     *
FROM
     people_massachusetts
WHERE
     hair_color = ‘red’
AND
     birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
ORDER BY
     last_name
LIMIT
     100
;
% (percent symbol)
The percent symbol is a wildcard character, meaning it can represent one or more characters in a database value. Wildcard characters are helpful for locating records that share common characters. They are typically used with the LIKE operator to find a pattern in the data.
For instance, if we wanted to get the names of every person in our table whose zip code begins with “02”, we can write this query:
SELECT
     first_name,
     last_name
WHERE
  address_zip LIKE ‘02%’
;
Here, “%” stands in for any group of digits that follow “02”, so this query turns up any record with a value for address_zip that begins with “02”.
LAST 30 DAYS
Once I started using SQL regularly, I found that one of my go-to queries involved trying to find which people took an action or fulfilled a certain set of criteria within the last 30 days.
Let’s pretend today is December 1, 2021. You could create these parameters by making the birth_date span between November 1, 2021 and November 30, 2021. That SQL query would look like this:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE
     hair_color = ‘red’
AND
     birth_date BETWEEN ‘2021-11-01’ AND ‘2021-11-30’
ORDER BY
     last_name
LIMIT
     100
;
But, that would require thinking about which dates cover the last 30 days, and you’d have to update this query constantly.
Instead, to make the dates automatically span the last 30 days no matter which day it is, you can type this under AND: birth_date >= (DATE_SUB(CURDATE(),INTERVAL 30))
(Note: You’ll want to double-check this syntax with your product team because it may differ based on the software you use to pull your SQL queries.)
Your full SQL query would therefore look like this:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
WHERE
     hair_color = ‘red’
AND
     birth_date >= (DATE_SUB(CURDATE(),INTERVAL 30))
ORDER BY
     last_name
LIMIT
     100
;
COUNT
In some cases, you may want to count the number of times that a criterion of a field appears. For example, let’s say you want to count the number of times the different hair colors appear for the people you are tallying up from Massachusetts. In this case, COUNT will come in handy so you don’t have to manually add up the number of people who have different hair colors or export that information to Excel.
Here’s what that SQL query would look like:
SELECT
     hair_color,
     COUNT(hair_color)
FROM
     people_massachusetts
AND
     birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31′
GROUP BY
     hair_color
;
AVG
AVG calculates the average of an attribute in the results of your query, excluding NULL values (empty). In our example, we could use AVG to calculate the average age of Massachusetts residents in our query.
Here’s what our SQL query could look like:
SELECT
  AVG(age)
FROM
  people_massachusetts
;
SUM
SUM is another simple calculation you can do in SQL. It calculates the total value of all attributes from your query. So, if we wanted to add up all the ages of Massachusetts residents, we can use this query:
SELECT
  SUM(age)
FROM
  people_massachusetts
;
MIN and MAX
MIN and MAX are two SQL functions that give you the smallest and largest values of a given field. We can use it to identify the oldest and youngest members of our Massachusetts table:
This query will give us the record of the oldest:
SELECT
  MIN(age)
FROM
  people_massachusetts
;
And this query gives us the oldest:
SELECT
  MAX(age)
FROM
  people_massachusetts
;
JOIN
There may be a time when you need to access information from two different tables in one SQL query. In SQL, you can use a JOIN clause to do this.
(For those familiar with Excel formulas, this is similar to using the VLOOKUP formula when you need to combine information from two different sheets in Excel.)
Let’s say we have one table that has data of all Massachusetts residents’ user IDs and their birthdates. In addition, we have an entirely separate table containing all Massachusetts residents’ user IDs and their hair color.
If we want to figure out the hair color of Massachusetts residents born in the year 2003, we’d need to access information from both tables and combine them. This works because both tables share a matching column: user IDs.
Because we’re calling out fields from two different tables, our SELECT statement is also going to change slightly. Instead of just listing out the fields we want to include in our results, we’ll need to specify which table they’re coming from. (Note: The asterisk function may come in handy here so your query includes both tables in your results.)
To specify a field from a specific table, all we have to do is combine the name of the table with the name of the field. For example, our SELECT statement would say “table.field” — with the period separating the table name and the field name.
We’re also assuming a few things in this case:

The Massachusetts birthdate table includes the following fields: first_name, last_name, user_id, birthdate
The Massachusetts hair color table includes the following fields: user_id, hair_color

Your SQL query would therefore look like:
SELECT
     birthdate_massachusetts.first_name,
     birthdate_massachusetts.last_name
FROM
     birthdate_massachusetts JOIN haircolor_massachusetts USING (user_id)
WHERE
     hair_color = ‘red’
AND
     birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
ORDER BY
     last_name
;
This query would join the two tables using the field “user_id” which appears in both the birthdate_massachusetts table and the haircolor_massachusetts table. You’re then able to see a table of people born in 2003 who have red hair.
CASE
Use a CASE statement when you want to return different results to your query based on which condition is met. Conditions are evaluated in order. Once a condition is met, the corresponding result is returned and all following conditions are skipped over.
You can include an ELSE condition at the end in case no conditions are met. Without an ELSE, the query will return NULL if no conditions are met.
Here’s an example of using CASE to return a string based on the query:
SELECT
     first_name,
     last_name
FROM
     people_massachusetts
CASE
  WHEN hair_color = ‘brown’ THEN ‘This person has brown hair.’
  WHEN hair_color = ‘blonde’ THEN ‘This person has blonde hair.’
  WHEN hair_color = ‘red’ THEN ‘This person has red hair.’
  ELSE ‘Hair color not known.’
END
;
Basic SQL Queries Marketers Should Know
Congratulations. you’re ready to run your own SQL queries! While there’s a lot more you can do with SQL, I hope you found this overview of the basics helpful so you can get your hands dirty. With a strong foundation of the basics, you’ll be able to navigate SQL better and work toward some of the more complex examples.
Editor’s note: This post was originally published in March 25 and has been updated for comprehensiveness.

If you’ve ever wondered how designers at Apple defined every little element in iOS as they were building it, then you’re in the right place.
As technology is constantly evolving, web design continues to become more formalized. Web designers and developers need to create code that can translate seamlessly from PC to mobile devices, make easy to understand site navigation, and innovate other site capabilities — these are all elements that companies standardize in digital style guides.
Digital style guides have become more useful to a brand’s overall image and memorability on the web because they set the expectations and standards for company web display. They’re especially important for websites and products that need to produce top-notch user experiences.
In this post, we’ll dive into what digital style guides are in detail and show you some impressive examples from famous companies that have done them well.

This type of style guide is to be treated as a manual that sets design standards for a company’s digital presence. Its key purpose is to create a universal design style for the brand and ensure consistency across all channels and mediums, where you establish your logo, color palette, typography, imagery guidelines, and so on.
Unlike brand style guides that encapsulate a company’s logo, mission statement, buyer personas and tone of voice, web design style guides are centered on digital presentation like UX/UI.
But, as a UX designer myself, I’ve always been curious, what can you find in the digital style guides of influential companies like Apple, Google, and Starbucks?
Believe it or not, a lot of companies make this information publicly available — they just don’t make it very easy to find. So, every time that I stumble across one, I bookmark it. Here are some of the best ones that I’ve found so far.
Examples of Awesome Digital Style Guides
1. Apple iOS
Apple’s style guide is especially interesting because it details how to design an entire operating system. Monterey, one of the latest versions of Apple’s OS X, has a more simplified user interface than its predecessor, Yosemite. Apple demonstrates this subtle-yet-palpable distinction with really nice graphical comparisons and then goes on to talk about the rationale behind every single aspect of the operating system’s design. It gives you a window into the minds of the designers.

2. Google: Material Design
Google pioneered a design style called Material Design, which exists as a hybrid between Skeuomorphic Design (gradients, textures, light elements) and Flat Design (simple, colorful, geometrical.) In doing this, they combined the benefits associated with each design style, while avoiding the drawbacks.
Because Google has been practicing Material Design for a few years now, you’ve probably already interacted with it on a daily basis — Google Calendar app, anyone? This style guide details exactly what Material Design is and how Google uses it. And I have to say that it is, by far, one of the best style guides that I’ve ever come across.

3. Starbucks
This is one of the most minimalistic style guides that I’ve seen — and yet, it houses a ton of useful information. It places a heavy emphasis on code and you can tell that it was built by developers, for developers. It lacks brand-related elements, so it walks the line between a website style guide and code library.

4. Atlassian
The product suite that Atlassian designs for is gigantic — so, naturally, they have a gigantic style guide. From foundational elements (like color palette and typography) to components (like tables and tooltips) to a full-blown pattern library, this guide has just about everything that you would expect from a product of this size.
Perhaps best of all, the rationale behind the entire style guide is summed up in three deceptively simple terms on the home page.

5. Mozilla
This digital style guide is primarily concerned with branding and communications. But with Mozilla taking a “privacy and open web” approach lately, it’s cool to see how they reflect this in their design.
Mozilla’s homepage also does a great job of outlining how its UX/UI is supposed to be accessible to people with visual impairments or disabilities — something inclusive and necessary as technology becomes more innovative.

6. Buffer
Buffer’s style guide is small and concise, going from grid through modals all in one place. It’s a friendly reminder that your digital style guide doesn’t have to be flashy if it communicates all the right points. Companies looking for somewhere to begin can take notes from Buffer’s simplistic style guide components and build their own from there.

7. Yelp
If you’re looking for a solid example of a website style guide, Yelp’s got that covered. Not only is it thorough, but it explains its Atomic Design system as a cookbook, and divides site elements as ingredients contributing to a dish.
This thing has it all: typography, layout, forms, containers, navigation, and code snippets for each piece. They do a great job of explaining what each element is, where it should be used, and how it should be implemented.

8. GOV.UK
England’s government services website has been widely heralded as a prime example of high-quality UX. Why? Because it boasts a simple and easy-to-use design that accommodates excessive amounts of information.
If you’re interested in what makes up a truly clean and effective design (hint: it usually starts with strong color usage, typography, and spacing), then GOV.UK’s style guide is worth taking a close look at. Much like the site, it’s very simple but very informative.

9. DeviantArt
The new DeviantArt style guide is unique because it’s more than just a guide — it’s an experience. It tells a story and leverages bold, full-width visuals to immerse the user in the emotional experience of the DeviantArt brand. That being said, it’s strictly a branding style guide, so only items like color and typography are covered.

10. Disqus
Color, icons, typography, and logo … Disqus keeps it short and sweet with this guide. But it’s all presented in a very nice, organized manner. This guide could be used as a great example for “where to start” when creating a style guide of your own, as it hits all of the fundamentals.

Feeling Inspired to Make Your Own Guide?
Now it’s your turn. By leveraging a digital style guide in your company, you can communicate your brand’s design language to internal designers, agencies, advertising partners, and even customers.
Start with the basic foundational elements (color, typography, logo, imagery), add some usage guidelines (“do and don’t”), and even incorporate some web components if you need to (modules, templates, code snippets. Use examples from other companies to learn from the best. Your team will be cranking out consistent designs in no time.

How consumers make purchasing decisions has changed greatly in just a few months. For instance, the pandemic has shifted some buyers more online.

In recent years, there has also been a push for more transparent and sustainable practices in brands. But what else is there? Well, that’s what we’re covering here.
In this article, you’ll learn some interesting statistics regarding consumer behavior that you should know in 2022 and what that means for your marketing strategy.
1. Consumers buy more online.
According to a 2022 GWI Commerce Report, there has been a 36% increase in the number of Americans doing most of their household or grocery shopping online.
During the 2021 holiday season, 46% of consumers shopped exclusively online according to a Jungle Scout report.
Why? The top reasons are lower prices, low-cost shipping, and convenience.
Back in 2020, NRF’s Winter 2020 Consumer View reported that 83% of shoppers say convenience while shopping is more important compared to five years ago. That was before the pandemic, so that percentage has likely increased.

Image Source
This shift has forced some brick-and-mortar stores to consider how to implement an ecommerce strategy, whether it’s launching their own online stores or partnering with third-party platforms.
There’s also a bigger need for a robust online presence. While this was always recommended for brands, nowadays, it’s vital for their long-term growth.
2. Reviews and user-generated content are more influential than ever.
Today, it seems like everyone is talking about influencer marketing.
When we surveyed over 1,000 global marketers in 2021, 61% of B2C marketers said they planned to increase their investment in influencer marketing, compared to only 44% in UGC.
However, some studies suggest that the opposite strategy may be more effective.

According to a 2021 Stackla report, UGC is 8.7 times more impactful than influencer content and 6.6 times more than branded content.
In fact, when it comes to sourcing gift ideas on social media, the Jungle Scout revealed that 56% of consumers are influenced by posts shared by friends and family – more than branded content and ads.
From a marketing perspective, this is a signal to brands that consumers want to hear more from other buyers. Consider implementing this into your social strategy by sharing customer reviews and promoting UGC through giveaways and other promotional tactics.
While it’s a great brand discovery tool, it also works well to reel in consumers at the consideration stage of the buyer’s journey.
3. Consumers seek personalization.
According to the Stackla report, 70 percent of consumers say it’s important for brands to offer a personalized experience – up from 67 percent in 2019.
HubSpot Blog Research from 2021 revealed that 53% of marketers leverage message personalization in their email marketing strategy. Of that percentage, 72% say it’s been one of the most effective tactics, more than subscriber segmentation, email automation, and dynamic content.
However, there are many more ways to personalize a user’s experience beyond personalization tokens – from product recommendations and usage recaps (think Spotify’s annual “UnWrapped” campaign) to smart content and 360 customer views.
With so many channels to reach audiences, marketers have a wealth of options when it comes to personalization. It’s just a matter of narrowing down the ones that offer the best ROI.

4. Gen Z leads the charge in social buying.
In May 2021, eMarketer found that Gen Zers are more likely to engage in social commerce than older generations.
Their report revealed that roughly 56% of consumers between 18 and 24 years old and 47.5% of 14- to 17-year olds have made at least one purchase on a social platform.
These figures are considerably higher than other generations, with only a third of consumers between 45 to 65 years of age making at least one purchase on social media in the year.
Which platforms is Gen Z mostly on? TikTok and Instagram.
A 2022 GWI Gen Z report found that 46% of Gen Zers use TikTok to find information about products and brands while 69% use Instagram.
This is likely because Instagram has slowly transitioned to an ecommerce platform with advanced shopping tools while TikTok has kept its focus as a short-form video platform.
So, what are Gen Z buyers looking for? On social media, the GWI report found that most are looking for content that’s:

Lighthearted and funny
Inspirational
Informational and helpful

As for their purchasing journey, the number one way Gen Z discovers and researches products is through vlogs (video blogs), according to the GWI report. Once they’ve made a purchase, they become most loyal to brands that offer access to exclusive content or services.
So, if your target audience includes Gen Z, your social media presence will be more important than ever.
5. Brands can benefit from an omnichannel approach.
In a March 2020 eMarketer study of how U.S. adults preferred that brands communicate with them, email came out on top alongside TV advertising. Social media posts came in third and text messages next.​​
Image Source
What this data suggests is that a single-channel strategy is not enough to attract and retain consumers.
Their attention is being pulled in many different directions and if your brand isn’t present, you may quickly be forgotten.
Now, this isn’t to say that every brand should be utilizing every single channel out there. Because after all, not every channel will offer a positive return on investment. What we are suggesting is consistently leveraging a mix of one to three channels while staying updated on how your audience is responding.
Some channels may work well during specific stages in the buyer’s journey and some may work only for a time. Listening to your data is the best way to know which channels are worth your time and effort.
6. Shoppers expect BNPL options.
Buy-now-pay-later is a payment service that allows customers to spread out their purchase payments into interest-free installments.
The topic started rising in popularity around mid-year in 2020 but peaked in September 2021, according to Google Trends.
In 2020, Paypal launched its own payment installment plan and during the 2021 Black Friday, they had a nearly 400% increase in BNPL transactions year-over-year.
Although one CNBC report found that only 7% of shoppers planned to use BNPL this past holiday season, experts expect that figure to double or triple in 2022.
It’s particularly popular with Gen Z – a population that is only now starting to join the workforce. The 2022 GWI report found that one in five Gen Z shoppers in the Asia-Pacific region used a BNPL service in the last month.
Payment is often not something marketers think about when advertising a brand’s products and/or services. However, this data suggests it may be helpful to highlight this as it’s becoming a stronger purchasing consideration for consumers.
7. There’s more demand for sustainable brands.
In 2019, the Harvard Business Review reported that sustainable products are five times more successful in average sales growth compared to brands that aren’t marketed as such. Today, that trend continues to grow.

A Global Sustainability Study 2021 report found that one third of consumers are willing to pay a premium for sustainable products.
In fact, 61% of Gen Z say they pay more for eco-friendly products than those that aren’t, according to the GWI report. Specifically, online shoppers are looking for:

The use of natural, organic materials
Recycled or recyclable items
Brands in which part of the proceeds go to eco-friendly causes
No harmful chemicals
Durability

Many brands may offer one or more of these but fail to advertise them. However, this is what can help set you apart from the competition, as sustainability becomes a growing purchasing factor for consumers.
Now that you know what’s influencing consumer behavior, you can begin to craft a marketing strategy that appeals to your audience’s most pressing needs.