{"id":896,"date":"2024-08-05T10:58:11","date_gmt":"2024-08-05T14:58:11","guid":{"rendered":"https:\/\/www.econai.tech\/?page_id=896"},"modified":"2026-05-06T08:54:19","modified_gmt":"2026-05-06T12:54:19","slug":"predicting-user-conversion","status":"publish","type":"page","link":"https:\/\/tomomitanaka.ai\/?page_id=896","title":{"rendered":"Predicting User Conversion"},"content":{"rendered":"\n

In today’s data-driven marketing landscape, predicting which users are likely to convert is crucial for optimizing strategies and improving ROI. In this blog post, we’ll walk through the process of building user conversion prediction models using BigQuery ML and the Google Analytics Sample Dataset. We’ll cover feature development, model building, and evaluation, all using SQL.<\/p>\n\n\n\n

Contents<\/h4>\n\n\n\n

<\/p>\n\n\n\n

    \n
  1. Feature Development<\/li>\n\n\n\n
  2. Building and Training Predictive Models\n
      \n
    • Logistic Regression Model<\/li>\n\n\n\n
    • Random Forest Model<\/li>\n\n\n\n
    • XGBoost Model<\/li>\n<\/ul>\n<\/li>\n\n\n\n
    • Confusion Matrix Results<\/li>\n\n\n\n
    • Performance Comparison<\/li>\n\n\n\n
    • Feature Importance<\/li>\n\n\n\n
    • Conclusion<\/li>\n<\/ol>\n\n\n\n

      1. Feature Development<\/h3>\n\n\n\n

      Let’s create features that could be indicative of user conversion:<\/p>\n\n\n\n

      SQL<\/span><\/path><\/path><\/svg><\/span>
      CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>TABLE<\/span> <\/span>`your-project.your-dataset.user_features`<\/span> <\/span>AS<\/span><\/span>\nWITH<\/span> user_sessions <\/span>AS<\/span> (<\/span><\/span>\n  <\/span>SELECT<\/span><\/span>\n    fullVisitorId,<\/span><\/span>\n    PARSE_DATE(<\/span>'%Y%m%d'<\/span>, <\/span>date<\/span>) <\/span>AS<\/span> visit_date,<\/span><\/span>\n    totals.transactions,<\/span><\/span>\n    totals.timeOnSite,<\/span><\/span>\n    totals.pageviews,<\/span><\/span>\n    device.deviceCategory,<\/span><\/span>\n    geoNetwork.country,<\/span><\/span>\n    trafficSource.medium<\/span><\/span>\n  <\/span>FROM<\/span><\/span>\n    <\/span>`bigquery-public-data.google_analytics_sample.ga_sessions_*`<\/span><\/span>\n  <\/span>WHERE<\/span><\/span>\n    _TABLE_SUFFIX <\/span>BETWEEN<\/span> <\/span>'20170701'<\/span> <\/span>AND<\/span> <\/span>'20170801'<\/span><\/span>\n)<\/span><\/span>\nSELECT<\/span><\/span>\n  fullVisitorId,<\/span><\/span>\n  <\/span>MAX<\/span>(<\/span>CASE<\/span> <\/span>WHEN<\/span> transactions > <\/span>0<\/span> <\/span>THEN<\/span> <\/span>1<\/span> <\/span>ELSE<\/span> <\/span>0<\/span> <\/span>END<\/span>) <\/span>AS<\/span> has_converted,<\/span><\/span>\n  <\/span>COUNT<\/span>(<\/span>DISTINCT<\/span> visit_date) <\/span>AS<\/span> num_visits,<\/span><\/span>\n  <\/span>AVG<\/span>(timeOnSite) <\/span>AS<\/span> avg_time_on_site,<\/span><\/span>\n  <\/span>AVG<\/span>(pageviews) <\/span>AS<\/span> avg_pageviews,<\/span><\/span>\n  <\/span>MAX<\/span>(deviceCategory) <\/span>AS<\/span> device_category,<\/span><\/span>\n  <\/span>MAX<\/span>(country) <\/span>AS<\/span> country,<\/span><\/span>\n  <\/span>MAX<\/span>(medium) <\/span>AS<\/span> traffic_medium,<\/span><\/span>\n  <\/span>SUM<\/span>(pageviews) <\/span>AS<\/span> total_pageviews,<\/span><\/span>\n  <\/span>SUM<\/span>(timeOnSite) <\/span>AS<\/span> total_time_on_site,<\/span><\/span>\n  DATE_DIFF(<\/span>MAX<\/span>(visit_date), <\/span>MIN<\/span>(visit_date), <\/span>DAY<\/span>) <\/span>AS<\/span> days_since_first_visit<\/span><\/span>\nFROM<\/span><\/span>\n  user_sessions<\/span><\/span>\nGROUP BY<\/span><\/span>\n  fullVisitorId;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
      <\/p>\n\n\n\n
      In this query, we’ve created several features that might be predictive of conversion:<\/p>\n\n\n\n
        \n
      1. has_converted: Whether the user has made a purchase (our target variable)<\/li>\n\n\n\n
      2. num_visits: Number of visits by the user<\/li>\n\n\n\n
      3. avg_time_on_site: Average time spent on the site per visit<\/li>\n\n\n\n
      4. avg_pageviews: Average number of pages viewed per visit<\/li>\n\n\n\n
      5. device_category: The user’s device type<\/li>\n\n\n\n
      6. country: The user’s country<\/li>\n\n\n\n
      7. traffic_medium: The traffic source medium<\/li>\n\n\n\n
      8. total_pageviews: Total number of pages viewed across all visits<\/li>\n\n\n\n
      9. total_time_on_site: Total time spent on the site across all visits<\/li>\n\n\n\n
      10. days_since_first_visit: Number of days between the user’s first and last visit<\/li>\n<\/ol>\n\n\n\n
        2. Building and Training Predictive Models<\/h3>\n\n\n\n
        Let’s build three different types of models to predict user conversion: Logistic Regression, Random Forest, and XGBoost.<\/p>\n\n\n\n
        Logistic Regression Model<\/h4>\n\n\n\n
        SQL<\/span><\/path><\/path><\/svg><\/span>
        CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> MODEL <\/span>`your-project.your-dataset.user_conversion_logistic`<\/span><\/span>\nOPTIONS(model_type=<\/span>'logistic_reg'<\/span>, input_label_cols=['has_converted']) <\/span>AS<\/span><\/span>\nSELECT<\/span><\/span>\n  * <\/span>EXCEPT<\/span>(fullVisitorId)<\/span><\/span>\nFROM<\/span><\/span>\n  <\/span>`your-project.your-dataset.user_features`<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        <\/p>\n\n\n\n
        Random Forest Model<\/h4>\n\n\n\n
        SQL<\/span><\/path><\/path><\/svg><\/span>
        CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> MODEL <\/span>`your-project.your-dataset.user_conversion_random_forest`<\/span><\/span>\nOPTIONS(model_type=<\/span>'random_forest_classifier'<\/span>, input_label_cols=['has_converted']) <\/span>AS<\/span><\/span>\nSELECT<\/span><\/span>\n  * <\/span>EXCEPT<\/span>(fullVisitorId)<\/span><\/span>\nFROM<\/span><\/span>\n  <\/span>`your-project.your-dataset.user_features`<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        <\/p>\n\n\n\n
        XGBoost Model<\/h4>\n\n\n\n
        SQL<\/span><\/path><\/path><\/svg><\/span>
        CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> MODEL <\/span>`your-project.your-dataset.user_conversion_xgboost`<\/span><\/span>\nOPTIONS(model_type=<\/span>'boosted_tree_classifier'<\/span>, input_label_cols=['has_converted']) <\/span>AS<\/span><\/span>\nSELECT<\/span><\/span>\n  * <\/span>EXCEPT<\/span>(fullVisitorId)<\/span><\/span>\nFROM<\/span><\/span>\n  <\/span>`your-project.your-dataset.user_features`<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        <\/p>\n\n\n\n
        3. Confusion Matrix Results<\/h3>\n\n\n\n
        Let’s evaluate the performance of all three models:<\/p>\n\n\n\n
        SQL<\/span><\/path><\/path><\/svg><\/span>
        -- Function to evaluate a model<\/span><\/span>\nCREATE<\/span> TEMP <\/span>FUNCTION<\/span> EvaluateModel(model_name STRING)<\/span><\/span>\nRETURNS<\/span> <\/span>TABLE<\/span><<\/span><\/span>\n  model STRING,<\/span><\/span>\n  accuracy FLOAT64,<\/span><\/span>\n  <\/span>precision<\/span> FLOAT64,<\/span><\/span>\n  recall FLOAT64,<\/span><\/span>\n  f1_score FLOAT64,<\/span><\/span>\n  log_loss FLOAT64,<\/span><\/span>\n  roc_auc FLOAT64<\/span><\/span>\n><\/span><\/span>\nAS<\/span> ((<\/span><\/span>\n  <\/span>SELECT<\/span><\/span>\n    model_name <\/span>AS<\/span> model,<\/span><\/span>\n    accuracy,<\/span><\/span>\n    <\/span>precision<\/span>,<\/span><\/span>\n    recall,<\/span><\/span>\n    f1_score,<\/span><\/span>\n    log_loss,<\/span><\/span>\n    roc_auc<\/span><\/span>\n  <\/span>FROM<\/span><\/span>\n    ML.EVALUATE(MODEL <\/span>`your-project.your-dataset.${model_name}`<\/span>,<\/span><\/span>\n      (<\/span><\/span>\n      <\/span>SELECT<\/span><\/span>\n        * <\/span>EXCEPT<\/span>(fullVisitorId)<\/span><\/span>\n      <\/span>FROM<\/span><\/span>\n        <\/span>`your-project.your-dataset.user_features`<\/span><\/span>\n      )<\/span><\/span>\n    )<\/span><\/span>\n));<\/span><\/span>\n<\/span>\n-- Evaluate all models<\/span><\/span>\nSELECT<\/span> * <\/span>FROM<\/span> EvaluateModel(<\/span>'user_conversion_logistic'<\/span>)<\/span><\/span>\nUNION ALL<\/span><\/span>\nSELECT<\/span> * <\/span>FROM<\/span> EvaluateModel(<\/span>'user_conversion_random_forest'<\/span>)<\/span><\/span>\nUNION ALL<\/span><\/span>\nSELECT<\/span> * <\/span>FROM<\/span> EvaluateModel(<\/span>'user_conversion_xgboost'<\/span>)<\/span><\/span>\nORDER BY<\/span> model;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        <\/p>\n\n\n\n
        Confusion matrices provide a detailed breakdown of the classification results by showing the counts of true positives (TP), true negatives (TN), false positives (FP), and false negatives (FN). Here’s an analysis of the confusion matrices for Logistic Regression, Random Forest, and XGBoost models.<\/p>\n\n\n\n
        Logistic Regression<\/h4>\n\n\n\n
        Logistic Regression shows a high number of true negatives (59,339) and a low number of false positives (184), which indicates that it correctly identifies non-converters most of the time. However, it struggles with correctly identifying converters, as shown by the relatively high number of false negatives (791) and the low number of true positives (209). <\/p>\n\n\n\n
        <\/td>Predicted: 0 (not converted)<\/td>Predicted: 1 (converted)<\/td><\/tr>
        Actual: 0 (not converted)<\/td>(TN) 59,339<\/td>(FP) 184<\/td><\/tr>
        Actual: 1 (converted)<\/td>(FN) 791<\/td>(TP) 209<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
        Random Forest<\/h4>\n\n\n\n
        The Random Forest model improves significantly over Logistic Regression in terms of detecting true positives (639 vs. 209), which indicates that it is better at identifying actual converters. The number of false negatives is also reduced to 361, meaning fewer actual converters are missed. The model also maintains a low false positive rate (126), which means it remains accurate in not falsely predicting conversions when there are none. Overall, Random Forest offers a better balance between detecting converters and avoiding false alarms.<\/p>\n\n\n\n
        <\/td>Predicted: 0 (not converted)<\/td>Predicted: 1 (converted)<\/td><\/tr>
        Actual: 0 (not converted)<\/td>(TN) 59,397<\/td>(FP) 126<\/td><\/tr>
        Actual: 1 (converted)<\/td> (FN) 361<\/td> (TP) 639<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
        XGBoost<\/h4>\n\n\n\n
        XGBoost provides a mixed performance compared to Random Forest. It has slightly more false positives (246) than Random Forest. It also shows a higher number of false negatives (512) than Random Forest, meaning it misses more actual converters. Despite having a higher true positive count (488) than Logistic Regression, XGBoost underperforms compared to Random Forest in detecting actual converters.<\/p>\n\n\n\n
        <\/td>Predicted: 0 (not converted)<\/td>Predicted: 1 (converted)<\/td><\/tr>
        Actual: 0 (not converted)<\/td>(TN) 59,277<\/td>(FP) 246<\/td><\/tr>
        Actual: 1 (converted)<\/td> (FN) 512<\/td>(TP) 488<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
        Based on the confusion matrix analysis, Random Forest<\/strong> emerges as the top-performing model for predicting user conversion due to its superior balance between identifying actual conversions and minimizing false predictions.<\/p>\n\n\n\n
        XGBoost<\/strong> is a viable alternative but may require adjustments, while Logistic Regression<\/strong> is the least effective in this context, particularly due to its high false negative rate.<\/p>\n\n\n\n
        4. Performance Comparison<\/h3>\n\n\n\n
        <\/td>Logistic Regression<\/td>Random Forest<\/td>XGBoost<\/td><\/tr>
        Precision<\/td>0.532<\/td>0.835<\/td>0.665<\/td><\/tr>
        Recall<\/td>0.209<\/td>0.639<\/td>0.488<\/td><\/tr>
        Accuracy<\/td>0.984<\/td>0.992<\/td>0.987<\/td><\/tr>
        F1 Score<\/td>0.300<\/td>0.724<\/td>0.563<\/td><\/tr>
        Log Loss<\/td>0.046<\/td>0.144<\/td>0.028<\/td><\/tr>
        AUC<\/td>0.986<\/td>0.986<\/td>0.993<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
        When evaluating predictive models, key metrics include precision, recall, accuracy, F1 score, Log Loss, and AUC.<\/p>\n\n\n\n
        Precision<\/strong> measures how often positive predictions are correct, while recall <\/strong>assesses how well the model identifies actual positives. Accuracy<\/strong> gives an overall correctness measure, but can be misleading in imbalanced datasets. F1 Score<\/strong> balances precision and recall. Log Loss<\/strong> penalizes incorrect predictions, and AUC<\/strong> shows the model’s ability to distinguish between classes.<\/p>\n\n\n\n
        1. Precision<\/strong><\/h4>\n\n\n\n
          \n
        • Logistic Regression (0.532):<\/strong> This model shows moderate precision, meaning that 53.2% of the users it predicted as converters were indeed converters. However, this also indicates that nearly half of the positive predictions were false positives.<\/li>\n\n\n\n
        • Random Forest (0.835):<\/strong> Random Forest excels in precision with 83.5%, making it very effective in minimizing false positives. This model is ideal if the goal is to avoid wasting resources on users who are unlikely to convert.<\/li>\n\n\n\n
        • XGBoost (0.665):<\/strong> XGBoost offers a precision of 66.5%, which is higher than Logistic Regression but lower than Random Forest. <\/li>\n<\/ul>\n\n\n\n
          2. Recall<\/strong><\/h4>\n\n\n\n