# Fractribution Quickstart
> Quick start guide for the legacy Snowplow Fractribution dbt package for marketing attribution modeling.
> Source: https://docs.snowplow.io/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-quickstart/legacy/fractribution/
> Documentation index: https://docs.snowplow.io/llms.txt

> **Note:** The Fractribution Package is no longer maintained, please refer to the [Attribution package](/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-models/dbt-attribution-data-model/) for marketing attribution analysis with Snowplow

## Requirements

In addition to [dbt](https://github.com/dbt-labs/dbt) being installed and a web events dataset being available in your database:

- have `snowplow_web_page_views` derived table available as a source (generated by the [snowplow\_web package](/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-models/legacy/dbt-web-data-model/))

- have a table with revenue data by users (`domain_userid`, `user_id`) that serves as another source for the fractribution calculations, you can choose either of the following options:

  - your `atomic.events` table with any [self-describing event](/docs/fundamentals/events/#self-describing-events) that captures revenue data
  - the `snowplow_ecommerce_transaction_interactions` derived table generated by the [snowplow\_ecommerce](/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-models/dbt-ecommerce-data-model/) package
  - any custom incremental table that is built on top of the `snowplow_web` model that results in an aggregated revenue dataset

- `python` or `docker` installed (or you can use Snowpark on Snowflake)

## Installation

Make sure to create a new dbt project and import this package via the `packages.yml` as [recommended by dbt](https://docs.getdbt.com/docs/build/packages#how-do-i-add-a-package-to-my-project), or add to an existing top level project. Do not fork the packages themselves.

Check [dbt Hub](https://hub.getdbt.com/snowplow/) for the latest installation instructions, or read the [dbt docs](https://docs.getdbt.com/docs/building-a-dbt-project/package-management) for more information on installing packages. If you are using multiple packages you may need to up/downgrade a specific package to ensure compatibility.

```yaml

packages:
- package: snowplow/snowplow_fractribution
  version: 0.3.6
```

> **Note:** Make sure to run the `dbt deps` command after updating your `packages.yml` to ensure you have the specified version of each package installed in your project.

## Setup

### 1. Override the dispatch order in your project

To take advantage of the optimized upsert that the Snowplow packages offer you need to ensure that certain macros are called from `snowplow_utils` first before `dbt-core`. This can be achieved by adding the following to the top level of your `dbt_project.yml` file:

```yml
dispatch:
  - macro_namespace: dbt
    search_order: ['snowplow_utils', 'dbt']
```

If you do not do this the package will still work, but the incremental upserts will become more costly over time.

### 2. Set variables

The package has some variables that need to be set before it can be run, you should edit these in your `dbt_project.yml` file. Further customization can be done via the variables listed in the [configuration page](/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-configuration/legacy/fractribution/).

- `snowplow__conversion_window_start_date`: The start date in UTC for the window of conversions to include
- `snowplow__conversion_window_end_date`: The end date in UTC for the window of conversions to include
- `snowplow__conversion_hosts`: `url_hosts` to process
- `snowplow__path_transforms`: A dictionary of path transforms and their arguments (see [Path Transform Options](/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-models/legacy/dbt-fractribution-data-model/#path-transform-options) section)

```yml
vars:
  snowplow_fractribution:
    snowplow__conversion_window_start_date: '2022-01-01'
    snowplow__conversion_window_end_date: '2023-02-01'
    snowplow__conversion_hosts: ['mysite.com']
    snowplow__path_transforms: \{'exposure_path' : null}
```

> **Tip: Snowflake Only**
>
> If you are using Snowflake, you can automatically run the python scripts using Snowpark when running the dbt package. This is done using macros that create and run a stored procedure on Snowpark after the dbt models have completed.
>
> To enable this you need to set some additional variables. For example, to enable this and use the `last_touch` attribution model:
>
> ```yml
> vars:
>   snowplow_fractribution:
>     snowplow__run_python_script_in_snowpark: true
>     snowplow__attribution_model_for_snowpark: 'last_touch'
> ```

**Modifying the conversions source**

By default the `snowplow__conversions_source` is your atomic events table. In most cases this is likely to be what you want to use, however you may wish to use the in-built conversions modeling as part of our [web package](/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-models/legacy/dbt-web-data-model/conversions/) if you have already defined this, by setting `snowplow__conversions_source` to `"\\{\\{ ref('snowplow_web_sessions') }}\\}"`.

Alternatively, if you are using Redshift/Postgres you may wish to include additional fields from a Self-Describing Event, or an Entity. To do this, you should create a new model in your project, e.g. `models/snowplow/snowplow_joined_events_table.sql` which should have something like the following content:

> _For more information about dealing with duplicates and the macro in this code, make sure to see our [deduplication docs](/docs/modeling-your-data/modeling-your-data-with-dbt/package-mechanics/deduplication/)._

```jinja2
with {{ snowplow_utils.get_sde_or_context('atomic',
                                          'my_custom_context',
                                          "'\{\{ get_lookback_date_limits(\"min\") }}\}'",
                                          "'\{\{ get_lookback_date_limits(\"max\") }}\}'",
                                          'my_prefix')}}

select
  events.*,
  b.*
from \\{\\{ source('atomic', 'events') }}\\} as events
  left join nl_basjes_my_prefix_1 b on
    events.event_id = b.my_prefix__id
    and events.collector_tstamp = b.my_prefix__tstamp

where
  -- use the appropriate partition key to filter on in addition to this, add a bit of a buffer if it is not derived_tstamp
  date(derived_tstamp) >= '\{\{ get_lookback_date_limits(\"min\") }}\}'
  and date(derived_tstamp) <= '\{\{ get_lookback_date_limits(\"max\") }}\}'
```

Finally ensure you set the `snowplow__conversions_source` to `"\\{\\{ ref('snowplow_joined_events_table') }}\\}"`

### 3. Configure macros

> **Tip:** While the macro and matching columns/tables use the term _channel_, it is entirely possible to define this in terms of something else e.g. a campaign.

All the below macros are created with the intention to let users modify them to fit their personal use case. If you wish to change this, copy the macro from the macros folder in the `snowplow_fractribution` package (at `[dbt_project_name]/dbt_packages/snowplow_fractribution/macros/conversion_clause.sql`) and add it to the macros folder of your own dbt project where you are free to make any alterations. You will find a detailed guide / illustration with sample code within the individual macros themselves.

> **Tip:** To overwrite these macros correctly with those in your project, ensure you prefix the macro name by `default__` in the definition e.g.
>
> ```jinja2
> {% macro default__conversion_value() %}
>     tr_total/100
> {% endmacro %}
> ```

#### `conversion_clause` macro

> The [`conversion_clause`](https://github.com/snowplow/dbt-snowplow-fractribution/blob/main/macros/conversion_clause.sql) macro specifies how to filter Snowplow events to only succesfful conversion events. How this is filtered will depend on your definition of a conversion. The default is filtering to events where `tr_total > 0`, but this could instead filter on `event_name = 'checkout'`, for example. If you are using the ecommerce model, you will still need to set this for the fractribution code to run (even though _all_ events are conversions in the ecommerce model), in this case change it to `transaction_revenue > 0`.

#### `conversion_value` macro

> The [`conversion_value`](https://github.com/snowplow/dbt-snowplow-fractribution/blob/main/macros/conversion_value.sql) macro specifies either a single column or a calculated value that represents the value associated with that conversion. The default is `tr_total`, but revenue or a calculation using revenue and discount\_amount from the default ecommerce schema, for example, could similarly be used.

#### `channel_classification` macro

> The [`channel_classification`](https://github.com/snowplow/dbt-snowplow-fractribution/blob/main/macros/channel_classification.sql) macro is used to perform channel classifications. This can be altered to generate your expected channels if they differ from the channels generated in the default macro. It is highly recommended that you examine and configure this macro when using your own data, as the ROAS calculations and attribution calculations will run against these channel definitions, and the default values will not consider any custom marketing parameters.

#### `channel_spend` macro

> The [`channel_spend`](https://github.com/snowplow/dbt-snowplow-fractribution/blob/main/macros/channel_spend.sql) macro is used to query the spend by channels. It requires a user supplied SQL script to extract the total ad spend by channel.
>
> Required output format:
>
> - `channel`: STRING NOT NULL
> - `spend`: FLOAT64 _(or other warehouse equivalent)_ (Use the same monetary units as conversion revenue, and NULL if unknown.)

### 4. Run the model

Execute the following either through your CLI, within dbt Cloud, or within [Snowplow Console](/docs/modeling-your-data/running-data-models-via-console/)

```yml
dbt run --select snowplow_fractribution
```

### 5. Run the python script to generate the final models

Depending on your setup, please follow the appropriate steps below. All these methods will create the following tables in your warehouse:

- `snowplow_fractribution_path_summary_with_channels`
- `snowplow_fractribution_report_table`
- `snowplow_fractribution_channel_attribution`

**Run on Snowflake using Snowpark**

If you enabled this already, the tables will have already been built as part of step 4. If you wish to just re-run the attribution modeling for some reason you can run the following:

```text
dbt run --select snowplow_fractribution_call_snowpark_macros
```

**Locally run python**

> **Tip:** To run the fractribution script locally in Python, we recommend using a virtual environment such as one in `conda` or `pyenv`.
>
> Example using conda:
>
> ```text
> conda create --name fractribution_env -c https://repo.anaconda.com/pkgs/snowflake python=3.8 absl-py
> conda activate fractribution_env
> ```

#### I. Install packages

You can install the packages using `pip install -r dbt_packages/snowplow_fractribution/utils/requirements.txt` (or the appropriate path from your terminal working directory).

Please note that some of the libraries are adapter specific. These are listed in the `requirements` file, and you can also find the necessary list for each adapter below:

**BigQuery:**

- `absl-py`==`1.2.0`
- `google-cloud-bigquery`==`3.5.0`

**Databricks:**

- `absl-py`==`1.2.0`,
- `databricks-sql-connector`==`2.1.0`
- `pandas`

**Snowflake:**

- `absl-py`==`1.2.0`,
- `snowflake-snowpark-python`==`0.11.0`

**Redshift:**

- `absl-py`==`1.2.0`,
- `redshift_connector`==`2.0.910`

***

**M1 Instructions (for Snowflake only)**

> **Note:** There is an issue with running Snowpark on M1 chips. A workaround recommended by Snowflake is to set up a virtual environment that uses x86 Python:
>
> ```text
> CONDA_SUBDIR=osx-64 conda create -n fractribution_env python=3.8 absl-py -c https://repo.anaconda.com/pkgs/snowflake
> conda activate fractribution_env
> conda config --env --set subdir osx-64
> ```

#### II. Set the connection parameters in your terminal

**BigQuery:**

```text
export project_id=project_id\
export bigquery_dataset=bigquery_dataset\
export google_application_credentials=google_application_credentials
```

**Databricks:**

```text
export databricks_schema=derived_schema_name\
export databricks_server_hostname=hostname\
export databricks_http_path=http_path\
export databricks_token=token
```

**Snowflake:**

```text
export snowflake_account=my_account\
export snowflake_user=sf_user\
export snowflake_password=password\
export snowflake_user_role=special_role\
export snowflake_warehouse=warehouse_name\
export snowflake_database=database_name\
export snowflake_schema=derived_schema_name
```

**Redshift:**

```text
export redshift_host=redshift_host\
export redshift_database=redshift_database\
export redshift_port=redshift_port\
export redshift_user=redshift_user\
export redshift_password=redshift_password\
export redshift_schema=redshift_schema
```

***

#### III. Run the fractribution script

Run the adapter specific main fractribution script by specifying the conversion window start and end dates, and the attribution model (if you are not using the default `shapley`, see [here](/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-models/legacy/dbt-fractribution-data-model/#attribution-models) for more options). Example:

**BigQuery:**

```text
python main_snowplow_bigquery.py --conversion_window_start_date '2022-06-03' --conversion_window_end_date '2022-08-01' --attribution_model last_touch
```

**Databricks:**

```text
python main_snowplow_databricks.py --conversion_window_start_date '2022-06-03' --conversion_window_end_date '2022-08-01' --attribution_model last_touch
```

**Snowflake:**

```text
python main_snowplow_snowflake.py --conversion_window_start_date '2022-06-03' --conversion_window_end_date '2022-08-01' --attribution_model last_touch
```

**Redshift:**

```text
python main_snowplow_redshift.py --conversion_window_start_date '2022-06-03' --conversion_window_end_date '2022-08-01' --attribution_model last_touch
```

***

> **Tip:** To help debug easier we have included logs to be printed each time a table is created in the warehouse, there should be 3 in total (`snowplow_fractribution_path_summary_with_channels`, `snowplow_fractribution_channel_attribution` and `snowplow_fractribution_report_table`). There is also a `--verbose` flag you can include in your shell prompt when executing the python scripts which may give you an even more detailed breakdown depending on your warehouse.

**Run python with docker**

#### I. Pull the docker image ​[Docker Hub: snowplow/fractribution](https://hub.docker.com/r/snowplow/fractribution)

You can pull the latest docker image from Docker Hub: `docker pull snowplow/fractribution:latest`. Alternatively, you can pull it based on the package version: `docker pull snowplow/fractribution:0.2.0`

#### II. Set the environment variables

Add the necessary environment variables to an environment file, e.g. `configs.env`. The necessary variables will differ depending on the data warehouse you are using. The easiest way to determine the variables you need to set is to check the Dockerfile in the fractribution dbt package: `dbt-snowplow-fractribution/utils/Dockerfile`. Please note that in case of BigQuery, the `google_application_credentials` env var is not needed for Docker as you mount this as a volume at run time.

Below is an example of the `config.env` file (set up for Snowflake). Note the last 4 variables are named the same across all warehouses. You do not need to specify the attribution model if using the default, `shapley`:

```text
snowflake_account=youraccount.ap-southeast-2
snowflake_user=user
snowflake_password=abc123
snowflake_user_role=DBT
snowflake_warehouse=WH
snowflake_database=snowplow
snowflake_schema=FRACTRIBUTION_DERIVED

conversion_window_start_date=2022-06-03
conversion_window_end_date=2022-08-01
attribution_model=last_touch
warehouse=snowflake
```

#### III. Run the docker container

Run the docker container :

**BigQuery:**

With BigQuery you need to mount your service account keyfile when running the docker image

```text
docker run --rm --env-file /path/to/env/file/configs.env -v /path/to/yourkeyfile.json:/keyfile.json -it snowplow/fractribution:latest
```

**Databricks:**

```text
docker run --rm --env-file /path/to/env/file/configs.env -it snowplow/fractribution:latest
```

**Snowflake:**

```text
docker run --rm --env-file /path/to/env/file/configs.env -it snowplow/fractribution:latest
```

**Redshift:**

```text
docker run --rm --env-file /path/to/env/file/configs.env -it snowplow/fractribution:latest
```

***

> **Tip:** To help debug easier we have included logs to be printed each time a table is created in the warehouse, there should be 3 in total (`snowplow_fractribution_path_summary_with_channels`, `snowplow_fractribution_channel_attribution` and `snowplow_fractribution_report_table`). In order to run the script with a `--verbose` flag you can set the `verbose_run` variable to "true" in your env-file, which may give you an even more detailed breakdown depending on your warehouse.