How to calculate abandonment and completion rates using the Google Analytics API

Google Analytics provides a useful Shopping Behaviour Analysis report that lets you examine the volumes of users who are performing important actions on your ecommerce website, such as viewing products, adding them to the basket, reaching the checkout, and successfully making a transaction.

Since you can’t (usually) sell products on an ecommerce website unless customers view a product page, add an item to their cart, or visit the checkout page, these are essential micro conversions in the step towards the final goal of a customer completing a purchase or converting. Similarly, the more people who abandon their cart or checkout the more likely it is that you’ll have a hard time increasing conversion rates.

In order for an ecommerce site to increase its conversion rate it is essential for it to focus on measuring and optimising the micro conversions shown within the Shopping Behaviour report.

The Shopping Behaviour reports allow you to observe critical ecommerce metrics on completion rates and abandonment rates, so you can spot potential issues and look for areas to optimise. Unfortunately, these useful metrics are not exposed via the Google Analytics API as they are calculated internally.

In this project I’ll show you how you can use my GAPandas Google Analytics Reporting API package for Python to extract the required raw data and calculate the same metrics yourself in Pandas so you can monitor them and work towards increasing your conversion rate.

How Google Analytics creates Shopping Behaviour reports

The Shopping Behaviour and Checkout Behaviour reports are created by Google Analytics using data stored against the ga:shoppingStage dimension. The data in this dimension are populated by Enhanced Ecommerce tracking in the Universal Analytics version of Google Analytics that came before the new GA4.

If do not have Enhanced Ecommerce tracking as part of your GA implementation, you won’t be able to extract any data and the Shopping Behaviour report will not be populated.

Each time a page is viewed, your Google Analytics tracking tells the Google Analytics server what shopping stage you’re at by passing in the relevant value based on events and other parameters you configure during setup.

Therefore, while the Google Analytics API does not give you direct access to the cart abandonment rate, checkout abandonment rate, or cart completion rate, or checkout completion rate, you can manually calculate them using the raw data, once you understand how the calculations are performed within Google Analytics.

Here are the values the Google Analytics API adds to the ga:shoppingStage dimension: ADD_TO_CART, ADD_TO_CART_WITH_VIEW, ALL_VISITS, CART_ABANDONMENT, CHECKOUT, CHECKOUT_1, CHECKOUT_1_ABANDONMENT, CHECKOUT_2, CHECKOUT_ABANDONMENT, CHECKOUT_WITHOUT_CART_ADDITION, CHECKOUT_WITH_CART_ADDITION, NO_CART_ADDITION, NO_PRODUCT_VIEW, NO_SHOPPING_ACTIVITY, PRODUCT_VIEW, TRANSACTION, TRANSACTION_WITHOUT_CHECKOUT, CHECKOUT_2_ABANDONMENT, and ADD_TO_CART_WITHOUT_VIEW.

By querying these data individually you will have access to the raw metrics for each of these stages, and by performing some simple calculations you can additionally calculate cart and checkout completion and abandonment rates, among other useful ecommerce metrics.

Shopping Behaviour shopping progression data

In the Google Analytics interface you’ll see a tab on the Shopping Behaviour report called “shopping progression” when you click “Sessions”. This includes raw data on the number of sessions within the selected period, and the number of sessions with product views, adds to cart, checkouts, and transactions. Each of these individual metrics is raw data that can be queried by filtering against the ga:shoppingStage dimension using Google Analytics API operators.

Shopping Behaviour abandonment data

When you click the “Abandonments” tab on the Shopping Behaviour report in Google Analytics you’ll be able to observe the abandonment rates and metrics for each of the shopping stages.

These are essentially the opposite of the metrics above, so you get back the number of sessions with no shopping activity, no cart addition, cart abandonment, and checkout abandonment as raw numbers. As with the previous example, these can all be extracted by filtering against the ga:shoppingStage dimension using the double equals operator ==.

Shopping behaviour completion rates

