EsheleD Marketing & Technology

24Sep/160

Penguin is now part of our core algorithm

Google's algorithms rely on more than 200 unique signals or "clues" that make it possible to surface what you might be looking for. These signals include things like the specific words that appear on websites, the freshness of content, your region and PageRank. One specific signal of the algorithms is called Penguin, which was first launched in 2012 and today has an update.

After a period of development and testing, we are now rolling out an update to the Penguin algorithm in all languages. Here are the key changes you'll see, which were also among webmasters' top requests to us:

  • Penguin is now real-time. Historically, the list of sites affected by Penguin was periodically refreshed at the same time. Once a webmaster considerably improved their site and its presence on the internet, many of Google's algorithms would take that into consideration very fast, but others, like Penguin, needed to be refreshed. With this change, Penguin's data is refreshed in real time, so changes will be visible much faster, typically taking effect shortly after we recrawl and reindex a page. It also means we're not going to comment on future refreshes.
  • Penguin is now more granular. Penguin now devalues spam by adjusting ranking based on spam signals, rather than affecting ranking of the whole site. 

The web has significantly changed over the years, but as we said in our original post, webmasters should be free to focus on creating amazing, compelling websites. It's also important to remember that updates like Penguin are just one of more than 200 signals we use to determine rank.

As always, if you have feedback, you can reach us on our forums, Twitter and Google+.

Posted by Gary Illyes, Google Search Ranking Team

24Sep/160

Using Google Analytics with Leadfeeder for B2B Lead Generation

Google Analytics is an important tool for marketers. It’s used to understand how people come to your website, how different content performs and how to get more conversions. All this is essential for knowing how to drive more high-quality traffic to your website.

For most B2B firms, the main purpose of their website is to generate sales leads by getting visitors to fill in some kind of contact form. But many see that just a fraction of visitors leave their contact information, and as a result, salespeople don’t get enough good quality leads from their website. So what can be done to improve this situation?

This guide will show you the 3 best ways to generate leads with Google Analytics:

1. Using Google Analytics Network report
2. Using a custom Google Analytics report template tool
3. Using Google Analytics with Leadfeeder for lead generation

One way to gain more leads from your website is identifying companies that visit, based on their IP address. With this data, you can have some information about the 98% of visitors that don’t explicitly contact you. When people visit a website using their office network, marketers can identify that someone from a certain company has visited and pinpoint what they have done there. For B2B outbound sales teams, this information can be very valuable.

If you see a company visiting your website, there’s a high probability that they’re in need of- and evaluating your product, which is the perfect time to get in touch with them.

Based on the IP address alone, it’s impossible to know exactly the name of the visitor, but in many cases this information is not needed. For example, if you sell email marketing tools and a company comes to your website and browses product pages, it’s a strong signal they are looking for a new email marketing tool. When you contact them, you want to contact the person who’s responsible for digital marketing, regardless of who visited your website.

For effective lead generation purposes, you should be able to identify real companies that have visited your website and see how they have behaved, to evaluate if they are a good lead.

1. Using Google Analytics Network Report

Using the Network report is the most common way to see which companies have been visiting your website. There have been many blog posts about this topic, for example this LunaMetrics post by Dan Wilkerson from 2012, this how-to article from Anna Lewis and a post by Traian Neacsu on uncovering hidden leads.

But these posts are all now a couple of years old and the Google Analytics interface has changed quite a lot since then. These days (2016) you can find the Network report in Google Analytics under Audience > Technology > Network.

Network report in Google Analytics

In the Network report (seen above) you will see a list of “Service Providers”. What Google Analytics means by “Service Provider” is the network where the visitor has been when they visited your website. Networks are always owned and registered by someone; typically a company, Internet Service Provider or some other organization.

One challenge in using the Network report is that many times the IP is registered by an Internet Service Provider (ISP) or some other non-interesting organization. In order to see the real companies, you should filter out ISPs from the list. The easiest way of doing this is to use the advanced search button and select to exclude Service Providers that match the following RegExp (just copy/paste this to the filter):

(not set|customer|internet|broadband|isp|cable com|network|tele|dsl|subscriber|pool|telecom|cable|addresses|telefonica|routed|leased line|communication|comcast|verizon|road runner|service provider|unknown|provider|t-mobile|wifi|telkom|sprint|at-t|residential|province|vodafone|clients|china|dial-up|netblock|wimax|wireless|elisa|sonera|dna oy|at&t|assigned|sl-cgn|block|consumers|kpn|telia|bredband|google|hosting|zscaler|city of|tdc|hubspot) 

Now the list of visiting companies should look a lot cleaner. If you wish to filter the results even further, e.g. only companies from a specific country, you should create a segment out of visitors from that country and look at the report again.

By default for each company in the list you can see how many sessions they have had during the selected time interval, how many pages they have visited and other metrics. When you click on one company, you can get to a more detailed report, like this one below.

In this view, select “Page Title” or “Page” as secondary dimension to know which pages the company has visited. This way you know what they have done on your website and what they were interested in. If they were visiting relevant product pages or they spent a lot to time on your site but didn’t contact you, maybe it’s a good lead for your outbound sales team to contact.

Using Network report to see what company did on the website

