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.
Posted by Gary Illyes, Google Search Ranking Team
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:
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.
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
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.
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
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
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.
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.
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'
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.
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.
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
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:
- 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.
- 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.”
Math.min((Math.floor(currentCapacity/25) + 5),25)
Here’s what that looks like for a few database sizes:
If you already have a database instance running on Cloud SQL Second generation, you can go ahead and turn this feature on now.
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.
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
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.
- 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.
- 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.
- 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.
- The AMP Project is open source and still evolving
- 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.
- 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.
- 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.
- Help is on hand
There’s a whole host of useful resources that will help if you have any questions:
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
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.
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
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:
- Open your AMP page in your browser
- Append "#development=1" to the URL, for example, http://localhost:8000/released.amp.html#development=1.
- 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.
When there are errors within an AMP page, the extension’s icon shows in a red color and displays the number of errors encountered.
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.
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