Since you can’t export the completion rates from the Google Analytics API we need to calculate them manually by extracting two values from the ga:shoppingStage dimension. The below table shows how to calculate each of the completion rates shown.

Shopping behaviour abandonment rates

Percentage abandonment rates can’t be extracted from the Google Analytics API individually, but can be calculated by querying two values from the ga:shoppingStage dimension and then dividing them to obtain the rate. Here’s how to calculate them yourself.

Load the packages

Now we’ve covered how the Shopping Behaviour abandonment and completion metrics work, we’ll write some Python code to extract the data from your own Google Analytics account and calculate the metrics GA doesn’t let you have via the regular API.

To get started, we’ll need to open a Jupyter notebook and load the packages required. We’ll be using Pandas for data analysis, and GAPandas for accessing the Google Analytics Reporting API. You can install GAPandas via PyPi by entering !pip3 install --upgrade gapandas.

!pip3 install --upgrade gapandas

import pandas as pd
import gapandas as gp

Set up GAPandas

We’ll need to set up GAPandas by specifying the Google Analytics view ID and the date range we want to query. We’ll also need a JSON client secrets keyfile to authenticate with the Google Analytics Reporting API. You can get this by creating a new project in the Google Developers Console and creating a JSON keyfile.

service = gp.get_service('client_secrets.json')

To save repeating ourselves in the queries that follow, and to allow you to re-use your Jupyter notebook at a later date, we can store the Google Analytics view ID and the start_date and end_date for the period we wish to examine in some variables.

view = '123456789'
start_date = '2021-01-01'
end_date = '2022-01-25'

Querying shoppingStage data from Google Analytics

Next, we’ll write a function to extract the individual metrics from the ga:shoppingStage dimension. Creeating a function means you can reuse the same code for multiple queries, simply by changing the argument you pass to the query.

In the function we’re constructing an API payload dictionary containing the start_date and end_date variables, the metrics we want to extract, and the dimensions we want to group by. I’ve included ga:userType in mine, as well as the date since it’s common to see differences in completion and abandonment rates according to whether a session is from a new or returning user.

We’ll use filters to pass in the ga:shoppingStage value, which needs to match one of the ones used in Google Analytics and in the tables we covered above.

def sessions_by_shopping_stage(view, start_date, end_date, shopping_stage, user_type=None):
    """Get sessions by ga:shoppingStage for a given date period by date. 
    
    Args:
        view (int): Google Analytics view ID
        start_date (date): Start date for period in Google Analytics date format
        end_date (date): End date for period in Google Analytics date format
        shopping_stage (string): Google Analytics ga:shoppingStage value from below list
        
        ADD_TO_CART, ADD_TO_CART_WITH_VIEW, ALL_VISITS, 
        CART_ABANDONMENT, CHECKOUT, CHECKOUT_1, 
        CHECKOUT_1_ABANDONMENT, CHECKOUT_2, CHECKOUT_ABANDONMENT, 
        CHECKOUT_WITHOUT_CART_ADDITION, CHECKOUT_WITH_CART_ADDITION, 
        NO_CART_ADDITION, NO_PRODUCT_VIEW, NO_SHOPPING_ACTIVITY, 
        PRODUCT_VIEW, TRANSACTION, TRANSACTION_WITHOUT_CHECKOUT, 
        CHECKOUT_2_ABANDONMENT, ADD_TO_CART_WITHOUT_VIEW
    
        user_type (string): Optional user type (default = None). Values = New Visitor or Returning Visitor
    
    Result: 
        Pandas dataframe containing the date and number of sessions for a given ga:shoppingStage
    """
    
    payload = {
        'start_date': start_date,
        'end_date': end_date,
        'metrics': 'ga:sessions',
        'dimensions': 'ga:date, ga:userType',
        'filters': 'ga:shoppingStage==' + shopping_stage
    }
    
    if user_type:
        payload['segment'] = 'sessions::condition::ga:userType==' + user_type
    
    df = gp.run_query(service, view, payload)
    df['sessions'] = df['sessions'].astype(int)
    return df