If you would really like to know what each company and their employees have done on your website, you can go to the brand new User Explorer report under the Audience section in Google Analytics. This report was introduced in Google Analytics in April 2016 and in the report you can dive into individual visitors and their behavior.

To know what a company did on your website, just create a segment where the Service Provider matches the company you are interested in (see below).

Using User Explorer to see visitors from one company

By doing this Google Analytics shows you Client IDs (anonymous and unique IDs of each visitor) from that company and by clicking one client ID (one user) you can see all the behavior of that user on your website. This way you can have a good understanding about what any given person from one specific company did on your website. Pretty powerful stuff, as you can see below.

Using User Explorer to see visits of one user

2. Using a custom Google Analytics report template tool

At Leadfeeder we created a ready-to-use Google Analytics custom report that anyone can take into use for free. Just click the link below and attach it as a custom report to the Google Analytics View you typically use: 

https://analytics.google.com/analytics/web/template?uid=zAQeJwWfT0yxlk8wfPWaGA 

When you click this link, you will be directed to Google Analytics and asked which view you want to attach it to. Remember that by default this custom report will be visible only for you. If other users want to use this report, they should also click the Add to Dashboard link above.

B2B Lead generation report by Leadfeeder

Our custom tool by default shows the most interested companies that have visited your website. To be more precise, this is a list of companies after ISP filtering (using the filter pasted above) sorted by pages / session, with the most interesting visits on top.

Typically companies that have spent a lot of time and loaded many pages are more interested than companies with many short visits. Once you click an interesting company, you can see a list of days on which they have visited. When you click even further into a specific date, you can see a breakdown of all the pages they visited. If page titles are not descriptive enough or not set, you can use “Page” as a secondary dimension to see page URLs (as shows below).

Using B2B Lead generation report by Leadfeeder

In the tool you can see several tabs that you can use (see above). The most interested companies tab is selected by default, but you can also select the second tab – companies with most visitors – that shows companies that have most visits on your website. Many times here you can find ISPs that were not filtered out along with other non-interesting companies. If you like, you can drill into details in the same way as in the first tab.

The 3rd and 4th tabs in the report are selectors that you can use to filter data. For example, if you are only interested in German companies, go to “Selector: Visitor location” tab and select Germany. After that click to the first tab to see the most interested companies from Germany. If you have a sales representative located in Germany searching for German leads, you can automatically have Google Analytics send this lead list by daily or weekly email.

Automating notifications from B2B Lead generation tool by Leadfeeder

Similarly, if your sales team’s responsibilities are divided by product, then sales reps might only be interested in leads that have visited certain product pages. Go to “Selector: Visited page” tab and select the product page each sales rep is interested in. Again, after making the selection, go to the first tab to see the list of the most interested companies visiting that product page and automate lead reports to your sales rep’s mailbox. You can also combine these selectors to create a list of companies from a specific country that have visited a specific product page.

3. Using Google Analytics with Leadfeeder for lead generation

Using the Google Analytics user interface for lead generation is possible as you can see, but not very salesperson-friendly.

 In order to better generate leads from your website for your sales department and do much much more, we created an online tool called Leadfeeder. Since Google Analytics is already collecting all the data about your website visitors, Leadfeeder fetches this data from Google Analytics API and does all the data crunching for you.

Leadfeeder lead generation main report

Once you sign up to Leadfeeder, it fetches all visitor data from your Google Analytics for the past 30 days. You don’t need to install any codes or script on your website; all you need to do is permit Leadfeeder to access your Google Analytics.

The web app filters out ISPs (a lot more than the Google Analytics filters shown in this post) handing you a clean list of companies. Once you see an interesting company and click on it, you see visit-by-visit, page-by-page what they have done on your website (as shown below). Leads are also enriched with additional company information such as company branch and size. With all this information it’s easier to determine whether the lead is interesting and whether they should be contacted.

Leadfeeder showing which pages a company has visited

Not all website visitors are interesting, so you can use custom feeds to filter out bounces and view only companies that have behaved in a way you find interesting. For example, you can define a custom feed rule to only show companies from a certain country, from a specific industry, companies that have visited a set number of pages and have visited a specific page but haven’t contacted you.

Using this kind of custom feed you can get a much more relevant list of leads for your sales team. In many companies sales responsibilities are divided by region or product so it’s good practice to make custom feeds for individual sales reps for only their areas of responsibility. Salespeople can subscribe to their personal custom feed to get daily or weekly email notifications about new companies that visit their website and match the set criteria. Understanding the online behaviour of your website visitors combined with knowing the location of the company visit gives sales reps powerful weapons for successful follow-up.

Using custom feeds in Leadfeeder to filter lead list

Seeing a qualified list of interested companies is already powerful, but this sales intelligence should fit within your existing sales process to be really useful. We know it’s the dream of many sales reps to have good leads magically appear in their CRM without the need to do anything, so that’s why at Leadfeeder we have built integrations to sync visitor data with your CRM.

The integration to Pipedrive and WebCRM are made two-way, which means that in Leadfeeder you can see CRM data for the visiting company, while in your CRM you can see all the website visits the company has made, once it’s been connected.

