<jupyter_start><jupyter_text>Conversion Analysis with Google Analytics DataThis sample notebook demonstrates working with Google Analytics page views and session data exported to Google BigQuery.Google Analytics offers BigQuery export as part of its premium offering. If you're a premium user, you have the ability to export any of your analytics views to a BigQuery dataset that you own. If you're not, you can use the Analytics API to retrieve and import the data used to generate the default Analytics dashboards.The sample data used in this notebook shares the same schema as the Google Analytics BigQuery export, but it is from a sample, pubicly available account. It is also small in size. This notebook demonstrates one possible custom analytics scenario, and is not based upon actual data.Related Links:* [BigQuery](https://cloud.google.com/bigquery/what-is-bigquery)* [Google Analytics](https://analytics.google.com/)* [Google Charting API](https://developers.google.com/chart/?hl=en) for data visualization<jupyter_code>import google.datalab.bigquery as bq<jupyter_output><empty_output><jupyter_text>Understanding the Hits DataIt's helpful to inspect the schema and a sample of the data we're working with.<jupyter_code>%%bq tables describe -n "google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910"<jupyter_output><empty_output><jupyter_text>The Google Analytics dataset has a large schema. It should be interesting to inspect some of the data in important columns.<jupyter_code>%%bq query -n sessions 
SELECT fullVisitorId, visitId, hit.hitNumber as hitNumber, hit.page.pagePath as path
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`
  CROSS JOIN UNNEST(hits) as hit
ORDER BY visitStartTime, hitNumber
%bq execute --query sessions<jupyter_output><empty_output><jupyter_text>The data is organized as a set of visits (or sessions), with each visit containing a set of hits (or page views), in succession. Each hit has a URL path associated with it. Here is another query that shows paths and the number of hits across sessions.<jupyter_code>%%bq query -n hits
SELECT hit.page.pagePath as path, COUNT(visitId) as hitCount
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`
  CROSS JOIN UNNEST(hits) as hit
GROUP BY path
ORDER BY hitCount DESC
%%bq execute -q hits<jupyter_output><empty_output><jupyter_text>Producing Conversion DataFor the purposes of this sample, the question to be answered is "Which path leads to higher conversion ratio: users landing on the landing page (path = '/') or users landing on a product page (eg. '/vests/yellow.html')?" "Conversion" is defined as the user loading the '/confirm/' page within a single session.<jupyter_code>%%bq query -n conversions
WITH
  AnnotatedVisits AS (
    SELECT
      visitId,
      hit.page.pagePath AS path,
      hit.hitNumber AS hitNumber,
      '/confirm.html' IN (SELECT page.pagePath FROM UNNEST(hits)) AS transacted
    FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`
      CROSS JOIN UNNEST(hits) AS hit
    ORDER BY visitStartTime, hitNumber)
SELECT
  IF (path = '/', 'home', 'product') AS start,
  IF (transacted, 'completed', 'abandoned') AS outcome,
  COUNT(*) AS count
FROM AnnotatedVisits
WHERE hitNumber = 1
GROUP BY start, outcome
ORDER BY outcome, start
%%bq execute -q conversions<jupyter_output><empty_output><jupyter_text>Visualizing the Conversion PathThe matrix tells us a bit about completed visits vs. abandoned visits, depending on the starting point. However, this is more easily seen in a [sankey diagram](https://developers.google.com/chart/interactive/docs/gallery/sankey), which is provided by the Google Charting API.<jupyter_code>%%chart sankey --data conversions
{
  "sankey": {
    "node": {
      "colors": [ "black", "red", "black", "green" ]
    }
  }
}<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08003088008234689, 'fraction_numerical': 0.008492022645393721, 'mean_word_length': 5.269354838709678, 'pattern_counts': {'":': 3, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '198547', 'license': 'Apache-2.0', 'n_tokens_mistral': 1162, 'n_tokens_neox': 1101, 'n_words': 442, 'path': 'docs/samples/Conversion Analysis with Google Analytics Data.ipynb', 'repo_name': 'JeffersonH44/datalab-r', 'size': '3886'}