Sessions with product views

Now we’ve got our function, we can use it to query the Google Analytics data for the number of sessions with product views, simply by passing in the default arguments and the PRODUCT_VIEW value for the ga:shoppingStage.

df_sessions_with_product_views = sessions_by_shopping_stage(view, start_date, end_date, 'PRODUCT_VIEW')
df_sessions_with_product_views.head()

By passing in the optional New Visitor value to the user_type argument, we can see the number of sessions with product views specifically for new visitors.

df_sessions_with_product_views_new = sessions_by_shopping_stage(view, start_date, end_date, 'PRODUCT_VIEW', 'New Visitor')
df_sessions_with_product_views_new.head()

Similarly, we can repeat the query and create a segment for returning visitors by passing in the value Returning Visitor. It’s normal for these values to be higher, since returning visitors typically have a higher propensity to purchase, often because they know and trust you and have purchased before.

df_sessions_with_product_views_returning = sessions_by_shopping_stage(view, start_date, end_date, 'PRODUCT_VIEW', 'Returning Visitor')
df_sessions_with_product_views_returning.head()

Sessions with add to cart

df_sessions_with_add_to_cart = sessions_by_shopping_stage(view, start_date, end_date, 'ADD_TO_CART')
df_sessions_with_add_to_cart.head()

Sessions with checkout

df_sessions_with_checkout = sessions_by_shopping_stage(view, start_date, end_date, 'CHECKOUT')
df_sessions_with_checkout.head()

Sessions with transactions

df_sessions_with_transaction = sessions_by_shopping_stage(view, start_date, end_date, 'TRANSACTION')
df_sessions_with_transaction.head()

Sessions with no shopping activity

df_sessions_with_no_shopping_activity = sessions_by_shopping_stage(view, start_date, end_date, 'NO_SHOPPING_ACTIVITY')
df_sessions_with_no_shopping_activity.head()

Sessions with no cart addition

df_sessions_with_no_cart_addition = sessions_by_shopping_stage(view, start_date, end_date, 'NO_CART_ADDITION')
df_sessions_with_no_cart_addition.head()

Sessions with cart abandonment

df_sessions_with_cart_abandonment = sessions_by_shopping_stage(view, start_date, end_date, 'CART_ABANDONMENT')
df_sessions_with_cart_abandonment.head()

Sessions with checkout abandonment

df_sessions_with_checkout_abandonment = sessions_by_shopping_stage(view, start_date, end_date, 'CHECKOUT_ABANDONMENT')
df_sessions_with_checkout_abandonment.head()

Sessions by shopping stage

The other (more efficient) approach to querying shopping stage data in the Google Analytics API is to fetch all the ga:shoppingStage values and then use the Pandas melt() or pivot() functions to re-shape the data to create a wide format dataframe with each value in its own column.

This approach is shown below and is essential if you wish to calculate cart or checkout abandonment rates or completion rates, since these require would otherwise require two queries to the Google Analytics API using the earlier method for extracting single values.

start_date = '2021-12-01'
end_date = '2022-01-23'

payload = {
    'start_date': start_date,
    'end_date': end_date,
    'metrics': 'ga:sessions',
    'dimensions': 'ga:date,ga:shoppingStage'
}

df = gp.run_query(service, view, payload)
df['sessions'] = df['sessions'].astype(int)
df.head()

The easiest way to use Pandas to re-shape the data is via the pivot() function. Using pivot() we can convert the long form dataframe into a wide format dataframe. To do that we’ll set the date column as the index, set the shoppingStage as the column names, and put the sessions metric in for the values. We need to use fillna(0) to turn NaN values into zeroes and then reset the index using reset_index().

df = df.pivot(index='date', columns='shoppingStage', values='sessions').fillna(0).reset_index()
df.head()

Checkout completion metrics

To specifically extract only the completion metrics we can filter the Pandas dataframe so it shows only specific columns with values that match the shopping behaviour reports. You can do this by passing a list of column names from the dataframe that correspond to the values that were stored in the ga:shoppingStage dimension when the original data were exported.