This makes it easier for sales reps to distinguish between new and old clients in Leadfeeder, create accounts and opportunities in their CRM with one click from Leadfeeder, and see in their CRM how prospects are interacting with their website.

Using CRM integration to sending leads from Leadfeeder to CRM

If you are not using a CRM at all, leads can also be sent to sales reps by email or you can assign leads for them to see inside Leadfeeder. It’s good practice to invite the whole sales team to use Leadfeeder with their own user profiles and it’s free to add users.

In addition, if you are using Mailchimp for email marketing, you can connect it to Leadfeeder to see in Leadfeeder what individuals do on your website when they click through from one of your MailChimp campaigns. This is possible because Mailchimp tags links uniquely for all recipients and Leadfeeder can transform these unique links into email addresses. This way you can know exactly who the visitor was on your website.

Leadfeeder offers a free 30-day trial with no credit card required, so if you are in B2B business and would like to get more sales leads, go and sign up at www.leadfeeder.com.

Conclusions

Web analytics has made marketing a lot more intelligent during the last 10 years, but similar development hasn’t transferred to sales.

Web analytics has enabled email tools to evolve into marketing automation by tracking what email clickers do on your website and triggering follow-up emails. Display marketing, similarly, has evolved into very efficient remarketing, where ads are shown to those who have completed action on your website.

In short, there are a lot of digital signals potential customers are giving all the time, but those haven’t been utilized well in sales so far. Many sales reps come to work, open their CRM and start calling through a lead list someone has given them. Meanwhile there are lots of potential customers browsing their website but sales reps aren’t aware of who they are. Our aim at Leadfeeder is to make sales more intelligent by providing salespeople actionable web analytics intelligence about potential customers, thereby making sales more effective.

Posted by Pekka Koskinen, Google Analytics Certified Partner

23Sep/160

Using BigQuery and Firebase Analytics to understand your mobile app

Posted by Sara Robinson, Developer Advocate

At Google I/O this May, Firebase announced a new suite of products to help developers build mobile apps. Firebase Analytics, a part of the new Firebase platform, is a tool that automatically captures data on how people are using your iOS and Android app, and lets you define your own custom app events. When the data's captured, it’s available through a dashboard in the Firebase console. One of my favorite cloud integrations with the new Firebase platform is the ability to export raw data from Firebase Analytics to Google BigQuery for custom analysis. This custom analysis is particularly useful for aggregating data from the iOS and Android versions of your app, and accessing custom parameters passed in your Firebase Analytics events. Let’s take a look at what you can do with this powerful combination.

How does the BigQuery export work?

After linking your Firebase project to BigQuery, Firebase automatically exports a new table to an associated BigQuery dataset every day. If you have both iOS and Android versions of your app, Firebase exports the data for each platform into a separate dataset. Each table contains the user activity and demographic data automatically captured by Firebase Analytics, along with any custom events you’re capturing in your app. Thus, after exporting one week’s worth of data for a cross-platform app, your BigQuery project would contain two datasets, each with seven tables:

Diving into the data

The schema for every Firebase Analytics export table is the same, and we’ve created two datasets (one for iOS and one for Android) with sample user data for you to run the example queries below. The datasets are for a sample cross-platform iOS and Android gaming app. Each dataset contains seven tables  one week’s worth of analytics data.

The following query will return some basic user demographic and device data for one day of usage on the iOS version of our app:

SELECT
  user_dim.app_info.app_instance_id,
  user_dim.device_info.device_category,
  user_dim.device_info.user_default_language,
  user_dim.device_info.platform_version,
  user_dim.device_info.device_model,
  user_dim.geo_info.country,
  user_dim.geo_info.city,
  user_dim.app_info.app_version,
  user_dim.app_info.app_store,
  user_dim.app_info.app_platform
FROM
  [firebase-analytics-sample-data:ios_dataset.app_events_20160601]

Since the schema for every BigQuery table exported from Firebase Analytics is the same, you can run any of the queries in this post on your own Firebase Analytics data by replacing the dataset and table names with the ones for your project.

The schema has user data and event data. All user data is automatically captured by Firebase Analytics, and the event data is populated by any custom events you add to your app. Let’s take a look at the specific records for both user and event data.

User data

The user records contain a unique app instance ID for each user (user_dim.app_info.app_instance_id in the schema), along with data on their location, device and app version. In the Firebase console, there are separate dashboards for the app’s Android and iOS analytics. With BigQuery, we can run a query to find out where our users are accessing our app around the world across both platforms. The query below makes use of BigQuery’s union feature, which lets you use a comma as a UNION ALL operator. Since a row is created in our table for each bundle of events a user triggers, we use EXACT_COUNT_DISTINCT to make sure each user is only counted once:

SELECT
  user_dim.geo_info.country as country,
  EXACT_COUNT_DISTINCT( user_dim.app_info.app_instance_id ) as users
FROM
  [firebase-analytics-sample-data:android_dataset.app_events_20160601],
  [firebase-analytics-sample-data:ios_dataset.app_events_20160601]
GROUP BY
  country
ORDER BY
  users DESC

User data also includes a user_properties record, which includes attributes you define to describe different segments of your user base, like language preference or geographic location. Firebase Analytics captures some user properties by default, and you can create up to 25 of your own.

