Ingest information from Google Analytics 4 and Google Sheets to Amazon Redshift utilizing Amazon AppFlow

Google Analytics 4 (GA4) offers invaluable insights into person conduct throughout web sites and apps. However what if it’s essential mix GA4 information with different sources or carry out deeper evaluation? That’s the place Amazon Redshift and Amazon AppFlow are available. Amazon AppFlow bridges the hole between Google functions and Amazon Redshift, empowering organizations to unlock deeper insights and drive data-informed selections. On this publish, we present you set up the info ingestion pipeline between Google Analytics 4, Google Sheets, and an Amazon Redshift Serverless workgroup.

Amazon AppFlow is a completely managed integration service that you should use to securely switch information from software program as a service (SaaS) functions, equivalent to Google BigQuery, Salesforce, SAP, HubSpot, and ServiceNow, to Amazon Internet Providers (AWS) providers equivalent to Amazon Easy Storage Service (Amazon S3) and Amazon Redshift, in only a few clicks. With Amazon AppFlow, you may run information flows at practically any scale and on the frequency you select—on a schedule, in response to a enterprise occasion, or on demand. You may configure information transformation capabilities equivalent to filtering and validation to generate wealthy, ready-to-use information as a part of the circulation itself, with out extra steps. Amazon AppFlow robotically encrypts information in movement, and lets you prohibit information from flowing over the general public web for SaaS functions which might be built-in with AWS PrivateLink, lowering publicity to safety threats.

Amazon Redshift is a quick, scalable, and absolutely managed cloud information warehouse that lets you course of and run your advanced SQL analytics workloads on structured and semi-structured information. It additionally helps you securely entry your information in operational databases, information lakes, or third-party datasets with minimal motion or copying of information. Tens of 1000’s of shoppers use Amazon Redshift to course of giant quantities of information, modernize their information analytics workloads, and supply insights for his or her enterprise customers.

Conditions

Earlier than beginning this walkthrough, it’s essential have the next conditions in place:

• An AWS account.
• In your Google Cloud undertaking, you’ve enabled the next APIs:
  • Google Analytics API
  • Google Analytics Admin API
  • Google Analytics Information API
  • Google Sheets API
  • Google Drive API

For extra info, consult with Amazon AppFlow assist for Google Sheets.

For the steps to allow these APIs, see Allow and disable APIs on the API Console Assist for Google Cloud Platform.

Structure overview

The next structure reveals how Amazon AppFlow can rework and transfer information from SaaS functions to processing and storage locations. Three sections seem from left to proper within the diagram: Supply, Transfer, Goal. These sections are described within the following part.

• Supply – The leftmost part on the diagram represents completely different functions performing as a supply, together with Google Analytics, Google Sheets, and Google BigQuery.
• Transfer – The center part is labeled Amazon AppFlow. The part incorporates packing containers that symbolize Amazon AppFlow operations equivalent to Masks Fields, Map Fields, Merge Fields, Filter Information, and others. On this publish, we give attention to organising the info motion utilizing Amazon AppFlow and filtering information based mostly on begin date. The opposite transformation operations equivalent to mapping, masking, and merging fields are usually not coated on this publish.
• Vacation spot – The part on the correct of the diagram is labeled Vacation spot and represents targets equivalent to Amazon Redshift and Amazon S3. On this psot, we primarily give attention to Amazon Redshift because the vacation spot.

This publish has two components. The first half covers integrating from Google Analytics. The second half focuses on connecting with Google Sheets.

Utility configuration in Google Cloud Platform

Amazon AppFlow requires OAuth 2.0 for authentication. You could create an OAuth 2.0 shopper ID, which Amazon AppFlow makes use of when requesting an OAuth 2.0 entry token. To create an OAuth 2.0 shopper ID within the Google Cloud Platform console, observe these steps:

1. On the Google Cloud Platform Console, from the tasks checklist, choose a undertaking or create a brand new one.
2. If the APIs & Providers web page isn’t already open, select the menu icon on the higher left and choose APIs & Providers.
3. Within the navigation pane, select Credentials.
4. Select CREATE CREDENTIALS, then select OAuth shopper ID, as proven within the following screenshot.

5. Choose the appliance kind Internet utility, enter the identify demo-google-aws, and supply URIs for Approved JavaScript origins https://console.aws.amazon.com. For Approved redirect URIs, add https://us-east-1.console.aws.amazon.com/appflow/oauth. Select SAVE, as proven within the following screenshot.

6. The OAuth shopper ID is now created. Choose demo-google-aws.

7. Beneath Extra info, as proven within the following screenshot, word down the Consumer ID and Consumer secret.

