Workflow: Automate Google Analytics Reporting - AlexK1919
Integrations: code, gmail, googleAnalytics, manualTrigger, scheduleTrigger, set, stickyNote

{"id": "21IdmArlNT9LlaFf", "meta": {"instanceId": "d868e3d040e7bda892c81b17cf446053ea25d2556fcef89cbe19dd61a3e876e9", "templateCredsSetupCompleted": true}, "name": "Automate Google Analytics Reporting - AlexK1919", "tags": [{"id": "BimZXo1NKE7JdlXm", "name": "Google Analytics", "createdAt": "2024-11-13T18:08:04.053Z", "updatedAt": "2024-11-13T18:08:04.053Z"}, {"id": "nezaWFCGa7eZsVKu", "name": "Utility", "createdAt": "2024-11-13T18:08:08.207Z", "updatedAt": "2024-11-13T18:08:08.207Z"}], "nodes": [{"id": "1b3a0365-92e0-4b51-9a5f-2562b7f3de39", "name": "When clicking \u2018Test workflow\u2019", "type": "n8n-nodes-base.manualTrigger", "position": [560, 940], "parameters": {}, "typeVersion": 1}, {"id": "5c35f802-82e7-457a-9f11-4d9026cbf0e0", "name": "Sticky Note", "type": "n8n-nodes-base.stickyNote", "position": [760, 360], "parameters": {"color": 6, "width": 1270.4518485107694, "height": 209.13454984057833, "content": "# Aggregate Google Analytics data and Email the results\n\nThis workflow will check for country views, page engagement and google search console results. It will take this week's data and compare it to last week's data.\n\n[Credit to Keith Rumjahn for the original workflow, which I modified.](https://rumjahn.com/how-i-used-a-i-to-be-an-seo-expert-and-analyzed-my-google-analytics-data-in-n8n-and-make-com/)"}, "typeVersion": 1}, {"id": "54288de3-60ec-4119-a067-e6b8e67949b9", "name": "Sticky Note1", "type": "n8n-nodes-base.stickyNote", "position": [760, 600], "parameters": {"color": 4, "width": 1269.8517211291685, "height": 745.919853945687, "content": "## Property ID\n\n1. Create your [Google Analytics Credentials](https://docs.n8n.io/integrations/builtin/credentials/google/oauth-single-service/?utm_source=n8n_app&utm_medium=credential_settings&utm_campaign=create_new_credentials_modal)\n2. Enter your [property ID](https://developers.google.com/analytics/devguides/reporting/data/v1/property-id) or Choose from the List of Properties."}, "typeVersion": 1}, {"id": "cc1c37f3-6354-4413-9ee1-473509fc23e7", "name": "Get Page Engagement Stats for this week", "type": "n8n-nodes-base.googleAnalytics", "position": [840, 740], "parameters": {"simple": false, "returnAll": true, "metricsGA4": {"metricValues": [{"name": "screenPageViews", "listName": "other"}, {"name": "activeUsers", "listName": "other"}, {"name": "screenPageViewsPerUser", "listName": "other"}, {"name": "eventCount", "listName": "other"}]}, "propertyId": {"__rl": true, "mode": "list", "value": "420633845", "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/", "cachedResultName": "Kenetic Brand Builders"}, "dimensionsGA4": {"dimensionValues": [{"name": "unifiedScreenName", "listName": "other"}]}, "additionalFields": {"keepEmptyRows": true}}, "credentials": {"googleAnalyticsOAuth2": {"id": "8OdVzOGJqhJ3ti8k", "name": "KBB Google Analytics account"}}, "typeVersion": 2}, {"id": "c6b8f171-0e43-4d55-9ba0-c17a8cddca5b", "name": "Get Page Engagement Stats for prior week", "type": "n8n-nodes-base.googleAnalytics", "position": [1240, 740], "parameters": {"simple": false, "endDate": "={{$today.minus({days: 7})}}", "dateRange": "custom", "returnAll": true, "startDate": "={{$today.minus({days: 14})}}", "metricsGA4": {"metricValues": [{"name": "screenPageViews", "listName": "other"}, {"name": "activeUsers", "listName": "other"}, {"name": "screenPageViewsPerUser", "listName": "other"}, {"name": "eventCount", "listName": "other"}]}, "propertyId": {"__rl": true, "mode": "list", "value": "420633845", "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/", "cachedResultName": "Kenetic Brand Builders"}, "dimensionsGA4": {"dimensionValues": [{"name": "unifiedScreenName", "listName": "other"}]}, "additionalFields": {"keepEmptyRows": true}}, "credentials": {"googleAnalyticsOAuth2": {"id": "8OdVzOGJqhJ3ti8k", "name": "KBB Google Analytics account"}}, "typeVersion": 2}, {"id": "3c056c98-055d-4dc5-870d-d9c01c467714", "name": "Get Google Search Results for this week", "type": "n8n-nodes-base.googleAnalytics", "position": [1640, 740], "parameters": {"simple": false, "returnAll": true, "metricsGA4": {"metricValues": [{"name": "activeUsers", "listName": "other"}, {"name": "engagedSessions", "listName": "other"}, {"name": "engagementRate", "listName": "other"}, {"name": "eventCount", "listName": "other"}, {"name": "organicGoogleSearchAveragePosition", "listName": "other"}, {"name": "organicGoogleSearchClickThroughRate", "listName": "other"}, {"name": "organicGoogleSearchClicks", "listName": "other"}, {"name": "organicGoogleSearchImpressions", "listName": "other"}]}, "propertyId": {"__rl": true, "mode": "list", "value": "420633845", "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/", "cachedResultName": "Kenetic Brand Builders"}, "dimensionsGA4": {"dimensionValues": [{"name": "landingPagePlusQueryString", "listName": "other"}]}, "additionalFields": {"keepEmptyRows": true}}, "credentials": {"googleAnalyticsOAuth2": {"id": "8OdVzOGJqhJ3ti8k", "name": "KBB Google Analytics account"}}, "typeVersion": 2}, {"id": "ea5cdc7a-b00b-45d6-86e9-dd2a61451cca", "name": "Get Country views data for this week", "type": "n8n-nodes-base.googleAnalytics", "position": [1240, 940], "parameters": {"simple": false, "returnAll": true, "metricsGA4": {"metricValues": [{"name": "activeUsers", "listName": "other"}, {"name": "newUsers", "listName": "other"}, {"name": "engagementRate", "listName": "other"}, {"name": "engagedSessions", "listName": "other"}, {"name": "eventCount", "listName": "other"}, {"listName": "other"}, {"name": "sessions", "listName": "other"}]}, "propertyId": {"__rl": true, "mode": "list", "value": "420633845", "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/", "cachedResultName": "Kenetic Brand Builders"}, "dimensionsGA4": {"dimensionValues": [{"name": "country", "listName": "other"}]}, "additionalFields": {"keepEmptyRows": true}}, "credentials": {"googleAnalyticsOAuth2": {"id": "8OdVzOGJqhJ3ti8k", "name": "KBB Google Analytics account"}}, "typeVersion": 2}, {"id": "d52e9084-d00b-490f-b107-ed9904423a03", "name": "Sticky Note4", "type": "n8n-nodes-base.stickyNote", "position": [500, 360], "parameters": {"color": 6, "width": 231.71528995536218, "height": 986.0715248510506, "content": "## AlexK1919 \n![Alex Kim](https://media.licdn.com/dms/image/v2/D5603AQFOYMkqCPl6Sw/profile-displayphoto-shrink_400_400/profile-displayphoto-shrink_400_400/0/1718309808352?e=1736985600&v=beta&t=pQKm7lQfUU1ytuC2Gq1PRxNY-XmROFWbo-BjzUPxWOs)\n\nI\u2019m Alex Kim, an AI-Native Workflow Automation Architect Building Solutions to Optimize your Personal and Professional Life.\n\n[Info](https://beacons.ai/alexk1919)"}, "typeVersion": 1}, {"id": "d1160f2f-80ca-4900-8b85-d94073cf38e3", "name": "Aggregate Data", "type": "n8n-nodes-base.code", "position": [1040, 1140], "parameters": {"jsCode": "// Helper function to decode and parse a URL-encoded JSON string\nfunction decodeUrlString(urlString) {\n    try {\n        const decoded = JSON.parse(decodeURIComponent(urlString));\n        console.log('Decoded URL string:', JSON.stringify(decoded, null, 2));\n        return decoded;\n    } catch (error) {\n        console.log('Error decoding URL string:', error.message);\n        return [];\n    }\n}\n\n// Main function to aggregate data\nfunction aggregateData(items) {\n    // Extract each urlString from the input\n    const data = items[0]?.json; // Get the first JSON object from input\n\n    if (!data) {\n        console.log('No data found in input items.');\n        return {};\n    }\n\n    // Decode each urlString\n    const engagementStatsThisWeek = decodeUrlString(data.urlString1 || '');\n    const engagementStatsPriorWeek = decodeUrlString(data.urlString2 || '');\n    const searchResultsThisWeek = decodeUrlString(data.urlString3 || '');\n    const searchResultsLastWeek = decodeUrlString(data.urlString4 || '');\n    const countryViewsThisWeek = decodeUrlString(data.urlString5 || '');\n    const countryViewsLastWeek = decodeUrlString(data.urlString6 || '');\n\n    // Aggregate the decoded data into a structured object\n    const aggregatedData = {\n        engagementStats: {\n            thisWeek: engagementStatsThisWeek,\n            priorWeek: engagementStatsPriorWeek,\n        },\n        searchResults: {\n            thisWeek: searchResultsThisWeek,\n            lastWeek: searchResultsLastWeek,\n        },\n        countryViews: {\n            thisWeek: countryViewsThisWeek,\n            lastWeek: countryViewsLastWeek,\n        },\n    };\n\n    console.log('Final Aggregated Data:', JSON.stringify(aggregatedData, null, 2));\n    return aggregatedData;\n}\n\n// Get input data from all nodes\nconst items = $input.all();\nconsole.log('Input items to Aggregate Data:', JSON.stringify(items, null, 2));\n\n// Perform aggregation\nconst aggregatedResult = aggregateData(items);\n\n// Output the aggregated result for downstream processing\nreturn { json: aggregatedResult };\n"}, "typeVersion": 2}, {"id": "14fea93c-7d9c-4f58-96a3-b241f6b0bcec", "name": "Get Google Search Results for prior week", "type": "n8n-nodes-base.googleAnalytics", "position": [840, 940], "parameters": {"simple": false, "endDate": "={{$today.minus({days: 7})}}", "dateRange": "custom", "returnAll": true, "startDate": "={{$today.minus({days: 14})}}", "metricsGA4": {"metricValues": [{"name": "activeUsers", "listName": "other"}, {"name": "engagedSessions", "listName": "other"}, {"name": "engagementRate", "listName": "other"}, {"name": "eventCount", "listName": "other"}, {"name": "organicGoogleSearchAveragePosition", "listName": "other"}, {"name": "organicGoogleSearchClickThroughRate", "listName": "other"}, {"name": "organicGoogleSearchClicks", "listName": "other"}, {"name": "organicGoogleSearchImpressions", "listName": "other"}]}, "propertyId": {"__rl": true, "mode": "list", "value": "420633845", "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/", "cachedResultName": "Kenetic Brand Builders"}, "dimensionsGA4": {"dimensionValues": [{"name": "landingPagePlusQueryString", "listName": "other"}]}, "additionalFields": {"keepEmptyRows": true}}, "credentials": {"googleAnalyticsOAuth2": {"id": "8OdVzOGJqhJ3ti8k", "name": "KBB Google Analytics account"}}, "typeVersion": 2}, {"id": "436c7977-0214-4b23-924a-3915c0f27d28", "name": "Get Country views data for prior week", "type": "n8n-nodes-base.googleAnalytics", "position": [1640, 940], "parameters": {"simple": false, "endDate": "={{$today.minus({days: 7})}}", "dateRange": "custom", "returnAll": true, "startDate": "={{$today.minus({days: 14})}}", "metricsGA4": {"metricValues": [{"name": "activeUsers", "listName": "other"}, {"name": "newUsers", "listName": "other"}, {"name": "engagementRate", "listName": "other"}, {"name": "engagedSessions", "listName": "other"}, {"name": "eventCount", "listName": "other"}, {"listName": "other"}, {"name": "sessions", "listName": "other"}]}, "propertyId": {"__rl": true, "mode": "list", "value": "420633845", "cachedResultUrl": "https://analytics.google.com/analytics/web/#/p420633845/", "cachedResultName": "Kenetic Brand Builders"}, "dimensionsGA4": {"dimensionValues": [{"name": "country", "listName": "other"}]}, "additionalFields": {"keepEmptyRows": true}}, "credentials": {"googleAnalyticsOAuth2": {"id": "8OdVzOGJqhJ3ti8k", "name": "KBB Google Analytics account"}}, "typeVersion": 2}, {"id": "15f3edcb-2e31-4faa-8db2-62da69bbfe8d", "name": "Parse - Get Page Engagement This Week", "type": "n8n-nodes-base.code", "position": [1040, 740], "parameters": {"jsCode": "function transformToUrlString(items) {\n    // Debug logging\n    console.log('Input items:', JSON.stringify(items, null, 2));\n    \n    // Check if items is an array and has content\n    if (!Array.isArray(items) || items.length === 0) {\n        console.log('Items is not an array or is empty');\n        throw new Error('Invalid data structure');\n    }\n\n    // Check if first item exists and has json property\n    if (!items[0] || !items[0].json) {\n        console.log('First item is missing or has no json property');\n        throw new Error('Invalid data structure');\n    }\n\n    // Get the analytics data\n    const analyticsData = items[0].json;\n    \n    // Check if analyticsData has rows\n    if (!analyticsData || !Array.isArray(analyticsData.rows)) {\n        console.log('Analytics data is missing or has no rows array');\n        throw new Error('Invalid data structure');\n    }\n    \n    // Map each row to a simplified object\n    const simplified = analyticsData.rows.map(row => {\n        if (!row.dimensionValues?.[0]?.value || !row.metricValues?.length) {\n            console.log('Invalid row structure:', row);\n            throw new Error('Invalid row structure');\n        }\n        \n        return {\n            page: row.dimensionValues[0].value,\n            pageViews: parseInt(row.metricValues[0].value) || 0,\n            activeUsers: parseInt(row.metricValues[1].value) || 0,\n            viewsPerUser: parseFloat(row.metricValues[2].value) || 0,\n            eventCount: parseInt(row.metricValues[3].value) || 0\n        };\n    });\n    \n    // Convert to JSON string and encode for URL\n    return encodeURIComponent(JSON.stringify(simplified));\n}\n\n// Get input data and transform it\nconst urlString = transformToUrlString($input.all());\n\n// Return the result\nreturn { json: { urlString } };"}, "typeVersion": 2}, {"id": "46cd21cd-c7f4-45cb-a724-db8a122f9de3", "name": "Parse - Get Page Engagement Prior Week", "type": "n8n-nodes-base.code", "position": [1440, 740], "parameters": {"jsCode": "function transformToUrlString(items) {\n    // Debug logging\n    console.log('Input items:', JSON.stringify(items, null, 2));\n    \n    // Check if items is an array and has content\n    if (!Array.isArray(items) || items.length === 0) {\n        console.log('Items is not an array or is empty');\n        throw new Error('Invalid data structure');\n    }\n\n    // Check if first item exists and has json property\n    if (!items[0] || !items[0].json) {\n        console.log('First item is missing or has no json property');\n        throw new Error('Invalid data structure');\n    }\n\n    // Get the analytics data\n    const analyticsData = items[0].json;\n    \n    // Check if analyticsData has rows\n    if (!analyticsData || !Array.isArray(analyticsData.rows)) {\n        console.log('Analytics data is missing or has no rows array');\n        throw new Error('Invalid data structure');\n    }\n    \n    // Filter out invalid rows and map each valid row to a simplified object\n    const simplified = analyticsData.rows\n        .filter(row => {\n            // Check if row is valid and its properties exist\n            const isValid = row \n                && row.dimensionValues \n                && row.dimensionValues[0] \n                && row.dimensionValues[0].value \n                && row.metricValues \n                && row.metricValues.length > 0;\n            \n            if (!isValid) {\n                console.log('Ignoring invalid or null row:', row);\n            }\n            return isValid;\n        })\n        .map(row => ({\n            page: row.dimensionValues[0].value,\n            pageViews: parseInt(row.metricValues[0].value) || 0,\n            activeUsers: parseInt(row.metricValues[1]?.value) || 0,\n            viewsPerUser: parseFloat(row.metricValues[2]?.value) || 0,\n            eventCount: parseInt(row.metricValues[3]?.value) || 0\n        }));\n    \n    // Convert to JSON string and encode for URL\n    return encodeURIComponent(JSON.stringify(simplified));\n}\n\n// Get input data and transform it\nconst urlString = transformToUrlString($input.all());\n\n// Return the result\nreturn { json: { urlString } };\n"}, "typeVersion": 2}, {"id": "6bef6c5c-74a1-4566-8b8d-372414ae9b0d", "name": "Parse - Get Google Search This Week", "type": "n8n-nodes-base.code", "position": [1840, 740], "parameters": {"jsCode": "function transformToUrlString(items) {\n    // Check if items is an array and get the JSON property\n    const data = items[0]?.json;\n\n    if (!data || !Array.isArray(data.rows)) {\n        console.log('No valid data found');\n        return encodeURIComponent(JSON.stringify([]));\n    }\n\n    try {\n        // Process each row, skipping invalid or null entries\n        const simplified = data.rows\n            .filter(row => {\n                // Skip null rows or rows without dimensionValues or metricValues\n                const isValid = row && row.dimensionValues && Array.isArray(row.metricValues);\n                if (!isValid) {\n                    console.log('Skipping invalid row:', row);\n                }\n                return isValid;\n            })\n            .map(row => ({\n                page: row.dimensionValues[0]?.value || 'Unknown',\n                activeUsers: parseInt(row.metricValues[0]?.value) || 0,\n                engagedSessions: parseInt(row.metricValues[1]?.value) || 0,\n                engagementRate: parseFloat(row.metricValues[2]?.value) || 0.0,\n                eventCount: parseInt(row.metricValues[3]?.value) || 0,\n                avgPosition: parseFloat(row.metricValues[4]?.value) || 0.0,\n                ctr: parseFloat(row.metricValues[5]?.value) || 0.0,\n                clicks: parseInt(row.metricValues[6]?.value) || 0,\n                impressions: parseInt(row.metricValues[7]?.value) || 0\n            }));\n\n        // Encode the simplified data as a URL-safe string\n        return encodeURIComponent(JSON.stringify(simplified));\n    } catch (error) {\n        console.log('Error processing data:', error.message);\n        throw new Error('Invalid data structure');\n    }\n}\n\n// Get the input data\nconst items = $input.all();\n\n// Process the data\nconst result = transformToUrlString(items);\n\n// Return the result\nreturn { json: { urlString: result } };\n"}, "typeVersion": 2}, {"id": "d0c2b575-6bf0-40d7-80e9-c4f1702df7c8", "name": "Parse - Get Google Search Prior Week", "type": "n8n-nodes-base.code", "position": [1040, 940], "parameters": {"jsCode": "function transformToUrlString(items) {\n    // Ensure the input is valid and contains data\n    const data = items[0]?.json;\n\n    if (!data || !Array.isArray(data.rows)) {\n        console.log('No valid data found');\n        return encodeURIComponent(JSON.stringify([]));\n    }\n\n    try {\n        // Process each row, skipping null or invalid rows\n        const simplified = data.rows\n            .filter(row => {\n                // Skip null rows\n                const isValid = row && row.dimensionValues && Array.isArray(row.metricValues);\n                if (!isValid) {\n                    console.log('Skipping invalid or null row:', row);\n                }\n                return isValid;\n            })\n            .map(row => ({\n                page: row.dimensionValues[0]?.value || 'Unknown',\n                activeUsers: parseInt(row.metricValues[0]?.value) || 0,\n                engagedSessions: parseInt(row.metricValues[1]?.value) || 0,\n                engagementRate: parseFloat(row.metricValues[2]?.value) || 0.0,\n                eventCount: parseInt(row.metricValues[3]?.value) || 0,\n                avgPosition: parseFloat(row.metricValues[4]?.value) || 0.0,\n                ctr: parseFloat(row.metricValues[5]?.value) || 0.0,\n                clicks: parseInt(row.metricValues[6]?.value) || 0,\n                impressions: parseInt(row.metricValues[7]?.value) || 0\n            }));\n\n        // If no valid rows, return an empty array\n        if (simplified.length === 0) {\n            console.log('No valid rows to process');\n            return encodeURIComponent(JSON.stringify([]));\n        }\n\n        // Encode the simplified data as a URL-safe string\n        return encodeURIComponent(JSON.stringify(simplified));\n    } catch (error) {\n        console.log('Error processing data:', error.message);\n        throw new Error('Invalid data structure');\n    }\n}\n\n// Get the input data\nconst items = $input.all();\n\n// Process the data\nconst result = transformToUrlString(items);\n\n// Return the result\nreturn { json: { urlString: result } };\n"}, "typeVersion": 2}, {"id": "1fca2a6c-1b60-4860-ad60-3e0696f2cb07", "name": "Parse - Country Views This Week", "type": "n8n-nodes-base.code", "position": [1440, 940], "parameters": {"jsCode": "function transformToUrlString(items) {\n    // In n8n, we need to check if items is an array and get the json property\n    const data = items[0].json;\n    \n    if (!data || !data.rows) {\n        console.log('No valid data found');\n        return encodeURIComponent(JSON.stringify([]));\n    }\n    \n    try {\n        // Process each row\n        const simplified = data.rows.map(row => ({\n            country: row.dimensionValues[0].value,\n            activeUsers: parseInt(row.metricValues[0].value) || 0,\n            newUsers: parseInt(row.metricValues[1].value) || 0,\n            engagementRate: parseFloat(row.metricValues[2].value) || 0,\n            engagedSessions: parseInt(row.metricValues[3].value) || 0,\n            eventCount: parseInt(row.metricValues[4].value) || 0,\n            totalUsers: parseInt(row.metricValues[5].value) || 0,\n            sessions: parseInt(row.metricValues[6].value) || 0\n        }));\n        \n        return encodeURIComponent(JSON.stringify(simplified));\n    } catch (error) {\n        console.log('Error processing data:', error);\n        throw new Error('Invalid data structure');\n    }\n}\n\n// Get the input data\nconst items = $input.all();\n\n// Process the data\nconst result = transformToUrlString(items);\n\n// Return the result\nreturn { json: { urlString: result } };"}, "typeVersion": 2}, {"id": "23679bde-bf02-465a-a656-5eeea0e82f34", "name": "Parse - Country Views Prior Week", "type": "n8n-nodes-base.code", "position": [1840, 940], "parameters": {"jsCode": "function transformToUrlString(items) {\n    // Ensure the input is valid and contains data\n    const data = items[0]?.json;\n\n    if (!data || !Array.isArray(data.rows)) {\n        console.log('No valid data found');\n        return encodeURIComponent(JSON.stringify([]));\n    }\n\n    try {\n        // Process each row, skipping invalid or null rows\n        const simplified = data.rows\n            .filter(row => {\n                // Skip null rows or rows without required properties\n                const isValid = row && row.dimensionValues && Array.isArray(row.metricValues);\n                if (!isValid) {\n                    console.log('Skipping invalid or null row:', row);\n                }\n                return isValid;\n            })\n            .map(row => ({\n                country: row.dimensionValues[0]?.value || 'Unknown',\n                activeUsers: parseInt(row.metricValues[0]?.value) || 0,\n                newUsers: parseInt(row.metricValues[1]?.value) || 0,\n                engagementRate: parseFloat(row.metricValues[2]?.value) || 0.0,\n                engagedSessions: parseInt(row.metricValues[3]?.value) || 0,\n                eventCount: parseInt(row.metricValues[4]?.value) || 0,\n                totalUsers: parseInt(row.metricValues[5]?.value) || 0,\n                sessions: parseInt(row.metricValues[6]?.value) || 0\n            }));\n\n        // If no valid rows, return an empty array\n        if (simplified.length === 0) {\n            console.log('No valid rows to process');\n            return encodeURIComponent(JSON.stringify([]));\n        }\n\n        // Encode the simplified data as a URL-safe string\n        return encodeURIComponent(JSON.stringify(simplified));\n    } catch (error) {\n        console.log('Error processing data:', error.message);\n        throw new Error('Invalid data structure');\n    }\n}\n\n// Get the input data\nconst items = $input.all();\n\n// Process the data\nconst result = transformToUrlString(items);\n\n// Return the result\nreturn { json: { urlString: result } };\n"}, "typeVersion": 2}, {"id": "d6797f36-d715-4821-9747-cea5c87dc2cb", "name": "Set urlStrings", "type": "n8n-nodes-base.set", "position": [840, 1140], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "93efb02f-f2f2-4e52-aa7a-3ccd1fb171cc", "name": "urlString1", "type": "string", "value": "={{ $('Parse - Get Page Engagement This Week').first().json.urlString }}"}, {"id": "5dea3377-0af2-48da-8666-5ee9452e25c5", "name": "urlString2", "type": "string", "value": "={{ $('Parse - Get Page Engagement Prior Week').first().json.urlString }}"}, {"id": "c6aa5d4d-d1e5-4493-96fd-60b2298ff6da", "name": "urlString3", "type": "string", "value": "={{ $('Parse - Get Google Search This Week').first().json.urlString }}"}, {"id": "711cb4fa-3e8c-4ad6-9b25-e2447d7492d1", "name": "urlString4", "type": "string", "value": "={{ $('Parse - Get Google Search Prior Week').first().json.urlString }}"}, {"id": "775bc64a-7986-48fb-a36d-4101158b83f0", "name": "urlString5", "type": "string", "value": "={{ $('Parse - Country Views This Week').first().json.urlString }}"}, {"id": "a6ae27a0-89b5-4a6f-8328-327750835c8d", "name": "urlString6", "type": "string", "value": "={{ $('Parse - Country Views Prior Week').first().json.urlString }}"}]}}, "typeVersion": 3.4}, {"id": "5990f2af-1fc4-4ed5-aea6-c46bebb463a8", "name": "Format Data", "type": "n8n-nodes-base.code", "position": [840, 1480], "parameters": {"jsCode": "const input = $input.first().json;\n\n// Extract data\nconst engagementStats = input.engagementStats || {};\nconst searchResults = input.searchResults || {};\nconst countryViews = input.countryViews || {};\n\n// Helper function to generate HTML for a table\nfunction generateTable(headers, rows, color) {\n    let table = `<table border=\"1\" style=\"border-collapse:collapse; width:100%; border:1px solid ${color};\">`;\n    // Add table headers\n    table += `<thead style=\"background-color:${color}; color:white;\"><tr>`;\n    headers.forEach(header => {\n        table += `<th style=\"padding:8px; text-align:left; border:1px solid ${color};\">${header}</th>`;\n    });\n    table += '</tr></thead>';\n    // Add table rows\n    table += '<tbody>';\n    rows.forEach(row => {\n        table += '<tr>';\n        row.forEach(cell => {\n            table += `<td style=\"padding:8px; border:1px solid ${color};\">${cell}</td>`;\n        });\n        table += '</tr>';\n    });\n    table += '</tbody></table>';\n    return table;\n}\n\n// Get today's date\nconst today = new Date();\nconst formattedDate = today.toLocaleDateString(undefined, {\n    year: 'numeric',\n    month: 'long',\n    day: 'numeric',\n});\n\n// Generate HTML content\nconst title = `GA Report for ${formattedDate}`;\nlet htmlContent = `<h1 style=\"text-align:center; color:#333;\">${title}</h1>`;\n\n// Colors for each segment\nconst engagementColor = '#4CAF50';\nconst searchColor = '#2196F3';\nconst countryColor = '#FF9800';\n\nhtmlContent += `<h2 style=\"color:${engagementColor};\">Engagement Stats</h2>`;\nhtmlContent += `<h3 style=\"color:#333;\">This Week</h3>`;\nif (engagementStats.thisWeek?.length) {\n    const headers = ['Page', 'Page Views', 'Active Users', 'Views per User', 'Event Count'];\n    const rows = engagementStats.thisWeek.map(stat => [\n        stat.page,\n        stat.pageViews,\n        stat.activeUsers,\n        stat.viewsPerUser.toFixed(2),\n        stat.eventCount,\n    ]);\n    htmlContent += generateTable(headers, rows, engagementColor);\n} else {\n    htmlContent += `<p style=\"color:${engagementColor};\">No data available for this week.</p>`;\n}\n\nhtmlContent += `<h3 style=\"color:#333;\">Prior Week</h3>`;\nif (engagementStats.priorWeek?.length) {\n    const headers = ['Page', 'Page Views', 'Active Users', 'Views per User', 'Event Count'];\n    const rows = engagementStats.priorWeek.map(stat => [\n        stat.page,\n        stat.pageViews,\n        stat.activeUsers,\n        stat.viewsPerUser.toFixed(2),\n        stat.eventCount,\n    ]);\n    htmlContent += generateTable(headers, rows, engagementColor);\n} else {\n    htmlContent += `<p style=\"color:${engagementColor};\">No data available for prior week.</p>`;\n}\n\nhtmlContent += `<h2 style=\"color:${searchColor};\">Search Results</h2>`;\nhtmlContent += `<h3 style=\"color:#333;\">This Week</h3>`;\nif (searchResults.thisWeek?.length) {\n    const headers = ['Page', 'Active Users', 'Engaged Sessions', 'Engagement Rate', 'Event Count', 'Avg Position', 'CTR', 'Clicks', 'Impressions'];\n    const rows = searchResults.thisWeek.map(result => [\n        result.page,\n        result.activeUsers,\n        result.engagedSessions,\n        result.engagementRate.toFixed(2),\n        result.eventCount,\n        result.avgPosition.toFixed(2),\n        result.ctr.toFixed(2),\n        result.clicks,\n        result.impressions,\n    ]);\n    htmlContent += generateTable(headers, rows, searchColor);\n} else {\n    htmlContent += `<p style=\"color:${searchColor};\">No data available for this week.</p>`;\n}\n\nhtmlContent += `<h3 style=\"color:#333;\">Last Week</h3>`;\nif (searchResults.lastWeek?.length) {\n    const headers = ['Page', 'Active Users', 'Engaged Sessions', 'Engagement Rate', 'Event Count', 'Avg Position', 'CTR', 'Clicks', 'Impressions'];\n    const rows = searchResults.lastWeek.map(result => [\n        result.page,\n        result.activeUsers,\n        result.engagedSessions,\n        result.engagementRate.toFixed(2),\n        result.eventCount,\n        result.avgPosition.toFixed(2),\n        result.ctr.toFixed(2),\n        result.clicks,\n        result.impressions,\n    ]);\n    htmlContent += generateTable(headers, rows, searchColor);\n} else {\n    htmlContent += `<p style=\"color:${searchColor};\">No data available for last week.</p>`;\n}\n\nhtmlContent += `<h2 style=\"color:${countryColor};\">Country Views</h2>`;\nhtmlContent += `<h3 style=\"color:#333;\">This Week</h3>`;\nif (countryViews.thisWeek?.length) {\n    const headers = ['Country', 'Active Users', 'New Users', 'Engagement Rate', 'Engaged Sessions', 'Event Count', 'Total Users', 'Sessions'];\n    const rows = countryViews.thisWeek.map(view => [\n        view.country,\n        view.activeUsers,\n        view.newUsers,\n        view.engagementRate.toFixed(2),\n        view.engagedSessions,\n        view.eventCount,\n        view.totalUsers,\n        view.sessions,\n    ]);\n    htmlContent += generateTable(headers, rows, countryColor);\n} else {\n    htmlContent += `<p style=\"color:${countryColor};\">No data available for this week.</p>`;\n}\n\nhtmlContent += `<h3 style=\"color:#333;\">Last Week</h3>`;\nif (countryViews.lastWeek?.length) {\n    const headers = ['Country', 'Active Users', 'New Users', 'Engagement Rate', 'Engaged Sessions', 'Event Count', 'Total Users', 'Sessions'];\n    const rows = countryViews.lastWeek.map(view => [\n        view.country,\n        view.activeUsers,\n        view.newUsers,\n        view.engagementRate.toFixed(2),\n        view.engagedSessions,\n        view.eventCount,\n        view.totalUsers,\n        view.sessions,\n    ]);\n    htmlContent += generateTable(headers, rows, countryColor);\n} else {\n    htmlContent += `<p style=\"color:${countryColor};\">No data available for last week.</p>`;\n}\n\n// Output the title and formatted HTML\nreturn {\n    json: {\n        title,\n        htmlContent,\n    }\n};\n"}, "typeVersion": 2}, {"id": "74ad1eef-3a5b-4939-83ee-be0c4b6c13cb", "name": "Input All", "type": "n8n-nodes-base.code", "position": [1240, 1140], "parameters": {"jsCode": "console.log($input.all());\nreturn $input.all();\n"}, "typeVersion": 2}, {"id": "019a40de-80c8-4ede-a86b-babb2c6288eb", "name": "Sticky Note5", "type": "n8n-nodes-base.stickyNote", "position": [760, 1380], "parameters": {"color": 5, "width": 1264.897623827279, "height": 295.7350020039967, "content": "## Format the data and Email"}, "typeVersion": 1}, {"id": "f81326ce-ac35-4463-8444-e9c2b7be027b", "name": "Email the Report", "type": "n8n-nodes-base.gmail", "position": [1040, 1480], "webhookId": "80d4d964-449a-4599-b2de-bca9c8822bbd", "parameters": {"sendTo": "info@alexk1919.com", "message": "={{ $json.htmlContent }}", "options": {"senderName": "Alex Kim"}, "subject": "=KBB {{ $json.title }}"}, "credentials": {"gmailOAuth2": {"id": "7eQtesjR8Fht0INE", "name": "AlexK1919 Gmail"}}, "typeVersion": 2.1}, {"id": "9358a6bc-3696-4647-b02d-891c597d1cb6", "name": "Schedule Trigger", "type": "n8n-nodes-base.scheduleTrigger", "position": [560, 1140], "parameters": {"rule": {"interval": [{}]}}, "typeVersion": 1.2}], "active": false, "pinData": {}, "settings": {"timezone": "America/Los_Angeles", "callerPolicy": "workflowsFromSameOwner", "executionOrder": "v1", "executionTimeout": -1, "saveManualExecutions": false}, "versionId": "34428c27-6f55-44a6-9b0b-f3de72fe2383", "connections": {"Input All": {"main": [[{"node": "Format Data", "type": "main", "index": 0}]]}, "Format Data": {"main": [[{"node": "Email the Report", "type": "main", "index": 0}]]}, "Aggregate Data": {"main": [[{"node": "Input All", "type": "main", "index": 0}]]}, "Set urlStrings": {"main": [[{"node": "Aggregate Data", "type": "main", "index": 0}]]}, "Parse - Country Views This Week": {"main": [[{"node": "Get Country views data for prior week", "type": "main", "index": 0}]]}, "Parse - Country Views Prior Week": {"main": [[{"node": "Set urlStrings", "type": "main", "index": 0}]]}, "When clicking \u2018Test workflow\u2019": {"main": [[{"node": "Get Page Engagement Stats for this week", "type": "main", "index": 0}]]}, "Parse - Get Google Search This Week": {"main": [[{"node": "Get Google Search Results for prior week", "type": "main", "index": 0}]]}, "Get Country views data for this week": {"main": [[{"node": "Parse - Country Views This Week", "type": "main", "index": 0}]]}, "Parse - Get Google Search Prior Week": {"main": [[{"node": "Get Country views data for this week", "type": "main", "index": 0}]]}, "Get Country views data for prior week": {"main": [[{"node": "Parse - Country Views Prior Week", "type": "main", "index": 0}]]}, "Parse - Get Page Engagement This Week": {"main": [[{"node": "Get Page Engagement Stats for prior week", "type": "main", "index": 0}]]}, "Parse - Get Page Engagement Prior Week": {"main": [[{"node": "Get Google Search Results for this week", "type": "main", "index": 0}]]}, "Get Google Search Results for this week": {"main": [[{"node": "Parse - Get Google Search This Week", "type": "main", "index": 0}]]}, "Get Page Engagement Stats for this week": {"main": [[{"node": "Parse - Get Page Engagement This Week", "type": "main", "index": 0}]]}, "Get Google Search Results for prior week": {"main": [[{"node": "Parse - Get Google Search Prior Week", "type": "main", "index": 0}]]}, "Get Page Engagement Stats for prior week": {"main": [[{"node": "Parse - Get Page Engagement Prior Week", "type": "main", "index": 0}]]}}}