A user’s language preference is one of the default user properties. To see which languages our users speak across platforms, we can run the following query:

SELECT
  user_dim.user_properties.value.value.string_value as language_code,
  EXACT_COUNT_DISTINCT(user_dim.app_info.app_instance_id) as users,
FROM
  [firebase-analytics-sample-data:android_dataset.app_events_20160601],
  [firebase-analytics-sample-data:ios_dataset.app_events_20160601]
WHERE
  user_dim.user_properties.key = "language"
GROUP BY
  language_code
ORDER BY
  users DESC

Event data

Firebase Analytics makes it easy to log custom events such as tracking item purchases or button clicks in your app. When you log an event, you pass an event name and up to 25 parameters to Firebase Analytics and it automatically tracks the number of times the event has occurred. The following query shows the number of times each event in our app has occurred on Android for a particular day:

SELECT
  event_dim.name,
  COUNT(event_dim.name) as event_count
FROM
  [firebase-analytics-sample-data:android_dataset.app_events_20160601]
GROUP BY
  event_dim.name
ORDER BY
  event_count DESC

If you have another type of value associated with an event (like item prices), you can pass it through as an optional value parameter and filter by this value in BigQuery. In our sample tables, there is a spend_virtual_currency event. We can write the following query to see how much virtual currency players spend at one time:

SELECT
  event_dim.params.value.int_value as virtual_currency_amt,
  COUNT(*) as num_times_spent
FROM
  [firebase-analytics-sample-data:android_dataset.app_events_20160601]
WHERE
  event_dim.name = "spend_virtual_currency"
AND
  event_dim.params.key = "value"
GROUP BY
  1
ORDER BY
  num_times_spent DESC

Building complex queries

What if we want to run a query across both platforms of our app over a specific date range? Since Firebase Analytics data is split into tables for each day, we can do this using BigQuery’s TABLE_DATE_RANGE function. This query returns a count of the cities users are coming from over a one week period:

SELECT
  user_dim.geo_info.city,
  COUNT(user_dim.geo_info.city) as city_count
FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
GROUP BY
  user_dim.geo_info.city
ORDER BY
  city_count DESC

We can also write a query to compare mobile vs. tablet usage across platforms over a one week period:

SELECT
  user_dim.app_info.app_platform as appPlatform,
  user_dim.device_info.device_category as deviceType,
  COUNT(user_dim.device_info.device_category) AS device_type_count FROM
TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
GROUP BY
  1,2
ORDER BY
  device_type_count DESC

Getting a bit more complex, we can write a query to generate a report of unique user events across platforms over the past two weeks. Here we use PARTITION BY and EXACT_COUNT_DISTINCT to de-dupe our event report by users, making use of user properties and the user_dim.user_id field:

SELECT
  STRFTIME_UTC_USEC(eventTime,"%Y%m%d") as date,
  appPlatform,
  eventName,
  COUNT(*) totalEvents,
  EXACT_COUNT_DISTINCT(IF(userId IS NOT NULL, userId, fullVisitorid)) as users
FROM (
  SELECT
    fullVisitorid,
    openTimestamp,
    FORMAT_UTC_USEC(openTimestamp) firstOpenedTime,
    userIdSet,
    MAX(userIdSet) OVER(PARTITION BY fullVisitorid) userId,
    appPlatform,
    eventTimestamp,
    FORMAT_UTC_USEC(eventTimestamp) as eventTime,
    eventName
    FROM FLATTEN(
      (
        SELECT
          user_dim.app_info.app_instance_id as fullVisitorid,
          user_dim.first_open_timestamp_micros as openTimestamp,
          user_dim.user_properties.value.value.string_value,
          IF(user_dim.user_properties.key = 'user_id',user_dim.user_properties.value.value.string_value, null) as userIdSet,
          user_dim.app_info.app_platform as appPlatform,
          event_dim.timestamp_micros as eventTimestamp,
          event_dim.name AS eventName,
          event_dim.params.key,
          event_dim.params.value.string_value
        FROM
         TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()),
TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP())
), user_dim.user_properties)
)
GROUP BY
  date, appPlatform, eventName

If you have data in Google Analytics for the same app, it’s also possible to export your Google Analytics data to BigQuery and do a JOIN with your Firebase Analytics BigQuery tables.


Visualizing analytics data

Now that we’ve gathered new insights from our mobile app data using the raw BigQuery export, let’s visualize it using Google Data Studio. Data Studio can read directly from BigQuery tables, and we can even pass it a custom query like the ones above. Data Studio can generate many different types of charts depending on the structure of your data, including time series, bar charts, pie charts and geo maps.

For our first visualization, let’s create a bar chart to compare the device types from which users are accessing our app on each platform. We can paste the mobile vs. tablet query above directly into Data Studio to generate the following chart:

From this chart, it’s easy to see that iOS users are much more likely to access our game from a tablet. Getting a bit more complex, we can use the above event report query to create a bar chart comparing the number of events across platforms:

Check out this post for detailed instructions on connecting your BigQuery project to Data Studio.

What’s next?

If you’re new to Firebase, get started here. If you’re already building a mobile app on Firebase, check out this detailed guide on linking your Firebase project to BigQuery. For questions, take a look at the BigQuery reference docs and use the firebase-analytics and google-bigquery tags on Stack Overflow. And let me know if there are any particular topics you’d like me to cover in an upcoming post.

