Back to Guides
    integrationsbeginner15 minutesDec 28, 2024

    Shopify + Cogny Integration Guide

    Connect Shopify to Cogny via BigQuery for AI-powered e-commerce analytics. Discover channel LTV, product opportunities, and discount impact in minutes.

    Shopify + Cogny Integration Guide

    TL;DR

    Connect Shopify to Cogny via BigQuery in 15 minutes for AI-powered e-commerce analytics revealing channel LTV, product opportunities, and discount impact insights.

    What you'll accomplish:

    • Export Shopify data (orders, customers, products) to BigQuery automatically
    • Connect BigQuery to Cogny for AI analysis of complete store performance
    • Discover which acquisition channels drive highest LTV customers
    • Identify product combinations that predict repeat purchases
    • Analyze how discount strategies affect long-term customer value

    Time required: 15 minutes | Difficulty: Beginner | Prerequisites: Shopify store (any plan), Google Cloud account (free tier fine), Cogny account

    Quick Start: Install Shopify-to-BigQuery connector (Fivetran, Stitch, or Supermetrics) → Configure automatic daily sync → Connect BigQuery to Cogny.

    Related Resources

    Essential guides for maximizing your Shopify data analysis:

    Question

    How do I connect Shopify to Cogny to analyze my e-commerce data with AI?

    Answer

    Export Shopify data to BigQuery. Connect BigQuery to Cogny. AI analyzes your entire store performance—orders, customers, products, traffic.

    You get insights no Shopify dashboard can show you.

    Quick Tip: Shopify's native BigQuery export is free—no third-party ETL tools needed for basic e-commerce data. Check if your Shopify Plus account has this enabled before purchasing a connector service.

    Why Connect Shopify?

    Shopify's built-in analytics show surface metrics. Sales. Traffic. Conversion rate.

    But you can't easily answer:

    • Which acquisition channel drives highest LTV customers?
    • What's the true CAC by channel when accounting for returns?
    • Which product combinations predict repeat purchases?
    • How do discount strategies affect long-term customer value?

    Cogny's AI answers these questions automatically.

    What You'll Get

    After connecting Shopify:

    Customer LTV analysis Identify high-value customer segments and acquisition sources

    Product performance intelligence Which products drive repeats, which are one-time purchases

    Channel attribution True multi-touch attribution across Google, Meta, email, organic

    Inventory optimization What to stock more of, what to discontinue

    Discount impact analysis How promotions affect immediate revenue vs. long-term value

    Note: This integration requires a Google Cloud project. If you don't have one, create a free account at cloud.google.com. Google provides $300 in free credits for new accounts.

    Prerequisites

    You need:

    1. Shopify store (any plan works)
    2. Google Cloud account (free tier is fine)
    3. Cogny account (for AI analysis)

    No coding required. No app installation needed.

    Step 1: Set Up Google Cloud Project

    Go to console.cloud.google.com

    Create a new project (or use existing).

    Name it something like shopify-analytics

    Click Create

    Google gives you $300 in free credits.

    Time: 2 minutes

    Step 2: Enable BigQuery API

    In Google Cloud Console, search for "BigQuery API"

    Click Enable

    Wait 30 seconds for activation.

    Time: 1 minute

    Step 3: Install Shopify BigQuery Integration

    Option A: Use Fivetran (Recommended)

    Easiest method. Free tier available. Automatic updates.

    Go to fivetran.com

    Sign up for free account.

    Click Add Connector

    Select Shopify

    Time: 5 minutes

    Option B: Use Segment + Shopify

    If you already use Segment.

    Connect Shopify to Segment. Configure BigQuery destination.

    Time: 10 minutes (if Segment is already set up)

    Option C: Use Shopify's Native BigQuery Export

    Currently in beta. Check shopify.dev for availability.

    For this guide, we'll use Fivetran (most reliable).

    Step 4: Configure Fivetran Connector

    Connect Shopify:

    In Fivetran:

    Click Shopify connector.

    Click Authorize Shopify

    Log into your Shopify admin.

    Select the store to connect.

    Grant permissions:

    • ✅ Read orders
    • ✅ Read customers
    • ✅ Read products
    • ✅ Read inventory
    • ✅ Read analytics

    Click Install app

    Time: 3 minutes

    Configure BigQuery destination:

    Fivetran asks for BigQuery details.

    Project ID: Your Google Cloud project ID (e.g., shopify-analytics-123456)

    Dataset location: Choose region

    • US: us (multi-region)
    • EU: eu (multi-region)

    Service account: Fivetran provides instructions

    Create service account in Google Cloud:

    1. Go to IAM & Admin → Service Accounts
    2. Click "Create Service Account"
    3. Name: fivetran-connector
    4. Grant role: BigQuery Data Editor
    5. Create key → JSON
    6. Download JSON key

    Upload JSON key to Fivetran.

    Click Save & Test

    Time: 5 minutes

    Step 5: Start Initial Sync

    Fivetran starts syncing your Shopify data.

    What gets synced:

    • Orders: Every order ever placed
    • Customers: Customer details and history
    • Products: Product catalog and variants
    • Inventory: Stock levels and locations
    • Refunds: Return and refund data
    • Discounts: Promotion usage
    • Abandoned checkouts: Lost sales opportunities

    Initial sync duration:

    • Small store (<1K orders): 10-20 minutes
    • Medium store (1K-10K orders): 1-2 hours
    • Large store (10K+ orders): 4-8 hours

    You'll get email when complete.

    Time: Varies (but you can move to next steps while syncing)

    Step 6: Verify Data in BigQuery

    While sync is running, check BigQuery.

    Go to console.cloud.google.com/bigquery

    In Explorer panel (left), expand your project.

    You should see a dataset: shopify

    Inside, tables appear as sync progresses:

    • orders
    • order_line
    • customers
    • products
    • product_variants
    • abandoned_checkouts
    • refunds

    Click on a table → Preview tab.

    See your data appearing.

    Time: 2 minutes

    Step 7: Connect BigQuery to Cogny

    Follow our BigQuery Connection Guide if you haven't already. This 5-minute setup enables Cogny to run AI-powered CAC analysis on your Shopify data.

    Quick steps:

    1. In Google Cloud, create service account for Cogny
    2. Grant BigQuery Data Viewer role
    3. Download JSON key
    4. In Cogny: Add Data Source → BigQuery
    5. Upload JSON key
    6. Select shopify dataset

    Time: 5 minutes

    Step 8: Let AI Analyze

    Once connected, Cogny's AI:

    Catalogs your schema (5 minutes)

    • Identifies all Shopify tables
    • Maps relationships (orders → customers → products)
    • Builds entity relationship graph

    Analyzes historical patterns (1-24 hours)

    • Customer behavior over time
    • Product performance trends
    • Channel effectiveness
    • Seasonal patterns

    Generates growth tickets (ongoing)

    • Specific optimization opportunities
    • Product recommendations
    • Pricing insights
    • Inventory guidance

    What's in Your Shopify Data?

    Orders table:

    Every purchase ever made.

    Key fields:

    • id - Unique order ID
    • created_at - Order date/time
    • total_price - Order value
    • customer_id - Who bought it
    • financial_status - Paid, pending, refunded
    • fulfillment_status - Shipped, delivered, etc.
    • source_name - Traffic source (web, mobile, POS)
    • referring_site - Where they came from
    • landing_site - First page they visited

    Customers table:

    Every customer who's ever purchased.

    Key fields:

    • id - Customer ID
    • email - Email address
    • first_name, last_name - Name
    • created_at - First purchase date
    • orders_count - Total orders
    • total_spent - Lifetime value
    • state - Enabled/disabled
    • accepts_marketing - Email subscription status

    Products table:

    Your catalog.

    Key fields:

    • id - Product ID
    • title - Product name
    • vendor - Brand/supplier
    • product_type - Category
    • created_at - When added to store
    • updated_at - Last modified
    • tags - Product tags

    Order_line table:

    Individual items in each order.

    Key fields:

    • order_id - Which order
    • product_id - Which product
    • variant_id - Which variant (size, color, etc.)
    • quantity - How many
    • price - Item price
    • total_discount - Discount applied

    Example AI Insights You'll Get

    1. Channel LTV Analysis

    Question AI answers: "Which acquisition channel brings the highest lifetime value customers?"

    Typical finding: "Google Ads customers: $230 average LTV Meta Ads customers: $180 average LTV Organic customers: $340 average LTV

    BUT: First purchase value is similar ($85-95)

    Difference: Repeat purchase rate

    • Organic: 42% make second purchase
    • Google Ads: 28%
    • Meta Ads: 24%"

    Recommendation: "Shift 30% of paid budget to retargeting existing customers. Expected LTV increase: 18%"

    2. Product Bundling Opportunities

    Question AI answers: "Which products are frequently bought together?"

    Typical finding: "Customers who buy Product A have 67% probability of buying Product B within 60 days. Currently: They buy these in separate orders (higher shipping, worse experience). Opportunity: Create bundle."

    Recommendation: "Create 'A + B Bundle' with 10% discount → Estimated 340 additional bundle sales/month → +$12K revenue"

    3. Discount Impact on LTV

    Question AI answers: "Do discount codes hurt long-term customer value?"

    Typical finding: "First-time customers using 20%+ discount:

    • Higher cart value initially (+$15)
    • But 40% lower repeat purchase rate
    • 6-month LTV: $95

    First-time customers with no discount:

    • Lower initial cart ($62 vs $77)
    • 52% repeat purchase rate
    • 6-month LTV: $187"

    Recommendation: "Replace deep first-order discounts with loyalty rewards. Expected LTV increase: 30%"

    4. Abandoned Checkout Recovery

    Question AI answers: "Why are people abandoning checkout, and which are worth recovering?"

    Typical finding: "High-value abandoned carts (>$150):

    • 68% abandon at shipping info step
    • Reason analysis: Unexpected shipping cost
    • Recovery rate with email: 22%
    • Average recovered value: $127"

    Recommendation: "Show shipping cost earlier in flow. Send recovery email with free shipping threshold. Expected recovery: $8.4K/month"

    Combining Shopify with Ad Platforms

    For best insights, connect:

    1. Shopify (order data, LTV)
    2. Google Ads (acquisition cost)
    3. Meta Ads (creative performance)
    4. GA4 (user journey, attribution)

    Cogny merges these data sources.

    What this unlocks:

    True CAC by channel: Not just ad spend ÷ conversions. Includes returns, discounts, customer service costs.

    Multi-touch attribution: See the full journey:

    1. Saw Meta ad (awareness)
    2. Searched brand on Google (consideration)
    3. Clicked Google ad (intent)
    4. Visited site 3x via direct (evaluation)
    5. Purchased via email campaign (conversion)

    Who gets credit? All of them (proportionally).

    LTV:CAC ratio by source: "Google Shopping: LTV $280, CAC $85 = 3.3x Meta Prospecting: LTV $190, CAC $78 = 2.4x Google Brand: LTV $310, CAC $22 = 14.1x"

    Recommendation: "Increase Google Brand budget 40%, decrease Meta prospecting 25%"

    Common Issues

    "Fivetran sync failing"

    Check:

    1. Shopify app still installed? (Shopify Admin → Apps)
    2. API permissions granted? (Re-authorize if needed)
    3. Billing address added to Shopify? (Required for API access)

    Re-run connector test in Fivetran.

    "Missing historical orders"

    Fivetran's free tier has limits on historical data. Upgrade to paid plan for full history.

    Or: Shopify API limits historical access for new apps to 60 days. Workaround: Contact Shopify support to request extended access.

    "Data looks outdated"

    Check sync schedule in Fivetran.

    Free tier: Daily sync Paid tier: Hourly or real-time

    For real-time needs, upgrade plan.

    "Some products missing"

    Check product status in Shopify:

    • Archived products aren't synced by default
    • Draft products aren't included
    • Deleted products disappear from future syncs

    "Can't see refund data"

    Refunds are in separate table: refunds and order_adjustments

    Cogny joins these automatically, but check both tables exist in BigQuery.

    Pro Tips

    1. Track UTM parameters in Shopify

    Shopify captures UTM parameters automatically in landing_site field.

    Make sure your ads use UTM tags:

    https://yourstore.com/product?utm_source=facebook&utm_medium=cpc&utm_campaign=summer_sale

    AI uses these for attribution.

    2. Use Shopify's customer tags

    Tag customers in Shopify Admin:

    • "VIP"
    • "Influencer"
    • "Wholesale"
    • "High Risk"

    These tags sync to BigQuery. AI analyzes behavior by segment.

    3. Configure abandoned checkout emails

    More abandoned checkout data = better AI insights on why people leave.

    Shopify can send these automatically: Settings → Checkout → Abandoned checkouts

    4. Enable Shopify Payments for better data

    Shopify Payments provides richer transaction data:

    • Payment method
    • Fraud analysis
    • Gateway fees

    Third-party gateways provide less detail.

    5. Sync historical data first

    Before making changes, sync at least 3 months of historical data. AI needs baseline to measure improvement.

    6. Connect Shopify + GA4

    Install GA4 on your Shopify store: Online Store → Preferences → Google Analytics

    Both Shopify (transaction data) and GA4 (behavior data) in Cogny = powerful insights.

    What You Can Do Now

    Immediate actions:

    1. Identify top LTV customers

      • See which acquisition sources bring best customers
      • Shift marketing budget accordingly
      • Build lookalike audiences

    2. Find product opportunities

      • Best sellers by cohort
      • Products that drive repeats
      • Slow movers to discontinue

    3. Optimize pricing and discounts

      • See impact of promotions on LTV
      • Identify optimal discount levels
      • Stop over-discounting

    4. Improve retention

      • Identify churn signals
      • See what drives repeat purchases
      • Get re-engagement recommendations

    Advanced: Custom SQL Queries

    You own the BigQuery data. Write custom queries anytime.

    Example: Find customers who bought once but not again

    SELECT
  c.email,
  c.first_name,
  c.total_spent,
  DATE_DIFF(CURRENT_DATE(), DATE(c.created_at), DAY) as days_since_first_purchase
