{"id":881,"date":"2024-08-05T09:10:08","date_gmt":"2024-08-05T13:10:08","guid":{"rendered":"https:\/\/www.econai.tech\/?page_id=881"},"modified":"2026-05-06T08:52:19","modified_gmt":"2026-05-06T12:52:19","slug":"analyzing-user-behavior-on-an-e-commerce-site","status":"publish","type":"page","link":"https:\/\/tomomitanaka.ai\/?page_id=881","title":{"rendered":"Analyzing User Behavior on an E-commerce Site"},"content":{"rendered":"\n<p>Understanding user behavior on an e-commerce site is crucial for optimizing user experience, improving engagement, and increasing conversions. <\/p>\n\n\n\n<p>In this post, we will dive into foundational metrics that define user behavior 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 will explore key metrics such as total visits, average page views, traffic source distribution, bounce rates, session durations, and engagement by device type and geographical location.<\/p>\n\n\n\n<p><strong>Prerequisites<\/strong>:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Access to Google Cloud Platform and BigQuery.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Exploring Foundational Metrics<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Total Visits<\/h4>\n\n\n\n<p>The total number of visits to your e-commerce site is a fundamental metric that provides an overview of site traffic.<\/p>\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 COUNT(DISTINCT visitId) AS total_visits\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;\" 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 style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #569CD6\">DISTINCT<\/span><span style=\"color: #D4D4D4\"> visitId) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> total_visits<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`<\/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\">Average Page Views per Visit<\/h4>\n\n\n\n<p>Average page views per visit indicate how many pages users are viewing during a single session.<\/p>\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 AVG(totals.pageviews) AS avg_pageviews_per_visit\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;\" 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 style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">AVG<\/span><span style=\"color: #D4D4D4\">(totals.pageviews) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> avg_pageviews_per_visit<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`<\/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\">Traffic Source Distribution<\/h4>\n\n\n\n<p>Understanding where your traffic is coming from can help you tailor marketing strategies and identify key referral sources.<\/p>\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 trafficSource.source, COUNT(*) AS visit_count\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`\nGROUP BY trafficSource.source\nORDER BY visit_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\">SELECT<\/span><span style=\"color: #D4D4D4\"> trafficSource.source, <\/span><span style=\"color: #DCDCAA\">COUNT<\/span><span style=\"color: #D4D4D4\">(*) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> visit_count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">GROUP BY<\/span><span style=\"color: #D4D4D4\"> trafficSource.source<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> visit_count <\/span><span style=\"color: #569CD6\">DESC<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Analyzing User Engagement Metrics<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Bounce Rate<\/h4>\n\n\n\n<p>Bounce rate measures the percentage of sessions where users only view a single page before leaving the site. A high bounce rate may indicate that users are not finding what they&#8217;re looking for.<\/p>\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  COUNTIF(totals.pageviews = 1) \/ COUNT(*) AS bounce_rate\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;\" 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\">  COUNTIF(totals.pageviews = <\/span><span style=\"color: #B5CEA8\">1<\/span><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\"> bounce_rate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`<\/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\">Average Session Duration<\/h4>\n\n\n\n<p>Average session duration provides insights into how long users are spending on your site during a single session.<\/p>\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 AVG(totals.timeOnSite) AS avg_session_duration\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;\" 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 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: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`<\/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\">User Engagement by Device Type<\/h4>\n\n\n\n<p>Different devices may offer different user experiences. Analyzing engagement by device type helps identify how users interact with your site on various devices.<\/p>\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  device.deviceCategory,\n  AVG(totals.pageviews) AS avg_pageviews,\n  AVG(totals.timeOnSite) AS avg_time_on_site\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`\nGROUP BY device.deviceCategory;\" 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\">  device.deviceCategory,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">AVG<\/span><span style=\"color: #D4D4D4\">(totals.pageviews) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> avg_pageviews,<\/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_time_on_site<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">GROUP BY<\/span><span style=\"color: #D4D4D4\"> device.deviceCategory;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">User Engagement by Geographical Location<\/h4>\n\n\n\n<p>Geographical analysis of user engagement can help tailor content and marketing efforts to different regions.<\/p>\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  geoNetwork.country,\n  AVG(totals.pageviews) AS avg_pageviews,\n  AVG(totals.timeOnSite) AS avg_time_on_site\nFROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`\nGROUP BY geoNetwork.country\nORDER BY avg_pageviews DESC\nLIMIT 10;\" 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\">  geoNetwork.country,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #DCDCAA\">AVG<\/span><span style=\"color: #D4D4D4\">(totals.pageviews) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> avg_pageviews,<\/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_time_on_site<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">GROUP BY<\/span><span style=\"color: #D4D4D4\"> geoNetwork.country<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> avg_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\">10<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<p>You can find&nbsp;<a href=\"https:\/\/github.com\/tomomitanaka00\/Blog-SQL\/blob\/main\/Section1.sql\">the complete code<\/a> in&nbsp;my GitHub repository.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Results<\/h3>\n\n\n\n<p>Our analysis of the Google Analytics Sample Dataset yielded several key insights into user behavior on this e-commerce site:<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Site Traffic and Page Views<\/h4>\n\n\n\n<p>The e-commerce site received 2,509 total visits on August 1, 2017. On average, users viewed 4.28 pages per visit. This suggests a moderate level of engagement, with visitors exploring multiple pages during their sessions.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Traffic Source Distribution<\/h4>\n\n\n\n<p>Following are the top 9 traffic sources.<\/p>\n\n\n\n<p>1) Direct traffic is the most significant source of visits. This high volume indicates strong brand loyalty and user familiarity with the website.<\/p>\n\n\n\n<p>Users are likely bookmarking the site, typing the URL directly into their browsers, or accessing the site via saved links. This suggests that the brand has a substantial user base that returns frequently.<\/p>\n\n\n\n<p>2) YouTube is a critical referral source, contributing a significant number of visits. This indicates effective use of video marketing or promotions on YouTube.<\/p>\n\n\n\n<p>It suggests that the site&#8217;s content or advertisements on YouTube are engaging enough to drive viewers to visit the site. <\/p>\n\n\n\n<p>Enhancing video content and further promoting it on YouTube could potentially increase this traffic even more.<\/p>\n\n\n\n<p>3) Visits from analytics.google.com imply that users are coming to the site through links within Google Analytics, possibly during analysis sessions or reporting.<\/p>\n\n\n\n<p>This could include users reviewing site data or settings and then navigating to the actual site.<\/p>\n\n\n\n<p>4) The &#8216;Partners&#8217; category, though unspecified, indicates traffic from associated or partner websites. This suggests collaborations or affiliations with other sites, which can be an essential source of targeted traffic.<\/p>\n\n\n\n<p>5) Traffic from DoubleClick for Advertisers shows that display ad campaigns are driving users to the site. Though the visit count is lower compared to other sources, it reflects the effectiveness of display advertising in attracting users. Optimizing ad campaigns and targeting could increase traffic from this source.<\/p>\n\n\n\n<p>6) Visits from google.com indicate organic search traffic. This suggests that the site&#8217;s SEO strategies are effective, but there may be room for improvement. Enhancing keyword optimization and content relevance can boost organic search traffic further.<\/p>\n\n\n\n<p>7) Traffic from sites.google.com likely comes from links within Google Sites or related services. This might be users navigating from related resources or content hosted on Google Sites.<\/p>\n\n\n\n<p>8&amp;9) Facebook and Quora, though lower in traffic, represent growth opportunities. Increasing engagement on these platforms through targeted content and active participation can attract more visitors.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td class=\"has-text-align-left\" data-align=\"left\">Source<\/td><td class=\"has-text-align-right\" data-align=\"right\">Visit Count<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Direct<\/td><td class=\"has-text-align-right\" data-align=\"right\">2166<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><a href=\"http:\/\/youtube.com\"><u><strong>youtube.com<\/strong><\/u><\/a><\/td><td class=\"has-text-align-right\" data-align=\"right\">180<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><a href=\"http:\/\/analytics.google.com\"><u><strong>analytics.google.com<\/strong><\/u><\/a><\/td><td class=\"has-text-align-right\" data-align=\"right\">57<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">Partners<\/td><td class=\"has-text-align-right\" data-align=\"right\">52<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">DFA (DoubleClick for Advertisers)<\/td><td class=\"has-text-align-right\" data-align=\"right\">15<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><a href=\"http:\/\/google.com\"><u><strong>google.com<\/strong><\/u><\/a><\/td><td class=\"has-text-align-right\" data-align=\"right\">12<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><a href=\"http:\/\/sites.google.com\"><u><strong>sites.google.com<\/strong><\/u><\/a><\/td><td class=\"has-text-align-right\" data-align=\"right\">8<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><a href=\"http:\/\/facebook.com\"><u><strong>facebook.com<\/strong><\/u><\/a><\/td><td class=\"has-text-align-right\" data-align=\"right\">7<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><a href=\"http:\/\/quora.com\"><u><strong>quora.com<\/strong><\/u><\/a><\/td><td class=\"has-text-align-right\" data-align=\"right\">6<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Bounce Rate<\/h4>\n\n\n\n<p>The site&#8217;s bounce rate is 48.6%, meaning nearly half of the visitors leave after viewing just one page. There may be room for improvement in initial user engagement or landing page optimization.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Session Duration<\/h4>\n\n\n\n<p>The average session duration is 325.05 seconds (about 5.4 minutes). This indicates that engaged users are spending a significant amount of time on the site, possibly browsing products or reading content.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Device Usage and Engagement<\/h4>\n\n\n\n<p>Desktop users show the highest engagement, with an average of 4.78 page views and 352.45 seconds spent on site.<br>Mobile users view fewer pages (3.22 on average) and spend less time (261.24 seconds).<br>Tablet users have the lowest engagement, with 3.12 page views and 194.86 seconds on site.<\/p>\n\n\n\n<p>This data suggests that the desktop experience may be more comprehensive or user-friendly. There could be opportunities to improve the mobile and tablet experiences to increase engagement on these devices.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Geographical Engagement<\/h4>\n\n\n\n<p>The top 10 countries by average page views show some interesting patterns:<br>Iraq and Saudi Arabia lead with the highest average page views (15 and 11 respectively), suggesting high engagement from these markets.<\/p>\n\n\n\n<p>Users from Finland, Iceland, and Venezuela spend significantly more time on the site compared to other countries, indicating deep engagement from these markets.<\/p>\n\n\n\n<p>The United States, while having lower average page views (5.8), represents a significant portion of traffic and maintains above-average session duration (368.6 seconds).<\/p>\n\n\n\n<p>These geographical insights can inform targeted marketing strategies and potential areas for localization or market expansion.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td>Country<\/td><td class=\"has-text-align-right\" data-align=\"right\">Avg. Pageviews<\/td><td class=\"has-text-align-right\" data-align=\"right\">Avg. Time on Site (seconds)<\/td><\/tr><tr><td>Iraq<\/td><td class=\"has-text-align-right\" data-align=\"right\">15.0<\/td><td class=\"has-text-align-right\" data-align=\"right\">493.0<\/td><\/tr><tr><td>Saudi Arabia<\/td><td class=\"has-text-align-right\" data-align=\"right\">11.0<\/td><td class=\"has-text-align-right\" data-align=\"right\">495.5<\/td><\/tr><tr><td>Dominican Republic<\/td><td class=\"has-text-align-right\" data-align=\"right\">10.4<\/td><td class=\"has-text-align-right\" data-align=\"right\">333.2<\/td><\/tr><tr><td>Finland<\/td><td class=\"has-text-align-right\" data-align=\"right\">9.5<\/td><td class=\"has-text-align-right\" data-align=\"right\">1145.0<\/td><\/tr><tr><td>Venezuela<\/td><td class=\"has-text-align-right\" data-align=\"right\">9.3<\/td><td class=\"has-text-align-right\" data-align=\"right\">4379.0<\/td><\/tr><tr><td>Iceland<\/td><td class=\"has-text-align-right\" data-align=\"right\">9.0<\/td><td class=\"has-text-align-right\" data-align=\"right\">1221.0<\/td><\/tr><tr><td>Thailand<\/td><td class=\"has-text-align-right\" data-align=\"right\">6.9<\/td><td class=\"has-text-align-right\" data-align=\"right\">108.0<\/td><\/tr><tr><td>United States<\/td><td class=\"has-text-align-right\" data-align=\"right\">5.8<\/td><td class=\"has-text-align-right\" data-align=\"right\">368.6<\/td><\/tr><tr><td>Norway<\/td><td class=\"has-text-align-right\" data-align=\"right\">5.6<\/td><td class=\"has-text-align-right\" data-align=\"right\">189.4<\/td><\/tr><tr><td>Serbia<\/td><td class=\"has-text-align-right\" data-align=\"right\">5.3<\/td><td class=\"has-text-align-right\" data-align=\"right\">1031.5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<p>The above analysis provides valuable insights into user behavior on the e-commerce site. <\/p>\n\n\n\n<p>The moderate page views per visit and significant average session duration suggest that engaged users find value in the site&#8217;s content or products. However, the relatively high bounce rate indicates there&#8217;s room for improvement in initial user engagement.<\/p>\n\n\n\n<p>The stark differences in engagement across devices highlight the need for a responsive, mobile-friendly design that provides a consistent experience across all platforms. <\/p>\n\n\n\n<p>This is particularly important given the lower engagement rates for mobile and tablet users.<\/p>\n\n\n\n<p>Geographically, while some smaller markets show high engagement, there&#8217;s potential to improve engagement in larger markets like the United States. <\/p>\n\n\n\n<p>This could involve tailoring content or products to specific regional preferences or addressing any barriers to engagement in these markets.<\/p>\n\n\n\n<p>Moving forward, these insights can guide efforts to optimize the user experience, refine marketing strategies, and ultimately drive conversions<\/p>\n\n\n\n<p>Future analyses could delve deeper into specific user segments, analyze the customer journey, or investigate correlations between these metrics and actual purchase behavior.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Understanding user behavior on an e-commerce site is crucial for optimizing user experience, improving engagement, and increasing conversions. In this post, we will dive into foundational metrics that define user behavior using the Google Analytics Sample Dataset in BigQuery. We will explore key metrics such as total visits, average page views, traffic source distribution, bounce<\/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-881","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/881","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=881"}],"version-history":[{"count":89,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/881\/revisions"}],"predecessor-version":[{"id":6300,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/881\/revisions\/6300"}],"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=881"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}