23Sep/160

Global Historical Daily Weather Data now available in BigQuery

Posted by Lak Lakshmanan, Google Cloud Platform Professional Services

Historical daily weather data from the Global Historical Climate Network (GHCN) is now available in Google BigQuery, our managed analytics data warehouse. The data comes from over 80,000 stations in 180 countries, spans several decades and has been quality-checked to ensure that it's temporally and spatially consistent. The GHCN daily data is the official weather record in the United States.

According to the National Center for Atmospheric Research (NCAR), routine weather events such as rain and unusually warm and cool days directly affect 3.4% of the US Gross Domestic Product, impacting everyone from ice-cream stores, clothing retailers, delivery services, farmers, resorts and business travelers. The NCAR estimate considers routine weather only  it doesn’t take into account, for example, how weather impacts people’s moods, nor the impact of destructive weather such as tornadoes and hurricanes. If you analyze data to make better business decisions (or if you build machine learning models to provide such guidance automatically), weather should be one of your inputs.

The GHCN data has long been freely available from the National Oceanic and Atmospheric Association (NOAA) website to download and analyze. However, because the dataset changes daily, anyone wishing to analyze that data over time would need to repeat the process the following day. Having the data already loaded and continually refreshed in BigQuery makes it easier for researchers and data scientists to incorporate weather information in analytics and machine learning projects. The fact that BigQuery analysis can be done using standard SQL makes it very convenient to start analyzing the data.

Let’s explore the GHCN dataset and how to interact with it using BigQuery.

Where are the GHCN weather stations?

The GHCN data is global. For example, let’s look at all the stations from which we have good minimum-temperature data on August 15, 2016:

SELECT
  name,
  value/10 AS min_temperature,
  latitude,
  longitude
FROM
  [bigquery-public-data:ghcn_d.ghcnd_stations] AS stn
JOIN
  [bigquery-public-data:ghcn_d.ghcnd_2016] AS wx
ON
  wx.id = stn.id
WHERE
  wx.element = 'TMIN'
  AND wx.qflag IS NULL
  AND STRING(wx.date) = '2016-08-15'

This returns:

By plotting the station locations in Google Cloud Datalab, we notice that the density of stations is very good in North America, Europe and Japan and quite reasonable in most of Asia. Most of the gaps correspond to sparsely populated areas such as the Australian outback, Siberia and North Africa. Brazil is the only gaping hole. (For the rest of this post, I’ll show only code snippets  for complete BigQuery queries and Python plotting commands, please see the full Datalab notebook on github.)

Blue dots represent GHCN weather stations around the world.

Using GHCN weather data in your applications

Here’s a simple example of how to incorporate GHCN data into an application. Let’s say you're a pizza chain based in Chicago and want to explore some weather variables that might affect demand for pizza and pizza delivery times. The first thing to do is to find the GHCN station closest to you. You go to Google Maps and find that your latitude and longitude is 42 degrees latitude and -87.9 degrees longitude, and run a BigQuery query that computes the great-circle distance between a station and (42, -87.9) to get the distance from your pizza shop in kilometers (see the Datalab notebook for what this query looks like). The result looks like this:

Plotting these on a map, you can see that there are a lot of GHCN stations near Chicago, but our pizza shop needs data from station USW00094846 (shown in red) located at O’Hare airport, 3.7 km away from our shop.

Next, we need to pull the data from this station on the dates of interest. Here, I'll query the table of 2015 data and pull all the days from that table. To get the rainfall amount (“precipitation” or PRCP) in millimeters, you’d write:


SELECT
  wx.date,
  wx.value/10.0 AS prcp
FROM
  [bigquery-public-data:ghcn_d.ghcnd_2015] AS wx
WHERE
  id = 'USW00094846'
  AND qflag IS NULL
  AND element = 'PRCP'
ORDER BY wx.date

Note that we divide wx.value by 10 because the GHCN reports rainfall in tenths of millimeters. We ensure that the quality-control flag (qflag) associated with the data is null, indicating that the observation passed spatio-temporal quality-control checks.

Typically, though, you’d want a few more weather variables. Here’s a more complete query that pulls rainfall amount, minimum temperature, maximum temperature and the presence of some weather phenomenon (fog, hail, rain, etc.) on each day:


SELECT
  wx.date,
  MAX(prcp) AS prcp,
  MAX(tmin) AS tmin,
  MAX(tmax) AS tmax,
  IF(MAX(haswx) = 'True', 'True', 'False') AS haswx
FROM (
  SELECT
    wx.date,
    IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp,
    IF (wx.element = 'TMIN', wx.value/10, NULL) AS tmin,
    IF (wx.element = 'TMAX', wx.value/10, NULL) AS tmax,
    IF (SUBSTR(wx.element, 0, 2) = 'WT', 'True', NULL) AS haswx
  FROM
    [bigquery-public-data:ghcn_d.ghcnd_2015] AS wx
  WHERE
    id = 'USW00094846'
    AND qflag IS NULL )
GROUP BY
  wx.date
ORDER BY
  wx.date

