Step 1: Google Cloud SQL database

Task # 1

To track additional information like device and browser details, and unsubscription HTML link from a Google Cloud SQL database list, you can modify the Cloud Function code as follows:

const { createCanvas } = require('canvas');
const crypto = require('crypto');
const { Pool } = require('pg');

const pool = new Pool({
  // Configure your Google Cloud SQL database connection details
  user: '[DB_USER]',
  password: '[DB_PASSWORD]',
  host: '[DB_HOST]',
  database: '[DB_NAME]',
  port: '[DB_PORT]'
});

function generateTrackingCode() {
  const codeLength = 12;
  const characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  let code = '';
  for (let i = 0; i < codeLength; i++) {
    const randomIndex = Math.floor(Math.random() * characters.length);
    code += characters.charAt(randomIndex);
  }
  return code;
}

exports.generateTrackingPixel = async (req, res) => {
  const trackingCode = generateTrackingCode();

  const device = req.headers['user-agent'];
  const browser = req.query.browser || '';
  const unsubscriptionLink = req.query.unsublink || '';

  const canvas = createCanvas(1, 1);
  const ctx = canvas.getContext('2d');
  ctx.fillStyle = '#000';
  ctx.fillRect(0, 0, 1, 1);

  const buffer = canvas.toBuffer('image/png');
  res.set('Content-Type', 'image/png');
  res.send(buffer);

  const insertQuery = 'INSERT INTO email_tracking (tracking_code, device, browser, unsubscription_link) VALUES ($1, $2, $3, $4)';
  const insertValues = [trackingCode, device, browser, unsubscriptionLink];

  try {
    await pool.query(insertQuery, insertValues);
  } catch (error) {
    console.error('Failed to insert tracking data:', error);
  }

  console.log(`Generated tracking code: ${trackingCode}`);
};

In the code above, we've made the following changes:

Added the necessary imports and configured the PostgreSQL connection using pg.Pool to interact with the Google Cloud SQL database.
Extracted the device and browser information from the request headers.
Extracted the unsubscriptionLink from the query parameters.
Modified the INSERT query to include the new custom dimensions (device, browser, unsubscription_link) and their corresponding values.
Executed the INSERT query to store the tracking data in the Google Cloud SQL database.

Make sure to replace the placeholder values ([DB_USER], [DB_PASSWORD], [DB_HOST], [DB_NAME], [DB_PORT]) with your actual database connection details.

By implementing these changes, you can track the device, browser, and unsubscription link information along with the email tracking code in the Google Cloud SQL database for further analysis and reporting.

Remember to deploy the updated Cloud Function to apply these changes.

DevCEDTeam / CED

Step 1: Google Cloud SQL database #14