{"id":906,"date":"2024-08-05T11:07:21","date_gmt":"2024-08-05T15:07:21","guid":{"rendered":"https:\/\/www.econai.tech\/?page_id=906"},"modified":"2026-05-06T08:59:41","modified_gmt":"2026-05-06T12:59:41","slug":"personalization-and-recommendation","status":"publish","type":"page","link":"https:\/\/tomomitanaka.ai\/?page_id=906","title":{"rendered":"Recommendation and Personalization"},"content":{"rendered":"\n

Personalization and recommendation systems play a crucial role in enhancing user experience and driving business growth. <\/p>\n\n\n\n

By leveraging the power of BigQuery ML and the rich data available in Google Analytics, we can create personalization and recommendation models. <\/p>\n\n\n\n

This post will guide you through the process of building such a system using the Google Analytics Sample Dataset in BigQuery.<\/p>\n\n\n\n

Contents<\/h4>\n\n\n\n
    \n
  1. Preparing the Data<\/li>\n\n\n\n
  2. Building a Matrix Factorization Model<\/li>\n\n\n\n
  3. Generating Recommendations<\/li>\n\n\n\n
  4. Evaluating the Model<\/li>\n\n\n\n
  5. Implementing Personalization<\/li>\n\n\n\n
  6. Conclusion<\/li>\n<\/ol>\n\n\n\n

    Preparing the Data<\/h3>\n\n\n\n

    To build an effective recommendation system, we need to prepare our data. Here’s a query to extract relevant features:<\/p>\n\n\n\n

    SQL<\/span><\/path><\/path><\/svg><\/span>
    -- Step 1: Preparing the Data<\/span><\/span>\nCREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>TABLE<\/span> <\/span>`predictive-behavior-analytics.Section9.user_item_interactions`<\/span> <\/span>AS<\/span><\/span>\nSELECT<\/span><\/span>\n  fullVisitorId,<\/span><\/span>\n  <\/span>CONCAT<\/span>(product.productSKU, <\/span>'_'<\/span>, product.v2ProductName) <\/span>AS<\/span> item_id,<\/span><\/span>\n  <\/span>SUM<\/span>(product.productQuantity) <\/span>AS<\/span> interaction_count,<\/span><\/span>\n  <\/span>SUM<\/span>(product.productPrice * product.productQuantity) \/ <\/span>1000000<\/span> <\/span>AS<\/span> total_revenue<\/span><\/span>\nFROM<\/span><\/span>\n  <\/span>`bigquery-public-data.google_analytics_sample.ga_sessions_*`<\/span>,<\/span><\/span>\n  UNNEST(hits) <\/span>AS<\/span> hits,<\/span><\/span>\n  UNNEST(hits.product) <\/span>AS<\/span> product<\/span><\/span>\nWHERE<\/span><\/span>\n  _TABLE_SUFFIX <\/span>BETWEEN<\/span> <\/span>'20170701'<\/span> <\/span>AND<\/span> <\/span>'20170731'<\/span><\/span>\n  <\/span>AND<\/span> hits.eCommerceAction.action_type = <\/span>'6'<\/span>  <\/span>-- Completed purchase<\/span><\/span>\nGROUP BY<\/span><\/span>\n  fullVisitorId, item_id<\/span><\/span>\nHAVING<\/span><\/span>\n  interaction_count > <\/span>0<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/code><\/pre>\n\n\n\n
    This query creates a table of user-item interactions, focusing on completed purchases.<\/p>\n\n\n\n
    Building a Matrix Factorization Model<\/h3>\n\n\n\n
    Now that we have our data prepared, let’s use BigQuery ML to create a matrix factorization model for item recommendations:<\/p>\n\n\n\n
    SQL<\/span><\/path><\/path><\/svg><\/span>
    -- Step 2: Building the Matrix Factorization Model<\/span><\/span>\nCREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> MODEL <\/span>`predictive-behavior-analytics.Section9.item_recommendation_model`<\/span><\/span>\nOPTIONS(<\/span><\/span>\n  model_type=<\/span>'MATRIX_FACTORIZATION'<\/span>,<\/span><\/span>\n  user_col=<\/span>'fullVisitorId'<\/span>,<\/span><\/span>\n  item_col=<\/span>'item_id'<\/span>,<\/span><\/span>\n  rating_col=<\/span>'interaction_count'<\/span>,<\/span><\/span>\n  feedback_type=<\/span>'implicit'<\/span><\/span>\n) <\/span>AS<\/span><\/span>\nSELECT<\/span><\/span>\n  fullVisitorId,<\/span><\/span>\n  item_id,<\/span><\/span>\n  interaction_count<\/span><\/span>\nFROM<\/span><\/span>\n  <\/span>`predictive-behavior-analytics.Section9.user_item_interactions`<\/span>;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    This model will learn latent factors for users and items based on their interactions.<\/p>\n\n\n\n
    Generating Recommendations<\/h3>\n\n\n\n
    With our model trained, we can now generate personalized recommendations for users:<\/p>\n\n\n\n
    SQL<\/span><\/path><\/path><\/svg><\/span>
    -- Step 3: Generating Recommendations<\/span><\/span>\nWITH<\/span> user_item_pairs <\/span>AS<\/span> (<\/span><\/span>\n  <\/span>SELECT DISTINCT<\/span><\/span>\n    ui1.fullVisitorId,<\/span><\/span>\n    ui2.item_id<\/span><\/span>\n  <\/span>FROM<\/span><\/span>\n    <\/span>`predictive-behavior-analytics.Section9.user_item_interactions`<\/span> ui1<\/span><\/span>\n  <\/span>CROSS JOIN<\/span><\/span>\n    (<\/span>SELECT DISTINCT<\/span> item_id <\/span>FROM<\/span> <\/span>`predictive-behavior-analytics.Section9.user_item_interactions`<\/span>) ui2<\/span><\/span>\n),<\/span><\/span>\npredictions <\/span>AS<\/span> (<\/span><\/span>\n  <\/span>SELECT<\/span> *<\/span><\/span>\n  <\/span>FROM<\/span> ML.PREDICT(MODEL <\/span>`predictive-behavior-analytics.Section9.item_recommendation_model`<\/span>,<\/span><\/span>\n    (<\/span>SELECT<\/span> * <\/span>FROM<\/span> user_item_pairs))<\/span><\/span>\n)<\/span><\/span>\nSELECT<\/span><\/span>\n  fullVisitorId <\/span>AS<\/span> user_id,<\/span><\/span>\n  ARRAY_AGG(STRUCT(item_id, predicted_interaction_count_confidence)<\/span><\/span>\n            <\/span>ORDER BY<\/span> predicted_interaction_count_confidence <\/span>DESC<\/span><\/span>\n            <\/span>LIMIT<\/span> <\/span>5<\/span>) <\/span>AS<\/span> top_5_recommendations<\/span><\/span>\nFROM<\/span> predictions<\/span><\/span>\nGROUP BY<\/span> fullVisitorId<\/span><\/span>\nLIMIT<\/span> <\/span>10<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    This query will generate top 10 recommended items for each user.<\/p>\n\n\n\n
    You can find\u00a0the complete code<\/a>\u00a0in\u00a0my GitHub repository.<\/p>\n\n\n\n
    Evaluating the Model<\/h3>\n\n\n\n
    To assess the performance of the Matrix Factorization recommendation model, we utilized several key metrics. These metrics provide insights into different aspects of the model’s effectiveness in predicting user-item interactions and generating relevant recommendations.<\/p>\n\n\n\n
    Evaluation Metrics<\/h4>\n\n\n\n
      \n
    1. Mean Average Precision (MAP):<\/strong> 0.2503<\/li>\n\n\n\n
    2. Mean Squared Error (MSE):<\/strong> 0.0417<\/li>\n\n\n\n
    3. Normalized Discounted Cumulative Gain (NDCG):<\/strong> 0.9750<\/li>\n\n\n\n
    4. Average Rank:<\/strong> 0.3338<\/li>\n<\/ol>\n\n\n\n
      Mean Average Precision (MAP)<\/strong><\/h5>\n\n\n\n
        \n
      • Our model achieved a MAP of 0.2503, indicating that approximately 25% of the recommended items are relevant to users.<\/li>\n\n\n\n
      • While there’s room for improvement, this score is reasonable for a recommendation system based on implicit feedback.<\/li>\n<\/ul>\n\n\n\n
        Mean Squared Error (MSE)<\/strong><\/h5>\n\n\n\n
          \n
        • The low MSE of 0.0417 suggests that our model’s predictions closely align with actual user interactions.<\/li>\n\n\n\n
        • This indicates high accuracy in predicting user-item interaction strengths.<\/li>\n<\/ul>\n\n\n\n
          Normalized Discounted Cumulative Gain (NDCG)<\/strong><\/h5>\n\n\n\n
            \n
          • With an impressive NDCG of 0.9750, our model excels at ranking relevant items higher in the recommendation lists.<\/li>\n\n\n\n
          • This high score implies that users are likely to find the most relevant items at the top of their recommendations.<\/li>\n<\/ul>\n\n\n\n
            Average Rank<\/strong><\/h5>\n\n\n\n
              \n
            • The average rank of 0.3338 indicates that relevant items typically appear within the top third of recommendation lists.<\/li>\n\n\n\n
            • This suggests that users won’t need to scroll far to find items of interest, enhancing the user experience.<\/li>\n<\/ul>\n\n\n\n
              Key Insights<\/h4>\n\n\n\n
                \n
              1. Ranking Effectiveness:<\/strong> The high NDCG and low Average Rank demonstrate that our model is particularly adept at prioritizing relevant items. This is crucial for user satisfaction in recommendation systems.<\/li>\n\n\n\n
              2. Prediction Accuracy:<\/strong> The low MSE indicates that our model accurately predicts the strength of user-item interactions, which is valuable for understanding user preferences.<\/li>\n\n\n\n
              3. Relevance vs. Ranking:<\/strong> While the MAP suggests moderate performance in terms of overall recommendation relevance, the high NDCG indicates that the most relevant items are consistently ranked at the top.<\/li>\n\n\n\n
              4. User Experience:<\/strong> The combination of strong ranking performance and low average rank suggests that users are likely to find relevant items quickly, potentially leading to higher engagement.<\/li>\n<\/ol>\n\n\n\n
                Conclusion<\/h4>\n\n\n\n
                Our Matrix Factorization model demonstrates robust performance, particularly in ranking relevant items and accurately predicting user-item interactions. While there’s room to improve overall precision, the model’s strength in prioritizing relevant recommendations suggests it will effectively enhance user experience and engagement in our recommendation system.<\/p>\n\n\n\n
                Implementing Personalization<\/h3>\n\n\n\n
                Beyond item recommendations, we can use our model for broader personalization efforts:<\/p>\n\n\n\n
                  \n
                1. Personalized Email Campaigns<\/strong>: Use top recommendations for each user to create targeted email content.<\/li>\n\n\n\n
                2. Website Personalization<\/strong>: Dynamically adjust product displays based on user preferences.<\/li>\n\n\n\n
                3. Tailored Promotions<\/strong>: Offer discounts on items likely to interest specific users.<\/li>\n<\/ol>\n\n\n\n
                  Conclusion<\/h3>\n\n\n\n
                  By leveraging BigQuery ML and Google Analytics data, we’ve created a powerful personalization and recommendation system. This approach allows for scalable, data-driven decision-making that can significantly enhance user experience and drive business growth.<\/p>\n\n\n\n
                  Remember, the key to successful personalization is continuous iteration and testing. Regularly update your model with new data and experiment with different features to improve its performance over time.<\/p>\n","protected":false},"excerpt":{"rendered":"
                  Personalization and recommendation systems play a crucial role in enhancing user experience and driving business growth. By leveraging the power of BigQuery ML and the rich data available in Google Analytics, we can create personalization and recommendation models. This post will guide you through the process of building such a system using the Google Analytics<\/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-906","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/906","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=906"}],"version-history":[{"count":29,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/906\/revisions"}],"predecessor-version":[{"id":6374,"href":"https:\/\/tomomitanaka.ai\/index.php?rest_route=\/wp\/v2\/pages\/906\/revisions\/6374"}],"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=906"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}