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

Google Analytics 4 (GA4) gives priceless insights into person conduct throughout web sites and apps. However what if it is advisable to 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 purposes and Amazon Redshift, empowering organizations to unlock deeper insights and drive data-informed choices. On this publish, we present you tips on how to set up the info ingestion pipeline between Google Analytics 4, Google Sheets, and an Amazon Redshift Serverless workgroup.

Amazon AppFlow is a totally managed integration service that you should utilize to securely switch information from software program as a service (SaaS) purposes, equivalent to Google BigQuery, Salesforce, SAP, HubSpot, and ServiceNow, to Amazon Net Companies (AWS) companies equivalent to Amazon Easy Storage Service (Amazon S3) and Amazon Redshift, in only a few clicks. With Amazon AppFlow, you’ll be able to 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 possibly can configure information transformation capabilities equivalent to filtering and validation to generate wealthy, ready-to-use information as a part of the circulate itself, with out extra steps. Amazon AppFlow routinely encrypts information in movement, and means that you can limit information from flowing over the general public web for SaaS purposes which can be built-in with AWS PrivateLink, lowering publicity to safety threats.

Amazon Redshift is a quick, scalable, and totally managed cloud information warehouse that means that you can course of and run your complicated 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 knowledge. Tens of hundreds of shoppers use Amazon Redshift to course of massive quantities of knowledge, modernize their information analytics workloads, and supply insights for his or her enterprise customers.

Conditions

Earlier than beginning this walkthrough, it is advisable to have the next stipulations 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 Knowledge API
  • Google Sheets API
  • Google Drive API

For extra info, discuss 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 exhibits how Amazon AppFlow can remodel and transfer information from SaaS purposes 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 totally different purposes appearing as a supply, together with Google Analytics, Google Sheets, and Google BigQuery.
• Transfer – The center part is labeled Amazon AppFlow. The part accommodates bins that characterize Amazon AppFlow operations equivalent to Masks Fields, Map Fields, Merge Fields, Filter Knowledge, and others. On this publish, we deal with organising the info motion utilizing Amazon AppFlow and filtering information primarily based on begin date. The opposite transformation operations equivalent to mapping, masking, and merging fields should not lined on this publish.
• Vacation spot – The part on the best of the diagram is labeled Vacation spot and represents targets equivalent to Amazon Redshift and Amazon S3. On this psot, we primarily deal with Amazon Redshift because the vacation spot.

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

Software configuration in Google Cloud Platform

Amazon AppFlow requires OAuth 2.0 for authentication. It’s essential to 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, comply with these steps:

1. On the Google Cloud Platform Console, from the tasks record, choose a undertaking or create a brand new one.
2. If the APIs & Companies web page isn’t already open, select the menu icon on the higher left and choose APIs & Companies.
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 sort Net utility, enter the title demo-google-aws, and supply URIs for Licensed JavaScript origins https://console.aws.amazon.com. For Licensed 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. Below Further info, as proven within the following screenshot, observe down the Consumer ID and Consumer secret.

Knowledge 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 labeled 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 circulate in Amazon AppFlow

Create a connection to Google Analytics 4 in Amazon AppFlow

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

1. Check in 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 Connect 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. (Non-obligatory) beneath Knowledge 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 feature if you wish to encrypt your information with your individual AWS KMS key as an alternative.

The next screenshot exhibits the Connect with Google Analytics 4 window.

Amazon AppFlow encrypts your information throughout transit and at relaxation. For extra info, see Knowledge 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 Identify (ARN) for that key:

1. For Connection title, enter a reputation in 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. If you create a circulate that makes use of Google Analytics 4 as the info supply, you’ll be able to choose this connection.

Create an IAM function for Amazon AppFlow integration with Amazon Redshift

You should utilize 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 Knowledge API.

1. Check in 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 realize 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 title 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 title 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, comply with 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 title appflow-redshift-connection. You possibly can 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 title and database title.
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 in the course of the creation of the Redshift cluster. Moreover, for the Amazon Redshift Knowledge 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, comply with these steps:

1. On the Amazon Redshift console, select Question editor v2 in Explorer.
2. Connect with your present Redshift cluster or Amazon Redshift Serverless workgroup.
3. Create a desk with the next Knowledge 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 exhibits the profitable creation of this desk in Amazon Redshift:

The next step is just relevant to Amazon Redshift Serverless. In case you are utilizing a Redshift provisioned cluster, you’ll be able to 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 circulate in Amazon AppFlow

To create an information circulate in Amazon AppFlow, comply with these steps:

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

2. In Supply title, 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 Stream set off, select Run on demand and select Subsequent, as proven within the following screenshot.

You possibly can run the circulate 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 area title dropdown menu, choose the attribute date, and from the Vacation spot area title, 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 exhibits 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 gives numerous dimensions and metrics for reporting functions. Consult with API Dimensions & Metrics for particulars.

7. In Discipline title, 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 now have pattern information created for this vary.

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

10. On the Amazon Redshift console, select Question editor v2 in Explorer.
11. Connect 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 exhibits the outcomes loaded into the stg_ga4_daily_summary desk.

Knowledge 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’ll be able to add the Nation Market phase file to your Google sheet earlier than continuing to the following steps. These steps present tips on how to configure Amazon AppFlow to arrange a connection between Google Sheets and Amazon Redshift for information migration. This process might be labeled into the next steps:

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

Create Google Sheets connection in Amazon AppFlow

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

1. On the Amazon AppFlow console, select Connectors, choose Google Sheets, then select Create connection.
2. Within the Connect 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 title, enter a reputation in your connection.
4. (Non-obligatory) Below Knowledge 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 feature 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, comply with these steps:

1. On the Amazon Redshift console, select Question editor v2 in Explorer
2. Connect 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),
Major Key(N_NATIONKEY)
) distkey(n_nationkey) sortkey(n_nationkey);

he following steps are solely relevant to Amazon Redshift Serverless. In case you are utilizing a Redshift provisioned cluster, you’ll be able to 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 circulate in Amazon AppFlow

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

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

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

6. Choose Manually map fields. From the Supply area title dropdown menu, choose Map all fields immediately. 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 exhibits the mapping.

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

The screenshot under exhibits the execution particulars of the circulate job.

10. On the Amazon Redshift console, select Question editor v2 in Explorer.
11. Connect 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 exhibits the outcomes loaded into the stg_nation_market_segment desk.

13. Run the next SQL to organize 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,
Major 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,
Major 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,
Major 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 exhibits the outcomes from the aggregated question in Amazon Redshift from information loaded utilizing Amazon Appflow.

Clear up

To keep away from incurring costs, 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 record of flows, choose the circulate title created and delete it.
3. Enter “delete” to delete the circulate.
4. Delete the Amazon Redshift workgroup.
5. Clear up sources in your Google account by deleting the undertaking that accommodates 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 knowledge integration are minimized so you’ll be able to deal with deriving actionable insights out of your information. Whether or not you’re archiving historic information, performing complicated analytics, or making ready information for machine studying, this connector streamlines the method, making it accessible to a broader vary of knowledge professionals.

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

In regards to the authors

Ritesh Kumar Sinha is an Analytics Specialist Options Architect primarily based out of San Francisco. He has helped prospects 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 prospects design end-to-end analytics options on AWS. Exterior 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 keen about enabling prospects to understand the facility of their information. He makes a speciality of migrating enterprise information warehouses to AWS Trendy Knowledge Structure.

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