BigQuery allows to define nested and repeated fields
in a table. Although this is very powerful, it makes it much more complex to retrieve the
data if one is not used to such structures. Especially beginners tend to use an
UNNEST statement
on the nested fields, followed by a huge
GROUP BY statement on the not-originally-repeated fields. Imho, using
expression subqueries
is oftentimes the better approach here.
SELECT
id,
(SELECT value from t.repeated_fields LIMIT 1)
FROM
table t Caution: When using expression subqueries, you need to make sure that the result is a single value (scalar or array), otherwise you will get the error message
Scalar subquery produced more than one element
In the example code above this is ensured by enforcing one result via LIMIT 1.
The most prominent use case is probably the BigQuery export schema of Google Analytics. To be honest, I also feel that the schema is not very friendly for newcomers with its ~30 RECORD-type (nested) fields and 300+ columns.
In a nutshell, each row represents one session.
A session consists of multiple hits. Those hits are also available in the nested and repeated hits field. But wait, there is more...
Each hit can have a number of so called customDimensions (meta data that can be attached to each hit). So the resulting table structue looks something
like this:
- field_1
- field_2
- hits
- field_1
- field_2
- customDimensions
- index
- value The following example uses the public Google Analytics sample dataset for BigQuery and shows a couple of sample expression subqueries
SELECT
fullVisitorId,
visitStartTime,
TIMESTAMP_SECONDS(visitStartTime) as started_at,
TIMESTAMP_SECONDS(visitStartTime + CAST( (SELECT time from t.hits ORDER BY hitNumber DESC LIMIT 1) /1000 AS INT64)) as ended_at,
(SELECT COUNT(*) from t.hits) as hit_count,
(SELECT page.hostname || page.pagePath from t.hits WHERE isEntrance = TRUE) as landing_page,
(
SELECT
(SELECT COUNT(*) from h.customDimensions)
FROM
t.hits h
WHERE
hitNumber = 1
) as customDimension_count_of_first_hit,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801` t
ORDER BY
visitStartTime ascIn Google BigQuery we can define named subqueries via WITH clauses.
Those WITH clauses are a very comfortable way to structure complex queries as it allows to reference those queries like actual tables later on.
Note: BigQuery also supports actcual temporary tables via CREATE TEMPORARY TABLE. See the official documention on
temporary tables for further infos.
This is out of scope for this snippet, though.
WITH filtered_data as (
SELECT
id
FROM
table
WHERE
id BETWEEN 5 and 10
)
SELECT
*
FROM
filtered_data 
Named subqueries are a great way to structure complex queries and give sub-results a meaningful name. When working with partitioned tables, I always use temporary tables via WITH to make sure I restrict the query to scan only a limited number of partitions.
Conceptual example:
DECLARE from_date TIMESTAMP DEFAULT "2018-04-09";
DECLARE to_date TIMESTAMP DEFAULT "2018-04-10";
WITH huge_table_partition as(
SELECT
*
FROM
huge_table
WHERE
_PARTITIONTIME BETWEEN from_date AND to_date
)
SELECT
*
FROM
huge_table_partitionWe can use variables by defining them with a DECLARE statement,
e.g.
DECLARE foo STRING DEFAULT "foo";
#DECLARE <variable> <type> DEFAULT <value>;with <type> being one of the BigQuery's built-in standard-sql data types
This is equivalent to variables of other SQL databases, e.g.
SET @foo = 'bar';foo varchar := 'bar';DECLARE foo_var STRING DEFAULT "foo";
SELECT foo_var
Hardcoding variables is generally considered a bad practice as it makes it harder to understand and modify a query. A frequent use case for me is the definition of date ranges (from and to dates) that are used for querying partitioned tables:
DECLARE from_date DATE DEFAULT DATE("2018-04-09");
DECLARE to_date DATE DEFAULT DATE("2018-04-10");
WITH data as(
SELECT
1 as id,
DATE("2018-04-08") AS date,
UNION ALL SELECT 2, DATE("2018-04-09")
UNION ALL SELECT 3, DATE("2018-04-10")
UNION ALL SELECT 4, DATE("2018-04-11")
)
SELECT
id,
date
FROM
data
WHERE
date BETWEEN from_date AND to_date
Why the Grumpy Designer Will Keep Working from Home, Thank You – A look at why the work-from-home lifestyle is too good to give up.
An Infinitely Scrollable Vertical Menu – Learn how to create a unique scrolling effect on a classic vertical menu.
The Third Age of JavaScript – A look at what the next 10 years may bring to JavaScript.
How to Spot Terrible Client Business Ideas – Tips for professionally-distancing yourself from ideas destined to fail.
The Easiest Guide to Webflow for Beginners – Get started with the Webflow app using this helpful guide.
Best GitHub Repos for Web Developers – Level up on your knowledge by following this list of repositories.
The Fastest Google Fonts – Benchmarking Google Fonts to see which embed technique performs the best.
Keyframes.app – Generate awesome CSS animations with this web-based tool.
5 Things a Modern CMS Shouldn’t Do – The modern CMS has made great strides, but there are still areas that need improvement.
Fluor.js – A tiny JavaScript library for easily adding interactions and effects to your website.
Gradihunt – Find or generate the perfect CSS gradient for your projects.
10 Free InDesign Business Proposal Templates – Make a great first impression with these beautiful InDesign templates.
TabNotes – A free Chrome extension that lets you take Markdown-supported notes in a new browser tab.
Figurative – Get all the things you love about Figma on your iPad Pro with this free iOS app.
Let’s Make One of Those Fancy Scrolling Animations Used on Apple Product Pages – Got Apple envy? Use this tutorial to recreate one of their most prominent website effects.
The 10 Best HDR Effect Photoshop Actions – Use this collection to create professional-grade effects on your images.
The post Weekly News for Designers № 542 appeared first on Speckyboy Design Magazine.
Did you know that 94% of a user’s first impression of your business comes from your site’s design?
In this post, we’ll look at how you can use emotion in your web design to create a great impression, enhance user experience (UX), and drive conversions.
Keep reading for my insider tips, or check out our web design services to learn how our award-winning agency can improve your online presence!
Many times, emotion plays a big role in your site’s conversion process.
Understanding users’ emotions and incorporating empathy into your design allows you to create long-lasting relationships. And people will feel more comfortable working with your business.
Without emotion and empathy, the creative process falls short, and visitors will be less likely to convert and become customers.
We don't just want to tell you about the beautiful work we do. WE WANT TO SHOW YOU We've built over WEBSITES IN INDUSTRIES LIKE YOURS
1
0
0
0
Now let’s dive into four tips for incorporating design elements that appeal to visitors’ emotions and better meet their needs.
Whenever I’m starting a new web design project (big or small), I research a client’s end users in order to discover opportunities and outline end goals for the project.
You can easily identify your end users by looking at your demographics data in Google Analytics and pairing that with what you know about your user base.
With that information, you can start creating target personas.
Once I identify key personas for a project, I like to put on my “end-user glasses” to help me view a website through their eyes.
Pretending to be the end user, I run through the following steps:
I then repeat the same steps while using competitor websites, keeping notes the entire time.
Once I have all of my notes, I take off my “end-user glasses” and start to make sense of the reactions I had on each site.
This is my favorite way to do a qualitative competitive analysis — and putting myself in the end user’s shoes allows me to create designs that better appeal to their needs and emotions.
Online surveys are a very useful tool when you want direct feedback from end users about their needs and emotions your site evokes.
I like to pair my “qualitative deep dive” above with feedback from end users to create a web design project plan.
Before you start thinking about the questions you want to ask participants, you should clearly identify the goals of your research.
Ask yourself the following questions:
You can easily set up user surveys using Hotjar or Crazy Egg tied with Survey Monkey.
Once you collect responses, you can compare feedback and identify opportunities to better meet users’ needs with your design.
Next, preference tests help you tap into audience insights and emotions to refine your website’s design.
Let’s say you started your web design project after conducting initial research, and you’ve entered the wireframing or prototyping phase.
If you would like to get feedback on your prototypes, quick preference tests are a cost-effective way to get insights from fresh eyes.
There are many preference tools out there, like UsabilityHub which can connect you with audiences to validate your web design choices or steer you in a new direction.
My last tip is not really a test, and it’s completely free if you take the time to pay attention to the details of your design project.
While moving through the tests above, you likely found areas of user tension and potentially some conversion resistance.
User tension is typically due to your website lacking:
After identifying areas of tension, you can write content to address users’ concerns and help them feel confident doing business with you.
Tuning in to the needs of your audience and incorporating emotional design elements will enhance your site’s design and streamline conversions.
If you need help freshening up your site’s design or conducting a UX audit, WebFX is happy to help.
With more than 1000 websites launched for clients and 50+ web design awards, our team can help you create a custom site that supports your business goals.
Contact us today to connect with a web design expert, or give us a call at 888-601-5359!
The post How to Use Emotion for Better Web Design and UX appeared first on WebFX Blog.
Here's what was popular in the PHP community one year ago today: