====== How to create custom functions in charts ======

Sometimes you need to have a conditional replacement of a string or a numerical calculation such as a currency conversion which should happen automatically.

One such example case for String manipulations is that you are tagging your Meta/Facebook using dynamic tagging using the site_source_name and placement tags:

{{:advanced_transformations:custom_functions_charts.png?600|}}

Unfortunately the Facebook Ads connector does not support returning the exact same site source names and placements as it returns in the UTM tags. So we need a custom function:

==== Converting Facebook platform position to site source name ====

<code>
--- Function to convert currencies for both the revenues and the costs
CREATE OR REPLACE FUNCTION fb_platform_position (platform_position text)
    RETURNS text
    AS $$
BEGIN
    IF lower(platform_position) = 'facebook_desktop_feed' THEN
        RETURN'feed';
    ELSIF lower(platform_position) = 'facebook_mobile_feed' THEN
        RETURN'feed';
    ELSIF lower(platform_position) = 'instagram_feed' THEN
        RETURN'feed';
    ELSIF lower(platform_position) = 'facebook_marketplace' THEN
        RETURN'marketplace';
    ELSIF lower(platform_position) = 'facebook_right_column' THEN
        RETURN'right_hand_column';
    ELSIF lower(platform_position) = 'instagram_explore' THEN
        RETURN'instagram_explore';
    ELSIF lower(platform_position) = 'messenger_inbox' THEN
        RETURN'messenger_inbox';
    ELSIF lower(platform_position) = 'facebook_stories' THEN
        RETURN'facebook_stories';
    ELSIF lower(platform_position) = 'instagram_stories' THEN
        RETURN'instagram_stories';
    ELSIF lower(platform_position) = 'instagram_reels' THEN
        RETURN'instagram_reels';
    ELSIF lower(platform_position) = 'facebook_instream_video' THEN
        RETURN'instream_video';
    ELSIF lower(platform_position) = 'facebook_instant_articles' THEN
        RETURN'instant_article';
    ELSIF lower(platform_position) = 'an' THEN
        RETURN'an_classic';
    ELSIF lower(platform_position) = 'others' THEN
        RETURN'others';
    ELSE
        RETURN lower(platform_position);
    END IF;
END;
$$
LANGUAGE plpgsql
IMMUTABLE STRICT PARALLEL SAFE;
</code>

==== Converting Facebook platform position to platform ====

<code>
--- Function to convert currencies for both the revenues and the costs
CREATE OR REPLACE FUNCTION fb_publisher_platform (publisher_platform text)
    RETURNS text
    AS $$
BEGIN
    IF publisher_platform = 'instagram' THEN
        RETURN 'ig';
    ELSIF publisher_platform = 'facebook' THEN
        RETURN 'fb';
    ELSIF publisher_platform = 'audience_network' THEN
        RETURN 'an';
    ELSIF publisher_platform = 'messenger' THEN
        RETURN 'msg';
    ELSE
        RETURN lower(publisher_platform);
    END IF;
END;
$$
LANGUAGE plpgsql
IMMUTABLE STRICT PARALLEL SAFE;
</code>

You can now call this function in charts in the following way

{{:advanced_transformations:custom_functions_charts_1.png?600 |}}