Back to posts

Send Google Analytics data to your SQL with Supabase

Berkay Demirbas

You may want to connect Google Analytics with databases such as CRM for growth and remarketing strategies.

If you want to know about the Google Analytics to CRM integration, you can find Google’s official summary here.

In this case, we will send any user data to our SQL database using Supabase through Google Tag Manager. It’s an experimental method, so I’m not recommended for production because of real-time SQL data sync issues.

First of all, you need to open a new supabase account and create a new table included columns like userID, userLogin, userSegment, etc.

Creating supabase database

Be sure your database is RLS (row-level security) disabled. This is required for fetching and writing data to your table.

RLS disabled

Go to your GTM account and create a new tag for Supabase CDN and script.

GTM tags

And then inject Supabase script CDN as Custom HTML Tag.

<script src="https://cdn.jsdelivr.net/npm/@supabase/supabase-js"></script>

Alternatively, you can try to add section for loading CDN like this;

var SupabaseCDN = document.createElement('script'); SupabaseCDN.src = 'https://cdn.jsdelivr.net/npm/@supabase/supabase-js'; document.head.appendChild(SupabaseCDN);

In Supabase Post Data Script, you should set up for auth configuration like Key and API URL. You can find here where is your API settings.

var SUPABASE_URL = {{Supabase API URL}} var SUPABASE_KEY = {{Supabase Key}} var supabase = supabase.createClient(SUPABASE_URL, SUPABASE_KEY, { fetch: fetch.bind(globalThis) })

And create your data scheme for dataLayer and SQL. For example, our table has a userID, we should push our clientID which is generated by GA.

ga(function(tracker) { var clientId = tracker.get('clientId'); window.dataLayer = window.dataLayer || []; dataLayer.push({ userID: clientId userData: userData }); var userDataArray = [userData]

You can create external dataLayer as an array for the user source.

And you can insert this user-based generated data to SQL like this on Node environments.

async function addToCRMList() { let { data, error } = await supabase .from('CRM') .insert({ userData: userData }) if (error) console.log('error', error) return data }

But unfortunately, Google Tag Manager’s code compiler doesn’t support ES6, so you will have to compile manually to ES5, a very ugly version. You can use Babel’s playground at this time.

function addToCRMList() { var _ref, data, error; return regeneratorRuntime.async(function updateCRMList$(context$1$0) { while (1) switch (context$1$0.prev = context$1$0.next) { case 0: context$1$0.next = 2; return regeneratorRuntime.awrap(supabase.from('CRM').insert({ userData: userData})); case 2: _ref = context$1$0.sent; data = _ref.data; error = _ref.error; if (error) console.log('error', error); return context$1$0.abrupt('return', data); case 7: case 'end': return context$1$0.stop(); } }, null, this); } //Send data to Supabase addToCRMList().then(function (data) { console.log(data); });

Add an All Pages trigger for this tag:

Supabase GTM script trigger

Finally, when your users visit your website, always log to the database and each row can be matched with dataLayer array.

final database

Supabase Google Analytics Database
© 2022, I use Cookie 🍪