The query returns rainfall amounts in millimeters, maximum and minimum temperatures in degrees Celsius and a column that indicates whether there was impactful weather on that day:

You can cast the results into a Pandas DataFrame and easily graph them in Datalab (see notebook in github for queries and plotting code):

BigQuery Views and Data Studio 360 dashboards

Since the previous query pivoted and transformed some fields, you can save the query as a View. Simply copy-paste this query into the BigQuery console and select “Save View”:

SELECT
  REPLACE(date,"-","") AS date,
  MAX(prcp) AS prcp,
  MAX(tmin) AS tmin,
  MAX(tmax) AS tmax
FROM (
  SELECT
    STRING(wx.date) AS date,
    IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp,
    IF (wx.element = 'TMIN', wx.value/10, NULL) AS tmin,
    IF (wx.element = 'TMAX', wx.value/10, NULL) AS tmax
  FROM
    [bigquery-public-data:ghcn_d.ghcnd_2016] AS wx
  WHERE
    id = 'USW00094846'
    AND qflag IS NULL
    AND value IS NOT NULL
    AND DATEDIFF(CURRENT_DATE(), date) < 15 )
GROUP BY
  date
ORDER BY
  date ASC

Notice my use of DATEDIFF and CURRENT_DATE functions to get weather data from the past two weeks. Saving this query as a View allows me to query and visualize this View as if it were a BigQuery table.

Since visualization is on my mind, I can go over to Data Studio and easily create a dashboard from this View, for example:

One thing to keep in mind is that the "H" in GHCN stands for historical. This data is not real-time, and there's a time lag. For example, although I did this query on August 25, the latest data shown is from August 22.

Mashing datasets in BigQuery

It’s quite easy to execute a weather query from your analytics program and merge the result with other corporate data.

If that other data is on BigQuery, you can combine it all in a single query! For example, another BigQuery dataset that’s publicly available is airline on-time arrival data. Let’s mash the GHCN and on-time arrivals datasets together:


SELECT
  wx.date,
  wx.prcp,
  f.departure_delay,
  f.arrival_airport
FROM (
  SELECT
    STRING(date) AS date,
    value/10 AS prcp
  FROM
    [bigquery-public-data:ghcn_d.ghcnd_2005]
  WHERE
    id = 'USW00094846'
    AND qflag IS NULL
    AND element = 'PRCP') AS wx
JOIN
  [bigquery-samples:airline_ontime_data.flights] AS f
ON
  f.date = wx.date
WHERE
  f.departure_airport = 'ORD'
LIMIT 100

This yields a table with both flight delay and weather information:

We can look at the distributions in Datalab using the Python package Seaborn:

As expected, the heavier the rain, the more the distribution curves shift to the right, indicating that flight delays increase.

GHCN data in BigQuery democratizes weather data and opens it up to all sorts of data analytics and machine learning applications. We can’t wait to see how you use this data to build what’s next.

23Sep/160

Google Consumer Surveys Launches Weekly U.S. Election Poll in Google Data Studio

With the U.S. presidential election less than 50 days away, the candidates are running full force to capture as many votes as possible. Worldwide, people are waiting anxiously to see whom the American people will pick as the 45th president. Now more than ever, the media is turning to polls to make sense of all the campaign activity.

Google Consumer Surveys — named as one of the “most accurate [polling] firms” by FiveThirtyEight in the 2012 election — has recently launched its 2016 U.S. Election Poll.

The 2016 election poll surveys more than 20,000 respondents each week from across the United States, demonstrating how Google Consumer Surveys can quickly collect thousands of representative responses. (Find out more about the poll methodology on page 3 of the polling report.)

Google Consumer Surveys, an online market research solution used to gain insights to inform important business decisions, has recently grown its footprint in politics with usage from groups such as NY Times Upshot, IJ Review, and Echelon Insights. Google’s survey platform and other online polling tools have gained popularity due to their accuracy, scalability, quick results, and low costs.

The election poll results from Google Consumer Surveys are displayed in an interactive data visualization in Google Data Studio, and voter preferences are updated weekly. This customized dashboard and report allows users to filter results by state, gender, and candidate to see different cuts of the data — and the report can easily be shared.

Check out the Google Consumer Surveys U.S. Election Poll for weekly updates as the American public gets closer to choosing its next president. Whether in politics or business, surveys are a powerful tool to get the public’s opinion. And spur lively discussions.

Happy Surveying!

More on Google Consumer Surveys

Many users, from small businesses to Fortune 500 companies, use Google Consumer Surveys today to run studies on consumer research, brand tracking, and ad effectiveness. Google also offers an enterprise survey solution with advanced targeting features such as user list and zip code targeting. 

Respondents answer questions on 1,000+ publisher sites to gain access to premium content. The publishers make money each time someone answers a question on their site. Google Consumer Surveys also has an app, Google Opinion Rewards, where people can answer survey questions for Google Play credit. There are over 10M potential respondents available to survey everyday.

Posted by Justin Cohen, Product Marketing Manager, Google Consumer Surveys

22Sep/160

Digging in on Cloud SQL automatic storage increases

Posted by Greg Wilson, Head of Developer Advocacy