Information ingestion from Google Analytics 4 to Amazon Redshift

On this part, you configure Amazon AppFlow to arrange a connection between Google Analytics 4 and Amazon Redshift for information migration. This process might be categorised into the next steps:

1. Create a connection to Google Analytics 4 in Amazon AppFlow
2. Create an IAM function for Amazon AppFlow integration with Amazon Redshift
3. Arrange Amazon AppFlow connection for Amazon Redshift
4. Arrange desk and permission in Amazon Redshift
5. Create information circulation in Amazon AppFlow

Create a connection to Google Analytics 4 in Amazon AppFlow

To create a connection to Google Analytics 4 in Amazon AppFlow, observe these steps:

1. Register to the AWS Administration Console and open Amazon AppFlow.
2. Within the navigation pane on the left, select Connections.
3. On the Handle connections web page, for Connectors, select Google Analytics 4.
4. Select Create connection.
5. Within the Hook up with Google Analytics 4 window, enter the next info. For Consumer ID, enter the shopper ID of the OAuth 2.0 shopper ID in your Google Cloud undertaking created within the earlier part. For Consumer secret, enter the shopper secret of the OAuth 2.0 shopper ID in your Google Cloud undertaking created within the earlier part.
6. (Elective) beneath Information encryption, select Customise encryption settings (superior) if you wish to encrypt your information with a buyer managed key in AWS Key Administration Service (AWS KMS). By default, Amazon AppFlow encrypts your information with an AWS KMS key that AWS creates, makes use of, and manages for you. Select this selection if you wish to encrypt your information with your individual AWS KMS key as an alternative.

The next screenshot reveals the Hook up with Google Analytics 4 window.

Amazon AppFlow encrypts your information throughout transit and at relaxation. For extra info, see Information safety in Amazon AppFlow.

If you wish to use an AWS KMS key from the present AWS account, choose this key beneath Select an AWS KMS key. If you wish to use an AWS KMS key from a unique AWS account, enter the Amazon Useful resource Title (ARN) for that key:

1. For Connection identify, enter a reputation to your connection
2. Select Proceed
3. Within the window that seems, check in to your Google account and grant entry to Amazon AppFlow

On the Handle connections web page, your new connection seems within the Connections desk. While you create a circulation that makes use of Google Analytics 4 as the info supply, you may choose this connection.

Create an IAM function for Amazon AppFlow integration with Amazon Redshift

You should use Amazon AppFlow to switch information from supported sources into your Amazon Redshift databases. You want an IAM function as a result of Amazon AppFlow wants authorization to entry Amazon Redshift utilizing an Amazon Redshift Information API.

1. Register to the AWS Administration Console, ideally as admin person, and within the navigation pane of the IAM dashboard, select Insurance policies.
2. Select Create coverage.
3. Choose the JSON tab and paste within the following coverage. Amazon AppFlow wants the next permissions to achieve entry and run SQL statements with the Amazon Redshift database.

{
  "Model": "2012-10-17",
  "Assertion": [
    {
      "Sid": "DataAPIPermissions",
      "Effect": "Allow",
      "Action": [
        "redshift-data:ExecuteStatement",
        "redshift-data:GetStatementResult",
        "redshift-data:DescribeStatement"
      ],
      "Useful resource": "*"
    },
    {
      "Sid": "GetCredentialsForAPIUser",
      "Impact": "Permit",
      "Motion": "redshift:GetClusterCredentials",
      "Useful resource": [
        "arn:aws:redshift:*:*:dbname:*/*",
        "arn:aws:redshift:*:*:dbuser:*/*"
      ]
    },
    {
      "Sid": "GetCredentialsForServerless",
      "Impact": "Permit",
      "Motion": "redshift-serverless:GetCredentials",
      "Useful resource": "*"
    },
    {
      "Sid": "DenyCreateAPIUser",
      "Impact": "Deny",
      "Motion": "redshift:CreateClusterUser",
      "Useful resource": [
        "arn:aws:redshift:*:*:dbuser:*/*"
      ]
    },
    {
      "Sid": "ServiceLinkedRole",
      "Impact": "Permit",
      "Motion": "iam:CreateServiceLinkedRole",
      "Useful resource": "arn:aws:iam::*:function/aws-service-role/redshift-data.amazonaws.com/AWSServiceRoleForRedshift",
      "Situation": {
        "StringLike": {
          "iam:AWSServiceName": "redshift-data.amazonaws.com"
        }
      }
    }
  ]
}

4. Select Subsequent, present the Coverage identify as appflow-redshift-policy, Description as appflow redshift coverage, and select Create coverage.

