A/Bテストの実践

In digital marketing, A/B testing is a key method for data-driven decision-making.

This post will guide you through the process of conducting A/B tests using the Google Analytics Sample Dataset in BigQuery, allowing you to see the power of SQL and real-world data for optimizing your marketing campaigns.

Why Use A/B Testing?

A/B testing enables you to compare two or more versions of a web page, app, or marketing campaign element to see which one performs better. By testing various elements such as CTA buttons, landing pages, or email subject lines, you can optimize your digital presence based on real user interactions.

This post will simulate an A/B test using a sample dataset to analyze how different changes impact metrics such as conversion rate, click-through rate, and bounce rate.

Step 1: Setting Up the Hypothesis

Before running an A/B test, it’s important to define a clear hypothesis. For this example, we’ll simulate a hypothesis for testing two versions of a landing page:

Hypothesis: Changing the call-to-action (CTA) button color from blue to green will increase conversion rates because green is associated with ‘go’ and may create a sense of urgency.

Metrics to Measure: Conversion Rate (users who complete a purchase), Bounce Rate (users who leave after viewing one page).

Step 2: Querying the Google Analytics Sample Dataset

To access and use the Google Analytics Sample Dataset in BigQuery, you’ll need to first query the dataset to extract the relevant data. Here’s an example SQL query to retrieve session data:

SELECT
  date,
  trafficSource.source AS traffic_source,
  device.deviceCategory AS device_category,
  totals.visits AS visits,
  totals.pageviews AS pageviews,
  totals.transactions AS transactions,
  totals.transactionRevenue / 1000000 AS revenue
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170101' AND '20170131'

This query extracts key metrics such as page views, transactions, and revenue, filtered by date range. You can modify the date range depending on your specific test window.

Step 3: Simulating A/B Test Data

To simulate A/B test data, you’ll need to split your users into two groups: Group A (control) and Group B (variation). We’ll use a random selection method in SQL to achieve this.

SELECT
  fullVisitorId,
  totals.transactions,
  totals.pageviews,
  CASE
    WHEN MOD(ABS(FARM_FINGERPRINT(fullVisitorId)), 2) = 0 THEN 'Group_A'
    ELSE 'Group_B'
  END AS test_group
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170101' AND '20170131'

In this case, visitors are randomly assigned to either Group A (control) or Group B (variation). Now, Group A will represent users who saw the blue CTA button, and Group B will represent users who saw the green CTA button.

Step 4: Calculating Key Metrics for Each Group

Next, you’ll calculate the conversion rate (transactions per visit) and bounce rate for both groups.

Conversion Rate Calculation:

SELECT
  test_group,
  COUNT(totals.transactions) / COUNT(fullVisitorId) AS conversion_rate
FROM
  (
    SELECT
      fullVisitorId,
      totals.transactions,
      totals.visits,
      CASE
        WHEN MOD(ABS(FARM_FINGERPRINT(fullVisitorId)), 2) = 0 THEN 'Group_A'
        ELSE 'Group_B'
      END AS test_group
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20170101' AND '20170131'
  )
GROUP BY
  test_group

Bounce Rate Calculation:

SELECT
  test_group,
  SUM(CASE WHEN totals.pageviews = 1 THEN 1 ELSE 0 END) / COUNT(fullVisitorId) AS bounce_rate
FROM
  (
    SELECT
      fullVisitorId,
      totals.pageviews,
      CASE
        WHEN MOD(ABS(FARM_FINGERPRINT(fullVisitorId)), 2) = 0 THEN 'Group_A'
        ELSE 'Group_B'
      END AS test_group
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20170101' AND '20170131'
  )
GROUP BY
  test_group

Step 5: Analyzing Results

After calculating the conversion rates and bounce rates for both groups, compare the results to determine if the change in the CTA button color impacted the user behavior as hypothesized.

Here’s an example of what your results might look like:

If the conversion rate for Group B (green CTA) is higher and the bounce rate is lower, you can conclude that changing the CTA color positively influenced user behavior.

Step 6: Statistical Significance Testing

To ensure the results are not due to random chance, run a statistical significance test.