---
title: "View: Intermediary DRP Table(s): drp.daily_last_click_cpo_cpa"
description: drp.daily_last_click_cpo_cpa 4111_CST_BAS_daily_last_click_cost_per_order.sql Creates a daily level last click cost per order and cost per acquisition calculation across marketing channels and vendors
---

[Back to home](https://support.daasity.com/migrated/knowledge)

- [ Welcome to Daasity
  
  
  
  ](https://support.daasity.com/migrated/knowledge/welcome-to-daasity) 
    - [ Integration Setup | Connect Your Data Sources ](https://support.daasity.com/migrated/knowledge/welcome-to-daasity#integration-setup-connect-your-data-sources)
    - [ Integration Setup | Amazon Seller Central ](https://support.daasity.com/migrated/knowledge/welcome-to-daasity#integration-setup-amazon-seller-central)
    - [ Navigating the Daasity App ](https://support.daasity.com/migrated/knowledge/welcome-to-daasity#navigating-the-daasity-app)
- [ Daasity Platform | Business User Analytics
  
  
  
  ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics) 
    - [ Brand Supplied Data (BSD) ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#brand-supplied-data-bsd)
    - [ Dashboards ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#dashboards)
    - [ Reports ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#reports)
    - [ Explores ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#explores)
    - [ Views ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#views)
    - [ Audiences ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#audiences)
    - [ Customizations ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#customizations)
- [ ELT+ | Technical User Documentation
  
  
  
  ](https://support.daasity.com/migrated/knowledge/elt-technical-user-documentation) 
    - [ Platform ](https://support.daasity.com/migrated/knowledge/elt-technical-user-documentation#platform)
    - [ Transformation ](https://support.daasity.com/migrated/knowledge/elt-technical-user-documentation#transformation)
    - [ Integrations ](https://support.daasity.com/migrated/knowledge/elt-technical-user-documentation#integrations)
- [ User Manual and Support
  
  
  
  ](https://support.daasity.com/migrated/knowledge/user-manual-and-support) 
    - [ Daasity Logic and Best Practices ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#daasity-logic-and-best-practices)
    - [ Marketing Analytics ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#marketing-analytics)
    - [ Customer Analytics ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#customer-analytics)
    - [ Date Analytics ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#date-analytics)
    - [ Inventory and Returns Analytics ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#inventory-and-returns-analytics)
    - [ Submit a Support Request ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#submit-a-support-request)
- [ Release Updates
  
  
  
  ](https://support.daasity.com/migrated/knowledge/release-updates) 
    - [ Release Updates ](https://support.daasity.com/migrated/knowledge/release-updates#release-updates)

# View: Intermediary DRP Table(s): drp.daily_last_click_cpo_cpa

### Source Database Table

drp.daily_last_click_cpo_cpa

### Script that Populates Database Table

4111_CST_BAS_daily_last_click_cost_per_order.sql

### Script Description and Logic

Creates a daily level last click cost per order and cost per acquisition calculation across marketing channels and vendors for the last 30 days

### Source Tables Used In Script

| **Schema** | **Table (or Derived Table) Name** | **Table Type** | **Purpose** |
| --- | --- | --- | --- |
| drp | daily_first_click_cpo_cpa | database | Primary |
| drp | master_spend | database | Primary |
| drp | order_channel | database | Primary |
| drp | order_status | database | Finding first orders |
| N/A | last_click | derived | Last click channel attribution dimensions |
| N/A | last_sync_date | derived | Finding max load date |

### SQL Flow

### Calculated and Derived Fields

| **Target Column** | **Target Column Data Type** | **Source schema.table** | **Source Column** | **Transformation/Logic** |
| --- | --- | --- | --- | --- |
| last_click_orders | INT | drp.order_channel | N/A | COUNT(*) FROM drp.order_channel oc INNER JOIN drp.order_status os ON oc.order_id = os.order_id |
| new_customer_last_click_orders | INT | drp.order_channel | first_order_flag | SUM(CASE WHEN os.first_order_flag = TRUE THEN 1 ELSE 0 END) |
| cost_per_order | DECIMAL(20,4) | drp.master_spend | total_spend | CASE WHEN ms.total_spend IS NULL THEN NULL WHEN lc.last_click_orders = 0 THEN 0 ELSE ms.total_spend / lc.last_click_orders END |
| cost_per_acquisition | DECIMAL(20,4) | drp.master_spend | total_spend | CASE WHEN ms.total_spend IS NULL THEN NULL WHEN lc.new_customer_last_click_orders = 0 THEN 0 ELSE ms.total_spend / lc.new_customer_last_click_orders END |

 

All content © Daasity 2021. Do not copy, share or distribute.