df_shopping_behaviour = df[['date', 'ALL_VISITS', 'PRODUCT_VIEW', 
                            'ADD_TO_CART', 'CHECKOUT', 'TRANSACTION']]
df_shopping_behaviour.head()

Checkout completion rates

To calculate completion rates, such as sessions with product views, sessions with add to cart, sessions with checkout, and sessions with transactions, we need to use two values from the dimension and then divide them accordingly to calculate the rate, then multiply that by 100 to express it as a percentage.

df_completion_rates = df[['date', 'ALL_VISITS', 'PRODUCT_VIEW', 
                          'ADD_TO_CART', 'CHECKOUT', 'TRANSACTION']]
df_completion_rates = df_completion_rates.assign(\
    sessions_with_product_views = ((df_completion_rates['PRODUCT_VIEW'] / df_completion_rates['ALL_VISITS'])*100))
df_completion_rates = df_completion_rates.assign(\
    sessions_with_add_to_cart = ((df_completion_rates['ADD_TO_CART'] / df_completion_rates['ALL_VISITS'])*100))
df_completion_rates = df_completion_rates.assign(\
    sessions_with_checkout = ((df_completion_rates['CHECKOUT'] / df_completion_rates['ALL_VISITS'])*100))
df_completion_rates = df_completion_rates.assign(\
    sessions_with_transactions = ((df_completion_rates['TRANSACTION'] / df_completion_rates['ALL_VISITS'])*100))
df_completion_rates = df_completion_rates[['date', 'sessions_with_product_views', 'sessions_with_add_to_cart', 
                                           'sessions_with_checkout', 'sessions_with_transactions']]

df_completion_rates.head()

Checkout abandonment metrics

You can use a similar approach to filter the Pandas dataframe to show only the abandonment metrics, simply by passing in a list of the metrics you want to keep.

df_abandonment_metrics = df[['date', 'ALL_VISITS', 'NO_SHOPPING_ACTIVITY',
                             'NO_CART_ADDITION', 'CART_ABANDONMENT', 'CHECKOUT_ABANDONMENT']]
df_abandonment_metrics.head()

Checkout abandonment rates

Finally, we can calculate the abandonment rates. Unlike the completion rates, which all use ALL_VISITS, the abandonment rates need to use specific metrics stored in ga:shoppingStage to ensure they show exactly what we need.

df_abandonment_rates = df.copy()
df_abandonment_rates = df_abandonment_rates.assign(\
    sessions_with_no_shipping_activity = ((df_abandonment_rates['NO_SHOPPING_ACTIVITY'] / df_abandonment_rates['ALL_VISITS'])*100))
df_abandonment_rates = df_abandonment_rates.assign(\
    sessions_with_no_cart_addition = ((df_abandonment_rates['NO_CART_ADDITION'] / df_abandonment_rates['PRODUCT_VIEW'])*100))
df_abandonment_rates = df_abandonment_rates.assign(\
    sessions_with_cart_abandonment = ((df_abandonment_rates['CART_ABANDONMENT'] / df_abandonment_rates['ADD_TO_CART'])*100))
df_abandonment_rates = df_abandonment_rates.assign(\
    sessions_with_checkout_abandonment = ((df_abandonment_rates['CHECKOUT_ABANDONMENT'] / df_abandonment_rates['CHECKOUT'])*100))
df_abandonment_rates = df_abandonment_rates[['date', 'sessions_with_no_shipping_activity', 'sessions_with_no_cart_addition', 
                                             'sessions_with_cart_abandonment', 'sessions_with_checkout_abandonment']]

And that’s it. You can now extract completion and abandonment data from the Google Analytics API, even though Google doesn’t let you do this by default. It’s fairly easy to change the payload to turn these data into a report that you can use to monitor the metrics over time to help your ecommerce team focus on improvement and keep an eye out for potential site issues.

df_abandonment_rates.head()

Matt Clarke, Tuesday, January 25, 2022