{"id":894,"date":"2024-08-05T10:57:50","date_gmt":"2024-08-05T14:57:50","guid":{"rendered":"https:\/\/www.econai.tech\/?page_id=894"},"modified":"2026-05-06T08:52:38","modified_gmt":"2026-05-06T12:52:38","slug":"user-engagement-analysis","status":"publish","type":"page","link":"https:\/\/tomomitanaka.ai\/?page_id=894","title":{"rendered":"Deep Dive into User Engagement Analysis"},"content":{"rendered":"\n<p>In my previous post, I covered basic metrics that define user behavior, such as total visits, average page views, and traffic source distribution. <\/p>\n\n\n\n<p>While these metrics are essential for understanding overall site performance, advanced engagement analysis can provide deeper insights into how users interact with your site. <\/p>\n\n\n\n<p>In this post, we&#8217;ll look at more sophisticated engagement metrics using the <a href=\"https:\/\/support.google.com\/analytics\/answer\/7586738?hl=en#zippy=%2Cin-this-article\">Google Analytics Sample Dataset in BigQuery<\/a>. <\/p>\n\n\n\n<p>We&#8217;ll explore engagement depth, content engagement, user flow, event-based engagement, and time to purchase.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1. Engagement Depth Analysis<\/h3>\n\n\n\n<p>Understanding how deeply users engage with your site can help identify the correlation between engagement and conversion rates. We&#8217;ll analyze sessions based on the number of page views.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Depth of Engagement<\/strong>: Examine how the number of pages viewed per session relates to session duration and conversion rate.<\/li>\n\n\n\n<li><strong>Insights<\/strong>: Sessions with higher pageviews tend to have longer durations and higher conversion rates, indicating more engaged users are more likely to convert.<\/li>\n<\/ul>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">SQL<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT\n  CASE \n    WHEN totals.pageviews = 1 THEN '1 page'\n    WHEN totals.pageviews BETWEEN 2 AND 5 THEN '2-5 pages'\n    WHEN totals.pageviews BETWEEN 6 AND 10 THEN '6-10 pages'\n    ELSE '10+ pages'\n  END AS pageview_bucket,\n  COUNT(*) AS sessions,\n  AVG(totals.timeOnSite) AS avg_session_duration,\n  ROUND(100 * COUNTIF(totals.transactions &gt; 0) \/ COUNT(*), 2) AS conversion_rate\nFROM\n  `bigquery-public-data.google_analytics_sample.ga_sessions_*`\nWHERE\n  _TABLE_SUFFIX BETWEEN '20170101' AND '20170331'\nGROUP BY\n  pageview_bucket\nORDER BY\n  sessions DESC;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">CASE<\/span><span style=\"color: #D4D4D4\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">WHEN<\/span><span style=\"color: #D4D4D4\"> totals.pageviews = <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">THEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1 page&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">WHEN<\/span><span style=\"color: #D4D4D4\"> totals.pageviews <\/span><span style=\"color: #569CD6\">BETWEEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">5<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">THEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;2-5 pages&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">WHEN<\/span><span style=\"color: #D4D4D4\"> totals.pageviews <\/span><span style=\"color: #569CD6\">BETWEEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">6<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">10<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">THEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;6-10 pages&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">ELSE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;10+ pages&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">END<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> pageview_bucket,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">sessions<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">AVG<\/span><span style=\"color: #D4D4D4\">(totals.timeOnSite) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> avg_session_duration,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">ROUND<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">100<\/span><span style=\"color: #D4D4D4\"> * COUNTIF(totals.transactions &gt; <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">) \/ <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*), <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> conversion_rate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_*`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">WHERE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  _TABLE_SUFFIX <\/span><span style=\"color: #569CD6\">BETWEEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170101&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170331&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">GROUP BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  pageview_bucket<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">sessions<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">DESC<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Explanation<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>pageview_bucket<\/strong>: Categorizes sessions based on the number of pages viewed.<\/li>\n\n\n\n<li><strong>sessions<\/strong>: Counts the number of sessions in each pageview bucket.<\/li>\n\n\n\n<li><strong>avg_session_duration<\/strong>: Calculates the average duration of sessions in each bucket.<\/li>\n\n\n\n<li><strong>conversion_rate<\/strong>: Computes the conversion rate for each pageview bucket by dividing the number of transactions by the number of sessions and multiplying by 100.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">2. Content Engagement Analysis<\/h3>\n\n\n\n<p>Identifying top-performing and underperforming content can help optimize your site.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Top-Performing Content<\/strong>: Identify pages with the highest pageviews and longest average time on page.<\/li>\n\n\n\n<li><strong>Underperforming Content<\/strong>: Detect pages with high exit rates to find areas needing improvement.<\/li>\n<\/ul>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">SQL<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT\n  hits.page.pagePath,\n  COUNT(*) AS pageviews,\n  AVG(hits.time) AS avg_time_on_page,\n  SUM(CAST(hits.isExit AS INT64)) AS exits,\n  ROUND(100 * SUM(CAST(hits.isExit AS INT64)) \/ COUNT(*), 2) AS exit_rate\nFROM\n  `bigquery-public-data.google_analytics_sample.ga_sessions_*`,\n  UNNEST(hits) AS hits\nWHERE\n  _TABLE_SUFFIX BETWEEN '20170101' AND '20170331'\n  AND hits.type = 'PAGE'\nGROUP BY\n  hits.page.pagePath\nORDER BY\n  pageviews DESC\nLIMIT 20;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  hits.page.pagePath,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> pageviews,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">AVG<\/span><span style=\"color: #D4D4D4\">(hits.time) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> avg_time_on_page,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #DCDCAA\">CAST<\/span><span style=\"color: #D4D4D4\">(hits.isExit <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> INT64)) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> exits,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">ROUND<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">100<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #DCDCAA\">CAST<\/span><span style=\"color: #D4D4D4\">(hits.isExit <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> INT64)) \/ <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*), <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> exit_rate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_*`<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  UNNEST(hits) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> hits<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">WHERE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  _TABLE_SUFFIX <\/span><span style=\"color: #569CD6\">BETWEEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170101&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170331&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> hits.type = <\/span><span style=\"color: #CE9178\">&#39;PAGE&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">GROUP BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  hits.page.pagePath<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  pageviews <\/span><span style=\"color: #569CD6\">DESC<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">LIMIT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Explanation<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>pagePath<\/strong>: The URL path of the page.<\/li>\n\n\n\n<li><strong>pageviews<\/strong>: Counts the number of pageviews for each page.<\/li>\n\n\n\n<li><strong>avg_time_on_page<\/strong>: Calculates the average time users spend on each page.<\/li>\n\n\n\n<li><strong>exits<\/strong>: Counts the number of times users exited from each page.<\/li>\n\n\n\n<li><strong>exit_rate<\/strong>: Computes the exit rate for each page by dividing the number of exits by the number of pageviews and multiplying by 100.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">3. User Flow Analysis<\/h3>\n\n\n\n<p>Understanding the common user paths through your site can help optimize navigation.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Common User Paths<\/strong>: Identify the most frequent page sequences to understand user navigation.<\/li>\n\n\n\n<li><strong>Optimization Opportunities<\/strong>: Improve user flow by enhancing navigation and linking related content.<\/li>\n<\/ul>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">SQL<\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH page_sequence AS (\n  SELECT\n    fullVisitorId,\n    visitId,\n    hits.page.pagePath,\n    hits.hitNumber,\n    LEAD(hits.page.pagePath) OVER (PARTITION BY fullVisitorId, visitId ORDER BY hits.hitNumber) AS next_page\n  FROM\n    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,\n    UNNEST(hits) AS hits\n  WHERE\n    _TABLE_SUFFIX BETWEEN '20170101' AND '20170331'\n    AND hits.type = 'PAGE'\n)\nSELECT\n  pagePath AS current_page,\n  next_page,\n  COUNT(*) AS frequency\nFROM\n  page_sequence\nWHERE\n  next_page IS NOT NULL\nGROUP BY\n  current_page, next_page\nORDER BY\n  frequency DESC\nLIMIT 20;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">WITH<\/span><span style=\"color: #D4D4D4\"> page_sequence <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    fullVisitorId,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    visitId,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    hits.page.pagePath,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    hits.hitNumber,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #DCDCAA\">LEAD<\/span><span style=\"color: #D4D4D4\">(hits.page.pagePath) <\/span><span style=\"color: #569CD6\">OVER<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">PARTITION<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">BY<\/span><span style=\"color: #D4D4D4\"> fullVisitorId, visitId <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> hits.hitNumber) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> next_page<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_*`<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    UNNEST(hits) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> hits<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">WHERE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    _TABLE_SUFFIX <\/span><span style=\"color: #569CD6\">BETWEEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170101&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170331&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> hits.type = <\/span><span style=\"color: #CE9178\">&#39;PAGE&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  pagePath <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> current_page,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  next_page,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> frequency<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  page_sequence<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">WHERE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  next_page <\/span><span style=\"color: #569CD6\">IS NOT NULL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">GROUP BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  current_page, next_page<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  frequency <\/span><span style=\"color: #569CD6\">DESC<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">LIMIT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Explanation<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>page_sequence<\/strong>: Creates a sequence of pages visited within each session.<\/li>\n\n\n\n<li><strong>current_page<\/strong>: The current page in the sequence.<\/li>\n\n\n\n<li><strong>next_page<\/strong>: The page that follows the current page in the sequence.<\/li>\n\n\n\n<li><strong>frequency<\/strong>: Counts the number of times users navigate from the current page to the next page.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">4. Event-Based Engagement Analysis<\/h3>\n\n\n\n<p>Analyze user interactions with specific site elements.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Popular Features<\/strong>: Identify the most frequent events to understand user interactions.<\/li>\n\n\n\n<li><strong>Improvement Areas<\/strong>: Detect less-used features to identify potential areas for improvement.<\/li>\n<\/ul>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">SQL<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT\n  hits.eventInfo.eventCategory,\n  hits.eventInfo.eventAction,\n  COUNT(*) AS event_count\nFROM\n  `bigquery-public-data.google_analytics_sample.ga_sessions_*`,\n  UNNEST(hits) AS hits\nWHERE\n  _TABLE_SUFFIX BETWEEN '20170101' AND '20170331'\n  AND hits.type = 'EVENT'\nGROUP BY\n  hits.eventInfo.eventCategory,\n  hits.eventInfo.eventAction\nORDER BY\n  event_count DESC\nLIMIT 20;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  hits.eventInfo.eventCategory,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  hits.eventInfo.eventAction,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> event_count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_*`<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  UNNEST(hits) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> hits<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">WHERE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  _TABLE_SUFFIX <\/span><span style=\"color: #569CD6\">BETWEEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170101&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170331&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> hits.type = <\/span><span style=\"color: #CE9178\">&#39;EVENT&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">GROUP BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  hits.eventInfo.eventCategory,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  hits.eventInfo.eventAction<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  event_count <\/span><span style=\"color: #569CD6\">DESC<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">LIMIT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">20<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Explanation<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>eventCategory<\/strong>: The category of the event.<\/li>\n\n\n\n<li><strong>eventAction<\/strong>: The action of the event.<\/li>\n\n\n\n<li><strong>event_count<\/strong>: Counts the number of occurrences of each event.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">5. Time to Purchase Analysis<\/h3>\n\n\n\n<p>Analyze the time it takes for users to make a purchase.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Time to Purchase<\/strong>: Understand the distribution of time it takes for users to make a purchase.<\/li>\n\n\n\n<li><strong>Optimization<\/strong>: Develop strategies to shorten the purchase funnel and reduce time to purchase.<\/li>\n<\/ul>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">SQL<\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH purchase_sessions AS (\n  SELECT\n    fullVisitorId,\n    visitId,\n    MAX(hits.time) \/ 1000 AS time_to_purchase\n  FROM\n    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,\n    UNNEST(hits) AS hits\n  WHERE\n    _TABLE_SUFFIX BETWEEN '20170101' AND '20170331'\n    AND totals.transactions &gt; 0\n  GROUP BY\n    fullVisitorId, visitId\n)\nSELECT\n  CASE\n    WHEN time_to_purchase &lt; 60 THEN '&lt; 1 min'\n    WHEN time_to_purchase &lt; 300 THEN '1-5 mins'\n    WHEN time_to_purchase &lt; 900 THEN '5-15 mins'\n    ELSE '15+ mins'\n  END AS time_bucket,\n  COUNT(*) AS purchase_count,\n  AVG(time_to_purchase) AS avg_time_to_purchase\nFROM\n  purchase_sessions\nGROUP BY\n  time_bucket\nORDER BY\n  purchase_count DESC;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">WITH<\/span><span style=\"color: #D4D4D4\"> purchase_sessions <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    fullVisitorId,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    visitId,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #DCDCAA\">MAX<\/span><span style=\"color: #D4D4D4\">(hits.time) \/ <\/span><span style=\"color: #B5CEA8\">1000<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> time_to_purchase<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_*`<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    UNNEST(hits) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> hits<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">WHERE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    _TABLE_SUFFIX <\/span><span style=\"color: #569CD6\">BETWEEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170101&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;20170331&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> totals.transactions &gt; <\/span><span style=\"color: #B5CEA8\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">GROUP BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    fullVisitorId, visitId<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">CASE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">WHEN<\/span><span style=\"color: #D4D4D4\"> time_to_purchase &lt; <\/span><span style=\"color: #B5CEA8\">60<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">THEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;&lt; 1 min&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">WHEN<\/span><span style=\"color: #D4D4D4\"> time_to_purchase &lt; <\/span><span style=\"color: #B5CEA8\">300<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">THEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1-5 mins&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">WHEN<\/span><span style=\"color: #D4D4D4\"> time_to_purchase &lt; <\/span><span style=\"color: #B5CEA8\">900<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">THEN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;5-15 mins&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">ELSE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;15+ mins&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">END<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> time_bucket,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> purchase_count,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">AVG<\/span><span style=\"color: #D4D4D4\">(time_to_purchase) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> avg_time_to_purchase<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  purchase_sessions<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">GROUP BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  time_bucket<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  purchase_count <\/span><span style=\"color: #569CD6\">DESC<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Explanation<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>purchase_sessions<\/strong>: Identifies sessions with transactions and calculates the time to purchase for each session.<\/li>\n\n\n\n<li><strong>time_bucket<\/strong>: Categorizes sessions based on the time taken to make a purchase.<\/li>\n\n\n\n<li><strong>purchase_count<\/strong>: Counts the number of purchases in each time bucket.<\/li>\n\n\n\n<li><strong>avg_time_to_purchase<\/strong>: Calculates the average time to purchase in each bucket.<\/li>\n<\/ul>\n\n\n\n<p>You can find&nbsp;<a href=\"https:\/\/github.com\/tomomitanaka00\/Blog-SQL\/blob\/main\/Section2.sql\">the complete code<\/a> in&nbsp;my GitHub repository.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Results<\/strong><\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">1. Engagement Depth Analysis<\/h4>\n\n\n\n<p>The analysis of engagement depth categorizes sessions based on the number of page views per session and provided insights into session duration and conversion rates for each category.<\/p>\n\n\n\n<p>The data shows a clear correlation between the depth of engagement and session duration, as well as conversion rates. Users who viewed more pages per session had significantly longer session durations and higher conversion rates.<\/p>\n\n\n\n<p>Notably, sessions with more than 10 page views had an average duration of over 13 minutes and a conversion rate of 14.19%, indicating that deeply engaged users are more likely to convert.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td>Pageview Bucket<\/td><td class=\"has-text-align-right\" data-align=\"right\">Sessions<\/td><td class=\"has-text-align-right\" data-align=\"right\">Avg. Session Duration (seconds)<\/td><td class=\"has-text-align-right\" data-align=\"right\">Conversion Rate (%)<\/td><\/tr><tr><td>1 page<\/td><td class=\"has-text-align-right\" data-align=\"right\">98,842<\/td><td class=\"has-text-align-right\" data-align=\"right\">62.10<\/td><td class=\"has-text-align-right\" data-align=\"right\">0.00<\/td><\/tr><tr><td>2-5 pages<\/td><td class=\"has-text-align-right\" data-align=\"right\">63,785<\/td><td class=\"has-text-align-right\" data-align=\"right\">127.58<\/td><td class=\"has-text-align-right\" data-align=\"right\">0.01<\/td><\/tr><tr><td>6-10 pages<\/td><td class=\"has-text-align-right\" data-align=\"right\">18,794<\/td><td class=\"has-text-align-right\" data-align=\"right\">320.01<\/td><td class=\"has-text-align-right\" data-align=\"right\">0.53<\/td><\/tr><tr><td>10+ pages<\/td><td class=\"has-text-align-right\" data-align=\"right\">15,396<\/td><td class=\"has-text-align-right\" data-align=\"right\">804.08<\/td><td class=\"has-text-align-right\" data-align=\"right\">14.19<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">2. Content Engagement Analysis<\/h4>\n\n\n\n<p>This analysis focuses on identifying top-performing and underperforming content by evaluating pageviews, average time on page, and exit rates.<\/p>\n\n\n\n<p>The homepage (\/home) has the highest number of pageviews but also a high exit rate of 42.32%, indicating that while many users land on the homepage, a large proportion leave without further interaction. <\/p>\n\n\n\n<p>Pages related to &#8220;google+redesign&#8221; showe high engagement with long average time on page, but some also have high exit rates, suggesting potential areas for content improvement or user flow optimization. <\/p>\n\n\n\n<p>For example, the &#8220;shop+by+brand\/youtube&#8221; page has a high exit rate of 43.74%, indicating that users may not find what they are looking for or that the page could be optimized further.<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular\"><table class=\"has-fixed-layout\"><tbody><tr><td>Page Path<\/td><td class=\"has-text-align-right\" data-align=\"right\">Pageviews<\/td><td class=\"has-text-align-right\" data-align=\"right\">Avg. Time<br>on Page<br>(seconds)<\/td><td class=\"has-text-align-right\" data-align=\"right\">Exits<\/td><td class=\"has-text-align-right\" data-align=\"right\">Exit Rate (%)<\/td><\/tr><tr><td>\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">201,478<\/td><td class=\"has-text-align-right\" data-align=\"right\">78,501<\/td><td class=\"has-text-align-right\" data-align=\"right\">85,267<\/td><td class=\"has-text-align-right\" data-align=\"right\">42.3<\/td><\/tr><tr><td>\/basket.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">49,173<\/td><td class=\"has-text-align-right\" data-align=\"right\">571,742<\/td><td class=\"has-text-align-right\" data-align=\"right\">5,860<\/td><td class=\"has-text-align-right\" data-align=\"right\">11.9<\/td><\/tr><tr><td>\/google+redesign\/shop+by+brand\/youtube<\/td><td class=\"has-text-align-right\" data-align=\"right\">39,801<\/td><td class=\"has-text-align-right\" data-align=\"right\">92,584<\/td><td class=\"has-text-align-right\" data-align=\"right\">17,408<\/td><td class=\"has-text-align-right\" data-align=\"right\">43.7<\/td><\/tr><tr><td>\/signin.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">22,229<\/td><td class=\"has-text-align-right\" data-align=\"right\">272,627<\/td><td class=\"has-text-align-right\" data-align=\"right\">4,232<\/td><td class=\"has-text-align-right\" data-align=\"right\">19.0<\/td><\/tr><tr><td>\/store.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">15,669<\/td><td class=\"has-text-align-right\" data-align=\"right\">423,165<\/td><td class=\"has-text-align-right\" data-align=\"right\">2,079<\/td><td class=\"has-text-align-right\" data-align=\"right\">13.3<\/td><\/tr><tr><td>\/google+redesign\/apparel\/men++s\/men++s+t+shirts<\/td><td class=\"has-text-align-right\" data-align=\"right\">15,127<\/td><td class=\"has-text-align-right\" data-align=\"right\">182,574<\/td><td class=\"has-text-align-right\" data-align=\"right\">4,796<\/td><td class=\"has-text-align-right\" data-align=\"right\">31.7<\/td><\/tr><tr><td>\/asearch.html<\/td><td class=\"has-text-align-right\" data-align=\"right\"><br>14,770<\/td><td class=\"has-text-align-right\" data-align=\"right\">238,278<\/td><td class=\"has-text-align-right\" data-align=\"right\">4,928<\/td><td class=\"has-text-align-right\" data-align=\"right\"><br>33.4<\/td><\/tr><tr><td>\/google+redesign\/apparel\/men++s\/men++s+outerwear<\/td><td class=\"has-text-align-right\" data-align=\"right\">13,688<\/td><td class=\"has-text-align-right\" data-align=\"right\">183,383<\/td><td class=\"has-text-align-right\" data-align=\"right\">3,146<\/td><td class=\"has-text-align-right\" data-align=\"right\">23.0<\/td><\/tr><tr><td>\/google+redesign\/shop+by+brand\/google<\/td><td class=\"has-text-align-right\" data-align=\"right\">12,453<\/td><td class=\"has-text-align-right\" data-align=\"right\">221,810<\/td><td class=\"has-text-align-right\" data-align=\"right\">3,476<\/td><td class=\"has-text-align-right\" data-align=\"right\">27.9<\/td><\/tr><tr><td>\/google+redesign\/apparel<\/td><td class=\"has-text-align-right\" data-align=\"right\">10,672<\/td><td class=\"has-text-align-right\" data-align=\"right\">247,376<\/td><td class=\"has-text-align-right\" data-align=\"right\">2,192<\/td><td class=\"has-text-align-right\" data-align=\"right\">20.5<\/td><\/tr><tr><td>\/google+redesign\/bags\/backpacks\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">10,139<\/td><td class=\"has-text-align-right\" data-align=\"right\">252,731<\/td><td class=\"has-text-align-right\" data-align=\"right\">1,436<\/td><td class=\"has-text-align-right\" data-align=\"right\">14.2<\/td><\/tr><tr><td>\/google+redesign\/electronics<\/td><td class=\"has-text-align-right\" data-align=\"right\">8,986<\/td><td class=\"has-text-align-right\" data-align=\"right\">260,675<\/td><td class=\"has-text-align-right\" data-align=\"right\">2,306<\/td><td class=\"has-text-align-right\" data-align=\"right\">25.7<\/td><\/tr><tr><td>\/google+redesign\/bags<\/td><td class=\"has-text-align-right\" data-align=\"right\">8,923<\/td><td class=\"has-text-align-right\" data-align=\"right\">264,973<\/td><td class=\"has-text-align-right\" data-align=\"right\">1,992<\/td><td class=\"has-text-align-right\" data-align=\"right\">22.3<\/td><\/tr><tr><td>\/google+redesign\/accessories<\/td><td class=\"has-text-align-right\" data-align=\"right\">8,549<\/td><td class=\"has-text-align-right\" data-align=\"right\">311,126<\/td><td class=\"has-text-align-right\" data-align=\"right\">1,449<\/td><td class=\"has-text-align-right\" data-align=\"right\">17.0<\/td><\/tr><tr><td>\/google+redesign\/accessories\/fun<\/td><td class=\"has-text-align-right\" data-align=\"right\">8,215<\/td><td class=\"has-text-align-right\" data-align=\"right\">341,145<\/td><td class=\"has-text-align-right\" data-align=\"right\">989<\/td><td class=\"has-text-align-right\" data-align=\"right\">12.0<\/td><\/tr><tr><td>\/yourinfo.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">8,176<\/td><td class=\"has-text-align-right\" data-align=\"right\">812,068<\/td><td class=\"has-text-align-right\" data-align=\"right\">920<\/td><td class=\"has-text-align-right\" data-align=\"right\">11.3<\/td><\/tr><tr><td>\/google+redesign\/drinkware<\/td><td class=\"has-text-align-right\" data-align=\"right\">7,633<\/td><td class=\"has-text-align-right\" data-align=\"right\">248,357<\/td><td class=\"has-text-align-right\" data-align=\"right\">2,219<\/td><td class=\"has-text-align-right\" data-align=\"right\">29.1<\/td><\/tr><tr><td>\/google+redesign\/accessories\/stickers\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">7,273<\/td><td class=\"has-text-align-right\" data-align=\"right\">283,394<\/td><td class=\"has-text-align-right\" data-align=\"right\">1,198<\/td><td class=\"has-text-align-right\" data-align=\"right\">16.5<\/td><\/tr><tr><td>\/google+redesign\/shop+by+brand\/youtube\/quickview<\/td><td class=\"has-text-align-right\" data-align=\"right\">6,856<\/td><td class=\"has-text-align-right\" data-align=\"right\">205,615<\/td><td class=\"has-text-align-right\" data-align=\"right\">892<\/td><td class=\"has-text-align-right\" data-align=\"right\">13.0<\/td><\/tr><tr><td>\/payment.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">6,774<\/td><td class=\"has-text-align-right\" data-align=\"right\">1,006,741<\/td><td class=\"has-text-align-right\" data-align=\"right\">531<\/td><td class=\"has-text-align-right\" data-align=\"right\">7.8<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">3. User Flow Analysis<\/h4>\n\n\n\n<p>The user flow analysis shows the most common paths users take on the site, which can be critical for understanding how to improve site navigation and user experience. <\/p>\n\n\n\n<p>For instance, many users transition from the homepage to revisiting the homepage or exploring product categories like &#8220;shop+by+brand\/youtube&#8221; and &#8220;shop+by+brand\/google.&#8221; <\/p>\n\n\n\n<p>The frequent navigation from &#8220;basket.html&#8221; to &#8220;signin.html&#8221; and then to &#8220;yourinfo.html&#8221; suggests that users are actively moving through the checkout process. <\/p>\n\n\n\n<p>Enhancing these commonly traversed paths can help streamline user journeys and improve overall site usability. <\/p>\n\n\n\n<p>Understanding these common pathways allows for targeted improvements to keep users engaged and reduce drop-off rates.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular\"><table class=\"has-white-background-color has-background has-fixed-layout\"><tbody><tr><td>Current Page<\/td><td class=\"has-text-align-right\" data-align=\"right\">Next Page<\/td><td class=\"has-text-align-right\" data-align=\"right\">Frequency<\/td><\/tr><tr><td>\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">45,199<\/td><\/tr><tr><td>\/basket.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/basket.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">9,407<\/td><\/tr><tr><td>\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/google+redesign\/shop+by+brand\/youtube<\/td><td class=\"has-text-align-right\" data-align=\"right\">7,700<\/td><\/tr><tr><td>\/basket.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/signin.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">6,648<\/td><\/tr><tr><td>\/google+redesign\/shop+by+brand\/youtube<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">6,227<\/td><\/tr><tr><td>\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/google+redesign\/shop+by+brand\/google<\/td><td class=\"has-text-align-right\" data-align=\"right\">6,174<\/td><\/tr><tr><td>\/basket.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/yourinfo.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">5,322<\/td><\/tr><tr><td>\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/google+redesign\/apparel\/men++s\/men++s+outerwear<\/td><td class=\"has-text-align-right\" data-align=\"right\">5,164<\/td><\/tr><tr><td>\/signin.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/signin.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">4,582<\/td><\/tr><tr><td>\/yourinfo.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/payment.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">4,440<\/td><\/tr><tr><td>\/myaccount.html?mode=vieworder<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/myaccount.html?mode=vieworderdetail<\/td><td class=\"has-text-align-right\" data-align=\"right\">4,018<\/td><\/tr><tr><td>\/google+redesign\/shop+by+brand\/youtube<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/google+redesign\/shop+by+brand\/youtube\/quickview<\/td><td class=\"has-text-align-right\" data-align=\"right\">3,950<\/td><\/tr><tr><td>\/signin.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/registersuccess.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">3,915<\/td><\/tr><tr><td>\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/asearch.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">3,698<\/td><\/tr><tr><td>\/registersuccess.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/store.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">3,651<\/td><\/tr><tr><td>\/basket.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/store.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">3,383<\/td><\/tr><tr><td>\/payment.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/revieworder.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">3,136<\/td><\/tr><tr><td>\/asearch.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/asearch.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">2,905<\/td><\/tr><tr><td>\/google+redesign\/bags\/backpacks\/home<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/google+redesign\/bags\/backpacks\/\/quickview<\/td><td class=\"has-text-align-right\" data-align=\"right\">2,896<\/td><\/tr><tr><td>\/store.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">\/basket.html<\/td><td class=\"has-text-align-right\" data-align=\"right\">2,734<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">4. Event-Based Engagement Analysis<\/h4>\n\n\n\n<p>This analysis focuses on user interactions with specific site elements by evaluating the frequency of different events.<\/p>\n\n\n\n<p>Event-based engagement analysis reveals that &#8220;Quickview Click&#8221; and &#8220;Add to Cart&#8221; are the most frequent actions, indicating high user interest in viewing product details and adding items to their cart. <\/p>\n\n\n\n<p>The significant number of &#8220;Remove from Cart&#8221; events suggests that users may reconsider their choices, highlighting a potential area to investigate for improving cart retention and reducing abandonment rates. <\/p>\n\n\n\n<p>Understanding these interactions can help refine features to better meet user needs and preferences.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td>Event Category<\/td><td class=\"has-text-align-right\" data-align=\"right\">Event Action<\/td><td class=\"has-text-align-right\" data-align=\"right\">Event Count<\/td><\/tr><tr><td>Enhanced Ecommerce<\/td><td class=\"has-text-align-right\" data-align=\"right\">Quickview Click<\/td><td class=\"has-text-align-right\" data-align=\"right\">70,047<\/td><\/tr><tr><td>Enhanced Ecommerce<\/td><td class=\"has-text-align-right\" data-align=\"right\">Add to Cart<\/td><td class=\"has-text-align-right\" data-align=\"right\">23,478<\/td><\/tr><tr><td>Enhanced Ecommerce<\/td><td class=\"has-text-align-right\" data-align=\"right\">Product Click<\/td><td class=\"has-text-align-right\" data-align=\"right\">15,519<\/td><\/tr><tr><td>null<\/td><td class=\"has-text-align-right\" data-align=\"right\">null<\/td><td class=\"has-text-align-right\" data-align=\"right\">7,155<\/td><\/tr><tr><td>Enhanced Ecommerce<\/td><td class=\"has-text-align-right\" data-align=\"right\">Remove from Cart<\/td><td class=\"has-text-align-right\" data-align=\"right\">2,968<\/td><\/tr><tr><td>Contact Us<\/td><td class=\"has-text-align-right\" data-align=\"right\">Onsite Click<\/td><td class=\"has-text-align-right\" data-align=\"right\">1,481<\/td><\/tr><tr><td>Enhanced Ecommerce<\/td><td class=\"has-text-align-right\" data-align=\"right\">Promotion Click<\/td><td class=\"has-text-align-right\" data-align=\"right\">17<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">5. Time to Purchase Analysis<\/h4>\n\n\n\n<p>This analysis examined the time it takes for users to make a purchase.<\/p>\n\n\n\n<p>The time to purchase analysis shows that most users make purchases within 5-15 minutes of their session, followed by those taking longer than 15 minutes.<\/p>\n\n\n\n<p>This suggests that while many users make quick decisions, a substantial number require more time to decide, indicating opportunities to streamline the purchase process and reduce decision time for users.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>Time Bucket<\/td><td class=\"has-text-align-right\" data-align=\"right\">Purchase Count<\/td><td class=\"has-text-align-right\" data-align=\"right\">Avg. Time to Purchase (seconds)<\/td><\/tr><tr><td>5-15 mins<\/td><td class=\"has-text-align-right\" data-align=\"right\">1,084<\/td><td class=\"has-text-align-right\" data-align=\"right\">573.0<\/td><\/tr><tr><td>15+ mins<\/td><td class=\"has-text-align-right\" data-align=\"right\">963<\/td><td class=\"has-text-align-right\" data-align=\"right\">1,795.8<\/td><\/tr><tr><td>1-5 mins<\/td><td class=\"has-text-align-right\" data-align=\"right\">240<\/td><td class=\"has-text-align-right\" data-align=\"right\">223.3<\/td><\/tr><tr><td>&lt; 1 min<\/td><td class=\"has-text-align-right\" data-align=\"right\">1<\/td><td class=\"has-text-align-right\" data-align=\"right\">0.0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<p>Advanced engagement analysis provides a comprehensive understanding of how users interact with an e-commerce site. <\/p>\n\n\n\n<p>By examining deeper metrics such as engagement depth, content engagement, user flow, event interactions, and time to purchase, we can gain valuable insights that go beyond basic metrics. <\/p>\n\n\n\n<p>These insights enable more targeted and effective strategies to enhance user experience, optimize site performance, and ultimately drive higher conversion rates.<\/p>\n\n\n\n<p>Implementing these findings can lead to a more engaging, user-friendly website that not only attracts visitors but also retains them and encourages them to complete their transactions. <\/p>\n\n\n\n<p>As the digital landscape continues to evolve, ongoing analysis and optimization are crucial for maintaining a competitive edge and meeting the ever-changing needs of users.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my previous post, I covered basic metrics that define user behavior, such as total visits, average page views, and traffic source distribution. While these metrics are essential for understanding overall site performance, advanced engagement analysis can provide deeper insights into how users interact with your site. In this post, we#8217;ll look at more sophisticated<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":877,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-894","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/894","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=894"}],"version-history":[{"count":113,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/894\/revisions"}],"predecessor-version":[{"id":6367,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/894\/revisions\/6367"}],"up":[{"embeddable":true,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/877"}],"wp:attachment":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=894"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}