Setting Up Google BigQuery for Ecommerce Analytics with Rails Solidus Integration

### 4.2 Query to Track Subscriptions from Campaigns
This query shows how many subscriptions were created from the same campaigns.
```sql
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.