About Dynamic Query

Dynamic Query gives you the ability to run ad hoc, custom queries on your DCP data in real time to answer your specific collaboration questions quickly and efficiently.

• Let a collaborator better segment your data before they create an audience.
• Build your own campaign measurement logic with Dynamic Query.

Dynamic Query works differently depending on whether you are querying your own data or data shared in a collaboration.

Before you start

Before you can run any query, configure your cloud bucket destination. This step is required, because every query runs against your own cloud bucket and writes results directly to your storage. AppsFlyer does not store your query results, so your data stays within your own cloud environment.

Run a query on your own data

The following sections cover how to access and query data you own.

Access Dynamic Query

This is a separate area from collaboration-based querying below. The Sources panel here only lists sources you own, you cannot add a collaborator's source in this view.

1. In AppsFlyer, go to the side menu > Collaborate > Data Clean Room.
2. Select the Dynamic Query tab.

Write your query

1. Browse the source's fields in the Sources panel. Click a field to preview its distinct values.

   Note

   This helps your filters match real data before you write the query. For more information, see Data Collaboration Platform (DCP) - Create and manage sources.

2. Write your query freely, at raw level or aggregated, your choice. Every source here belongs to you, so there's no permission check and no GROUP BY requirement.

   Note

   If you are also an AppsFlyer client, you can query your AF attribution data this way too, but results are aggregated only, even though it is your own data.

Run a query with collaboration data

The following sections cover how to access and query data shared with you in a collaboration.

Access Dynamic Query in a collaboration

1. In AppsFlyer, go to the side menu > Collaborate > Data Clean Room.
2. Select the Collaborations tab, then select the relevant collaboration.
3. Select the Dynamic Query tab.

When you query data shared in a collaboration, Dynamic Query can access every source added to that collaboration.

Write your query

1. Browse the source's fields in the Sources panel. Click a field to preview its distinct values.

   Note

   This helps your filters match real data before you write the query. For more information, see Data Collaboration Platform (DCP) - Create and manage sources.

2. Check the source's permission type. There are two permission types:

   Permission type	Join required?	Result level
   Intersected	Yes, you must join to your own source	Aggregated only
   All Data	No, you can query it freely	Aggregated only

   Note

   This determines whether your query needs a join. For more information, see Data Collaboration Platform (DCP) sharing permissions.

3. Write your query with a GROUP BY clause or an aggregate function, such as COUNT(*).

   Important

   A source you do not own always returns aggregated results, no matter which permission type it is shared under. Raw-level results stay available only for sources you own.

Privacy and query restrictions

Dynamic Query applies privacy protections at multiple levels:

• Non-owned sources always return aggregated results, see "Run a query with collaboration data" above.

• Key-type identifier fields, such as AppsFlyer ID, Android ID, and IDFA (Identifier for Advertisers), always return as hashed values in query results.
• Distinct values are not available for fields categorized as Keys, such as AppsFlyer ID or Android ID, when browsing the Sources panel.

Explore sample queries

The following examples show how each permission type works in practice.

SELECT
    t.brand_name,
    COUNT(DISTINCT t.hashed_email) AS total_clients,
    COUNT(*) AS total_purchases,
    SUM(t.revenue) AS total_revenue
FROM <collaborator_transactions_table> t
WHERE t.event_time BETWEEN DATE_SUB(current_date(), 60) AND current_date()
GROUP BY t.brand_name
ORDER BY total_revenue DESC
LIMIT 20;

SELECT
    t.country,
    COUNT(DISTINCT l.hashed_email) AS loyalty_members,
    COUNT(*) AS total_purchases,
    SUM(t.revenue) AS total_revenue
FROM <collaborator_transactions_table> t
JOIN <my_loyalty_club_table> l ON l.hashed_email = t.hashed_email  -- required join satisfies intersection permission
WHERE t.event_time BETWEEN DATE_SUB(current_date(), 30) AND current_date()
    AND t.brand_name = '<collaborator_brand_name>'
GROUP BY t.country
ORDER BY total_revenue DESC
LIMIT 20;

SELECT
    t.country,
    t.category,
    COUNT(DISTINCT t.hashed_email) AS total_buyers,
    COUNT(*)                       AS total_purchases,
    SUM(t.revenue)                 AS total_revenue,
    AVG(t.revenue)                 AS avg_order_value
FROM <collaborator_transactions_table> t
WHERE t.event_time BETWEEN DATE_SUB(current_date(), 30) AND current_date()
GROUP BY t.country, t.category
ORDER BY total_buyers DESC
LIMIT 20;

View and download query results

The following sections cover how to run your query and manage its results.

Traits and limitations

See also

• Data Clean Room Dynamic Query
• Data Collaboration Platform (DCP) guide
• Data Collaboration Platform (DCP) sharing permissions
• Data Collaboration Platform (DCP) - Create a collaboration
• Raw data field dictionary