There’s a cool new setting in the storage dialog of Cloud SQL Second Generation: “Enable automatic storage increase.” When selected, it checks the available database storage every 30 seconds and adds more capacity as needed in 5GB to 25GB increments, depending on the size of the database. This means that instead of having to provision storage to accommodate future database growth, storage capacity grows as the database grows.

There are two key benefits to Cloud SQL automatic storage increases:

  1. Having a database that grows as needed can reduce application downtime by reducing the risk of running out of database space. You can take the guesswork out of capacity sizing without incurring any downtime or performing database maintenance.
  2. If you're managing a growing database, automatic storage increases can save a considerable amount of money. That’s because allocated database storage grows as needed rather than you having to provision a lot of space upfront. In other words, you pay for only what you use plus a small margin.

According to the documentation, Cloud SQL determines how much capacity to add in the following way: “The size of the threshold and the amount of storage that is added to your instance depends on the amount of storage currently provisioned for your instance, up to a maximum size of 25 GB. The current storage capacity is divided by 25, and the result rounded down to the nearest integer. This result is added to 5 GB to produce both the threshold size and the amount of storage that is added in the event that the available storage falls below the threshold.”

Expressed as a JavaScript formula, that translates to the following (units=GB):

Math.min((Math.floor(currentCapacity/25) + 5),25)

Here’s what that looks like for a few database sizes:

Current capacity
Threshold
Amount auto-added
50GB
7GB
7GB
100GB
9GB
9GB
250GB
15GB
15GB
500GB
25GB
25GB
1000GB
25GB
25GB
5000GB
25GB
25GB

If you already have a database instance running on Cloud SQL Second generation, you can go ahead and turn this feature on now.

21Sep/160

Always know which way you’re headed with this Google Maps update

One of the basic features of the Google Maps app is the ability to open the app and find out which direction you're facing in a matter of seconds. To make orienting yourself even easier in Google Maps for Android, we've replaced the direction arrow on your blue dot with a shining blue beam – think of it as a flashlight guiding your travels.

The beam also tells you how accurate your phone’s direction is at any given time. The narrower the beam, the more accurate the direction. The wider the beam, the more likely it is that your your phone’s compass is temporarily uncalibrated, which means that its sensors aren’t working as they should be. This can happen by doing something as simple as charging your phone or walking by a metal pole, which most of us do everyday. Thankfully, there’s a really easy fix. Any time you want to get back on track – not just when you see a prompt or notification – simply move your phone in a figure 8 motion a few times. This should immediately result in a more accurate direction.

Help_Article_v1.gif

Once you master the curving motion, you’re one step closer to having a more accurate compass when you use Google Maps on your Android phone.

Posted by: Raja Ayyagari, Product Manager, Google Maps

Filed under: Google Maps No Comments
21Sep/160

8 tips to AMPlify your clients

Here is our list of the top 8 things to consider when helping your clients AMPlify their websites (and staying ahead of their curiosity!) after our announcement to expand support for Accelerated Mobile Pages.

  1. Getting started can be simple

If a site uses a popular Content Management System (CMS), getting AMP pages up and running is as straightforward as installing a plug-in. Sites that use custom HTML or that are built from scratch will require additional development resources.

  1. Not all types of sites are suitable

AMP is great for all types of static web content such as news, recipes, movie listings, product pages, reviews, videos, blogs and more. AMP is less useful for single-page apps that are heavy on dynamic or interactive features, such as route mapping, email or social networks.

  1. You don’t have to #AMPlify the whole site

Add AMP to a client's existing site progressively by starting with simple, static content pages like articles, products, or blog posts. These are the “leaf” pages that users access through platforms and search results, and could be simple changes that also bring the benefits of AMP to the website. This approach allows you to keep the homepage and other “browser” pages that might require advanced, non-AMP dynamic functionality.

If you're creating a new, content-heavy website from scratch, consider building the whole site with AMP from the start. To begin with, check out the getting started guidelines.

  1. The AMP Project is open source and still evolving

If a site's use case is not supported in the AMP format yet, consider filing a feature request on GitHub, or you could even design a component yourself.

  1. AMP pages might need to meet additional requirements to show up in certain places

In order to appear in Google’s search results, AMP pages need only be valid AMP HTML. Some products integrating AMP might have further requirements than the AMP validation. For example, you'll need to mark up your AMP pages as Article markup with Structured Data to make them eligible for the Google Top Stories section.

  1. There is no ranking change on Search

Whether a page or site has valid and eligible AMP pages has no bearing on the site’s ranking on the Search results page. The difference is that web results that have AMP versions will be labeled with an icon.

  1. AMP on Google is expanding globally

AMP search results on Google will be rolling out worldwide when it launches in the coming weeks. The Top Stories carousel which shows newsy and fresh AMP content is already available in a number of countries and languages.

  1. Help is on hand

There’s a whole host of useful resources that will help if you have any questions:

Webmasters Help Forum: Ask questions about AMP and Google’s implementation of AMP
Stack Overflow: Ask technical questions about AMP
GitHub: Submit a feature request or contribute

What are your top tips to #AMPlify pages? Let us know in the comments below or on our Google Webmasters Google+ page. Or as usual, if you have any questions or need help, feel free to post in our Webmasters Help Forum.

Posted by Tomo Taylor, AMP Community Manager

