Setting Up Google BigQuery for Ecommerce Analytics with Rails Solidus Integration

In today’s data-driven world, ecommerce stores must efficiently collect and analyse data from a variety of sources to make informed decisions. For many ecommerce businesses running on Rails Solidus, managing data across multiple platforms (like Google Ads, Mailchimp, and offline campaigns) can be a daunting task. However, by centralising all your data in Google BigQuery, you can achieve a single source of truth, making data processing, analysis, and reporting much easier.

This blog post will walk you through the process of setting up Google BigQuery, connecting it with various data sources (specifically Rails Solidus), and providing practical SQL query examples to analyse the performance of digital marketing campaigns and calculate important metrics like Lifetime Value (LTV) and campaign-driven sales.

Step 1: Setting Up Google BigQuery

Before you can begin importing data from Rails Solidus or other platforms, you need to set up a Google Cloud account and enable BigQuery.

1.1 Create a Google Cloud Project

  • Go to the Google Cloud Console (https://console.cloud.google.com/).
  • Click on the Project drop-down in the top navigation and click New Project.
  • Enter a name for your project and select a billing account (you’ll need to set this up if you haven’t already).
  • Click Create.

1.2 Enable BigQuery API

  • Once your project is created, search for BigQuery in the search bar at the top of the Google Cloud Console.
  • Select BigQuery API and click Enable.

1.3 Create a BigQuery Dataset

  • In the BigQuery Console, select your project, then click Create Dataset.
  • Provide a dataset name, select a location, and set the default table expiration (optional).
  • Click Create Dataset.

Your dataset is where you’ll store tables related to your Rails Solidus data and other data sources.

Step 2: Connecting Rails Solidus to BigQuery

To get data from Rails Solidus into BigQuery, you’ll need to set up an ETL (Extract, Transform, Load) process. This can be done by using a data pipeline, such as Google Cloud Storage, Cloud Functions, or third-party tools like Fivetran or Stitch to automate data transfer.

2.1 Setting Up the BigQuery Connector

For the sake of simplicity, we’ll cover a manual method to export data from Solidus to BigQuery using Google Cloud Storage as an intermediary.

2.1.1 Exporting Solidus Data to CSV

  1. Export Data from Rails Solidus: You’ll first need to extract relevant data from Solidus. Let’s assume you want to track orders, subscriptions, and customer information:

    # In Rails console, you can export the data to a CSV file
require 'csv'

orders = Spree::Order.where(created_at: 1.year.ago..Time.now)

CSV.open("orders.csv", "wb") do |csv|
  csv << ["id", "user_id", "total", "created_at"]
  orders.each do |order|
    csv << [order.id, order.user_id, order.total, order.created_at]
  end
end

2.1.2 Loading the Data from Google Cloud Storage to BigQuery

Now that the data is in Google Cloud Storage, you can load it into BigQuery.

  1. Go to the BigQuery Console.
  2. Click on your project and dataset, then click Create Table.
  3. In the Create Table panel, choose Google Cloud Storage as the source.
  4. Enter the path to your uploaded CSV file (e.g., gs://your-bucket-name/orders.csv).
  5. Select the file format as CSV and define the schema for your data (you’ll need to define fields like id, user_id, total, and created_at).
  6. Click Create Table.

Repeat this process for other data sources like subscriptions and customer information.

2.2 Automating the Data Transfer

For automating this, you can either write custom scripts or use third-party tools like Fivetran or Stitch to set up continuous data pipelines between your Rails Solidus app and BigQuery.

Step 3: Connecting Marketing Data to BigQuery

Google Ads, Mailchimp, and offline campaigns all provide ways to export their data for analysis.

3.1 Google Ads

  1. Link Google Ads to Google Analytics: Set up a connection between Google Ads and Google Analytics to track campaign data.
  2. Export Google Ads Data to BigQuery: Use the Google Ads BigQuery Export feature to send campaign data directly to BigQuery.
  3. Alternatively, use the Google Ads API to fetch performance data and upload it to BigQuery in CSV format.

3.2 Mailchimp

Mailchimp allows you to export campaign data (e.g., email opens, clicks, conversions) in CSV format. You can then upload this to Google Cloud Storage and follow the same process to import it into BigQuery.

  1. Go to the Reports section of Mailchimp and export the campaign data as a CSV file.
  2. Upload the CSV to Google Cloud Storage.
  3. Load the data into BigQuery using the same method as above.

3.3 Offline Campaigns

For offline campaigns (such as print, radio, or in-store promotions), tracking can be done by using unique discount codes or using QR codes that link to unique landing pages. Once the offline campaigns are completed, the data can be aggregated and uploaded into BigQuery through CSV exports.

Step 4: Analysing the Data with BigQuery

Now that your data is loaded into BigQuery, you can start querying it to gain insights into your ecommerce business and marketing campaigns. Below are some practical SQL queries to help you get started.

4.1 Query to Track Orders from Campaigns

This query shows how many orders were generated from specific marketing campaigns, such as Google Ads, Mailchimp, and offline promotions.

SELECT
  campaign_name,
  COUNT(DISTINCT o.id) AS total_orders,
  SUM(o.total) AS total_sales
FROM
  `your_project.your_dataset.orders` o
JOIN
  `your_project.your_dataset.campaigns` c
ON
  o.campaign_id = c.id
WHERE
  c.campaign_type IN ('Google Ads', 'Mailchimp', 'Offline')
  AND o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY
  campaign_name
ORDER BY
  total_sales DESC;

4.2 Query to Track Subscriptions from Campaigns

This query shows how many subscriptions were created from the same campaigns.

SELECT
  campaign_name,
  COUNT(DISTINCT s.id) AS total_subscriptions
FROM
  `your_project.your_dataset.subscriptions` s
JOIN
  `your_project.your_dataset.campaigns` c
ON
  s.campaign_id = c.id
WHERE
  c.campaign_type IN ('Google Ads', 'Mailchimp', 'Offline')
  AND s.created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY
  campaign_name
ORDER BY
  total_subscriptions DESC;

4.3 Query to Calculate Lifetime Value (LTV)

Lifetime Value (LTV) is a key metric that tells you the total revenue you can expect from a customer over the course of their relationship with your business.

SELECT
  customer_id,
  SUM(total) AS lifetime_value
FROM
  `your_project.your_dataset.orders`
WHERE
  customer_id IS NOT NULL
GROUP BY
  customer_id
ORDER BY
  lifetime_value DESC;

This query calculates the total value each customer has brought to the business over their lifetime, based on all their orders.

4.4 Query to Join Campaigns with LTV

This query shows the lifetime value of customers acquired through different marketing campaigns, helping you understand which campaigns bring the most valuable customers.

SELECT
  c.campaign_name,
  SUM(o.total) AS campaign_lifetime_value
FROM
  `your_project.your_dataset.orders` o
JOIN
  `your_project.your_dataset.campaigns` c
ON
  o.campaign_id = c.id
WHERE
  c.campaign_type IN ('Google Ads', 'Mailchimp', 'Offline')
GROUP BY
  c.campaign_name
ORDER BY
  campaign_lifetime_value DESC;

Conclusion

Setting up Google BigQuery for your Rails Solidus store allows you to centralise data from multiple sources—whether that’s ecommerce data from Solidus or campaign data from platforms like Google Ads and Mailchimp. By using BigQuery as your single source of truth, you can easily process and analyse your data, uncover valuable insights, and measure the performance of your marketing campaigns.

With the queries provided, you can begin to track key metrics like campaign-driven sales, subscriptions, and lifetime value, helping you make more informed decisions about where to invest your marketing budget.