I just tested the package in a dbt project and got a conflict between the project's "country_codes.csv" seed file and the one in dbt_census_utils. We should make sure all of our seed files are prefixed with "census_utils_" so that there are no conflicts.

One of Donny at Census' suggestions was to write a macro for parsing GA 4 client IDs. These client IDs are slightly different from Universal Analytics ones because they all include a prefix of GA 1.1 and also end with the Unix timestamp of the user's first interaction. Capturing the last 10 digits in the Client ID tells you the timestamp of first user interaction, which analysts may want to use.

GA 4 client ID is accessible as 'user_pseudo_id' if the user is using the BigQuery export. GA 4 does not automatically have the client ID in its UI the way UA did. There are a few popular guides on the internet for how to pass it from Google Tag Manager to GA 4... this one recommends adding a '.' at the end so that GA 4 doesn't convert it to a number in scientific notation. Whereas this guide just sends the cooke ID minus the first 6 characters. I think we can probably trust people to append a '.' at the end on their own if they used the first guide and want to make them match, but I'm noting it in case we get any feedback on that later.

Usage might look like this:

--GA 4 client ID is GA1.2.1213337569.1677539575 . I got this by going to cloudflare.com and looking at my _ga cookie in developer tools.

select
{{parse_ga4_client_id (client_id, 'unique_id') }} as unique_id, --returns 1213337569
{{parse_ga4_client_id (client_id, 'timestamp') }} as first_visit_ts, -- returns 1677539575
{{parse_ga4_client_id (client_id, 'client_id') }} as client_id --returns 1213337569.1677539575

Acceptance Criteria:
-GA4 macro can parse out the first, unique identifier part of a GA 4 cookie.
-GA 4 macro can parse out the second, timestamp part of the GA 4 cookie.
-GA 4 macro can parse out both parts of the GA 4 cookie, just slicing off the prefix.

A very useful macro is one that easily extracts the domain from an e-mail address, so that you can note which people signing up for your product are coworkers. Here's a simple way to do it:

{% macro extract_email_domain(email_address) %}

{# This is the SQL to extract email domain in the Snowflake SQL: https://docs.snowflake.com/en/sql-reference/functions/regexp_substr #}

regexp_substr(lower({{ emai_addressl }}), '@(.*)', 1, 1, 'e',1)

{# This is the SQL to extract email domain in BigQuery SQL, which has 2 less parameters: https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_substr #}

regexp_substr(lower({{ emai_addressl }}), '@(.*)', 1, 1)

{# Need SQL to extract email domain in Redshift SQL #}

{% endmacro %}

Note: this doesn't currently check for improperly formatted emails, like periods at the end of the domain or whitespaces.

Acceptance Criteria:
-Extract e-mail domain from e-mail addresses.
-Handle improperly formatted e-mails.
-Macro should work in Snowflake, BigQuery, and Redshift.

Describe the issue

https://github.com/sutrolabs/dbt_census_utils/blob/main/macros/get_country_code.sql

Having a 200-line case statement feels pretty icky! Instead, could you do a subquery along these lines?

select 
user_id, 
{{ dbt_census_utils.get_country_code('country_name') }}
from {{ ref('whatever') }}

select 
user_id,
(select country_code from {{ ref('country_codes') }} where country_name = lower(country_name))
from {{ ref('wherever') }}

Relevant error or model output

No response

Expected behavior

Subquery above

dbt Project configurations

N/A

Package versions

Have just eyeballed this, haven't used it in prod

What database are you using dbt with?

snowflake

dbt Version

1.5.x

Are you interested in contributing this feature?

• Yes.
• No.

dbt recommends hosting dbt docs for packages on Github pages. Here's an example of Fivetran doing that.

We should do this later in the project when there's more documentation to actually host.

Acceptance Criteria:
-Generate dbt docs.
-Host them on Github pages.

Follow steps here: https://docs.getdbt.com/guides/legacy/building-packages#4-add-integration-tests

Note: actual integration tests should be added as the macros are added, they're not all included in this issue.

Acceptance Criteria:
-Set up the basic integration tests folder.

Some Census destinations are very picky about names. For example, Facebook requires that location names be:
lowercased, have spaces and special characters removed.

We should create a macro that removes and replaces characters for different destinations. If different destinations need different types of formatting, we can use variables to clean them in different ways. ie:

{{ census_utils.clean_name(city_name, 'facebook') }}

Acceptance Criteria:
-Names are stripped of spaces and other characters that Facebook won't accept.
-Macro is written in a way that it can support other standards, such as Google.
-Macro works on Snowflake, Redshift, and BigQuery (testing environments for SF/RS to be provided later).

When sending geographic data to a destination, it's important that all of the country info be consistent and what the destination expects. If the destination requires country code, or if your DW has a mix of country names and country codes, it's important to convert all of the country names into codes. We can accomplish this with a seed file mapping names to codes (the same seed can also help with the reverse transformation).

Acceptance Criteria:
-The macro checks for country names, and if it finds a match, converts them to codes. Everything else is left as is (this way, if it's already a code it will stay that way).
-The macro lowercases country names so that capitalization doesn't affect whether it finds a match. The country code that is output should be uppercase though.
-If possible, the macro should check for any countries that have been renamed in the past 50 years, ie Burma/Myanmar. This would be handled by having multiple rows for them in the seed file.

When modeling customer behavior, it's important to distinguish real customers from employees testing the system. I've done this 3 different ways in the past:

1. Checking if their email domain matches the company's e-mail domain.
2. Checking if their email is in a list of internal e-mails in a seed file or gsheet that's being ingested into the data warehouse. This is useful if internal users are signing up with their g-mail accounts in order to have a bunch of different accounts.
3. Checking if their IP address matches a list of internal IPs in a seed file or gsheet. This is useful for web data that doesn't have an e-mail associated with it.

We can implement a macro for this using 2 project variables:
1 variable that sets what the company email domain is (we'll default this to getcensus.com and then customers can overwrite it).
A variable that sets the location of the table/view and column name for internal e-mails
A variable that sets the location of the table/view and column name for internal IP addresses.

Acceptance Criteria:
-Identify email addresses that use the company e-mail domain.
-Identify email addresses that are in a table/view of internal e-mails.
-Identify IP addresses that are in a table/view of internal IP addresses.
-Macro should work in Snowflake, BigQuery, and Redshift.

Create a readme, inspired by Fivetran package's excellent installation instructions and dbt_utils' clear explanation of macros.

Acceptance Criteria:
-Readme explains how to install the package.
-Readme explains how to use each macro.

There should be a macro to check if an e-mail address is from a personal account. We could set the list of personal e-mails at the top of the macro, but it would probably be better to make a seed file of personal e-mails. I've attached a list we could use

Macro would look something like this:
{% macro is_personal_email(email_address) %}

{% endmacro %}
free_email_provider_domains.txt

Acceptance Criteria:
-Macro returns true if an e-mail address is personal based on the email domain (we can use the extract email domain macro for this).
-Macro returns false if an e-mail address is not personal.
-Macro works on Snowflake, Redshift, and BigQuery.

We should have issue and PR templates for the project!