21Sep/160

Jackpot: APMEX Doubles New User Revenue with Google Optimize 360

A few months ago we shared a spotlight post on Google Optimize 360 (beta), a new testing and personalization solution in the Google Analytics 360 Suite. Today we’d like to share how one of our customers, APMEX, uses Optimize 360 to deliver an online shopping experience that matches the personal touch its customers get over the phone.

Built with full native integration for all the data that matters to your business, Optimize 360 let the APMEX team use their Analytics 360 goals and audiences to deliver better online experiences for their customers.

APMEX Case Study 

"Investments you hold" is the motto of APMEX, one of the nation's largest precious metals retailers. From the gold Maple Leafs of the Royal Canadian Mint to the platinum bars of Credit Suisse, APMEX offers thousands of bullion and numismatic products for easy sale online.

While APMEX is a large company, its marketing resources are limited. But APMEX works hard to give its online customers a concierge-level customer experience — the same personal experience customers get over the phone. "We refuse to believe that our customers’ experiences should be limited by our resources," says Andrew Duffle, Director of Analytics at APMEX.

APMEX relies on Optimize 360 to help it bring a personal concierge-level touch to its website users. "We test everything," says Duffle. "Creative versus non-creative, the impacts of SEO content on engagement, conversion rate optimization on low-performing pages, new user experiences, and even the price sensitivity of different products."

"One of our goals was to capture conversions on pages that were otherwise being used as educational resources," says Andy Mueller, Manager of Business Intelligence at APMEX. "We thought if people were checking the price of metals, they might respond to offers that really reflected their interests." In one test, new users coming to APMEX to check silver prices were given limited-time offers on United States Silver Eagles. If they're interested enough to check prices, the theory went, they might appreciate a chance to buy.

Before

After
                                                

The results were excellent, says Mueller: "We found the sessions that included an offer resulted in a median rate of 112% more revenue per session, with a 100% probability to beat baseline." The experiment did more than boost revenue: It also increased APMEX’s new customer counts. "We saw a 9% increase in new customer acquisition. Our customers have a long lifespan, so giving up a little margin on the first sale to the right customer is worth it to us."

Some of APMEX’s other tests have also produced astounding results. In one, the team used Analytics 360 to build an audience of people who had put Silver Buffalo coins in their shopping cart and then abandoned the cart. Those who returned to APMEX in the next few days saw the Silver Buffalo first thing on their homepage. As a result, the conversion rate for the coin doubled with this audience. 

"With all the data that Optimize 360 puts at our fingertips, we use it daily to build and evolve our customer relationships," says Duffle.

For more, read the full case study with APMEX.

Posted by Tiffany Siu, Product Marketing Manager, Google Optimize 360

19Sep/160

How to best evaluate issues with your Accelerated Mobile Pages

As you #AMPlify your site with Accelerated Mobile Pages, it’s important to keep an eye periodically on the validation status of your pages, as only valid AMP pages are eligible to show on Google Search.

When implementing AMP, sometimes pages will contain errors causing them to not be indexed by Google Search. Pages may also contain warnings that are elements that are not best practice or are going to become errors in the future.

Google Search Console is a free service that lets you check which of your AMP pages Google has identified as having errors. Once you know which URLs are running into issues, there are a few handy tools that can make checking the validation error details easier.

1. Browser Developer Tools

To use Developer Tools for validation:

  1. Open your AMP page in your browser
  2. Append "#development=1" to the URL, for example, http://localhost:8000/released.amp.html#development=1.
  3. Open the Chrome DevTools console and check for validation errors.

Developer Console errors will look similar to this:


2. AMP Browser Extensions

With the AMP Browser Extensions (available for Chrome and Opera), you can quickly identify and debug invalid AMP pages. As you browse your site, the extension will evaluate each AMP page visited and give an indication of the validity of the page.

Red AMP icon indicating invalid AMP document.

When there are errors within an AMP page, the extension’s icon shows in a red color and displays the number of errors encountered.

Green AMP icon indicating valid AMP document.

When there are no errors within an AMP page, the icon shows in a green color and displays the number of warnings, if any exist.

Blue AMP icon indicating AMP HTML variant if clicked.

When the page isn’t AMP but the page indicates that an AMP version is available, the icon shows in a blue color with a link icon, and clicking on the extension will redirect the browser to the AMP version.

Using the extensions means you can see what errors or warnings the page has by clicking on the extension icon. Every issue will list the source line, source column, and a message indicating what is wrong. When a more detailed description of the issue exists, a “Learn more” link will take you to the relevant page on ampproject.org.


3. AMP Web Validator

The AMP Web Validator, available at validator.ampproject.org, provides a simple web UI to test the validity of your AMP pages.

To use the tool, you enter an AMP URL, or copy/paste your source code, and the web validator displays error messages between the lines. You can make edits directly in the web validator which will trigger revalidation, letting you know if your proposed tweaks will fix the problem.

What's your favourite way to check the status of your AMP Pages? Share your feedback in the comments below or on our Google Webmasters Google+ page. Or as usual, if you have any questions or need help, feel free to post in our Webmasters Help Forum.

Posted by Tomo Taylor, AMP Community Manager

Page 1 of 46312345...102030...Last »