Cost monitoring in Google BigQuery can be a difficult task, especially within a growing organization and lots of (independent) stakeholders that have access to the data. If your organization is not using reserved slots (flat-rate pricing) but is billed by the number of bytes processed (on-demand pricing), costs can get quickly out of hand, and we need the means to investigate or "debug" the BigQuery usage in order to understand:
Previously, we had to manually set up query logging via Stackdriver as explained in the article
Taking a practical approach to BigQuery cost monitoring
but in late 2019 BigQuery introduced
INFORMATION_SCHEMA views
as a beta feature that also contain data about BigQuery jobs via the
INFORMATION_SCHEMA.JOBS_BY_* views
and became generally available (GA) at 2020-06-16
SELECT
creation_time,
job_id,
project_id,
user_email,
total_bytes_processed,
query
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
SELECT * FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
SELECT * FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATIONINFORMATION_SCHEMA.JOBS_BY_USER
view for all jobs that have been run in region US in the currently selected projectcost_in_dollar is estimate by calculating the total_bytes_processed in Terabyte and
multiplying the result with $5.00 (which corresponds to the the cost as of today 2020-06-21).
Also, we only take those costs into account if the query was not answered from the cache
(see the cache_hit != true condition)creation_time is converted to our local timezoneWHERE clause to filter
on the partition column creation_timeJOBS_BY_PROJECT with JOBS_BY_USER or JOBS_BY_ORGANIZATION
While playing around with the INFORMATION_SCHEMA views I've hit a couple of gotchas:
region-us in the view specification) and
must run the job in that region (e.g. from the BigQuery UI via
More > Query Settings > Processing location)US
can only access resources in location US. Though it would be very helpful for organizations that actively use different locations,
something like this is not possible:
SELECT * FROM
(SELECT * `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION)
UNION ALL
(SELECT * `region-eu`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION)JOBS_BY_USER view seems to "match" the user based on the email address. My user email adress is a @googlemail.com
address; in the user column it is stored as @gmail.com. Thus, I get no results when usingTruncated by Planet PHP, read more at the original (another 1126 bytes)
Do you want to provide a great experience for visitors on your site?
If so, user experience (UX) testing can help you analyze your site to ensure you’re providing a great experience for your audience.
But what is user experience testing?
On this page, we’ll answer this question, and also include:
Keep reading to learn more about user experience testing and subscribe to our weekly newsletter to stay on top of the latest tips and tricks for marketing your business!
User experience (UX) testing is known as the process of testing different areas and elements of your site that impact a user’s visit and determining the best way to optimize each for your audience.
UX testing enables you to gather the information you need to create a website that users love. It also allows you to identify weak points so that you can improve your site.
User experience testing is vital to your business for two big reasons:
Users are quick to judge your company based on their experience on your site. If you deliver poor user experience, your audience will likely leave your site, which means you miss out on conversions. Not to mention, they’ll remember the poor experience they had and won’t feel compelled to return.
Here are a few stats that speak to how poor user experience can have negative consequences:
As you can see, poor user experience can have lasting impacts on your business. Users are less likely to visit your site, stay on your site, and purchase from your company.
UX testing, however, can fix this problem. By testing different elements of your site, you can optimize it to create a better experience for your visitors. Your optimizations will lead them to have a positive user experience, which leads to them having a positive impression of your brand.
As we hinted in the previous section, user experience testing can help you increase sales. If you’re continually testing your site and revamping it to provide a better experience, your audience will be more likely to buy.
Did you know that slow loading websites cause a $2.6 billion revenue loss annually? And that’s just one aspect of the user experience!
As you can see, the way users experience your site strongly impacts whether leads convert. Poor user experience will lead to fewer conversions but conducting user experience testing and optimizing your site will enable you to increase your revenue.
Now that you have the answer to “what is user experience (UX) testing?” and know why you need to do it, it’s time to dive in and start conducting tests! But this now begs the question: How do you conduct UX testing?
Here are five main ways to test the user experience on your site.
With moderated user testing, you have a group of participants using your site and a moderator present to guide a discussion about how they experience your site. It functions as a discussion group where the moderator answers questions and tries to guide the participants as they navigate through the site.
Pros: Helps you see areas of confusion and improve them, allows you to get first-hand insight on your site
Cons: Answers may not be entirely authentic because of the moderator, the conversation may become bias if the moderator is not entirely objective
Another option for doing user experience testing for websites is unmoderated user testing. With this option, participants work through scenarios and tasks on their own — this typically occurs in a remote setting like their home. They can go through your provided steps and report back on their experience.
Pros: More authentic responses because they’re on their own, are not influenced by other participants’ opinions.
Cons: May not know how to navigate through the steps without guidance, difficult to follow-up with responses in real-time
With Card sorting, you can better understand how users find information on your site. These are digital or physical cards that contain different components of your site, like “Clothes,” “Women’s Boots,” or “Jewelry.”
Users take these cards and group them together based on what makes sense to them. It helps you get into the mind of your audience and how they group products and topics together.
This user experience test allows you to analyze how users navigate your site, so you can help them find information faster. This strategy helps you swap cards around to create a navigation that’s logical for your audience.
Here’s an example of how some participants may sort categories if you owned a clothing store:
| Category 1: Shoes | Category 2: Activewear | Category 3: Formal Wear |
| Card 1: Women’s Boots | Card 1: Activewear Tops | Card 1: Men’s Dress Shirts |
| Card 2: Women’s Sandals | Card 2: Activewear Bottoms | Card 2: Men’s Dress Pants |
| Card 3: Men’s Flip Flops | Card 3: Activewear Bras | Card 3: Men’s Dress Shoes |
| Card 4: Men’s Boots | Card 4: Running Shoes | Card 4: Men’s Belts |
So, if this was your company, you might have thought that it was best to put all your shoes in one category for your navigation. Upon using card sorting, you find that people actually put running shoes with activewear and dress shoes with formal wear.
This insight would be valuable to your business because it would help you see how your audience expects things to be grouped together in your navigation and site architecture, rather than what you think your audience expects.
Pros: Helps you adapt your site to fit the way users browse through your site
Cons: Limited to evaluating site architecture
Another great way to conduct user experience testing for websites is to create a poll or survey. Polls and surveys allow you to ask people directly about their experience and get immediate feedback. It’s a great way to gather a large data sample of responses to help you see what’s not working with your site.
Pros: Get an abundance of data, can focus on specific aspects of your site
Cons: Getting responses from your audience may be difficult, may not take the survey seriously
A/B testing is one of the most common types of testing companies do to analyze their site. A/B testing allows you to test two different versions of the same element on your site. It enables you to see which element provides a better experience.
For example, Lush could test their CTA button to see if the white button performs better than a yellow button, or a button of another color. This comparison test would enable them to see the impact of changing the button color in terms of earning clicks and conversions.
Pros: Enables you to compare to see improvements, can only require small and easy changes to improve the experience
Cons: Can only test one element at a time for precise results (time-consuming), focuses mostly on aesthetics.
Ready to dive into testing? Check out these short user experience testing checklist to get the best practices for successful UX testing!
Identify your goals: First and foremost, you need to know what you want to achieve with your UX testing.
Many businesses focus on increasing their sales as a goal. However, you may want to focus on encouraging more people to download one of your guides or signing up for your email list.
You must establish your goals for UX testing, so you understand how testing will help you reach that specific goal.
Choose the method that’s best for your chosen goals: This item is one of the most critical user experience testing best practices.
When you’re conducting tests for your site, you need to figure out which tests are best. Your goals will help you choose what kinds of tests to run.
Since there are numerous tests you can run, it’s important to identify those that will help you achieve your goals and will fit within your budget.
For example, you may not have the budget to do moderated user testing, but you still want direct feedback. So, you may opt for unmoderated user testing as a more budget-friendly option that provides direct feedback.
Establish a plan: If you want to drive success with your testing, you need to establish a plan. Your plan lays out everything about your test, including:
When you know this information, you’ll set your test up for success and drive better results.
Analyze your results: One of the most critical user experience testing best practices is analyzing your results. You don’t want to go through the trouble of running all these tests and not analyze the results of them because you won’t know how to fix your site or know what’s working.
Analyzing the results will depend upon the tests you use and what you want to measure. For moderated results, you may focus on common responses from all your participants, whereas with A/B testing, you may focus on metrics like clickthrough rate (CTR) or dwell time.
To monitor important metrics, you can use a tool like Google Analytics to analyze important metrics and understand how your campaign performs.
In this post, we answered important questions like:
But now, here’s the most critical question that still needs an answer: Do I have to do UX testing on my own?
No! You can rely on a digital marketing expert, like WebFX, to help you conduct useful UX tests and improve the experience on your site.
With our user experience analysis services, we’ll help you analyze your site to figure out where you can improve the UX on your website!
Want to learn more? Contact us online or call us today at 888-601-5359 to speak with a strategist!
The post What Is User Experience (UX) Testing? [+4 UX Best Practices] appeared first on WebFX Blog.
There is no MEDIAN() function in Google BigQuery, but we can still calculate the MEDIAN
with the
PERCENTILE_CONT(x, 0.5) or
PERCENTILE_DISC(x, 0.5)
functions. The difference between those two functions is the linear interpolation that is applied
when using PERCENTILE_CONT(x, 0.5) - so that's probably what you want when dealing with numeric
values. Take the numbers 1,2,3,4 for example:
PERCENTILE_CONT(x, 0.5) yields 2.5 (as the 50% percentile is exactly between 2 and 3)PERCENTILE_DISC(x, 0.5) yields 2 (as the 50% percentile is >= 2)SELECT
PERCENTILE_CONT(x, 0.5) OVER() AS median_cont,
PERCENTILE_DISC(x, 0.5) OVER() AS median_disc
FROM
UNNEST([1,2,3,4]) as x
LIMIT 1Result
| median_cont | median_disc |
|---|---|
| 2.5 | 2 |
Caution: As of today (2020-06-20), BigQuery only supports PERCENTILE_CONT and PERCENTILE_DISC for window functions
(hence the OVER() clause and the LIMIT 1 in the example above):
PERCENTILE_CONT is under development, and we will publish the documentation once it is GA. We will support it as analytic function first, and we plan to support it as aggregate function (allowing GROUP BY) later.
Source: SO: percentile functions with GROUPBY in BigQuery
The more common use case is probably to calculate the median as a result of a GROUP BY statement.
I.e. I would like to write something like this to get the median of quantity per product_id.
SELECT
product_id,
PERCENTILE_CONT(quantity, 0.5) AS median
GROUP BY
product_idRight now, that is only possible for the average via
AVG()
but not for the median. But we can still work around that limitation by using the
PERCENTILE_CONT function on a window partitioned by product_id, then group by the product_id
(to get only one row per product_id)
and resolve a single median value via ANY_VALUE().
There is also the APPROX_QUANTILES()
function (mentioned here) that can by applied to a GROUP BY. I didn't have a practical use case for approximate functions
yet, though. Thus, I don't know the implications of "not using an exact calculation" and rather mention
this for the sake of completeness. Example:
SELECT
product_id,
APPROX_QUANTILES(quantity, 100)[OFFSET(50)] as approx_median
GROUP BY
product_id
How CSS Variables Can Improve Efficiency and Consistency – Using CSS variables means less repetitive code.
What is Developer Experience (DX)? – The term is used to describe many things, but goes deeper than you might think.
The complete guide to CSS media queries – Use this handy guide to get the most out of media queries.
How to Create a Motion Hover Effect for a Background Image Grid – Create a compelling motion hover effect with this tutorial.
Color Copy Paste – Copy and paste colors directly from your camera to Figma, Sketch or your browser.
Cool Little CSS Grid Tricks for Your Blog – How the author used modern CSS to solve a long-standing layout challenge.
The Most Important Thing Your Clients Should Know About Their Website – How to help clients understand the inevitable changes their website will need over time.
Free Isometric Illustrations – Grab this free pack of 40+ items, available in six file formats.
Learn Z-Index Using a Visualization Tool – CSS z-index can be hard to grasp. This visual guide can help you get the hang of it.
Dealing With Overly Opinionated Design Clients – Learn how to put a stop to design-by-committee before it ruins your project.
SocialSizes.io – A resource for social media image and video sizes – complete with downloadable templates.
Massimo Says – An AI-powered, design-specific text generator.
12 Stunning Examples of Text Distortion Effects in Web Design – Code snippets that demonstrate a variety of beautiful distortion effects.
Helping Your Clients Master eCommerce – Use your expertise to point clients towards online success.
A Cheatsheet for User Interview and Follow Ups Questions – A great resource for knowing what to ask your users.
CSS :is() and :where() are coming to browsers – Get the lowdown on what these new CSS pseudo-classes are and how to use them.
Stranger Things, JavaScript Edition – Find the explanation to some of the weirdest JavaScript moments here.
The post Weekly News for Designers № 545 appeared first on Speckyboy Design Magazine.