FROM `shopify.customers` c
WHERE c.orders_count = 1
  AND DATE_DIFF(CURRENT_DATE(), DATE(c.created_at), DAY) > 60
ORDER BY c.total_spent DESC
LIMIT 100

    Use case: Re-engagement campaign for one-time buyers.

    Example: Top products by repeat purchase rate

    WITH repeat_products AS (
  SELECT
    ol.product_id,
    p.title,
    COUNT(DISTINCT o.customer_id) as unique_customers,
    COUNT(DISTINCT CASE WHEN customer_order_number > 1 THEN o.customer_id END) as repeat_customers
  FROM `shopify.order_line` ol
  JOIN `shopify.orders` o ON ol.order_id = o.id
  JOIN `shopify.products` p ON ol.product_id = p.id
  GROUP BY ol.product_id, p.title
)
SELECT
  title,
  unique_customers,
  repeat_customers,
  ROUND(100.0 * repeat_customers / unique_customers, 1) as repeat_rate_pct
FROM repeat_products
WHERE unique_customers >= 20
ORDER BY repeat_rate_pct DESC
LIMIT 20

    Use case: Stock more of products that drive loyalty.

    Cogny writes these queries automatically. But you can run your own anytime.

    Next Steps

    Immediate actions:

    • Connect BigQuery to Cogny for AI analysis of your Shopify data
    • Review your top 20% of customers—find common acquisition patterns

    Advanced implementation:

    Need help? Contact support

    FAQ

    Q: Will this slow down my Shopify store?

    No.

    Fivetran reads data via Shopify's API. Zero impact on storefront performance.

    Q: What about customer privacy (GDPR)?

    You're the data controller. Customer data stays in YOUR BigQuery. Cogny only reads (never shares or sells).

    For GDPR compliance:

    • Update your privacy policy to mention data analysis
    • Provide customer data deletion on request
    • BigQuery supports right-to-erasure

    Q: Can I connect multiple Shopify stores?

    Yes.

    Each store becomes a separate dataset in BigQuery. Cogny can analyze them together or separately.

    Great for:

    • Multi-brand businesses
    • International stores
    • Separate B2B and B2C stores

    Q: What's the cost?

    Fivetran:

    • Free tier: 500K rows/month (enough for small stores)
    • Paid: $60-100/month for most stores

    BigQuery:

    • Storage: ~$1-5/month for typical Shopify data
    • Queries: Included in Google Cloud free tier for most usage

    Total: ~$5-100/month depending on store size.

    Q: How often does data sync?

    Fivetran free tier: Daily (overnight) Fivetran paid: Every 6 hours, hourly, or real-time

    For most stores, daily is fine. AI insights don't change hour-to-hour.

    Q: Can I see real-time sales in Cogny?

    Near real-time, yes (with paid Fivetran + streaming sync).

    But AI analysis is batch (runs daily). For real-time dashboards, use Shopify's native analytics.

    Q: What happens if I cancel Fivetran?

    Data already in BigQuery stays there. Future updates stop.

    You keep historical data forever (unless you delete it).

    Q: Can I use this with Shopify Plus?

    Yes.

    Actually works better on Plus:

    • More API call limit
    • Better historical data access
    • Richer data fields

    Ready to See What AI Finds in Your Store?

    Most Shopify stores discover:

    • 15-25% of customers drive 80% of LTV (who are they? where do they come from?)
    • 30-40% of product catalog generates <2% of revenue (dead weight)
    • 20-35% of marketing budget goes to channels with lowest LTV customers
    • Specific product combinations that predict 3-4x repeat purchase rate

    You can't see this in Shopify's dashboard. Too much data. Too many dimensions. Too many interactions.

    AI finds it in minutes.

    Not set up yet?

    Schedule a demo and we'll show you exactly what insights are hiding in your Shopify data.

    About This Guide

    Written by the Cogny team—built by the founders who created AI optimization systems for Netflix, Zalando, and Momondo at Campanja, and scaled growth for Kry, Epidemic Sound, and Yubico through GrowthHackers.se.

    We've integrated Shopify data for hundreds of e-commerce brands. This is the proven process.

    Last Updated: December 28, 2024

    Ready to Get Started?

    See Cogny in Action

    Schedule a demo to see how AI can transform your marketing analytics and automate your growth optimization.

    Schedule Demo