5. Within the navigation pane, select Roles and Create function. Select Customized belief coverage and paste within the following. Select Subsequent. This belief coverage grants Amazon AppFlow the power to imagine the function for Amazon AppFlow to entry and course of information.

{
  "Model": "2012-10-17",
  "Assertion": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "appflow.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

6. Seek for coverage appflow-redshift-policy, test the field subsequent to it, and select Subsequent.

7. Present the function identify appflow-redshift-access-role and Description and select Create function.

Arrange Amazon AppFlow connection for Amazon Redshift

To arrange an Amazon AppFlow connection for Amazon Redshift, observe these steps:

1. On the Amazon AppFlow console, within the navigation pane, select Connectors, choose Amazon Redshift, and select Create connection.

2. Enter the connection identify appflow-redshift-connection. You may both use Amazon Redshift provisioned or Amazon Redshift Serverless, however on this instance we’re utilizing Amazon Redshift Serverless. Choose Amazon Redshift Serverless and enter the workgroup identify and database identify.
3. Select the S3 bucket and enter the bucket prefix.

4. For Amazon S3 entry, choose the IAM function hooked up to the Redshift cluster or namespace throughout the creation of the Redshift cluster. Moreover, for the Amazon Redshift Information API, select the IAM function appflow-redshift-access-role created within the earlier part after which select

Arrange a desk and permission in Amazon Redshift

To arrange desk and permission in Amazon Redshift, observe these steps:

1. On the Amazon Redshift console, select Question editor v2 in Explorer.
2. Hook up with your present Redshift cluster or Amazon Redshift Serverless workgroup.
3. Create a desk with the next Information Definition Language (DDL).

create desk public.stg_ga4_daily_summary

(

event_date date,

area varchar(255),

nation varchar(255),

metropolis varchar(255),

deviceCategory varchar(255),

deviceModel varchar(255),

browser varchar(255),

active_users INTEGER,

new_users integer,

total_revenue  NUMERIC(18,2)

);

The next screenshot reveals the profitable creation of this desk in Amazon Redshift:

The next step is barely relevant to Amazon Redshift Serverless. If you’re utilizing a Redshift provisioned cluster, you may skip this step.

4. Grant the permissions on the desk to the IAM person utilized by Amazon AppFlow to load information into Amazon Redshift Serverless, for instance, appflow-redshift-access-role.

GRANT INSERT ON TABLE public.stg_ga4_daily_summary TO "IAMR:appflow-redshift-access-role";

Create information circulation in Amazon AppFlow

To create an information circulation in Amazon AppFlow, observe these steps:

1. On the Amazon AppFlow console, select Flows and choose Amazon Redshift. Select Create circulation and enter the circulation identify and the circulation description, as proven within the following screenshot.

2. In Supply identify, select Google Analytics 4. Select the Google Analytics 4 connection.
3. Choose the Google Analytics 4 object, then select Amazon Redshift because the vacation spot, choosing the public schema and stg_ga4_daily_summary desk in your Redshift occasion.

4. For Movement set off, select Run on demand and select Subsequent, as proven within the following screenshot.

You may run the circulation on schedule to drag both full or incremental information refresh. For extra info, see Schedule-triggered flows.

5. Choose Manually map fields. From the Supply discipline identify dropdown menu, choose the attribute date, and from the Vacation spot discipline identify, choose event_date and select Map fields, as proven within the following screenshot.

6. Repeat the earlier step (step 5) for the next attributes after which select Subsequent. The next screenshot reveals the mapping.

Dimension:browser --> browser
Dimension:area --> area
Dimension:nation --> nation
Dimension:metropolis --> metropolis
Dimension:deviceCategory --> devicecategory
Dimension:deviceModel --> devicemodel
Metric:activeUsers --> active_users
Metric:newUsers --> new_users
Metric: totalRevenue --> total_revenue
Dimension:date --> event_date

The Google Analytics API offers numerous dimensions and metrics for reporting functions. Consult with API Dimensions & Metrics for particulars.

7. In Area identify, enter the filter start_end_date and select Subsequent, as proven within the following screenshot. The Amazon AppFlow date filter helps each a begin date (criteria1) and an finish date (criteria2) to outline the specified date vary for information switch. We’re utilizing the date vary as a result of we’ve got pattern information created for this vary.

8. Evaluate the configurations and select Create circulation.
9. Select Run circulation, as proven within the following screenshot, and anticipate the circulation execution to be accomplished.

10. On the Amazon Redshift console, select Question editor v2 in Explorer.
11. Hook up with your present Redshift cluster or Amazon Redshift Serverless workgroup.
12. Enter the next SQL to confirm the info in Amazon Redshift.

choose * from public.stg_ga4_daily_summary

The screenshot under reveals the outcomes loaded into the stg_ga4_daily_summary desk.

Information ingestion from Google Sheets to Amazon Redshift

Ingesting information from Google Sheets to Amazon Redshift utilizing Amazon AppFlow streamlines analytics, enabling seamless switch and deeper insights. On this part, we display how enterprise customers can keep their enterprise glossary in Google Sheets and combine that utilizing Amazon AppFlow with Amazon Redshift and get significant insights.

For this demo, you may add the Nation Market section file to your Google sheet earlier than continuing to the following steps. These steps present configure Amazon AppFlow to arrange a connection between Google Sheets and Amazon Redshift for information migration. This process might be categorised into the next steps:

1. Create Google Sheets connection in Amazon AppFlow
2. Arrange desk and permission in Amazon Redshift
3. Create information circulation in Amazon AppFlow

Create Google Sheets connection in Amazon AppFlow

To create a Google Sheets connection in Amazon AppFlow, observe these steps:

1. On the Amazon AppFlow console, select Connectors, choose Google Sheets, then select Create connection.
2. Within the Hook up with Google Sheets window, enter the next info. For Consumer ID, enter the shopper ID of the OAuth 2.0 shopper ID in your Google Sheets undertaking. For Consumer secret, enter the shopper secret of the OAuth 2.0 shopper ID in your Google Sheets undertaking.
3. For Connection identify, enter a reputation to your connection.
4. (Elective) Beneath Information encryption, select Customise encryption settings (superior) if you wish to encrypt your information with a buyer managed key in AWS KMS. By default, Amazon AppFlow encrypts your information with an AWS KMS key that AWS creates, makes use of, and manages for you. Select this selection if you wish to encrypt your information with your individual AWS KMS key as an alternative.
5. Select Join.
6. Within the window that seems, check in to your Google account and grant entry to Amazon AppFlow.

Arrange desk and permission in Amazon Redshift

To arrange a desk and permission in Amazon Redshift, observe these steps:

1. On the Amazon Redshift console, select Question editor v2 in Explorer
2. Hook up with your present Redshift cluster or Amazon Redshift Serverless workgroup
3. Create a desk with the next DDL

create desk public.stg_nation_market_segment(
n_nationkey int4 not null,
n_name char(25) not null ,
n_regionkey int4 not null,
n_comment varchar(152) not null,
n_marketsegment varchar(255),
Main Key(N_NATIONKEY)
) distkey(n_nationkey) sortkey(n_nationkey);

he following steps are solely relevant to Amazon Redshift Serverless. If you’re utilizing a Redshift provisioned cluster, you may skip this step.

4. Grant the permissions on the desk to the IAM person utilized by Amazon AppFlow to load information into Amazon Redshift Serverless, for instance, appflow-redshift-access-role

GRANT INSERT ON TABLE public.stg_nation_market_segment TO "IAMR:appflow-redshift-access-role";

Create information circulation in Amazon AppFlow

1. On the Amazon AppFlow console, select Flows and choose Google Sheets. Select Create circulation, enter the circulation identify and circulation description, and select Subsequent.
2. Choose Google Sheets in Supply identify and select the Google Sheets connection.
3. Choose the Google Sheets object nation_market_segment#Sheet1.
4. Select the Vacation spot identify as Amazon Redshift, then choose stg_nation_market_segment as your Amazon Redshift object, as proven within the following screenshot.

5. For Movement set off, choose On demand and select Subsequent.

You may run the circulation on schedule to drag full or incremental information refresh. Learn extra at Schedule-triggered flows.

6. Choose Manually map fields. From the Supply discipline identify dropdown menu, choose Map all fields straight. When a dialog field pops up, select the respective attribute values and select Map fields, as proven within the following screenshot. Select Subsequent.

The next screenshot reveals the mapping.

7. On the Add Filters web page, select Subsequent.
8. On the Evaluate and create web page, select Create circulation.
9. Select Run circulation and anticipate the circulation execution to complete.

The screenshot under reveals the execution particulars of the circulation job.

10. On the Amazon Redshift console, select Question editor v2 in Explorer.
11. Hook up with your present Redshift cluster or Amazon Redshift Serverless workgroup.
12. Run the next SQL to confirm the info in Amazon Redshift.

choose * from public.stg_nation_market_segment

The screenshot under reveals the outcomes loaded into the stg_nation_market_segment desk.

13. Run the next SQL to arrange a pattern dataset in Amazon Redshift.

create desk public.buyer (
c_custkey int8 not null ,
c_name varchar(25) not null,
c_address varchar(40) not null,
c_nationkey int4 not null,
c_phone char(15) not null,
c_acctbal numeric(12,2) not null,
c_mktsegment char(10) not null,
c_comment varchar(117) not null,
Main Key(C_CUSTKEY)
) distkey(c_custkey) sortkey(c_custkey);

create desk public.lineitem (
l_orderkey int8 not null ,
l_partkey int8 not null,
l_suppkey int4 not null,
l_linenumber int4 not null,
l_quantity numeric(12,2) not null,
l_extendedprice numeric(12,2) not null,
l_discount numeric(12,2) not null,
l_tax numeric(12,2) not null,
l_returnflag char(1) not null,
l_linestatus char(1) not null,
l_shipdate date not null ,
l_commitdate date not null,
l_receiptdate date not null,
l_shipinstruct char(25) not null,
l_shipmode char(10) not null,
l_comment varchar(44) not null,
Main Key(L_ORDERKEY, L_LINENUMBER)
) distkey(l_orderkey) sortkey(l_shipdate,l_orderkey)  ;

create desk public.orders (
o_orderkey int8 not null,
o_custkey int8 not null,
o_orderstatus char(1) not null,
o_totalprice numeric(12,2) not null,
o_orderdate date not null,
o_orderpriority char(15) not null,
o_clerk char(15) not null,
o_shippriority int4 not null,
o_comment varchar(79) not null,
Main Key(O_ORDERKEY)
) distkey(o_orderkey) sortkey(o_orderdate, o_orderkey) ;
copy lineitem from 's3://redshift-downloads/TPC-H/2.18/10GB/lineitem.tbl' iam_role default delimiter '|' area 'us-east-1';
copy orders from 's3://redshift-downloads/TPC-H/2.18/10GB/orders.tbl' iam_role default delimiter '|' area 'us-east-1';
copy buyer from 's3://redshift-downloads/TPC-H/2.18/10GB/buyer.tbl' iam_role default delimiter '|' area 'us-east-1';

14. Run the next SQL to do the info analytics utilizing Google Sheets enterprise information classification within the Amazon Redshift dataset.

choose
n_marketsegment,
sum(l_extendedprice * (1 - l_discount)) as income
from
public.buyer,
public.orders,
public.lineitem,
public.stg_nation_market_segment
the place
c_custkey = o_custkey
and l_orderkey = o_orderkey
and c_nationkey = n_nationkey
group by
1
order by
income desc;

The screenshot under reveals the outcomes from the aggregated question in Amazon Redshift from information loaded utilizing Amazon Appflow.

Clear up

To keep away from incurring fees, clear up the sources in your AWS account by finishing the next steps:

1. On the Amazon AppFlow console, within the navigation pane, select Flows.
2. From the checklist of flows, choose the circulation identify created and delete it.
3. Enter “delete” to delete the circulation.
4. Delete the Amazon Redshift workgroup.
5. Clear up sources in your Google account by deleting the undertaking that incorporates the Google BigQuery sources. Observe the documentation to clear up the Google sources.

Conclusion

On this publish, we walked you thru the method of utilizing Amazon AppFlow to combine information from Google Adverts and Google Sheets. We demonstrated how the complexities of information integration are minimized so you may give attention to deriving actionable insights out of your information. Whether or not you’re archiving historic information, performing advanced analytics, or making ready information for machine studying, this connector streamlines the method, making it accessible to a broader vary of information professionals.

For extra info, consult with Amazon AppFlow assist for Google Sheets and Google Adverts.

In regards to the authors

Ritesh Kumar Sinha is an Analytics Specialist Options Architect based mostly out of San Francisco. He has helped clients construct scalable information warehousing and massive information options for over 16 years. He likes to design and construct environment friendly end-to-end options on AWS. In his spare time, he loves studying, strolling, and doing yoga.

Tahir Aziz is an Analytics Resolution Architect at AWS. He has labored with constructing information warehouses and massive information options for over 13 years. He loves to assist clients design end-to-end analytics options on AWS. Outdoors of labor, he enjoys touring and cooking.

Raza Hafeez is a Senior Product Supervisor at Amazon Redshift. He has over 13 years {of professional} expertise constructing and optimizing enterprise information warehouses and is captivated with enabling clients to understand the facility of their information. He makes a speciality of migrating enterprise information warehouses to AWS Trendy Information Structure.

Amit Ghodke is an Analytics Specialist Options Architect based mostly out of Austin. He has labored with databases, information warehouses and analytical functions for the previous 16 years. He loves to assist clients implement analytical options at scale to derive most enterprise worth.